September 26, 2008 at 8:26 am
Hi everyone, my dev server has been rebuilding index for five table for the past 95 hours :Whistling: XD what should I do? should I restart my server? My sql service can't be stopped and my sql server agent is "change pending..." Would I loose all the work of the last 95 hours? Any kind of advice is welcomed, please :crying:
September 26, 2008 at 9:30 am
Wow, that is LONG
How much was the fragmentation %? I wonder if REBUILD (instead of REORGANIZE) would've been quicker
Was this tested in other environments to estimate the hours first?
September 26, 2008 at 11:03 am
Jerry Hung (9/26/2008)
Wow, that is LONGHow much was the fragmentation %? I wonder if REBUILD (instead of REORGANIZE) would've been quicker
Was this tested in other environments to estimate the hours first?
The fragmentation was over 60% and my tables are quite big, over 200 thousand records, and nope there was tried in any other enviroments we just got the new hw. My dev server is quite strong, it has 2 dual core processors and 4gb of RAM. It is running on Windows Server R2.
Suggestions????? :crying:
September 26, 2008 at 11:09 am
Are things still running, meaning do you see lots of disk IO taking place? I think it could run long, and yes, you're lose the work.
If you have enterprise, you can run this online. If not, I might schedule this overnight, maybe Fri night late, and see how long it takes for one table.
September 26, 2008 at 11:28 am
everything is still running, just my RAM is being exploited, more than 1.5gb is being used. Disk space is still normal, no changes, and my database log hasn't increased, could that mean a problem? like if the process is not responding and I don't know about this?
September 26, 2008 at 12:22 pm
chileu17 (9/26/2008)
Jerry Hung (9/26/2008)
Wow, that is LONGHow much was the fragmentation %? I wonder if REBUILD (instead of REORGANIZE) would've been quicker
Was this tested in other environments to estimate the hours first?
The fragmentation was over 60% and my tables are quite big, over 200 thousand records, and nope there was tried in any other enviroments we just got the new hw. My dev server is quite strong, it has 2 dual core processors and 4gb of RAM. It is running on Windows Server R2.
Suggestions????? :crying:
Rebuilding indexes on a couple of hundred thousand rows, even on a low-end machine, shouldn't take anywhere NEAR that long. I would expect a minute or two TOPS, even on weak hardware.
I have a copy of SQL Dev Edition on a Pentium D 3Ghz with 4 Gig of RAM, and I just ran an index rebuild on a 1-million-row table with 4 indexes. That took 18 seconds. Not exactly high-end hardware.
Something has definitely gone wrong. If it's still rebuilding after 95 hours, then I would give up on it, assume that work is lost, dump and revert to a prior backup. You should have dev scripts and such from that time period, in source control or at least as files, so you should be okay in that regard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 12:40 pm
Just for fun
I REBUILD (not REORGANIZE) 5 indexes on a 0.5 million table, on SQL 2008 RTM, 5GB RAM (limit to 3GB for SQL though), 4 CPU
1 Clustered (8% fragment)
3 Nonclustered (46, 50, 1% fragmented)
1 Spatial
That took 3 minutes
95 hours is definitely bad, I suggest re-try as well
September 26, 2008 at 12:46 pm
95 hours is definitely bad, I suggest re-try as well
... checking for error messages in both SQL log and Event log.
September 26, 2008 at 12:58 pm
Wouldn't hurt to fire up Activity Monitor and see if the rebuild is blocked.
😎
September 26, 2008 at 2:38 pm
thanks guys for the quick replies, I just killed my 100 hour process XD
I have restarted my dev server and everything seems to be back to normal, my biggest table is more than 10 million records and I read somewhere that doing this "online" would take a lot of time so that is why I was hesitating on killing the rebuilding process. As you probably know, you don't get the chance to mess up with so much data too often and I didn't want to loose that much time in vane.
I will restart the rebuilding process in my big tables in a sec, but as far as I know I couldn't do the "offline" index rebuilding, would that be faster? and is it a possibility to first do a dbcc shrinkdatabase first and then the rebuilding, would that a better sequence of actions?
Thanks again for the replies.
PS I couldn't use the activity monitor because my sql server wouldn't accept no connections :crying:
September 26, 2008 at 3:14 pm
I finished rebuilding one of my indexes on more than 10 million rows. I was happy to see this working. Thanks.
But there is one thing now, even these process has ended my sql server process is still "using" 1.8gb of Ram and it's doing almost a 100 thousand pages reads and over 500 thousands reads. I checked my activity monitor and there is process using my tempdb, is that ok? or should I restart my sql server everytime I do this rebuilding process?
Thanks in advance.
September 29, 2008 at 8:18 am
SQL Server is built to take a certain amount of RAM (as much as it can unless you tell it otherwise) and hang onto it indefinitely. That's by design, and is usual/expected. Don't worry about that.
Same goes for processing in tempdb. That's normal.
On the earlier question about shrinking the database before rebuilding the indexes: DON'T! Shrinking the database should be reserved for special situations, like you just intentionally deleted a ton of data that you don't intend to ever replace. Never do it as a regular action. Fragments your indexes and tables, destroys performance.
On the question about reads, I'm not sure what you're asking. Reads are usual if you're doing anything with the data. Please clarify that part of the question.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2008 at 10:39 am
Thanks for the reply, I described my pages read and written just because I thought it had something to do with all the RAM memory being consumed, but as you explained before it is just because SQL Server takes as much RAM as it needs and never let go of it, even though that doesn't seem right to me :ermm: but it seems like sql server works that way.
About the shrinking command, you say that if I do that, it would kill my performance right?but what if after shrinking my db, I rebuild/reorganize my indexes, would that be ok??
I have a medium size db. It has more than 30gb of data, and on my dev enviroment I did shrink it. It went from more than 30gb to 15gb :ermm: is that alright? or what type of performance consecuences should I expect from it??By the way, I am rebuilding most of my indexes there XD
September 30, 2008 at 5:38 am
SQL Server takes as much RAM as it needs and never let go of it, even though that doesn't seem right to me :ermm: but it seems like sql server works that way.
Nah, SQL Server do occupy memory but it frees if it has excess of it and some OS process requires it. Freeing up of memory does not happen very frequently as generally OS has enough memory left with it to perform the tasks.
September 30, 2008 at 7:11 am
You can shrink a database if you need to. Rebuilding indexes afterwards will help.
I just recommend against it because, (a) it fragments indexes and reduces performance, and (b) it usually just needs to grow again, and that results in file fragmentation and all the hard drive problems that causes.
If you're rebuilding the indexes right after, that takes care of (a). So long as it doesn't have to grow itself again in your dev evironment, that takes care of (b).
It's just recommended against as a general practice. As with pretty much everything in SQL, there are exceptions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply