SQL Server Health Check HTML Report

SQL Server Health Check HTML Report

Report Overview

SQL Server Health Check HTML Report is the improved version of the old report and it can help you to quickly monitor the health of a given SQL Server instance. This report fetches and process the data from system tables and dynamic management views. It reports the crucial factors that showcase the current database instance performance. It’s been written using T-SQL script. We can run it on any SQL Server instance starting from 2008 R2 to 2019. Save the result as .HTML format and the same can be viewed through any web browser. We neither tested it on Azure nor RDS instances, thereof we may need to do some code changes for cloud instances. Script download is available at the end of this post.

Report Parts

Report contains below parts:

  • SERVER DETAILS
  • INSTANCE INFORMATION
  • DISK SPACE USAGE
  • TEMPDB USAGE
  • CPU USAGE
  • MEMORY USAGE
  • PERFORMANCE COUNTER DATA
  • MISSING BACKUP REPORT
  • DATABASE CONNECTIONS
  • DATABASE LOG SPACE USAGE
  • SQL AGENT JOB STATUS
  • BLOCKING PROCESS INFORMATION
  • LONG RUNNING TRANSACTIONS
  • TOP 20 TABLES
  • INDEX ANALYSIS
  • SQL ERRORLOG

Execution Procedure

  1. Create the stored procedure “[usp_SQLhealthcheck_report]”
  2. Execute the procedure Ex: EXECUTE [usp_SQLhealthcheck_report];
  3. Pass Mail profile and Email details @MailProfile, @MailID. Not mandatory
  4. Save Result As: Save output as .HTML on your local
  5. Make sure that the saved report is on the same folder where Images folder exists. So that we can see the LOGO at right corner.

Output Format

  1. Direct HTML report
  2. Send email with HTML as an attachment

Critical Performance Counter Data

This section captures the critical performance counters from management views. Some of those counters plays the vital role in system performance evaluation.

Page Life Expectancy (PLE): How many seconds a page is being available in Memory.

Buffer Cache Hit Ratio (BHR): What is the percentage of database calls reaching Buffer Cache instead of Disk storage.

Grants Pending: If any Memory requests pending from OS

Memory Low Alerts: 0 indicates no such alerts registered.

Recommendation for Continues Monitoring

If we want to design performance trends, then I would recommend the below parameters for monitoring on continues basis:

CPU Usage

  • SQL Process Utilization
  • System Idle

Memory Usage

  • Total_Physical_Memory_MB
  • Physical_memory_in_use_GB

Performance Counter Data

  • Page Life Expectancy
  • Buffer Cache Hit Ratio
  • Memory Grants Pending
  • Memory Grants Outstanding
  • Process_Physical_Memory_Low
  • Process_Virtual_Memory_Low

Blocking Process Information

Sample Screenshots from HTML Report:

Create procedure on the required database:

Execute the procedure. I don’t need to send email rather just need a HTML report

SQL Server Health Check HTML Report

Store the output as in HTML report

SQL Server Health Check HTML Report SQL Server Health Check HTML Report SQL Server Health Check HTML Report

Create Images folder and create the required company logo and name it as Logo_Image.jpg

SQL Server Health Check HTML Report

Open the report in a web browser, Sample Screenshots

SQL Server Health Check HTML Report SQL Server Health Check HTML Report SQL Server Health Check HTML ReportSQL Server Health Check HTML ReportSQL Server Health Check HTML Report SQL Server Health Check HTML Report SQL Server Health Check HTML Report SQL Server Health Check HTML Report

Summary:

SQL Server Health Check HTML Report is useful to quickly get the performance snapshot of a database instance. After going through the overall system parameters, except the minor issues we can clearly state that the both instances are optimally performing. Index maintenance and the database log file maintenance are the critical activities to be considered. Please make note that the HC report tool is not reporting the SQL code issues and its scope is restricted to the database system level performance.

Note:

  1. Always test the script on Pre-Production environment
  2. Top 20 Tables may take some time for VLDB
  3. You can customize the script based on requirement

SQL Server Health Check HTML Report

Posted in Performance Tuning, SQL Scripts, SQL Server DBA | Tagged , , , , , | 49 Comments

Filtered Index on NULL values is still doing a Key Lookup

Filtered Index on NULL values is still doing a Key Lookup

Filtered Index on NULL values is still doing a Key Lookup takes you through a strange behavior of filtered index in SQL Server. Here, we’ve discussed how a filtered index can help to improve the performance of your query. When I was working with one of the customer, in their critical production environment I’ve seen they created a filtered index on NULL values to cover more frequently used queries. But, when we are checking through the long running procedures we could see that Filtered Index on NULL values is still doing a Key Lookup. Let me explain it with an example.

Note: I am using AdventureWorks2016 database.

Here is the query:

SELECT SalesOrderID,

               OrderQty,

               UnitPrice

FROM    SALES.SalesOrderDetail

WHERE OrderQty>1 AND

                ProductID = 877 AND

                CarrierTrackingNumber IS NULL;

Why we need a covering index for the above query:

  • As per the business requirement, majority of our focus is on product number 877.
  • 90% of the cases we are fetching data based on the same predicate (OrderQty>N AND ProductID = 877 AND CarrierTrackingNumber IS NULL).
  • OrderQty is a variable (1 to N).
  • We are using the same condition/predicate in 8 different procedures. On and average this condition hits 10 times per minute.

How to determine the index columns?

OrderQty: As per the requirement, it’s a variable passed at runtime. Value is between 1 to N. Since it’s a predicate/where/condition we should keep it in Key columns.

ProductID: Since 90% of the queries are fetching data only for the product 877, we can have a filtered index that cover this particular scenario.

CarrierTrackingNumber: Like ProductID, we can have a filtered index on this column.

SalesOrderID: Since it’s part of the PRIMARY KEY we need not fetch it separately. Primary key columns are available in all non-clustered indexes by default. Thereof, no need to get it separately.

UnitPrice: This is the only column where we are not covering either in index key or in filter. Hence we need to add this column to index. Since we do not have any predicate on it and we are just fetching data, we can add it to INCLUDED list.

We are covering all columns for the above given query. Now, let’s create the index:

CREATE NONCLUSTERED INDEX

Custom_NIX_SALES_SalesOrderDetail_OrderQty_ProductID_877_CarrierTrackingNumber_ISNULL

ON SALES.SalesOrderDetail (OrderQty)

INCLUDE (UnitPrice)

WHERE ProductID = 877 AND

CarrierTrackingNumber IS NULL;

Create the above index, execute the query and see what will happen:

Filtered Index on NULL values is still doing a Key Lookup

Results:

Surprising, you are covering all columns from the given query. But, still it’s doing the clustered index scan. We’ll force it to use the newly created index, then only we’ll come to know the reason.

Force the query to use the covering index:

Filtered Index on NULL values is still doing a Key Lookup

Filtered Index on NULL values is still doing a Key Lookup

Results:

  • Even we are covering all columns, you can see that it’s doing the Key Lookup.
  • When you check it, the Key Lookup is happening to get the matching values for the predicate “CarrierTrackingNumber IS NULL”
  • Covering index that we’ve created is already having the filter “CarrierTrackingNumber IS NULL”. But, why it’s not using that index?

Why a Filtered Index on NULL values is still doing a Key Lookup?

  • When we are creating the filtered index on a value (ProductID = 877), it’s working fine. But, when are doing the same with “IS NULL” it’s not working.
  • I am still unable to find the root cause.
  • But, boss (Microsoft) is saying it doesn’t work, also suggested the resolution.
  • Here, you can find Microsoft take on this issue.
  • Microsoft is suggesting the below resolution:

To resolve this issue, include the column that is tested as NULL in the returned columns. Or, add this column as include columns in the index

Resolution:

  • Add the column in include columns.
  • Drop and recreate the index by adding this column in INCLUDE columns list and rerun the query.

Filtered Index on NULL values is still doing a Key Lookup

Results:

You can clearly see that, by default the query started using the covering filtered index without any key lookup.

Performance test:

We’ll test the performance of the query by comparing with CLUSTERED INDEX SCAN and the covering NON-CLUSTERED INDEX seek.

Filtered Index on NULL values is still doing a Key Lookup

Filtered Index on NULL values is still doing a Key Lookup

Filtered Index on NULL values is still doing a Key Lookup

Performance Improvements:

Filtered Index on NULL values is still doing a Key Lookup

Summary:

  • “Filtered index” is really useful when you want to customize and narrow down the data usage and it works like a magic with the minimized index maintenance cost.
  • When you are creating a filtered index on IS NULL condition, be aware that we need to include the same column.
  • Filtered Indexes in SQL Server is an awesome feature. But, Terms and Conditions applied.
  • Below are the few interesting articles that describes more side effects of Filtered Indexes

An Unexpected Side-Effect of Adding a Filtered Index by Paul White

A-Z of Filtered Indexes with examples in Sql Server by Basavaraj Biradar

What You Can (and Can’t) Do With Filtered Indexes by Brent Ozar

Posted in Performance Tuning, SQL Development | Tagged , | 3 Comments

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index takes you through a simple scenario where filtered indexes are helpful to improve the performance of frequently used queries. Before starting, I assume that you know “WHAT” a filtered index is or have a look at here. Filtered indexes are very helpful to cover frequently used queries with the minimized index size and maintenance cost. Lets consider a simple scenario as below:

Note: I am using the database [AdventureWorks2016]

Let’s say we have a query to fetch order details for a specific product (870). The same query has been getting called 8 to 10 times per minute. Now, our job is to improve the query performance:

SELECT SalesOrderID,

               OrderQty,

                UnitPrice

FROM     [Sales].[SalesOrderDetail]

WHERE  OrderQty > 2 AND ProductID = 870;

Analysis:

It’s just a simple query and it’s fetching SalesOrderID, OrderQty and UnitPrice for the Product 870 where OrderQty is greater than N(N is a variable). Before looking into the improvement aspects first we’ll see the execution plan and write down our observations:

Switching off ROW COUNT’s and ON statistics:

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

Observations:

  1. Total Logical Reads: 39875
  2. Clustered Index Scan is happening with Parallelism
  3. From Tool Tip:
  • Object (Index is being used): PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
  • Output List (Columns Fetching): SalesOrderID, OrderQty and UnitPrice
  • Predicate (Condition): OrderQty > 2 AND ProductID = 870

Find the Solution for Improvements:

Do we really need an index here?

Ans:

Yes, we need any index as the same query is getting called for 8 to 10 times per minute. Also, we could see high on logical reads.

How to decide the index key columns?

Ans:

Your queries where clause can answer this question.

Predicate is OrderQty > 2 AND ProductID = 870.

Now, it’s clear that we need to choose OrderQty AND ProductID are the key columns of the index.

INDEX (ProductID, OrderQty).

But, as per the business requirement OrderQty is a variable which means its value may get changed from time to time. But, productID is 870 only as our business focus is only on specific product 870. Since ProductID = 870 is constant and the same query is getting used widely, we can apply the same filter on Index:

INDEX (OrderQty)

WHERE ProductID = 870.

We determined the key columns also, applied the required filter on it. Now, we will create an index and see how it works.

Create Index and Test it:

CREATE NONCLUSTERED INDEX Custom_NIX_Sales_SalesOrderDetail_OrderQty_ProductID_870

ON [Sales].[SalesOrderDetail] (OrderQty)

WHERE ProductID = 870;

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

Surprising, isn’t it? We’ve created a filtered index that exactly covers the query. But, still it’s doing the clustered index scan. Why it’s not using the index that we have created? Let’s force it to use the index that we’ve created. Then, we’ll come to know why it’s not a proper index:

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

  • From the above images you can clearly see that why optimizer didn’t choose the newly created index unless you forced it.
  • It’s not being used as it’s not the optimized plan. We can see that it’s creating “WorkTable” a temp table on TempDB.
  • Also, the Key Lookup is happening for the column “UnitPrice” and it’s taking the 97% of cost. For each row filtered in Index Seek a Key Lookup is running for the corresponding UnitPrice.
  • Also, we can see the number of executions in Key Lookup is 11552. You have created an index but still the problem is not resolved yet. How to fix and improve it?

Draw the conclusion:

  • If we can remove the Key Lookup then optimizer surely chose the covering filtered index that we created as a solution.
  • Remove the Key Lookup by including the column “UnitPrice” as below:

CREATE NONCLUSTERED INDEX Custom_NIX_Sales_SalesOrderDetail_OrderQty_ProductID_870

ON [Sales].[SalesOrderDetail](OrderQty)

INCLUDE (UnitPrice)

WHERE ProductID = 870;

Test the Solution:

After adding the included column, you can see the optimizer started using the newly created index:

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

 

Now, compare the executions between Clustered Index SCAN and newly created Non-Clustered filtered index seek:

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

SQL Server Performance Tuning using Filtered Covering Index

Performance Improvements:

SQL Server Performance Tuning using Filtered Covering Index

We could see the improvement of 97% in CPU and 99% in IO. This is all we are just talking about one execution. If you can calculate the average improvement based on number of executions per a given day, it’ll be huge and you can project the same to your end customer.

Summary:

  • Always chose the right candidate for the right position (Key Column, Filtered Column and Include column).
  • Costly Key lookups are the first thing to be removed from your execution plans.
  • Always consider the data growth percentage in production while designing an index.
  • Filtered indexes are really helpful for more frequent queries based on a constant conditions as it reduces the index storage which in-turns reduces the maintenance.
  • Post SQL Server Performance Tuning using Filtered Covering Index can help you to understand the basics of covering filtered index usage in real time production environment.

 

Posted in Performance Tuning, SQL Development | Tagged | 1 Comment