Rajendra Gupta
SQL Server Services Info

A handy SQL Notebook for the purposes of troubleshooting in Azure Data Studio

November 5, 2019 by

This article prepares a handy SQL Notebook for DBAs. You can use this notebook to troubleshoot performance issues and for getting SQL Server instance details.

Introduction

SQL Notebook is an exciting and useful feature introduced in Azure Data Studio. In the article, SQL Notebooks introduction and overview, we took an overview of the notebook feature along with the introduction to markdown language.

Prerequisites and important points

  • Download and install Azure Data Studio using the link on a suitable platform such as Windows, macOS, and Linux. Current release – v1.2.2:

    Installation of Azure Data Studio

  • SQL Notebook is a technique for preparing human-readable scripts embedded document. You can also execute the t-SQL scripts, PowerShell, python codes inside the notebook
  • Understand the basics of SQL Notebook, different methods of launching it using the link mentioned above
  • This article combines the DBATools PowerShell command for preparing a troubleshooting guide in Azure Data Studio
  • This article uses open-source PowerShell module DBATools for SQL Notebook. You can download and install DBATools using the link. You can also follow the SQLShack article DBATools PowerShell Module for SQL Server for more information regarding DBATools
  • If you have already installed it, upgrade it to the latest version using the following command in an administrative console of Azure Data Studio:

  • This article uses the SQL Server Diagnostic Information Queries by Glenn Berry in the SQL Notebook. We will cover the useful queries in this article.

New-DbaDiagnosticAdsNotebook DBATools command

DBATools version 1.0 introduces a new command New-DbaDiagnosticAdsNotebook for preparing the notebook with the diagnostic queries.

It is always good to check the syntax, description, and examples of the DBATools command using the Get-help command:

New-DbaDiagnosticAdsNotebook

Syntax

  • SqlInstance: In this argument, specify the SQL instance name (default or named)
  • TargetVersion: By default, it installs the diagnostic queries for the SQL instance specified in the above argument. We can also specify a target SQL instance version. This version should be SQL Server 2005 and greater
  • SqlCredential: By default, it uses Windows authentication for connecting to SQL Server instance. We can specify alternate credentials using this argument
  • IncludeDatabaseSpecific: We can enable this argument for including database-specific queries in the notebook
  • EnableException: If there is an error, the command uses a mechanism using the try-catch block and displays a friendly message. We can disable this behavior, but we should avoid any specifying this switch in the command

In the Azure Data Studio, execute the following query in an integrated PowerShell terminal for the SQL Server 2017 version. It automatically fetches all the diagnostic queries and imports them in the notebook with the corresponding heading on each section. We do not need to specify or import queries manually in this notebook:

Installing SQL Notebook

In the above screenshot, we can see it creates the SQL NoteBook in the specified directory. The extension of the notebook is ipynb.

You can browse the destination directory and verify the notebook file:

browse the destination directory

Double-click on this file to open it in the Azure Data Studio. If the tool is already opened, you get the following warning message. You can close the data studio to skip this warning message:

Warning message

We can also open it from the File | Open file menu option:

Open File menu

Specify the file path, and it opens the newly created notebook, as shown in the following image:

Diagnostic notebook

Explore SQL Notebook diagnostic queries

Firstly, let’s attach this notebook to a SQL Instance connection so that we do not need to specify a connection for executing each query:

Attach a connection to the notebook

In the Attach To section, click on New Connection. Specify the SQL instance, authentication mode (Windows, SQL):

Sepcify connection details

Click on Connect, and it shows the SQL connection as shown below:

View SQL Server connection

Let’s explore queries specified in the diagnostic SQL Notebook.

Version Info

It returns the SQL instance and OS versions in this section. Click on the code, and you get a play icon for executing this query in the connection we specified earlier:

Version Info

SQL Notebook displays the result in the notebook itself. We do not require copying the code in another client tool and execute it. We can view, copy and clear the results if required:

Version Info

Click on eclipse, and it gives you the option to clear output, as shown below:

Clear output

Core Counts

It reads the SQL Server error logs using the sys.xp_readerrorlog and filter results for getting socket, physical and logical core counts:

Core Counts

Server Properties

This section queries the SQL instance using the system defined function SERVERPROPERTY() and gives us useful information such as build, collation, edition, version, physical hostname, cluster property, instance default data, log path, etc. You can refer to Microsoft docs for more information about this function:

Server Properties

Configuration Values

It gives you various configuration options along with their minimum, maximum, in use-value for server-wide options. You can refer to the following link for reference:

Configuration Values

Global Trace Flags

If you have enabled any trace flags globally, you can use the query in this section to get the details. In my instance, I do not have any such trace flag. Therefore, you do not get any output trace flag:

Global Trace Flags

Process Memory

You can query dynamic management view sys.dm_os_process_memory for getting details of SQL Server memory allocations. You can refer Microsoft docs to learn about this DMV:

Process Memory

SQL Server Services Info

We can use sys.dm_server_services Dynamic management view for getting status of SQL Server, SQL Agent, and full-text services. It also shows service startup type, last start timestamp, service account, instant file initialization, and clustered status:

SQL Server Services Info

Last Backup by Database

Backup information is a vital monitoring aspect for any DBA. We can use this section for fetching the following information about the last database backups. SQL Notebook query fetches the information from the system Database MSDB:

  • Database recovery model
  • Log use wait status
  • Last full, differential and log backup timestamp

Last Backup by Database

SQL Server Agent Jobs

This section queries the MSDB jobs for fetching SQL agent jobs information such as created date, owner, job status (enabled\disabled), and next run schedule:

SQL Server Agent Jobs

SQL Server Agent Alerts

If we have specified SQL Server agent alerts, we get the details in this section query. It queries the msdb.dbo.sysalerts system table for fetching the required details. I do not have any agent alerts for my SQL instance; therefore, the result is blank:

SQL Server Agent Alerts

Host Info

It uses the sys.dm_os_host_info DMV for fetching underlying operating system information for the SQL instance:

Host Info

SQL Server NUMA Info

It is useful to know the SQL Server Numa information. We can get the information using the DMV sys.dm_os_nodes:

SQL Server NUMA Info

System Memory

It is important to know the system memory information such as total physical memory, available physical memory, page file information, and system memory state. It runs DMV sys.dm_os_sys_memory for this data:

System Memory

Cluster Node Properties

If SQL instance is part of the Windows failover cluster, we get run DMV sys.dm_os_cluster_nodes to get cluster owner and status description. I am connected to a standalone instance; therefore, you do not get any output row:

Cluster Node Properties

AlwaysOn AG Status

If you are using AlwaysOn Availability Groups, this section helps you in executing a query to find the AG health and synchronization status with LSN information:

AlwaysOn AG Status

Hardware Info

It is good to know the hardware configuration of SQL Server 2017 underlying hardware. You can query sys.dm_os_sys_info to fetch these details:

Hardware Info

There are many sections in this diagnostic book to retrieve useful information about SQL Server. You can execute these queries and etch the information in the SQL Notebook.

Let’s explore few useful diagnostic queries.

TempDB Data Files

It is essential to know a number of data files for the system database TempDB. This section fetches the information from the error log:

TempDB Data Files

Volume Info (Monitor Disk Space)

You can monitor the disk space for the disk that holds the database files. We should take proactive actions if the free disk space is getting low. You get total disk space, available size, space free (%) as shown below:

Volume Info (Monitor Disk Space)

Drive Level Latency

You should monitor the disk latency for the underlying storage system. A slow disk drive can cause performance issues for the workload:
Drive Level Latency

In the output, you can note read, write, overall latency for the disk. You also get Avg Bytes/Read, Avg Bytes/write, and Avg Bytes/Transfer useful performance monitor counters as well:

Latency output

IO Latency by File

We might have multiple databases in a single SQL instance. We are interested in knowing which data files causing IO latency. It is an instrumental section in monitoring the latency for the data files:

IO Latency by File

IO Warnings

SQL Server logs an entry in the error log if the IO request takes longer than 15 seconds to complete. This section fetches all such occurrence from the error log:

IO Warnings

Missing Indexes All Databases

We should evaluate the missing index recommendation by the SQL Server. We should implement them if we find them suitable:

Missing Indexes All Databases

VLF Counts

We should also monitor the virtual log files (VLF) count and take appropriate actions if the VLF count is large. This section queries the sys.dm_db_log_info DMV and retrieves the information for each database in the instance:

VLF Counts

CPU Usage by Database

We can use this section to get a CPU usage breakdown for each database. We can identify the pattern of CPU usage and focus on the queries for the specific database for reducing the CPU usage:

CPU Usage by Database

IO Usage by Database

We can use this section for knowing IO usage information for the individual database. It provides handy information for each database to troubleshoot performance issues:

IO Usage by Database

Total Buffer Usage by Database

We can execute the query specified here to know the contribution of each database in the buffer utilization. We might see an issue where a database is using most of the buffer space and the other databases see a performance issue due to this. Get the data and investigate:

Total Buffer Usage by Database

Top Waits

Waits provide internal information of query execution in SQL Server. We can find the bottleneck on which query is waiting for its execution. This section fetches the information from the DMV’s and displays the top consuming wait resources:

Top Waits

CPU Utilization History

We can get the CPU utilization of the SQL Server and OS processes using the ring buffer. Suppose you get high CPU utilization alerts and we can get a history of CPU utilization that whether SQL or OS process is causing this issue:

CPU Utilization History

PLE by NUMA Node

Page life expectancy is a useful monitoring parameter in troubleshooting performance issues. You should monitor the PLE value and investigate if the PLE value is continuously low:

PLE by NUMA Node

Memory Grants Pending

We should not have memory grants pending in SQL Server. If you have pending memory grants, queries will wait until memory is not allocated:

Memory Grants Pending

Ad hoc Queries

We should minimize ad-hoc queries in SQL Server. An ad-hoc query causes SQL Server to prepare an execution plan for each query. It might bloat plan cache and cause performance issues if we have a large number of ad-hoc queries:

Execute query in this section to get ad-hoc queries Ad hoc Queries

Top Logical Reads Queries

We should identify and tune the queries with high logical reads. This section uses DMV’s to fetch top logical reads and suggest any missing index as well:

Top Logical Reads Queries

Top Avg Elapsed Time Queries

We get the list of queries taking a long execution time. You can further investigate, fine-tune those queries to reduce the performance bottleneck:

Top Avg Elapsed Time Queries

Conclusion

We explored SQL Notebooks in Azure data studio with the diagnostic queries. You can keep this notebook handy and execute the queries for your SQL instance. We do not require copying scripts to SSMS. We can directly execute queries in a notebook and view the data.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
162 Views