1

In our production environment (SQL Server 2008 Enterprise), we've been having issues with CPU usage and network utilization. Based on the specs of the (multiple) machines we have and the general functionality of our software product, load should not be an issue.

As a developer, I figured the problem must lie elsewhere, probably in the queries that are being run. I thought it would be a good idea to attach the SQL Profiler to a production instance to figure out which queries (or even code) are the hotspots, and work on optimizing those areas first. (None of our hardware/network guys have done this before.)

I attached the profiler to one of our development instances for a couple hours, and determined we have a few problem areas:

  1. Queries being executed inside loops -- it was common to see the same few queries being executed tens of thousands of times.
  2. Queries that don't get cached -- I saw a few SELECT * queries show up; there were also quite a few that are non-parameterized. This is a large subset of #1, where a query would only vary by a single WHERE parameter.
  3. Long-running queries -- ones that are actually taxing the CPU; these may be inefficiently written, not utilizing indexes, etc. The hit count on these was much lower.

Based on the summary statistics I generated, the biggest problem queries are of type #1 and #2. However, I can't immediately take those results and start working because the development instance queries are essentially what the other devs are working on at the time, not what is going to be run most in production.

I've been reading that attaching the SQL Server Profiler to an instance is problematic because of overhead. For obvious reasons, attaching in a production environment should be as lightweight as possible.

What I need to know is this -- given the three different types of queries I need to spot, how do I set up the profiler to have a minimal impact on performance? Is there another tool that I could use to accomplish this?

Notes:

  • Our production environment has definite on- and off-peak times, so it may be possible to get a decent sample by capturing only 30-60 minutes of data during a peak time.
  • I'd like to log to a database -- after playing around with the profiler, that log format is the easiest to consume later on.
Improve this question

3 Answers 3

Reset to default
4

Instead of attaching the SQL PRofiler, use the sp_trace_xxx procedures. The reason why the SQL Profiler is not recommended in production is that it can cause the server to block on waiting for network communication with the Profiler to flush ('network' can mean local shared memory, the location is not relevant). The idea is that a single threaded GUI managed process won't be able to keep up with a full speed stream of events from a multi-threaded native highly optimized server. By using the Trace procedures instead you are taking the GUI/Profiler out of the equation and you let the server flush the events as fast as they can be written to disk on the server, which is always much faster than how the Profiler would be able to process them. If you add a decent filter on the events (eg. only trace RPC:Completed and Batch:Completed events with a duration higher than > 1 second or something similar) then you won't have any serious impact on the server.

The GUI Profiler is capable of saving a trace as a series of sp_trace calls for you, so you don't have to manually create the script.

Improve this answer
3
  • Okay, this is more what I'm looking for. Do you have any suggestion for capturing the queries involved in category #1 above? The ones I saw were generally short-running (< 50ms) but numbered in the thousands. Putting a high threshold on the wait/execution time won't capture that data.
    – Jon Seigel
    Sep 1, 2010 at 3:52
  • I suggest you use the sys.dm_Exec_query_stats view instead: msdn.microsoft.com/en-us/library/ms189741.aspx. It keeps track of aggregate usage stats like number of times run, total/avg execution time/cpu/io etc. You can quickly find the most offensive queries, eg. by a simple SELECT TOP(10) * FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC. Other ORDER fields will give you other criteria, like IO offenders, but elapsed time is a good all-around starts point. See the link to MSDN for how to obtain the query text from the sql_handle column.
    – Remus Rusanu
    Sep 2, 2010 at 1:40
  • Note that the SSMS performance dashboards will look at exactly the same DMVs to find the most offensive queries and it may be easier to use than raw querying the DMV.
    – Remus Rusanu
    Sep 2, 2010 at 1:43
0

Ideally, you could also have a staging or test environment, where you put code that you want to test, not develop, and then put a production-level load against it. In this case, you would first load a copy of production code and databases, start the profiler, and then let the users or testers use it like they would the production system.

Once you've done that, and optimized some things, you load that code back into your test environment, and test it again, to see if you find another bottleneck.

Now, if you can't do that, you should have an idea from running the profiler in your dev environment if it would be lightweight enough (or not) to try to run against production directly.

Improve this answer
3
  • Unfortunately, we have no way to simulate a production-level load on our system. Even if I could get everyone here to use the system at the same time (impossible), I don't think it would accurately simulate (even on a smaller scale) what code is covered in production with real clients.
    – Jon Seigel
    Aug 31, 2010 at 14:50
  • 1
    Well, in an ideal world with a big budget, you could get LoadRunner or something similar and simulate the behavior(s) of as many client connections as you can generate. As it stands, maybe you just try the profiler in production at a non-peak time, make sure that (from the user perspective) that the application doesn't grind to a halt, and gather some data there. Then, get closer to peak time with the profiler on, maybe with a new config. It's possible that the overhead, for you, will be negligible.
    – mfinni
    Aug 31, 2010 at 16:35
  • Okay, good idea. I will do a trial run at an off-peak time and see what happens.
    – Jon Seigel
    Aug 31, 2010 at 20:45
0

Have the profiler run from a different server/work station.

Improve this answer
1
  • This answer recommends against running the profiler over a network connection.
    – Jon Seigel
    Sep 1, 2010 at 3:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.