Examining SQL Trace's Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Examining SQL Trace's Tables

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-10-13 : 02:27:29

Hi,

I have run a trace on my system for a 24 hour period in an attempt to find out what's slowing down my system at weird parts of the day. I have definately come across rogue queries that run extemely heavy at times.

The thing is they are running EXTREMELY slow when looking at the trace table, however run I run an individual trace on the same SPROC with same data passed, I'm finding it hard to reproduce the same results. Sometimes it's running fast, some times moderate.

Why would this be? Caching?

Here is an example

this data is in the trace table, this is the heaviest query found in the 24 hour period, and its the heaviest on reads by FAR. This is for a web app, and runs every 6 hours.

TEXTDATA / READS / WRITES / CPU
exec select_newest_cache 3561295 0 14375

However when I run the trace individually I get these results, which aren't even in the same ballpark as the original.

TEXT / Duration / CPU / READS / WRITES
SET NOCOUNT ON 0 0 0 0
IF @genderID > 2 0 0 0 0
SELECT TOP 1001.. 115 30 67 0

exec select_newest_cache @GenderID = 1 115 30 75 0

Thanks very much, knowing the answer to this should help me resolve my rogue query problems :)

Mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-13 : 14:55:26

Hopefully you are running SQL Profiler on a client machine rather than on the SQL Server so that you aren't impacting the trace results. You should probably even consider saving the trace data onto a completely different SQL Server as well. Could you confirm these?

Tara Kizer

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-10-13 : 18:18:12

Hi Tara,

Everything was done on the same server. I setup the trace on the live server, and logged it to a table on the live server. I then compared trace results by running queries with tracing on, on the live server.

Is this the wrong approach?

thanks very much!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-13 : 18:21:36

Yes it's the wrong approach. SQL Profiler impacts performance so you must run it on a different machine and should save the data to a different SQL Server.

Tara Kizer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-13 : 19:28:36

If another computer is not available, at least save the trace to a file instead of a table, preferably on a disk that is not used for your database or log files. You can always open the trace file in Profiler afterwards to examine it, and upload the results to a table if needed.

Check out graz's ClearTrace utility, it's really excellent and works with trace files directly:

http://www.cleardata.biz/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources