Monitoring

Prashanth Jayaram

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

January 17, 2018 by

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

Read more »
Prashanth Jayaram

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

Read more »
Jefferson Elias

How to monitor object space growth in SQL Server

August 16, 2017 by

Introduction

There are many situations in a DBA’s life that lead him or her to monitor space growth over time in SQL Server. In short, we often want to know which tables are growing the most and how much they grow.

For that reason, it’s preferable to develop a solution that will help us in that particular task and this is exactly what this article is about. The main components will include

Read more »
Ed Pollack

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by

Description

Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.

Read more »
Daniel Tikvicki

How to monitor total SQL Server indexes size

June 12, 2017 by

SQL Server indexes overview

Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.

Read more »
David Alcock

Monitoring SQL Server with Dynamic Management Objects – Requests

May 17, 2017 by

In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic Management Views. The article demonstrated how we can use two views, namely sys.dm_exec_sessions and sys.dm_exec_connections to view activity on our instance of SQL Server and we achieved this by utilising some of the functionality available to us within T-SQL like joins, aliases and functions, all of which helped us develop a query to return monitoring information to us.

Read more »
David Alcock

Monitoring SQL Server with Dynamic Management Objects – Sessions and connections

May 12, 2017 by

A fundamental task of Database Administrators is monitoring SQL Server performance. Whilst SQL Server does give us a user interface inside management studio that enables us to view current activity (in the rather aptly named Activity Monitor) this article is going to focus on querying Dynamic Management Objects with T-SQL to return various pieces of useful process information that we can use to monitor server usage.

Read more »

What is the SQL Server Virtual Log file and how to monitor it

April 26, 2017 by

Microsoft SQL Server has many important features when it comes to file structures. Generally speaking, DBAs often ignore file structures and growth configurations. More specifically, the SQL Server Log files are often left at default values and never touched, even when they begin to cause problems caused. These Log files are important and critical part of the database, especially when it comes to large systems.

Read more »

Monitoring SQL Server Deadlocks – the easy way

February 21, 2017 by

SQL Server is a very powerful tool and wherever I go, I see the tool being way much underutilized. Some people even don’t know about the features which are already in the SQL Server and they have not used it, like SQL Server Extended Events. Though, Extended Events is a way more complex and detailed topic which I cannot cover in just one article I will discuss one of its very good utilities out of the countless.

Read more »

How to install the SQL Server Utility Control Point and configure multiple instances for monitoring

January 16, 2017 by

As we progress in our career as a SQL Server Database Administrators we encounter situations where managing and monitoring a large number of SQL Servers is the daily job requirement. Monitoring SQL Server is a very vast and detailed area of study. Out of all the monitoring the very basis and critical monitoring in the Resource Utilization Monitoring for SQL Server.

Read more »
Ahmad Yaseen

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

Read more »
Eli Leiba

Construct a special multi-statement table function for checking SQL Server’s health

December 24, 2015 by

The Problem

Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report.

Read more »
Milena Petrovic

Troubleshooting SQL Server issues with sys.dm_os_performance_counters

March 13, 2014 by

sys.dm_os_performance_counters

sys.dm_os_performance_counters is a system Dynamic Management View (DMV) that returns one row for each SQL Server performance counter. It’s useful for obtaining information about current performance counter values. These counter values are also shown in Windows Performance Monitor, besides other operating system counters Read more »
Milena Petrovic

Windows Performance Monitor basics

March 12, 2014 by
Windows Performance Monitor is a tool shipped with Windows that can be used to monitor and examine how applications affect computer performance. It can measure real-time server performance and save the measured values into a log file that can be analyzed later. It enables real time system and SQL Server performance monitoring, tracking the performance impact of applications and services, setting threshold for each monitored metric, and generating an alert or performing a specific action when the defined threshold is exceeded
Read more »