Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Which Indexes will be used?

Aug 6 2011 12:00AM by Paresh Prajapati   

During the performance issues of database servers and database itself, one of my activity to improve performance of the Indexes. I have created some of the clustered indexes on tables which have only nonclustered indexes and also on some heap tables. Also i have created some additional nonclustered indexes  called as Covering indexes and some of having composite indexes.

After creating or having so many indexes on tables, it is real time to check the usage of them as which indexes are used by execution plans or query plans among them.

We should review the indexes statistics and usage report of all the indexes of tables as how they are seek, scanned and update. Let us pick one example and will create one tables and some of the indexes as well.
USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblIndexUsed','U') > 0)
DROP TABLE TblIndexUsed

CREATE TABLE TblIndexUsed
(
Id bigint identity(1,1),
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

INSERT INTO TblIndexUsed
SELECT
object_id,
name,
create_date,
type_Desc
FROM SYS.OBJECTS

GO 10000
Now we are creating one clustered index, Normal nonclustered indexes, We are not creating additional nonclustered index not.
-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_UObjectIdDate ON TblIndexUsed (Id,CreateDate)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UId on TblIndexUsed (Id)

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UCreateDate on TblIndexUsed (CreateDate)
GO
Now we are running the below query to check the usage of above created index on table.
SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE  ID >=30000  and CreateDate >= GETDATE() - 50 

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 and  ID >=30000  

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE ID >= 30000 

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 


(Click on image to enlarge)

Once again we are creating more indexes, but they are additional covering indexes.
-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UId_Covering ON TblIndexUsed (Id) 
INCLUDE (ObjectId,ObjectName,CreateDate,ObjectType)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UCreateDate_Covering ON TblIndexUsed (CreateDate) INCLUDE (Id,ObjectId,ObjectName,ObjectType)
GO
After creating covering indexes we are reviewing execution plans again for the same above queries.
SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE  ID >=30000  and CreateDate >= GETDATE() - 50 

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 and  ID >=30000  

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE ID >= 30000 

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 


(Click on image to enlarge)

You can see here the difference between both of the execution plans and impact of indexes on queries and the indexes usage for the same.

Tags: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, index, #SQL Server, #sql, sql server 2011, database, SQL Scripts, query,


Paresh Prajapati
6 · 23% · 7444
4 Readers Liked this
nadabadan Liked this on 8/16/2011 8:49:00 AM
Profile
Paresh Prajapati Liked this on 8/16/2011 7:08:00 AM
Profile · Blog · Facebook · Twitter
Nirav Liked this on 3/28/2012 12:46:00 AM
Profile · Blog · Facebook · Twitter
kavan dhruv Liked this on 11/12/2012 4:26:00 AM
Profile · Facebook · Twitter
4
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Which Indexes will be used?" rated 5 out of 5 by 4 readers
SQL Server - Which Indexes will be used? , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]