profiler

Can profiler cause performance issues? profiler was ran to capture currently happening blocks!

Thanks

V1rt

I added those found at this link;<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;q224453>http://support.microsoft.com/default.aspx?scid=kb;en-us;q224453</a><br /><br />I added RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting, SQL:BatchStarting, SP: StmtStarting, Exception and RPC:Completed<br /><br />What do you think about it?<br /><br />

what if it only left 350mb of freespace in drive c? it's because profiler creates a .tmp file. Will it affect performance of the system?

There are three overheads associated with SQL Server Profiler:

1. The overhead of the trace itself. This is not very high. If you run the Profiler from a machine other than the server (and have good bandwidth), you will still see an extremely small hit in performance from capturing the trace events.

2. The overhead of Profiler application. This is the heaviest hit generally. The Profiler applicaiton is not very efficient. In earlier verisons of SQL Server it was also susceptible to memory leaks. It's generally not recommended to run Profiler on a production server if you can avoid it.

3. The overhead of the trace files. Profiler creates trace files, then "streams" the file to the GUI, a database table, etc. This incurs extra writes to disk as the traces are processes. This of course involves memory and disk IO overhead.

I agree with Luis. I have a machine setup that does nothing but run profiler, capture perfmon files, and run reports against them. We see no visible impact on production servers by doing it this way. We have a good backplane and network connections though to support this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

I will follow all your advice. I have a question though on the READ column from my trace. Value that I am getting related to sp_cursor averaging 45. I also see some at 6000, 1074, 6578, and 827. What does this value mean?

I saw tons of cursor in profiler. I have read a lot about cursors and a lot of people are recommending not to use it. Can those thousands of continuous cursors that I saw is contributing to the bad performance of the application?

Thanks guys...

Absolutely. Cursors are one of the worst things you can use in SQL Server. The reads will vary by the what type of cursor and what the cursor is actually doing. You need to track down what has the cursor and see if you can change it to a set-based statement. If you can, you can make big improvements on your overall server performance.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

I am having a hard time deciphering the trace.

I have read athttp://www.winnetmag.com/Article/ArticleID/7046/7046.html that if I see tons of sp_cursorfetch, the application is doing server-side API cursor.

What should I look for in that trace? What should I do to easily understand that trace?

Any help will be greatly appreciated!

Looks like i'm about to find out the culprit! Although, I have the CPU graph shown at 90%

Thanks guys.....

I mean, what should I look for to find the culprit query?

is value of 1000 a big READ?

or probably, what value is considered acceptable? What type of UNIT is in the READ column?

I saw another one, READS=2097, DURATION=843 and WRITES=0. If this is low, what then can be contributing to the poor performance? I have verified our network, and response time durin those times were really good.

I don't see sp_execute but I see

exec sp_cursorfetch 180370068, 16, 11, 1
exec sp_cursorfetch 180388642, 16, 10, 1
exec sp_cursorfetch 180370068, 16, 13, 1
exec sp_cursorfetch 180388642, 16, 14, 1

I see thousands of this ...and yes, it is a 3rd party application
and also yes, we have the latest service pack

thanks Luis...

I found the culprit ..... <img src='/community/emoticons/emotion-2.gif' alt='' />, a query that was eating up cpu and was running for more than 1 hour

Hey guys,<br /><br />I dumped my profiler trace to a table in separate sql server. And I executed;<br /><br />SELECT Starttime,SPID,Duration,Reads,Writes,CPU, Textdata,ApplicationName,LoginName,hostname FROM mytrace WHERE Duration IN <br />(SELECT DISTINCT TOP 20 Duration <br />FROM mytrace ORDER BY Duration DESC) order by duration desc<br /><br />I was shocked with the result I saw. There were SPIDs that have been running for more than 1.5 hours(Duration columns). Unfortunately, the TextData column were NULL. ApplicationName were NULL.<br /><br />Below are the values from the READs column whose Textdata columns are NULL<br /><br />1. 776398<br />2. 3377970<br />3. 82857<br />4. 747537<br />5. 747572<br />6. 40997<br /><br />And the CPU values for those SPIDS are<br /><br />1. 7515<br />2. 50746<br />3. 5125<br />4. 54937<br />5. 60000<br />6. 1610<br /><br />Should I disregard this result? Or is it something that can be considered as the culprit?<br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization. What about the READs? What are those very high numbers? How should they be interpreted?<br /><br />Any help will be greatly appreciated....<br /><br />Thanks guys...<br /><br />Oh btw, I also have other rows that shows queries that have long duration such as 90000. I think, this is in milliseconds. So that'd be 90 seconds. That's not good. <img src='/community/emoticons/emotion-1.gif' alt='' />

GUI tool

And also, just want to let you know that I can see the corresponding user account being used by the client

make sure you ignore spids < 50 which are system spids

Cheers
Twan

but is the explanation regarding long duration from the webserver but doesn't have data in textdata column?

thanks...

Regarding your questions below:<br /><br />Should I disregard this result? <br />&gt; No. It really depends on what eventclass this duration and cpu is for.<br />&gt; For example, if this is for Logout (where textdata is NULL) then you should be OK. You &gt; can neglect it.<br />&gt; But if it some other intersting event, you should watch out for it.<br /><br />Or is it something that can be considered as the culprit?<br />&gt; See above comment<br /><br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization.<br />&gt; If you really want any meaningful data, you should correlate the Performance Monitor &gt; &gt; data you capture to thge profiler trace you capture at the same time and then start &gt; &gt; corelating it. So when you see 90% CPU usage in Perfmon, you drill into the trace file &gt; to see at that time what events took the most CPU and duration and toubleshot from &gt; &gt; &gt; that point of time.<br /><br />What about the READs? What are those very high numbers? How should they be interpreted?<br />&gt; READs is Page IOs. Each Read is an 8K data read into memory. See the first comment for &gt; this too.<br /><br /><br />Thanks,<br />-- Sri<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by v1rtu0s1ty</i><br /><br />Hey guys,<br /><br />I dumped my profiler trace to a table in separate sql server. And I executed;<br /><br />SELECT Starttime,SPID,Duration,Reads,Writes,CPU, Textdata,ApplicationName,LoginName,hostname FROM mytrace WHERE Duration IN <br />(SELECT DISTINCT TOP 20 Duration <br />FROM mytrace ORDER BY Duration DESC) order by duration desc<br /><br />I was shocked with the result I saw. There were SPIDs that have been running for more than 1.5 hours(Duration columns). Unfortunately, the TextData column were NULL. ApplicationName were NULL.<br /><br />Below are the values from the READs column whose Textdata columns are NULL<br /><br />1. 776398<br />2. 3377970<br />3. 82857<br />4. 747537<br />5. 747572<br />6. 40997<br /><br />And the CPU values for those SPIDS are<br /><br />1. 7515<br />2. 50746<br />3. 5125<br />4. 54937<br />5. 60000<br />6. 1610<br /><br />Should I disregard this result? Or is it something that can be considered as the culprit?<br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization. What about the READs? What are those very high numbers? How should they be interpreted?<br /><br />Any help will be greatly appreciated....<br /><br />Thanks guys...<br /><br />Oh btw, I also have other rows that shows queries that have long duration such as 90000. I think, this is in milliseconds. So that'd be 90 seconds. That's not good. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks,<br />-- Sri