Monitoring

SQL Server Transaction Log Growth Monitoring and Management

April 1, 2019 by Ahmad Yaseen

In the previous articles of this series on the SQL Server Transaction Log, we discussed the importance of the SQL Server Transaction Log and the role that it plays in maintaining the database consistency, by ensuring that the committing transactions data will be preserved and the failed transaction will be rolled back. It also helps to recover the database to a specific point in time in case of system or hardware failure. This is achieved by writing a log record to the SQL transaction log file before writing the data pages to the physical data file, using Write-ahead Logging process.

SQL Server monitoring tools for memory performance

March 27, 2019 by Bojan Petrovic

Memory pressure slowing down queries

This article is the sequel in a series about SQL Server monitoring tools and common performance issues. The first article SQL Server monitoring tools for disk I/O performance is about how to detect and solve high input/output on hard disk subsystems when doing too much work during peak or maintenance times.

SQL Server trace flags guide; from -1 to 840

March 4, 2019 by Nikola Dimitrijevic

SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features

SQL Server monitoring tools for disk I/O performance

February 26, 2019 by Bojan Petrovic

The goal of this article is to get familiar with SQL Server monitoring tools and identify what some of the most common SQL Server performance problems are.

How to monitor internal data structures of SQL Server In-Memory database objects

February 25, 2019 by Prashanth Jayaram

This is the continuation of the previous article SQL Server In-Memory database internal memory structure monitoring. In this article we are going to dissect the details of other components that are available to validate the SQL Server In-Memory objects.

SQL Server In-Memory database internal memory structure monitoring

January 30, 2019 by Prashanth Jayaram

We can use a variety of available options to keep systems performing at their very best. The SQL Server In-Memory OLTP technological advancement is a great innovation to relieve tempdb contentions. This feature is available with an additional advantage to monitor systems without incurring stress on the database servers.

Tracking Times In Data Flows for Finding Performance Issues

January 9, 2019 by Timothy Smith

We’re facing a challenge with several of our data flows that use more time than they have in the past and we’re not sure when this trend started. We know in the past month, our reports have been delayed by over a day from the start to the finish. For some of our data flows we use SQL Server Agent that calls SSIS packages or procedures, while some of them use a custom data import and reporting application we’ve created. How can we track the length of time for these data flows, since we’re using a combination of tools for importing data?

Helpful Linux commands for SQL Server DBAs – iotop and iostat

December 13, 2018 by Rajendra Gupta

In the article, we will learn how to use the ‘iotop’ and ‘iostat’ commands with various configuration options.

Helpful Linux commands for SQL Server DBAs – top

December 11, 2018 by Rajendra Gupta

SQL Server 2017 and SQL Server 2019 supports both the Linux operating system. As part of performance troubleshooting or monitoring resources in Windows system, we need to check certain things like disk space, CPU or Memory consumption, check running processes, uptime etc. We can get the information, on a windows based SQL server, from the task manager.

How to collect performance and system information in SQL Server

August 16, 2018 by Bojan Petrovic

Introduction

In this article, we’re going through many of the tools we can use for monitoring SQL Server performance. SQL Server is chock-full of lots of good reports that allow a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports.

Dashboard Reports

Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports:

How to monitor and manage Transparent Data Encryption (TDE) in SQL Server

July 13, 2018 by Sreekanth Bandarla

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why

June 5, 2018 by Nikola Dimitrijevic

When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two

How to identify and monitor unused indexes in SQL Server

April 17, 2018 by Nikola Dimitrijevic

SQL Server indexes are essentially copies of the data that already exist in the table, ordered and filtered in different ways to improve the performance of executed queries. Seeks, scans and lookups operators are used to access SQL Server indexes.

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

January 17, 2018 by Prashanth Jayaram

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.

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

October 17, 2017 by Prashanth Jayaram

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.

How to monitor object space growth in SQL Server

August 16, 2017 by Jefferson Elias

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

How to monitor the SQL Server tempdb database

July 6, 2017 by Daniel Tikvicki

When it comes to the monitoring of SQL Server system databases, the tempdb database is one of the most important for consideration, since it holds most of the internally created objects.

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by Ed Pollack

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.

How to monitor total SQL Server indexes size

June 12, 2017 by Daniel Tikvicki

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.

What are SQL Server deadlocks and how to monitor them

May 19, 2017 by Jefferson Elias

Introduction

As a DBA, I’ve been, more than, confronted with performance problems. Sometimes, poorly written code or lack of indexes will generate blocking conditions that we refer to as a“Deadlock”. Due to their nature and like any similar blocking situation, deadlocks can have a direct impact on user experience and the more they occur, the bigger the impact.

Monitoring SQL Server with Dynamic Management Objects – Requests

May 17, 2017 by David Alcock

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.

Monitoring SQL Server with Dynamic Management Objects – Sessions and connections

May 12, 2017 by David Alcock

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.

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

April 26, 2017 by Musab Umair

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.

Illustration of an example availability monitoring service Using PowerShell and SQL

March 23, 2017 by Prashanth Jayaram

This article discusses a simple solution of how to monitor SQL service availability across multiple servers and reporting. To build this I’ll use SQL Server with simple PowerShell script and cmdlets which generate JSON data and displays results in HTML

Monitoring SQL Server Deadlocks – the easy way

February 21, 2017 by Musab Umair

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.