April 15, 2011 at 8:08 am
Good Day ,
We deployed Online Indexing on SQLS erver 2005 Enterprise Edition . I was rather disapppointed when I noticed that this caused locks on the system and a serious degradation in performance . We work in an envirnement where Online Indexing should have been the big saviour as our server run jobs 24 x 7 and there is no quiet time. Any ideas ?
April 15, 2011 at 2:26 pm
The online option just reduces blocking locks it does not reduce all resources needed to rebuild an index so there will be a performance hit, but tables with indexes being rebuilt will still be queryable. Check out this article, http://msdn.microsoft.com/en-us/library/ms191261.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2011 at 2:35 pm
Jack, thank you for the feedback. In our case it did not reduce the locking issues at all .
April 15, 2011 at 4:09 pm
lianvh (4/15/2011)
Good Day ,We deployed Online Indexing on SQLS erver 2005 Enterprise Edition . I was rather disapppointed when I noticed that this caused locks on the system and a serious degradation in performance . We work in an envirnement where Online Indexing should have been the big saviour as our server run jobs 24 x 7 and there is no quiet time. Any ideas ?
A question. Do you run any T-SQL to determine the degree of Index fragmentation, and if so do you then limit the indexes being rebuilt to those with say a percentage of fragmentation greater than say 30 percent?
Another question. Do you process all Index rebuilding in a single script? If so when one index is rebuilt, do you have a DELAY command so that other T_SQL commands get a period of time to run.
Now neither of these will completely solve the problem you are having, but might mitigate it enough so as not to seriously interfere with other processing.
April 16, 2011 at 4:02 am
We build all of our indexes online with the sort_in_tempdb option. This helps somewhat with contention on our busiest production servers.
April 17, 2011 at 11:05 pm
Thank you for your feedback, I have this setting configured , but my problem still persists.
April 17, 2011 at 11:06 pm
In the script all the indexes of one table are rebuilt at a time .
April 18, 2011 at 7:09 am
Probably silly question, but did you specify "ONLINE" in your statement even though you have enterprise edition ?
April 19, 2011 at 6:35 am
I too would like to see the exact statement used for one of these online indexing operations.
Your poor performance is almost certainly due to an insufficient IO subsystem. Indexing really hammers the IO. Do a file IO stall analysis and a wait stats analysis while the indexing mx is ongoing to uncover where the slowness is.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 19, 2011 at 7:06 am
Yes the ONLINE parameter was set in the rebuild index script
April 19, 2011 at 7:08 am
We used the 'ÄLL' statement in the rebuild script .
April 19, 2011 at 7:09 am
The server is running on a SAN with 15kdisks. The Log and MData files are on seperate LUNS.
April 19, 2011 at 10:56 am
lianvh (4/19/2011)
The server is running on a SAN with 15kdisks. The Log and MData files are on seperate LUNS.
That answer leaves umpteen things unanswered. You statement could be this: 2 drives in a RAID1 with both LUNs from the same Raid Group and with an Exchange server LUN and an Oracle LUN sitting on the same 2 spindles, all attached with a cheapo misconfigured 100MB NIC to an iSCSI SAN. Or it could be a 1000 spindle 3PAR system 500 drives per LUN, completely dedicated to SQL Server, with 4 8Gb FC HBAs shoving data at the CPUs just as fast as they can handle it.
Hopefully you know exactly what the underlying configuration is and how it is shared on the backend by other systems.
Still think a file IO stall and wait stats analysis is needed here to really find out why things are slow. Also use sp_whoisactive to uncover blocking issues.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 19, 2011 at 12:36 pm
lianvh
I suggest that you read this blog posting of Kimberly L. Tripp
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Spring-cleaning-your-indexes-Part-I.aspx
It may suggest a plan that you can implement to solve your problem
April 20, 2011 at 8:26 am
To summarize:
1) Please post the precise statements your script is generating and running.
2) Please investigate your disk configuration all the way down to the individual physical spindle/SSD device level; separate LUNs can still share physical spindles.
2a) regarding tempdb; are the tempdb data and tempdb logs also on separate dedicated spindles, or not?
2b) We've put our tempdb data and logs on SSD's with good results. Combined with sort_in_tempdb, this might at least speed things up.
3) I agree with the prior poster; if you're seeing performance impacts, then try one index at a time with a DELAY WAITFOR between them. Definitely only reindex what you need to, whether you choose to rebuild or reorganize.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply