August 11, 2011 at 12:27 am
I need to find out all such queries( say, for eg. long running queries ) on a daily basis and report whenever such ( not so required queries ) are running on my server .
I would be great if i can automate the whole process to check it daily...
Thanks
August 11, 2011 at 2:28 am
One option could be to use MDW to collect query statistics related information on which you can report out.
DMVStats[/url] is another tool - I don't know if this works on SQL 2008 though.
Other options would be to collect this information from DMVs and persist it somewhere for reporting (which is more or less what the tools above do).
August 11, 2011 at 2:41 am
depending on how far you want to go with this (and what you truely want out of it) then it may be worth investing in some 3rd party software from like sqlsentry, redgate, quest, idera etc etc. All of them are great tools.
MDW and dmvstats are limited out the box and require additional work (again, depending what you want out of it) to truely get a top notch monitoring tool.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 11, 2011 at 6:08 am
While I'd prefer you run out & buy 50 or 60 copies of Red Gate SQL Monitor[/url] (my employer), you might be able to get a quick & easy report by getting the top 10 queries from sys.dm_exec_query_stats. You can combine that DMV with others to put together a pretty quick report. Now, it'll only show you what's in cache, so if some long running query aged out of cache before you run your report, you won't see it. That's where building a full scale monitoring solution (or buying a great one) comes in.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply