x

What to look for in SQL Profiler

Any help/advice would be greatly appreciated.

SQL 2000

I am trying to monitor a server running a basic trace(Simply running profiler and starting a trace - using all basic/default properties).

Since I am relatively new to using SQL Profiler I am not sure what I am actually looking at.

I want to monitor a server as an application is running agaisnt the DB on the server to possibly find offending code(long running SQL, locks, deadlocks, etc.)

Baiscally the app runs slow at times and I have no idea why.

I hope this makes sense to someone out there.

P.S. I noticed there are different tenplates that can be used for a trace. SHould I be using a different template other than SQLProfilerStandard

more ▼

asked Dec 14, 2009 at 12:02 AM in Default

Chris gravatar image

Chris
38 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

First off, is this a production server? If so, you really need to stop running Profiler on the server. Set up a server side trace, a scripted trace, that will collect the data and output to files. The Profiler GUI can cause performance issues on your server all by itself.

The basic trace template will show you some of what you need to do performance tuning. The key point is to use a tool, a process, either of your own or a third party one, to clean the output, removing parameter values, and then input the data into a database table so that you can aggregate the data through queries (here's one from Microsoft). Once you can aggregate the data that's coming out of trace, you can decide what's important to you. Do you want to see the longest running queries? Select the MAX duration. Want to see the query that's called most frequently, Get the count of each query and sort by the count... the possibilities are endless.

What you're seeing is a query, in the raw data, with parameter values, and then basic metrics about the query, duration, start time, end time, cpu, disk I/O. You can use these to understand what each query is doing on the system.

more ▼

answered Dec 14, 2009 at 10:52 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left

The different trace templates look for different things. Does your application use T-SQL? stored procs? Are you worried about connections/disconnections?

Typically if you are trying to watch activity, I'd look for the T-SQL Batch Completed events.

Many of the other events duplicate things, like Batch Starting. That shows you the same SQL, although it gives you a start time so you can see how long the batch took. I prefer to keep it simple, look for long durations and see if they need tuning. I would only investiate deadlocks if you are having them. They'll appear in the log, but most applications I've worked with never create deadlocks.

We have a free ebook you can read as well: http://www.sqlservercentral.com/articles/books/65797/

more ▼

answered Dec 14, 2009 at 12:07 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x473
x34

asked: Dec 14, 2009 at 12:02 AM

Seen: 1886 times

Last Updated: Dec 14, 2009 at 12:07 AM