| Author |
Topic |
|
mattbeckman
Starting Member
8 Posts |
 Posted - 2008-06-19 : 20:29:05
|
| Hi all,In SQL Server 2005 EE I created a maintenance plan to rebuild indexes for a few large tables. I have selected five specific tables, and I'm using both "sort results in tempdb" as well as "keep index online while reindexing". If I execute this plan for all these tables, are the indexes guaranteed to remain online? There are all different types of indexes on these tables. For example, the table "Contacts" has 8 indexes: 1 Clustered, 1 PK Unique Non-Clustered, 2 Unique Non-Clustered, and 4 Non-Unique Non-Clustered. I've heard that only certain types of indexes can remain online during a reindex (Clustered and Non-Unique Non-Clustered??).Will SQL Server rebuild an index that isn't compatible with the online reindex mode, or will it choose to ignore it?Thanks,- Matt |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 22:41:38
|
| Yes you can rebuild all indexes online except XML indexing ,Partition Index ,Index in Temptable.If you are doing online indexing for huge table and choose sort in Tempdb , you are going to take huge space for TEMPDB. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 00:10:21
|
| >> I've heard that only certain types of indexes can remain online during a reindex (Clustered and Non-Unique Non-Clustered??).It's not about index type, but column's data type in the table. May unable to build index online if table has text, ntext, image or xml column. |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-06-23 : 15:08:16
|
| From my experience it is possible to rebuild partitioned indexes online. You just cannot rebuild individual partitions of the index online.About the LOB data types.Clustered indexes cannot be rebuilt online if the table contains a LOB column.NonClustered indexes cannot be rebuilt online if the index contains a LOB columns.Ola Hallengrenhttp://ola.hallengren.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-23 : 18:54:41
|
| Good to know that. |
|
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-24 : 15:53:01
|
| Thanks! I was able to perform a database-wide online index rebuilding maintenance plan last night without a hitch. However, the DB's I used did not have any LOB types in the underlying tables or indexed columns. Tonight I want to rebuild all indexes on a much larger database that has LOB columns. I know Clustered indexes will have to be rebuilt offline, but do I need to go through and hand-pick the non-Clustered indexes or will a maintenance plan built for all indexes in a DB ignore the clustered indexes when the ONLINE = ON option is specified?I was thinking of using Tara's script for defragmenting (http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx), but everything needs to remain online, and I believe her script rebuilds all indexes regardless of the need to remain online.Ideas? |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-06-24 : 16:12:19
|
| I'm not sure how Tara's script works here.If you would like to use my script and you need to have the indexes online, you could run it like this.http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlEXECUTE dbo.IndexOptimize@Databases = 'USER_DATABASES',@FragmentationHigh_LOB = 'INDEX_REORGANIZE',@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',@FragmentationMedium_LOB = 'INDEX_REORGANIZE',@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',@FragmentationLow_LOB = 'NOTHING',@FragmentationLow_NonLOB = 'NOTHING',@FragmentationLevel1 = 5,@FragmentationLevel2 = 30,@PageCountLevel = 1000This means that indexes with a fragmentation above 30% are rebuilt online if possible. If it's not possible they are reorganized instead. Indexes with a fragmentation between 5% and 30% are reorganized. Indexes with a fragmentation below 5% or a size below a 1000 pages are not touched. This way the indexes will stay online.Ola Hallengrenhttp://ola.hallengren.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-24 : 16:20:58
|
quote:
Originally posted by mattbeckman
I was thinking of using Tara's script for defragmenting (http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx), but everything needs to remain online, and I believe her script rebuilds all indexes regardless of the need to remain online.
My script rebuilds them online where possible (depending on SQL Server edition, column data types, etc...). It is all explained in the comments of the stored procedure.The online option is only available under certain circumstances, so any defragment code needs to check the conditions.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-24 : 16:32:11
|
| You could try running mine with REORGANIZE first for those that can't use the ONLINE option of REBUILD that way when the REBUILD runs, it might not need to do it offline as the index may not be fragmented anymore. I prefer not to run REORGANIZE and REBUILD in the same script as that isn't what we would want in production. REORGANIZE can't always fix the fragmentation. There are times where you must use REBUILD and in the cases where ONLINE isn't available, it must be fixed offline.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-06-24 : 16:46:35
|
| Tara, I agree that for indexes with a high fragmentation the best practise is to rebuild them offline if it cannot be done online. This is my normal recommendation (@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE').The idea with my design is to put the indexes in categories based on the fragmentation level, LOB existence and index size. For each category you can define an action.http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlOla Hallengrenhttp://ola.hallengren.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Rebuild Index Task - Online Only?