asked on

Sudden spike in SAN usage

Hi EE,

Today we had a sudden spike on our Flash Array Storage network, something was running for 4 hours and consuming 1 GB of throughput.
We have isolated the spike down to 3 servers (virtual Vmware boxes); these servers are dedicated SQL servers training and dev environments no much activity on them normally.
I have checked to see any SQL jobs were running they weren't and restores were occurring this time.

Any ideas on how to troubleshooting this issue as we don't want this occurring tomorrow morning?

Thank you.

David Johnson, CD

are you in the middle of a ransomware attack?

Zack

ASKER

Hi David,

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

Thank you.

Zack

ASKER

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;

Open 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.

SOLUTION