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 |
|
imnewtothisstuff
Starting Member
2 Posts |
 Posted - 2009-09-02 : 17:44:50
|
Hello guys !i have some questions about rebuilding my indexes on my database tables.I've a SQL Server 2k5 standard edition running and need to rebuild my indexes. They're fragmented up to 99%  Reorganizing just doesnt the trick anymore.I tried to use the "ALTER INDEX REBUILD WITH ONLINE ON", but sql server manager keeps telling me that "ONLINE ON" is only supported in the enterprise edition.Question 1:Am i right in assuming that the database table would be offline when rebuilding my indexes without "ONLINE ON"?Or does it mean that only the indexes are offline and the database access would only slow down while rebuilding?Quesion 2:Is it possible to upgrade my SQL 2k5 Standard to Enterprise Edition on the fly, without reinstalling? e.g. with a registry key change or something like this?BIG thanks in advance !i hate databases :) |
|
|
scuzymoto
Starting Member
17 Posts |
Posted - 2009-09-02 : 18:10:06
|
| See this books online article. http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspxClarifying my post..., while the opposite of 'online' is 'offline' don't take that to mean users will simply get an error, they may just have to wait while the process releases necessary locks.If there are 'waits' that arise, SQL is smart enough to hold the queries in a wait state and fulfill the request as soon as possible. Usually not very long. If you use 'drop existing', some table scans may need to be used in the absence of the index meaning a few queries would be slower than usual. In my experience most typical medium/small indexes ( < 50MB ) can be rebuilt within a 2 or 3 seconds. The larger the index, or heavier usage may dictate that you must do it during low usage or off hours. I rebuilt a 600mb index on my test server to verify and it took 19 seconds. |
 |
|
|
scuzymoto
Starting Member
17 Posts |
Posted - 2009-09-02 : 18:21:46
|
| To answer question number 2, upgrading editions is as involved as upgrading versions, meaning you'd want to take full backup's and expect a reboot/service outage. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-02 : 19:03:12
|
| You could use REORGANIZE instead of REBUILD to avoid the offline issue. I attended a SQL seminar where the data architect from MySpace.com presented their massive SQL Server environment. Due to the number of database servers that they have, they cannot afford to use Enterprise Edition. And because of the offline nature of index rebuilds, they only use REORGANIZE. They also design their system to have very little fragmentation, so index rebuilds are probably not necessary.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." |
|
|
|
imnewtothisstuff
Starting Member
2 Posts |
Posted - 2009-09-03 : 08:18:09
|
| Big thx to all.@tkizer: I have a REORGANIZE running every week, but this doesnt help much, fragmentation does not drop beyond a certain level.@scuzymoto: you wrote: "SQL is smart enough to hold the queries in a wait state and fulfill the request as soon as possible"In your example, the rebuild took 19 seconds. Does this mean that all queries have to wait this time? in my words, that would mean that the affected table is offline for 19 seconds. Am i right?i hate databases :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-09-04 : 21:38:45
|
| Why don't you rebuild them in offline hours? |
|
|
|
|
|
|
|
|
Rebuilding Index - Database offline?