October 18, 2018 at 1:46 pm
We have different SQL Servers versions and they are developed by different 3rd party. We receive complaints about performance and then we move to that server for checking the reason, usually they are due to poor performance as they grow and run as live they face such performance related issues. We have SQL Servers of version 2005, 2008, 2012 & 2016. We do not have any monitoring tool due to organizations security policies so we want to make it our own monitoring tool. I want to collect some data based on events and send email/sms or for example if a query takes longer than 10 second, if a deadlock occurs, then save such queries in database somewhere and then we show that detail report on a web page and further applying filters based on server IP version date time etc, like we see the monitoring tool on Management studio.
Performance related DMV are not available on 2005 & 2008, most of our servers are on 2008 and we are deciding to upgrade gradually but until they all are moved (may take 1 year), they are almost 70 different servers/applications. Let me know if there is any solution available/possible. Any dba using technique like this, please share suggestion / solution / idea. We do not want to connect database servers for monitoring, we want to collect such events from different servers and store information in a separate server to keep live servers busy for business purpose only.
October 18, 2018 at 2:51 pm
Look at the reports included in SSMS. Right click the server and choose Reports_>Standard Reports
October 18, 2018 at 2:51 pm
shamshad.ali - Thursday, October 18, 2018 1:46 PMWe have different SQL Servers versions and they are developed by different 3rd party. We receive complaints about performance and then we move to that server for checking the reason, usually they are due to poor performance as they grow and run as live they face such performance related issues. We have SQL Servers of version 2005, 2008, 2012 & 2016. We do not have any monitoring tool due to organizations security policies so we want to make it our own monitoring tool. I want to collect some data based on events and send email/sms or for example if a query takes longer than 10 second, if a deadlock occurs, then save such queries in database somewhere and then we show that detail report on a web page and further applying filters based on server IP version date time etc, like we see the monitoring tool on Management studio.
Performance related DMV are not available on 2005 & 2008, most of our servers are on 2008 and we are deciding to upgrade gradually but until they all are moved (may take 1 year), they are almost 70 different servers/applications. Let me know if there is any solution available/possible. Any dba using technique like this, please share suggestion / solution / idea. We do not want to connect database servers for monitoring, we want to collect such events from different servers and store information in a separate server to keep live servers busy for business purpose only.
There are DMVs/DMFs available in 2005 and 2008 many of which have been used for monitoring and/or performance tuning.
A free tool you may want to check is Steve Stedman's Health Monitor which works with older versions of SQL Server:
Database Health Monitor
You can get some other ideas, DMVs to use for monitoring 2005, 2008 from quite a few sources.
Glenn Berry Diagnostic Queries has versions for 2005 and 2008:
SQL Server Diagnostic Information Queries
Microsoft Downloads:
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx" id="if_insertedNode_1539895475714">Troubleshooting Performance Problems in SQL Server 2008
Performance Tuning Waits and Queues
You can also search for Joe Sack's PDF on Waits and Queues which is for 2005/2008 - don't have a link but the name of the article is: Performance Tuning with Wait Statistics
Sue
October 18, 2018 at 10:28 pm
For 2008, this works without change: https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server-part-1/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2018 at 6:29 am
Here is how can you monitor slow SQL running queries:
https://www.red-gate.com/simple-talk/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/
and here is how can you monitor your SQL Server database effectively:
https://www.red-gate.com/simple-talk/sql/database-administration/eight-steps-to-effective-sql-server-monitoring/
October 23, 2018 at 10:40 am
I also like to use the First Responder Kit from Brent Ozar. It's light weight and offers a wealth of information.
https://www.brentozar.com/first-aid/
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply