January 27, 2009 at 9:20 am
I need to add a non-clustered index (INT column) to a table with ~50 million rows (niiiiiiiiice). :pinch:
Does SQL perform a table lock whilst this runs (for hours) or just a row lock?? Am I doomed to bring the entire system to it's knees by even attempting such a foolish thing.
Err.... :blink:
January 27, 2009 at 9:25 am
well If you have Enterprise Edition you could use ONLINE=ON 😉
If you don't you are probably out of luck 🙁
* Noel
January 27, 2009 at 9:28 am
I believe it will take a shared table lock.
A noncluster on 50 million shouldn't take too long. It's creating clusters on big tables that slows things down a lot. Do it in a maintenance window and there shouldn't be a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 9:29 am
SQL 2005 Standard Ed :crying:
:ermm: Any suggestions how long this might take (in hours) so I can prepare for the phonecall of doom (at 3am when the server throws in the towel)
January 27, 2009 at 9:40 am
FNS (1/27/2009)
:ermm: Any suggestions how long this might take (in hours) so I can prepare for the phonecall of doom (at 3am when the server throws in the towel)
Unless your server is heavily overloaded or your disks are toast, it should take minutes, not hours.
Why don't you test it out on a test server first, then you can get a ballpark figure?
If it helps you at all, I can create a nonclustered index on a 43 million row table (on my desktop) in 3 min, 33 sec.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 10:13 am
Do you have tempdb on a separate drive?
If you do, using SORT_IN_TEMPDB could alleviate the problem a bit.
As long as there are not a lot of constant inserts going on you may be ok.
It is not the same to try on a desktop without user interaction than on an actively used server 😉
* Noel
January 27, 2009 at 10:30 am
noeld (1/27/2009)
It is not the same to try on a desktop without user interaction than on an actively used server 😉
True, but I'd hope most servers are more powerful than my desktop. Plus, if it's done during a maintenance window (and I wouldn't want to try creating indexes while the server was most active) there shouldn't be too much activity.
That example was just to point out that we're not talking hours or days to create a single nonclustered index. Even when I have created then on actively used databases, I don't think I've ever had a single NC index take an hour to create (on tables up to 150 million rows).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 11:19 am
True, but I'd hope most servers are more powerful than my desktop. Plus, if it's done during a maintenance window (and I wouldn't want to try creating indexes while the server was most active) there shouldn't be too much activity.
True, but it was meant to tell the PO that such thing could be just a "very rough" estimate. Totally agree that a server should be much more powerful but I have seen deadlocks ( in large quantities) when this kind of operation is run at inappropriate times.
So you advice still holds, do it at "off-peak" hours.
* Noel
January 27, 2009 at 12:46 pm
noeld (1/27/2009)
So you advice still holds, do it at "off-peak" hours.
Absolutely. A former colleague of mine recently took down a business-critical system by deciding to create an index during peak hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2009 at 12:55 pm
Is there enough available space in the filegroup in which you intend to place this index? If not, you should probably allocate more space to it yourself beforehand rather than rely on autogrow.
Is there an existing clustered index on the table?
If so, how large is it?
If a clus index exists, naturally those column(s) must appear in every n-c index. So, if those columns are long/numerous, it will take longer, and more space, to create the n-c index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply