Filters to Use
-
Duration > 5000 milliseconds (5 seconds)
-
Don’t collect system events
-
Collect data by individual database ID, not all databases at once
-
Others, as appropriate
Filters are used to reduce the amount of data collected, and the more filters you use, the more data you can filter out that is not important. Generally, I use three filters, but others can be used, as appropriate to your situation. And of these, the most important is duration. I only want to collect information on those that have enough duration to be of importance to me, as we have already discussed.
Collecting the Data
Depending on the filters you used, and the amount of time you run Profiler to collect the data, and how busy your server is, you may collect a lot of rows of data. While you have several choices, I suggest you configure Profiler to save the data to a file on you local computer (not on the server you are Profiling), and not set a maximum file size. Instead, let the file grow as big as it needs to grow. You may want to watch the growth of this file, in case it gets out of hand. In most cases, if you have used appropriate filters, the size should stay manageable. I recommend using one large file because it is easier to identify long running queries if you do.
As mentioned before, collect your trace file during a typical production period, over a period of 3-4 hours or so. As the data is being collected, it will be sorted for you by duration, with the longest running queries appearing at the bottom of the Profiler window. It can be interesting to watch this window for awhile while you are collecting data. If you like, you can configure Profiler to automatically turn itself off at the appropriate time, or you can do this manually.
Once the time is up and the trace stopped, the Profiler trace is now stored in the memory of the local computer, and on disk. Now you are ready to identify those long running queries.
Analyzing the Data
Guess what, you have already identified all queries that ran during the trace collection that exceed your specified duration, whatever it was. So if you selected a duration of 5 seconds, you will only see those queries that took longer than five seconds to run. By definition, all the queries you have captured need to be tuned. “What! But over 500 queries were captured! That’s a lot of work!” It is not as bad as you think. In most cases, many of the queries you have captured are duplicate queries. In other words, you have probably captured the same query over and over again in your trace. So those 500 captured queries may only be 10, or 50, or even 100 distinct queries. On the other hand, there may be only a handful of queries captured (if you are lucky).
Whether you have just a handful, or a lot of slow running queries, you next job is to determine which are the most critical for you to analyze and tune first. This is where you need to set priorities, as you probably don’t have enough time to analyze them all.
To prioritize the long running queries, you will probably want to first focus on those that run the longest. But as you do this, keep in mind how often each query is run.
For example, if you know that a particular query is for a report that only runs once a month (and you happened to have captured it when it was running), and this query took 60 second to run, it probably is not as high as a priority to tune as a query that takes 10 seconds to run, but runs 10 times a minute. In other words, you need to balance the length of how long a query takes to run, to how often it runs. With this in mind, you need to identify and prioritize those queries that take the most physical SQL Server resources to run. Once you have done this, then you are ready to analyze and tune them.
Analyze Queries by Viewing Their Execution Plans
To analyze the queries that have captured and prioritized, you will need to move the code to Query Analyzer in order to view the execution plan, so that it can be analyzed. As the focus of this article is on auditing, not analysis, we won’t spend the time here to show you how to analyze specific queries. This is a very large subject unto itself, and is covered in other parts of this website.
How you move the code to Query Analyzer for analysis depends on the code. If the code you have captured is Transact-SQL, you can cut and paste it directly into Query Analyzer for analysis. But if the code you have captured is within a stored procedure, you have to do a little more work, because the Profiler does not show what the code in the Stored Procedure looks like, but only shows the name of the stored procedure, along with any parameters that were passed along to it. In this case, to analyze the query in Query Analyzer, you must go to the stored procedure in question, and cut and paste the code to Query Analyzer. Then, assuming there were parameters passed to it, you will have to manually modify the code from the stored procedure so that it will run with the parameters found when it was captured by Profiler.
Now the time-consuming chore begins, and that is the analysis of each query’s execution plan to see if there is any way the query can be tuned for better performance. But because you have now identified and prioritized these problematic queries, your time will be much more efficiently spent.
Read the Next Article in this Series
Return to Previous Article in the Series
]]>