Glenn Berry's SQL Server Performance

The 2008 version of SQL Server Management Studio (SSMS) has a new option that can save you a lot of pain.  In the 2005 version, you could bring up a table in the designer, and make just about any schema change you wanted, and then click the Save button on the toolbar. SSMS would automatically generate Transact-SQL code to implement the change as it saw fit and then run that code, which could be very costly on a large or busy table.

For example, SSMS might want to create a new table, copy all of the data from the existing table into the new table, then rename the old and new tables. This was known as “table-recreation”, which could be very expensive on a 100 million row table.

Now, in the 2008 version of SSMS, you can set an option that will prevent SSMS from doing this. This will save a lot of DBAs from themselves!

Sharon Bjeletich has a post on SQLCAT about a SQL Server 2005\2008 Performance and Scalability Troubleshooting flowchart. The first step in the troubleshooting effort is to gain some background knowledge by reading the articles below (which I have helpfully linked to).

Troubleshooting Performance Problems in SQL Server 2005 (this is the best of the bunch)

HOW TO: Troubleshoot Application Performance with SQL Server (this is a little old)

How to troubleshoot slow-running queries on SQL Server 7.0 or on later versions (this is a little older)

How to monitor blocking in SQL Server 2005 and in SQL Server 2000

INF: Analyzing and Avoiding Deadlocks in SQL Server

SQL Server technical bulletin - How to resolve a deadlock

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

That subject line in an e-mail from Microsoft on the first day of the quarter is what every Microsoft MVP hopes to see, relatively early in the day. Fortunately, mine arrived at 9:01AM, Mountain Time, so my repeated clicking on the Send/Receive button could cease…

That means that my MVP for SQL Server was renewed for 2008, which was a great relief to me. This is my second year getting the MVP award, so I don’t feel quite so junior anymore!

There is a new column in the sys.dm_os_sys_info dynamic management view in SQL Server 2008, called sqlserver_start_time, which shows the last time SQL Server was started. I have a couple of sample queries below that show some of the other useful information you can get from this DMV.

Knowing the number of CPUs, (logical and physical) can be very useful, so it is nice to be able to get that information from a DMV.

-- Get some hardware information from SQL Server 2008
SELECT cpu_count AS 'Logical CPU Count', hyperthread_ratio AS 'Hyperthread Ratio',
cpu_count/hyperthread_ratio As 'Physical CPU Count', 
physical_memory_in_bytes/1048576 AS 'Physical Memory (MB)'
, sqlserver_start_time
FROM sys.dm_os_sys_info


-- Get some hardware information from SQL Server 2005
SELECT cpu_count AS 'Logical CPU Count', hyperthread_ratio AS 'Hyperthread Ratio',
cpu_count/hyperthread_ratio As 'Physical CPU Count', 
physical_memory_in_bytes/1048576 AS 'Physical Memory (MB)'
FROM sys.dm_os_sys_info

SQL Server 2008 RC0 was released to the public a couple of weeks ago, and we have been using it on a couple of Production servers for about three weeks. So far, I am pretty happy with RC0, especially compared with the February CTP (CTP6) that we were previously using.

We have RC0 running iFTS on a dedicated, standalone server (a Dell PowerEdge 1950 with one, quad-core Xeon 5450, 16GB of RAM, connected to our 3PAR S400 SAN). The FT Catalog is on a table with about 70 million rows of data that is very volatile (about 80-100 inserts/second and 70-90 deletes/second).

We have been doing quite a bit of testing on this server and I have noticed the following improvements in RC0:

We can have Auto Change Tracking enabled, and iFTS has absolutely no problem keeping up with our rate of data change.

So far, I have not had to do a single Master merge, and my FT Index fragment count has stayed very low.

In general, the amount of maintenance required to keep iFTS happy (Master merges, relational index reorganizations, etc), has been reduced to almost nothing.

With CTP6, I had to disable Auto Change Tracking (and have an Agent Job run a command to apply the pending updates) every five minutes. I also had to run nearly constant Master merges to keep the FT Index fragment count under control. I also had to spend a lot of server effort keeping the relational index fragmentation under control, and I had to be very careful that relational index reorganization jobs and FT Index Master merges never ran concurrently. It was a lot of administrative effort in CTP6, but this is dramatically improved in RC0 of SQL Server 2008.

I am spending a few days with a group of veteran SQL Server DBAs on the beautiful campus of Ohio University in Athens, Ohio, which was founded in 1804. It really is a wonderful setting, next to the Hocking River, with lush vegetation and many lovingly maintained and restored old buildings, and lots of ground hogs! From what I can see, groundhogs (not woodchucks according to Ohio natives) are extremely common here, despite the denials of my NewsGator co-worker Nick Harris. He claims he never saw any groundhogs while he was a student here.

Walden Pond is where Henry David Thoreau lived for two years in the 1840's, in order to be able to contemplate life away from the hustle and bustle of civilization. I am certainly no Thoreau, but I am busy thinking about SQL Server 2008, away from my normal responsibilities at home. Unfortunately, I cannot get into exactly what I am doing here...

I have also gotten to spend some time talking with Satya Jayanty, who is a SQL Server MVP from Edinburgh, Scotland. I had met him briefly at the Microsoft MVP Summit in Seattle, back in April of 2008, but I have had more time to get to know him this week, and I am impressed with his attitude and knowledge about SQL Server 2005/2008. His blog is a good resource for SQL Server information.

Conor Cunningham (formerly of Microsoft, and now with SQLSkills) has a good post up about the performance disadvantages of DML triggers.  I completely agree with him here. I am happy to say that we have zero DML triggers in any of our Production databases at NewsGator.

When I first started at NewsGator nearly two and half years ago, one of the first things I said was that “Triggers Are Bad”, which I still get teased about to this day. Back in late 2003, at a previous job, I witnessed first hand the consequences of over-use of DML triggers in a large, n-tier client/server application.

We had application code that allowed the administrator(s) for the customer to add rules to audit data changes for legal and compliance reasons. This code ended up adding DML triggers for each audit rule. Only about 10 rules were actually legally required, but the customer administrators went a little wild, adding several hundred extra rules (and triggers), which ended up causing severe performance problems for SQL Server 2000.  This was quite the lesson that I will not soon forget.

Microsoft has released Cumulative Update 8 (CU8) for SQL Server 2005 SP2, which is a roll-up of all post SP2 fixes. It is Build 3257. This article explains the difference between the various types of patches that Microsoft issues for SQL Server 2005.

Microsoft has finally taken the wraps off of a completely revised feature called Activity Monitor in the RC0 version of SQL Server 2008, which has been available to the public since June 10, 2008. If you have used Vista or Windows Server 2008, this should seem pretty similar to the Windows Resource Monitor included in those products, which I like to call “Task Manager on Steroids”.  A screen shot of it is shown below.

The Overview section shows % Processor time, Waiting Tasks, Database I/O, and Batch Requests/sec. The other sections below the Overview can be opened up for more detail about Processes, Resource Waits, Data File I/O, and Recent Expensive Queries.

This is a great tool for getting a slightly deeper and more SQL Server specific view of what is going on with your SQL Server than what you can see with Task Manager or Windows Resource manager. Of course, this is no substitute for for a good set of DMV queries wielded by an knowledgeable DBA, but it is certainly useful for starting to drill into the old, familiar “my server is running slowly, and I don’t know why” problem.

Microsoft has posted quite a bit of new material about different new features in SQL Server 2008. There are lots of technically oriented PowerPoints, hands on lab documents and even VirtualPC Images that you can download.