November 4, 2013 at 11:07 am
Hello,
We have a SQL Server 2005 Enterprise Edition - 64 bit instance with 12 user databases. The activity monitor database drop down shows only 1 user database - thats not accurate. Of the other databases, one is offline. How do you go about determining the level of activity for the others. Would profiler be the next step?
Any comments / URLs would be appreciated - thanks.
November 5, 2013 at 9:27 am
I'd suggest not relying too much on those screens. Instead, if you're interested in monitoring the system, use a combination of Performance Monitor, Dynamic Management Objects and Extended Events. You'll get a much better and more accurate view of what is up with your systems.
"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
December 6, 2013 at 4:19 am
For this sort of thing I would recommend a server-side profiler trace. Don't use the Profiler itself if you intend to trace over an extended period.
December 6, 2013 at 6:12 am
kevaburg (12/6/2013)
For this sort of thing I would recommend a server-side profiler trace. Don't use the Profiler itself if you intend to trace over an extended period.
If you're on 2008 or better, I strongly recommend not using trace. Extended events are much less intrusive, filter better, collect better information more easily. The only problem with them is that they're just a little more difficult to consume since in 2008 & 2008R2 there is no GUI available.
"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
December 6, 2013 at 6:14 am
Grant Fritchey (12/6/2013)
kevaburg (12/6/2013)
For this sort of thing I would recommend a server-side profiler trace. Don't use the Profiler itself if you intend to trace over an extended period.If you're on 2008 or better, I strongly recommend not using trace. Extended events are much less intrusive, filter better, collect better information more easily. The only problem with them is that they're just a little more difficult to consume since in 2008 & 2008R2 there is no GUI available.
why the recommendation not to use them? Performance implications or because extended events are simply better?
December 6, 2013 at 6:22 am
kevaburg (12/6/2013)
Grant Fritchey (12/6/2013)
kevaburg (12/6/2013)
For this sort of thing I would recommend a server-side profiler trace. Don't use the Profiler itself if you intend to trace over an extended period.If you're on 2008 or better, I strongly recommend not using trace. Extended events are much less intrusive, filter better, collect better information more easily. The only problem with them is that they're just a little more difficult to consume since in 2008 & 2008R2 there is no GUI available.
why the recommendation not to use them? Performance implications or because extended events are simply better?
Both.
I have tons of evidence, but I'll give you just one.
When you filter a trace event, the event fires, the data is collected, it goes into the buffer, then a determination is made if that event meets the filter criteria. In short, it filters after collecting the data. With an extended event, the event fires, the filter checks to see if the criteria is met, if so, it collects the data, if not, the data is never collected. Just that one thing alone makes a huge difference in how those two different mechanisms interact with the system and there are lots more.
"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
December 6, 2013 at 6:24 am
Grant Fritchey (12/6/2013)
kevaburg (12/6/2013)
Grant Fritchey (12/6/2013)
kevaburg (12/6/2013)
For this sort of thing I would recommend a server-side profiler trace. Don't use the Profiler itself if you intend to trace over an extended period.If you're on 2008 or better, I strongly recommend not using trace. Extended events are much less intrusive, filter better, collect better information more easily. The only problem with them is that they're just a little more difficult to consume since in 2008 & 2008R2 there is no GUI available.
why the recommendation not to use them? Performance implications or because extended events are simply better?
Both.
I have tons of evidence, but I'll give you just one.
When you filter a trace event, the event fires, the data is collected, it goes into the buffer, then a determination is made if that event meets the filter criteria. In short, it filters after collecting the data. With an extended event, the event fires, the filter checks to see if the criteria is met, if so, it collects the data, if not, the data is never collected. Just that one thing alone makes a huge difference in how those two different mechanisms interact with the system and there are lots more.
Thank you! It makes sense if you think about it.....;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply