是否可能对Server中的某些角色组执行/ TempDB使用限制？

Question

我有一个SQL环境，我是sysadmin。此环境包括公司数据仓库。我在这个环境中做了很多不同的工作，这也是另一个可以访问它的系统管理员。

考虑到它包含数据仓库，生产团队的一些用户也可以访问它。

底层的原始数据非常分散。这些数据来源于许多不同的来源，有时需要过度使用OUTER JOINs、Temp Tables、Procedures等来获取感兴趣的数据。

我已经看到，生产团队中的一些用户有时执行了大量的查询，导致TempDB被50+ GB控制不住，最终导致服务器关闭(到目前为止，我们在同一个驱动器上有servers和TempDB )。

是否有一种方法可以禁止生产团队使用太大的查询，同时不对系统管理员设置相同的限制，因为考虑到复杂的原始数据结构，有时需要执行TempDB广泛的查询？

我不想妨碍生产团队使用/创建Stored procedures、Temp Tables等。我只想研究一下是否有限制执行时间/ TempDB使用的选项。

Answer 1

您不能限制某些用户只使用一定数量的tempdb，相反，您可以限制它的最大大小。

无论如何，将tempdb与您的系统文件放在同一个磁盘上是个坏主意。

您可以监视是谁在消耗您的tempdb，您可以这样做：

    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;

去

    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
    ));

去

    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;

在这里，我创建了一个使用sys.dm_数据库_任务_空格_用法的视图，它实时地显示了现在正在使用超过1Gb的tempdb的用户。然后，我将从该视图读取的查询放在每5分钟运行一次的作业中，因此，如果它的消耗超过1Gb，它就会捕获每个查询以及它的所有者。那就看你怎么反应了。我的操作很简单: tempdb的大小有限(100 to )，因此不可能溢出它。每一个违反者都会受到这样的惩罚：

无法为数据库中的“dbo.Large对象存储系统对象:422539438653440”分配空间，因为“主”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长，创建磁盘空间。错误。

同时，每一次溢出的尝试都被我的工作捕获了。所以我可以很容易地看出他是谁和他在做什么，并采取行动。

Answer 2

我也遇到了类似的问题，因为有一种类型的查询会导致tempdb爆炸，超过400 to。

这就是我们解决它的方法。

我们使用Pinal (https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/)的以下查询来标识语句及其sessionID

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,   --- USE THIS ONE FOR THE KILL
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC

将where子句更改为要对其进行筛选的值。(时间/读/写/SQLText/AmountOfPages在tempdb中)

将此值存储到@SessionIDParam中，然后使用动态sql终止会话。

你可以每时每刻处理这件事。

Answer 3

我不清楚为什么服务器会“关闭”，但是如果您的磁盘中满是文件--包括tempdb的文件--我看到它可能无法使用，至少在重新启动之前是这样。

因此，我的想法--但我不知道这是否有效，尤其是在不重新启动系统更改的情况下--这是您在工作日不想做的事情--我的想法是，为了正常使用，您将tempdb设置为假定给定的文件大小，但不会超出限制。也许一点也不长。那么，您的用户就不能炸毁tempdb并关闭服务器。但你也不能。

如果您想自己使用系统，而不需要对tempdb进行限制(如果需要的话)，那么您只需更改tempdb文件大小设置，然后在工作结束后更改该设置。

...If，你可以这么做。(如果它生长，你可能无法缩小它，而不重新启动。)

如果不允许直接执行此操作，则可以在Server之外使用诸如“磁盘配额”甚至“瘦配置”之类的内容来强制执行磁盘使用限制。但是，当tempdb与您的数据数据库位于同一硬盘上时，这些方法可能无法工作--无法区分它们。除非您可以设置单个文件大小的配额.(如果磁盘是FAT32，那么无论如何就是4G。如果有必要，您可以为一个数据库提供多个文件。甚至tempdb，这也是值得推荐的。)

如果您没有额外的磁盘，从技术上讲，使用第三方软件(我认为)可以设置一个"RAM磁盘“--基本上是一个程序保留了大量的RAM，然后假装它是一个磁盘--并将tempdb文件放在这个RAM磁盘中。如果磁盘被填满了，那么对于您的真实数据文件来说就无关紧要了。但是，这意味着您只是为了这种行为而耗尽RAM。所以这可能不太好。

或者，如果您的坏结果是真正的数据库需要增长，而它们却不能--因为磁盘中装满了tempdb -那么您可以通过维护任务来避免这种情况，即在需要之前使每个数据库都增长。例如，在一夜之间，任何空闲空间小于20%的数据库，都会调整文件的大小以使其更大。然后，如果tempdb吃掉了整个服务器，其他数据库仍然有您首先给它们的空闲空间。