May 19, 2009 at 12:21 pm
Need a Defragment Script with out using Cursors.
May 19, 2009 at 12:38 pm
What's the problem with the cursor? In my book cursors are okay for these maintenance tasks.
Anyway, you can use a string concatenation or FOR XML PATH() in combination with sys.tables.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 19, 2009 at 12:42 pm
pradyothana (5/19/2009)
Need a Defragment Script with out using Cursors.
:w00t: Is perhaps Microsoft licensing "cursors" as a non-standard option now?
Don't put ideas like that one in their heads! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 19, 2009 at 12:47 pm
May 19, 2009 at 1:05 pm
For what??????????????????:w00t:
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 19, 2009 at 1:34 pm
Well... This is definitely a new chapter of "Coding for Fun" 😀
Compare and take your choice..
[font="Courier New"]SET NOCOUNT ON
DECLARE @now DATETIME
DECLARE @i INT
DECLARE @count INT
DECLARE @name NVARCHAR(128)
DECLARE @schema NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)
DECLARE @table_count INT
---====================================
-- Run each approach @count times
SELECT
@count = 100
SELECT @table_count = COUNT(*) FROM sys.tables
PRINT ('Table count: ' + CONVERT(VARCHAR(20), @table_count) + ' iterations: ' + CONVERT(VARCHAR(20), @count))
---====================================
-- Start the CURSOR test
SELECT
@now = GETDATE(),
@i = 1
WHILE @i <= @count
BEGIN
SELECT @sql = NULL
DECLARE c CURSOR FOR SELECT name, SCHEMA_NAME(schema_id) FROM sys.tables
OPEN c
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM c INTO @name, @schema
IF (@@FETCH_STATUS != 0)
BREAK
SELECT @sql = N'ALTER INDEX ALL ON ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@name) + N' REORGANIZE'
END
CLOSE c
DEALLOCATE c
SELECT @i = @i + 1
END
PRINT ('Cursor: ' +
CONVERT(NVARCHAR(30), DATEDIFF(SECOND, @now, GETDATE())) + 's ' +
CONVERT(NVARCHAR(30), DATEDIFF(MILLISECOND, @now, GETDATE())) + 'ms')
---====================================
-- FOR XML PATH
SELECT
@now = GETDATE(),
@i = 1
WHILE @i <= @count
BEGIN
SELECT @sql = NULL
SELECT @sql =
STUFF(
(SELECT NCHAR(10) + N'ALTER INDEX ALL ON ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N' REORGANIZE'
FROM sys.tables
FOR XML PATH('')),
1, 1, '')
SELECT @i = @i + 1
END
PRINT ('FOR XML: ' +
CONVERT(NVARCHAR(30), DATEDIFF(SECOND, @now, GETDATE())) + 's ' +
CONVERT(NVARCHAR(30), DATEDIFF(MILLISECOND, @now, GETDATE())) + 'ms')
---====================================
-- String concatenation
SELECT
@now = GETDATE(),
@i = 1
WHILE @i <= @count
BEGIN
SELECT @sql = NULL
SELECT @sql = ISNULL(@sql, '') + NCHAR(10) + N'ALTER INDEX ALL ON ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N' REORGANIZE'
FROM sys.tables
SELECT @i = @i + 1
END
PRINT ('String Concatenation: ' +
CONVERT(NVARCHAR(30), DATEDIFF(SECOND, @now, GETDATE())) + 's ' +
CONVERT(NVARCHAR(30), DATEDIFF(MILLISECOND, @now, GETDATE())) + 'ms')
[/font]
:hehe::hehe::hehe:
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 19, 2009 at 1:39 pm
Since this question was asked in the SQL 2005 forum then ALTER INDEX is a better choice to defrag indexes as DBCC INDEXDEFRAG is deprecated.
Francis
May 19, 2009 at 1:48 pm
Oups, sorry. Too many SSE2k in my environment yet...
I corrected the previous script and added schema-support and security for names 😉
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply