Big Procedure Cache in SQL Server 2005

As I once mentioned in a post I felt that the procedure cache was really out of proportion. We were assured that this was not an issue but now I stumbled upon the following post where they explain that the behavior has been seriously changed in SP2.

Align SQL Server 2005 plan cache size limit to a size similar to that of SQL Server 2000: The maximum size limit of the SQL Server 2005 Plan Cache in RTM and SP1 is significantly larger than in SQL Server 2000. To reduce the SQL Server 2005 plan cache maximum size limit to a size similar to that of SQL Server 2000, the limit for signaling internal memory pressure on a cache is changed from that of SQL Server 2005 RTM and SP1. The following table shows how the maximum limit of the plan cache is determined for each version of SQL Server:

SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB

SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

Example:
For a SQL Server with 32Gb total SQL server memory,
SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB

SQL Server 2005 SP2 cachestore limit will be 75% X 4 + 10% X (32-4) = 5.8GB

I suppose I was not the only one who thought this was just too much. I love the way SQL Server is self tuning for a great part but maybe it would be nice to allow expert users to configure some aspects themselfs anyway, especially with x64 where memory usage is lifted to a whole new dimension. When looking at the fixlist for Service Pack 2 I am getting more and more convinced to wait for it before installing SQL Server 2005 in large production environments.

Troubleshooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1

The SQL Programmability team have started a series of posts that everyone - using SQL Server 2005 - should read (not for the faint of heart though).

They could help you isolate performance issues very fast and we all know this can be the difference between success or catastrophic failure.

Performance Impact of Enabling Page Checksum and Default Trace

Check out this great post about the impact of Page Checksum and Default Trace

SQL Server 2005 Compact Edition

SQL Server 2005 Compact Edition has been RTM'ed (formerly known as SQL Server Everywhere). More information here.

There finally is some information about the Best Practices Analyzer for SQL Server 2005. It will be released with Service Pack 2. More information here.

We have set a new date for our SQL Server 2005 Migration and we are going to production in April if all goes well.

xml datatype and distributed queries

Recently I stumbled upon a peculiar limitation in SQL Server 2005.

I tried to execute a query against a remote server when I got the following error:

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'OPENROWSET' has xml column(s).

I came across the following remark in the Books Online (Guidelines for Using Distributed Queries):

Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.

This statement is not entirely true, when querying only non-xml fields it seems to work just fine.


Repro script (SQLCMD mode)

:CONNECT myServer

use myDB
GO
CREATE TABLE myXml
(ID int, someXml xml)
GO

:CONNECT myRemoteServer

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID from myDB.dbo.myXml') -- should work just fine
GO

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID, someXml from myDB.dbo.myXml') -- raises the above mentioned error
GO

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;', 'SELECT ID, CAST(someXml as varchar(max)) from myDB.dbo.myXml') -- workaround?
GO

If I find an explanation for this limitation I will post an update but I haven't figured out why this is not possible yet.

Another year (almost) over

2007 here we come!

It will be the year of SQL Server 2005 SP2, Windows 2003 Server SP2, yet another SQL Server 2005 migration, the year they start building my house, elections in Belgium, the last year in my twenties, our 10th anniversary, ...

Let's hope it will not be a year of disasters and violence but that's probably too much to ask :-(

Hope to see you all again next year!

SQL Server 2005 SP2 - December CTP

Is now available here!

Trace Flag 1200

When you enable trace flag 1200 SQL Server gives you extensive information about all the locks being taken by a specific query or procedure.

There is a little behavior change between SQL Server 2000 and SQL Server 2005 however. In SQL Server 2000 it was a session wide flag so DBCC TRACEON (1200) was enough to get you going while SQL Server 2005 considers it a server wide flag meaning you have to use DBCC TRACEON (1200, -1) to get it going again.

Thanks to Dirk G from MCS Belgium for clearing out the difference in behavior between SQL Server 2000 and SQL Server 2005.

For SQL Server 2000 you do the following:
DBCC TRACEON (3604)
DBCC TRACEON (1200)

For SQL Server 2005 you do the following:
DBCC TRACEON (3604)
DBCC TRACEON (1200, -1)

A query cannot update a text column and a clustering key

A colleague recently got the following error message when executing an update query:

"The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time."

The message is clear but should actually read:

"The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time when multiple rows might be affected."

It is mentioned in the Books Online in the notes of the UPDATE statement. There is a little change in behavior between SQL Server 2000 and SQL Server 2005 however.

The books online for SQL Server 2000 state:

"If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message."

The books online for SQL Server 2005 state:

"If the UPDATE statement could change more than one row while updating both the clustering key and one or more text, ntext, or image columns, the partial update to these columns is executed as a full replacement of the values."

So the restriction is a bit less drastic in SQL Server 2005 but it might have a performance impact though. Obviously updating the clustering key is something you wouldn't want to do too often either.

I was wondering where this restriction was coming from so I decided to ask the only person who would know the answer since the internet had really no reference to why this was happening and I couldn't come up with a reason myself either. Paul Randal was nice enough to share that it was necessary to prevent replication from breaking.

Visual Studio Team Edition for Database Professionals

You have probably read on blogs everywhere that Visual Studio Team Edition for Database Professionals has been released (check out GertD's Blog). The download will be available on MSDN any day now. Do note that Visual Studio Team Suite is required (of which a free trial can be downloaded).

Unlike my love for ISQLw over Management Studio I do feel VSTE for DBPros was a missing piece from our toolkit. It finally brings some features our way that have been around for a long time for .NET developers like Unit Testing, Refactoring, Team Foundation Server integration, ...

Another nice feature is "Data Generator". Random data can be easily generated (even based on regular expressions) and can be configured to take into account the ratio between related tables.

Ad hoc queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005

This seems to be the official description of the bug we ran into on our cloudy day :-(

Check out the KB article here

We are carefully playing around with new SQL Server 2005 features however since we are planning to go for it again in Q1 2007.

- Database mirroring
- Database snapshots
- Analysis services

Very exciting stuff! If I run into obscure behavior you will read about it.

The first one would be to beware of snapshots and statistics, remember that snapshots are read only so when you query a snapshot the statistics should exist (and be up-to-date) in the source database when the snapshot is created or your query plans may not be as optimal as you like ;-) It's pure logic but something to remember!

SQL Server - Best Practices

A great website on Best Practices for SQL Server is now available on Technet.

Installed SQL Server 2005 - Service Pack 2 CTP

We've just installed the CTP for Service Pack 2.
I'll be posting the nifty features I come across every now and then (check for updates on this post).

• Drag & Drop scripts no longer asks for the connection for each and every one of the files (so the good old Query Analyzer behavior is back!)
• They have implemented Kalen Delaney's request for adding an optional database_id parameter to the OBJECT_NAME function
• The vardecimal functionality is nice too, the nifty part is that they have added stored procedures to estimate the space saved by using this option
• They have updated the build number in the about box of SSMS (which they forgot in build 2153)
• ...

What's new in SQL Server 2005 SP2

Check out the new improvements here

Some additions that catch the eye:

• Added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
  
  Funny that they add this new datatype only to the Enterprise Edition in my humble opinion. Although I understand they probably have the most benefit of storage saving features.
  


• Plan cache improvements that provide improved system performance, better use of the available physical memory for database pages, and the ability to return text XML query plans that contain an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_text_query_plan table-valued function.
  
  Sounds very nice but I'd love to see more detail on how exactly this is achieved.
  


• The value of the BypassPrepare property of the Execute SQL task has been changed to True by default.In earlier versions, the value of the BypassPrepare property was false, which indicated that statements were always prepared. In SP2, by default queries are not prepared. This eliminates errors with certain providers when you try to prepare a statement that uses parameter placeholders ("?").
  
  Seems I'm not the only one with this problem.
  


• Generate Script Wizard. You can now specify that the scripted objects include a DROP statement before the CREATE statement. Scripting of objects into separate files is now possible.
  
  Finally!
  

The fixlist for SQL Server 2005 SP2 CTP can be found here

Why "lock pages in memory" may increase I/O performance

I was reading SQL Server I/O Basics, Chapter 2 when I came across a very interesting chapter discussing the "Lock Pages In Memory" policy.

You all know that the AWE API is used to lock pages in memory and this is true for 32-bit as well as 64-bit SQL Server installations. Do note that the "awe enabled" configuration settings exists in 64-bit installations but it is of no meaning. In order for this to function the "Lock Pages In Memory" privilege is needed for the SQL Server service account. In a 32-bit environment the "awe enabled" configuration settings still has to be enabled.

But the thing I really learned here is why I/O can be improved by this setting. Because memory for an I/O should not cause page faults, the memory first has to be locked, the I/O goes through and the memory has to be unlocked again. Having the "Lock Pages In Memory" privilege avoids this lock and unlock transition during I/O and thus improves performance. This behavior can be disabled by removing the privilege or by enabling trace flag 835 (for 64-bit installations).

If you do decide to use AWE and thus "Lock Pages In Memory" do test your solution thoroughly with this setting. It might cause memory pressure which could lead to performance degradation. You might also want to consider specifying a max memory setting when you enable this. One of the side effects of AWE is that the specified memory is grabbed as soon as SQL Server starts. As of Windows 2003 and SQL Server 2005 this however has been solved through dynamic allocation of AWE memory.

Another, unrelated, interesting fact that is mentioned in the paper is that using "Encrypted file systems" (EFS) stops I/O from being done in an asynchronous manner. This could of course hinder performance, especially for checkpoint operations. I can't say I ever thought about doing this but just so you'd remember.

*EDIT*
I found a KB article KB922121 discussing the EFS behavior with SQL Server.

The problem with ALTER TABLE

Recently we were trying to optimize some very large tables and we noticed that a bigint was used where an int was perfectly suitable. In order to get rid of it we issued an ALTER TABLE to change the datatype to int.

So far so good until I we checked the allocated space by the tables and noticed that the size hadn't changed. Another optimization we issued was creating a clustered index on the tables. As we checked the size of the tables after this action we noticed that now they had become smaller than before.

Lucky for me I don't like SQL Server behavior I can't explain and so I started looking for answers. It was quite obvious that SQL Server wasn't changing the original size of the column although inserting a value larger than the allowed value resulted in "Arithmetic overflow error converting expression to data type int.", so the metadata was definitely altered correctly.

I started looking for answers when I came across the next post by Kalen Delaney, explaining our issue very well.

The post contains a very good query to check this behavior and sure enough the offset of the column was still 4 bytes off (8 byte bigint vs 4 byte int).

SELECT c.name AS column_name, column_id, max_inrow_length,
pc.system_type_id, leaf_offset
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p ON p.partition_id = pc.partition_id
JOIN sys.columns c ON column_id = partition_column_id
AND c.object_id = p.object_id
WHERE p.object_id=object_id('bigchange');

So thank you very much Kalen for making me understand yet another SQL Server mystery :-)

SQL Server 2005 SP2

First rumours say it will be released in Q1 2007.

The userstore cache problem (which we know VERY well) should be fixed too :-)

It's quite necessary since SQL Server 2005 will only run on Windows Vista as of Service Pack 2.

*EDIT*

This seems to be not entirely true, it is supported with SP1 but you have to carefully look how to install it on Vista.

Internet Explorer 7 RC1

Some Microsoft employees convinced me to install IE7.
It has some nice new features like tabbed browsing, anti-phishing, popup blocker, ...


I have been using Maxthon for a long time, it is a shell around IE and contains a lot of nice features too. I might no longer need it now IE7 supports a lot of the features I got used too. There is one feature missing though (can be downloaded but doesn't come out of the box as far as I see) and that is the "Mouse Gestures" feature.

IE7 will be offered by Windows Update too and should be released the 1st of November.

Anyway, most of the sites I tend to visit seem to work quite well and last but not least I am able to post to my blog using IE7 too.

SQL Server 2005 Upgrade Technical Reference Guide

Microsoft has released a very nice whitepaper (and a big one too) about migrating to SQL Server 2005. They cover all sorts of migration scenarios like side-by-side, in-place, 32-bit to 64-bit, upgrading your logshipping, replication, etc.

Really a must read if you are thinking of migrating.

SQL Server vs I/O

Out of all the hardware requirements for SQL Server the I/O requirement is one of the most common bottlenecks since it is usually the most difficult to manage and extend. Before you consider installing SQL Server on a production environment make sure you understand the I/O requirements for SQL Server and test your hardware accordingly.

I have already posted some best practices regarding SQL Server vs a SAN (some of them apply to non SAN solutions too).

First get to know SQL Server on I/O level by reading the following whitepapers:

• SQL Server 2000 I/O Basics
• SQL Server I/O Basics, Chapter 2

Also read the whitepaper Physical Database Storage Design on how to structure you drives and database files.

Test drive your configuration by using the following tools:

• SQLIO
• SQLIOSim x86, SQLIOSim x64, SQLIOSim IA64 these are the replacements for SQLIOStress (I found these links here)

While SQLIO is really designed to test your disk subsystem from a performance point of view SQLIOSim is designed to test the robustness of your disk subsystem. SQLIO requires a lot more input on what exactly to test and you can find more information on SQL Server I/O patterns in this presentation by Gert Drapers.

Don't forget to check your waitstats once in a while to see if your SQL Server is waiting for I/O related operations (more info on waitstats can also be found here - in SQL Server 2005 they are well documented in the Books Online).

For SQL Server 2000: DBCC SQLPERF(WAITSTATS)
For SQL Server 2005: SELECT * FROM sys.dm_os_wait_stats