Link to home
Create AccountLog in
Avatar of Zack
ZackFlag for Australia

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.
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

are you in the middle of a ransomware attack?
Avatar of Zack

ASKER

Hi David,

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

Thank you.
Avatar of 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
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Zack

ASKER

Hi Guys,

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