Impact of Nonclustered Index without Clustered Index - SQL Server by serverku

We have read and learn so many times from online sources as it is not best practice to create a non-clustered index without any clustered index created on the table. We should have clustered index on the table.

Have you practically seen that best practice? What will be the impact on query when table have a non-clustered index but not clustered index?. Without a clustered index on the table the query execution plan sometime used non-clustered index and sometimes not as depends on where condition used in the query.

I will show you here, how it behaves with and without clustered index. We have a two shot of the show and will see this behavior with normal non-clustered index and additional covering non-clustered index. Let we created a sample table and get insert some records in it.

 
--- Creating tables
IF (OBJECT_ID('ItemDetails','U') > 0 )
 DROP TABLE ItemDetails
GO

CREATE TABLE ItemDetails
(
 ItemAutiId int identity(1,1),
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO

-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

As discussed, we will go through the following, Will review execution plan for each and compare.

1. Normal non-clustered index behavior without and with clustered index on table.

-- Creating nonclustered index
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

 
--Now creating a clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustered index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

You can see here first case did table scan and second case did clustered index scan. Now moving other shots,

2. Covering nonclustered index behavior without and with clustered index on table.

-- Dropping existing indexes on table
DROP INDEX [IX_ItemType] ON  [ItemDetails]
DROP INDEX [IX_ItemAutiId] ON  [ItemDetails]

GO

-- Creating covering index on table
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

-- Creating clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustred index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

In this scenario first case did table scan and second case did non-clustered index seek. Do you have any more idea about it if you experienced with it?

Missing index suggesion by Execution Plan - SQL Server by serverku

The index is a most important factor in the optimization and performance. Whenever we work for the query optimization, we are mostly looking for indexes, then check any other factors. Also scheduling the job for the maintenance of the indexes on periodical basis. From Index usage and statistics report, we can have an idea for the index optimization. But SQL Server query execution plan also recommended and give the suggestion with index hint. We will look here the demonstration for the same with execution plan.

--- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO


-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Review the execution plan.
SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
ON (it.ItemType = id.ItemType)
GO

You can see the index hint with the execution plan, and following is a script for same.

/*
Missing Index Details from SQLQuery.sql - ReportServer (dba (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.0255%.
*/

/*
USE [ReportServer]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO
*/

Apply it and enjoy!

Using Covering Index and Performance Review - a new Index type of SQL Server 2005 by serverku

We all know about the indexes and the concept of it. As well as the types of the indexes. During working one query optimization, I got suggested by the SQL Server Optimizer with index creation and that index was Covering index.

What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,

USE DEMO
GO

-- Creating table which will be used for demo

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

CREATE TABLE TblCoveringIndex
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in table 

INSERT INTO TblCoveringIndex
SELECT TOP 80000
convert(bigint,CONVERT(VARCHAR(100),a.object_id) + CONVERT(VARCHAR(100),b.object_id) ),
CONVERT(VARCHAR(100),a.name) + CONVERT(VARCHAR(100),b.name) ,
B.create_date,
b.type_Desc
FROM SYS.OBJECTS A
CROSS JOIN SYS.OBJECTS B

GO

Now we will review the execution plan without any indexes created on the table.

--  Execution plan with any indexes
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex
WHERE CreateDate >= GETDATE() - 50

GO

Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblCoveringIndex (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblCoveringIndex (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblCoveringIndex (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

We have created an additional non-clustered index with an included column, which are going to be used in the select list. After index's creation, It is finally time to review for each index created and execution plans of the queries using each of them. Here we force the query to use the normal non-clustered index and addition non-clustered index created.

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate)) 
-- Forcing index hint of normal nonclustered index
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate_Covering)) 
-- Forcing index hint of additional nonclustered index 
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

GO

(Clink on image to enlarge)

I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.

Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication by serverku

Few days back, I was working to add new table in a publication in transaction replication. After adding an article in a publication and after reinitialized, I see non-clustered index was not copied at subscriber database. I tried reinitialized one more time and had a same issue. Let me generate scenario and look forward to the solution after that,

USE [PublisherDB]
GO

CREATE TABLE [dbo].[EmpList]
(
 [EmpId] [int] NOT NULL PRIMARY KEY,
 [EmpName] [varchar](100) NULL,
 [EmpCity] varchar(100) NULL,
 [EmpAddress] [varchar](200) NULL
)

CREATE NONCLUSTERED INDEX IX_EmpList_Name ON [dbo].[EmpList] ([EmpName]) 

I added above created a table in publication and reinitialized subscription with new snapshot and check indexes of that table at both publisher and subscriber side,

EXEC sp_helpindex [EmpList]

Non-clustered is not copied to subscriber after new snapshot applied. After some workaround I found a solution. Make true for the option of copy nonclustered indexes from article property,
Go to Publication property –>  Articles tab –> Article properties –> Set Properties of Highlighted Table Article or Set Properties of All Table Articles and apply changes as following,

After applying changes above and new snapshot generated after that, it copied non clustered indexes to the subscriber database. There are some other options you can find there,

All existing non-clustered indexes copied after new snapshot applied, but I created one more non-clustered index on that table and it's not copied subscriber database without generating a new snapshot. That new index copied after only new snapshot applied of that table. So I am leaving one discussion open here, why that newly created non-clustered index not copied without generating a new snapshot? Any alternative way or recommends solution?

Moving table or indexes on another filegroup - SQL Server by serverku

Because of data growth and performance issue we need to move tables or indexes to other file groups. The purpose of moving tables or indexes to improve the database performance as it reduces I/O from single drive or single filegroup. We can create another filegroup and move the tables and indexes to new created filegroups, then it will spit the I/O to two different filegroups. This way it will improve performance.

When we need to move the tables or indexes to another filegroup? We should move those table needs to move which are having more rows or heavily in size, Also need to move if those table's data are frequently updated. Here I would like to show the demo for the same. Let us see how can we move tables/indexes to another filegroup.

-- First we are creating database  
-- Main primary file on Primary filegroup 
-- Secondary files on secondary filegroup. 
-- Log files on different file group. 
CREATE DATABASE filegroupdb 
ON PRIMARY 
 ( name = filegroupdb_data, 
 filename = 'C:\FileGroupDB_Data.mdf', 
 size = 4 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ), 
filegroup secfilegroup 
 ( name = filegroupdb_data_1, 
 filename = 'C:\FileGroupDB_Data_1.ndf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 
log ON 
 ( name = filegroupdb_log, 
 filename = 'C:\FileGroupDB_Log.ldf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 

GO 

After creating database we will create a new table on Primary filegroup. If you not mentioned filegroup name, then it will be created on default filegroup. Let us create it.

USE filegroupdb 
GO 

-- Creating table on Primary filegroup 
IF( Object_id('tblFileGroup', 'u') > 0 ) 
  DROP TABLE tblfilegroup 
GO 

CREATE TABLE tblfilegroup 
  ( 
     objectid   INT NOT NULL PRIMARY KEY, 
     objectname VARCHAR(100), 
     createdate DATETIME 
  ) 
ON [PRIMARY] 

GO

The table is created and now we look the property of the table where it is created,

SP_HELP tblFileGroup

Now we are coming to the point which is tables/indexes actually will be moved. There are two ways through we can move the tables and indexes.

#1. By moving clustered index
Using this way we just need to move clustered index and tables and all indexes will be moved to different filegroup.

How can with #1 way?

-- Defining dropping constraint with move
ALTER TABLE tblFileGroup
DROP CONSTRAINT PK__tblFileG__9A619291029E5EB6 WITH (MOVE TO SecFileGroup)
GO

-- Adding constraint
ALTER TABLE tblFileGroup 
ADD CONSTRAINT PK__tblFileG__9A619291029E5EB6 PRIMARY KEY(ObjectId)
GO

Let us check again the properties of tables as successfully moved or not.

SP_HELP tblFileGroup

#2. Recreate a clustered index with drop existing
We just need to recreate a table's clustered index with drop existing clustered index on that.

How can with #2 way?

CREATE UNIQUE CLUSTERED INDEX PK__tblFileG__9A619291029E5EB6
ON tblFileGroup(ObjectId)
   WITH DROP_EXISTING
ON SecFileGroup

GO

You also worked with moving tables/indexes to another location, Share your ideas here!

SQL Server Logical Reads - What's it? by serverku

Recently, when I was working on the tuning of stored procedures and I experienced with one performance issue, that was high Logical Reads.

Logical Reads?
"Number of pages read from the data cache" - It occurs every time when the database engine request a page from buffer cache, otherwise physical reads occurs if current page is not available in buffer cache. Let us go, through the sample demo and get experience for the logical reads. First, we need require objects, so we are creating database and tables inside it.

USE DEMO
GO

-- Creating a table
IF (OBJECT_ID('TblLogicalReads','U') > 0)
  DROP TABLE TblLogicalReads
GO

CREATE TABLE TblLogicalReads
(
 TranId INT,
 TrnData VARCHAR(100),
 TrnDate DATETIME
)

GO

After creating objects, 49999 records will be inserted by following scripts.

DECLARE @cnt BIGINT

SET @cnt  = 1

WHILE (@cnt < 50000)
BEGIN

INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END

GO

Now we are checking logical reads from the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

/* Output :

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 8 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 59 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 4 ms.
   
*/

You can see here, logical reads are high.

How can we reduce it?
There are many factors to reduce it as it depends on But here, for example, we need to create some required indexes on columns which are used in the queries.

-- Creating indexes on tables 
CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO

CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Finally we are on the stage where we need to review logical reads after creating indexes on tables

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

/* Output : 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/

Hope you understood the logical reads difference and performance impact from the examples given above. You can share your knowledge for the same.

Using force index hint - SQL Server by serverku

I wrote about my previous just learned tips about covering index and will publish my future post for the detail understanding it. We should generate index statistics and index usage report periodically, so we can have more idea of the index utilization. Sometime we require different indexes other than the query optimizer used for the execution for the best performance.

How can we use different indexes other that query optimizer used for execution? You can force the indexes to be used with Index Hint. Here is the small demonstration by example. Please look on below script and created objects for demo.

-- Creating objects
USE DEMO
GO

-- Creating table which will be used for demo

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

CREATE TABLE TblForceIndexHint
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

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

GO

Now creating one clustered index, Normal non-clustered index and an additional non-clustered index with include means covering index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblForceIndexHint (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblForceIndexHint (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblForceIndexHint (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

To review how force Indexes hint works, We need to get the data with CreateDate filtered. So let us start with there.

-- Keep query optimizer to decides and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE CreateDate >= GETDATE() - 50

Here you see the first query (by Query optimizer) and last query (forcing index hint) have used same index. Reviewing same index hints, but the query will get the data on ObjectId filter from where condition.

-- Keep query optimizer to decide and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE ObjectId >= 1000000

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE ObjectId >= 1000000

Here you see the first query (by Query optimizer) and third query (forcing index hint) have used same index. Please share your experience if you ever used force indexes hint.