Sql-server – Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server

query-timeoutsql servertempdb

I have a SQL Environment of which I am sysadmin. This Environment includes the Company data warehouse. I conduct many different jobs from this Environment, and it is also Another sysadmin which has access to it.

Seeing that it contains the data warehouse, some users from the production team has access to it as well.

The underlying raw data is very spread out. The data comes from many different sources, which sometimes requires excessive use of OUTER JOINs, Temp Tables, Procedures etc. in order to fetch the data of interest.

I have seen that some users in the production team has sometimes done massive queries, resulting in TempDB growing uncontrollably by 50+ GB, eventually resulting in the server shutting down (thus far, we have the SQL servers and TempDB on the same drive).

Is there a way to prohibit the production team of using too big queries, while not putting the same restrictions on the sysadmins, as there is sometimes neccessary to conduct TempDB extensive queries given the complex raw data structure?

I do not want to hinder the production team of using/creating Stored procedures, Temp Tables etc. I just want to investigate if there is an option to restrict execution time / TempDB usage.

Best Answer

You cannot limit some users for use the certain amount of tempdb only, instead, you can put the limit to it's max size.

Anyway, putting tempdb at the same disk with your system files is a bad idea.

You can monitor who is consuming your tempdb making smth like this:

    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;

go

    CREATE TABLE [dbo].[tempdb_details](
        [dt] [datetime] NOT NULL,
        [spid] [smallint] NOT NULL,
        [db] [nvarchar](128) NULL,
        [loginame] [nchar](128) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [login_time] [datetime] NOT NULL,
        [internal_objects_diff_Gb] [decimal](10, 2) NULL,
        [user_objects_diff_Gb] [decimal](10, 2) NULL,
        [cmd] [nchar](16) NOT NULL,
        [txt] [nvarchar](max) NULL,
        [Statement] [nvarchar](max) NULL,
     CONSTRAINT [PK_tempdb_details_dt_spid] PRIMARY KEY CLUSTERED 
    (
        [dt] ASC,
        [spid] ASC
    ));

go

    insert into dbo.tempdb_details
               (dt, spid, db, loginame, hostname, program_name, 
                login_time, internal_objects_diff_Gb, 
                user_objects_diff_Gb, cmd, txt, Statement)          
    select GETDATE() as dt,
           p.spid,
           db_name(p.dbid) as db,
           p.loginame,
           p.hostname,
           p.program_name,
           p.login_time,
           v.internal_objects_diff_Gb,
           v.user_objects_diff_Gb,
           p.cmd,
           txt.text as txt,
           CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
                   WHEN p.stmt_start >= 0
                   THEN substring(txt.text, stmt_start/2 + 1,
                                  CASE stmt_end
                                       WHEN -1 THEN datalength(txt.text)
                                       ELSE stmt_end/2 - stmt_start/2 + 1
                                  END)
              END AS Statement
    from dbo.vw_tempdb_usage v join sys.dm_exec_sessions s 
            on v.session_id = s.session_id
         join sys.sysprocesses p
            on v.session_id = p.spid
         cross apply sys.dm_exec_sql_text(p.sql_handle)  txt   
    where v.internal_objects_diff_Gb > 1 or v.user_objects_diff_Gb > 1;

Here I created a view using sys.dm_db_task_space_usage that shows me in real time who is using more than 1Gb of tempdb now. Then I put the query that reads from this view in a job running every 5 minutes, so it captures every query along with it's owner if it consumes more than 1Gb. Then it's up to you how you want to react to it. My actions are simple: tempdb has a limited size (100Gb) so it's impossible to overflow it. Every violator is just rolling back receiving smth like this:

Could not allocate space for object 'dbo.Large Object Storage System object: 422539438653440' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. error.

At the same time every attempt to overflow it is captured by my job. So I can easily see who it was and what he was doing, and take actions.

Related Question