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

Holiday - SQLUG Event

Although I'm on a holiday I took the time last week to visit a SQL Server event organized by the Belgian SQL Server User Group (yeah yeah I know => FREAK). Dirk Gubbels from MCS was invited to talk about "SQL server 2005 performance monitoring and tuning".

It was nice review session about performance tuning and the power of SQL Server 2005 regarding this matter.

Great talk by Dirk and nicely organized by the UG.

PS. And yes, another WHOLE week @ home :-)

Super undocumented trace flag 144

First of all I would like to thank Slava & Ketan from Microsoft for their very fast and accurate support. It's amazing that people at their level are willing to help customers.

As mentioned in a previous post the UpdateBatch behavior causes half of the memory from our server to be allocated to procedure cache. This might not be a disaster but we didn't really feel comfortable with this situation.

Lucky for us there is a undocumented (gee really) traceflag that changes the behavior of the way plans are cached. With trace flag 144 enabled the (n)varchar variables are cached with their maximum size instead of the defined parameter size for parameterized queries. This has cut down the size of our plan cache with many gigabytes.

We were warned to look for the spinlocks on SQL_MGR with this trace flag enabled. We used another undocumented feature DBCC SQLPERF(SpinLockStats) to monitor the spinlocks. Although we do not see anything alarming (with the limited understanding we have about spinlock internals) we do see different behavior:

With the trace flag:
Collisions: 2091
Spins: 452704

Without the trace flag:
Collisions: 1977661
Spins: 146579351

In case you do have problems with the spinlocks there is hotfix 716 (which is not publicly available as far as I know).

*EDIT*
Our good friend Nick adapted our framework which will be released in November (.NET 2.0). This framework copied the UpdateBatch behavior but it has been changed now so we will no longer need the trace flag as of the new framework. The parameter length is now equal to the column length.