We help IT Professionals succeed at work.

Performance and Health check

Hello

I'm running a SQL 2005 Server enterprise edition SP2.
Can someone tell me what i can do to check the performance of the server/database.
It is running 1 database from an external company and the application is slow.

Are there any tools i can use ?
What are the steps to take when someone asks you to check the database or sql server.

Thanks,

Robin
Comment
Watch Question

Senior Software Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
Most of what you need to do can be done with Windows and SQL itself:
http://www.sql-server-performance.com/tips/performance_main.aspx

If the 3rd party application is poorly designed then generally all you can do is compensate for the bad design.  For example, we use DevTrack from TechExcel (we would never buy it again or recommend it) and it has a terrible SQL design.  In order to try to improve speed I used monitoring suggestions from that link.  I found that because of a poor schema design they run frequent MAX() or COUNT() functions which creates a high I/O load.  I compensated by adding my own indexes and reconfiguring my hardware.

However, had they used the rules of normalization and some clever lookup tables the application would be substantially faster!  Also because they don't use stored procedures transactions are broken into multiple round trips to the database which causes deadlocks when we have too many users.  Our servers are state of the art x64 servers yet we still suffer because of the bad architecture.

Commented:
You can use SQL Profiler, thereafter chek the generated reports and details for a reakdown of performance.
There are a number of tools out there that provide monitoring/health check functionality (i.e. worst performing queries)
http://www.sqlpower.com/
http://www.idera.com/Products/SQLcheck/Default.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Note the last link is for a free performance dashboard from MS, if you are running SQL 2005 SP2. The others are £££/$$$.

SQL Profiler is a good place to go, leave it monitoring for a while (can log the output to a db table) and then look through the output, see which statements are worst performing.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
The link I provided gives excellent instructions for using SQL Profiler to get meaningful results.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.