SQL Server Feature Changes You Might Not Notice

It would be nice if the SQL Server team would keep a log of all the changes it makes to each version of SQL Server. Yes, I know each new SQL Server release has a list of what’s new and each service pack includes a ReadMe file, but the focus is mainly on the major new features, not subtle changes to existing features. There are several types of changes made to SQL Server features that you might not notice, even if you regularly use the feature.

Related: Subtle Changes You Might Have Missed

Some of the changes include new options being added to existing features. One example of a feature that offers new options in SQL Server 2005 is the SET option called DEADLOCK PRIORITY. Prior to SQL Server 2005, you could set this value to LOW or NORMAL. I always thought of the LOW setting as the "martyr flag;" if your process was involved in a deadlock, it would be chosen as the victim and be rolled back. I discuss DEADLOCK PRIORITY in my SQL Server Internals and Tuning course, and I had already taught the course several times on SQL Server 2005 before I realized new options had been added to the DEADLOCK PRIORITY setting. In SQL Server 2005, you can now set DEADLOCK PRIORITY to any integer from -10 to 10 or to LOW (equivalent to -5), NORMAL (equivalent to 0), or HIGH (equivalent to 5).

Other changes involve the way a feature is managed internally, which many users might never know or even care about. One example of this type of change is trigger processing. In all SQL Server versions prior to SQL Server 2005, the special "inserted" and "deleted" tables available while processing a trigger were just views of the transaction log. In SQL Server 2005, with the addition of row-level versioning, the SQL Server engineers decided to use this new technology to support triggers. The inserted and deleted tables are now materialized in the version store. They are accessed in your code the same as before, but if you have a lot of triggers, you might notice additional stress on your tempdb database, which is where the row versions are kept. And without knowing the internal changes made to trigger processing, you might have no clue how to track down why the tempdb is being used so heavily.

Another type of change is a replacement to an existing feature. If you're comfortable with an existing feature, you might never realize there's a new way to do the same thing or, as in following example, a new way to get the same information. I use server-side traces a lot and programmatically manage them using metadata. SQL Server 2000 supplied a table-valued function called fn_trace_getinfo to return information about all my traces. It’s a bit awkward to interpret the results, but I was used to it. Again, it took me many months of using SQL Server 2005 to realize there was a new metadata view called sys.traces, which can provide you with the same (or more) information as the fn_trace_getinfo function. I never bothered to look for a new way to get this information because I already had a tool to do the job. I use sys.traces exclusively now that I know about it.

It’s even harder to find changes in OSs that affect SQL Server’s behavior. I researched a lot about memory management problems when Address Windowing Extensions (AWE) was first included in SQL Server. Yes, I know that when you use a 64-bit system, AWE isn't necessary. But another change snuck right by me. In Windows 2000, AWE isn't dynamic, meaning that with AWE enabled, SQL Server will lock memory up to your configured MAXIMUM when your SQL Server instance starts up. Memory won't be released until the instance is shut down. When I learned about this behavior, I just thought it was a feature of AWE, not of the OS. So I was surprised when a business partner with a SQL Server on Windows 2003 indicated that he was seeing allocated memory growing on an AWE-enabled SQL Server. Read about the different OS behaviors for AWE.

I’m not complaining about these changes. They're all changes for the better and make SQL Server more powerful and easy to use. However, for anybody who writes or teaches about SQL Server, or who might occasionally think of themselves as an expert, it can be a challenge to keep up with all the changes.