This is the last part of the series, where I discuss about the monitoring application that I build. If you would like to know how it works, please see the previous post here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2016/09/30/say-hello-to-sql-live-health-check-part-1/) and here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2016/10/05/sql-live-health-check-part-2-historical-data-and-configuation/).
If you have already downloaded the application and tried it, I hope it does helps you in some way. You might also wondering what will be the minimum permission requirement for this to work so you can be more secure your environment, which one should be! This post will will go through that in detail, apart from me saying what permission is needed, I will actually show you the tables I used and why those permission is required.
Let's get started!
As you already seen from part 1 and part 2, the application mainly capture information as below:
page life expectancy, buffer hit ratio, db instance last restart, blocking info
Let's go through them one by one:
| Data captured: | Last Restart: |
| Table used: | master.dbo.sysprocesses |
| Permission required: | VIEW SERVER STATE |
| Description: | Before you ask, yes, I will be changing that to DMV in the next version, but for now, let keep this 🙂 It will query the first spid login time using this table, which means the time of when SQL instance starts, some use the startup of tempdb time, which will be roughly the same as well. |
| Data captured: | PLE, buffer hit ratio |
| Table used: | sys.dm_os_performance_counters |
| Permission required: | VIEW SERVER STATE |
| Description: | This is pretty self explanatory, just capture the data through performance counter. |
| Data captured: | Blocking |
| Table used: | sys.dm_tran_locks, sys.databases, sys.dm_os_waiting_tasks sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_sql_text |
| Permission required: | VIEW SERVER STATE, VIEW ANY DATABASE (default granted) |
| Description: | this capture the blocking info through joining a few dmv to get both the blocker and the requester detail information |
Unless you took the view any database permission out, all the login needs is just the view server state permission and nothing else. Easy right?
One last bit I wanted to get through is the DB that holds the data, this application does requires a database to keep all the data it collected. It has no requirement for the edition, but it has to be SQL 2008 or up. As the data will growth pretty fast if you add in more db instances for monitoring, utilizing page compression for the tables will help with the disk size. In the next version, I will add in some housekeeping to clean up the old data, but for now, you will have to manually purge them if required.
Enough about the setup or the background of the app, let's talks a bit about the benefit it can brings to the table.
Scenario 1:
| Issue: | User report the application is running slow, and suspect it might be a DB issue at that time (2 hours ago) |
| Old method: | Login to the server through SSMS, check if any blocking occurs, check PLE and check what resources that query is waiting. Most of the time you cannot find it as the blocking will be gone by then. |
| With SQL Live health check: | You might alert the issue before the user calls you, and you already can see if any blocking is occurring at the time. You also can see the PLE along with the trend. |
Scenario 2:
| Issue: | User reports that they cannot connect the DB at a given time (5 hours ago) |
| Old method: | Try to login to the DB via SSMS, check if the db instance got restarted at the given time |
| With SQL Live health check: | You can view back in time on the DB availability, you can also see when the last DB instance restarted very easily. |
There are many more scenarios that this little application can help you, feel free to download and use it. If you want any other features to be added, please leave them in the comment, and I will try to add them in to make all our lives easier.
Link for the download is here (https://sqlhealthcheck.codeplex.com/).
