Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Defragmentation (2005, 2008, 2012) Expand / Collapse
Author
Message
Posted Saturday, December 31, 2011 2:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
Comments posted to this topic are about the item Index Defragmentation (2005, 2008, 2012)

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Post #1228702
Posted Tuesday, January 3, 2012 5:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 25, 2012 4:09 AM
Points: 44, 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 !
Post #1229169
Posted Wednesday, January 4, 2012 10:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 217, Visits: 1,011
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
Post #1230159
Posted Saturday, January 7, 2012 12:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 6, 2013 8:11 PM
Points: 10, Visits: 141
sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.
Post #1231815
Posted Saturday, January 7, 2012 1:08 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 217, Visits: 1,011
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
Post #1231968
Posted Saturday, January 7, 2012 1:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 6, 2013 8:11 PM
Points: 10, Visits: 141
Very good point. Hadn't thought about that.
Post #1231971
Posted Sunday, February 5, 2012 2:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
Nicely Done!

Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1246967
Posted Friday, March 9, 2012 6:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 6:20 AM
Points: 247, Visits: 800
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.



Post #1264328
Posted Monday, March 12, 2012 11:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 6:20 AM
Points: 247, Visits: 800
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/






Post #1265407
Posted Monday, March 12, 2012 2:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #1265569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse