SQL Server: Execution Plans

When I started using SQL Server, I was not using the “Show Execution Plan” properly to analyse the query. I always thought that when I compose a query, it is the best it can be; I never even think of performance on my query and I did not give much importance to looking at the “Execution Plan” on my query. In the initial days of my career, I was happy to retrieve the data, not even knowing how it was returned to me and what sort of “Execution Plan” was used on the query. I presumed SQL Server would handle the query performance. I think it is the nature for software engineers, when starting out, or when first learning new technology, not to take the time to learn everything they really need to know before writing a code. Perhaps this is because of competition and immaturity in the IT field.

Days are starting moving and data has been growing on the database file. On one fine day, my customer was not happy with the performance on the query using the application. He has come to me with an unhappy face stating that he was spending extra time to finish his office work due to the slowness of the queries. Initially, I told the customer to increase the system resources, like increase the hard disk in the machine as a temporary solution. Albeit, hard disk cost is cheap but it is not the permanent solution for the performance degradation in the query. He agreed in half-mind that he will do the required things from his side but he asked me to re-look and fine-tune the queries for a permanent solution instead of suggesting he keep on increasing the system resources. I have to consider his personal opinion because customer satisfaction is important in the IT industry. I have promised to my customer that I will re-look and fine-tune the queries.

HOW?

In the initial days of my career, I know the basic things in MS-SQL Server. To be frank, I was not having any idea on my mind whilst doing promise to my customer. But, I personally felt that I would do something to achieve the task with the help of “GOOGLE” and “BOL”. Thanks to “GOOGLE” and “BOL”.

I was reading MS-SQL books, BOL help and searching on websites. I have heard and crossed the concept of “Show Execution Plan”. We can set this option ON using SQL Query Analyzer. Show Execution Plan is an important graphical tool that enables the developer and DBA to analyse, assist and optimise the query and improve the performance of the query.

Show Execution Plan displays different icons for a different task. I am mainly interested on “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” icons in this article. May be I could be writing in my future article on other icons.

As the days and years have moved along like a formula 1 race car, I have decided that it is time for me to fully understand how “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “ClusteredIndex Seek” icons work.

Now, I am ready to analyse the queries and upgrade the performance on my queries. Before analysing the queries, a few questions have been raised in my mind.

When did MS-SQL Server use “Table Scan”?

When did MS-SQL Server use “Index Scan”?

When did MS-SQL Server use “Index Seek”?

When did MS-SQL Server use “Clustered Index Scan”?

When did MS-SQL Server use “Clustered Index Seek”?

I am mainly concern that in what bases MS-SQL Server would use one of this option to analyse the query. I now have taken that time, and here is what I learned. This information should be useful to new developers and DBAs. I have decided to write this article to share my knowledge to help other to get better idea on these methods.

If you like, you can read this article as is, or in front of a SQL Server, following along with my exercises.

DependencyDetails

This sp will return the dependency details of the select table or all tables as a resultset. This resultset will
display the dependency details if it is used in
1. select * from table or
2. insert/delete/update dml statement or
3. select col1, col2 from table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[DependencyDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DependencyDetails]
GO

Create Proc DependencyDetails (@TableName As Varchar(100)='')
As

SET NOCOUNT ON

Declare @MinTableID Int, @MaxTableID Int

IF Len(@TableName) = 0
Begin
Select @MinTableID = Min(ID), @MaxTableID = Max(ID) From SysObjects Where xType = 'U'
End
Else IF Len(@TableName) > 0
Begin
IF Exists (Select ID From SysObjects Where Name = @TableName And xType = 'U')
Begin
Select @MinTableID = ID, @MaxTableID = ID
From SysObjects Where Name = @TableName And xType = 'U'
End
Else
Begin
Print 'Passed parameter [' + @TableName + '] is not an User Table'
Return 0
End
End
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Select *' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SelAll = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Insert/Update/Delete' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ResultObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Selected Columns' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ReadObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Order By SO.Name

SET NOCOUNT OFF

Replace Object Owner

This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects. SP should available in "master" database to execute in more than one database.

--$History: $ sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- Drop Procedure
--------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_ReplaceObjectOwner' AND type = 'P')
DROP PROCEDURE sp_ReplaceObjectOwner
GO

--------------------------------------------------------------------------------------------------------------------------------------------
-- Create procedure
--------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROC sp_ReplaceObjectOwner As

--------------------------------------------------------------------------------------------------------------------------------------------
-- OBJECT NAME: sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- AUTHOR: Vijayakumar G
--------------------------------------------------------------------------------------------------------------------------------------------
-- CREATED: 04-04-2005
--------------------------------------------------------------------------------------------------------------------------------------------
/* DESCRIPTION: This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects.
SP should available in "master" database to execute in more than one database.
1. Table
2. Stored Procedures
3. Function
4. View
We can later add / modify the filter condition */
--------------------------------------------------------------------------------------------------------------------------------------------
-- PARAMETERS: None
--
--------------------------------------------------------------------------------------------------------------------------------------------
-- RETURNS: 0 - success
--
-- returns from SQL Server
--------------------------------------------------------------------------------------------------------------------------------------------

-- VERSION: $Revision: $ UPDATED: $Modtime: $
--------------------------------------------------------------------------------------------------------------------------------------------
set nocount on

DECLARE @Object_ID Int, @Object_Name Sysname,
@Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname

Declare @New_Owner_Name Sysname
Set @New_Owner_Name = 'dbo'
DECLARE CUR_ReplaceObjectOwner CURSOR FOR

Select
SO.ID As "Object ID", SO.Name As "Object Name",
SO.UID As "Owner ID", SU.Name As "Owner Name",
'[' + SU.Name + '].' + '[' + SO.Name + ']' As "Owner + Object Name"
From
SysObjects SO, SysUsers SU
Where
SO.UID = SU.UID And
SO.xType In ('U','P', 'FN','V') And
SO.Name Not Like '%dt_%' And
SO.Name Not In ('syssegments', 'sysconstraints')
Order By xType


OPEN CUR_ReplaceObjectOwner

FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
If @New_Owner_Name != @Owner_Name
exec sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name
If @@Error != 0
Begin
Return 1
End
-- Get the next author.
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END

CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner

set nocount off
return 0

GO