Performance Tuning - Professional SQL Server 2005 Performance Tuning - page 13

Knowledge
Another invaluable tool in performance tuning is knowledge, and there is no better source on internal
knowledge on SQL Server than the blogs of the people who work on the product themselves. Some of
the many useful blogs include:

SQL Server storage team blog: http://blogs.msdn.com/sqlserverstorageengine/
default.aspx

❑

Gert Draper: http://blogs.msdn.com/gertd/

❑

Euan Garden: http://blogs.msdn.com/euanga/default.aspx

❑

Slava Ok: http://blogs.msdn.com/slavao/

❑

SQL Engine Tips: http://blogs.msdn.com/sqltips/default.aspx

❑

Ian Jose: http://blogs.msdn.com/ianjo/

❑

❑ Wei Xaio: http://blogs.msdn.com/weix/

❑ SQL Manageability: http://blogs.msdn.com/sqlrem/default.aspx

❑ Query Opt team: http://blogs.msdn.com/queryoptteam/

❑ Craig Freedman: http://blogs.msdn.com/craigfr/

❑ SQL CAT Blog: http://blogs.msdn.com/sqlcat/

SQLBPwebsite: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/
default.mspx

❑

Preventative Measures
Routine monitoring of SQL Server systems will help show performance problems as they start to arise,
and not as they erupt into crisis. Addressing a small issue as it starts is often a lot easier than dealing
with an erupting volcano of an issue when it occurs unexpectedly.

Capturing baseline performance numbers and understanding workload characteristics are another
important part of monitoring system performance. A customer question might be about the value for
a specific performance counter. This might take the form of a question something like this:

''Our system is getting 50 transactions per second. Is that OK?''

Without any idea what the hardware is, what the database looks like, and how much work is there in
each transaction, the numbers are meaningless.

On the other hand, what if the customer had a performance baseline that showed that for a specific
workload they could achieve 75 transactions per second with 100 users at 50 percent CPU load with an
I/Othroughputof1.5MB/Secatalatencyof2msec? From that baseline they can now see that only
getting 50 transactions per second is only about two-thirds of what their system is capable of.

If they also noticed that those 50 transactions per second are consuming 95 percent CPU and the I/O
throughput is now at 4 MB/Sec with a latency of 25 msec, that's a good indication that there are some