网站首页> 日志> SQL Server 常用脚本
SQL Server 常用脚本
编辑时间:2017-05-04 11:56:54 作者:李大宝 浏览量:827

1、查看连接对象

 USE master
 GO
 --如果要指定数据库就把注释去掉
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='mydb'

2、然后使用下面语句看一下各项指标是否正常,是否有阻塞,正常情况下搜索结果应该为空。

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'  
ORDER BY [cpu_time] DESC

查看是哪些SQL语句占用较大可以使用下面代码

--在SSMS里选择以文本格式显示结果
SELECT TOP 10 
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC

3、如果SQLSERVER存在要等待的资源,那么执行下面语句就会显示出会话中有多少个worker在等待

SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

4、查询CPU占用最高的SQL语句

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

5、索引缺失查询

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

6、获取所有字段信息

SELECT
  表名   = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
  序     = a.colorder,
  字段名 = a.name,
  标识   = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
  主键   = CASE
    WHEN EXISTS (
      SELECT *
      FROM sysobjects
      WHERE xtype='PK' AND name IN (
        SELECT name
        FROM sysindexes
        WHERE id=a.id AND indid IN (
          SELECT indid
          FROM sysindexkeys
          WHERE id=a.id AND colid IN (
            SELECT colid
            FROM syscolumns
            WHERE id=a.id AND name=a.name
          )
        )
      )
    )
    THEN '√'
    ELSE ''
  END,
  类型   = b.name,
  字节数 = a.length,
  长度   = COLUMNPROPERTY(a.id,a.name,'Precision'),
  小数   = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
    WHEN 0 THEN ''
    ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)
  END,
  允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
  默认值 = ISNULL(d.[text],''),
  说明   = ISNULL(e.[value],'')
FROM syscolumns a
  LEFT  JOIN systypes      b ON a.xtype=b.xusertype
  INNER JOIN sysobjects    c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
  LEFT  JOIN syscomments   d ON a.cdefault=d.id
  LEFT  JOIN sys.extended_properties e ON   a.id= e.major_id AND a.colid = e.minor_id
ORDER BY c.name, a.colorder



/*
常规服务器动态管理对象包括:
dm_db_*:数据库和数据库对象
dm_exec_*:执行用户代码和关联的连接
dm_os_*:内存、锁定和时间安排
dm_tran_*:事务和隔离
dm_io_*:网络和磁盘的输入/输出
*/
 
--- 运行下面的 DMV 查询以查看 CPU、计划程序内存和缓冲池信息。
select
    cpu_count,
    hyperthread_ratio,
    scheduler_count,
    physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
    virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
    bpool_committed * 8 / 1024 as bpool_committed_mb,
    bpool_commit_target * 8 / 1024 as bpool_target_mb,
    bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
 
 
 
--- 高I/O开销的查询  Identifying Most Costly Queries by I/O
 SELECT TOP 10
      [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
    , [Total IO] = (total_logical_reads + total_logical_writes)
    , [Execution count] = qs.execution_count
    , [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
             (CASE WHEN qs.statement_end_offset = -1
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
            ,[Parent Query] = qt.text
    , DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
 
 
 
--- 高CPU开销的查询 Identifying Most Costly Queries by CPU
 SELECT TOP 10
      [Average CPU used] = total_worker_time / qs.execution_count
    , [Total CPU used] = total_worker_time
    , [Execution count] = qs.execution_count
    , [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
             (CASE WHEN qs.statement_end_offset = -1
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
    , [Parent Query] = qt.text
    , DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
 
 
 
 
--- 高开销的缺失索引 Cost of Missing Indexes
 SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
 
 
 
 
--- 最常执行的查询 Identifying Queries that Execute Most Often
 SELECT TOP 10
 [Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;
 
 
 
 
 
--- 重复编译的查询(plan_generation_num 指示该查询已重新编译的次数)
select top 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid
from sys.dm_exec_query_stats a
      cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
 
 
 
 
 
 
--- 服务器等待的原因 SQL Query Records Causes of Wait Times
 SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
               / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
 
 
 
 
--- 读和写 Identifying the Most Reads and Writes
SELECT TOP 10
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
 
SELECT TOP 10
        [Total Writes] = SUM(total_logical_writes)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;
 
 
 
 
--- 运行下面的 DMV 查询以查找 I/O 闩锁等待统计信息。
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats 
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type
 
 
 
 
--- 查看数据库的阻塞信息
select * from sysprocesses a where a.program_name = '.Net SqlClient Data Provider' and blocked != 0
 
 
---查看所有会话的 找到活动事务对应的执行语句
select  dc.session_id,
        ds.login_name,
        ds.login_time,              
        dc.connect_time,
        dc.net_transport,
        dc.client_net_address,
        ds.host_name,
        ds.program_name,
        case ds.status  when 'sleeping' then '睡眠 - 当前没有运行任何请求 '
                        when 'running'  then '正在运行 - 当前正在运行一个或多个请求 '
                        when 'Dormancy' then '休眠 – 会话因连接池而被重置,并且现在处于登录前状态'
                        when 'Pre-connected' then '预连接 - 会话在资源调控器分类器中'
                        end as status ,
        ds.cpu_time as cpu_time_ms,
        ds.memory_usage*8 as memory_kb,
        ds.total_elapsed_time as total_elapsed_time_ms,
        case ds.transaction_isolation_level when 0 then '未指定'
                                            when 1 then '未提交读取'
                                            when 2 then '已提交读取'
                                            when 3 then '可重复'
                                            when 4 then '可序列化'
                                            when 5 then '快照'
                                        end '会话的事务隔离级别',
        dt.text             
from sys.dm_exec_connections  dc        --执行连接,最近执行的查询信息 
cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dt
join sys.dm_exec_sessions ds  on dc.session_id=ds.session_id
where ds.login_name= 'LCGS609999' 
--where ds.program_name = '.Net SqlClient Data Provider'
ORDER BY dt.text
 
 
--kill 53;
 
 
-- 检查分析死锁信息
 
1、使用sql profiler 抓取死锁链及图
2、跟踪分析SQL日志
 
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
 
参考资料:
http://support.microsoft.com/zh-cn/kb/832524
 
 
 
 
 
-- 检查分析阻塞信息
 
WHILE 1=1
BEGIN
   EXEC master.dbo.sp_blocker_pss80        -- or sp_blocker_pss08
   -- Or for fast mode
   -- EXEC master.dbo.sp_blocker_pss80 @fast=1
   -- Or for latch mode
   -- EXEC master.dbo.sp_blocker_pss80 @latch=1
   WAITFOR DELAY '00:00:15'
END
GO
 
cmd执行命令:
osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
 
参考资料:
http://support.microsoft.com/zh-cn/kb/271509
 
 
揭开隐藏数据的面纱,优化应用程序性能
https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx


发表评论,请先 登录

袭人 

2018-08-16
00000000000000000