December 31, 2011 at 2:30 pm
Comments posted to this topic are about the item Index Defragmentation (2005, 2008, 2012)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
January 3, 2012 at 5:26 am
Monday's SSC email features another script on index defragmentation (certainly a worthy topic for community understanding and action), so now we have several contenders [my pref order!]
http://sqlfool.com/2011/06/index-defrag-script-v4-1
http://www.sqlservercentral.com/scripts/Fragmentation/86879/
http://blogs.msdn.com/b/blogdoezequiel/archive/2011/07/03/adaptive-index-defrag.aspx
http://msdn.microsoft.com/en-us/library/ms189858.aspx
http://msdn.microsoft.com/en-us/library/ms186878.aspx
I suggest that it would be good to have an objective ranking of these alternatives, to guide SSC community on what/why/how
As humorous aside, whilst researching MSDN/BOL topics I found this
showing MS took 3 years and 2 months to decide not to fix the reported bug. No wonder SQL2011 has become SQL2012 !
January 4, 2012 at 10:32 am
Gregory, nice script. But I have some suggestions:
1. When you rebuild an index then statistics are updated with FULL SCAN, so it's not necessary and even harmful to run sp_updatestats afterwards since it will update stats with some sample rate.
2. I'm not sure about best practise but instead of rebuild/reorganize all indexes and then update all statistics, I would prefer to do it table by table. It makes more sense in terms of performance. For the server it's easy to do an update stats while some of the indexes are still cached rather than reading everything from disk again after rebuilding all the indexes for the DB.
January 7, 2012 at 12:58 am
sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.
January 7, 2012 at 1:09 pm
richard mascarenhas (1/7/2012)
sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.
If you run rebuild and update stats when nobody works with DB then it will be like you said, but if you have 24/7 system who will guarantee that nobody update at least one record between rebuild and sp_updatestats operations? And if such update happens then sp_updatestats will update your statistics with sample rate because based on rowmodctr that statistics requires updating. You can test it yourself:
CREATE DATABASE UpdateStatsTest
GO
USE UpdateStatsTest
CREATE TABLE dbo.T (ID int NOT NULL)
ALTER TABLE dbo.T ADD CONSTRAINT PK_T PRIMARY KEY CLUSTERED (ID )
INSERT dbo.T(ID) VALUES(1)
GO
ALTER INDEX [PK_T] ON [dbo].[T] REBUILD
INSERT dbo.T(ID) VALUES(2) -- Insert one record between rebuild and updatestats
EXEC sp_updatestats
Here is result, stats has been updated:
Updating [dbo].[T]
[PK_T] has been updated...
1 index(es)/statistic(s) have been updated, 0 did not require update.
January 7, 2012 at 1:32 pm
Very good point. Hadn't thought about that.
February 5, 2012 at 2:27 am
Nicely Done!
March 9, 2012 at 6:29 am
I'm confused, somehow I have version:
Version 1.9
Removed option for all database in simple recovery model
Listed out all 80 (or earlier) compatibility databases
15 Aug 2011
Yet, this is a December version of 1.4?
The reason I'm writing is, I have several databases that use row level locking. When I try the stored proc I get on some indexes: Number: 2552 [SQLSTATE 01000]
--*****Error: The index "PK__ICMUT013__6CFA4C671550F9CF" (partition 1) on table "ICMUT01331001" cannot be reorganized because page level locking is disabled. [SQLSTATE 01000]
--*****Object Name: [authjcrdb].[authjcrsch].[ICMUT01331001] [SQLSTATE 01000]
--*****Error Code: alter index [PK__ICMUT013__6CFA4C671550F9CF] on [authjcrdb].[authjcrsch].[ICMUT01331001] reorganize [SQLSTATE 01000]
I'm looking for the ability or changing to set to page level locking for the reindex and then go back to row level.
March 12, 2012 at 11:21 am
Also, what does:
"NB:
@Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)
***There is a possible issue with database names containing GUID's***"
What's the possible issue? You mean if you use a GUId in the name there may be problems/
March 12, 2012 at 2:41 pm
Sailor (3/12/2012)
Also, what does:"NB:
@Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)
***There is a possible issue with database names containing GUID's***"
What's the possible issue? You mean if you use a GUId in the name there may be problems/
For whatever reason, I have found that the sharepoint databases, which by default contain, GUID's always cause the SP to exit ungracefully. I wish I knew what caused the problem, but I have not yet found the issue.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 11, 2016 at 7:14 am
Thanks for the script.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply