6

I need to see which indexes I should add to my database so I need to run SQL Server Profiler so I can use it as input for the Database Engine Tuning Advisor.

My question is, does the profiler affect the performance of the DB? Also, would running the profiler on another machine help alleviate the weight on the DB?

Do you recommend doing this on a production server or should I go another route?

Jonas Stawski
  • 479
  • 2
  • 8
  • 18

3 Answers3

5

To find "missing indexes" you can use dmvs instead: One Two Search

Profiler is useful but does impose some load. You just have to be careful to see up the trace correctly, and don't start one on a server that already has performance problems...

Be careful about what you profile too. I have seen some idiot dump text query plans from profiler on a running server which promptly died trying to write 100s of MBs of trace files every second.

gbn
  • 6,079
  • 1
  • 18
  • 21
4

Yes it affects performance. The best indicator would be to monitor your performance on the server before running the profiler and the performance once it's running. Running it on another server will still affect performance. For instance, when I run SQL profiler from another server I see my network activity rise.

For the most part this is all very minuscule work compared to what a server can handle so you should be okay. If you have any concerns just do some performance monitoring and you'll probably see that it's not that bad.

Shane
  • 1,869
  • 4
  • 20
  • 34
-2

Running the profiler on a local machine is fine. Generally speaking, profiling will cause the server to perform additional I/O as it has to generate the profile information and move it over the wire. The additional CPU used is tiny.

There are no worries in running profiler in a production environment...

McGovernTheory
  • 113
  • 5
  • 1
    There are definate worries when running profiler against a production server. I know that if I run profiler against my production database server, the server will slow down, and the client application will start throwing errors because profiler will start eating up memory, quickly. – mrdenny Dec 30 '10 at 00:27
  • i would add "as long as you know what you are doing" after the "..."; it's very easy to consume enough network bandwidth with profile data if you're sending it over the wire, or eat up RAM to hold the trace data if you're running profiler on the server...seen it..had to clean it up.. – SqlACID Jan 01 '11 at 16:44