May 3, 2010 at 2:25 pm
Is there anything that can tell me how long it might lock down my table if I add a new index? We are running 2005 Standard so I can't do online indexes so I don't want to lock down the table for more than a few seconds if I can help it. Though if we get a good performance increase it will be worth it. Just trying to figure out what kind of damage I'll do to the onlines.
Thanks
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 3, 2010 at 3:29 pm
SQLJocky (5/3/2010)
Is there anything that can tell me how long it might lock down my table if I add a new index? We are running 2005 Standard so I can't do online indexes so I don't want to lock down the table for more than a few seconds if I can help it. Though if we get a good performance increase it will be worth it. Just trying to figure out what kind of damage I'll do to the onlines.Thanks
Heh... with the information you gave us, my answer would be "proably somewhere between 1 second and 3 days". You given absolutely no indication as to the size of the table in row counts, number or type of columns nor what the clustered index is based on.
My recommendation is to make a copy of the table on a test server and test for how long.
--Jeff Moden
May 3, 2010 at 3:35 pm
First suggestion is to try the operation on similar set of hardware. I frequently start by trying this type of operation on my "test box". It has a lot of disk but only modest RAM (4GB) and Processor (dual 3ghz). I figure if the operation completes in that environment in a few seconds then it will complete on the production server pretty quickly. (BTW I almost always script the operation and try and run it during a lull.
The real drivers are the number and size of the rows involved, available RAM, and I/O system performance. If your table has less than 100K rows I would not worry too much. If you have a larger table do more offline testing.
As an example, I have built a non-clustered index on a 300M row table in about 2 hours. However, that does not give you a good benchmard since I did not describe hardware, other processing load, table characteristics, etc. In this case it was a narrow table (avg 100 bytes/row) and a narrow index (about 40 bytes/row). It was a dual processor with attached RAID 5 array. But the numbers still don't really answer you question. There are just too many variables to consider. So the bottom line is to test your specific change in a local dev environment.
May 3, 2010 at 5:55 pm
Jeff Moden (5/3/2010)
SQLJocky (5/3/2010)
Is there anything that can tell me how long it might lock down my table if I add a new index? We are running 2005 Standard so I can't do online indexes so I don't want to lock down the table for more than a few seconds if I can help it. Though if we get a good performance increase it will be worth it. Just trying to figure out what kind of damage I'll do to the onlines.Thanks
Heh... with the information you gave us, my answer would be "proably somewhere between 1 second and 3 days". You given absolutely no indication as to the size of the table in row counts, number or type of columns nor what the clustered index is based on.
My recommendation is to make a copy of the table on a test server and test for how long.
If you will notice, I didn't ask you how long it would take. I asked if there was anything that could tell me how long (such as the Tuning Advisor or something.) Regardless, thank you for your recommendation.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 3, 2010 at 5:57 pm
Thank you Ray for going through all of that. I truly don't know why I didn't think to just test it on a dev server. I appreciate your comments.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
May 4, 2010 at 1:25 am
Also the business hours matters mean are you building index in peak hours or off-peak hours, on production always go for off peak hours.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2010 at 5:58 am
Just remember that you'll need to sweat contention for resources, i/o, cpu, memory, tempdb, all are part & parcel of creating indexes, so testing in a completely isolated environment will tell you how long it ought to take, but running it in production may provide different results.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 4, 2010 at 4:19 pm
SQLJocky (5/3/2010)
Jeff Moden (5/3/2010)
SQLJocky (5/3/2010)
Is there anything that can tell me how long it might lock down my table if I add a new index? We are running 2005 Standard so I can't do online indexes so I don't want to lock down the table for more than a few seconds if I can help it. Though if we get a good performance increase it will be worth it. Just trying to figure out what kind of damage I'll do to the onlines.Thanks
Heh... with the information you gave us, my answer would be "proably somewhere between 1 second and 3 days". You given absolutely no indication as to the size of the table in row counts, number or type of columns nor what the clustered index is based on.
My recommendation is to make a copy of the table on a test server and test for how long.
If you will notice, I didn't ask you how long it would take. I asked if there was anything that could tell me how long (such as the Tuning Advisor or something.) Regardless, thank you for your recommendation.
As you will notice, I picked up on that, too... that's why I made the recommendation I did. 😉
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply