CODEWITHSUNDEEP
sql-serverdatabaset-sql
Oxoron
Oxoron

Reputation: 674

How to measure the filegroup disk space taken during SP execution?

I regularry get an error "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'FileGroup_Name'" while execution of one Stored Procedure. I've refactored SP a bit, but there is a problem: this error can suddenly happens on prod, and I can't repeat it on a test environment. So, the single way to test my changes - measure disk space taken in the 'FileGroup_Name' disk space.

So, the question is "How to measure the filegroup disk space taken during SP execution"?

P.S. I know it's possible to increase a space, but by some reasons that's not an appropriate solution in my current case.

Upvotes: 0

Views: 264

Answers (1)

sepupic
sepupic

Reputation: 8687

Rather than monitor tempdb disk space, try to exec your sp in SSMS getting actual execution plan.

It's probably not your temporary objects to overflow tempdb, but sort/hash spills, and they'll be reported in the actual plan.

Or you can monitor when and what eats your tempdb.

For example, I use a script to monitor it based on the following view:

create view [dbo].[vw_tempdb_usage]
as    
select session_id,
         cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
         cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
         cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
         cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
       cast(sum(internal_objects_alloc_page_count -
                internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
       cast(sum(user_objects_alloc_page_count -
                user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
from sys.dm_db_task_space_usage
group by session_id
having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count +
      user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0

You can join it to sys.dm_exec_sessions and sys.dm_exec_requests by session_id and get the statement that is currently executed to see what is growing. In our environment there are internal objects that tend to overflow tempdb, and they are always caused by internal sorts. So my advice is to find tempdb spills using actual execution plan.

Upvotes: 1

Related Questions