October 2, 2019 at 3:51 pm
I'm analyzing PAL results during a period of poor performance and finding warnings of excessive "Workfiles Created/sec." My understanding is that these workfiles are created when data being processed is too big to fit into memory and spilled to disk. But MSSQL is only using 30GB of the 59GB max it is allocated, and the server shows 26GB or memory available (2GB free + 24GB standby).
Why would anything be spilled to disk if that much memory is unused? How does MSSQL decide the data is too big to fit into memory? This server has auto-update statistics on and the dragging process is one where data is inserted, updated, and finally deleted from staging tables keyed to many other tables with cascading enabled, so there are many locks in a large transaction, but this same process works much faster on a larger scale on a busier server (though with occasional deadlocks).
According to the PAL report, sql server is creating 1800 workfiles/sec during these performance problems without even trying to utilize additional memory that's readily available.
October 2, 2019 at 3:56 pm
One reason for available memory not being used is inappropriate memory grants. Make sure that you are updating your statistics regularly.
John
October 2, 2019 at 4:02 pm
"Work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors. Worktables are used for queries that use various spools (table spool, index spool, and so on)."
So you're probably looking at an execution plan with some sort of spool which gets spilled to TempDB right away.
October 2, 2019 at 5:53 pm
@dinors: the problematic process leans heavily on cursors - is actually a procedure that calls a dozen other procedures that each have their own cursor. If this causes it to immediately spill to disk as workfiles, how do I avoid the spill without completely rebuilding the code? Adding memory hints to the procedure, like "OPTION(min_grant_percent=15,max_grant_percent=25," wouldn't seem to help since memory pressure isn't causing the spill.
I don't understand the logic of forcing workfiles to disk when memory is available. It seems like a waste of resources. Seems like this would also mean a cursor on a memory-optimized table would basically write the table to disk in the form of workfiles.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply