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.

SQL Server 2005 Best Practices Analyzer

When I look at this scheduled webcast something tells me that the Best Practices Analyzer is not far away :-)

USERSTORE_TOKENPERM issue

Our migration to SQL Server 2005 x64 was catastrophic :-( We probably ran into an obscure bug that will be fixed in Service Pack 2. A temporary workaround is to add the user(s) that connect to SQL Server to the sysadmin role.

The issue is that the USERSTORE_TOKENPERM cache grows too large. Every statement that gets executed is matched to this store to check whether the executing user has sufficient permissions. While checking this SQL Server uses spinlocks to prevent multiple users from accessing the store, when the cache grows too large the queries might suffer from slowdowns. Under great load this can become a big issues because locks might be taken too long causing many problems.

Our problem was enlarged by the fact that we use UpdateBatch for our inserts and updates. UpdateBatch sends the statements as prepared statements with the parameters already defined. Unfortunately when doing this it sizes varchars by the length of the text to insert and not the field length. This makes SQL Server consider it as a new query whenever the size is different and as a result our procedure cache gets flooded with query plans. SQL Server stores only one entry for parameterized queries but since it considers practically all our inserts as unique queries the userstore grows larger too.

Lucky for us SQL Server 2005 offers a lot of dmv's to check all this.

To check the size of the cache items use the following query:
SELECT * FROM sys.dm_os_memory_clerks
ORDER BY (single_pages_kb + multi_pages_kb) DESC

The single_pages_kb are stolen from the buffer pool whereas multi_pages_kb are pages taken outside the buffer pool.

SQL Server 2005 Migration

A big hooray! We've migrated our production environment to SQL Server 2005 today!
Tomorrow will be a first test day but Wednesday will be the real test since most offices are closed tomorrow and Tuesday making Wednesday a little heavier too :-)

A couple of figures:
Database Size: 330GB
Daily growth: 2GB
Max. Users: 4500
Concurrent Users: 2000

So far it is a 'flat' migration meaning we haven't implemented any new features and will keep it this way for another 2 weeks to make sure everything runs smoothly. We have a transactional replication to SQL Server 2000 so we can go back whenever something goes seriously wrong. After 2 weeks this will be removed and we can start using new features.

We have changed a lot on system level though:
Windows 2003 x64 Enterprise Edition
SQL Server 2005 x64 Enterprise Edition
Storport Driver and MPIO
Disk alignment

Next on the list for November is a whole new framework in .NET 2.0. Exciting times!

Rebuilding an index and full scan of statistics

I recently saw a post on a forum discussing update stats and when to trigger it. Well first of all SQL Server triggers the update if auto stats is enabled when approximately 20% of the rows have changed. There is a difference between SQL Server 2000 and SQL Server 2005 however.

SQL Server 2000 uses a counter to track row modification whereas SQL Server 2005 uses a counter that tracks changes on column level. The biggest difference here is that updates are rated different when updating a key column. A non-key column update just raises the counter with the number of updated columns whereas a key column update raises the counter with 2 for each column. Another difference is that TRUNCATE TABLE and BULK INSERT does not raise the counters in SQL Server 2000 but they are accounted for in SQL Server 2005.

Obviously sometimes you may want to disable auto stats because they can hinder your performance in an OLTP environment and some of your tables might have a good representative set of data that statistically speaking will not change too much during the day. SQL Server 2005 gives you the option to update statistics async so the query triggering the update will not be blocked while updating the statistics but nevertheless you might not even want this to happen. In that case my advice is to update your statistics before your rebuild your indexes, unless you specify a full scan anyway (tables that are less than 8MB are always fully scanned for stats updates) but on big databases it might not be a viable option.

Why do I say before you reindex? Because a reindex automatically triggers an update stats with a full scan for THAT index since it has to read all the data for it anyway (pretty intelligent no?). When you trigger an update stats with the default sample set those stats are overwritten again with 'less' accurate stats. But yet another difference in SQL Server 2005, when you use sp_updatestats only statistics that require an update are actually updated.

You can easily check this behavior by using DBCC SHOW_STATISTICS after you rebuild an index.

Inside SQL Server 2005

As promised I bought the two Inside SQL Server 2005 books.

Inside Microsoft SQL Server 2005: T-SQL Querying
Inside Microsoft SQL Server 2005: T-SQL Programming

I'm half way the T-SQL Querying book and it's quite good. Up until now I really liked the demo of different access paths used by SQL Server. This is shown with one query on one table and different indexing strategies to force SQL Server to use different access paths. Another interesting thing to see is the way Itzik rewrites queries on a thousand different ways and each time a little better :-)

I also like the little stories where some 'trivial' things are explained like why people say SEQUEL, the negative logic riddle about the two gates, Halloween, the Gauss story, ... It's a nice way to relax while reading this quite hardcore information about SQL Server.

On to the next half, the next book and by then hopefully the sequel to these 2 :-)

NOLOCK vs Clustered Index Order Part V

Just when I had given up hope to ever knowing the answer to the following posts.

Part I
Part II
Part III
Part IV

Itzik Ben-Gan comes up with this 3 part series. It was really something that puzzled me and kept me busy quite some time. You see patience is rewarded somehow :-)

Great job Itzik, you really made my day! As a reward I just ordered both parts of Inside SQL Server 2005 :-)

Foreign Keys DROP/CREATE generator

We were in the process of moving tables between filegroups and the way to do this is to drop and recreate the clustered index. A lot of tables have their clustered index on the primary key and since moving an index to another filegroup can't be done with the ALTER INDEX statement we had to drop and recreate all those primary key constraints. But wait a minute... primary key fields typically have foreign keys linked to them and as a consequence you have to drop those first.

Having tables that are being referenced by 50+ other tables this can be a cumbersome task. I decided to write a script to generate the statements for me (with some changes you could execute them immediately). Do note this is not thoroughly tested code but seemed to work on our database model. I don't know of course what kind of funky stuff you guys come up with.

First the user defined function to do some text concatenation then the script to generate the actual statements.


IF OBJECT_ID (N'dbo.GetFieldsConcat', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetFieldsConcat;
GO
CREATE FUNCTION dbo.GetFieldsConcat (@objectName sysname, @foreignKeyName sysname, @isParent bit)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @theFields varchar(max)
SET @theFields = ''
SELECT @theFields = @theFields + '[' + CASE WHEN @isParent = 1 THEN pc.name ELSE cc.name END + '],'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
INNER JOIN sys.foreign_key_columns pfkc ON pfkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns pc ON pc.column_id = pfkc.referenced_column_id AND pc.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.column_id = pfkc.parent_column_id AND cc.object_id = fk.parent_object_id
WHERE ((@isParent = 1 AND op.name = @objectName) OR (@isParent = 0 AND oc.name = @objectName))
AND fk.name = @foreignKeyName
IF LEN(@theFields) > 0
SET @theFields = LEFT(@theFields, LEN(@theFields) - 1)
RETURN(@theFields)
END;
GO


DECLARE @TableName sysname
SET @TableName = 'tbl_stockable_type'
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[' + fks.name + '].[' + fk.name + ']'') AND parent_object_id = OBJECT_ID(N''[' + cs.name + '].[' + oc.name + ']''))' + CHAR(13) + CHAR(10) +
'ALTER TABLE [' + cs.name + '].[' + oc.name + '] DROP CONSTRAINT [' + fk.name + ']'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
WHERE op.name = @TableName
UNION ALL
SELECT 'ALTER TABLE [' + cs.name + '].[' + oc.name + '] WITH NOCHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY(' + dbo.ufn_GetFieldsConcat(oc.name, fk.name, 0) + ')' + CHAR(13) + CHAR(10)
+ 'REFERENCES [' + ps.name + '].[' + op.name + '] (' + dbo.ufn_GetFieldsConcat(op.name, fk.name, 1) + ')'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
WHERE op.name = @TableName

SQL Server 2005 Books Online (July 2006)

Updated Books Online - Download
Updated Samples - Download

Hello world from Vista B2

Ah, finally took me the time to install the Vista B2 release. It looks really great so I hope it works as good as it looks! Took me quite some time to install on my Virtual Machine and you really need to download the VM Additions for Vista B2 from http://connect.microsoft.com in order to get it to run smoothly. Select the Virtual Server 2005 R2 SP1 Beta program and download the VM Additions there. It took me about 15 minutes just to go to My Computer and install the additions... just to show you the importance of the VM additions ;-)

Next on the list Office 2007!

SPID -2 (2 44 HUD HUD)

I had someone come up to me having trouble with an unkillable SPID -2. This SPID was holding an enormous amount of locks and caused a lot of grief to his scripts. He had already tried to restart the SQL Server service but to no avail. I knew the negative SPIDs had something to do with artificial SPIDs but this was buried in my mind far far away :-)

As always the Books Online to the rescue! Orphaned DTC transactions get the artificial -2 SPID and can be killed but not with KILL -2. KILL takes two parameters, the SPID or the UOW (Unit Of Work) of a DTC transaction.

Determining the UOW (which is a GUID) can be done in several ways. It can be found in the request_owner_guid column of sys.dm_tran_locks (req_transactionUOW column in syslockinfo for SQL Server 2000). You can find it in the error log or in the MS DTC Monitor.

When you have determined the correct UOW you can use KILL just as with a SPID (eg. KILL '8CAF7C31-564C-43EC-9B37-640B50FDDEC0'). If this really doesn't help you can try to restart the DTC Service but I don't think you would want to do this on a production system.

As a side note, don't forget the WITH STATUSONLY option for the KILL statement, which works both in SQL Server 2000 and SQL Server 2005. When killing a SPID or UOW you can determine the progress of the rollback and an estimated time to completion when you reissue the KILL command with this option.

SQLblog.com

Finally a place where all interesting information is grouped together.
Check out http://sqlblog.com/ - "THE Place for SQL Server Blogs"

Although my blog is not listed it still is a great site ;-)