December 6, 2016 at 5:23 am
Could anybody suggest how to exclude MDW queries under query statistics ? Is there a way I could exclude certain user databases from gathering query statistics ?
December 6, 2016 at 7:37 am
Preethi S Raj (12/6/2016)
Could anybody suggest how to exclude MDW queries under query statistics ? Is there a way I could exclude certain user databases from gathering query statistics ?
Yeah - completely turn off and stop using MDW. Useless stuff that never should have been baked into the product. I have never come across a client using it, and in fact can't recall anyone actually using it.
Failing that, use database_id as a filter?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2016 at 7:56 am
Thanks Kevin for your reply. I read numerous articles about MDW, its merits and demerits. The reason I was evaluating this less popular feature is because
1) It is NOT a 3rd party tool
2) I would be interested in monitoring a single instance
3) The databases monitored are in the size of few GBs
Could you please help me understand your strong views about not using MDW? Any other suggestions? I have used SQL Sentry and SQL Nexus in my previous experience. However that is not an option here.
Regards..Preethi
December 6, 2016 at 8:13 am
Preethi S Raj (12/6/2016)
Thanks Kevin for your reply. I read numerous articles about MDW, its merits and demerits. The reason I was evaluating this less popular feature is because1) It is NOT a 3rd party tool
2) I would be interested in monitoring a single instance
3) The databases monitored are in the size of few GBs
Could you please help me understand your strong views about not using MDW? Any other suggestions? I have used SQL Sentry and SQL Nexus in my previous experience. However that is not an option here.
Regards..Preethi
Sorry, I am not going to waste my time explaining why no one uses MDW.
There are free monitors out there (Steve Steadman has one I believe, there are likely others). You can roll your own. You can use sp_whoisactive and Glenn Berry's SQL Server Diagnostic Scripts and find and fix lots of stuff. You can hire a performance consultant for a day or two to help.
IIRC SQL Nexus is free, yet you say it is not an option. Why? Note I don't use SQL Nexus (nor recommend it) either for a variety of reasons but I am wondering why your shop won't use a free option.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2016 at 10:30 am
The MDW itself puts too much load on a system. The overhead is not worth the benefits. That's the number one reason I'd recommend against it.
"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, 2016 at 11:09 am
Thanks all for your inputs.
Regards..Preethi
December 7, 2016 at 6:12 am
Looks like there is no way to exclude some of databases from report. But you can increase number of displayed queries in report by editing "snapshots.rpt_top_query_stats" stored procedure (change "SELECT TOP XX" statement in this procedure).
December 8, 2016 at 4:16 am
tazzman (12/7/2016)
Looks like there is no way to exclude some of databases from report. But you can increase number of displayed queries in report by editing "snapshots.rpt_top_query_stats" stored procedure (change "SELECT TOP XX" statement in this procedure).
I've found that you can query directly MDW:
exec snapshots.rpt_top_query_stats @instance_name=N'<servername>', @end_time='2016-12-07 15:45:00', @time_window_size=15, @order_by_criteria=N'CPU', @database_name=NULL
@time_window_size=15 - in minutes
@end_time='2016-12-07 15:45:00' - UTC time, and you may need to change time/date format
@order_by_criteria=N'CPU' - several options available: CPU, Duration, Total I\O, Physical Reads, Logical Writes
@database_name=NULL - seems doesn't work
December 8, 2016 at 4:23 am
Thanks for the info.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply