Any help/advice would be greatly appreciated.
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
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.
answered Dec 14 '09 at 10:52 AM
Grant Fritchey ♦♦
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/
answered Dec 14 '09 at 12:07 AM
Steve Jones - Editor ♦♦