SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gregory Ferdinandsen / Index Defragmentation and update stats / Latest Posts

RE: Index Defragmentation and update stats

GregoryF — Wed, 24 Aug 2011 08:34:52 GMT

[quote][b]skraghava (8/24/2011)[/b][hr]I think it if page level locking is disabled then you have to rebuild and not reorganize.[/quote]Send me a private message and I will send you the latest version of this script, it has a few kinks worked out after further QA in different environments.

RE: Index Defragmentation and update stats

skraghava — Wed, 24 Aug 2011 07:46:27 GMT

I think it if page level locking is disabled then you have to rebuild and not reorganize.

RE: Index Defragmentation and update stats

skraghava — Wed, 24 Aug 2011 07:43:34 GMT

if i excecute i got below error.Msg 2552, Level 16, State 1, Line 1The index "INDEXNAME" (partition 1) on table "TABLENAME" cannot be reorganized because page level locking is disabled.Does allow_page_locks should be considered?

RE: Index Defragmentation and update stats

shaun.stuart — Thu, 09 Jun 2011 14:18:55 GMT

[quote][b]shaun.stuart (6/9/2011)[/b][hr]Some changes I would make:- get rid of the switch from full or bulk to simple mode or make this an option. This could break log shipping, backup chains, etc. if not careful.[/quote]Oops. just noticed this was an option already :-)

RE: Index Defragmentation and update stats

shaun.stuart — Thu, 09 Jun 2011 14:12:00 GMT

Some changes I would make:- get rid of the switch from full or bulk to simple mode or make this an option. This could break log shipping, backup chains, etc. if not careful.- If the index is rebuilt, skip the update stats portion. An index rebuild always updates stats with a 100% sampling rate. (See http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx)Otherwise, very nice.

RE: Index Defragmentation and update stats

Jon Cooney — Thu, 19 May 2011 07:27:26 GMT

I changed a few references to @DBName to quotename(@DBName) as there is an issue with database names with spaces.Otherwise, thanks very much.

RE: Index Defragmentation and update stats

GregoryF — Thu, 14 Apr 2011 11:33:10 GMT

There is included an option for a full rescan if update stats is a selected option

RE: Index Defragmentation and update stats

originsone — Wed, 06 Apr 2011 14:43:37 GMT

You may want to reconsider your use of sp_updatestats as the only way to update the statistics of the database. This normally will only sample a portion of each table and you may not be getting the best query plan as a result. I have seen where it will do a full scan, but sometimes I have seen it scan below 1% if the table is large enough.

RE: Index Defragmentation and update stats

GregoryF — Tue, 15 Mar 2011 05:54:20 GMT

An interesting note, I just found this error. I will look at how to address it.Msg 2725, Level 16, State 2, Line 1An online operation cannot be performed for index 'PK_notable_query_plan' because the index contains column 'query_plan' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

Index Defragmentation and update stats

GregoryF — Mon, 14 Mar 2011 23:00:14 GMT

Comments posted to this topic are about the item [B]Index Defragmentation and update stats[/B]