mssql temp文件个数不够导致的锁等待 解决方法

tempdb 数据库 - SQL Server | Microsoft Learn

创建tempdb临时数据文件_最佳实践_云数据库 RDS_云数据库 RDS for SQL Server-华为云

操作场景

tempdb是系统数据库，是一个全局资源，可供连接到SQL Server实例或SQL数据库的所有用户使用 。它是一个临时数据库，无法永久保存数据，作用是给实例中的各种请求处理中间数据，分为主数据文件（.mdf）、次要数据文件（.ndf）和日志文件（.ldf）。当服务重启的时候，tempdb会被重新创建。

tempdb数据库如果在设计上存在缺陷，会存在性能上的问题。尤其是tempdb数据库在一些高并发的场景，如果应用频繁地创建和销毁临时表，会导致实例卡顿从而影响业务。

微软官方建议将临时数据库的文件拆分成多个，一般与逻辑CPU个数相同，超过8个则使用8个数据文件，解决闩锁争用问题每次额外加4个文件。

 

USE master
go
SET NOCOUNT ON

DECLARE
    @time_lock int,
    @show_type int

SELECT
    @time_lock = 1,
    @show_type = 0    -- 0. block only   1. all process    2. block, if not block, show all process
    

-- ================================================
-- Get Lock spid
-- ================================================
DECLARE
    @level smallint,
    @rows int
SELECT
    @level = 0,
    @rows = 0

DECLARE @tb_block TABLE(
    ID int IDENTITY
        PRIMARY KEY,
    block_id smallint,
    spid smallint,
    blocked smallint,
    waittime bigint,
    level smallint,
    UNIQUE(
        spid, blocked, block_id)
        WITH(
            IGNORE_DUP_KEY = ON)
)
INSERT @tb_block(
    block_id, spid, blocked, waittime, level)
SELECT
    CASE
        WHEN blocked = spid OR blocked = 0 THEN spid
        ELSE 0 END, 
    spid, blocked, MAX(waittime), @level
FROM master.dbo.sysprocesses P WITH(NOLOCK)
WHERE spid > 50
    AND blocked > 0
GROUP BY spid, blocked

SELECT
    @rows = @rows + @@ROWCOUNT

IF @show_type = 1 OR (@show_type = 2 AND @rows = 0)
    INSERT @tb_block(
        block_id, spid, blocked, waittime, level)
    SELECT
        CASE
            WHEN blocked = spid OR blocked = 0 THEN spid
            ELSE 0 END, 
        spid, blocked, MAX(waittime), @level
    FROM master.dbo.sysprocesses P WITH(NOLOCK)
    WHERE spid > 50
    GROUP BY spid, blocked

INSERT @tb_block(
    block_id, spid, blocked, waittime, level)
SELECT
    CASE
        WHEN blocked = spid OR blocked = 0 THEN spid
        ELSE 0 END, 
    spid, blocked, MAX(waittime), @level
FROM master.dbo.sysprocesses P WITH(NOLOCK)
WHERE spid > 50
    AND spid IN(
            SELECT blocked FROM @tb_block)
GROUP BY spid, blocked

SELECT
    @rows = @rows + @@ROWCOUNT

WHILE @rows > 0
BEGIN
    SELECT
        @level = @level + 1

    UPDATE A SET
        level = @level,
        block_id = B.block_id
    FROM @tb_block A, @tb_block B
    WHERE A.blocked = B.spid
        AND A.level = 0
        AND A.block_id = 0
        AND B.level = @level - 1
        AND B.block_id > 0

    SELECT
        @rows = @@ROWCOUNT
END

-- ================================================
-- only keep waittime >= @time_lock
-- ================================================
DELETE A
FROM @tb_block A
WHERE NOT EXISTS(
        SELECT * FROM @tb_block
        WHERE block_id = A.block_id
            AND waittime >= @time_lock * 1000)

-- ================================================
-- Get SQL Script
-- ================================================
IF OBJECT_ID(N'tempdb..#LockSQL') IS NOT NULL
    DROP TABLE #LockSQL

CREATE TABLE #LockSQL(
    EventType nvarchar(30), 
    spid int,
    sql nvarchar(4000),
    id int IDENTITY
        PRIMARY KEY)

DECLARE 
    @spid smallint,
    @sql nvarchar(4000)

DECLARE CUR_Lock CURSOR FORWARD_ONLY READ_ONLY LOCAL
FOR
SELECT DISTINCT 
    spid
FROM @tb_block
OPEN CUR_Lock
FETCH CUR_Lock INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT
        @sql = N'
INSERT #LockSQL(
    EventType, spid, sql) 
EXEC(N''DBCC INPUTBUFFER(' + RTRIM(@spid) + N')
WITH NO_INFOMSGS'')
'
    EXEC sp_executesql @sql

    IF @@ROWCOUNT > 0
    BEGIN
        UPDATE A SET 
            spid = @spid
        FROM #LockSQL A
        WHERE IDENTITYCOL = @@IDENTITY
    END

    FETCH CUR_Lock INTO @spid
END
CLOSE CUR_Lock
DEALLOCATE CUR_Lock

-- ================================================
-- Result
-- ================================================
;WITH
BLOCK AS(
    -- Block spid
    SELECT 
        L.ID,
        P.sql_handle,

        L.block_id,
        L.spid , 
        L.blocked, 

        P.waittype,
        waittime = CONVERT(bigint, P.waittime), 
        lastwaittype = CONVERT(nchar(32), LEFT(P.lastwaittype, LEN(P.lastwaittype) - 1)),
        waitresource = RTRIM(CONVERT(nvarchar(256), P.waitresource)),
        DbName = DB.name,
        hostname = RTRIM(CONVERT(nvarchar(256), P.hostname)),
        program_name = RTRIM(CONVERT(nvarchar(256), P.program_name)),
        hostprocess = CONVERT(nchar(10), P.hostprocess),
        loginame = CONVERT(nvarchar(256), P.loginame),  
        P.login_time, 
        nt_domain = RTRIM(CONVERT(nvarchar(256), P.nt_domain)),
        P.net_address,
        P.open_tran,  
        P.status, 
        cputime = P.cpu
    FROM @tb_block L
        INNER JOIN master.dbo.sysprocesses P WITH(NOLOCK)
            ON L.spid = P.spid
        INNER JOIN sys.databases DB WITH(NOLOCK)
            ON P.dbid = DB.database_id
),
BLSQL1 AS(
    -- block sql - 1
    SELECT
        spid,
        sql
    FROM #LockSQL
),
BLSQL2_PL AS(
    SELECT DISTINCT
        P.spid,
        P.sql_handle, P.stmt_start, P.stmt_end
    FROM master.dbo.sysprocesses P WITH(NOLOCK), @tb_block L
    WHERE P.spid = L.spid
        AND P.sql_handle > 0x
),
BLSQL2 AS(
    SELECT
        PL.spid,
        PL.sql_handle,
        sql_text = PT.text,
        sql_current =  SUBSTRING(
                    PT.text,
                    (PL.stmt_start / 2) + 1,
                    CASE PL.stmt_end
                        WHEN - 1 THEN LEN(PT.text)
                        ELSE (PL.stmt_end - PL.stmt_start) / 2 + 1
                    END)
    FROM BLSQL2_PL PL
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) PT
),
BLPATH AS(
    SELECT 
        ID, block_id, spid, blocked, level,
        path = CONVERT(varchar(4000), RIGHT(1000 + block_id, 3))
    FROM @tb_block A
    WHERE NOT EXISTS(
            SELECT * FROM @tb_block
            WHERE block_id = A.block_id
                AND A.blocked = spid)
    UNION ALL
    SELECT
        A.ID, A.block_id, A.spid, A.blocked, A.level,
        path = CONVERT(varchar(4000), B.path + RIGHT(1000 + A.spid, 3))
    FROM @tb_block A, BLPATH B
    WHERE A.block_id = B.block_id
        AND A.blocked = B.spid
        AND B.blocked <> B.spid
)
SELECT
    Path = SPACE(BLPATH.level * 2) + N'|-' + RTRIM(level) + N'-',
    BLOCK.block_id,
    BLOCK.spid , 
    BLOCK.blocked, 

    BLOCK.waittype,
    waittimeFMT = RTRIM(BLOCK.waittime / 3600000)
        + ':'+ RIGHT(100 + (BLOCK.waittime / 60000) % 60, 2) 
        + ':'+ RIGHT(100 + (BLOCK.waittime / 1000) % 60, 2) 
        + '.'+ RIGHT(1000 + BLOCK.waittime % 1000, 3), 
    BLOCK.waittime, 
    BLOCK.lastwaittype,
    BLOCK.waitresource,
    BLOCK.DbName,
    BLOCK.hostname,
    BLOCK.program_name,
    BLOCK.hostprocess,
    BLOCK.loginame,
    BLOCK.login_time, 
    BLOCK.nt_domain,
    BLOCK.net_address,
    BLOCK.open_tran,  
    BLOCK.status, 
    BLOCK.cputime,

    BLSQL1.sql,

    BLSQL2.sql_text,
    BLSQL2.sql_current
FROM BLOCK
    INNER JOIN BLPATH
        ON BLOCK.ID = BLPATH.ID
    LEFT JOIN BLSQL1
        ON BLOCK.spid = BLSQL1.spid
    LEFT JOIN BLSQL2
        ON BLOCK.spid = BLSQL2.spid
            AND BLOCK.sql_handle = BLSQL2.sql_handle        
ORDER BY BLPATH.path
GO

增加个数
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp5', FILENAME = N'K:\SQLTEMP\tempdb5.ndf', SIZE = 8MB, FILEGROWTH = 100MB)

GO


SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END AS Autogrowth,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files
WHERE type=0
GO    

select COUNT(*) as cpu_num from sys.dm_os_schedulers where is_online=1