How to do troubleshooting of 1000 sql queries everyday?

Question

What would be the best way to trouble shoot thousands of sql queries everyday? Trouble shooting might includes

1. finding the blocked queries,
2. improving performance of query,
3. Queries that are hogging maximum processing time.

Answer 1

Well, recording the query, type of trouble, and the number of occurrences will quickly tell where effort is best spent, with select troubletype,count(*) from database group by troubletype. Then maybe drilling down into the most proliferate queries causing the problem.

If queries are being generated ad hoc, then maybe providing some useful, debugged queries for users to select from. That way there are fewer mistakes made.

Answer 2

You could run SQL Profiler, but that's going to have an adverse affect on performance.

A better option would be to look at the performance counters for the server and try to discern a pattern.

Regardless, I'd start by taking a look at the MSDN documentation titled "Troubleshooting Performance Problems in SQL Server 2005"

Answer 3

1. Have a look at Analyzing Deadlocks with SQL Server Profiler

2. Optimizing SQL Server Query Performance, Improving SQL Server Performance, Index Covering Boosts SQL Server Query Performance

Ingeneral Query Performance enhancement is more of an art than a science. This will be a query to query based exercise, and could vary depending on many factors.

You should try to learn the basics of query performance improvements, so that you could easil spot a bottleneck in a query.