SQL Server Tuning

Q : I have SQL Server 2000 with approximately 45 databases on a single instance. These databases are used by multiple ASP applications running on my public Web site. SQL Server regularly reaches 100 percent CPU utilization and requires reboots. How should I start troubleshooting and repairing this problem?

A : Start with rebuilding all of your indexes. It’s easy, assuming you have memory configured correctly. Then run SQL Profiler for a few hours and capture a snapshot of your standard business T-SQL traffic. Run this trace through the Index Tuning Wizard (ITW) that comes built into SQL Server 2000. It will take a look at the queries and make recommendations for indexes to improve performance.

If you have SQL Server 2005 installed, you can use the Database Tuning Advisor (DTA) which is the successor to ITW, to tune your workload. DTA can tune SQL Server 2000 as well. It tunes a database on a production server by offloading most of the tuning load onto a test server. DTA uses the production server hardware configuration information, without actually copying the data from the production server to the test server. It only copies the metadata and necessary statistics.

Finding the Bottleneck

Q : When I run one particular query, CPU usage will reach 100 percent after a few seconds, and the server will be shut down in a few minutes. I can reproduce it every time. We have hundreds of millions of unique values in the table in question.

Is this a hardware issue or a software issue? I can’t get any information from Event Log. The server is an HP AMD 64-bit machine. I am using SQL Server 2005 (64-bit).

A : If the system really is crashing (bugchecking) then you will need to take a look in the memory.dmp file. First check the Startup and Recovery settings (in the Control Panel) and make sure it’s set to create at least a kernel memory dump. If that is already set, then find the memory.dmp file and save it to another machine. The next step is to download the Debugging Tools for Windows (windbg) from microsoft.com/whdc/devtools/debugging. Make sure to select the right package for your system (in this case you want the 64-bit versions), and then choose the x64 package.

When the debugger is installed, run windbg and set the symbol path to the Microsoft Symbol Server. To do this, type Ctrl+S and enter a valid symbol path. I usually use something like srv*c:\symcache*http://msdl.microsoft.com/download/symbols. For more information on this, see Debugging Tools and Symbols: Getting Started.

Next, open the memory.dmp file in your favorite Windows debugger and run !analyze -v. This will tell you what the bugcheck was and give more details on why the server crashed.

Log Shipping or Mirroring?

Q : Is it true that once mirroring becomes available, there is no reason to use log shipping anymore? It seems that mirroring is less functional than log shipping if you want to use the mirrored database for reporting. It also seems that mirroring only allows you to query against a database snapshot, which represents a point in time. Log shipping, on the other hand, appears to allow you to query against current data. I know the data can be out of date depending on how often you ship the logs, but this seems better than having to constantly create new snapshots. In other words, shipping the logs every hour seems a lot better than using mirroring and creating a new snapshot each hour.



A : If reporting is needed, log shipping is not necessarily better in this scenario than mirroring and using snapshots. The log shipping secondary must be restored with the WITH STANDBY option in order to allow read-only access. The data is static at that point in time up to the last transaction log loaded, just like a database snapshot represents a point in time. If you need more up-to-date data to query against with log shipping, the secondary database must have transaction logs applied, which means users cannot access the database while that is occurring. Log shipping is not really a reporting solution.

Database mirroring and using a snapshot on the mirror may be easier to administer since you are not affecting the process like you are with log shipping. You are just creating a new snapshot on a periodic basis. The problem, though, with mirroring and snapshots is that since each snapshot is named, either each new snapshot created would have a different name (which may impact the reporting application) or the old snapshot would need to be deleted and then created again (which would impact the availability of the reporting solution). So to some degree, both log shipping and mirroring have different advantages as reporting solutions in addition to their intended use for availability and disaster recovery. But with log shipping, reporting is unavailable when you apply a new transaction log to refresh data, and with database mirroring, reporting is unavailable when you drop and recreate the existing snapshot, or create an additional snapshot to make it more current.

Log shipping is based on backup and restore, and supports multiple secondaries, each of which can have its own schedule or delay, which can also account for other problems, such as user error. Database mirroring can be simpler to implement. When using its synchronous mode, the mirror database is more up-to-date (with no data loss) than its log shipping counterpart, which is only as good as the last log that has been applied to the secondary. With the right configuration, mirroring also supports an automatic failover scenario. Database mirroring, unlike log shipping, can only have one mirror database. Both database mirroring and log shipping provide protection at the database level, while failover clustering provides protection at the instance level. A geographically dispersed cluster could work over distances for availability purposes, but this can be an expensive solution. Database mirroring and log shipping can work with clustering to provide extremely high availability for your SQL Server™ instances.