April 2, 2015 at 5:41 am
Hi,
I required to have an automation script which will generate a script to alter all non-clustered indexes to clustered index for performance improvement. I am on SQL Server 8.00.760 (SP3). Will it be possible?
Also to mention the non-clustered indexes are associated with primary keys of the base tables. Hence to alter them the primary keys need to be dropped along with the foreign keys associated with them and recreate those with 'CLUSTERED' option and foreign keys afterward.
While Google-ing I found a script whose purpose is reverse (clustered to non-clustered) and written on SQL Server 2012. Hence not working on SQL Server 8. 🙁
/***************************************************************************************************************
*
* This Script will perform the following operations:
* 1) Drop All RI
* 2) Drop All PKEYS
* 3) Recreate PKEYS as NONCLUSTERED
* 4) Recreate ALL RI
*
* To do this it will create a work table and populate it with the commands to execute then iterate through
* the work table to execute pregenerated commands.
*
***************************************************************************************************************/
CREATE TABLE #workTable
(
CommandID INT IDENTITY(1,1),
Command VARCHAR(MAX)
)
SET NOCOUNT ON;
/***************************************************************************************************************
*
* DROP FOREIGN KEYS
*
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS --''');
INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + fk.NAME + ']'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
/***************************************************************************************************************
*
* DROP CLUSTERED PKEYS
*
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS --''');
DECLARE @object_id int;
DECLARE @parent_object_id int;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key bit;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);
SET @action = 'DROP';
--SET @action = 'CREATE';
DECLARE PKcursor CURSOR FOR
select kc.object_id, kc.parent_object_id
from sys.key_constraints kc
inner join sys.objects o
on kc.parent_object_id = o.object_id
where kc.type = 'PK' and o.type = 'U'
and o.name not in ('dtproperties','sysdiagrams') -- not true user tables
order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
,QUOTENAME(OBJECT_NAME(kc.parent_object_id));
OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @action = 'DROP'
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
ELSE
BEGIN
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
+ ' PRIMARY KEY'
+ CASE INDEXPROPERTY(@parent_object_id
,OBJECT_NAME(@object_id),'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END
+ ' (';
DECLARE ColumnCursor CURSOR FOR
select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
from sys.indexes i
inner join sys.index_columns ic
on i.object_id = ic.object_id and i.index_id = ic.index_id
where i.object_id = @parent_object_id
and i.name = OBJECT_NAME(@object_id)
order by ic.key_ordinal;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
SET @TSQL = @TSQL + ',';
SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
+ ' '
+ CASE @is_descending_key
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @TSQL = @TSQL + ');';
END;
INSERT INTO #workTable (Command)
SELECT @TSQL;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;
CLOSE PKcursor;
DEALLOCATE PKcursor;
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
/***************************************************************************************************************
*
* CREATE NONCLUSTERED PKEYS
*
*
* NOTE: Subsection where script determines if PKEY or not was modified to only create NONCLUSTERED pkeys
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS --''');
--SET @action = 'DROP';
SET @action = 'CREATE';
DECLARE PKcursor CURSOR FOR
select kc.object_id, kc.parent_object_id
from sys.key_constraints kc
inner join sys.objects o
on kc.parent_object_id = o.object_id
where kc.type = 'PK' and o.type = 'U'
and o.name not in ('dtproperties','sysdiagrams') -- not true user tables
order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
,QUOTENAME(OBJECT_NAME(kc.parent_object_id));
OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @action = 'DROP'
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
ELSE
BEGIN
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
+ ' PRIMARY KEY'
+ ' NONCLUSTERED'
+ ' (';
DECLARE ColumnCursor CURSOR FOR
select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
from sys.indexes i
inner join sys.index_columns ic
on i.object_id = ic.object_id and i.index_id = ic.index_id
where i.object_id = @parent_object_id
and i.name = OBJECT_NAME(@object_id)
order by ic.key_ordinal;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
SET @TSQL = @TSQL + ',';
SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
+ ' '
+ CASE @is_descending_key
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @TSQL = @TSQL + ');';
END;
INSERT INTO #workTable (Command)
SELECT @TSQL;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;
CLOSE PKcursor;
DEALLOCATE PKcursor;
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
/***************************************************************************************************************
*
* CREATE FOREIGN KEYS
*
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS --''');
INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')'
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
SET NOCOUNT OFF;
/***************************************************************************************************************
*
* Iterate through Work Table on the database
*
***************************************************************************************************************/
--SELECT * FROM #workTable
DECLARE @Command VARCHAR(MAX)
DECLARE WorkTableCursor CURSOR
FOR
SELECT Command
FROM #workTable
OPEN WorkTableCursor
FETCH NEXT FROM WorkTableCursor
INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Command
EXECUTE (@Command)
FETCH NEXT FROM WorkTableCursor
INTO @Command
END
CLOSE WorkTableCursor
DEALLOCATE WorkTableCursor
DROP TABLE #workTable
Any help is highly appreciated.
Thanks
April 2, 2015 at 6:57 am
since you can only have ONE clustered index on a table, your idea will fail for any table with more than one index.
performance will not automagically improve due to a clustered index, so you should throw this idea out the window right away.
the right thing to do is to dig in and find out what the performance problems are...start with using SSMS: in object exporter, right click on a server and choose "Activity Monitor"
you can see if the CPU's are busy with the neat graphics at the top,a dn there's a panel dedicated to "Recent Expensive Queries",
and try to tune any of the ones you see there.
Lowell
April 2, 2015 at 7:06 am
Lowell (4/2/2015)
since you can only have ONE clustered index on a table, your idea will fail for any table with more than one index.performance will not automagically improve due to a clustered index, so you should throw this idea out the window right away.
the right thing to do is to dig in and find out what the performance problems are...start with using SSMS: in object exporter, right click on a server and choose "Activity Monitor"
you can see if the CPU's are busy with the neat graphics at the top,a dn there's a panel dedicated to "Recent Expensive Queries",
and try to tune any of the ones you see there.
Thanks for your suggestion. But I am very new to SQL Server administration and from DBA team I have been told to alter non-clustered indexes to clustered for a list of tables.
:(:(
April 2, 2015 at 7:11 am
sudip.k.datta (4/2/2015)
But I am very new to SQL Server administration and from DBA team I have been told to alter non-clustered indexes to clustered for a list of tables.
Go back to them, tell them that you're new to DBA work and need a hand from them to finish the job they gave you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2015 at 7:13 am
Lowell (4/2/2015)
start with using SSMS: in object exporter, right click on a server and choose "Activity Monitor"
SQL Server 8.00.76 is SQL Server 2000 RTM (no service packs). Enterprise Manager is the admin tool.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply