SQL Server tuning tools for finding overload

Question

I use SQL Server as a DBMS for my very big corporate DB (with different financial data). And some times my system go down. I don't understand why. What programs/tools I can use for finding process/program/thread, that overload my SQL Server?

Thanks for all answers!

Answer 1

I'm afraid that the question is slightly too vague, what you might primarily need to get is not programs / tools but a deeper understanding of SQL Server, how it operates and what to expect / do in different situation.

Your indication is the server is being overloaded, but do you mean this at:

• a query level, and need to pull the most expensive queries from the querycache? (there are dozens of answers on how to do that.)
• The query plan cache is being flooded due to a lack of parameterization? (see simple vs forced parameterization for dynamic sql)
• The I/O subsystem is insufficient for the task requested. (perfmon is your friend)

There are dozens of things that can be affecting your performance, so this is really just not going to be answered without more specific information - or going on an advanced course about how SQL works etc.

Answer 2

There isn't really any tool that will magically pinpoint any problem. What there is, however, is a fairly successful SQL Server performance investigation methodology called Waits and Queues. Download the white paper from the link, read it, follow the described methodology and you should be able to locate the problem. Once you know the problem, you can come back and ask specific questions how to solve the problem you found.