November 22, 2016 at 9:36 am
Hello,
We have migrated SQL standard edition from 2008R2 to SQL 2014. After the migration we are seeing huge amount of blocking and overall SQL instance is very slow.
Major wait stats we are seeing is Resource_Semaphore_Query_Compile and some of the queries are requesting more amount of memory and waiting on compile time.
Also, this new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logic processors each (60 total ), since this is a standard edition, max memory is set to 128GB and i believe there is some issues with Maxdop setting and how SQL 2014 is utilizing the processors.
Maxdop is set to 4 and we changed it to 2, still no luck.
Regarding the resource semaphore wait types , i did updated stats on all databases with full scan and we cannot tune the queries as they are encrypted and vendor code which used to perform fine in SQL 2008 R2.
Any one faced this issue ? Please help me we are in production now and our overall system is very slow.
November 22, 2016 at 10:41 am
Robin35 (11/22/2016)
Hello,We have migrated SQL standard edition from 2008R2 to SQL 2014. After the migration we are seeing huge amount of blocking and overall SQL instance is very slow.
Major wait stats we are seeing is Resource_Semaphore_Query_Compile and some of the queries are requesting more amount of memory and waiting on compile time.
Also, this new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logic processors each (60 total ), since this is a standard edition, max memory is set to 128GB and i believe there is some issues with Maxdop setting and how SQL 2014 is utilizing the processors.
Maxdop is set to 4 and we changed it to 2, still no luck.
Regarding the resource semaphore wait types , i did updated stats on all databases with full scan and we cannot tune the queries as they are encrypted and vendor code which used to perform fine in SQL 2008 R2.
Any one faced this issue ? Please help me we are in production now and our overall system is very slow.
Quick questions, what is the compatibility level? What is the cost threshold for parallelism? Why are you changing the maxdop?
😎
What is the point of this?
2 physical cores with 30 logic processors each (60 total )
Are you saying that you are multiplying the number of physical cores by 30?
November 22, 2016 at 11:12 am
The biggest issue with upgrading to 2014 is the new cardinality estimator. Overall, it helps, but there are edge cases, especially with queries that were poorly written to begin with, where performance suffers severely. The biggest question is the compatibility level. That's what determines which cardinality estimator is in use.
If possible, capture execution plans on the old version of the database and compare them to the new version. If those plans are different (maybe even radically so), check the row estimates. If they're way off, it's likely the cardinality estimator. In that case, if you can't modify the queries, you'll have to look to plan guides as a mechanism of applying the traceflag hint [/url]to adjust the cardinality estimator.
However, you don't know this is problem until you gather some data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2016 at 11:16 am
Eirikur Eiriksson (11/22/2016)
Robin35 (11/22/2016)
Hello,We have migrated SQL standard edition from 2008R2 to SQL 2014. After the migration we are seeing huge amount of blocking and overall SQL instance is very slow.
Major wait stats we are seeing is Resource_Semaphore_Query_Compile and some of the queries are requesting more amount of memory and waiting on compile time.
Also, this new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logic processors each (60 total ), since this is a standard edition, max memory is set to 128GB and i believe there is some issues with Maxdop setting and how SQL 2014 is utilizing the processors.
Maxdop is set to 4 and we changed it to 2, still no luck.
Regarding the resource semaphore wait types , i did updated stats on all databases with full scan and we cannot tune the queries as they are encrypted and vendor code which used to perform fine in SQL 2008 R2.
Any one faced this issue ? Please help me we are in production now and our overall system is very slow.
Quick questions, what is the compatibility level? What is the cost threshold for parallelism? Why are you changing the maxdop?
😎
What is the point of this?
2 physical cores with 30 logic processors each (60 total )
Are you saying that you are multiplying the number of physical cores by 30?
Thanks for the reply. Compatibitliy is set to 120 and we cannot change it back atleast for now until we find the root cause. CTP is 25 and reason for changing the maxdop is old server has 4 NUMA nodes and Maxdop is set to 4 and it worked without issues.... but in the new server its 2 NUMA nodes so changed the maxdop to 2.
How ever, i have enabled the Soft NUMA node using the startup trace flag 8079 (please see below link )and i'm no longer seeing Resource semaphore query compile waits (atleast for now, will have to wait for couple of hours to see how the performance is going to be) but still there is some blocking going on which we have seen in old server too.
https://msdn.microsoft.com/en-us/library/ms345357(v=sql.120).aspx
Sorry to confuse you on cores, 2 sockets, Physical cores - 30 and logical cores - 60
Please let me know if you have any questions.
November 22, 2016 at 11:24 am
Thanks Grant for your input. We have faced the CE issues on other servers when we migrated to SQL 2014 and for some queries we did use the query hint and it resolved and we had to rewrite the query to satisfy new CE in SQL 2014 but the problem here there's isn't any particular that's slow, whole instance is slow and heavy blocking it's hard to find the queries (we cannot see the queries as they are encrypted)...if there's a particular session that's causing the blocking we could find and tune the query but 30 different sessions from different databases are blocking 80 to 100 different sessions which doesnt seem to be an issue with particular query so that's the reason i'm inclined to tune the any server settings....as i mentioned in previous post i'm no longer seeing Resource Semaphore waits but i'm really not sure if enabling the soft Numa will fix the issue.
Also, i have to determine the correct MAXDOP setting now to ensure it will not cause any issues. On old server, we have 4 numa nodes and our MAXDOP is set to 4 and CTP is 25but where as in the new server has 2 NUMA nodes and we set the MAXDOP to 2 but after enabling SOFT NUMA, it created 2 additional numa nodes , so i'm thinking to set MAXDOP 4.
Please recommend if i'm miss interpreting anything.
November 22, 2016 at 11:42 am
All kinds of issues here:
1) I assume this is testing BEFORE upgrading, right? That way you don't have production issues that could have been avoided. It irks me to no end when my long-term clients do this without telling me about it!! :hehe:
2) What type of licensing do you have? Standard Edition SQL 2014 can only use 20 cores in some licensing scenarios.
3) MAXDOP upper limit is not based on NUMA nodes. It is based on the number of physical cores per NUMA node. Therefore you could go up to 15 and still keep remote memory accesses down.
4) I don't know your app but I am going with locking/blocking being caused by
a) mismatched data types (quite possibly due to ADO.NET or EF/nHibernate defaults)
b) functions around columns in WHERE clause
c) suboptimal indexing
All of those have fixes, although you can only do the third of them. And the scans those things cause can be chewing up your memory, leading to a host of memory-related performance issues.
5) If you ARE now in production with this arrangement I strongly urge getting a very experienced performance tuning consultant on board quickly to help stop the hemorrhaging. There are some good ones here on SSC.com. In my experience few magic-bullet fixes often alleviates the crush enough to open up headroom so you can hobble by until the vendor gets their act together.
6) BTW, did you happen to go from rotating disk to SSD during this migration??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 22, 2016 at 11:56 am
I don't see any mention of cost threshold for parallelism? What's that set to?
Also, in addition to the cardinality estimator, all sorts of changes have been made to the optimizer. Any or all of these could be affecting you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2016 at 12:10 pm
TheSQLGuru (11/22/2016)
All kinds of issues here:1) I assume this is testing BEFORE upgrading, right? That way you don't have production issues that could have been avoided. It irks me to no end when my long-term clients do this without telling me about it!! :hehe:
2) What type of licensing do you have? Standard Edition SQL 2014 can only use 20 cores in some licensing scenarios.
3) MAXDOP upper limit is not based on NUMA nodes. It is based on the number of physical cores per NUMA node. Therefore you could go up to 15 and still keep remote memory accesses down.
4) I don't know your app but I am going with locking/blocking being caused by
a) mismatched data types (quite possibly due to ADO.NET or EF/nHibernate defaults)
b) functions around columns in WHERE clause
c) suboptimal indexing
All of those have fixes, although you can only do the third of them. And the scans those things cause can be chewing up your memory, leading to a host of memory-related performance issues.
5) If you ARE now in production with this arrangement I strongly urge getting a very experienced performance tuning consultant on board quickly to help stop the hemorrhaging. There are some good ones here on SSC.com. In my experience few magic-bullet fixes often alleviates the crush enough to open up headroom so you can hobble by until the vendor gets their act together.
6) BTW, did you happen to go from rotating disk to SSD during this migration??
We did test in non production environments before moving to production but the issues is happening only on production and yes we moved to prod and its live now.
we have core based license if i believe correctly. we dont have access to view the code to tune the queries. We will have to engage the vendor. We haven't moved the storage as part of this migration.
November 22, 2016 at 12:12 pm
Grant Fritchey (11/22/2016)
I don't see any mention of cost threshold for parallelism? What's that set to?Also, in addition to the cardinality estimator, all sorts of changes have been made to the optimizer. Any or all of these could be affecting you.
Grant, CTP value is 25.
November 22, 2016 at 12:14 pm
does it has to do anything with high amount of available memory on this server, 870Gb available.
Also, we are having query compile issue and i found this artcle...anyone aware of this ?
https://support.microsoft.com/en-us/kb/3024815
This hotfix introduces a new trace flag -T 6498. This trace flag lets more than one large query compilation gain access to the big gateway when there is sufficient memory available. It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory. For example, this allows for two large query compilations on a 64 GB Target Memory computer.
After you apply this hotfix, you have to enable trace flag -T 6498 to enable the new behavior.
November 22, 2016 at 12:31 pm
Robin35 (11/22/2016)We did test in non production environments before moving to production but the issues is happening only on production and yes we moved to prod and its live now.
we have core based license if i believe correctly. we dont have access to view the code to tune the queries. We will have to engage the vendor. We haven't moved the storage as part of this migration.
So what is different between your testing and now that you are live in production? Did you test on the same hardware? Did you test at the same scale of concurrent activity and on the same production database?
My 20 cores limit was for Enterprise Edition + CAL licensing. SQL 2014 Standard Edition is limited to just SIXTEEN CORES (https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx#CrossBoxScale). This means you are stuck on just ONE of your CPUs (with just one on the second), with some rather nasty side-effects due to the unbalanced nature of your configuration. So not only is the RAM way above what your SQL Server could use, your core count is to.
Your vendor likely doesn't have the know-how to help you with this problem. Based on past experience with countless software developers and vendors over the years I can promise they also have a bunch of issues with their database and code too.
If you want a consultant that can give you immediate relief from your current situation (and to have someone that can evaluate the vendor's stuff, and bring a host of other benefits) drop me a PM.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 22, 2016 at 12:37 pm
Robin35 (11/22/2016)
does it has to do anything with high amount of available memory on this server, 870Gb available.Also, we are having query compile issue and i found this artcle...anyone aware of this ?
https://support.microsoft.com/en-us/kb/3024815
This hotfix introduces a new trace flag -T 6498. This trace flag lets more than one large query compilation gain access to the big gateway when there is sufficient memory available. It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory. For example, this allows for two large query compilations on a 64 GB Target Memory computer.
After you apply this hotfix, you have to enable trace flag -T 6498 to enable the new behavior.
The OP doesn't have 870GB available to SQL Server. This is Standard Edition so limited to 128GB, which is set as max memory here IIRC.
That fix is most beneficial for very large enterprise edition data warehouses, although multiple-concurrent-exceptionally-complex-query systems with standard edition and memory at or near the limit of 128GB can also benefit.
There are many more probable causes of compilation issues on most systems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 22, 2016 at 4:28 pm
We have observed one thing. Our old server has 4 processors with 8 cores each and 8 logical processors each...no hype threading enabled but new server has 2 processors with 15 core each and 30 logical processors each..hyper threading enabled. does hyper threading makes any difference in sql server performance ?
Also, SQL 2008 and 2014 standard edition capacity limits on processors has changed.
SQL 2008 - 4 procesors - per processor socket, and not per logical CPU basis
SQL 2014 - Limited to lesser of 4 Sockets or 16 cores
this means we will only be able to use 16 cores in SQL 2014 but in SQL 2008 we are able to use 32 processors. Is this true ?
November 23, 2016 at 3:58 am
we will only be able to use 16 cores in SQL 2014 but in SQL 2008 we are able to use 32 processors. Is this true ?
Yes. If Windows is reporting 30 cores per processor with HT enables then you have 15 real cores. If it is reporting 60 cores per processor then you have 30 real cores. SQL will take the first 16 cores available, which if you are booting to bare metal will be the first 8 real cores and the first 8 HT cores on socket 0. If you are booting as a guest then the actual assignment will be less predictable and may even be dynamic but will still be limited to 16 cores and with the added 'feature' of increased context switching.
You have a wonderful box for Enterprise Edition, but a poor box for Standard Edition.
This means you are stuck on just ONE of your CPUs, with some rather nasty side-effects due to the unbalanced nature of your configuration
from TheSQLGuru
There is a lot of sense in this. Personally I have not hit this problem but have seen it documented in a few places.
My advice is to get a consultant for a few days who has the skills to deal with this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 23, 2016 at 4:10 am
Robin35 (11/22/2016)
We have observed one thing. Our old server has 4 processors with 8 cores each and 8 logical processors each...no hype threading enabled but new server has 2 processors with 15 core each and 30 logical processors each..hyper threading enabled. does hyper threading makes any difference in sql server performance ?Also, SQL 2008 and 2014 standard edition capacity limits on processors has changed.
SQL 2008 - 4 procesors - per processor socket, and not per logical CPU basis
SQL 2014 - Limited to lesser of 4 Sockets or 16 cores
this means we will only be able to use 16 cores in SQL 2014 but in SQL 2008 we are able to use 32 processors. Is this true ?
1) Hyperthreading is usually best left on for modern CPUs for most SQL Server workloads. But this is not an absolute by any stretch. And with your currently messed-up configuration - it's anyone's guess. Stress-testing with your app, at scale, is the only way to know (once you get your cores balanced, obviously).
2) As for limits, with 2012+ I believe you get 16 physical cores or 32 hyperthreaded cores. 2008 was limited by physical CPUs, but this was set in the day when a quad-core was top-of-the-line. I do not know how 2008 works or if it has a limit to the number of physical/hyperthreaded cores per CPU.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply