| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-04-04 : 08:26:21
|
| Hey Guys,I am rebuilding my indexes for the first time in awhile. Some of my indexes are probably pretty out of control (150 million records)I started going thru different tables 3 hours ago, and about 20 mins ago my SQL Server became unresponsive to sql requests. I am trying to connect and cancel the job, but cant connect to sql.All other requests to sql are timing out. I am on the server fine thru terminal services, and can see the high CPU usage.Any suggestions how I can stop the rebuild, so that my server is responsive?Thanks very much!!Mike123 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-04 : 09:15:25
|
Try connecting with DAC to gain access to sql server.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-04 : 09:17:37
|
If you have access through ts and restart the sqlervice, it may take as long time as until now to rollback the operation.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 09:20:54
|
| Very tough call ... I mean an hour to rollback is not the end of the worldanother 10 hours to index is a problem :S |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 09:22:22
|
| Restarting MSSQLSERVER service via CONTROL PANEL -> ADMIN TOOLS -> SERVICES then clicking "RESTART"Is this how you would advise restarting the services ? After I do this just wait for the rollback to complete ?Any risks doing this ? Thanks very much!mike123 |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 10:13:39
|
| Im watching sqlservr.exe and while its taking 90+% CPU, the IO Read Bytes / IO Write Bytes are not moving ..Does this mean im frozen ? Any suggestions on how to proceed greatly apprecaited.... I am really stuck in a bad spot right now!thanks againmike123 |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 13:14:47
|
| high cpu and no i/o sounds like a loop error condition ?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-04 : 17:22:59
|
| I doubt it's in a loop condition as it was very thoroughly tested, plus I've got it running on about 40 SQL instances with maybe 100-200 databases. It's also being used by many people across the world from what I can tell via the email I get from my blog. I have a feeling that since it hasn't been run in so long that it's just got a ton of work to do. Perhaps it's being blocked by some other process, you'd have to connect via the DAC to see. For future runs of it where it hasn't been run in a very long time, I'd suggest doing it slowly by passing in 95 for the min percent parameter and keep bumping that number down each run until you've done them all. And yes that's how you'd restart the service. If it doesn't come up quickly, you can check its progress via the Application Log in Event Viewer. It'll give you a percentage in there.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." |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 17:46:47
|
| Hey Tara,With no I/O do you still think it was doing work ? I tried restarting the service many times, but always got timed out. I upped the timeout limit to 5 minutes and tried multiple times with no luck. I was atttempting to connect locally via windows authentication under admin as well as remotely via sql. I am not familiar with DAC but will be looking into it. I don't understand either how it would loop endlessly only for me this time. It didn't do this when I ran on re-organize, but mind you it didnt complete all indexes either :SI rebooted and everything was fine. I'm not sure the DB would be fine if it was in the middle of actually doing rebuilding ? I will stick to your advice on passing 95 and going down. Would you recommend doing this on re-organize first ? For example, re-organize down to 1%, then start rebuilding at 95% and work my way down ?Thanks very much!Mike123 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-04 : 18:47:54
|
| If you have multiple cores or CPUs on the box, set your max degree of parallelism to something less than your total number. I used to have this problem on quad and 8 core boxes and it always went away if I set it to 1 or 2 under the number. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 20:13:09
|
| Hey Rob,Sounds like a good suggestion, any downsides to this ?Thanks!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-04 : 20:36:15
|
| We have 16 cores and contacted Microsoft directly on what number to set the max degree of parallelism to. We were told 8, although we may bump it down further as we were pegged at 100% CPU recently and saw the parallelism thing going crazy on tons of queries. A simple index resolved the issue, but to avoid the issue later, we'll probably drop the value down again.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." |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-04 : 20:47:50
|
| I've never seen any downsides, only improvement, especially on the quad core. |
|
|
|
|
rebuilding index = server cpu redlined