Solved

SQL Server 2005 Profiler Trace

Posted on 2011-03-01
9
282 Views
Last Modified: 2012-05-11
Would running Profiler trace on our SQL 2005 Server affect either the SQL Server Service and the SQLAgent?

If not, could it have negative impact on the database performance in any other way?
0
Comment
Question by:YZlat
  • 4
  • 3
  • 2
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35010538
Running a profiler trace (either server side or client side via profiler) will effect performance.  That is why it is important to not do it more than necessary.
0
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 35010588
This is recommended that you should run the SQL Profiler client in a different host. The reason is that SQL Profiler client itself would eat some CPU and memory resources.

Definitely it has impact on overall SQL Server performance. If you have to run it for the same server for a long time, run a Server-side trace. For few hours, this is ok to use SQL Server Profiler client.

Below can be used for reference - (A clear detail about profiler by Brad)
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx


0
 
LVL 35

Author Comment

by:YZlat
ID: 35010678
How can I diagnoze what's causing high CPU usage without significantly affecting the server performance?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35010697
Profiler won't SIGNIFICANTLY affect performance.  Most estimates say 5-10%.  
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 35

Author Comment

by:YZlat
ID: 35010730
so if I run a profiler trace from my client computer, will it be a problem?

Also which events should I include in my trace to analyze CPU usage?
0
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 35010781
It will be better if you run from different host.

Please read the below article which is far better than our one-liner answers -
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p4.aspx

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 35010807
Server side traces give you the most inclusive picture.  It's not the profiler application itself that causes the overhead, it's the recording of the events.  Whether that is done from profiler running locally on the SQL server or remotely, won't change that (much).  The downside of using a local client app is that under high load you won't be able to capture 100% of events.  That makes it not as useful.  

I mostly capture SQL Batch Complete and RPC event complete but I have a set of complex queries which, once loaded into SQL Server, allow me to extract data samples from the profiler data.
0
 
LVL 35

Author Comment

by:YZlat
ID: 35010975
Brandon, I am concerned because on database server Processor time is at 100% and I am worried that something might happen if I run Profiler trace from my client machine.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35011426
is it sqlservr.exe using the CPU?
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
t-sql month question 8 43
When to use an Aggregate Function. 18 38
Merge Statement 3 9
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now