Derrick Leggett Blog

Ramblings, grumblings, and other nonintelligible nonsense about SQL Server.

<b>SQL Server SP4 Rollout</b>

As we all know, SQL Server SP4 arrived on the scene late last week.  It’s been a LONG time since we’ve had a major service pack release for SQL Server 2000, so this service pack is very large.  If you look at the release notes, the service pack fix list includes 285 fixes for SQL Server and an additional 90 fixes for analysis services.  You can find the complete fix lists, along with release notes and the service pack downloads here:


Here are some snippets I noticed in the release notes and fixes that are interesting:


  • SQL Query Analyzer will permit connections to SQL Server 2005. However, some functionality may not be available.  That’s still really kewl!!!
  • FIX: Concurrency enhancements for the tempdb database
    • This particular issue caused us a LOT of issues at my current employer, so it’s good to see this as part of the fixes.
  • There are a lot of issues related to cursors.  Imagine that.  :)  Reading this fix list should give you several more reasons to avoid cursors when at all possible.  They SUCK!!! 
  • Profiler (we hope) will finally return the CPU counters correctly.  This has always been aggravating.  Hopefully, they also fixed the issues with functions not showing up correctly in Profiler when called from stored procedures.


As with any of the service packs they have released for SQL Server, thorough testing should be completed before rolling the service pack to a production system.  There have been service packs in the past that didn’t exactly go smooth, if any of you were around in the 7.0 and pre-7.0 days.  In addition, they usually catch some things during the first few weeks they missed or didn’t get quite right (SP3a?).  Here is the general release roadmap we are currently planning:


  1. Review the release notes and fix lists to determine what needs to be tested at SQL Server and applications levels before releasing service pack to any environment. 
    1. Review release notes and fix list.
    2. Monitor MS and SQL Server forums for issues.
    3. Make a formalized test plan from review.
  2. Release to development environment and test for two weeks.
    1. Have DBA teams and applications teams test and signoff on the test plan when complete. 
    2. Work through issues as encountered and document.
  3. Roll to QA and test for an additional week.
    1. Have QA team test and signoff.
    2. Work through issues as encountered and document.
  4. Roll to UAT, Release and Production.
    1. Complete final test of environment and major production applications after release.


When we complete the review and have the test plans created, I will add them to the blog.  These are not extensive test plans.  They are created just to insure we test all the major components and processes we believe might be affected after our review of the service pack documentation.


As one last item to cover on the service pack, if you haven't gone through the process of upgrading MDAC components throughout the environment, then you need to get a test plan together and get it done.  Old MDAC installations caused a host of issues with the latest SQL Server service pack release in our environment.  In addition, there have been a lot of important performance, stability, and security fixes throughout the MDAC release cycle.  At a minimum, all MDAC components should be at 2.7 SP1 Refresh even before the upgrade.  With the upgrade though, you should be up to the latest version.  You can find out more about this by reading the release notes for SQL Server SP4 and MDAC 2.8.  Here is the download site for all MDAC components: 



UPDATE #1:  (20050516)


Well, I'm glad we didn't install anything yet.  :)  We're still smoothing out the details of the test plan.  We got this little notification from Microsoft about SP4 not working very well with AWE enabled systems though:


I seem to remember saying something earlier in this post about being careful and service packs not going very well sometimes.  We'll keep watching this one and keep you posted.  Hopefully, nobody rushed to install it on their production systems with massive amounts of RAM.


Legacy Comments

Anatoly Lubarsky
re: <b>SQL Server SP4 Rollout</b>
You can connect to MSSQL 2005 via query analyzer on pre-sp4 also.

Sunil R. Warrier
re: <b>SQL Server SP4 Rollout</b>
I Installed it in one of the production system. The small test on test server didn't given any oparational issues. waiting in the production ... :)

re: <b>SQL Server SP4 Rollout</b>
We've notice some odd behavior in an AWE server (8 gig total memory, 6 for SQL).

We had the recovery interval off of the default of zero - don't ask why.. someone was debugging a while back and set it to five. This should have caused checkpoints to happen at a max of 5 minutes.

They actually were firing every 39 minutes, then taking almost 5 minutes to complete.

Setting the recovery interval value to zero caused the checkpoint interval to drop to about 5 minutes, with a 2 minute duration...

Still seems like checkpoints are taking way too long.


re: <b>SQL Server SP4 Rollout</b>
Rolled out sp4 and hit big performance issues on some reports, had queries taking > 10 min that used to run in a number of seconds. I had to re-write to not use temp tables for one report and use the query option (force order). queries are now running in about 1 min.

It seems the query optimizer has changed some how.