SQL Server - SQL2005 Performance Tool

Asked By mwebb

03-Feb-10 01:24 PM

We are having performance issues with our software vendor concerning the
performance of an application that uses SQL2005. The vendor thinks the poor
performance is due to our servers/network we do not agree. We think it is
their database design.

What tools are available to measure the performance of an SQL 2005 database?
--

04-Feb-10 07:38 PM

mwebb (mwebb@) writes:

Database consultants. :-)

The prime tool I would use is to set up a server side trace that captures
the events RPC:Completed and SQL:BatchCompleted, and let it run for a
while, and then analyse the trace to see what queries takes the most
execution time, and then study these queries with regards to index and
other things that can affect performance.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

07-Feb-10 01:33 PM

I agree with Erland that SQL Trace is an invaluable tool to identify problem
queries.  I usually specify a Duration filter (e.g. 1000000 microseconds) to
identify the most problematic long-running queries and tackle those first
with query and index tuning.  There is also the case where you might have a
large number of short-running queries that use a lot of resources in
aggregate, due to execution and/or compilation costs.  An unfiltered trace
can help identify those.

Also, consider that poor application performance can be due to an issue on
the database server, application server or client.  Use Windows Performance
Monitor to measure utilization (CPU, disk, memory) on each.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

09-Feb-10 09:15 AM

http://www.idera.com/Products/Free-Tools/SQL-check/

This is more of a quick "vital signs" check than a full database workup.
Still, it is a good starting point, especially for a free product.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP