SQL Server | SQL in the Wild

Token and perm user store growth

Author: Gail 2 May 2008 0 Comments

I wrote about TokenandPermUserStore issue a while back. I revisited the issue recently on 2 production servers to see if it was still an issue on a fairly recent SQL build.

The two servers in question run the same application, but with a vast difference in usage.

Server 1 – Itanium, 12 processors, 48 GB memory, build 3042 (SP2) running on Windows Server 2003 DataCenter SP1
Server 2 – x64, 16 processors, 64 GB memory, build 3215 (SP2 CU5) running on Windows Server 2003 DataCenter SP2

On both servers there’s a job that checks the size of the token cache and flushes it if it’s above 750 MB. The size was set because when I first set up the jobs, I noticed that the CMEMTHREAD waits were getting noticeable if the cache got above 1 GB.

On both servers, I set up a job that would log the size of the cache to a table. On server 1, that ran every 15 min. On server 2 it ran every 5 min. The results were a little frightening.

(more…)

Admin, SQL Server

Execution plans – important properties

Author: Gail 23 April 2008 3 Comments

Time I wrote another piece on execution plans (see the first post for the full list)

So, now a quick look at some of the important properties of exec plan operators.

Estimated number of rows – This is the number of rows that the query optimiser estimates this operator will process. The estimate is based on the statistics and on other data available at time of compilation. It’s possible that this number includes a fraction, due to the way to optimiser does its estimates
Actual number of rows – This is the actual number of rows that were processed by the operator. This value is calculated by the query processor at execution time. An estimated execution plan will not include this. (more…)

Execution Plans, SQL Server

Execution plan operations – misc

Author: Gail 30 March 2008 0 Comments

This is going to be the last post on the operators in an execution plan. I’m not going to cover all the other operators. There are a lot of operators, many quite specialised or only appearing in specific scenarios. Conor Cunningham’s writing a series covering the deep, dark details of some of the operations

So onto the operators. I’m going to cover Sort, Concatenate and Scalar Function.

Sort

Sort has three logical operators:

Sort
Distinct Sort
Top-N Sort

Sort is used to satisfy any ORDER BY that is specified in a query, if the index used does not naturally sort the rows in the desired way. It also may appear in the execution plan before a merge join or a stream aggregate. Sometimes SQL may decide that a sort followed by a merge join or stream aggregate is cheaper than a hash join or hash aggregate.

Distinct sort is used for DISTINCT, sometimes for UNION and sometimes for GROUP BY where no aggregates are specified.

(more…)

Execution Plans, SQL Server

Four minor new features of SQL 2008

Author: Gail 24 March 2008 0 Comments

There are a couple of new features of SQl Server 2008 that I’m pretty excited about that I haven’t seen geet agreat amount of attention. I thought I’d write a short article on them, so they don’t get overlooked.

Variable initialisation
Row constructors
Multi-server queries
Intellisense/syntax checking

Variable Initialisation

I’ve always been more partial to the C# way of declaring and initialising variables all at once. Now it’s possible in T-SQL too.

DECLARE @SomeVariable INT = 0, @SomeOtherVariable CHAR(5) = ‘Hello’

It’s not revolutionary, but I do like it. Anything that reduces the amount of typing and reduces the chances of bugs is worthwhile in my opinion

(more…)

SQL Server

Dev Chat

Author: Gail 19 March 2008 1 Comment

I spent yesterday at the JHB SQL Server 2008 Dev Chat. Most definitely a day well spent. The two presenters (Peter Willmot and Eben de Wit) were both knowledgeable and entertaining and the material covered was interesting.

The sessions covered some of the larger changes in SQL Server 2008.

First up was a brief look at some of the changes on the administration side. The first topic was around the transparent database encryption. this is a fantastic feature for people concerned about the security of the database files themselves. The database is encrypted on disk, decrypted in memory, and any attempt to attach the encrypted files to another instance of Sql will fail, unless the certificate that was used to encrypt the database is present on the second instance.

One major warning on that. Loose the certificate, say goodbye to your database.

Second of the admin-related changes discussed was the DMF (Policy-based management). This is a fantastic new feature and there are two nice ways of using it.

(more…)

SQL Server

On recoverability

Author: Gail 6 March 2008 2 Comments

Had a lovely situation this morning.

I can’t go into details, but essentially a database had no off-site backups, no tape backups and the only full backups were on disk. Then the SAN glitched and both the data files and backups were corrupted.

I’m probably preaching to the converted, but I don’t think there are many more important things on a production server than ensuring good backups.

However, having the backups is not enough. Can they be restored? If there’s a complete drive failure of all drives connected to a server, can the databases be recovered?

When the pawpaw hits the fan (as the local saying goes), that’s the only thing that matters.

Admin, Rants, SQL Server

Parameter sniffing, pt 2

Author: Gail 25 February 2008 2 Comments

A while back I wrote about parameter sniffing, the situation where SQL compiles and caches an execution plan that is appropriate for a certain value of a parameter, but is non optimal for other values. There’s another side to parameter sniffing though – when the optimiser can’t sniff at all.

When a batch is submitted to the optimiser, the value of any parameters (from stored procedure, auto parametrisation or sp_executesql) are known to the optimiser. So are the value of any constants used in the SQL statement. The value of local variables, however, are not.

When the value of a variable used in the where clause is not known, the optimiser has know idea what value to use to estimate the number of affected rows. Hence, it has no idea how many rows from the table will satisfy the condition.

(more…)

Performance, SQL Server

RIP Ken Henderson

Author: Gail 29 January 2008 2 Comments

I just ran across the news now. Oh my.

I attended a presentation that he did at the Pass conference in Denver last year. I was struck by his passion and humility. I have two of his books, and they are for me a first point of reference for any problem.

He will be sorely missed.

Edit: Just ran across a very nice tribute to Ken.

Ken Henderson

SQL Server

Execution plan operations – aggregates

Author: Gail 27 January 2008 2 Comments

On to aggregates.

I’m not going to dwell too long on the logical operators for aggregates, as they aren’t as interesting as the joins were. Rather I’m going to concentrate on the physical operators involved.

The two physical operators used for aggregates (and a couple of other logical operations) are:

Stream Aggregate
Hash Aggregate

Stream Aggregate

The stream aggregate is the faster and more efficient of the aggregate operators. For it to be used, the input rowset must be sorted in order of the grouping columns.

(more…)

Execution Plans, SQL Server

Comparisons with NULL

Author: Gail 16 January 2008 1 Comment

Or ‘True, False or FileNotFound’ ⁽¹⁾

Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when it is used in a comparison, it returns results that look surprising. Take this as a quick example

DECLARE @Var1 INT
SET @Var1 = NULL

SELECT
CASE WHEN @Var1 = NULL THEN ‘Var1 = Null’ ELSE ‘Var1 <> NULL’ END AS EqualityTest,
CASE WHEN @Var1 <> NULL THEN ‘Var1 <> Null’ ELSE ‘Var1 = NULL’ END AS InequalityTest

The results look a little strange. They’re supposed to. (No, I didn’t mean IS NULL/IS NOT NULL)

(more…)

SQL Server, T-SQL