Just a short one again today, as an issue came up today which required some new indexes to resolve the problem, and I wanted to stick it up while it was fresh in my mind.
There are many great articles out there on SQL Profiler and Database Tuning Advisor, and Microsoft provide detailed overviews also on Profiler and Tuning Advisor.
What I want to talk about is the timing of when you run your trace dependent on what you’re trying to resolve.
General Performance Improvement
If you just want to analyse the environment in general for improvements, you will want to run your trace for as long a period as possible. This doesn’t mean indefinitely, this means a window where the system is running at full tilt so you can capture as many events as possible, while keeping the amount of trace data manageable for processing at a later date. You will want to run the Profiler with the “Tuning” template selected, and save your output either to a file, or to a table in a dedicated tuning database (all personal preference, and before you get into this, you’d be best to read the MSDN guide).
Specific performance problem
This is what I’m getting caught with at the moment. During apparently random periods during the day we would get timeouts on a service that otherwise worked fine. Upon looking at the stuck transactions the response time would increase from .7 seconds to 35 seconds. So having run a trace during a period when everything was working, I found no useful information with regards to new indexes. I then ran a trace and made sure it captured the bad data, and lo and behold it found a significant indexing problem (98% improvement on the query according to Tuning Advisor), and hey presto, problem solved. So when monitoring performance for a specific problem, make sure that you are grabbing data at a time when this problem is occurring, otherwise you will just be chasing your tail.
I can go more in depth on Tuning Advisor and Profiler at a later date, but in the meantime have a look at the Microsoft overviews and make sure you’ve got your timing right depending on the problem you want to solve, and don’t waste a day running traces at the wrong times and getting shouted at by your boss, the sys admins, the head of finance, the cleaner, and probably your better half later for ignoring her call at lunch…