Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 532

CREATE TABLE SQLROCKS (COL1 int, COL2 nvarchar(100))
ON PS_SQLROCKS (COL1)
CREATE CLUSTERED INDEX IX_SQLROCKS
ON SQLROCKS (COL1)
ON PS_SQLROCKS (COL1)
Tuning Using SQL Server Profiler
SQL Server 2005 Profiler is a great resource to use during preproduction to tune long-running queries,
fix locking and deadlocking issues, and identify potential bottlenecks related to I/O, memory, and pro-
cessors. Installed as part of the SQL Server2005 management tools, the profiler comes with a variety of
options and selections that can provide the user with highly detailed data as to what is actually happen-
ing on the database. SQL Server Profiler also provides the ability to replay server workloads. Although
this is a feature that may not be of much use when deploying an application for the first time, it is espe-
cially useful when changing hardware.
Tuning Using the Database Engine Tuning
Advisor
Another great tool to assist you in your tuning needs is the Database Engine Tuning Advisor. This tool
can be used as both a graphical interface as well as a command line application and can be used to tune
SQL Server databases by analyzing the performance of workloads executed against the databases. The
reports generated by the advisor help augment the information required to successfully tune a SQL
Server database.
Consequences of Incorrectly Sizing the
Production Environment
A couple of years ago we undertook a massive multi-million dollar project to deploy Siebel Enterprise
applications companywide. The implementation consisted of databases deployed on SQL Server. Our
planning was immaculate, all the necessary precautions had been taken, and we were sure that nothing
would go wrong on D-Day. We went live over a weekend, and on Monday morning all users started
accessing the new application. Everything was fine until about 10 a.m., and then some unfortunate events
started to unfold. The machine that served as the database server was under severe load, processor usage
was consistently over the danger mark, processor queues were very high, and we started to see memory
pressure as well. By 12 p.m. the server was offline and so was the newly deployed Siebel application.
So what was the problem? After careful analysis, it was determined that the database servers had not
been sized correctly and that we were facing what we called memory pressure . The servers needed more
memory and as luck would have it, we would have to order it from the vendor who had a two-day
turnaround time. The consequences of incorrect sizing can be severe and could lead to hours or even
days of downtime for some of your most critical applications. Hence, the importance of load testing or
stress testing as discussed earlier.
So what can you do post production? Options are limited. If memory is the problem and if there are free
DIMM slots, it could be easy to add capacity. If I/O is the problem, then the issue could be as complex