I was wondering the same as when I used to run the soon to be removed (in a future SQL release) DBCC REINDEX command I would have 0 fragmented indexes. With the recommended method of "ALTER INDEX..." in place of DBCC REINDEX I was noticing that the process still left fragmented indexes. After some research I discovered that indexes with a low amount (<1,000? <500?) of pages are not defragmented using the ALTER INDEX method. The community-based maintenance scripts such as Ola's (https://ola.hallengren.com/) use this recommended method. Do the fragmented indexes left behind after maintenance have a "small" page count? Ex:SELECT a.index_id, name, avg_fragmentation_in_percent, page_count FROMsys.dm_db_index_physical_stats (NULL,NULL, NULL, NULL, NULL) AS a JOIN sys.indexesAS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHEREavg_fragmentation_in_percent > 30 Some reference: https://msdn.microsoft.com/en-us/library/ms189858.aspx?f=255&MSPPError=-2147217396 NoteIn general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.http://dba.stackexchange.com/questions/5365/why-is-my-database-still-fragmented-after-i-rebuilt-and-reindexed-everything Hope this helps! -Stephen From: [email protected] To: [email protected] Subject: [mssms] RE: Disabling rebuild index site maintenance task Date: Sun, 15 Mar 2015 23:20:47 +0000
I had this job run for the first time last night. When I run the query to check the statistics I’m still seeing a lot of records with a last updated date in 2013 and 2014, though the amount of records has reduced from 295 to 37. Does this sound right for anyone who runs this? Daniel. From: [email protected] [mailto:[email protected]] On Behalf Of Corkill, Daniel Sent: Tuesday, 10 March 2015 8:47 AM To: [email protected] Subject: [mssms] Disabling rebuild index site maintenance task Last night I implemented the reindexing and defrag optimisation steps for the ConfigMgr database detailed here: https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/ and I plan on scheduling the job to run once a week. For those of you who have done this, should I be disabling the built in ConfigMgr ‘rebuild indexes’ site maintenance task now? It’s currently set to run once a week too. Daniel. ********************************************************************* This email, including any attachment, is confidential to the intended recipient. It may also be privileged and may be subject to copyright. If you have received this email in error, please notify the sender immediately and delete all copies of the email. Any confidentiality or privilege is not waived. Neither the Council nor the sender warrant that this email does not contain any viruses or other unsolicited items. This email is an informal Council communication. The Council only accepts responsibility for information sent under official letterhead and duly signed by, or on behalf of, the Chief Executive Officer. Privacy Collection Notice Logan City Council may collect your personal information, e.g. name, residential address, phone number etc, in order to conduct its business and/or meet its statutory obligations. The information will only be accessed by employees and/or Councillors of Logan City Council for Council business related activities only. If your personal information will be passed onto a third party, Council will advise you of this disclosure, the purpose of the disclosure and reason why. Your information will not be given to any other person or agency unless you have given us permission or we are required by law. ********************************************************************* This email, including any attachment, is confidential to the intended recipient. It may also be privileged and may be subject to copyright. If you have received this email in error, please notify the sender immediately and delete all copies of the email. Any confidentiality or privilege is not waived. Neither the Council nor the sender warrant that this email does not contain any viruses or other unsolicited items. This email is an informal Council communication. The Council only accepts responsibility for information sent under official letterhead and duly signed by, or on behalf of, the Chief Executive Officer. Privacy Collection Notice Logan City Council may collect your personal information, e.g. name, residential address, phone number etc, in order to conduct its business and/or meet its statutory obligations. The information will only be accessed by employees and/or Councillors of Logan City Council for Council business related activities only. If your personal information will be passed onto a third party, Council will advise you of this disclosure, the purpose of the disclosure and reason why. Your information will not be given to any other person or agency unless you have given us permission or we are required by law.
