March 14, 2016 at 2:14 pm
I was told that the memory needs to be atleast 30% of the largest database size. Is there a document from microsoft that could validate it?
March 14, 2016 at 3:22 pm
I doubt there's a document to support that. It's a bad estimate. I've successfully run 200gb databases on servers with 16gb of memory. It really depends on the load on the server, the queries, how many connections you have coming in, how much data is being moved, not simply database size. More memory is always better, but simply saying 30% of the largest database is not how I would make the point. Instead, focus on wait statistics to show what is causing the server to run slow. You can absolutely show that you need more memory that way.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 14, 2016 at 3:48 pm
Grant Fritchey (3/14/2016)
I doubt there's a document to support that. It's a bad estimate. I've successfully run 200gb databases on servers with 16gb of memory. It really depends on the load on the server, the queries, how many connections you have coming in, how much data is being moved, not simply database size. More memory is always better, but simply saying 30% of the largest database is not how I would make the point. Instead, focus on wait statistics to show what is causing the server to run slow. You can absolutely show that you need more memory that way.
Grant, I'm seeing a lot of PAGIOLATCH_XX waittypes and also CXPACKET but I think CXPACKET is due to high MAXDOP setting. Could PAGIOLATCH_XX waittypes be a good indication?
March 14, 2016 at 5:34 pm
sunny.tjk (3/14/2016)
Grant Fritchey (3/14/2016)
I doubt there's a document to support that. It's a bad estimate. I've successfully run 200gb databases on servers with 16gb of memory. It really depends on the load on the server, the queries, how many connections you have coming in, how much data is being moved, not simply database size. More memory is always better, but simply saying 30% of the largest database is not how I would make the point. Instead, focus on wait statistics to show what is causing the server to run slow. You can absolutely show that you need more memory that way.Grant, I'm seeing a lot of PAGIOLATCH_XX waittypes and also CXPACKET but I think CXPACKET is due to high MAXDOP setting. Could PAGIOLATCH_XX waittypes be a good indication?
Memory might help but finding and fixing those queries will help even if you have a TB of ram.
--Jeff Moden
March 15, 2016 at 2:56 am
sunny.tjk (3/14/2016)
Could PAGIOLATCH_XX waittypes be a good indication?
It's an indication of buffer pool churn. Whether that's due to insufficient memory, bad queries or poor indexing is another matter.
Oh, and CXPacket isn't a problem. All it means is that queries are running in parallel. Most of the times when I've seen it very high it's been a combination of too-low cost threshold for parallelism, bad queries and poor indexing.
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
March 15, 2016 at 4:53 am
What the other people have said...
CXPacket is only a concern if you also see other CPU waits. The one thing I will say, sight unseen, if your Cost Threshold for Parallelism is still set to the default of 5, change that to a higher number. 20 if it's a reporting system, 50 if it's an OLTP system.
Other than that, more info would be needed to know if it's a memory issue. Look up Paul Randal's query for wait stats and use that. It eliminates waits that don't really indicate anything at all. Post the top 10.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2016 at 5:29 am
Grant Fritchey (3/15/2016)
CXPacket is only a concern if you also see other CPU waits.
I tend to worry about it if I also see parallelism-related latch waits. But that's worry to the extent of increase cost threshold, set maxdop based on NUMA config (if lots of CPUs) and recommend tuning of queries.
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
March 16, 2016 at 4:37 pm
Grant Fritchey (3/15/2016)
What the other people have said...CXPacket is only a concern if you also see other CPU waits. The one thing I will say, sight unseen, if your Cost Threshold for Parallelism is still set to the default of 5, change that to a higher number. 20 if it's a reporting system, 50 if it's an OLTP system.
Other than that, more info would be needed to know if it's a memory issue. Look up Paul Randal's query for wait stats and use that. It eliminates waits that don't really indicate anything at all. Post the top 10.
Grant,
Attached is the screenshot of the results I got when i ran Paul Randall's wait stats query.
We increased Cost Threshold from 20 to 40 which didn't improve the performance but changed it back to 5 after consulting MSFT.
I'm thinking we are having a high MAXDOP value of 8( MSFT recommends 3) due to which we are seeing high CXPacket waittypes.
March 16, 2016 at 4:45 pm
sunny.tjk (3/16/2016)
Grant Fritchey (3/15/2016)
What the other people have said...CXPacket is only a concern if you also see other CPU waits. The one thing I will say, sight unseen, if your Cost Threshold for Parallelism is still set to the default of 5, change that to a higher number. 20 if it's a reporting system, 50 if it's an OLTP system.
Other than that, more info would be needed to know if it's a memory issue. Look up Paul Randal's query for wait stats and use that. It eliminates waits that don't really indicate anything at all. Post the top 10.
Grant,
Attached is the screenshot of the results I got when i ran Paul Randall's wait stats query.
We increased Cost Threshold from 20 to 40 which didn't improve the performance but changed it back to 5 after consulting MSFT.
I'm thinking we are having a high MAXDOP value of 8( MSFT recommends 3) due to which we are seeing high CXPacket waittypes.
Once again, I recommend that although hardware and "Black Arts" setting can help, if you're truly having performance problems, you won't get a better ROI than finding and fixing the performance challenged code. You can literally get hundreds and, sometimes, thousands of times better performance that way. Make no doubt about it, though... it's not going to be just one piece of code that you will fix.
--Jeff Moden
March 17, 2016 at 3:29 am
sunny.tjk (3/16/2016)
I'm thinking we are having a high MAXDOP value of 8( MSFT recommends 3) due to which we are seeing high CXPacket waittypes.
Nope!
Oh, and CXPacket isn't a problem. All it means is that queries are running in parallel. Most of the times when I've seen it very high it's been a combination of too-low cost threshold for parallelism, bad queries and poor indexing.
If you want to 'fix' the high CXPacket waits (which probably aren't a problem anyway), tune your queries, tune your indexes. The vast majority of the time I've seen excessive parallelism it's because of inefficient queries and poor indexing.
Find the inefficient queries, fix them.
Oh, and I doubt 3 would be the recommended maxdop. It's usually set to a value related to the number of physical cores in a NUMA node. So unless you somehow have a 3-core processor, it's probably not a good value.
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
March 17, 2016 at 3:44 am
I'm getting here late, but I'm with Gail and Jeff. It sure sounds like the problem is primarily one of query tuning and possibly the cost threshold for parallelism. Change the cost threshold, but then, as Jeff says, identify the poorly performing queries and start to work on them. That's where you're most likely to get the most bang for the buck of your time and effort.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 17, 2016 at 8:22 am
GilaMonster (3/17/2016)
sunny.tjk (3/16/2016)
I'm thinking we are having a high MAXDOP value of 8( MSFT recommends 3) due to which we are seeing high CXPacket waittypes.Nope!
Oh, and CXPacket isn't a problem. All it means is that queries are running in parallel. Most of the times when I've seen it very high it's been a combination of too-low cost threshold for parallelism, bad queries and poor indexing.
If you want to 'fix' the high CXPacket waits (which probably aren't a problem anyway), tune your queries, tune your indexes. The vast majority of the time I've seen excessive parallelism it's because of inefficient queries and poor indexing.
Find the inefficient queries, fix them.
Oh, and I doubt 3 would be the recommended maxdop. It's usually set to a value related to the number of physical cores in a NUMA node. So unless you somehow have a 3-core processor, it's probably not a good value.
I know the problem queries, so I'll go and tune them.
Also, we have 3 physical quad core processors (single NUMA )which is part of the problem I think. We may have to add more processors.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply