Sudden spike in SAN usage

are you in the middle of a ransomware attack?

Hi David,

Good suggestion no our network engineer confirmed we are all good.

Thank you.

Hi Team,

Managed to narrow it down to 1 server I think I cracked it here is the explanation of what I think happened:

The server has a database called 'Allocate' and it uses memory rather than temp tables to store datasets and as it collates information for user requested reports according to the development notes.  

The SQL server only has 16GB associated with it and according to the following query;

SELECT
[DatabaseName] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
COUNT_BIG(*) [Pages in Buffer],
COUNT_BIG(*)/128 [Buffer Size in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY [Pages in Buffer] DESC; 

Select allOpen in new window

12GB is associated purely with the Allocate DB.

It’s highly probable that when the reports were being run the SQL server ran out of memory and because MAXDOP wasn’t configured on this server, all the CPU’s available began fighting for memory that wasn’t there causing an IO storm.

Let me know my explanation holds water.

Cheers.

Hi Guys,

Thank you for your input here are some links for anyone else who wants to look into forced parameterization:

https://www.brentozar.com/blitz/forced-parameterization/
https://www.brentozar.com/archive/2018/09/can-forced-parameterization-go-wrong/
https://www.dbrnd.com/2016/12/sql-server-increase-query-performance-using-a-forced-parameterization-simple-parameterization/

Thank you.

Hi Guys,

Thank you very much for the input it's very much appreciated.