The Real World: Rebuilding Index - 1 Instance, 106 Databases
-
OP: Something that might be useful for you down the track is to only reorganize indexes that allow page locks. Otherwise it will throw an error and the index will not get reorganized and it will have to wait until it gets to 30% or higher to get rebuilt.
I have found this in a few vendor databases that ship with allow_page_locks = off.
The work around is to enable page locks just before each index is reorganized and then switch it off immediately after. The other option is to always rebuild them.
-
KenpoDBA (2/17/2015)
g.britton (2/17/2015)
Just a thought since there's another article today talking about SQL Server Express. Could Minion be made to work with Express? There's no Agent but perhaps some simple scripts to call the procs so that they can be scheduled with Windows Task SchedulerI don't see why not. You could either call it from sqlcmd or ssis, or anything else that can make a DB connection. I'm not going to write it for you, but they're just SPs so if you can make a connection you can call Minion Reindex. The Agent scheduler is just how we usually do things. You could even wrap the SQL call into a powershell and run that from the windows scheduler.
Yeah, did that, just thought you might want to add it to the package. No matter! It's really easy and works great!
Gerald Britton, Pluralsight courses
-
james.a.payne (2/17/2015)
OP: Something that might be useful for you down the track is to only reorganize indexes that allow page locks. Otherwise it will throw an error and the index will not get reorganized and it will have to wait until it gets to 30% or higher to get rebuilt.I have found this in a few vendor databases that ship with allow_page_locks = off.
The work around is to enable page locks just before each index is reorganized and then switch it off immediately after. The other option is to always rebuild them.
Yeah Minion Reindex will let you turn that option on and off quite easily.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Sounds like some nasty little application creating all of those databases. Weird, as you say!
One comment would be the use of sp_msforeachdb. Personally, I'm not a fan because it can skip databases. I've never actually witnessed this bug, but since I found it out, I have avoided using it.
-
Thanks a lot
-- query in one select
exec sp_MSforeachdb @command1= '
if ''?'' not in (''master'',''model'',''msdb'',''tempdb'')
begin
use [?]
create table #indtab (ID SMALLINT IDENTITY(1,1), REBUILDSTMT nvarchar(600))
insert into #indtab
SELECT
CASE WHEN avg_fragmentation_in_percent > 30 THEN
''ALTER INDEX ['' + b.name + ''] ON ['' + SCHEMA_NAME(c.schema_id) + ''].['' + OBJECT_NAME(a.object_id) + ''] REBUILD WITH (ONLINE = OFF,FILLFACTOR=80,SORT_IN_TEMPDB=ON,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF); -- fragmentation ='' + cast(avg_fragmentation_in_percent as nvarchar(10)) + ''''
ELSE
''ALTER INDEX ['' + b.name + ''] ON ['' + SCHEMA_NAME(c.schema_id) + ''].['' + OBJECT_NAME(a.object_id) + ''] REORGANIZE; -- fragmentation ='' + cast(avg_fragmentation_in_percent as nvarchar(10)) + ''''
END
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
JOIN sys.objects AS c
ON b.object_id = c.object_id
ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 10
and b.name is not null
select ''?'' as db , a.* from #indtab a
declare @sql nvarchar(600)
declare @rbldrows smallint
select @rbldrows = max(id) from #indtab
while (@rbldrows > 0)
begin
--print ''Total number of rebuild statements:'' + cast(@rbldrows as nvarchar(10))
select @sql= REBUILDSTMT from #indtab where id = @rbldrows
print @sql
set @rbldrows = @rbldrows - 1;
end
--print ''Index Rebuild Complete''
drop table #indtab
end'
-
Hello
I want to know if after you rebuild and reorganize the need to update the statistics with the command "EXEC sp_updatestats"
Thanks.
-
No; in fact, you should not do that. The rebuild will use full statistics from the entire table, whereas updating the stats would just use a sampling of rows. Iow, you'd get less accurate stats than you had after the rebuild.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
thanks!!
another question
But reorganize indexes that if I update the statistics?
-
Oh yawn. Not another stab at re-indexing. Don't want put it down as an issue, but there is some really good free index defragmenter code out there already. I'd download those first, and have a look at how they work.
Here's some tips to take those others a step further:
1. Take a look at Ola Hallengren's maintenance routines before even thinking of using this code. It does index maintenance as well as backups and various other functions. It has more code and ideas than this example.
2. Only sample on a handful of indexes per database each night (or whatever your window is) using the physical stats DMF. Otherwise you can end up using a heck of a lot of resource doing that, and doing very little defragmenting.
3. Particularly if you have multi-column clustered indexes, you will want to occasionally do a deeper sample than the usual 'LIMITED' mode for the DMF, possibly even 'DETAILED' (usually 'SAMPLED' is good enough). That might reveal, for instance, an index that has little logical fragmentation, but gone to 4 levels deep, yet has very little in the 3rd level. On a rebuild (online) we're back to just 2 index levels.
4. As Paul Randall says, his 30% guideline was just made up on the fly. It's probably not a bad one, just something to bear in mind - you might want to analyse over the longer term. You might want to tweak those levels, for example we're more biased towards defrags than re-builds - especially for massive tables. We tend use repeated defrags, to incrementally chip away at any major problems. Otherwise if it recrurs over a long time you might want to consider other options, such as altering the fillfactor and / or take other action.
5. Put in logging of what happens to your index defragmentation (/ rebuild) process: does it fail (deadlock, timeout, other), how long did it take, when did you last do it. Then you can build a self-administering / tuning system that can adjust it's own parameters.
6. Check and update your statistics after you've done the re-indexing work.
We've built on all those ideas, and more, and come up with what we call a site-wide self-tuning re-indexer. Sorry it's not published, but above is the seed corn ideas, and a better example starting point, if you want to do something similar.
This DBA says - "It depends".
-
Minion Reindex is much more configurable than Ola's. It doesn't require extra jobs to exclude objects or for a group of tables to have different configs like different fill factors. It also has very extensive logging.
Seriously, take a look at it, you'll never go back.
Of course, you don't have to install it to see what you think, on the download page there's a link to a recorded webinar that explains the features.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Dear Minion. The hint about other free re-indexer code being out there, was implicitly pluralised. I only mentioned Ola H as that was what we (loosely) based our version on (which takes the technology to another level higher). Minion looks OK, but it didn't quite fit our very demanding enviroment. Although, it (Minion) it has got to be better than what about 90% of devs come up with - which is to say not even quite as good as this article.
Sorry those are very back-handed compliments, but I'm not very good at "biggin' up", after all I'm a techie - not a salesperson.
This DBA says - "It depends".
-
I think Scott P was trying to say that your index statistics get rebuilt, for just that index, as part of an index rebuild. True, but not if it is a defragment or if the statistics are columns statisitics (i.e. not index statistics). Please still read the deep dives anyway, as he helpfully suggested.
Hence it is best not to rebuild your statistics till after your indexes, and then only for really old stale ones for the index statistics. Again, see Deep Dives or similar authoritative references for a fuller explanation.
This DBA says - "It depends".
-
SQLBoar,
That's fine, but what does Minion not do that you want it to?
I don't care about the sales aspect of it either, I'm a tech as well.
So give me a list of things you want it to do that it doesn't and I'll look into it.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
ScottPletcher (2/17/2015)
There is no such thing as an accurate, global freespace percentage. Freespace needs to be set index by index. Thus, a generic rebuild should never specify a fillfactor, but let it default to what has already been set for that index.Well taken and understood Scott. I used this because the application is extremely write-intensive. The script can be customized to suit the environment.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising -
james.a.payne (2/17/2015)
OP: Something that might be useful for you down the track is to only reorganize indexes that allow page locks. Otherwise it will throw an error and the index will not get reorganized and it will have to wait until it gets to 30% or higher to get rebuilt.I have found this in a few vendor databases that ship with allow_page_locks = off.
The work around is to enable page locks just before each index is reorganized and then switch it off immediately after. The other option is to always rebuild them.
Thanks a lot James. This is one of the benefits of daring to write: I learn more...
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply