Index seek vs scan - Microsoft SQL 2008

Table  Creation:-

/****** Script for no Index ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_NI
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for Unique Cluster Index  with Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_UCI_IC
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for Unique Cluster Index  without Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_UCI
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for Unique NonCluster Index  with Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_UNCI_IC
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for Unique NonCluster Index  without Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_UNCI
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for NonCluster Index  with Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_NCI_IC
FROM [AdventureWorks2008R2].[Person].[Person]

/****** Script for NonCluster Index  without Include Colume ******/
SELECT [BusinessEntityID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName] INTO dbo.Person_NCI
FROM [AdventureWorks2008R2].[Person].[Person]


Index Creation:-

CREATE UNIQUE CLUSTERED INDEX Person_UCI_BusinessEntityID ON
      dbo.[Person_UCI]([BusinessEntityID]);
GO

CREATE CLUSTERED INDEX Person_CI_BusinessEntityID ON
      dbo.[Person_CI]([BusinessEntityID]);
GO

CREATE UNIQUE NONCLUSTERED INDEX Person_UNCI_BusinessEntityID ON
      dbo.[Person_UNCI]([BusinessEntityID]);
GO

CREATE NONCLUSTERED INDEX Person_NCI_BusinessEntityID ON
      dbo.[Person_NCI]([BusinessEntityID]);
GO

CREATE UNIQUE NONCLUSTERED INDEX Person_UNCI_IC_BusinessEntityID ON
      dbo.[Person_UNCI_IC]([BusinessEntityID]) INCLUDE (FirstName);
GO

CREATE NONCLUSTERED INDEX Person_NCI_IC_BusinessEntityID ON
      dbo.[Person_NCI_IC]([BusinessEntityID]) INCLUDE (FirstName);
GO

Query Execution Plan:-

 1. Clustered Index Seek

Clustered Index Seek
 2. Clustered Index Scan & Table Scan

Clustered Index Scan & Table Scan

 3.Non Clustered Index Seek

Non Clustered Index Seek
 4.Non Clustered Index Seek


Non Clustered Index Seek
 5.Table Scan


Table Scan
 6.Non Clustered Index Scan


Non Clustered Index Scan








Few nice tools for Microsoft SQL DBA

1.  http://sqlspacemap.codeplex.com/

SQL Space Map is a tool that lets you quickly and easily view the relative size of database objects on a map, so you can compare them visually. It helps identify large objects, objects with a high index/data ratio, and tables which may be growing more quickly than expected.



2.  http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

SQL Sentry Plan Explorer builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6, and does not require a collector service or database. Features include color-coding that highlights potential trouble areas, multiple grids and other views to allow for sorting and deeper analysis, and collection of real runtime metrics with the generation of actual plans from within the tool.


3.  http://pal.codeplex.com/

Ever have a performance problem, but don't know what performance counters to collect or how to analyze them? The PAL (Performance Analysis of Logs) tool is a powerful tool that reads in a performance monitor counter log and analyzes it using known thresholds.



SQL Server Backup, Integrity Check, and Index and Statistics Maintenance Script.
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 20122011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.