June 2, 2008 at 2:39 pm
I need to perform a test where I have to gather performance metrics like consumed memory, CPU, I/O for a process, particularly for a bulk insert.
What I am going to do is to query sysprocesses table, but since this table holds metrics only for currently executing processes and it cumulates these values for the duration of a process I will append query from sysprocesses at at the end of my bulk insert statement.
My question is there any better way to accomplish this? Are there any DMVs which holds these data even if my process disconnected already ?
Thanks
June 2, 2008 at 3:46 pm
I'd be interested as well. What people typically do is quiesce the system, make sure nothing else is happening, and then run your process.
Keep in mind that the server is very dynamic and what performance you can one time could be much different than the next time.
June 3, 2008 at 5:51 am
I've never tried doing it like that before. I generally just schedule perfmon to collect counters during the anticipated runtime of a process like that. Also collect Profiler data and then you can join the two together inside the Profiler gui to see which queries caused which CPU spike.
The other approach is to use some other tool that monitors the server like Operations Manager 2007, Quest Foglight or Idera's Diagnostic Manger.
Unfortunately, since you're monitoring the server, it's very difficult to say that 30% CPU went to this one process unless you know for a fact that is the only process running on the servrer at that time. If it's a production server being accessed 24/7 then you can't guarantee that.
If someone has something better, I'm all ears.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 9:29 am
I would use Profiler to gather the relevant information.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply