Browse DevX
Sign up for e-mail newsletters from DevX


Diagnose Server Performance Problems with SQL Server's SQL Profiler : Page 2

SQL Profiler is a great SQL Server tool that can help you reach your database optimization goals. This 10-Minute Solution presents a few preparatory steps to start using Profiler for monitoring performance problems. It discusses some techniques, tools, and commands that reveal information about server behavior.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The first condition for optimization is to establish a baseline definition of what performance is so you can measure the server under a typical load. The second condition is an understanding of index design, because the query optimizer's behavior is dependent on the structure of the available indexes and the way in which you send search arguments to it.

You can create your baseline by starting a trace from Profiler. You can use trace templates to start the investigation of your server. These templates have the appropriate events, data columns and filters already set for you, so you don't really need to know much about them when you start out. You need only to know what your goal is and then select the trace template that represents it. The templates are named for some very common tasks. Figure 1 shows the list from the file selection box in Profiler. Chances are that most of your needs will be met by the preconfigured traces provided in these templates. When you open one, you see a four-page dialog that gives you a little more control over the details of the trace. Of particular importance are the server you want to trace, the destination of the trace result, the format of the trace result, and setting some limits on the trace result.

Figure 1 | The list from the file selection box in Profiler

Select the Server You Want to Trace
From New on the File menu of your template, select the button with the ellipsis to get a list of SQL Servers on your network. You'll need to be a member of Administrators to run a trace on a remote server. While you're at it, you should take advantage of the feature in Profiler that limits the impact of a trace on the server being traced. Follow the prompts to designate the server to be traced, but then redirect the trace output to a different machine so you can relieve the target server of minding the trace output. To help you build your repository of trace data, create a separate database on your own workstation to collect the statistics so you can collect them in a centralized place and develop your history away from the server(s) you monitor. This helps to make you a good guest on the server being traced.

Along with redirecting the trace to a different machine, storing the resulting traces into a table is also helpful. Once they are there, you'll have the advantage of running select statements against the trace table so you can sum them-something you can't do if you save the trace results to a file. Designating a table instead of a file for the collection also enables you to summarize the results to keep track of your work. For example, sometimes I will create more than one table on the destination machine so I can make adjustments to the database object I'm watching on the source machine. This lets me incrementally measure the effects of the changes I've made and document the improvement (I hope) of those changes. A little code fragment collects the various results for me, where I can do the aggregations out of the summarized TraceResult table:

use dev SELECT * into traceresult from ( select * from [test trace1 where reads >200 union all select * from [test trace 2] where reads >200 union all select * from [test trace 3] where reads >200 union all select * from [test trace 4] where reads >200 )X

The Union All is required, because the test traces collect a text field and you are not allowed to do a Select Distinct on text, but Union All neatly sidesteps that issue.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date