Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database activity Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 11:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:49 PM
Points: 65, Visits: 590
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.
Post #1511213
Posted Tuesday, November 5, 2013 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1511535
Posted Friday, December 6, 2013 4:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
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.
Post #1520505
Posted Friday, December 6, 2013 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1520539
Posted Friday, December 6, 2013 6:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
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?
Post #1520541
Posted Friday, December 6, 2013 6:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1520552
Posted Friday, December 6, 2013 6:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
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.....
Post #1520553
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse