February 27, 2018 at 11:46 pm
February 28, 2018 at 9:05 am
There really isn't enough information here to draw any conclusions. You appear to have a 6 Gb virtual machine, with only 4 GB allocated to SQL Server, which for most business workloads, would likely be highly inadequate. However, as you haven't said what kind of workload that SQL instance is supposed to be able to handle, we have no idea whether that quantity is inadequate or not. Mind you, it would have to be really small to fit in just 4 Gb. You also appear to be saying that Windows is suddenly using the paging file, and I have to guess that means that your workload is slowing down. As you didn't provide any quantification of just how much slow down you're seeing, or exactly why the use of the paging file is a problem, I don't know what to suggest. However, seeing that your last re-boot was 272 days ago, it would probably be a good idea to re-boot the machine anyway, and see if the problem remains. If that fails to resolve the issue, then find out what changed in the application, or in the database, that might have started the slowdown. Either way, if the problem continues, you'll need to provide a LOT more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2018 at 10:03 am
Thank you for your reply.
Sorry, this is a test server
I do not know if there is a slowdown for the database because I do not know how to measure the workload.
I'm just wondering why windows uses the swap file now when it was not the case before?
I'm going to restart the server and I'll let you know
thank you
anthony
February 28, 2018 at 10:58 pm
sgmunson - Wednesday, February 28, 2018 9:05 AMThere really isn't enough information here to draw any conclusions. You appear to have a 6 Gb virtual machine, with only 4 GB allocated to SQL Server, which for most business workloads, would likely be highly inadequate. However, as you haven't said what kind of workload that SQL instance is supposed to be able to handle, we have no idea whether that quantity is inadequate or not. Mind you, it would have to be really small to fit in just 4 Gb. You also appear to be saying that Windows is suddenly using the paging file, and I have to guess that means that your workload is slowing down. As you didn't provide any quantification of just how much slow down you're seeing, or exactly why the use of the paging file is a problem, I don't know what to suggest. However, seeing that your last re-boot was 272 days ago, it would probably be a good idea to re-boot the machine anyway, and see if the problem remains. If that fails to resolve the issue, then find out what changed in the application, or in the database, that might have started the slowdown. Either way, if the problem continues, you'll need to provide a LOT more detail.
Hi,
Thank you for your reply
I have reboot my server.
Memory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)
Why SQL server.exe and reporting services use now less than before restart.
Thank you
March 1, 2018 at 1:32 am
Don't trust the task manager, it's a blatant liar!
😎
What is the output of this code?
SELECT
OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;
SELECT
ISNULL(DB_NAME(OBD.database_id),'ResourceDB') AS DBNAME
,OBD.page_type AS page_type
,SUM(OBD.row_count) AS SUM_row_count
,SUM(OBD.free_space_in_bytes) AS SUM_free_space_in_bytes
,SUM(OBD.read_microsec) AS SUM_read_microsec
FROM sys.dm_os_buffer_descriptors OBD
GROUP BY DB_NAME(OBD.database_id)
,OBD.page_type
ORDER BY DB_NAME(OBD.database_id) ASC
,OBD.page_type ASC;
March 1, 2018 at 1:54 am
antoninSQL - Wednesday, February 28, 2018 10:58 PMMemory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)Why SQL server.exe and reporting services use now less than before restart.
Regardless of how accurate those figures are, SQL Server will only grab the memory it needs to start. It'll take more and more as and when it needs it (for example to cache data or plans) until it reaches either the max server memory or the available memory on the computer. It'll give back memory when requested by the operating system, subject to the value of min server memory. That's a basic overview - there may be circumstances under which it will grab more memory than expected, especially in older versions. I'm not sure whether or not Reporting Services behaves in the same way.
John
March 1, 2018 at 2:38 am
Eirikur Eiriksson - Thursday, March 1, 2018 1:32 AMDon't trust the task manager, it's a blatant liar!
😎What is the output of this code?
SELECT
OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;SELECT
ISNULL(DB_NAME(OBD.database_id),'ResourceDB') AS DBNAME
,OBD.page_type AS page_type
,SUM(OBD.row_count) AS SUM_row_count
,SUM(OBD.free_space_in_bytes) AS SUM_free_space_in_bytes
,SUM(OBD.read_microsec) AS SUM_read_microsec
FROM sys.dm_os_buffer_descriptors OBD
GROUP BY DB_NAME(OBD.database_id)
,OBD.page_type
ORDER BY DB_NAME(OBD.database_id) ASC
,OBD.page_type ASC;
Hi
This is output after reboot
---------------
| total_page_file_kb |
| 7339512 |
| available_page_file_kb |
| 5600084 |
| total_page_file_kb |
| 7339512 |
| available_page_file_kb |
| 5600084 |
| system_cache_kb |
| 1293788 |
| kernel_paged_pool_kb |
| 170576 |
| kernel_nonpaged_pool_kb |
| 55228 |
| system_memory_state_desc |
| Available physical memory is high |
-----
Thank you
March 1, 2018 at 3:07 am
John Mitchell-245523 - Thursday, March 1, 2018 1:54 AMantoninSQL - Wednesday, February 28, 2018 10:58 PMMemory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)Why SQL server.exe and reporting services use now less than before restart.
Regardless of how accurate those figures are, SQL Server will only grab the memory it needs to start. It'll take more and more as and when it needs it (for example to cache data or plans) until it reaches either the max server memory or the available memory on the computer. It'll give back memory when requested by the operating system, subject to the value of min server memory. That's a basic overview - there may be circumstances under which it will grab more memory than expected, especially in older versions. I'm not sure whether or not Reporting Services behaves in the same way.
John
Thank you for this information John
anthony
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply