SQL SERVER性能优化SQL

 

                                         sql server 性能优化方法

 

目录

                        

一、查看是否有死锁

二、查看当前正在执行的sql语句

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

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

五、 查询索引碎片

六、修改索引填充因子(FILLFACTOR:填充因子,ONLINE:ON 重建索引时表仍然可以正常使用)

七、查询未使用过的索引

八、查询表下索引使用情况

         九、查询表结构信息



一、查看是否有死锁

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

 

四、查询逻辑读取最高的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

 

  • 0
    点赞
  • 24
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值