I have been tasked to see whether SCOM 2012
is the right tool to do the following, which have been asked of me:
- Find out what are the longest running queries
- Find out the number of locks
Does anyone have any advice, or can anyone point me to a comprehensive guide in trying to get SCOM
to monitor the above?
Best Answer
SCOM
is extended using management packs. The Microsoft SQL Server management pack is documented here.If you look at the
Appendix - Reports
section you'll see that out-of-the-box reports includeSQL Server Lock Analysis
andTop 5 Deadlocked Databases
.The management pack does alert on long running SQL Agent jobs, but it does not monitor or report on long running queries. For that, you may need to look at some more SQL-specific analysis tools or use SQL scripts (such as this one: Find the TOP n Longest Running Procedures (or Queries), Ordered by Total Impact on Server)