February 25, 2012 at 8:25 am
TheSQLGuru (2/25/2012)
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.
I get this part.
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.
This part I don't get. Any pointers to reading? Every resource I pull up in Google describe these things as nice to have, especially with a high load SQL Server installation. I can understand Gilamonster's scenario.
February 25, 2012 at 11:01 am
patrickmcginnis59 (2/25/2012)
TheSQLGuru (2/25/2012)
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.I get this part.
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.
This part I don't get. Any pointers to reading? Every resource I pull up in Google describe these things as nice to have, especially with a high load SQL Server installation. I can understand Gilamonster's scenario.
Sorry you don't understand it, but I am not sure I can explain it any better than I did. Assuming you really do understand the differences between latches and locks and how the query is really executing I can't do any better - certainly not on a forum. Now, in a presentation or other FTF scenario, especially with a white board . . .
BTW, note that I did NOT say FusionIO cards are not "nice to have". The VERY larger percentage of installed SQL Servers out there would DRASTICALLY benefit from the almost-micro-second-scale data access provided. I have recommended FusionIO for years - well before they became as popular as they are now. I just said that there are cases where they can lead to unexpected/unintended consequences and expose serious flaws in design/code that were previously hidden by "poor" IO systems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2012 at 11:12 am
Oh, I would imagine the reason you don't see/hear much about this type of thing is that high-end companies almost always tie the hands of their consultants with NDAs that prevent them from even mentioning that the consultant worked with them or even that the client uses SQL Server at all.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2012 at 12:35 pm
removed redundant post
February 25, 2012 at 12:36 pm
removed redundant post
February 26, 2012 at 6:11 am
patrickmcginnis59 (2/24/2012)
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.
Answering my own post!
I can imagine one situation where higher performance io can appear to lead to problems, but its only because its a database accessed via the web, and with the better io performance, it serves more pages and concurrent access increases with the usual accompanying pressures, but effectively, this should be seen at the database level as an increase in load.
Every other resource I found out there just confirms my suspicion that higher performance does indeed let queries and updates finish faster, resulting in as far as I can see reduced concurrent processing. In other words, with slow io, queries and updates last longer, and with concurrent access they have a greater chance of overlapping (executing concurrently), with fast io, they have a lesser chance of overlapping. So in the absence of actual programming errors, I'm betting that the performance and concurrency of even poorly designed queries, updates and unnormalized schemas improve with fast io.
Any insight either way is always welcome because I'm a big ssd user! Feel free to assume I know what a latch is!
February 26, 2012 at 10:37 pm
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.
Thanks!
Out of curiosity, so this new upgraded server is installed with SQL 2005 Enteprise Edition? Even though there's no memory limit on SQL 2005 Standard Edition, but SE only supports 4 CPU and doesnt support Parallel processing of indexing operations. Since you've got 24-core, it must be on EE.
http://qa.sqlservercentral.com/articles/News/comparisonofsqlserver2005editions/1768/
Also, worth mentioning here is the disk partition alignment since you mention your server was upgraded from Windows 2003.
Quote: Windows Server 2008 aligns partitions by default. When servers are upgraded to Windows Server 2008, preexisting partitions are not automatically aligned and must be rebuilt for optimal performance
http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
February 27, 2012 at 6:45 am
I'm with most here: Scratching my head and going "huh?" on Kevin's situation. Gail's makes total sense. Anything that increases available parallelism can easily increase deadlock and related issues. That one's crystal clear. But I've never seen faster I/O cause a performance decrease on a server, nor an increase in blocking/deadlocking. Not by itself. Edge scenarios can (and do) occur, but I'd tend to suspect that something else was done besides an increase in I/O speed.
- 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
February 27, 2012 at 9:49 am
Simon-413722 (2/26/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.
Thanks!
Out of curiosity, so this new upgraded server is installed with SQL 2005 Enteprise Edition? Even though there's no memory limit on SQL 2005 Standard Edition, but SE only supports 4 CPU and doesnt support Parallel processing of indexing operations. Since you've got 24-core, it must be on EE.
http://qa.sqlservercentral.com/articles/News/comparisonofsqlserver2005editions/1768/%5B/quote%5D
It is 64-bit Standard Edition. Is that 4 processor limit a physical or virtual limitation? This server at the client is a dual proc 12 core system.
Also, worth mentioning here is the disk partition alignment since you mention your server was upgraded from Windows 2003.
Quote: Windows Server 2008 aligns partitions by default. When servers are upgraded to Windows Server 2008, preexisting partitions are not automatically aligned and must be rebuilt for optimal performance
http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
Simon
Good thing to remind folks of. Actually, it's a new box, so Win2008 was applied to all new drives. The partition alignment offset was verified on all disks to be 1024kb prior to installing SQL.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 27, 2012 at 9:54 am
WayneS (2/27/2012)
Simon-413722 (2/26/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.
Thanks!
Out of curiosity, so this new upgraded server is installed with SQL 2005 Enteprise Edition? Even though there's no memory limit on SQL 2005 Standard Edition, but SE only supports 4 CPU and doesnt support Parallel processing of indexing operations. Since you've got 24-core, it must be on EE.
http://qa.sqlservercentral.com/articles/News/comparisonofsqlserver2005editions/1768/%5B/quote%5D
It is 64-bit Standard Edition. Is that 4 processor limit a physical or virtual limitation? This server at the client is a dual proc 12 core system.
It's a licensing limitation, it can use more, it's just a violation of your licence. I believe it's proc, but I could be mistaken. check with your MS rep.
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 27, 2012 at 4:51 pm
Gail is right, SQL 2005 licensing is imposed on the physical CPU/sockets. So, if you have 4CPU x 6 cores (=24 cores), then its not a violation of SQL 2005 Standard Edition. Thats how our company has been deploying SQL, having less physical sockets and more cores. But every case might be different so I wouldnt say this is the right way for all deployment.
The licensing model is a bit different for VM as you might have a pool of VMs installed across few physical servers. SQL 2012 licensing will be core-based instead. I wonder if M$ did this on purpose knowing companies might recycle servers to host SQL 2012. They claim its cheaper but again depends on case.
If you have a test environment, you could test out 'max degree of parallelism' config. I've got a workforce app that sends thousands of small requests to the oltp and it seem parallellism caused detrimental performance and disabling parallellism has increased the app stability and performance. Perfmon definitely helps in troubleshooting if too many parallellism is causing issues. Other config option worth reminding are granting lock pages in memory to SQL Service account, making sure SQL max memory is set, creating the proper number of tempdb data files, etc. There are many good article about parallellism option
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply