I recently made the switch to Pulse (you can read about it
here), so I am working with lots of new clients lately. One of those clients was having issues with refreshing their data warehouse and SSAS cubes. The process usually takes around 30 minutes to complete, but the last week it would easily take between 5 and 12 hours. Not really the way you want it to go, right?
Problem
One of the packages in SSIS was taking ages to process so I used
sp_WhoIsActive by Adam Machanic (
b |
t) and came to the conclusion the process had a wait type of PREEMPTIVE_OS_WAITFORSINGLEOBJECT, which can be found in sys.dm_os_wait_stats.
Jonathan Kehayias (
b |
t) mentions on
MSDN:
For a thorough understanding of (Non-)Preemptive waits in SQL Server, Pinal Dave (
b |
t) has written an excellent blog post
here. A little excerpt:
PREEMPTIVE: Simply put, this wait means non-cooperative. While SQL Server is executing a task, the OS interrupts it. This leads to SQL Server to involuntarily give up the execution for other higher priority tasks. This is not good for SQL Server as it is a particular external process which makes SQL Server to yield. This kind of wait can reduce the performance drastically and needs to be investigated properly.
You see the word
drastically there? That's kinda what happened :)
Continuing with my investigation, I executed the source query from the package manually in SSMS and it took around 20 seconds. Executing the package in SSDT succeeded but resulted in the following two informational messages:
The buffer manager has allocated # bytes even though the memory pressure has been detected and repeated attempts to swap buffers have failed.
The buffer manager failed a memory allocation call for 65536 bytes, but was unable to swap out any buffers to relieve memory pressure. # buffers were considered and # were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
As the second message states there is not enough memory available, Well there should be, because I remembered there recently had been a memory increase from 16 to 32 GB on the server. But about that RAM, it is shared across the server... and between different applications... That started me thinking about the max server memory for SQL Server and the memory left for the rest of the server (OS, SSRS and SSIS!), because they're all running on the same server.
Solution
The maximum server memory can be set by editing the Server Properties (in SSMS):
or by using plain T-SQL. The beneath example sets the maximum memory to 4 GB:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
When the memory increased to 32 GB recently, the max server memory was also set from 13 to 26 GB, so the memory available to processes other then SQL Server didn't increase much. I changed the memory limit back to 20 GB to release an extra 6 GB to the rest of the apps running on the server. This way the (DTExec-)process for SSIS had enough memory to perform the tasks in-memory and the nightly process was running as normal again.
HTH
