Category Archives: Performance Optimization

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

A-Z of Filtered Indexes with examples in Sql Server

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.

Before using filtered index I strongly recommend everyone to go through the article INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Basically, adding a filtered index on a table may cause the existing working stored procedure to fail if the stored procedure was not created with a setting which doesn’t meet the filtered index prerequisites.

Let us understand filtered index with examples:

Example 1:

Let us first create a customer table with hundred thousand records as shown in the below image by the following script. Note Customer tables Country and CountryCopy column type and values are same. And also every tenth record has the Country/CountryCopy column value as United States and rest of the records column value as India. Also the age column value between 18 to 75 years.

FilteredIndex Example 1

CREATE DATABASE SqlHintsFilteredIndexDemo
GO
USE SqlHintsFilteredIndexDemo
GO
CREATE TABLE dbo.Customer(
CustomerId      INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstName       VARCHAR(50) ,
LastName        VARCHAR(50),
Country         VARCHAR(50),
CountryCopy     VARCHAR(50),
Age				INT)
GO
SET NOCOUNT ON
GO
--Populate 100K records, where every 10th record record 
--has country as United States and rest of the 
--records have country as India
DECLARE @i INT = 1, @istring VARCHAR(20), @Country VARCHAR(50)
WHILE(@i<=100000)
BEGIN
 IF(@i % 10 =0) 
  SET @Country = 'United States'   
 ELSE
  SET @Country = 'India'
     
 SET @istring = CAST(@i AS VARCHAR(20))
 
 INSERT INTO dbo.Customer(FirstName, LastName, 
                  Country, CountryCopy, Age)
 VALUES ('FN' + @istring, 'LN' + @istring,@Country , @Country,
 ROUND(RAND(convert(varbinary, newid()))*57+18,0))--Age 18 to 75
 SET @i = @i +1
END
GO

Let us now create a classic non-clustered index on the Country column

CREATE NONCLUSTERED INDEX IX_Customer_Country
ON dbo.Customer(Country)
GO

Let us now create a filtered non-clustered index on the CountryCopy column

CREATE NONCLUSTERED INDEX IXF_Customer_CountryCopy
ON dbo.Customer(CountryCopy)
WHERE CountryCopy = 'United States'
GO

Now table has three indexes one is a clustered index created created on the CustomerId column as result of it being a primary key column, the Country column has classic non-clustered index IX_Customer_Country on it and the CountryCopy column has filtered non-clustered index IXF_Customer_CountryCopy on it. Let us now see how many number of rows each of these indexes has and also the size of these indexes using the below script:

SELECT I.name [Index Name],i.type_desc [Index Type],
 PS.row_count [Number of rows],
 PS.used_page_count [Used page count],
 PS.reserved_page_count [Reserved page count]
FROM sys.indexes I
 INNER JOIN sys.dm_db_partition_stats ps
  ON I.object_id = ps.object_id AND I.index_id = ps.index_id
WHERE I.object_id = OBJECT_ID('Customer')

RESULT:
Filtered Indexes Require Less Storgae

So, from the above result it is clear that the number of records in the filtered index is equal to the number records in the table which matches to the filter criteria, so the filtered indexes requires less storage space and they perform better from performance perspective.

Now let us check by running below two queries and see how filtered and classic index perform from performance perspective:

--Filtered Index
SELECT COUNT(1) FROM dbo.Customer WITH(NOLOCK)
WHERE CountryCopy = 'United States'
GO
--Classic regular Index
SELECT COUNT(1) FROM dbo.Customer WITH(NOLOCK)
WHERE Country = 'United States'
GO

RESULT:
FilteredIndexPerformance

From the above result it is clear that the query which uses filtered index has a cost of 37% whereas the query which uses classic index has a cost of 63%. From this result it is clear that the query which uses filtered index performs better.

Let us drop the two non-clustered indexes created in this example by the following script:

DROP INDEX IX_Customer_Country ON dbo.Customer
GO
DROP INDEX IXF_Customer_CountryCopy ON dbo.Customer
GO

Example 2: This example explains the columns in the filter index’s filter criteria/expression doesn’t need to be a key column in the filtered index definition

If we know that the look-up on the Customer tables record by FirstName and LastName is always for the United States customers. Then in such a scenario, a filtered index like below is more suitable than having a regular non-clustered index.

CREATE INDEX IXF_CUSTOMER_FirstName_LastName
ON dbo.Customer(FirstName,LastName)
WHERE Country = 'United States'
GO

This index will only index the records whose country is ‘United States’ by FirstName and LastName. And also observe that the Country column is used in the filter criteria, but it is not a key column of the index.

Note: This index will not be used if country of the customer is not ‘United States’.

Example 3: Filter Criteria need to be part of the queries WHERE clause to force the usage of the Filtered Index

Let us try executing the below query and see whether it is using filtered index created in the previous example (i.e. example 2).

Note: For the customer with FirstName = ‘FN90000’ AND LastName = ‘LN90000’ the Country/CountryCopy column value is ‘United States’

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT
FilterIndex Not Used 1

From the above result it is clear that query is not using the filtered index. Now let us add the filter index’s filter criteria in the queries WHERE clause and verify whether it is using the filtered index:

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Country = 'United States'
 AND FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:
ForcingFilteredIndex

From the above examples it is clear that the filter index’s filter expression need to be part of the queries WHERE clause to force it’s usage.

Below result depicts the performance comparison of the above two queries (i.e. one which doesn’t use the filtered index and another one which uses the filtered index):
FilteredIndexPerformanceComparision

EXAMPLE 4: Whether I can specify the index hint for the query to force filtered index usage instead of writing the filter expression of the filtered index in the queries WHERE clause?

While going through Example 3, you may have thought instead of writing filter index expression in the queries WHERE clause, we would have specified the INDEX hint in the query to force it’s usage. Let us see whether this thought works:

SELECT *
FROM dbo.Customer WITH(NOLOCK, 
        INDEX(IXF_CUSTOMER_FirstName_LastName))
WHERE FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

So, from the above result it is clear that we can’t force a filtered index usage by specifying the index hint, instead of it as explained in example 3 the WHERE clause of the query need to have filtered index’s filter expression.

EXAMPLE 5: Whether using a local variable instead of the constant of the filter expression of the filtered index’s in the queries WHERE clause still results in the usage of the filtered index?

As per Example 3, to force the filter index usage we need to write the filter index’s filter expression in the queries WHERE clause (i.e. Country = ‘United States’). Instead of directly specifying the country name ‘United States’ directly in the query, whether we can declare a local variable and assign it the country name and then use the local variable in the query. The below example demonstrates this use case scenario:

--Query with filter index's filter expressions value 
--as a local variable in the WHERE clause 
DECLARE @CountryName VARCHAR(50) = 'United States'
SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Country = @CountryName 
 AND FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:
Filtered Index Local Variable as Filter Expression

From the above query result it is clear that we can’t replace the constant of the filter expression of the filtered index in the queries where clause by a local variable.

Drop the index IXF_CUSTOMER_FirstName_LastName created in example 2 by the following script:

DROP INDEX IXF_CUSTOMER_FirstName_LastName ON dbo.Customer

EXAMPLE 6: Whether the WHERE clause of the query need to have the same constant expression as specified in the Filtered Index’s filter expression to force the filtered index usage.

Let us create a filtered index on the FirstName and LastName column with filter criteria as Age > 60.

CREATE INDEX IXF_CUSTOMER_FirstName_LastName
ON dbo.Customer(FirstName,LastName)
WHERE Age > 60
GO

Note: For the customer with FirstName = ‘FN88002’ AND LastName = ‘LN88002’ the age column value is 72.

Let us check whether the below query which has the same constant expression in the WHERE clause as in the filtered index’s filter expression forces the usage of the filtered index

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 60 
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 5

So, from the above example it is clear that if the queries WHERE clause has the same constant expression as the filtered index’s filter expression, then the filtered index is used.

Let us change the WHERE clauses Age > 60 condition to Age > 65 and see whether the query is still forcing the usage of filtered index.

SELECT FirstName, LastName
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 65
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 6

From the above result the query is still using the filtered index when the WHERE clause Age > 60 condition is changed to Age > 65.

Now let us try to change the WHERE clauses Age > 65 condition to Age > 55 and see whether the query is still forcing the usage of filtered index.

SELECT FirstName, LastName
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 55
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 6 2

From the above result it is clear that the query is not using the filtered index when the WHERE clause Age > 65 condition is changed to Age > 55.

So, the conclusion is: the constant expression specified in the queries WHERE clause should be same or subset of the filtered index’s filter expression to force the filtered index usage.

Will be continued with couple of more examples…

Usage of OR Boolean Operator/CASE Statement in WHERE Clause Leads to Index/Table Scan

There are multiple scenario where usage of OR Clause  or CASE statement in the WHERE Clause leads to Index/Table Scan instead of Index seek. In this article we will go through one such use case scenario.

Script @ http://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and table used in this article.

Create a Non-Clustered index on the FirstName Column of the Employee Table using the below statement:
Create INDEX IX_Employee_FirstName ON Employee(FirstName)

UseCase Scenario:
Need to write a stored procedure which returns all the employee details  if input parameter @FirstName is not passed and if it’s values is passed then details of only those employees whose FirstName matches with input parameter value.

For the above use cases we can write the stored procedure using one of the below option:
1) Using OR Boolean Operator
CREATE PROCEDURE GetEmployeeDetailsUsingOR
@FirstName VARCHAR(50)  = NULL
AS
BEGIN
select *
from Employee with(noloCK)
where (@FirstName is null or  FirstName = @FirstName)
END

2) Using CASE Statement
CREATE PROCEDURE GetEmployeeDetailsUsingCASE
@FirstName VARCHAR(50)   = NULL
AS
BEGIN
select *
from Employee with(noloCK)
where FIRSTNAME = (CASE WHEN @FirstName is null THEN FIRSTNAME
ELSE @FirstName END)
END

3) Using ISNULL
CREATE PROCEDURE GetEmployeeDetailsUsingISNULL
@FirstName VARCHAR(50)   = NULL
AS
BEGIN
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = ISNULL(@FirstName,FIRSTNAME)
END

4) Using COALESCE
CREATE PROCEDURE GetEmployeeDetailsUsingCOALESCE
@FirstName VARCHAR(50)  = NULL
AS
BEGIN
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = COALESCE(@FirstName,FIRSTNAME)
END

Now we will verify the execution plan of all the stored procedures for the scenario where we want to get the details of all the employees whose FirstName is ‘FirstName4000’,  by using the statement listed below. In our employee table as we have index on the FirstName column and it has high selectivity, so ideally in this case it has to use Index Seek, but for our surprise it is using either Index/Table Scan.

EXEC GetEmployeeDetailsUsingOR ‘FirstName4000’
EXEC GetEmployeeDetailsUsingCASE ‘FirstName4000’
EXEC GetEmployeeDetailsUsingISNULL ‘FirstName4000’
EXEC GetEmployeeDetailsUsingCOALESCE ‘FirstName4000’

Below image provides IO details when these Stored Procedures are executed:

Workaround Solution’s:
There are multiple workaround solutions to this problem i.e. we can create separate stored procedures one with parameter and another without parameter, use if/else etc.

One of the workaround solution is use if/else as below:
CREATE PROCEDURE GetEmployeeDetails
@FirstName VARCHAR(50) = NULL
AS
BEGIN

IF @FirstName IS NULL
SELECT *
FROM Employee WITH(NOLOCK)
ELSE
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = @FirstName
END

Now let us see the execution plan and IO when we execute the this stored procedure. As expected it is now doing an index seek and IO’s are drastically reduced as shown in the below images:

Usage of Function on Index Column in WHERE Caluse Leads to Index/Table Scan

There are multiple scenarios where usage of function on indexed column leads to Index/Table Scan instead of Index seeks. In this article we will go through few such use case scenarios.

Script @ http://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and Employee table used in this article. Create two Non-Clustered index’s using the below script, one index on the FirstName Column and second index on the HireDate Column of the Employee Table:

Create INDEX IX_Employee_FirstName ON Employee(FirstName)
GO
Create INDEX IX_Employee_HireDate ON Employee(HireDate)
GO

Use Case Scenario:
Need to write a query which returns all the employees for a given HireDate. As we know the HireDate column in the Employee table is of DateTime DataType and the date stored in this column has time part also in it.
For the above use cases we can write query as below:

SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),HireDate,101) = ’05/16/2001′

Now let us see the execution plan and IO’s for the above query:

From the above execution plan it is clear that instead of doing index seek, our query is resulting in Index Scan even when we have index on the HireDate column.

Workaround Solution:
One of the workaround solution is to re-write this query without using function on the index column HireDate as below:
SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE HireDate BETWEEN ’05/16/2001 00:00:00′ AND ’05/16/2001 23:59:59:997′

Now let us see the execution plan and IO when we execute this query. As expected it is now doing an index seek and IO’s are drastically reduced as shown in the below images:

Implicit Conversion Leading to Index/Table Scan

In search predicate comparing column of lower data type to a value of higher data type leads to an implicit conversion being performed on a column of lower data type. This implicit conversion will result in index scan/table scan instead of index seek. This is one of the very common mistakes, which is very difficult to catch.

Script @ http://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and table used in this article.

Let us understand this with an example:

First create a Non-Clustered index on the FirstName Column of the Employee Table using the below statement if it doesn’t exists:
Create INDEX IX_Employee_FirstName ON Employee(FirstName)

Now Create a stored procedure like below:
CREATE PROCEDURE GetEmployeeByFirstName
@FirstName NVARCHAR(50)
AS
BEGIN
SELECT *
FROM Employee
WHERE FirstName = @FirstName
END

In the above stored procedure the @FirstName is defined as of NVARCHAR(50) data type, but the FirstName column in the Employee table is of VARCHAR(50). This mistake leads to the implicit conversion and results in index scan instead of index seek as shown in the below execution plan when we execute statement like: EXEC GetEmployeeByFirstName ‘FirstName4000’. Also see the IO’s and Time taken to execute

Workaround Solutions for this Problem:
One solution is to change the input parameter @FirstName to Varchar(50) which matches with FirstName column data type in the Employee table. Alter the stored procedure as below:

ALTER PROCEDURE GetEmployeeByFirstName
@FirstName VARCHAR(50)
AS
BEGIN
SELECT *
FROM Employee
WHERE FirstName = @FirstName
END

Another solution is to change the stored procedure as below:
ALTER PROCEDURE GetEmployeeByFirstName
@FirstName NVARCHAR(50)
AS
BEGIN
Declare @Name VARCHAR(50)
SET @Name = @FirstName

SELECT *
FROM Employee
WHERE FirstName = @Name
END

Above workarounds leads to the index seek as shown in the below execution plan. It also drastically reduced the IO’s and Execution time as shown in the below images: