SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Defragmentation (2005, 2008, 2012)


Index Defragmentation (2005, 2008, 2012)

Author
Message
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 885
Comments posted to this topic are about the item Index Defragmentation (2005, 2008, 2012)

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
dick.baker
dick.baker
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 91
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://ola.hallengren.com
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
http://connect.microsoft.com/SQLServer/feedback/details/321628/closing-view-tsql-dialog-early-for-maintenance-plan-reindex-task-throws-incorrect-error
showing MS took 3 years and 2 months to decide not to fix the reported bug. No wonder SQL2011 has become SQL2012 !
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 1516
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.


Alex Suprun
richard mascarenhas
richard mascarenhas
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 143
sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 1516
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.




Alex Suprun
richard mascarenhas
richard mascarenhas
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 143
Very good point. Hadn't thought about that.
Dev
Dev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3514 Visits: 1602
Nicely Done!
Sailor
Sailor
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 861
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.



Sailor
Sailor
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 861
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/



GregoryF
GregoryF
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 885
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search