【SQLserver】常见问题和解决方案-阿里云开发者社区

1 分析

1.1 游标使用

select * 
from bugtracertest..syscursors
where status <> -1

1.2 查询死锁情况

WITH    CTE_SID ( BSID, SID, sql_handle )        
  AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                       JOIN CTE_SID B ON A.SESSION_ID = B.BSID
             )
    SELECT  C.BSID ,  C.SID , S.login_name , S.host_name , S.status ,S.cpu_time ,
            S.memory_usage ,S.last_request_start_time , S.last_request_end_time ,S.logical_reads ,
            S.row_count ,
            q.text
    FROM    CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
ORDER BY sid

1.2.1 解决方案

首先在sqlserver的查询分析器中查看特定数据库被阻塞的进程

select * from sysprocesses where dbid in (select dbid from sysdatabases where name=’ BugTracer’) and blocked>0

然后查看阻塞超时设置:SELECT @@LOCK_TIMEOUT

再次根据阻塞的进程ID查看阻塞语句:dbcc inputbuffer(108)

上面的108就是阻塞进程的ID。根据发现的语句可以用来分析是程序代码的哪部分造成了这个死锁，从而得以解决。

如果事情紧急，可以立即杀死阻塞进程，从而终结死锁的情况

kill 108

1.3 查询数据库使用情况

1.3.1 查询数据库连接数

SELECT * 
FROM [Master].[dbo].[SYSPROCESSES] 
WHERE [DBID] IN ( SELECT 　[DBID] FROM [Master].[dbo].[SYSDATABASES]
WHERE  NAME='BugTracerTest')

1.3.2 查询数据库会话数

Select b.session_id, a.client_net_address,a.local_net_address,
       b.status,b.login_name,b.login_time,b.client_interface_name
from sys.dm_exec_sessions  b left join sys.dm_exec_connections a on  a.session_id = b.session_id
order by b.session_id
查询每台机器使用程序连接的会话数
select a.client_net_address,a.client_interface_name,count(*) Count
From (
Select b.session_id, a.client_net_address client_net_address,a.local_net_address,
       b.status,b.login_name,b.login_time,b.client_interface_name client_interface_name
from sys.dm_exec_sessions  b left join sys.dm_exec_connections a on  a.session_id = b.session_id
) a
group by a.client_net_address,a.client_interface_name

1.3.3 查询数据库的负载

SELECT substring (a.name,0,20) as [数据库名],
[连接数] = (SELECT COUNT(*) FROM master..sysprocesses b WHERE a.dbid = b.dbid), [阻塞进程] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid AND blocked <> 0),
[总内存] = ISNULL((SELECT SUM(memusage) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总IO] = ISNULL((SELECT SUM(physical_io) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总CPU] = ISNULL((SELECT SUM(cpu) FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0), [总等待时间] = ISNULL((SELECT SUM(waittime)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0) FROM master.dbo.sysdatabases a WITH (nolock)
WHERE  DatabasePropertyEx(a.name,'Status') = 'ONLINE'
ORDER BY [数据库名]

1.4 整理索引碎片

第一步：查询表的索引碎片情况
DBCC SHOWCONTIG WITH ALL_INDEXES；
第二步：删除并重建索引 
第三步：使用DROP_EXISTING子句重建索引 
第四步：执行DBCC DBREINDEX
第五步：执行DBCC INDEXDEFRAG 

Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10)
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S')

删除并重建索引

用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不再可用，查询性能也许会受到明显的影响，直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。

删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用，以使那些索引回到它们应该在的位置。

 使用DROP_EXISTING子句重建索引

为了避免在重建聚集索引时表上的非聚集索引重建两次，可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值，以避免非聚集索引重建两次。和删除并重建索引一样，该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。

除了和上一个方法一样的好处之外，该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。

 执行DBCC DBREINDEX

DBCC DBREINDEX类似于第二种方法，但它物理地重建索引，允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引，不像第二种方法。

DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的，所以如果在完成之前中断了，那么你会丢失所有已经执行过的碎片。

 执行DBCC INDEXDEFRAG

DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引键的逻辑顺序，通过重新整理索引里存在的叶页来减少外部碎片，通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCCINDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。

1.4.1 查询索引

SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
       i.[name] AS 索引名称,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
     WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS '索引类型',
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
ORDER BY i.[name];

1.4.2 整理索引

select 'drop index ' + index_name + ' on ' + tab_name + ';' ,a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
       i.[name] AS index_name,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
     WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS index_Type,
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and a.index_only = '不唯一';

select 'create index ' + 'idx_' + tab_name + '_' + a.index_colum + ' on ' + tab_name + '('  + a.index_colum + ');',
       a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE  WHEN t.[type] = 'U' THEN'表'
             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
       i.[name] AS index_name,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
       CASE WHEN i.[type] = 1 THEN '聚集索引'
            WHEN i.[type] = 2 THEN  '非聚集索引'
            WHEN i.[type] = 3 THEN  'XML索引'
           WHEN i.[type] = 4 THEN '空间索引'
           WHEN i.[type] = 5 THEN '聚簇列存储索引'
           WHEN i.[type] = 6 THEN '非聚集列存储索引'
     WHEN i.[type] = 7 THEN'非聚集哈希索引'
           END AS index_Type,
      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
           ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and index_only = '不唯一'

1.4.3 整理索引语法

非聚集不唯一索引

Drop index index_name on table_name;
Create index idx_name on tale_name(colum_name);

唯一非聚集索引

Drop index index_name on table_name;
Create unique index udx_tablename on table_name(colum_name);

主健重建

ALTER TABLE table_name DROP PRIMARY KEY ;
ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (colum _id, colum_id);

1.5 检查系统空间

1.5.1 检查数据库空间使用情况

查看哪些表占用了比较大的磁盘空间

select o.name, SUM(p.reserved_page_count) as reserved_page_count,
               SUM(p.used_page_count) as used_page_count, 
              SUM( case when(p.index_id<2) then (p.in_row_data_page_count+ p.lob_used_page_count+p.row_overflow_used_page_count) else p.lob_used_page_count+p.row_overflow_used_page_count end ) as DataPages, 
              SUM( case when (p.index_id<2) then row_count else 0 end ) as rowCounts 
from sys.dm_db_partition_stats p 
inner join sys.objects o on p.object_id=o.object_id group by o.name order by rowCounts desc

1.5.2 查看表的占用情况

SELECT 
name '表名', 
convert (char(11), row_Count) as '数据条数', 
(reservedpages * 8) '已用空间(KB)', 
(pages * 8) '数据占用空间(KB)', 
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)', 
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)', 
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)' 
from( 
SELECT name, 
SUM (reserved_page_count) as reservedpages , 
SUM (used_page_count) as usedpages , 
SUM ( 
    CASE 
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
        ELSE lob_used_page_count + row_overflow_used_page_count 
    END 
    ) as pages, 
SUM ( 
    CASE 
        WHEN (index_id < 2) THEN row_count 
        ELSE 0 
    END 
    )  as row_Count 
FROM sys.dm_db_partition_stats 
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id 
where type='U' 
group by sys.objects.name 
union 
SELECT sys.objects.name, 
sum(reserved_page_count) as reservedpages, 
sum(used_page_count) as usedpages, 
0 as pages, 
0 as row_count 
from sys.objects inner join sys.internal_tables on 
 sys.objects.object_id = sys.internal_tables.parent_id 
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id 
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216) 
group by sys.objects.name) t 
order by '已用空间(KB)' desc

1.6 SQL查询情况

1.6.1 物理读高的100条

增加输出查询的IP地址，如果没有退出，可以定位是谁做什么？

SELECT TOP 100 
 a.session_id,a.client_net_address,
 qs.total_physical_reads,qs.execution_count,
 qs.total_physical_reads /qs.execution_count as avg_io,
 qt.text, db_name(qt.dbid) as dbname, qt.objectid
 FROM sys.dm_exec_query_stats qs 
  CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt
  Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
             From sys. dm_exec_requests  a, sys.dm_exec_connections b
             where a.session_id = b.session_id 
             ) a on qs.sql_handle = a. sql_handle
  ORDER BY qs.total_physical_reads desc

1.6.2 逻辑读高的100条

增加输出查询的IP地址，如果没有退出，可以定位是谁做什么？

SELECT TOP 100
a.session_id,a.client_net_address,
 qs.total_logical_reads,qs.execution_count,
 qs.total_logical_reads /qs.execution_count as avg_io,
 qt.text, db_name(qt.dbid) as dbname
 FROM sys.dm_exec_query_stats qs
 cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
 Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
             From sys. dm_exec_requests  a, sys.dm_exec_connections b
             where a.session_id = b.session_id 
             ) a on qs.sql_handle = a. sql_handle
 ORDER BY qs.total_logical_reads desc

1.6.3 查询CPU高的100条

SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC

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 qs
ORDER BY [avg_cpu_cost] DESC

1.6.4 查询内存消耗高的SQL

SELECT a.session_id,a.client_net_address,SS.SUM_EXECUTION_COUNT,
T.TEXT,
SS.SUM_TOTAL_ELAPSED_TIME,
SS.SUM_TOTAL_WORKER_TIME,
SS.SUM_TOTAL_LOGICAL_READS,
SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
 Left join (select a.session_id, a.plan_handle plan_handle,b.client_net_address  client_net_address
             From sys. dm_exec_requests  a, sys.dm_exec_connections b
             where a.session_id = b.session_id 
             ) a on ss.plan_handle = a. plan_handle
ORDER BY SUM_TOTAL_LOGICAL_READS DESC

1.6.5 查找执行慢的SQL

SELECT a.session_id,a.client_net_address,
(total_elapsed_time / execution_count)/1000 N'平均时间ms' 
,total_elapsed_time/1000 N'总花费时间ms' 
,total_worker_time/1000 N'所用的CPU总时间ms' 
,total_physical_reads N'物理读取总次数' 
,total_logical_reads/execution_count N'每次逻辑读次数' 
,total_logical_reads N'逻辑读取总次数' 
,total_logical_writes N'逻辑写入总次数' 
,execution_count N'执行次数' 
,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) N'执行语句' 
,st.text
,creation_time N'语句编译时间' 
,last_execution_time N'上次执行时间' 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
             From sys. dm_exec_requests  a, sys.dm_exec_connections b
             where a.session_id = b.session_id 
             ) a on qs.sql_handle = a. sql_handle
ORDER BY total_elapsed_time / execution_count DESC

1.6.6 查询正在执行的语句

SELECT   spid,
         blocked,
         DB_NAME(sp.dbid) AS DBName,
         program_name,
         waitresource,
         lastwaittype,
         sp.loginame,
         sp.hostname,
         a.[Text] AS [TextData],
         SUBSTRING(A.text, sp.stmt_start / 2,
         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
         END - sp.stmt_start) / 2) AS [current_cmd]
FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE    spid > 50

1.7 SQLServer管理分析

https://blog.csdn.net/f756707471/article/details/43736601

使用性能监视器找出硬件瓶颈

SQLServer硬件性能监控列表

操作系统性能监控列表

SQLServer2000配置性能监控列表

数据库配置设置性能监控列表

索引性能监控列表

应用程序和T-SQL性能监控列表

SQLServer数据库作业性能监控列表

使用Profiler找出低效的查询

怎样最好的实现SQLServer性能监控

2 评估

2.1 设计

2.1.1 查询索引使用情况

SELECT db_name(database_id) as N'数据库名称',
         ind.index_id '索引的ID',
        obj.name as N'表名',
       ind.name '索引的名称',
        ind.type_desc '索引类型的说明',
       indUsage.user_seeks N'用户索引查找次数',
        indUsage.user_scans N'用户索引扫描次数',
        indUsage.last_user_seek N'用户上次执行搜索的时间',
         indUsage.last_user_scan N'用户上次执行扫描的时间',
         indUsage.user_lookups ,
        indUsage.user_updates '通过用户查询执行的更新次数',
        rows as N'表中的行数'
 FROM    sys.indexes AS ind
       INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
        LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
     LEFT join sysindexes c on c.id = ind.object_id AND c.indid = 1                                                          
 WHERE  database_id=db_id('bugtracer')   ---改成要查看的数据库
        AND ind.type_desc <> 'HEAP' --不是堆
       AND obj.type <> 'S' --不是系统基表
ORDER BY user_seeks,user_scans,indUsage.user_lookups,obj.name,ind.name

注意：通过索引的使用情况来分析系统设计的不合理性

2.2 调整索引

http://www.zzvips.com/article/44216.html

【SQLserver】常见问题和解决方案