Category Archives: Performance Optimization

How to get an Index’s Root Page, Intermediate Pages and Leaf Pages Information? Tip 5: Sql Server 101 Performance Tuning Tips and Tricks

In this article, I will explain you on How to get an Index’s Root Page, Intermediate Pages and Leaf Pages Information. Prior to that let us get some insights into the Index structure.

In Sql Server an index is made up of a set of pages (index nodes) that are organized in a B+ tree structure. This structure is hierarchical in nature. The top node is called the ROOT node (Root Page). The bottom level of nodes in the index are called the leaf nodes (leaf Pages). Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list. In case of Clustered Index, data in a table is stored in a sorted order by the Clustered Index key Column. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Non-Clustered Index structure is similar to that of a B+ Tree Structure of Clustered Index, but the leaf nodes of a Non-Clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. Because of this in case of Non-Clustered Index, Sql Server takes additional steps to locate the actual data. The row locators structure depends on whether underlying table is a clustered table (Table with Clustered Index) or a heap table (Table without Clustered Index). In case the underlying table is a Clustered table the row locator points to the Clustered Index key value corresponding to the Non-Clustered Key and it is used to look-up into the Clustered Index to navigate to the actual data row. If underlying table is a heap table, the row locator points to the actual data row address. In case of Non-Clustered Index, the data rows of the underlying table are not sorted and stored in order based on their Non-Clustered keys.

B+ Tree Structure of a Clustered Index

Structure of Clustered Index

Let us explain how to get an Index’s Root Page, Intermediate Pages and Leaf Pages Information with an example

To explain how to get an Index’s Root Page, Intermediate Pages and Leaf Pages Information, let us create a Customer table as shown in the below image with 35K records. Execute the following script to Create the Customer Table with Clustered Index on the CustomerId column with sample 35K records.

--Create Demo Database
CREATE DATABASE SqlHints101PerfTips4
GO
USE SqlHints101PerfTips4
GO
--Create Demo Table Customers
CREATE TABLE dbo.Customers (
	CustomerId INT IDENTITY(1,1) NOT NULL,
	FirstName VARCHAR(50), LastName  VARCHAR(50),
	PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
	CreationDate DATETIME
)
GO
--Populate 35K dummy customer records
INSERT INTO dbo.Customers (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 35000 REPLACE(NEWID(),'-',''), REPLACE(NEWID(),'-',''), 
    CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10)),
    REPLACE(NEWID(),'-','') + '@gmail.com',     
    DATEADD(HOUR,CAST(RAND(CHECKSUM(NEWID())) * 19999 as INT) + 1 ,'2006-01-01')
FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
GO
--Create a PK and a Clustered Index on CustomerId column
ALTER TABLE dbo.Customers 
ADD CONSTRAINT PK_Customers_CustomerId 
PRIMARY KEY CLUSTERED (CustomerId)

As explained in the previous article we can use Dynamic Management Function sys.dm_db_database_page_allocations to get the list of all the pages associated with an Index. Note sys.dm_db_database_page_allocations is an un-documented (i.e. feature that may change or removed without any notice or may produce un-expected result) DMF. So avoid using it in the Production environment.

Get all the Pages Associated with the Clustered Index PK_Customers_CustomerId

To get all the Pages associated with the Index PK_Customers_CustomerId of the Customers table, we need to pass the @DatabaseId, @TableId, @IndexId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations. We can use sys.indexes catalog view as shown below to get the Index Id for a Index.

SELECT OBJECT_NAME(object_id) table_name, object_id, 
     name index_name, index_id, type, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Customers'
	AND name = 'PK_Customers_CustomerId'

RESULT:
Use sys.indexes to get the indexid for the index

As explained in the previous article:

  • Clustered Index will always will have the Index Id as 1.
  • Index Id for the Non-Clustered Index will be >=2.
  • Index Id for the Heap Table is 0.

We can execute the below query to get all the pages associated with the Clustered Index PK_Customers_CustomerId

SELECT DB_NAME(PA.database_id) [DataBase], 
    OBJECT_NAME(PA.object_id) [Table], SI.Name [Index], 
    is_allocated, allocated_page_file_id [file_id], 
    allocated_page_page_id [page_id], page_type_desc, 
    page_level, previous_page_page_id [previous_page_id], 
    next_page_page_id [next_page_id]
FROM sys.dm_db_database_page_allocations 
    (DB_ID('SqlHints101PerfTips5'), 
         OBJECT_ID('Customers'), 1, NULL, 'DETAILED') PA
         LEFT OUTER JOIN sys.indexes SI 
        ON SI.object_id = PA.object_id 
                   AND SI.index_id = PA.index_id
WHERE is_allocated = 1  
	and page_type in (1,2) -- INDEX_PAGE and DATA_PAGE Only
ORDER BY page_level DESC, is_allocated DESC,
         previous_page_page_id

RESULT:
Clustered Index All Pages

As shown in the above image the Root Page File Id is 1 and Page Id is 968. Root Page is the one with maximum Page_level (here in this example it is 2) having previous_page_id and next_page_id as NULL. Intermediate Pages File Id is 1 and Page Id’s are: 2016 and 2032. Intermediate pages are the one whose Page_Level is less than the root page level and greater than 0 (i.e. Leaf Page Level (i.e. 0) < Intermediate Page level (here it is 1) < Root Page level (here it is 2)). And leaf level pages are the the ones whose page level is 0.

We can use the below function to get the pages of the different levels of a B+ Tree Index:

We can use the below function to get the B+ Tree Clustered/Non-Clustered Index Root Page, Intermediate Level Pages and Leaf Level Pages. Note I am using sys.dm_db_database_page_allocations un-documented (i.e. feature that may change or removed without any notice or may produce un-expected result) DMF. So avoid using it in the Production environment.

CREATE FUNCTION dbo.GetPagesOfBPlusTreeLevel(
	@DBName VARCHAR(100), @TableName VARCHAR(100) = NULL, @IndexName VARCHAR(100) = NULL, 
	@PartionId INT = NULL, @MODE VARCHAR(20), @BPlusTreeLevel VARCHAR(20) 
)
RETURNS
@IndexPageInformation TABLE (
	[DataBase] VARCHAR(100), [Table] VARCHAR(100), [Index] VARCHAR(100), 
	[partition_id] INT, [file_id] INT, [page_id] INT, page_type_desc VARCHAR(100), 
	page_level INT, [previous_page_id] INT, [next_page_id] INT)
AS
BEGIN

	DECLARE @MinPageLevelId INT = 0 , @MaxPageLevelId INT = 0, @IndexId INT = NULL
		
	SELECT	@IndexId = index_id
	FROM sys.indexes
	WHERE OBJECT_NAME(object_id) = @TableName AND name = @IndexName

	IF @IndexId IS NULL
		RETURN
	
	IF @BPlusTreeLevel IN ('Root', 'Intermediate') 	
	BEGIN 	
		SELECT  @MaxPageLevelId = (CASE WHEN  @BPlusTreeLevel ='Intermediate' THEN MAX(page_level) - 1 ELSE MAX(page_level) END), 
				@MinPageLevelId = (CASE WHEN  @BPlusTreeLevel ='Intermediate' THEN 1 ELSE MAX(page_level) END)
		FROM sys.dm_db_database_page_allocations 
			(DB_ID(@DBName), OBJECT_ID(@TableName), @IndexId, @PartionId, 'DETAILED') PA
 					LEFT OUTER JOIN sys.indexes SI 
				ON SI.object_id = PA.object_id AND SI.index_id = PA.index_id
		WHERE is_allocated = 1 AND page_type in (1,2)  -- INDEX_PAGE and DATA_PAGE Only

		IF @MaxPageLevelId IS NULL OR @MaxPageLevelId = 0 
			RETURN
	END
 
	INSERT INTO @IndexPageInformation
	SELECT DB_NAME(PA.database_id) [DataBase], OBJECT_NAME(PA.object_id) [Table], SI.Name [Index], 
		[partition_id], allocated_page_file_id [file_id],  allocated_page_page_id [page_id], page_type_desc, 
		page_level, previous_page_page_id [previous_page_id], next_page_page_id [next_page_id]
	FROM sys.dm_db_database_page_allocations 
			(DB_ID(@DBName), OBJECT_ID(@TableName), @IndexId, @PartionId, 'DETAILED') PA
			 LEFT OUTER JOIN sys.indexes SI 
			ON SI.object_id = PA.object_id AND SI.index_id = PA.index_id
	WHERE is_allocated = 1 AND page_type in (1,2) -- INDEX_PAGE and DATA_PAGE Only
			AND page_level between @MinPageLevelId AND @MaxPageLevelId
	ORDER BY page_level DESC, previous_page_page_id

	RETURN
END

How to get an Index’s Root Page Information?

We can use the above function like below to get the root page of the Clustered Index PK_Customers_CustomerId on the Customers Table in the SqlHints101PerfTips5 Database.

SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel 
	('SqlHints101PerfTips5', 'Customers', 
	 'PK_Customers_CustomerId', NULL, 'DETAILED', 'Root')

RESULT:
How to Get Root Page of B Tree Index

There will be always on Root Page. Root Page is the one with maximum Page_level having previous_page_id and next_page_id as NULL. If the number of records in the table are very less and just one page is enough to store it. Then Sql Server doesn’t create the B+ Tree structure. So, in such scenario, the above function will not return the Root Page information.

How to get an Index’s Intermediate Pages Information?

We can use the above function like below to get the Index’s Intermediate pages of the Clustered Index PK_Customers_CustomerId on the Customers Table in the SqlHints101PerfTips5 Database.

SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel 
	('SqlHints101PerfTips5', 'Customers', 
	 'PK_Customers_CustomerId', NULL, 'DETAILED', 'Intermediate')

RESULT:
How to Get Intermediate Pages of a B Tree Index

Intermediate pages are the one whose Page_Level is less than the root page level and greater than Leaf Page Level (i.e. 0) (i.e. Leaf Page Level < Intermediate Page level < Root Page level). In the following situations an Index will not have intermediate pages: 1) If the number of records in the table are very less and just one page is enough to store it. Then Sql Server doesn't create the B+ Tree structure. 2) If the number records stored in the table are very less and it is spanning only couple of pages in such scenario Sql Server can have B+ Tree Index with just Root Page and the Leaf Level Pages. In such scenarios this function will not return any information. If we have a very large Table, then we can multiple Intermediate Levels.

How to get an Index’s Leaf Page Information?

We can use the above function like below to get the Index’s Leaf pages of the Clustered Index PK_Customers_CustomerId on the Customers Table in the SqlHints101PerfTips5 Database.

SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel 
	('SqlHints101PerfTips5', 'Customers', 
	 'PK_Customers_CustomerId', NULL, 'DETAILED', 'Leaf')

RESULT:
How to Get Leaf Pages of a B Tree Index

In case of Clustered Index the Page_Type_Desc of Leaf Pages is DATA_PAGE where as in case of Non-Clustered Index it will be INDEX_PAGE.

In the Next Article I will explain how we can use the DBCC PAGE command to look into what actually Sql Server stores in the Page.

How to find the list of all Pages that belongs to a Table and Index? Tip 4: Sql Server 101 Performance Tuning Tips and Tricks

I was writing an article on when and why we have to use included columns and wanted to explain it by showing how key column and included column values are stored in the Index Pages. That time, I got realized that first I need to explain how we can look into the Sql internal storage. Because of this I have decided to explain: How to find all the pages allocated to a Table and Index? How to identify Root, Intermediate and Leaf Pages of the Index? and How to inspect the Page data?. As a result of this realization I have written this and the next two articles. These articles will be referred in most of the articles in this series of 101 Performance Tuning Tips and Tricks.

From Sql Server 2012 onwards we have an un-documented (i.e. feature that may change or removed without any notice or may produce un-expected result) dynamic management function sys.dm_db_database_page_allocations to get the list of pages associated with one or more Tables and Indexes. This function returns one row for each page associated with the Table/Index.

This dynamic Management function takes following 5 parameters/arguments

  • @DatabaseId: Database ID of the Database whose tables or indexes assocaited pages information is required. This is a mandatory required parameter. We can use the DB_ID() function to get the Database ID of the Database by Database Name
  • @TableId: Table Id of the table whose assocaited pages information is required. We can use the OBJECT_ID() functio to get the table id by table name. It is an optional parameter, if it is Passed as NULL then it returns the pages associated with all tables in the Database and when it is NULL then next two parameters (i.e. @IndexId and @PartionId) values are ignored
  • @IndexId: Index Id of the Index whose associated pages information we are looking for. We can use the sys.indexes catalog view to get the Index Id. It is an optional parameter, if it is passed as NULL then it returns the pages associated with all the indexes.
  • @PartitionId: Id of the partition whose associated pages information we are looking for. It is an optional parameter, if it is passed as NULL then it returns the pages associated with all the partitions.
  • @Mode: This is mandatory Parameter, it’s value can be either ‘LIMITED’ or ‘DETAILED’. ‘LIMITED’ returns less information. However, ‘DETAILED’ returns detailed/more information. Obivously the ‘DETAILED’ mode uses more resources.

Let us understand the usage of this Dynamic Management Function sys.dm_db_database_page_allocations with examples

To understand how we can use this DMF to identify the pages belonging to the Table and Index, let us create a Customer table as shown in the below image with 35K records. Execute the following script to Create the Customer Table with Clustered Index on the CustomerId column and Composite Non-Clustered Index on the FirstName and LastName Column with sample 35K records.

--Create Demo Database
CREATE DATABASE SqlHints101PerfTips4
GO
USE SqlHints101PerfTips4
GO
--Create Demo Table Customers
CREATE TABLE dbo.Customers (
	CustomerId INT IDENTITY(1,1) NOT NULL,
	FirstName VARCHAR(50), LastName  VARCHAR(50),
	PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
	CreationDate DATETIME
)
GO
--Populate 35K dummy customer records
INSERT INTO dbo.Customers (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 35000 REPLACE(NEWID(),'-',''), REPLACE(NEWID(),'-',''), 
    CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10)),
    REPLACE(NEWID(),'-','') + '@gmail.com',     
    DATEADD(HOUR,CAST(RAND(CHECKSUM(NEWID())) * 19999 as INT) + 1 ,'2006-01-01')
FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
GO
--Create a PK and a Clustered Index on CustomerId column
ALTER TABLE dbo.Customers 
ADD CONSTRAINT PK_Customers_CustomerId 
PRIMARY KEY CLUSTERED (CustomerId)
GO
--Create a Non-Clustered Composite Index on the FirstName and LastName column
CREATE NONCLUSTERED INDEX IX_Customers_FirstName_LastName
    ON dbo.Customers (FirstName, LastName)

Example 1: Get all the Pages Associated with the Table Customers

To get all the Pages associated with the table Customers we need to pass only the @DatabaseId, @TableId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations. We can write a query like below to get this information:

SELECT DB_NAME(PA.database_id) [DataBase], 
	OBJECT_NAME(PA.object_id) [Table], SI.Name [Index], 
	is_allocated, allocated_page_file_id [file_id], 
	allocated_page_page_id [page_id], page_type_desc, 
	page_level, previous_page_page_id [previous_page_id], 
	next_page_page_id [next_page_id]
FROM sys.dm_db_database_page_allocations 
	(DB_ID('SqlHints101PerfTips4'), 
         OBJECT_ID('Customers'),NULL, NULL, 'DETAILED') PA
	     LEFT OUTER JOIN sys.indexes SI 
		ON SI.object_id = PA.object_id 
                   AND SI.index_id = PA.index_id
ORDER BY page_level DESC, is_allocated DESC,
         previous_page_page_id

RESULT:
How to get all the Pages Associated with a Table in Sql Server

From the above result we can see that, this DMV has returned all the pages associated with all the indexes of the Table when we have not specified (i.e. NULL is passed here) the @IndexId parameter value. In the result we can see the pages associated with the non-clustered index IX_Customers_FirstName_LastName and as well as the clustered index PK_Customers_CustomerId.

The records with page_level as 0 are the leaf pages and INDEX_PAGE with previous_page_id and next_page_id as NULL are the ROOT pages of the Index. And the root pages page_level value will be the maximum value for that index.

Clustered Index leaf pages store the actual table data, so the page type of the leaf pages of the Clustered Index is a DATA_PAGE. The Page Type of the non-leaf page (i.e. Root Page and Intermediate Page) of the Clustered Index is an INDEX_PAGE. Where as for the Non-Clustered index all the pages including leaf pages are of Type INDEX_PAGE, as the leaf page of the Non-Clustered index again points to the Clustered Index where actual Table Data is stored.

allocated_page_file_id (i.e. file_id) and allocated_page_page_id (i.e. page_id) are the frequently used columns from the result set of this DMV.

Example 2: Get all the Pages Associated with an Index

To get all the Pages associated with a specific Index of the table, we need to pass the @DatabaseId, @TableId, @IndexId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations.

Assume that we want to find all the pages associated with the Non-Clustered Index IX_Customers_FirstName_LastName. Then first question comes in the mind is how to get the Index Id for the Non-Clustered Index IX_Customers_FirstName_LastName. Answer to this question is very simple we can use the sys.indexes catalog view to get the Index Id. We can write a query like below to get Customers tables all the Indexes and their Index Id

SELECT OBJECT_NAME(object_id) table_name, object_id, 
     name index_name, index_id, type, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Customers'

RESULT:
How to get Index Id in Sql Server

From the above result we can see that Index Id for the Non_Clustered
Index IX_Customers_FirstName_LastName is 2.

Note:

  • Clustered Index will always will have the Index Id as 1.
  • Index Id for the Non-Clustered Index will be >=2.
  • Index Id for the Heap Table is 0.

We can write a query like below to get all the pages associated with the Non-Clustered Index IX_Customers_FirstName_LastName

SELECT DB_NAME(PA.database_id) [DataBase], 
	OBJECT_NAME(PA.object_id) [Table], SI.Name [Index], 
	is_allocated, allocated_page_file_id [file_id], 
	allocated_page_page_id [page_id], page_type_desc, 
	page_level, previous_page_page_id [previous_page_id], 
	next_page_page_id [next_page_id]
FROM sys.dm_db_database_page_allocations 
	(DB_ID('SqlHints101PerfTips4'), 
         OBJECT_ID('Customers'), 2, NULL, 'DETAILED') PA
	     LEFT OUTER JOIN sys.indexes SI 
		ON SI.object_id = PA.object_id 
                   AND SI.index_id = PA.index_id
ORDER BY page_level DESC, is_allocated DESC,
         previous_page_page_id

RESULT:
How to get all the Pages Associated with an Index

Following are the list of columns returned by this DMF

database_id
object_id
index_id
partition_id
rowset_id
allocation_unit_id
allocation_unit_type
allocation_unit_type_desc
data_clone_id
clone_state
clone_state_desc
extent_file_id
extent_page_id
allocated_page_iam_file_id
allocated_page_iam_page_id
allocated_page_file_id
allocated_page_page_id
is_allocated
is_iam_page
is_mixed_page_allocation
page_free_space_percent
page_type
page_type_desc
page_level
next_page_file_id
next_page_page_id
previous_page_file_id
previous_page_page_id
is_page_compressed
has_ghost_records

Query Store in Sql Server 2016

Introduction to Query Store

Many a time we come across a scenario where suddenly in production without any release or changes some query which was working perfectly alright till yesterday is taking too long to execute or consuming lot of resources or timing out.

Most of the times such issue are related to execution plan change (commonly referred as Plan Regression). Till yesterday the query was running fine as it was running with good cached execution plan and today a bad plan is generated and cached. Because of this bad cached plan the query which was executing perfectly alright suddenly starts misbehaving.

To identify and fix such performance problems due to the execution plan change the Query Store feature introduced in Sql Server 2016 will be very handy.

Query Store basically captures and stores the history of query execution plans and its performance data. And provides the facility to force the old execution plan if the new execution plan generated was not performing well.

To understand Query Store with an example let us create a demo database SqlhintsQSDemo and a Customer table having sample data as shown in the following image by executing the following statement:

Customer Table for Query Store Demo

Script:

--------Create Demo database--------
CREATE DATABASE SqlhintsQSDemo
GO
USE SqlhintsQSDemo
GO
--------Create a Customer Table------
CREATE TABLE dbo.Customer( 
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Populate 100,000 customers with unique FirstName 
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 100000 NEWID(), NEWID()
FROM SYS.all_columns SC1 
	CROSS JOIN SYS.all_columns SC2
GO 
--Populate 15000 customers with FirstName as Basavaraj
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 15000 'Basavaraj', NEWID()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2

Execute the following statement to create a non-clustered index on the FirstName column of the customer table and the stored procedure GetCustomersByFirstName to get the customer details by FirstName

-- Create non-clustered index on the FirstName column
CREATE INDEX IX_Customer_FirstName on dbo.Customer (FirstName)
GO
-- Create stored procedure to get customer details by FirstName
CREATE PROCEDURE dbo.GetCustomersByFirstName
(@FirstName AS NVARCHAR(50))
AS
BEGIN
	SELECT * FROM dbo.Customer 
	WHERE FirstName = @FirstName
END

Enabling Query Store

Query store is a database level feature, which is disabled by default. Follow the following steps to enable it by using Management Studio

Step 1: Right click on the demo database SqlHintsQSDemo and select the Properties menu option as shown in the below image

Enable Query Store DB Properties window

Step 2: Step 1 Pops-up the properties window. In the properties window from the left navigation options select the new option Query Store. Below is the view of the properties window after selecting the Query Store option.

Query Store Properties window

Step 3: From the Query Store option window of Step 2 we can see that the Operation Mode (Requested) setting has three different options. Selecting the ReadWrite option will enable the Query Store and starts capturing the query execution data such as execution plan and it’s performance stats. Selecting Read option will only allow the data to read from the query store but no new query data is captured.
Below image shows the various query store options after selecting the ReadWrite Operation Mode. To know each of these options select the option it will display the details about it below.

Query Store Properties window Post Selecting ReadWrite

Query store captured data is stored in the respective query store enabled database. As we can see from the above image 100 MB is reserved for query store data in the SqlhintsQSDemo database. This value can be changed by changing the Max Size (MB) option value.

Click OK after selecting the desired query store options, it will enable the query store. And if we refresh the SqlhintsQSDemo database in the object explorer we can see the Query Store folder as shown in the below image

Query Store folder in the object explorer

Now query store feature is enabled let us understand how it comes handy in resolving performance issues

Example Usage of Query Store feature to resolve Performance issue

This example explains the scenario where suddenly in production without any release or changes some query which was working perfectly alright, starts taking too long to execute or consuming lot of resources or timing out. And how the query store comes handy in such situation

To explain this we will use the stored procedure GetCustomersByFirstName to fetch the records from the Customer table based on FirstName. And the Customer table has unique FirstName for all the customers except for the FirstName ‘Basavaraj’. There are 15,000 customers with FirstName as ‘Basavaraj’, so fetching a record where CustomerName is ‘Basavaraj’ will be better with table scan (i.e. clustered index scan) than using the non-clustered index on FirstName. But fetching the Customer records where FirstName value is anything other than ‘Basavaraj’, it makes sense to use the non-clustered index on FirstName. In this case where the majority queries will be better of executing by using the Non-clustered index on FirstName

Let us execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Query doing Non Clustered Index Seek

From the execution plan we can see that the Stored Procedure is using the Non-clustered index seek on the FirstName column to fetch the Customer details, which is the optimal option too.

From the object explorer select the “Top Resource Consuming Queries” option as shown in the below image to view the query plan and the performance stats capture for the above SP execution in the Query Store

Top Resource Consuming Queries Option

Below is the Query Store view of the “Top Resource Consuming Queries”. Here we can see that Plan Id 6 is corresponding to the above stored procedure execution which was using Non-Clustered Index Seek. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed ones, has one execution plan, it took 16129 micro seconds and query id is 6.

Qury Store After First Execution of the Query

Now the execution of the GetCustomersByFirstName will always use the Non-Clustered index on the FirstName column, as the first time execution of it cached this Non-Clustered Index Seek plan. So as long as this plan is cached the query where we need to get the Customer data by FirstName will always execute optimally. The only way this cached query plan is removed from the cache if the server is re-started or the memory pressure on the server etc. We can also execute the following DBCC statement to remove all the cached plans from the database server

DBCC FREEPROCCACHE

After executing the above DBCC statement all the cached plans are removed from the cache. Now try executing the same stored procedure i.e. GetCustomersByFirstName but this time pass the @FirstName parameter value as Basavaraj instead of Xyz. While executing the following statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Basavaraj'

Result:
Query doing Clustered Index San

From the result we can see that instead of using Non-Clustered Index on FirstName, it is doing the Clustered Index Scan. The reason it is doing clustered index scan is, there are 15,000 records whose FirstName is ‘Basavaraj’ and there are no previously cached plans. It makes sense for the Sql Server to use Clustered Index scan, instead of using a Non-Clustered index seek which uses bookmark look-up for each of the record. Now this plan is cached and each time the SP: GetCustomersByFirstName is executed it will use the clustered index scan

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Basavaraj’. You need to refresh the query store if it is already open. Here we can see a new plan with plan id 11 is generated which corresponds to the clustered index scan. So we can see that now we have two query plans corresponding to the query executed by the stored procedure GetCustomersByFirstName.

Query Store View with two plans for the Same query 1

Let us again execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Using worst Cached Plan

From the result we can see that this time the stored procedure execution is using the Clustered index scan instead of the non-clustered index scan which it was doing previously. To get the customer details whose FirstName is ‘Xyz’, from the result we can see it is using the cached plan which is optimized/compiled for the @FirstName parameter value ‘Basavaraj’. This is how a perfectly working query starts misbehaving without any release or changes.

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. You need to refresh the query store if it is already open. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed 3 times and has two execution plans i.e. 6 and 11 and plan 11 which is a clustered index scan is the current plan. In the below image we can clearly see than plan 11 is taking more time to execute compared to plan 6. So, in this case it is optimal to execute the query with plan 6 compared to plan 11.

Query Store View after  third execution

From the above query store view it is clear that plan 6 is the optimal plan for the execution of the stored procedure GetCustomersByFirstName compared to plan 11 as majority of the time the stored procedure is executed with a parameter value other than ‘Basavaraj’.

As shown in the below image query store gives an option to force the execution plan for the future execution of the queery. To force the plan select the plan 6 (i.e. light blue color circle) and then click on the Force Plan option.

Query Store force the execution plan 1

After forcing the plan 6 as shown in the above image. Now execute the GetCustomersByFirstName one more time with @FirstName parameter value as ‘Xyz’.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Execution plan after forcing it by the query store

From the above result we can see that execution of the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’ is doing a non-clustered index scan compared to clustered index which it was doing previously.

Below is the view of the Query Store after forcing the execution plan 6 and executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. We can see that plan 6 circle has a right tick mark, which means this is the force plan which now will not change even after server re-start. As shown in the below image adjacent to Force Plan option we have an Un-force Plan which an be used to un-force this forced plan.

Query store view post forcing the plan

New features in SQL SERVER 2016