October 20, 2011 at 5:16 am
Hi all,
I'm trying to find a way to reliably rebuild all indexes on all tables within a given database, in order to reduce the fragmentation of the databases. I have 42 indexes with the avg_fragmentation_in_percent greater than 0 for the database (dbid 27). Of these, 15 range from 50% to 98.24% fragmentation and there is a mixture of types, i.e. clustered, non-clustered and heaps (non-indexes) are also for some reason dotted about too.
Now, my first attempt was to use a script, below.
DROP PROCEDURE dbo.reindex_all
GO
DEALLOCATE foo
GO
Then execute the following:
CREATE PROCEDURE dbo.reindex_all @database VARCHAR(MAX)
AS
DECLARE foo CURSOR FOR
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, database_id) SchemaName,
OBJECT_NAME(object_id, database_id) TableName
FROM sys.dm_db_index_physical_stats(27,NULL,NULL,NULL,NULL)
FOR READ ONLY
DECLARE @table SYSNAME, @schema SYSNAME
OPEN foo
FETCH foo INTO @schema, @table
DECLARE @cmd NVARCHAR(MAX)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @schema + '.' + @table + ' REBUILD WITH (ONLINE=OFF)'
EXEC sp_executesql @cmd
FETCH foo INTO @schema, @table
END
CLOSE foo
DEALLOCATE foo
Then execute the following:
USE [master]
EXEC reindex_all '<database name>'
Now, this worked on some databases but the one I was hoping for, not. It received an error that I have a theory was generated from the way the index name or metadata was parsed by the code, perhaps an escape character was parsed incorrectly:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.3441'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
However, this procedure and query works perfectly well with other DBs (I even ran it on AdventureWorks!)
It's worthwhile noting that there was actually a small improvement on the fragmentation, in single figures, on some of the indexes.
Anyway, I took another approach and used SSMS to create a new job in the Agent to 'Reindex Database'. This is a point-and-click affair and when I had a look at the T-SQL generated for it (too long to list here), it effectively created an ALTER INDEX .. REBUILD ... WITH ONLINE = OFF for each individual index on every table. Great, I think.
So I took a dump of sys.dm_db_index_physical_stats for DBID = 27 before and after running the SSMS reindex job, and I ran SQL Profiler to capture a trace of all SQL statements started and ended using my session ID during the period of the job. (Again too long to list here).
So it ran, completed successfully, I dumped the data into Excel and formatted it a bit and found ... not a single bit of difference. The deltas for every single index were nil. The indexing procedure that SSMS provided had, in fact, done ... nothing.
Checked the error logs. Answer came there none.
Has anyone seen this error before? Have they created a more reliable script than mine to reindex databases? Or am I overlooking something?
Many thanks in advance.
🙂
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 20, 2011 at 5:22 am
No point in reinventing the wheel.
October 20, 2011 at 5:23 am
404 error, could you check the link please?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 20, 2011 at 5:34 am
Just search Michelle's site a bit. You'll find this[/url].
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 20, 2011 at 5:39 am
derek.colley (10/20/2011)
404 error, could you check the link please?
Mad bad, fixed the original url
October 20, 2011 at 5:39 am
Marvellous work there. Thanks Grant & Ninja 😎
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 20, 2011 at 5:41 am
HTH, all I did use use the script and like it. Michelle actually wrote it! 😉
October 20, 2011 at 5:55 am
Ninja's_RGR'us (10/20/2011)
HTH, all I did use use the script and like it. Michelle actually wrote it! 😉
ditto
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 20, 2011 at 7:26 am
Just for your info, the reason your script was failing is that one or more databases, schemas or tables has a name that's either a reserved word or starts with an illegal character. The way this kind of thing is done is:
SET @cmd = 'ALTER INDEX ALL ON [' + @database + '].[' + @schema + '].[' + @table + '] ....
But rather use Michelle's. It won't rebuild things that don't need rebuilding. It'll use online when it can (if the options are set), it'll reorg rather than rebuild for low fragmentation and it logs what it does.
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
October 20, 2011 at 9:06 am
Thank you! I knew it would be something simple. And you're right, my script was built on the fly whereas Michelle has obviously put a lot of work in. I'm going to assess her script then recommend to my CAB that it's applied to my affected DBs.
Many thanks for looking at this.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 20, 2011 at 12:28 pm
It's better to use system QUOTENAME function to add [].
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply