June 15, 2018 at 9:44 am
We've been experiencing "excessive paging" for a while (per perfmon review). Application is sluggish.
Added memory to the server (virtual machine), there's memory for the OS. Last night we increased frequency for updating statistics. This morning, we are still experiencing excessive paging. And application is still sluggish.
Environment:
SQL Server 2014 EE // Windows 2012 R2 standard
Memory on server: 30 GB // OS memory allocation 4 GB // Buffer Pool total size: 18.74 GB
Note we added memory to the server when we discovered excessive paging. That made no difference, we'll reduce it in the near future.
One concern, tempdb, ldf and mdf are all on the same drive. They are separate from the operating system, which is on another. Considering moving mdf and ldf
onto separate drives.
Looked at several counters in perfmon, nothing stood out. My question, is moving tempdb, ldf and mdf to separate drives going to be enough to eliminate excessive paging and improve application performance. Bear in mind we're on a VM. On the surface, moving to separate drives doesn't look like a viable solution. Any comments / URLs would be appreciated.
June 15, 2018 at 9:51 am
First, how do you know this is excessive paging? I assume you mean Windows paging to disk. Or is this VMWare paging? You should be able to tell us that this used to be xx pages/sec and now it's yy pages/sec. If you can't do that, you don't know if this is excessive.
Adding memory to the VM is always good for SQL Server. If this didn't help, have you set max memory for the SQL instance? If not, SQL will use the additional memory. You need to ensure that you leave some ram for the OS.
Lastly, are there other VMs on this host? Maybe you haven't added memory, but configured more and you have VMs fighting for RAM.
SQL Server VMS are nothing like file server, app server, web server, etc. VMs. If your sysadmins/VMadmins don't understand this, they might be causing issues.
June 15, 2018 at 11:23 am
First, how do you know this is excessive paging? I assume you mean Windows paging to disk. Or is this VMWare paging? You should be able to tell us that this used to be xx pages/sec and now it's yy pages/sec. If you can't do that, you don't know if this is excessive
>> should be able to tell us that this used to be xx pages/sec and now it's yy pages/sec >> can't tell you want it used to be, discovered excessive paging after running the perfmon system performance template. The diagnostic report provided indicated that. If you're suggesting that perfmon could be reflecting VMWare paging, I have no experience with that.
Adding memory to the VM is always good for SQL Server. If this didn't help, have you set max memory for the SQL instance? If not, SQL will use the additional memory. You need to ensure that you leave some ram for the OS.
>> have you set max memory for the SQL instance? >> sure that was set to 26 GB. Note that we increased the memory due to what we were experiencing.
Lastly, are there other VMs on this host? Maybe you haven't added memory, but configured more and you have VMs fighting for RAM.
>> are there other VMs on this host? >> Yes we do. There are no issues with the other VMs, just this one.
Thanks for responding!
June 15, 2018 at 11:30 am
bobba - Friday, June 15, 2018 11:23 AMFirst, how do you know this is excessive paging? I assume you mean Windows paging to disk. Or is this VMWare paging? You should be able to tell us that this used to be xx pages/sec and now it's yy pages/sec. If you can't do that, you don't know if this is excessive>> should be able to tell us that this used to be xx pages/sec and now it's yy pages/sec >> can't tell you want it used to be, discovered excessive paging after running the perfmon system performance template. The diagnostic report provided indicated that. If you're suggesting that perfmon could be reflecting VMWare paging, I have no experience with that.
Adding memory to the VM is always good for SQL Server. If this didn't help, have you set max memory for the SQL instance? If not, SQL will use the additional memory. You need to ensure that you leave some ram for the OS.
>> have you set max memory for the SQL instance? >> sure that was set to 26 GB. Note that we increased the memory due to what we were experiencing.
Lastly, are there other VMs on this host? Maybe you haven't added memory, but configured more and you have VMs fighting for RAM.
>> are there other VMs on this host? >> Yes we do. There are no issues with the other VMs, just this one.Thanks for responding!
Only other question, based on all the running VMs has the host been over provisioned, more resources allocated to VMs than actually exists.
June 18, 2018 at 11:44 am
Lynn,
"running VMs has the host been over provisioned, more resources allocated to VMs than actually exists.".
That's a good suggestion. Waiting for our VM administrator to get in (out for the past few days). If that yields nothing, the next step for me would be to move ldf and mdf to their own directories. Not expecting much with that frankly.
Thank you...
June 18, 2018 at 1:31 pm
bobba - Friday, June 15, 2018 9:44 AM...
Note we added memory to the server when we discovered excessive paging. That made no difference, we'll reduce it in the near future.
One concern, tempdb, ldf and mdf are all on the same drive. They are separate from the operating system, which is on another. Considering moving mdf and ldf
onto separate drives.
Looked at several counters in perfmon, nothing stood out. My question, is moving tempdb, ldf and mdf to separate drives going to be enough to eliminate excessive paging and improve application performance. Bear in mind we're on a VM. On the surface, moving to separate drives doesn't look like a viable solution. Any comments / URLs would be appreciated.
I'm not sure I understand everything you're saying, but I have the following comments to add:
- The location of tempdb vs user database files will not really have any effect on OS memory paging, that is for managing I/O issues.
- Moving files to different folders on the same drive will have no effect on I/O issues. Folders are logical items not physical places on disk.
- Are the drives for this VM sourced from a SAN? If so, moving files to a different "disk" may end up just being sourced from the same set of disks on the SAN anyway.
Do you know what is causing the OS memory paging? What else is running on this VM besides SQL Server? If you are sure that it is SQL Server, what queries are running when you are experiencing the excessive paging? Look for running queries that have a large number of reads:SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
DB_NAME(er.database_id) AS database_name,
er.status AS request_status, er.command, --er.percent_complete,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.wait_resource,
er.open_transaction_count, er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.reads, er.writes, er.logical_reads,
OBJECT_NAME(st.objectid, st.dbid) AS object_name
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE s.is_user_process = 1
ORDER BY s.session_id
June 19, 2018 at 1:26 pm
excessive paging resolution is simple, add memory or reduce load.
June 21, 2018 at 7:08 am
Goher2000,
Note that memory was added to the server. Made no difference.
Ran perfmon late yesterday and this morning. Still getting excessive paging. The report showed little activity however, ran for 5 minutes this morning and got the following: CPU - 12%, Disk 10/sec (7 reads / 3 writes), memory at 91% (2.8 GB available).
Load does not look like much. And we're still getting paging. I'm going through the counters, don't expect ;much.
Waiting to hear back from our VM administrator for his analysis. Note we implemented a weekly server reboot, initial response from users was positive. Did not expect that to last long, it didn't. Update statistics as well.
I'm suspicious of an application issue but need additional information. Thank you for responding.
June 21, 2018 at 7:42 am
bobba - Thursday, June 21, 2018 7:08 AMGoher2000,
Note that memory was added to the server. Made no difference.Ran perfmon late yesterday and this morning. Still getting excessive paging. The report showed little activity however, ran for 5 minutes this morning and got the following: CPU - 12%, Disk 10/sec (7 reads / 3 writes), memory at 91% (2.8 GB available).
Load does not look like much. And we're still getting paging. I'm going through the counters, don't expect ;much.
Waiting to hear back from our VM administrator for his analysis. Note we implemented a weekly server reboot, initial response from users was positive. Did not expect that to last long, it didn't. Update statistics as well.
I'm suspicious of an application issue but need additional information. Thank you for responding.
You really do need more information from the VM admin. I noticed earlier that you weren't able to indicate any specifics on the paging - just that a diagnostic report indicated such. But there are also different types of paging and you would want to know exactly what that report is saying - OS, VM paging?. It seems at this point, no one even knows what the issue is. I think you need to know what the issue really is before you can work on addressing it.
Sue
June 21, 2018 at 9:21 am
Sue_H - Thursday, June 21, 2018 7:42 AMbobba - Thursday, June 21, 2018 7:08 AMGoher2000,
Note that memory was added to the server. Made no difference.Ran perfmon late yesterday and this morning. Still getting excessive paging. The report showed little activity however, ran for 5 minutes this morning and got the following: CPU - 12%, Disk 10/sec (7 reads / 3 writes), memory at 91% (2.8 GB available).
Load does not look like much. And we're still getting paging. I'm going through the counters, don't expect ;much.
Waiting to hear back from our VM administrator for his analysis. Note we implemented a weekly server reboot, initial response from users was positive. Did not expect that to last long, it didn't. Update statistics as well.
I'm suspicious of an application issue but need additional information. Thank you for responding.
You really do need more information from the VM admin. I noticed earlier that you weren't able to indicate any specifics on the paging - just that a diagnostic report indicated such. But there are also different types of paging and you would want to know exactly what that report is saying - OS, VM paging?. It seems at this point, no one even knows what the issue is. I think you need to know what the issue really is before you can work on addressing it.
Sue
Basically, Sue is saying you have to identify the problem before you can fix it. Saying you have excessive paging doesn't really say what the problem is you are encountering.
June 21, 2018 at 6:57 pm
did you configure max memory for sql server.. ?
June 26, 2018 at 12:55 pm
Goher2000,
Yes max memory is configured for SQL Server. We added memory as well when we first encountered the problem. Excessive paging still occurs. Previous comment by Lynn Pettis: "Sue is saying you have to identify the problem before you can fix it. Saying you have excessive paging doesn't really say what the problem is you are encountering".
Problem is the VM administrator is buried with other tasks and that report will not be available for a while. We've done as much as we can. Now we wait.
Thank you for responding...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply