?
Solved

Performance and Health check

Posted on 2008-01-28
4
Medium Priority
?
2,430 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Geert Bettens
  • 2
4 Comments
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 1200 total points
ID: 20766012
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.
0
 
LVL 3

Expert Comment

by:RobbP
ID: 20766157
You can use SQL Profiler, thereafter chek the generated reports and details for a reakdown of performance.
0
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 800 total points
ID: 20766334
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.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20769080
The link I provided gives excellent instructions for using SQL Profiler to get meaningful results.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month5 days, 20 hours left to enroll

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question