September 28, 2017 at 3:53 pm
We have a large SQL cluster that is in use but not performing well. I am not a DBA, but I am trying to get some insight into what the problems might be. The server is a Clustered Cisco C-Series, with 256GB of Ram and is connected to All Flash SAN for storage. Monitoring is continually reporting high memory utilization and disk latency, but when you look at the MS SQL instance utilization in Windows, it shows a max of 2.5GB in use, but 242 committed. I have monitored it overnight for two days and every night I get alarms for memory utilization, but the only time it is highly utilized is during the backup window, when the network traffic and CPU % go up. The memory for SQL never shows more than 2.5GB committed. I don't know what the high and low memory limits are, but I was told SQL can use up to 242 leaving 14GB for the OS and all the system processes. This server swaps memory like crazy, but we cannot figure out why it won't use more. Any ideas would be much appreciated, keeping in mind I can only suggest possibilities, not actually make changes.
September 28, 2017 at 5:45 pm
aman4God - Thursday, September 28, 2017 3:53 PMWe have a large SQL cluster that is in use but not performing well. I am not a DBA, but I am trying to get some insight into what the problems might be. The server is a Clustered Cisco C-Series, with 256GB of Ram and is connected to All Flash SAN for storage. Monitoring is continually reporting high memory utilization and disk latency, but when you look at the MS SQL instance utilization in Windows, it shows a max of 2.5GB in use, but 242 committed. I have monitored it overnight for two days and every night I get alarms for memory utilization, but the only time it is highly utilized is during the backup window, when the network traffic and CPU % go up. The memory for SQL never shows more than 2.5GB committed. I don't know what the high and low memory limits are, but I was told SQL can use up to 242 leaving 14GB for the OS and all the system processes. This server swaps memory like crazy, but we cannot figure out why it won't use more. Any ideas would be much appreciated, keeping in mind I can only suggest possibilities, not actually make changes.
Umpteen things can be at play here, including how you are monitoring memory, what your settings are, what your version and EDITION of SQL Server is, etc. I am curious where you got the 242 and 14 numbers from too.
Run dbcc memorystatus to see what SQL Server is REALLY using for a LOT of different memory categories/types.
How is performance.
What do you mean by "this server swaps memory like crazy"?
Any error log messages (in any error log) relevant to the issue?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 28, 2017 at 11:18 pm
How are you checking memory? Where do you see that SQL's only using 2.4GB of memory?
What's your max server memory setting?
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
September 29, 2017 at 9:36 am
So I will try to give as much info as I can. As I said, I do not administer this server or am not a DBA, just trying to help solve a problem. I am using a tool called Dynatrace to monitor the server. It reports back the Windows information for process memory utilization as a basic stat. I got the information about the memory configuration from the person who did the initial server build. He said the server was installed with 256GB of Ram and SQL was configured to use up to 242GB and leave the remaining for the System. The swapping is from a report from the DBA team using spotlight monitoring and it reports on how fast the pages move in and out of RAM. I am told the expected timing is supposed to be 10-12 minutes and our server swaps pages every 1-2 minutes or less sometimes. The questions about our current config are related to the poor performance we are seeing from the end-users. There are a large amount of systems and reports that connect to the database and run, and I know there are a lot of factors that could come into play. When I see the server under load and the system reports that SQL has over 200GB of committed memory and the SQL server service is only using 2.5GB, the CPU is rarely above 30% and the Pure storage is idling, so there seems to be a bottleneck somewhere. The host reports stalled IO and high latency, but the storage shows little activity. Also, the pagefile is set fairly large (38GB) and running on C:\ which is a spinning disk local to the server. There are 12k page faults per second being reported as well. I don't see any errors or warnings related to SQL in the system logging and none in the SQL logging.
It is SQL Server 2014 (standard) running on Server 2012 R2
I am working on the output of the dbcc memorystatus.
(result) An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
September 29, 2017 at 10:22 am
What kind of SAN fabric sits between the server and the SAN? 1 Gigabit ? 10 Gigabit? You may be I/O bound to some degree. The difference between a local disk (typically a 6 GB/sec interface with max data transfer closer to between 4 and 5) and a disk on a SAN can be substantial. A 1 Gigabit fiber connection is extremely slow in comparison, by roughly an order of magnitude. You may want to grab some Perfmon stats for I/O Waits and the IO Queue, which might help illustrate where the bottleneck is, or is not.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 11:38 am
The Pure storage array has 8 total 8Gb fiber connections to a pair of Nexus 5ks, 4 per fabric. The host itself has dual 8Gb fiber links to the same 5ks.
September 29, 2017 at 12:54 pm
We could go back and forth for weeks and not be able to help you with this. My recommendation is hire a professional to give your system a performance review. Odds are that person could find the root cause of this in minutes to a few hours (and likely find other issues too).
BTW, any chance you have 32 bit SQL Server installed? Don't laugh - I still come across that at clients!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2017 at 1:12 pm
I understand, and I appreciate the replies everyone has given so far. I guess I was just hoping to maybe run across someone that had dealt with a similar issue in the past. I agree there are so many different things to troubleshoot. I thought maybe there was some caveat that might cause a SQL server with plenty of memory to not fully utilize it. When I look at disk activity like someone mentioned above the C: is the most active drive on the server, so I don't know what is up with the thing. SQL binaries are the only thing on C, the install is on the SAN drives.
Anyways, thanks for trying. I will check on the 32-bit thing you mentioned as well when troubleshooting there are no dumb ideas.
One last question. Is it realistic to think that if the system was starved for resources because too much was available to SQL that it would cause slowness on the overall server? With the pagefile on C: and the activity of the drive it seems like it might be trying to page too much because the server doesn't have enough RAM.
September 29, 2017 at 2:47 pm
aman4God - Friday, September 29, 2017 9:36 AMWhen I see the server under load and the system reports that SQL has over 200GB of committed memory and the SQL server service is only using 2.5GB
Where are you getting those numbers from?
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
September 29, 2017 at 2:50 pm
aman4God - Friday, September 29, 2017 9:36 AMSo I will try to give as much info as I can. As I said, I do not administer this server or am not a DBA, just trying to help solve a problem. I am using a tool called Dynatrace to monitor the server. It reports back the Windows information for process memory utilization as a basic stat. I got the information about the memory configuration from the person who did the initial server build. He said the server was installed with 256GB of Ram and SQL was configured to use up to 242GB and leave the remaining for the System. The swapping is from a report from the DBA team using spotlight monitoring and it reports on how fast the pages move in and out of RAM. I am told the expected timing is supposed to be 10-12 minutes and our server swaps pages every 1-2 minutes or less sometimes. The questions about our current config are related to the poor performance we are seeing from the end-users. There are a large amount of systems and reports that connect to the database and run, and I know there are a lot of factors that could come into play. When I see the server under load and the system reports that SQL has over 200GB of committed memory and the SQL server service is only using 2.5GB, the CPU is rarely above 30% and the Pure storage is idling, so there seems to be a bottleneck somewhere. The host reports stalled IO and high latency, but the storage shows little activity. Also, the pagefile is set fairly large (38GB) and running on C:\ which is a spinning disk local to the server. There are 12k page faults per second being reported as well. I don't see any errors or warnings related to SQL in the system logging and none in the SQL logging.It is SQL Server 2014 (standard) running on Server 2012 R2
I am working on the output of the dbcc memorystatus.
(result) An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
That error message is from SSMS, shut it down (SSMS) and restart it then run the DBCC command.
September 29, 2017 at 3:25 pm
GilaMonster - Friday, September 29, 2017 2:47 PMaman4God - Friday, September 29, 2017 9:36 AMWhen I see the server under load and the system reports that SQL has over 200GB of committed memory and the SQL server service is only using 2.5GBWhere are you getting those numbers from?
From Task Manager which I have read is not the correct way to monitor SQL memory usage.
September 29, 2017 at 3:30 pm
aman4God - Friday, September 29, 2017 3:25 PMGilaMonster - Friday, September 29, 2017 2:47 PMaman4God - Friday, September 29, 2017 9:36 AMWhen I see the server under load and the system reports that SQL has over 200GB of committed memory and the SQL server service is only using 2.5GBWhere are you getting those numbers from?
From Task Manager which I have read is not the correct way to monitor SQL memory usage.
SQL is not going to be using only ~2GB of memory. Don't use Task Manager
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply