SQLServer 常用工具类SQL

以下SQL，均是从互联网或同事那里鼓捣来的，我把它们收集起来，便于以后查阅。感谢那些提供的大神们。

工作者线程

对于 32 位操作系统：
总可用逻辑 CPU<=4 时，最大工作者线程 =256。
总可用逻辑 CPU>4 时，最大工作者线程 =256+（（逻辑 CPU-4）×8）。
对于 64 位操作系统：
总可用逻辑 CPU<=4 时，最大工作者线程 =512。
总可用逻辑 CPU>4 时，最大工作者线程 =512+（（逻辑 CPU-4）×16）。
可使用如下SQL进行检测

--查看最大工作者线程数，max_workers_count 字段即为最大工作者线程数
SELECT  * FROM sys.dm_os_sys_info 

--查看当前工作者线程
SELECT COUNT(*) FROM sys.dm_os_workers 

SQLOS

SQLOS是一个底层的SQL Server 的“专用操 作系统”，用于管理调度、I/O 争用、内存管理和其他资源协调等工作。这个组件是承接 SQL Server 和 Windows 的中间层，具有相当重要的作用。

--每个调度对应一行，一个逻辑CPU 对应一个用户调度，并 且展示每个调度的负载和健康情况
SELECT * FROM Sys.dm_os_schedulers 

--返回每个正在等待资源的任务
SELECT * FROM Sys.dm_os_waiting_tasks

--memory clerks 用于分配内存。这个 DMV 显示所有内存 clerk 的情况和每个 clerk 占用多少 内存
SELECT * FROM Sys.dm_os_memory_clerks

缓存

Buffer Pool

--查看每个数据库缓存大小
SELECT  COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,
        CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id)
        END AS 'Database'
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
        database_id
ORDER BY 'Cached Size (MB)' DESC

执行计划

--查看当前已缓存的计划数及所占的 MB 数
SELECT  
    COUNT(*) AS 'Number of Plans' ,       
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)' 
FROM  sys.dm_exec_cached_plans `

查询等待类型TOP10

自上一次SQL Server启动之后，所有非系统等待信息中总等待时间排名最久的10个等待类型。根据这些等待类型，可以粗略地找到一个进一步查看问题的切入点。对于CPU压力，通常相关的等待类型有SOS_SCHED-ULER_YIELD、CXPACKET和CMEMTHREAD

SELECT TOP ( 10 )
        wait_type ,
        waiting_tasks_count ,
        ( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
        max_wait_time_ms ,
        CASE waiting_tasks_count
          WHEN 0 THEN 0
          ELSE wait_time_ms / waiting_tasks_count
        END AS avg_wait_time
FROM    sys.dm_os_wait_stats
WHERE   wait_type NOT LIKE '%SLEEP%'   -- 去除不相关的等待类型
        AND wait_type NOT LIKE 'XE%'
        AND wait_type NOT IN -- 去除系统类型   
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
  'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
  'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
  'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
  'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
  'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC

查询CPU消耗最高的10个语句

SELECT TOP ( 10 )
        SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE QS.statement_end_offset
                      END - QS.statement_start_offset ) / 2 ) + 1) AS statement_text ,
        execution_count ,
        total_worker_time / 1000 AS total_worker_time_ms ,
        ( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms ,
        total_logical_reads ,
        total_logical_reads / execution_count AS avg_logical_reads ,
        total_elapsed_time / 1000 AS total_elapsed_time_ms ,
        ( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms ,
        qp.query_plan
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

查询数据库中所有的表及其字段的类型、长度等

SELECT distinct  
    OBJECT_NAME(A.object_id) AS 表名,
    A.name as 列名,   
    B.name as 类型,  
    A.max_length AS 字段长度,  
    A.precision AS 位数,  
    A.scale AS 小数位
FROM sys.columns A 
  left outer join systypes B on A.system_type_id = B.xtype 
WHERE
  OBJECTPROPERTY(A.object_id, 'IsMsShipped') = 0 
  AND B.name != 'sysname'

查看数据库中各个表的数据行数

exec sp_executesql @stmt=N'begin try 
            SELECT TOP 1000
            (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
            a3.name AS [schemaname],
            a2.name AS [tablename],
            a1.rows as row_count,
            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
            a1.data * 8 AS data,
            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
            FROM
            (SELECT
            ps.object_id,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN row_count
            ELSE 0
            END
            ) AS [rows],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
            SUM (ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            GROUP BY ps.object_id) AS a1
            LEFT OUTER JOIN
            (SELECT
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
            WHERE it.internal_type IN (202,204)
            GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
            INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
            INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
            WHERE a2.type <> N''S'' and a2.type <> N''IT''
            end try
            begin catch
            select
            -100 as l1
            ,   1 as schemaname
            ,       ERROR_NUMBER() as tablename
            ,       ERROR_SEVERITY() as row_count
            ,       ERROR_STATE() as reserved
            ,       ERROR_MESSAGE() as data
            ,       1 as index_size
            ,       1 as unused
            end catch',@params=N''