Sql-server – Identify cached data for a specific query in data cache

buffer-poolpage-life-expectancyperformancesql serversql-server-2008-r2

I'm trying to identify, if possible, how long it takes for data being cached by a stored procedure to be taken out of the cache.

I know there is the PLE counter in sys.dm_os_performance_counters, but I think in my scenario that won't help me very much.

Disclaimer, all of this was handed down to me to improve:

I have a stored procedure which returnes about 250 Mb of data into the cache, when being ran by one user. The procedure returns user-specific information, so the procedure is called with a @userID input parameter.

From the data returned by this stored procedure (about 3000 rows) it displays row-specific information, for one row at a time. There is a functionality that when a user presses a "Next" button in the application UI it goes and gets the row-specific information for the next row in the result and so on.

Due to the data already being in cache, when testing with a single user the response time was ~1 second, because all of the 250 Mb would fit in cache without any problem and not being cleared very fast.

I've tested the procedure with a cold cache and the execution time was about 10 seconds to return the data.

Now, I have about 30 users running the stored procedure in the same time interval and they have reported experiencing about a 10 second delay when pressing the "Next" button.

Surprisingly this is the same exec. time when pushing data into the cache and because there are 30 users, my conclusion at this point is because the total amount of data being pushed into cache is about 7Gb (total server memory is 16Gb), the data gets pushed in and out of cache very often.

This is all just a theory at this point, but I'm trying to identify some metrics that directly point at this being the issue. I know PLE could be an indicator, but is there anything that could be more specific, for a query for example.

My recommendation would be to re-write the stored procedure in a more efficient way, but that would take lots of time and business is not very interested in spending time for that. If I can prove, with this theory of mine, that there is no way, then they might allow for a re-write of the procedure.

Oh, and crazy enough, they're not very happy about increasing server memory either. Talking about not giving me options..

Best Answer

If the goal of your question is to determine if your stored procedure is having to do physical reads to satisfy the query (instead of retrieving the data from memory), you might be able to use the following query in the stored procedure to capture the physical reads during execution and possibly save that information for auditing purposes

declare @start_reads bigint
select @start_reads = reads from sys.dm_exec_requests where session_id = @@spid

-- Your query here 

select reads - @start_reads from sys.dm_exec_requests where session_id = @@spid
Related Question