February 22, 2012 at 12:50 pm
ALTER INDEX... REORGANISE instead of rebuild, or persuade for an Enterprise Edition upgrade (for online index build/rebuild)
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
February 22, 2012 at 2:39 pm
GilaMonster (2/22/2012)
ALTER INDEX... REORGANISE instead of rebuild, or persuade for an Enterprise Edition upgrade (for online index build/rebuild)
Okay, so what I see that we're down to is:
1. Upgrade to EE.
2. Change reindexing strategy:
2a. Just reorganize the larger tables (the ones causing problems).
2b. For those larger tables, will need periodic down time to do an index rebuild.
I want to thank everyone for chiming in. Especially you Gail, for keeping us all straight.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2012 at 3:02 pm
p.s. Index reorganise does not update statistics.
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
February 23, 2012 at 7:30 am
Another question related to this. Tech Support people are saying that we didn't start having this problem until the server was upgraded. It went from a 4-core 8GB ram system, to a 24-core, 72GB ram. Also upgraded from Windows 2003 x32 / SS2005 x32 --> Windows 2008 x64 / SS2005 x64.
I don't think that this would cause or magnify this problem... but since the TS people are yelling about it being a SS x64 and/or hardware issue, I'm just checking to confirm.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 23, 2012 at 10:48 am
WayneS (2/23/2012)
Another question related to this. Tech Support people are saying that we didn't start having this problem until the server was upgraded. It went from a 4-core 8GB ram system, to a 24-core, 72GB ram. Also upgraded from Windows 2003 x32 / SS2005 x32 --> Windows 2008 x64 / SS2005 x64.I don't think that this would cause or magnify this problem... but since the TS people are yelling about it being a SS x64 and/or hardware issue, I'm just checking to confirm.
The only way I would see the hardware being an issue is if SQL Server wasn't correctly configured to take advantage of it when it was installed. (Or just plain misconfigured).
Or if the CPU / Memory came "factory broken" or loose on the motherboard, which actually happens on very rare occasions.
February 23, 2012 at 11:09 am
I have seen a case where the hardware was upgraded and the performance went through the floor. Terribly written code, poor design, useless indexes. On the older hardware SQL hadn't been able to run more than 1 query at the same time (only 1 processor), on the newer it could suddenly run 8 (dual quad-core) and everything blocked everything else and the whole thing ground to a halt.
Was great pleasure to turn around to the pm with an 'I told you so' comment when he came to me screaming about it.
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
February 23, 2012 at 11:44 am
Brandie Tarvin (2/23/2012)
WayneS (2/23/2012)
Another question related to this. Tech Support people are saying that we didn't start having this problem until the server was upgraded. It went from a 4-core 8GB ram system, to a 24-core, 72GB ram. Also upgraded from Windows 2003 x32 / SS2005 x32 --> Windows 2008 x64 / SS2005 x64.I don't think that this would cause or magnify this problem... but since the TS people are yelling about it being a SS x64 and/or hardware issue, I'm just checking to confirm.
The only way I would see the hardware being an issue is if SQL Server wasn't correctly configured to take advantage of it when it was installed. (Or just plain misconfigured).
Or if the CPU / Memory came "factory broken" or loose on the motherboard, which actually happens on very rare occasions.
That last strikes me as quite possible; worth checking to see how much store SQL server is using, also check the SQL Server config while you are at it, and looking for hardware fault warnings in windows error logs.
Were the discs replace as part of teh hardware upgrade? I've seen idiocies when that happened.
Another thing to check is the server's network connections (especially with if using SAN) - I once saw a major hardware upgrade cripple a system because someone had failed to understand the network config (so that multigigabit connections were replaced by 100Mbit connections).
Finally, have you tried setting sql server maximum parallelism to 4 to see what happens?
Tom
February 24, 2012 at 6:18 am
GilaMonster (2/23/2012)
I have seen a case where the hardware was upgraded and the performance went through the floor. Terribly written code, poor design, useless indexes. On the older hardware SQL hadn't been able to run more than 1 query at the same time (only 1 processor), on the newer it could suddenly run 8 (dual quad-core) and everything blocked everything else and the whole thing ground to a halt.Was great pleasure to turn around to the pm with an 'I told you so' comment when he came to me screaming about it.
Same here Gail, and it isn't just parallelism either. In high-volume systems especially the "get things done more quickly" that comes with upgraded hardware makes the system start getting activity "stepping on each other's toes" due to the faster speed - whereas before the slowness of the system kept things spread apart. Clients that have upgraded to FusionIO are sometimes seeing a big mess with this, especially when it comes to deadlocks. You really DO need to do GOOD testing (as in at-scale) BEFORE upgrading anything, even to just faster hardware!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2012 at 8:10 am
TheSQLGuru (2/24/2012)
GilaMonster (2/23/2012)
I have seen a case where the hardware was upgraded and the performance went through the floor. Terribly written code, poor design, useless indexes. On the older hardware SQL hadn't been able to run more than 1 query at the same time (only 1 processor), on the newer it could suddenly run 8 (dual quad-core) and everything blocked everything else and the whole thing ground to a halt.Was great pleasure to turn around to the pm with an 'I told you so' comment when he came to me screaming about it.
Same here Gail, and it isn't just parallelism either. In high-volume systems especially the "get things done more quickly" that comes with upgraded hardware makes the system start getting activity "stepping on each other's toes" due to the faster speed - whereas before the slowness of the system kept things spread apart. Clients that have upgraded to FusionIO are sometimes seeing a big mess with this, especially when it comes to deadlocks. You really DO need to do GOOD testing (as in at-scale) BEFORE upgrading anything, even to just faster hardware!!
I'd like to know the mechanism behind this. When thinking this through, slowness of the system in my mind brings things closer together instead of keeping things spread apart, as queries lasting longer by inference brings their start and end times "closer together" with the increased chance of blocking. Increased speed would at first glance make queries and updates farther apart with less chance of blocking, and your explanation seems completely counterintuitive.
Did you ever find out what happened that caused the higher performance hardware to decrease performance? I can understand the addition of processors.
February 24, 2012 at 11:47 am
patrickmcginnis59 (2/24/2012)
When thinking this through, slowness of the system in my mind brings things closer together instead of keeping things spread apart, as queries lasting longer by inference brings their start and end times "closer together" with the increased chance of blocking. Increased speed would at first glance make queries and updates farther apart with less chance of blocking, and your explanation seems completely counterintuitive.
Yes and no. You're forgetting the fact that faster systems can bear heavier loads, which means more people can run the same query at the same time, and more threads can lock up more resources.
This may not be what they were talking about, but I know this happens: Poor code sometimes results from developer attempts to "tweak" and brute-force code to work on old hardware resources. And when a hard-coded "fix" for one system gets migrated to a new, faster system, the code can start locking up resources left and right because suddenly it can run more than one or two or six times simultaneously. I can totally see someone throwing NOLOCK() and index hints all over their code, and throwing a table index for each column in a table into their system, then, when it gets upgraded, SQL freaks because suddenly the schema is ... not quite broken, but very very nasty.
February 24, 2012 at 11:59 am
Brandie Tarvin (2/24/2012)
patrickmcginnis59 (2/24/2012)
When thinking this through, slowness of the system in my mind brings things closer together instead of keeping things spread apart, as queries lasting longer by inference brings their start and end times "closer together" with the increased chance of blocking. Increased speed would at first glance make queries and updates farther apart with less chance of blocking, and your explanation seems completely counterintuitive.Yes and no. You're forgetting the fact that faster systems can bear heavier loads, which means more people can run the same query at the same time, and more threads can lock up more resources.
I can accept that. However, the identified change wasn't the system load, it was the hardware specs. I'm also not questioning that his clients saw problems. The explanation he offered didn't make sense to me, thus my inquiry. He alluded to additional problems independent of increase thread or system load.
Additional load in isolation of hardware upgrades could cause problems too. There could have been additional load that never established a connection with the older hardware, or any number of problems. What didn't make sense was his explanation of "distance between queries" when plotted against some sort of axis as the distance he alluded to. If anything, the increased speed of query execution (in the absense of increased threads or system load) should mean to my simple mind that they get out of each other's way quicker.
February 24, 2012 at 12:40 pm
I've never actually run into the problem that Gail describes. I was just throwing out a W.A.G. as to what she could have meant. I'm sure she'll reply with more details tomorrow, as it's getting pretty late in her timezone.
I doubt she's online right now.
February 24, 2012 at 2:45 pm
It's not quite midnight...
In my scenario the old server had a single processor. So SQL could only run a single query at a time (sure, several could be waiting for locks, IO, latches, etc) but there was a limit to how much could happen concurrently. With the new hardware (8 cores, can't remember the config) suddenly there's more running at the same time (as opposed to queued waiting for a worker), so more locks, more IO load (which the server couldn't satisfy) and everything just locked up nice and solid.
That's increasing CPU count with terrible code, Kevin's scenario I'm not so sure of.
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
February 24, 2012 at 6:28 pm
We also have a number of SS2005 Std Edition production databases with active services running against them. So we have a Saturday night maint window when services are being brought down for few hrs, we kill all active connections and put DBs into a dbo mode. Once thats done, we run our reindexing steps....
February 25, 2012 at 7:29 am
The scenarios I was speaking of is cases where massive IO stalls let to queries doing things like PAGEIOLATCH_xx waits - and thus not actually doing useful work. That "useful work" eventually gets fired once the data page(s) are in the buffer pool and the latches let go. But when that work is executing, things such as LOCKS of various flavors are acquired which can conflict/block/deadlock.
Now picture FusionIO being added to the mix. All of a sudden the hundreds to thousands of milliseconds it would take to get data into RAM go down to 0.1-2 ms. LOTS more stuff is "ready to run" much more quickly and unfortunate consequences arise.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply