As professional production SQL Server DBAs, we have to deal with our SQL Server instances when the end-user reports that famous “slowness” with their queries. Even if the problem isn’t with the database server itself, the finger-pointing will be primarily against the database (pretty much like the Simpson’s meme where Bart is blamed for everything all the time, even if there are times when he didn’t do anything).
Whether it is an issue with the database server or not, you need to have a way to confirm if your SQL Server instance is working as expected or if it is indeed throwing sparks all over the place. Now, every SQL Server DBA has its way of figuring out if the SQL Server instance is healthy or not, but sometimes the process isn’t as straightforward, or maybe they don’t even have one (worst case scenario).
With that said, let’s look at what Monitor for SQL Server has to offer to see if we can incorporate it into our workflow.
Installation
You can get dbForge Monitor by clicking on the “Get For Free” button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.
Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.
After completing the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and right-click on it.
This is what you’ll be seeing:
After you hit the “Show Monitor” option, this is what you’ll see:
Important Note: if your dpi scaling is above 100%, some information will not be displayed properly because it will not fit the developer's intended. Let’s remember that SQL Server Management Studio doesn’t scale that well, but that’s something that happens with many applications; it is not exclusively to SQL Server Management Studio.
Within the view that you just saw, we can see a ton of general information, all below the “Overview” section, which displays the following:
● CPU utilization %
● Memory utilization (in GB)
● Disk Activity (in MB)
● Batch Requests/sec
● Compile/recompile per sec
● Waiting tasks
● Connections
● Deadlocks
● Transactions/sec
● Full scans/sec
● Range scans/sec
● Page reads/sec
● Page writes/sec
● Page faults/sec
● Page lookup/sec
● Page splits/sec
● Page Life Expectancy
● Read Latency (in ms)
● Write Latency (in ms)
● I/O Latency (in ms)
● Host Properties
● SQL Server properties
Let’s see what’s in the section labeled “Data IO”:
We get the total read/write amount of Megabytes and the read/write latency (in ms) per database data file (first the data files and then the transaction log files).
Let’s see what’s in the section labeled “Databases”:
We can see general information about all of our current databases.
Let’s see what’s in the section labeled “Wait Stats”:
We get information on the current wait stats, sorted from the highest one to the lowest one. Let me pause here to stress the importance of this information; when you’re having continuous problems with your SQL Server instance, the wait stats are one of those critical areas to keep an eye on.
Let’s see what’s in the section labeled “Top Queries”:
This section is also a very important one because it shows the top 10 executed queries in your instance, and you can sort that information by any of the columns presented there. Not only that, but you also get the execution plan for each query, and if you hover your mouse cursor on any of the plan operators, then you will get additional information. This, combined with the wait stats information, should be enough to call it a day, but let’s keep going to see what else we can find.
Let’s see what’s in the section labeled “Sessions”:
We get the list of sessions within our instance.
Let’s see what’s in the section labeled “Backups”:
We get information on our database backups. This section is another critical one because production SQL Server DBAs should be aware of the latest status of all the database backups within each SQL Server instance under his/her umbrella.
Final thoughts
● After going through all the information sections that the tool provides, I can conclude that it provides a ton of value to the production DBA that has to stay on top of their game, and SQL Server performance Monitor is an ideal tool to help him/her in the proactive and reactive situations.
● Devart provides more information about this tool in their official documentation center, so be sure to check that out.
● I’m going to be honest, I would’ve liked to see an option to export the information from each one of the sections. Something like that is very helpful when you have to report back to your manager or the business on any findings, right after your troubleshooting session; whether it is to report that everything is running smoothly like butter, or to pin-point any situation that has to be addressed. Regardless, I’m also aware that the Devart team is constantly enhancing their suite of SQL Tools, so I guess it will only be a matter of time before that feature (or any additional) sees the light of day.
Top comments (0)