sql server 性能优化方法

sql server 性能优化方法

一、查看是否有死锁

DECLARE  @tab TABLE(NAME varchar(100),value varchar(200));
 
INSERT INTO @tab EXEC('DBCC OPENTRAN WITH TABLERESULTS');
 
SELECT name,CAST(value AS DATETIME) startDate,getdate() currentDate
 
,DATEDIFF(s,CAST(value AS DATETIME),getdate()) diffsecond FROM @tab WHERE name in 
 
('OLDACT_STARTTIME')
 
 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 =(SELECT CASE WHEN ISNUMERIC(value)=0 THEN -1 ELSE value end FROM @tab WHERE name in 
 
('OLDACT_SPID') )


二、查看当前正在执行的sql语句
 
     SELECT  [Spid] = session_id , ecid ,
 
            [Database] = DB_NAME(sp.dbid) ,[User] = nt_username ,
 
            [Status] = er.status , [Wait] = wait_type ,
 
            [Individual Query] = SUBSTRING(qt.text,
 
                                           er.statement_start_offset / 2,
 
                                           ( CASE WHEN er.statement_end_offset = -1
 
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
 
                                                       * 2
 
                                                  ELSE er.statement_end_offset
 
                                             END - er.statement_start_offset )
 
                                           / 2) ,
 
            [Parent Query] = qt.text , Program = program_name ,hostname ,  nt_domain , start_time
 
    FROM    sys.dm_exec_requests er
 
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
 
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
 
    WHERE   session_id > 50 -- Ignore system spids.
 
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
 
ORDER BY    1 ,
 
            2 
 
--删除解锁
 
KILL 1000  --spid


三、查询前 10 个可能是性能最差的 SQL 语句

SELECT TOP 10 TEXT AS 'SQL Statement'
 
    ,last_execution_time AS 'Last Execution Time'
 
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
 
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
 
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
 
    ,execution_count AS "Execution Count"
 
    ,qp.query_plan AS "Query Plan"
 
FROM sys.dm_exec_query_stats qs
 
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 
ORDER BY total_elapsed_time / execution_count DESC
 
where database_id=db_id('数据库名称')   --指定数据库
 
     and object_name(a.object_id) not like 'sys%'
 
     and object_name(a.object_id) like '表名'  --指定索引表
 
     and b.name is not null
 
     --and b.name like '索引名' --指定索引名称 可以先使用 sp_help '你的表名' 查看表的结构和所有的索引信息
 
group by db_name(database_id) ,
 
       object_name(a.object_id),
 
       b.name,
 
       user_seeks ,
 
       user_scans 
 
order by user_seeks,user_scans,object_name(a.object_id)


四、查询逻辑读取最高的sql

SELECT TOP ( 25 )
 
        P.name AS [SP Name] ,
 
        Deps.total_logical_reads AS [TotalLogicalReads] ,
 
        deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
 
        deps.execution_count ,
 
        ISNULL(deps.execution_count / DATEDIFF(SECOND, deps.cached_time,
 
                                               GETDATE()), 0) AS [Calls/Second] ,
 
        deps.total_elapsed_time ,
 
        deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
 
        deps.cached_time
 
FROM    sys.procedures AS p
 
        INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
 
WHERE   deps.Database_id = DB_ID()
 
ORDER BY deps.total_logical_reads DESC

五、 查询索引碎片

--创建变量 指定要查看的表
 
declare @table_id int
 
set @table_id=object_id('TableName')
 
--执行
 
dbcc showcontig(@table_id)
Logical Scan Fragmentation-逻辑扫描碎片：该百分比应该在0％到10％之间，高了则说明有外部碎片。 
 
Extent Scan Fragmentation-扩展盘区扫描碎片：该百分比应该是0％，高了则说明有外部碎片。 
 
扫描密度［最佳值:实际值］：该百分比应该尽可能靠近100％。低了则说明有外部碎片。
 
六、修改索引填充因子（FILLFACTOR：填充因子,ONLINE:ON 重建索引时表仍然可以正常使用）

--修改表下所有索引填充因子 
 
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)
 
--修改表下指定索引填充因子
 
ALTER INDEX indexName ON  TableName REBUILD WITH (FILLFACTOR = 80);   
 
 
七、查询未使用过的索引
 
 
SELECT  DB_NAME(diu.database_id)                  AS DatabaseName ,
 
        s.name +'.' +QUOTENAME(o.name)            AS TableName    ,
 
        i.index_id                                AS IndexID   ,
 
        i.name                                    AS IndexName        ,
 
        CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX'
 
           ELSE 'NOT UNIQUE INDEX'    END         AS IS_UNIQUE,
 
        CASE WHEN i.is_disabled=1 THEN 'DISABLE'
 
           ELSE 'ENABLE'            END           AS IndexStatus,
 
        o.create_date                             AS IndexCreated,
 
        STATS_DATE(o.object_id,i.index_id)        AS StatisticsUpdateDate,
 
        diu.user_seeks                            AS UserSeek ,
 
        diu.user_scans                            AS UserScans ,
 
        diu.user_lookups                          AS UserLookups ,
 
        diu.user_updates                          AS UserUpdates ,
 
        p.TableRows ,
 
        'DROP INDEX ' + QUOTENAME(i.name) 
 
        + ' ON ' + QUOTENAME(s.name) + '.'
 
        + QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement'
 
FROM    sys.dm_db_index_usage_stats diu
 
        INNER JOIN sys.indexes i ON i.index_id = diu.index_id
 
                                    AND diu.object_id = i.object_id
 
        INNER JOIN sys.objects o ON diu.object_id = o.object_id
 
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
 
        INNER JOIN ( SELECT SUM(p.rows) TableRows ,
 
                            p.index_id ,
 
                            p.object_id
 
                     FROM   sys.partitions p
 
                     GROUP BY p.index_id ,
 
                            p.object_id
 
                   ) p ON p.index_id = diu.index_id
 
                          AND diu.object_id = p.object_id
 
WHERE   OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1
 
        AND diu.database_id = DB_ID()
 
        AND i.is_primary_key = 0        --排除主键索引
 
        AND i.is_unique_constraint = 0         --排除唯一索引
 
        AND diu.user_updates <> 0              --排除没有数据变化的索引
 
        AND diu.user_lookups = 0
 
        AND diu.user_seeks = 0
 
        AND diu.user_scans = 0
 
        AND i.name IS NOT NULL                 --排除那些没有任何索引的堆表
 
ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;
 
GO
 
 
八、查询表下索引使用情况

 select db_name(database_id) as N'数据库名称',
 
       object_name(a.object_id) as N'表名',
 
       b.name N'索引名称',
 
       user_seeks N'用户索引查找次数',
 
       user_scans N'用户索引扫描次数',
 
       max(last_user_seek) N'最后查找时间',
 
       max(last_user_scan) N'最后扫描时间',
 
       max(rows) as N'表中的行数'
 
from sys.dm_db_index_usage_stats a join 
 
     sys.indexes b
 
     on a.index_id = b.index_id
 
     and a.object_id = b.object_id
 
     join sysindexes c
 
     on c.id = b.object_id
 
where database_id=db_id('数据库名称')   --指定数据库
 
     and object_name(a.object_id) not like 'sys%'
 
     and object_name(a.object_id) like '表名'  --指定索引表
 
     and b.name is not null
 
     --and b.name like '索引名' --指定索引名称 可以先使用 sp_help '你的表名' 查看表的结构和所有的索引信息
 
group by db_name(database_id) ,
 
       object_name(a.object_id),
 
       b.name,
 
       user_seeks ,
 
       user_scans 
 
order by user_seeks,user_scans,object_name(a.object_id)

 九、查询表结构信息
 
        SELECT  表名 = CASE WHEN a.colorder = 1 THEN d.name
                          ELSE ''
                     END ,
                表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
                           ELSE ''
                      END ,
                字段序号 = a.colorder ,
                字段名 = a.name ,
                标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
                          THEN '√'
                          ELSE ''
                     END ,
                主键 = CASE WHEN EXISTS ( SELECT  1
                                        FROM    sysobjects
                                        WHERE   xtype = 'PK'
                                                AND parent_obj = a.id
                                                AND name IN (
                                                SELECT  name
                                                FROM    sysindexes
                                                WHERE   indid IN (
                                                        SELECT
                                                              indid
                                                        FROM  sysindexkeys
                                                        WHERE id = a.id
                                                              AND colid = a.colid ) ) )
                          THEN '√'
                          ELSE ''
                     END ,
                类型 = b.name ,
                占用字节数 = a.length ,
                长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,
                小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
                允许空 = CASE WHEN a.isnullable = 1 THEN '√'
                           ELSE ''
                      END ,
                默认值 = ISNULL(e.text, '') ,
                字段说明 = ISNULL(g.[value], '')
        FROM    syscolumns a
                LEFT JOIN systypes b ON a.xusertype = b.xusertype
                INNER JOIN sysobjects d ON a.id = d.id
                                           AND d.xtype = 'U'
                                           AND d.name <> 'dtproperties'
                LEFT JOIN syscomments e ON a.cdefault = e.id
                LEFT JOIN sys.extended_properties g ON a.id = G.major_id
                                                       AND a.colid = g.minor_id
                LEFT JOIN sys.extended_properties f ON d.id = f.major_id
                                                       AND f.minor_id = 0
         WHERE   d.name = 'TableName'    --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
ORDER BY        a.id ,
                a.colorder