pivot information: indexes

Showing posts with label indexes. Show all posts

Tuesday, March 20, 2012

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s.
Thanks
www.SQLporn.co.uk
This was the best case, especially in DB2. I would suggest that in SQL Server it is generally not the case, especially if you choose your storage system carefully. (such as raid 1+0)
|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s
Thank
www.SQLporn.co.ukIt's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables o
n a seperate datafile to the actual table? I.e. a file using a different dis
k/s.
Thanks
www.SQLporn.co.ukThis was the best case, especially in DB2. I would suggest that in SQL Serv
er it is generally not the case, especially if you choose your storage syste
m carefully. (such as raid 1+0)|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Friday, March 9, 2012

PK vs. Unique clustered indexes

Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between
>> having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the
>> tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is
>> drop
>> this index and recreated it as a primary key with the same columns and
>> name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>|||Good catch, Jacco!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
>> unique index (you have control over whether it is to be a clustered index or not).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
>> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is drop
>> this index and recreated it as a primary key with the same columns and name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>>
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> > There is one thing to look out for and that is that unique indexes can be on nullable columns
> > (although they only allow one NULL value), but primary keys can't. But if the columns in the
> > unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> > there isn't one already on the table.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> >> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
> >> unique index (you have control over whether it is to be a clustered index or not).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> >> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> >> Are there any differences, performance related or other wise, between having
> >> a clustered primary key vs. a clustered unique key?
> >>
> >> I'm am trying to ready a SQL2K server for replication. Some of the tables
> >> in database A don't have a primary key but they do have a cluster unique
> >> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> >> this index and recreated it as a primary key with the same columns and name.
> >> Would this pose any type of possible problem ?
> >>
> >> TIA,
> >> Billy
> >>
> >>
> >
> >
>
>

PK vs. Unique clustered indexes

PK still shows fragmentation after ALTER INDEX.. REBUILD

I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can’t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

SELECT object_id AS [Object_Id], CAST('' AS sysname) AS [Table_Nm], index_id AS [Index_Id], CAST('' AS sysname) AS [Index_Nm], avg_fragmentation_in_percent AS [Frag],0 AS [IndexRebuilt_Ind], GetDate() AS [Start_DtTm], GetDate() AS [End_DtTm], '00:00:00' AS [Duration]

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

IF ((SELECT MIN([Index_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id) = 1) AND ((SELECT COUNT([Object_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id AND [Frag] >= @.ip_Frag_Limit) > 0)

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

INSERT [DBA_IndexStatistics] SELECT [Object_Id], [Table_Nm], [Index_Id], [Index_Nm], [Frag], [IndexRebuilt_Ind], [Start_DtTm], [End_DtTm], [Duration] FROM #Tmp_Index_Stats WHERE [IndexRebuilt_Ind] = 1 ORDER BY [Duration] DESC

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

|||No, I realized that. If you look at the code block for the Clustered Indexes, there is a line commented out that runs the ALTER INDEX ALL ON... However, this still shows that the Primary Key Clustered Indexes retain the same fragmentation levels.

PK still shows fragmentation after ALTER INDEX.. REBUILD

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

PK still shows fragmentation after ALTER INDEX.. REBUILD

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

Wednesday, March 7, 2012

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clusterin
g
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:

> I have application that I inherited. Looks to me the indexes where set u
p
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys tha
t
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:

> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

Tuesday, March 20, 2012

Placing of Indexes on seperate data file

Placing of Indexes on seperate data file

Placing of Indexes on seperate data file

Friday, March 9, 2012

PK vs. Unique clustered indexes

PK vs. Unique clustered indexes

PK vs. Unique clustered indexes

PK still shows fragmentation after ALTER INDEX.. REBUILD

PK still shows fragmentation after ALTER INDEX.. REBUILD

PK still shows fragmentation after ALTER INDEX.. REBUILD

Wednesday, March 7, 2012

PK Index Cluster/Non Cluster

PK Index Cluster/Non Cluster

PK Index Cluster/Non Cluster

pivot information

Blog Archive

About Me