Performance and Health check


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.


Geert BettensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
Most of what you need to do can be done with Windows and SQL itself:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)

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 DeveloperCommented:
The link I provided gives excellent instructions for using SQL Profiler to get meaningful results.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.