You can then determine what queries are consuming the most resources, and perform addition tuning, But unless you limit the amount of memory allocated to SQL, you will not see a drop in memory usage.Īnd, if your infrastructure admin wants to see the database servers running at 10-15 percent memory like a file server or a web server, then he needs some education. Jonathan Kehayas's article posted above is the best place to start. You need to determine what the correct max server memory setting may be for your server, and adjust it accordingly. Can you'll share some insights into the troubleshooting process. How would you end up finding that rogue query.
I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. Okay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. GROUP BY src.object_name, src.object_type, src.index_name, src.Index_Type INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id SELECT TOP 10 src.object_name, src.object_type, src.index_name, src.index_type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128 INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc AS object_type, i.name AS index_name, i.type_desc AS index_type, p., p.index_id, au.allocation_unit_id SUM(CAST( AS BIGINT)) / 1048576 AS empty_MB To see what is in the buffer pool, take a look at: sys.dm_os_buffer_descriptorsĬASE database_id WHEN 32767 THEN 'ResourceDB' ELSE db_name(database_id) END AS Database_name, Most likely the top type of clerk consuming memory is: MEMORYCLERK_SQLBUFFERPOOL SUM(pages_kb) / 1024 AS size_MB -SUM(single_pages_kb + multi_pages_kb) / 1024 AS size_MB Start by looking high level at: sys.dm_os_memory_clerks If you do already have a reasonable maximum server memory setting for your instance, then here are some ways to see what SQL Server has in memory. If the operating system is doing a lot of paging then there could be a problem because SQL Server wants to manage its own memory usage, and if something is out in an OS Swap file that SQL Server thinks is already in RAM then you are doing extra writes and reads to disk that you don't want to do. Memory spiking at 90% is not necessarily a problem and in many cases for SQL Server would be considered normal.
Usually, when looking into task manager it just says sqlservr.exe is consuming memory which is fair and it will consume memory as this is how the product is designed unless you place a cap with maxmemory setting.so basically how would one end up troubleshooting such a scenario when you don't exactly know what is causing a memory churn. to troubleshoot the issue but knowing what is in the buffer pool or for that matter the result set from these dmv's doesn't give much insight or make sense when you're fire fighting. I know people say use dmv's such as sys.dm_os_memory_clerks etc. I was working today with the internal infrastructure admin and their alarms started ringing as the memory was spiking > 90%.
0 kommentar(er)
