January 11, 2008 at 2:07 pm
We used Profiler to trace the IO of execution of stored procedures and T-SQLs and record the result in a database table, but looking at the data I found the number of Writes for almost every event are very low, most of them 0, while the number of Reads look reasonable. Is this accurate or is there a setting somewhere which makes this happen? Does anyone know what's going on? Thanks!
Some eg:
Reads CPU Writes
1780421 19577 0
445115 2938 0
667580 4562 0
January 11, 2008 at 2:12 pm
We have had this issue when the profiler stops collecting accurate data. It looses the stats on some columns. What we used to do was to restart the server when we can. Thankfully we never lost it on our production servers..(Cross finger)
-Roy
January 12, 2008 at 11:54 am
Do the procs do any updates, inserts or deletes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2008 at 3:35 pm
Most of them don't, I guess that's probably the reason; but I saw t-sqls which do not do inserts, updates or deletes and they still have 35 Writes for eg.
January 16, 2008 at 3:51 pm
If your code doesn't write then profiler won't show writes - I'm a little confused as to exactly what you're asking - you can always lift the execution code from profiler and drop into a query window and run against a test system with stats io on. Or actually open the code and see what it does.
One point is that you will not see the same results comparing profiler and query window io stats so always work to one system or the other - I normally work with profiler for more complex queries.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 11:26 pm
Jenny Zhou (1/16/2008)
Most of them don't, I guess that's probably the reason; but I saw t-sqls which do not do inserts, updates or deletes and they still have 35 Writes for eg.
Temporary work tables or hash tables. Created in TempDB by the query processor as necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply