Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
prodigy2006
Yak Posting Veteran
66 Posts |
 Posted - 2009-09-25 : 12:34:51
|
| During peak hours , I created indexes on few tables. Next thing that I hear is that the users of different application cannot log into the server. My question is,creating indexes on tables of put tremendous load on the server?Answers will be appreciated.Thanks,Sunny |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-25 : 16:44:09
|
| Yes it can. If you are using Enterprise Edition, then make sure you specify the ONLINE option. And if your tempdb is optimized (one data file per CPU), then you should also use the SORT_IN_TEMPB option. Using both of these options drastically reduces the impact to the users while the indexes are being created.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
prodigy2006
Yak Posting Veteran
66 Posts |
Posted - 2009-09-25 : 17:08:21
|
Could you please tell me where do i mention ONLINE and SORT_IN_TEMPDB in an index creation script.Thanks,Sunilquote:
Originally posted by tkizer
Yes it can. If you are using Enterprise Edition, then make sure you specify the ONLINE option. And if your tempdb is optimized (one data file per CPU), then you should also use the SORT_IN_TEMPB option. Using both of these options drastically reduces the impact to the users while the indexes are being created.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
prodigy2006
Yak Posting Veteran
66 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Server down