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.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-07-11 : 09:22:35
|
| Does anybody have a trick way of capturing CPU cycles and I/O for a given dml statement and storing that in a table?<O> |
|
|
dsdeming
479 Posts |
Posted - 2002-07-11 : 13:30:45
|
| I haven't really used it, but couldn't you include something like this at the end of the proc?INSERT INTO Mytable( ProcedureName, CPU, DiskIO ) SELECT 'WhateverProcNameIs', cpu, physical_io FROM master..sysprocesses WHERE spid = @@spid |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-11 : 13:33:35
|
| well, that would give the stats for a given connection and I am look to isolate just stats for a given statement...see my thread here.<O> |
|
|
|
dsdeming
479 Posts |
Posted - 2002-07-11 : 13:42:56
|
| Short of performing the same insert multiple time within a proc, I'm stumped. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-11 : 13:48:32
|
quote:
Short of performing the same insert multiple time within a proc, I'm stumped.
I assume you mean do the same insert multiple times and capture the delta...well, that won't even work bacause you would be capturing the stats for the insert, plus the stats from the last select from sysprocesses...I am really trying to do a pretty simple thing. I want to know how much a set of dml 'costs' if I have a surrogate int pk vs having a natural varchar pk. I want to know at what point (natural key size / number of rows / load) does the performance hit (if it exists) of using a varchar as a natural key really matter...Anybody that can help me come up with a good way to capture this data can be a co-author on the SQL Team article I will write based on the results of my testing......I really like where I am going with the trace. It's exactly what I need; all except for the minor issue that it doesn't work.<O> |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2002-07-11 : 14:51:47
|
quote:
Does anybody have a trick way of capturing CPU cycles and I/O for a given dml statement and storing that in a table?<O>
Can't you just use the profiler? The TSQL SQL Statement completed event is what I would use. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-11 : 16:13:05
|
| As suggested, capture a profiler trace to a table and you can get your CPU,reads and writes from that.HTHJasper Smith |
|
|
|
|
|
|
|
|
Capturing CPU and I/O