Sql server Blog Forum Helping SQL server DBAs and Developers

9Mar/160

How important is clustered index for a table

 

I am not sure, how to name this topic. May be, start by following questions.

 

How important and effective is clustered index for a table?

How to solve the heap fragmentation?

Why my query is running slow most of the time for this particular table?

Are the primary key and clustered index are same?

 

There are developers still they understand that, we must need a primary key to create a clustered index. No, that’s not. Both are different used for different purpose.

The primary key is to enforce the business logic. I.e. enforce the uniqueness. By default it will create a clustered index, if we are not changing manually to non-clustered.

You can create a clustered index without a primary key. In two ways, create [unique] clustered index. The unique is an optional keyword.

A heap table is “without a clustered index is called heap”

 

Coming into the picture.

Recently a developer came to me for an application slowness. The query is in more environment (QA, DEV & Prod) each run differently. DBAs know where to start analysis the query.

 

I cannot show all the codes. But it’s a simple search character code like ‘’ with more conditions.

The table has massive fragmentation. Since, it’s a heap, but small. A 50K data table, with a simple query runs more than 5 minutes to return the data.

1. After a de-fragmentation only of all non-clustered indexes, it still runs 1 minute to return the data.

2. After a clustered index creation, it runs boom, less than a second. This is a best practice and the table has clustered index will be good and it enforce the logical ordering and every record in a non-clustered index has to link to the clustering index key.

Think about the heap it an unordered data, so it needs travel all the pages to get data. Probably, a full scan. But clustered index will be ordered logically with matching of index columns and it’s easy to get the data by clustering key.

From 2008 onwards, the same result we can get by rebuilding the table ALTER TABLE REBUILD. But internally it changes the heap records and ask all non-clustered indexes to rebuild it to match with rebuilt heap, for a big table it is worse.

SELECT OBJECT_NAME(F.OBJECT_ID) obj,i.name ind,
f.avg_fragmentation_in_percent,
f.page_count,table_schema,
avg_page_space_used_in_percent, forwarded_record_count
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('Regression'),NULL,NULL,NULL,null) F
JOIN SYS.INDEXES I
ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id
JOIN INFORMATION_SCHEMA.TABLES S
ON (s.table_name=OBJECT_NAME(F.OBJECT_ID))
WHERE f.avg_fragmentation_in_percent>15 and f.page_count>=1000
AND f.database_id=DB_ID('Regression')
AND OBJECTPROPERTY(I.OBJECT_ID,'ISSYSTEMTABLE')=0
order by f.avg_fragmentation_in_percent desc

 

Before clustered index creation: IX_mtProduct_ProductID & heap has 2372493 pages

Table Index Fragmentation Page count
mtProduct IX_mtProduct_Description

0.342466

292

mtProduct IX_mtProduct_Mat_Seg_Desc

97.4359

819

mtProduct IX_mtProduct_MaterialType

96.81529

471

mtProduct IX_mtProduct_MCG

98.17987

934

mtProduct IX_mtProduct_ProdType

92.49423

866

mtProduct IX_mtProduct_ProductFamily

96.52568

662

mtProduct IX_mtProduct_ProductID

99.07121

646

mtProduct IX_mtProduct_ProductLine

98.75

880

mtProduct NULL

11.40322

2372493

 

After clustered index creation: CX_mtProduct_ProductID Clustered index has 1954 pages

Table Index Fragmentation Page count
mtProduct IX_mtProduct_Description

0.869565217

345

mtProduct IX_mtProduct_Mat_Seg_Desc

1.604278075

187

mtProduct IX_mtProduct_MaterialType

0.591715976

169

mtProduct IX_mtProduct_MCG

1.754385965

171

mtProduct IX_mtProduct_ProdType

2.580645161

310

mtProduct IX_mtProduct_ProductFamily

1.435406699

209

mtProduct CX_mtProduct_ProductID

0.204708291

1954

mtProduct IX_mtProduct_ProductLine

1.369863014

219

 

Now, see the number pages for heap- 2372493 and clustered index - 1954 and it can change over a time, when there is a modification but, not like a heap.

It's always good to have a clustered index to search the record effectively.

Hope now you have understood the effectiveness of the clustered index and we should create a clustered index, when there is a candidate column available or sometimes create a dumpy column for the very big table. Generally a Surrogate key – Ex: IDENTITY.

 

There are already many posts available to choose the cluster index.

http://www.sqlskills.com/blogs/kimberly/ever-increasing-clustering-key-the-clustered-index-debate-again/

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

 

 

 

 

 

 

Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts

12Mar/11Off

INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

 

INDEX DEFRAGMENTATION SCRIPT SQL 2000

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

Use the DBCC SHOWCONTIG statement. It will display the fragmentation information.

 

Here I used 3 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild%

Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts

23Nov/109

INDEX DEFRAGMENTATION SCRIPT

 

INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005

I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script.

I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC).

For VLDB - Please use Ola Hallengren's script https://ola.hallengren.com/, Which has many parameters.

Script changes:

·         I used while loop instead of a cursor.
·         I used to rebuild the index if the page level locking is disabled.

Msg 2552, Level 16, State 2, Line 1
The index "index name" (partition 1) on table "table name” cannot be reorganized because page level locking is disabled

 

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

If you want to determine the level of fragmentation, you can use the SYS.DM_DB_INDEX_PHYSICAL_STATS statement. The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object.

Here I used 4 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
3. If the page level locking is disabled then rebuild
4. If the above conditions are false and statistics are outdated more than 10 days then update the statistics

Before you run the procedure create the tables provided for history propose

Read Gail Shaw's post 'What is fragmentation' and how it will affect the performance http://sqlinthewild.co.za/index.php/2008/10/20/what-is-fragmentation/

 

-- For SQL-2005/2008
USE MSDB;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go
--Archive the data's in master DB
USE MASTER;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[indexdefragmentation]    Script Date: 09/07/2012 05:18:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--drop proc [indexdefragmentation]
CREATE PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary:     Remove the Index Fragmentation to improve the query performance
Contact:        Muthukkumaran Kaliyamoorhty SQL DBA
Description:   This Sproc will take the fragmentation details and do four kinds of work.

1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize & update stats
3. Check the fragmentation between 5% to 30% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the above conditions are false
/*statistics will be updated if its gets 10 days older */
ChangeLog:
Date              Coder                                                             Description
2012-sep-7        Muthukkumaran Kaliyamoorhty                        created
2012-sep-8        Muthukkumaran Kaliyamoorhty                        updates                Stats date    
*************************All the SQL keywords should be written in upper case*************************
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT,
@stats_date VARCHAR(8)

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schemaName SYSNAME,
statsDate VARCHAR(8)
)            

EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schemaName,statsDate)
SELECT OBJECT_NAME(F.OBJECT_ID) obj,i.name ind,
f.avg_fragmentation_in_percent,
f.page_count,OBJECT_SCHEMA_NAME(f.object_id),
CONVERT(VARCHAR(8),STATS_DATE(I.OBJECT_ID,I.index_id),112)as statsdate
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F
JOIN SYS.INDEXES I
ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id
JOIN INFORMATION_SCHEMA.TABLES S
ON (s.table_name=OBJECT_NAME(F.OBJECT_ID))
AND f.database_id=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0
AND alloc_unit_type_desc = ''IN_ROW_DATA''
AND is_disabled = 0
'

)
SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag
TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

--SELECT * FROM #tempfrag

WHILE (@min_id<=@max_id)
/*While loop begin*/
BEGIN
SELECT
@tab_name=table_name,
@schema_name=schemaname,
@ind_name=index_name ,
@frag=frag ,
@pages=pages,
@stats_date=statsDate
FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
/*First if condition*/
BEGIN
IF (@frag>30 AND @pages>1000)
/*Sub if condition 1st */
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',@stats_date,GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 5 AND 30) AND @pages>1000  )
/*Sub if condition 2nd */
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
IF (@stats_date <CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',@stats_date,GETDATE())
END
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',@stats_date,GETDATE())
END CATCH
END

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
/*Sub if condition 3rd*/
ELSE
BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) '  )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
END
END
ELSE

BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
/*End of the first if condition*/
END

SET @min_id=@min_id+1
/*While loop end*/
END

DROP TABLE #tempfrag
INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history
END
GO

Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts