SQL SERVER – Monitoring SQL Server Never Got This Easy

In administering SQL Server databases, the DBA is often presented with the task of identifying resource bottlenecks on the system and the sources of those bottlenecks. Also, the DBA is challenged with the task of identifying why specific queries take significant time to execute or affect the performance of other queries on the system. This allows the DBA to find opportunities to tune queries and improve their performance and the overall performance impact on the system. In this blog we will explore identifying if any disk I/O bottlenecks are present that impact query performance on the system. Such action would allow a DBA to further deal with the I/O bottleneck.

Download Diagnostic Manager if you want to run along with the example demonstrated in this blog post. .

Introducing Extended Events for IO monitoring

The applications users have worked with the System Administrator on slow-performing application issues. They suspect that slow disk I/O is impacting the performance of database queries. You may be asked to verify if this is the case and you can use SQL Server Extended Events to find if any queries are experiencing on disk I/O waits.

CREATE EVENT SESSION [XEvent_IO_Tracker] ON SERVER
ADD EVENT sqlos.wait_info(ACTION(sqlserver.session_id,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([wait_type],(68)) OR [package0].[equal_uint64]([wait_type],(66))
OR
[package0].[equal_uint64]([wait_type],(67)) OR [package0].[equal_uint64]([wait_type],(182)))
AND
[package0].[greater_than_uint64]([duration],(0)) AND [package0].[equal_uint64]([opcode],(1)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\XeventIObottleneck.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);
GO
ALTER EVENT SESSION [XEvent_IO_Tracker] ON SERVER STATE = START;
GO

Let’s verify that the session is actively running and capturing data by executing the following DMV query:

SELECT * FROM sys.dm_xe_sessions WHERE name = 'XEvent_IO_Tracker';

The next step is to induce an IO operation and capture using our XEvent. Open a New Query window and copy and paste the following T-SQL command.

DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks2012
GO
SELECT COUNT_BIG(*) FROM [Production].[ProductInventory] a
CROSS JOIN [Production].[ProductInventory] b
CROSS JOIN [Production].[ProductInventory] c
CROSS JOIN [Production].[ProductInventory] d
WHERE a.Quantity > 300

Once the query completes, go back to the other query window and execute the following command to stop the Extended Event session:

ALTER EVENT SESSION [XEvent_IO_Tracker] ON SERVER  STATE = STOP
GO

In Object Explorer, expand the plus next to XEvent_IO_Tracker and right-click on the package0.event_file and select View Target Data:

In the above example, I used the “View Live Data” feature of Extended Events to look at the data. We can see the type of lock and the actual query too.

This is a great start, but resource constraint inside SQL Server can be of various types and IO is just one of the resource.

Diagnostic Manager does more than the eye can see

There are a number of standard tools like PAL that can be used for monitoring SQL Server. I took a look at the new Diagnostics Manager from Idera to understand what it does. It has been like a Pandora box for monitoring point of view. Some of the things that struck my eye are:

Alerting

When it comes to active server monitoring, it is important to understand that things can go wrong. We are always dealing with limited resources CPU, Memory, IO, throughput, Network etc. As an intelligent DBA, it is critical for us to know when the CPU is peaking, when the backups happen, when the services stop abruptly, when logs files expand and more. I found most of these are already configured and readily and are available to extend using configuration and templates.

I sort of like the starter out-of-box templates readily available to get started with this tool.

Standard OOB Reporting

The next stand out I found was the ability which is available inside SQL Server Management Studio. A set of standard reports. It is surely beyond ~30+ reports neatly categorized under “Server”, “Virtualization” and “Activity” headings.

I will not get into each of these reports but things like forecast based on existing and historical data is something caught my eye and has been worth a mention. With limited disk capacity, it is critical to know when to increase the hardware configuration based on usage pattern is critical. With SQL Server 2014, it is critical to add the capability of how In-Memory tables sizing will work and how memory needs to be accounted. If these get added, then this can be a great asset for DBA’s running on the latest versions.

Rich Dashboard

I think this is the strength of this tool. It allows standard configured values for known standard resources and gives us the flexibility to create additional counters / values to monitor. This is like opening a can of worms for many. But I am pleasantly surprised that the standard templates are exhaustive enough to start with.

I wish these standard templates have some additional recommendations for monitoring some complex environments like SQL Server AlwaysOn using multiple AG’s health, Database Mirroring status, Cluster Resources health and basic activities like backup operations on databases. Though simple tasks like disk monitoring, PLE, Memory, CPU, Network, Waits etc are quite essential – the servers of today are doing way more work than the eye can meet.

Monitor with guidance

Who doesn’t require some help when it comes to monitoring workloads like SQL Server? I personally liked the concept of reference range values when working with SQL Server performance counters.

Instead of me trying to monitor, track and look at each performance counters, I would love to see how I can bring my own counters and ranges from tools like PAL in the future. The ability to import and if required to export can be of great help.

Some of the salient points that caught my eye and worth a mention in the new versions are:

  • Monitoring of both Physical and Virtual environments of SQL Server.
  • Ability to monitor query from specific application.
  • Ability to look at historic data to troubleshoot past issues on the server.
  • Track database growth so that we can make corrective planning on capacity on servers.
  • Can look at data on mobile devices like iPhone, Android, Blackberry or Tablet to view real-time and historical SQL Server performance data.
  • Management pack available for Microsoft’s System Center Operations Manager (SCOM).
  • TempDb monitoring, CPU, Memory, WaitStats and more.

You can download Diagnostic Manager from here.

Conclusion

This blog post is to show you that most of the SQL Server work that we do as DBA is identify a needle in a haystack. Trust me with tools like Idera’s Diagnostics Manager, we are overwhelmed with information that we need to know – yet there is more to explore, enhance and extend what the tool gives out of the box.  The best learning experience one can get is by knowing what all the tool does and trying to understand how some of these have been implemented ourselves.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Monitoring Your Server at a Glance – A Open Conversation with DBAs

A few days ago I was visiting a large Database Shop and I happened to meet quite a few DBAs there. After my session was over we all went to the company’s cafe for a quick coffee. While having a coffee in the relaxed environment, I shared details about my recent vacation with my family. After sharing a quick story of my vacation, I asked the DBAs around me about their own vacations. To my surprise many of the DBA’s have not taken a holiday for quite a some time. Upon asking why they have not taken holiday the answer was simple (and as expected) – due to the job.

Excuse for No Vacation

Here are a few reasons I received from them why they do not take days off from their job.

“I do not want to leave, I do not know the guy who will manage the server after me. He may not know where to look when things go wrong.”

“Forget about days off, almost every other night I have to wake up to fix something on my server, I do not remember when I slept 6 hours without waking up to look at the server.”

“I am new in the job and I have lots to learn. Honestly, when something goes wrong, I have no clue where to look so I take lots of time to figure things out.”

There were quite a few responses in the same line; I realized that DBAs do want to take a break from their work and enjoy time with their friends and family, however, due to some reasons (insecurity, possessiveness, inexperience or just pure love for the job) they are not able to take breaks.

A Key Question and Honest Answer

Here is the key question I asked them back -

What one thing you think you need in your job, to be able to take a much-needed vacation without any stress or worry?”

I got a very interesting answer to my question-

“We need a single screen for all of our systems, which can help us know what a exactly going on on our server at all times.”

A very interesting answer – what every DBA wants is a tool, feature, or a utility that can tell them what exactly is going on in their server every single time, consistently and accurately. I think this is a fair request. I am a DBA and Developer and I know there are challenges in our job. We are often so familiar with our application that it is not possible for other DBAs to know what is going on in our system. When something goes wrong, they all have to run to us as we have the solution from our experience. Sometime I am worried to leave my server alone as I think some developer will deploy a code that will make the server behave slowly or sluggishly.

Honestly, this is not a healthy environment. You need to have transparent system where you know what is going on in your server with a single glance and if you want more information, you should be able to get it by double clicking it. Sometimes there is no out-of-the-box solution for the product itself. We need to depend on third party solutions or experts who know what they are doing.

Here is what I do

As soon we were done with coffee, I immediately opened my laptop and displayed Spotlight on SQL Server Enterprise. When I was done displaying various features of the tool, I could see a ray of hope in the eyes of the DBAs. I could sense that they did not know about this product or they have not seen anything like this. Here is the home screen of the Spotlight on SQL Server Enterprise.

You can see in the above image there are many sections for various important monitoring areas of SQL Server.

Sessions: This section demonstrates the various details about your sessions like how many sessions are open, how many different computers are connected as well as how many active sessions there are. The one which attracted my attention was details about the response time. This shows the full round-trip response time for of a query representative of general workload.

SQL Processes: This section demonstrates how many total, system, and user processes are. We can also see blocked processes there as well. The best part is that we can right click on any area of this home screen and see further details. I right clicked on User Processes and selected Session Details.

It immediately demonstrated the display of various user sessions and the script which was running in the session.

SQL Memory: This section demonstrates a very vital information about memory – like how big is my buffer or how much of my cache is being used. I also love the section of procedure cache, because we do not often track something this important when we are talking about SQL and memory. Here is the image of my computer’s Cache Size by Object Type which displays various object types and size taken from my memory cache.

Disk Storage: This section talks about IO activities, keeping in mind database workload. We can see details about various data files, filegroups, database count, log files but my favorite section is MAX IO/Wait which is at the bottom of the section. We can see various Wait types and upon clicking details we can see Diagnose I/O Pressure as well.

There are plenty of things to discuss about this tool. There is animated movement of packets, batches, logical reads, checkpoints etc, it just gives us everything we want to know at a glance. If we want to see any further details we can just right click and see more details about that particular object or activity.

Here is the best part – if there is anything where we need to focus or get alerted, Spotlight on SQL Server Enterprise will turn that to red and will send email alerts.

Moreover, Spotlight on SQL Server Enterprise makes it even easier for DBAs to go on vacation… and still keep an eye on the servers! By using Spotlight Mobile which, by the way is FREE for Spotlight users,  you can monitor the health of your SQL Server environment via the Heatmap, view an alarms list, get alarm details and have the ability to snooze, or acknowledge alarms anywhere, anytime and on any device!

Well, I will stop here today, as this is what I demonstrated in the cafe to my DBA friends. After watching demos, they had promised me that they will download the trial and give this product a shot and see if this can earn them a vacation.

I would like to know your opinion about this project. You can download the fully functional 30-day free version and install it on your machine.

Reference: Pinal Dave (http://blog.sqlauthority.com)