SP3: To Install or Not to Install?

Microsoft released SQL Server 2000 Service Pack 3 (SP3) on January 17, raising the inevitable question, "To install or not to install?" SQL Server Product Support Services (PSS) recommends applying the latest service pack even if you're not aware of a specific fix that will help you. I'm aggressive about applying service packs because I hate to spend hours tracking down a problem only to find that the last service pack fixed it. However, some DBAs prefer to wait a few weeks and monitor the newsgroups to be sure that installing a new service pack won't create a problem. And some SQL Server shops that have high uptime requirements for their production systems might have to wait months before applying a service pack. A recent newsgroup posting said that one company took 8 months to test and deploy SP2 on one of its critical systems.

The "don't fix it if it ain't broke" school of thought is prudent when it comes to server maintenance. SQL Server service packs are especially tricky because you can't easily remove them. To remove SP3 (like SP1 and SP2), you have to uninstall SQL Server, then reinstall from the ground up. Needless to say, you should have a well-crafted recovery plan—including tested backups—before you apply a service pack to a production server. Like doctors, good system administrators should live by the creed "first do no harm" when making decisions that can affect the stability or availability of the systems they manage.

However, once you've installed SP3, you can take advantage of the upgrades it provides. Service packs are supposed to fix bugs, but SQL Server service packs also regularly add new features; SP3 is no exception. The readme.txt file's section 5.1, Database and Desktop Engine Enhancement, provides a complete and cumulative list of all the features that SQL Server 2000 service packs have added. One new SP3 function, fn_get_sql(), is particularly interesting. This function is similar to the existing DBCC INPUTBUFFER command. Both functionalities let you see the SQL statement that's being executed by a given SPID, but fn_get_sql() is more powerful. DBCC INPUTBUFFER shows only the first 255 characters of a SQL statement and shows only the SQL statement at the top of a nesting hierarchy. Fn_get_sql() lets you see the entire SQL statement that's being executed, even if it's nested within multiple layers of procedure calls. For example, if a procedure named Proc1 calls Proc2, DBCC doesn't let you see when Proc2 is running, whereas fn_get_sql() shows when Proc2 is being called.

Note that SP3's main download page announces that the service pack contains a "new monitoring API" with which "database administrators (DBAs) or third-party tools can diagnose problem processes." That item refers to fn_get_sql(), which is a great function but hardly a monitoring API. This misnomer has already caused some confusion and started discussions on the SQL Server newsgroups, with people searching for documentation about a new API that doesn't exist.

Experienced and junior DBAs alike need to read the readme.txt files for each service pack. Read them end to end—you'll almost always find a nugget of information that's relevant to your environment. In the case of SP3, you also need to read the addendum to the readme file to get all the readme information. Think of it as readme.txt Part 2.

Even if you don't immediately plan to apply SP3 to any of your servers, visit the SP3 download site. With SP3, Microsoft has released an updated version of SQL Server Books Online (BOL) and updated samples for SQL Server. You can find all the relevant download files, fix lists, and readme documents at http://www.microsoft.com/sql/downloads/2000/sp3.asp .

One final note: Please review the latest security posting about the SQL Slammer worm at http://www.microsoft.com/security/slammer.asp and update your systems accordingly. This newest bulletin describes a patch that fixes a SQL Server problem that Microsoft originally found and fixed last summer. The QL Slammer worm couldn't have spread so quickly over the weekend if SQL Server administrators—including some at Microsoft, according to news reports—had kept up with the latest security hot fixes. (For coverage of the SQL Slammer worm, see the News and Views section below.) Over the past few weeks, I've discussed the importance of adhering to best practices, and this unfortunate worm attack underscores the importance of making sure the basics are covered. I'll revisit the best-practice discussion in an upcoming issue of SQL Server Magazine UPDATE. But PLEASE review the Microsoft security bulletin right now.