InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:54:35 GMT20http://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxPlease post new questions in a new threadThanksMon, 18 Oct 2010 12:14:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxHi guys, I have a question for you:I am doing an index rebuild offline weekly. I have a Service that runs 24/7 and inserts articles in the db.Do I need to run a script that will stop this Service during the offline rebuild?Is there a queue that is generated with all the requests or because of the lock on those tables I will just have some errors?Mon, 18 Oct 2010 09:32:35 GMTtina 2565http://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]muthukkumaran (12/2/2009)[/b][hr][quote][b]GilaMonster (12/2/2009)[/b][hr][quote][b]muthukkumaran (12/2/2009)[/b][hr]Hi,Read & use the index de-fragmentation script added in my signature.[/quote]Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details[/quote]Gail,Thanks for the update & Suggestion.I ll edit and post Script ASAP.[/quote]Now the article has been published.Wed, 09 Dec 2009 00:04:15 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxThank you very much all of you.. I really appreciate you guys for putting out such a great discussion. I have used the script by muthukumaran and studied the results. The results were convincing. Actually, I am referring to two different databases on the same instance here. Both of them are mission critical and at the same time, Application team has agreed for a weekly outage of two hours on both the databases. So, we can do the rebuild offline. Now, I have a small concern here... While executing the stored procedure on one of my databases on a test server.. I have noticed CPU usage was maxed out. usually, this box sits at 45-50% CPU usage.. Also when this SP was running, I had none other than me logged into this machine... any comments on its effect on my prod server...Thu, 03 Dec 2009 06:56:37 GMTALI SQLDBAhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxOk, looked around and seems there's both definitions of internal and external fragmentation. I usually use them to mean fragmentation internal to SQL (and fixable within SQL) and fragmentation external to SQL (fixable outside SQL)Thu, 03 Dec 2009 01:44:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxAgreed NagrajThu, 03 Dec 2009 00:40:06 GMTluckysql.kindahttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx/* Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.*/Ali, If you have a downtime/offpeak please rebuild during ur downtime as doing it online is always more expensive than a offline rebuild.If you dont have any downtime online rebuild is the only option.Thu, 03 Dec 2009 00:04:21 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxGila - "External fragmentation usually refers to file-level fragmentation that's fixed with a disk defragmentation program."According to my understanding the external fragmentation is nothing to do with disk fragmentation program. This is simply hip hop between extents because of page split. What's ur PoV (point of view)?Wed, 02 Dec 2009 22:24:13 GMTluckysql.kindahttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]GilaMonster (12/2/2009)[/b][hr][quote][b]muthukkumaran (12/2/2009)[/b][hr]Hi,Read & use the index de-fragmentation script added in my signature.[/quote]Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details[/quote]Gail,Thanks for the update & Suggestion.I ll edit and post Script ASAP.Wed, 02 Dec 2009 08:06:14 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]muthukkumaran (12/2/2009)[/b][hr]Hi,Read & use the index de-fragmentation script added in my signature.[/quote]Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error detailsWed, 02 Dec 2009 07:59:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]Ali_SQLDBA (12/2/2009)[/b][hr]Most of my tables are either clusterd or non clustered. [/quote]Um, the distinction is clustered or heap. A nonclustered index is a separate structure and can sit on either tables with a clustered index or heaps[quote]so as per your expertise... this is not a big deal for me to worry. Right?[/quote]Not for the small tables. For the large ones, it's still something that you need to do.[quote]Also, do you recommend me t run rebuild offline scheduling an outage of the database every week.??[/quote]Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.Use a custom script rather than just a maintenance plan. I'm fond of this one - [url]http://sqlfool.com/2009/06/index-defrag-script-v30/[/url], lots of options to customise how much you rebuild, where the threshold are and other details.Wed, 02 Dec 2009 07:57:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxMost of my tables are either clusterd or non clustered. so as per your expertise... this is not a big deal for me to worry. Right?Also, do you recommend me t run rebuild offline scheduling an outage of the database every week.??Thanks AliWed, 02 Dec 2009 07:40:51 GMTALI SQLDBAhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxHi,Gail already point out pages are smallIf the table is "Heap" fragmentation ll not removeWed, 02 Dec 2009 07:18:56 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote]But I am not able to understand [b]why the rebuild index task is not able to defrag the columns with int data type[/b]. Is it something set wrong at the database level settings..? I am confused.. Help me out plzzzz [Crying][/quote]Hi,Can u send me the Object structure.Wed, 02 Dec 2009 07:12:32 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]luckysql.kinda (12/2/2009)[/b][hr]I want you to go ahead and remove the indexes which are referring LOB (text,ntext,image) and run rebuild again.[/quote]Why?External fragmentation usually refers to file-level fragmentation that's fixed with a disk defragmentation program.Internal fragmentation usually refers to fragmentation of the index pages that's fixed with ALTER INDEX .. REBUILD or ALTER INDEX ... REORGANIZEWed, 02 Dec 2009 07:09:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspx[quote][b]sqldba.jagan (12/2/2009)[/b][hr]The tables in the database has varied page count. I think I have very few pages >=10 - <=25 and their frag ranges from 87.5 - 33.33. [/quote]Don't even bother rebuilding indexes so small. Firstly, under 24 pages the index won't show much, if any change at all when rebuilt. This is due to the way SQL allocates pages in smaller indexes. The other point is that fragmentation is only a performance issue when doing large scans of an index. Emphasis large. The rough rule of thumb is that you should start worrying about fragmentation when an index reaches around 1000 pages as, at that point, the performance impact of the fragmentation may become noticeable.Wed, 02 Dec 2009 07:07:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxI have ran the scripts and executed the store proc. I see that only update statistics is applied on my database. the tables in the database has varied page count. I think I have very few pages >=10 - <=25 and their frag ranges from 87.5 - 33.33. I cannot remove the indexes which include LOBs in it. Our database is updated quaterly by our third party vendor on a quaterly basis. Even if i drop those indexes now, It's a temporary solution for my problem.But I am not able to understand why the rebuild index task is not able to defrag the columns with int data type. Is it something set wrong at the database level settings..? I am confused.. Help me out plzzzz :crying:ThanksAliWed, 02 Dec 2009 07:01:03 GMTALI SQLDBAhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxRun following query and let us know the result.select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')Find 'avg_fragmentation_in_percent' - External Fragmentationand 'avg_page_space_used_in_percent' - InternalI want you to go ahead and remove the indexes which are referring LOB (text,ntext,image) and run rebuild again.Wed, 02 Dec 2009 02:18:45 GMTluckysql.kindahttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxHi,Read & use the index de-fragmentation script added in my signature.Wed, 02 Dec 2009 02:03:24 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxHow big's the index? How many pages?Wed, 02 Dec 2009 00:13:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic827173-146-1.aspxHello SQL GurusWe have rebuilding index issue in our SQL environment. our jobs were failing since the server has been in production (since a week). I have learnt that the issue is because of the rebuild index LOB datatypes. According to MSDN, while rebuilding index, if a clustered index has any lob data type included in it or if a non clustered index has any lob data type coloumns included in it then the Rebuild will not suceed with rebuild = ON option. I tried to rebuild the database when it is offline. defragmentaion is not reducing. I have collected the tables with rows >= 4000 and checked the corresponding coloumns included in it and also checked the respective data types. surprisingly, the data types on the coloumns are INTs and my rebuild offline does not defrag those coloumns. Can anyone help me out in this regard.ThanksAliTue, 01 Dec 2009 20:46:29 GMTALI SQLDBA