Self-Service Hotfixes

Those of you who read my blog regularly may have noticed the previous post and maybe more important the comments on this post.Somehow I think we were not the only ones having this questions and remarks. As a follow-up please read the following blog posts as they are very relevant to the discussion.

First of all there is the Self-Service hotfix which is explained here. This allows you to download CU's without formally contacting Microsoft Support.

Second of all there is a nice explanation about the new Service Pack approach here. Basically is sums up the ISM model again but the news is that they will include Service Packs into this model too, which means they will also be released on schedule and more often!

Great news and again excellent proof that Microsoft is willing to listen to it's customers.

*NOTE*Self-service: I had to change my country to United States in order to get the download link on top.

SQL Server 2005 SP2 Cumulative Hotfix Packages and SQL Server 2005 Service Pack 3

The guys @ Microsoft have been busy improving the quality of SQL Server even more.

First of all CU7 was released (Build 3239) here.
As usual this means CU8 has been announced here.

I know there has been a lot of requests from the customers to release Service Pack 3 for SQL Server 2005. Although I see the benefits in the Incremental Servicing Model many people like the "certainty" of a good old service pack. Because Microsoft listens to its customers they have decided to release Service Pack 3 this year. More information can be found here.

Table Variable vs Parallelism

There are many myths surrounding table variables and one of the most common is probably the 'in memory' story. There are however a couple of other interesting facts about temp variables which you should also know about. The guys from the Storage Engine have an excellent post about table variables so you definitely have to read it.

One of the things that caught my eye in the post was the fact that queries do not go parallel when table variables are involved. This was actually something I had never come across so I decided to put it to the test. I borrowed a query from Craig Freedman who has an excellent series on parallelism. Here we go:

CREATE TABLE T (A INT, B INT IDENTITY, C INT, D INT)
CREATE CLUSTERED INDEX TA ON T(A)

SELECT COUNT(*) FROM T OPTION (MAXDOP 0)

UPDATE STATISTICS T WITH ROWCOUNT = 1000000, PAGECOUNT = 100000

SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DECLARE @t AS TABLE
(NumberOfRows int)

INSERT INTO @t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

For those of you who want to try it with a temp table:

CREATE TABLE #t
(NumberOfRows int)

INSERT INTO #t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DROP TABLE #t

SQL Server 2005 SP2 Cumulative Hotfix Package 7 - Announced

With every release comes a new announcement of course.

http://support.microsoft.com/kb/949095/

SQL Server 2005 SP2 Cumulative Hotfix Package 6 - Released

It is out there! The build number is 3228.

http://support.microsoft.com/kb/946608/

Mainstream support on SQL Server 2000 SP4 & SQL Server 2005 SP1

One last reminder that the mainstream support for SQL Server 2000 SP4 and SQL Server 2005 SP1 will end on 08/04/2008.
For more information about this visit the guys from Microsoft SQL Server Release Services.

SQL Server 2005 Best Practices Analyzer (January 2008)

In case you have not noticed yet, the SQLCat team has released a new version of the Best Practices Analyzer. Among other things it contains 60+ new rules and the Analysis Services rules have been updated so they match the Best Practices warnings in BIDS 2008.

More information can be found here and the download can be found here.

SQL Server 2005 SP2 Cumulative Hotfix Package 6 - Announced

Because CU5 is released here is the next announcement.
The question is of course how many of you have already implemented CU5?

SQL Server 2005 SP2 Cumulative Hotfix Package 5 - Released

It is out there! The build number is 3215.

http://support.microsoft.com/kb/943656/

EXEC AT

With every release there are really some features that you seem to miss, maybe because they are small or maybe because you just do not use that functionality that often.

That is how I recently stumbled upon a nice feature that has been added in SQL Server 2005 but I had never seen before.

EXEC AT allows you to execute queries on a linked server just like OPENQUERY and OPENROWSET but with a little less limitations. OPENQUERY and OPENROWSET for example do not accept variables for their parameters and they act like a table and thus limit you from executing certain statements like DDL.

EXEC AT on the other hand can take parameters:

EXEC sp_addlinkedserver [MyLinkedServer], 'SQL Server';

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AT [MyLinkedServer]

It can do DDL:

EXEC
(
'USE myDB;
CREATE TABLE myTable
(myId int NOT NULL PRIMARY KEY,
myVarChar varchar(100) NULL
)'
) AT [MyLinkedServer]

It can take a username:

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AS USER = 'WesleyB' AT [MyLinkedServer]

And last but not least it can take a variable:
DECLARE @SQLStmt nvarchar(max)
SET @SQLStmt = 'SELECT [LanguageID], [Description] FROM myDB.dbo.Translations'

EXEC(
@SQLStmt
) AT [MyLinkedServer]

It may not be the most funky feature in SQL Server 2005 but it really has potential when you are working with linked servers.

SQL Server 2005 Books Online (September 2007)

Because I have a day off my colleague said I should stop working and blog something.
Since inspiration comes mostly at work I will keep this one simple.

The updated Books Online for SQL Server 2005 are available here.

Sequential GUIDs

Today we found a table that was perfect for testing the newsequentialid() default. My colleague killspid thought it might be useful to talk about it and said "Blog it!".

This new feature has been added to SQL Server 2005 although it was available in SQL Server 2000 with the addition of an extended stored procedure written by Gert Drapers.

The Books Online state the following for the newsequentialid function:
"Creates a GUID that is greater than any GUID previously generated by this function on a specified computer." I can hardly say I ever saw a statement more simple but yet so complete.

Anyway, we decided to play around a bit with the sequential GUID too determine if this statement said it all. One of the most important limitations is that it can only be used as a default for a column with the uniqueidentifier type, doing otherwise will result in the following error:
"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.".

It's a default so can we override the contents? Well it turns out you can but there is nothing sequential about that is there?

But why would you want a GUID to be sequential? Because it makes quite a good clustering key candidate. It's unique, static, relatively narrow and sequential. All these factors make it a nice alternative to an identity for some tables. The good news is that it helped us going from a 2 seconds process to a 500 msecs process and all we had to do is change the newid() default to newsequentialid() and of course change the fillfactor since it would be a waste to keep it low with a sequential key. Should you run off and change all your defaults? No, because as always it all depends!

Here is a little test script that proves a few of these points:


SET NOCOUNT ON
GO
CREATE TABLE myGuidTest
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO
CREATE TABLE myGuidTest2
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO
--Let's see if it really is "greater than any GUID previously generated by this function on a specified computer"
INSERT INTO myGuidTest VALUES (default)
GO 5
INSERT INTO myGuidTest2 VALUES (default)
GO 5
INSERT INTO myGuidTest VALUES (default)
GO 5
SELECT * FROM myGuidTest ORDER BY 1
GO
SELECT * FROM myGuidTest2 ORDER BY 1
GO
--Can we insert our own GUID?
DECLARE @newID uniqueidentifier
SET @newID = NewID()
SELECT @newID
INSERT INTO myGuidTest VALUES (@newID)
GO
INSERT INTO myGuidTest VALUES (default)
GO 5
SELECT * FROM myGuidTest ORDER BY 1
--Cleanup
DROP TABLE myGuidTest
DROP TABLE myGuidTest2

Update statistics before or after my index rebuild?

I already posted some information about this some time ago and now I finally have some confirmation from a real guru about this often asked question.


Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?

A4) No! An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So - not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manualy update stats only does a sampled scan


So to wrap it up, a reindex updates the statistics with a full scan because he has to read through the entire index anyway. It does however not update non-index statistics so therefore you might want to trigger an update anyway. The nice thing about SQL Server 2005 is that he will only update the statistics if it is necessary if you use sp_updatestats.

A little test script to demonstrate some of these points.

SQL Server 2005 SP2 Cumulative Hotfix Package 4 - Released

It is out there! We are at the nice round build 3200.
Same as with CU3 there is also no download available.

http://support.microsoft.com/default.aspx/kb/941450

SQL Server 2005: Migration aftermath

I guess we can safely say that the migration was a success. It was quite a flat migration since we needed to guarantee a "backout" to SQL Server 2000, the only changes that were done is the replacement of SCSIPort with StorPort drivers (this was the only server left to migrate) and we decided to change the physical implementation of the database.

A couple of preliminary results since replication is still running and it obviously has impact on performance:

Long running transactions:
Before: 70.000/day > 100 msec - 1.000/day > 1000 msec
After: 14.000/day > 100 msec - 650/day > 1000 msec

Long running CUD transactions:
Before: 40.000/day > 100 msec - 250/day > 1000 msec
After: 4.000/day > 100 msec - 100/day > 1000 msec

Avg Disk Secs/Read:
Before: 12 msec
After: 9 msec

Avg Disk Secs/Write:
Before: 15 msec
After: 9 msec

Job duration dropped with over 50%.

Now it is time to start implementing new features like partitioning, service broker, vardecimal and hopefully many more. We have a lot of ideas floating around in our heads but unfortunately there are a couple of other priorities to take care of first Since we were no longer happy with our existing reindexing job we did decide to rewrite it from scratch and this gave us the opportunity to implement online reindexing.

I would like to thank all of my colleagues and the people from Microsoft who made this migration possible. I also apologize to all the colleagues I had to send away from my desk during the migration preparation ;-)

SQL Server 2005: Migration imminent and something about TF4618

So we are back at the point of no return. This Sunday we will be migrating our largest SQL Server 2000 to SQL Server 2005. During the first week we will use transactional replication to ensure a 'backout' in case of failure. Last year when we attempted to migrate this server we unfortunately hit the TokenAndPermUserStore issue and had to rollback the migration.

As explained in this post we created some tests script which were able to degrade the performance significantly. There were a couple of hotfixes for issues concerning the TokenAndPermUserStore cache with the latest being build 3179. We eventually did our tests on build 3186 which is the version we will be going to production with, unfortunately we were still able to degrade the performance with our scripts.

We looked at a couple of alternatives to prevent this decrease from happening and as far as we know there are 3 ways to prevent the TokenAndPermUserStore cache from growing too large and these are:

• add the user(s) that access the database to the sysadmin role
• create a scheduled job that issues DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
• use TF4618
  

After we ran our tests with trace flag 4618 we found that the TokenAndPermUserStore cache remained very small (less than 1MB) and our response times were very stable. The catch with this trace flag is that it has to be enabled by adding -T4618 to the SQL Server startup parameters (using DBCC TRACEON does not help in this case). What this trace flag does is evict old entries when new entries get inserted to keep the cache small. Because this extra work might cause an increase in CPU usage be sure to carefully monitor this. We did not see any significant increase in our stress tests.

We also decided to take 1 more "risk" and that is a reorganization of our database file layout. A legacy layout because of the previous SAN was 4 files for data and 4 files for the non-clustered indexes. Since we noticed a great difference in response times of the luns between the 2 filegroups we now use 1 filegroup spread over the 8 luns, this resulted in a great balance over all luns and improved response times.

After many hours of reorganizing, stress testing, replication testing and going mad we are finally there. We had days of paranoia and days of euphoria and we ended with euphoria, hopefully it will remain this way. Monday will tell us if everything was as well as we suspected so I suppose the only thing that remains is to wish us luck :-)

SQL Server 2005 SP2 Cumulative Hotfix Package 3

Keep an eye out for KB939537 which will be the next cumulative hotfix package for SQL Server 2005 SP2. Unfortunately the fix itself is not yet available for download.

Any guesses on the build number? I'm going for 3193 :-)

*EDIT*
It is build 3186 so I was pretty close but too pessimistic ;-)

TokenAndPermUserStore continued

Dirk G. from MCS Belgium informed me about another hotfix for the TokenAndPermUserStore issue. Build 3179 contains another fix for this cache (the latest build is 3182 by the way). The official description is "The TokenAndPermUserStore cache store may continue to grow steadily and decrease performance".

Check out the hotfixes here. Unfortunately they are on-demand so you will have to contact Microsoft to obtain this build.

Run batch multiple times

Kalen posted an awesome tip that is so simple and yet so cool I just had to refer to it.

Even more USERSTORE_TOKENPERM

Reading this and this post reminded me of our very bad experience with the USERSTORE_TOKENPERM cache.

When we ran into our USERSTORE_TOKENPERM issue last year there was no real awareness of the problem yet (SQL Server 2005 x64 - Enterprise Edition Build 2153). It was so bad we had to revert to SQL Server 2000 because things got so slow that SQL Server started to become unresponsive. After we got everything back to the original server with SQL Server 2000 we had a couple of weeks of detective work in front of us. Having an issue with this much impact needs to be investigated and the root cause must be found before you can even think about migrating again. We had done all the steps needed before migrating like stress testing, functional jobs, system jobs, ... and yet we did not see the behavior it was showing in production.

We have spent quite some time on the phone with an Escalation Engineer from Microsoft trying to pinpoint the root cause of our problem. After we convinced the engineer that the root cause was not parallelism as he suggested we finally got some vital information that helped us understand what was happening. When he told us about the USERSTORE_TOKENPERM cache and its behavior we started running some tests and now knew what we were looking for. Sure enough the size of this cache just kept growing and the remove count certainly did not.

select
distinct cc.cache_address,
cc.name,
cc.type,
ch.clock_hand,
cc.single_pages_kb + cc.multi_pages_kb as total_kb, cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count, cc.entries_in_use_count, ch.removed_all_rounds_count, ch.removed_last_round_count, rounds_count
from sys.dm_os_memory_cache_counters cc join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address =
ch.cache_address)
where cc.type = 'USERSTORE_TOKENPERM'
order by total_kb desc

Since it was announced that this problem would be fixed in Service Pack 2 we decided to create some test scripts that really put stress on this cache. We tried to come as close as possible to our production situation at that time which was sp_executesql for the CUD operations (UpdateBatch). Putting pressure on this cache was quite easy, have different query text so they get cached as different query plans and execute them twice since the first time it just gets inserted in the cache and the problem comes with the lookup action. We forced this behavior by executing queries with varchar parameters that increase in length every iteration. Also remember to use a login that is not a sysadmin since this causes SQL Server to skip the TokenAndPermUserStore check. We ended up creating a couple of scripts to do just this and also to monitor the cache size. The 'engine' behind it are simple command files and SQLCMD. Unfortunately I can not post the scripts since they are the property of the customer.

As far as we have seen from our tests the behavior has certainly improved in SP2. It takes a lot longer for the duration of the queries to increase and it does not increase to the point where SQL Server becomes unresponsive nor does the duration become unacceptably high. Since we are really pushing the cache to the limit it does still cause some increase in duration but not nearly to the point where SP1 gets it. We do notice however that under the constant 'hammering' of these queries even SP2 has the greatest trouble to keep the cache under control even when we call DBCC FREESYSTEMCACHE('TokenAndPermUserStore'). Obviously this scenario is not realistic since we really are trying to make it go wrong. Not only did the TokenAndPermUserStore behavior change in SP2 they cut down the size of the plan cache dramatically in SP2 too.

The conclusion however is that under Service Pack 2 we do not experience the same catastrophic behavior as before.

A lot of time has passed and we are going to migrate to SQL Server 2005 on the 9th of September. We are using a whole new .NET Framework on the application tier with a whole new data access layer, so a lot of the factors that existed that last time have now changed. We are starting our stress tests next month, if I get the chance and if we find anything peculiar I will let you know.

Hopefully this time there will not be any surprises :-(