数据性能调校——查看最耗资源的各种SQL

最新推荐文章于 2021-01-19 22:59:36 发布

weixin_30271335

最新推荐文章于 2021-01-19 22:59:36 发布

阅读量143

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/gered/p/9540887.html

版权

 

从计划高速缓存中清除查询计划

DBCC FREEPROCCACHE 清除缓存中的过程

DBCC DROPCLEANBUFFERS清除内存中的数据

 

SELECT DB_ID('你的数据库名')

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) where dbid = 6 -- koubei = 6 指定数据库ID

)AS query_text

FROM sys.dm_exec_query_stats

ORDER BY [avg_cpu_cost] DESC

 

 

查询SQL改进版

 

select 

    highest_cpu_queries.plan_handle, 

    highest_cpu_queries.total_worker_time,

highest_cpu_queries.avg_cpu_cost,

highest_cpu_queries.execution_count,

    q.dbid,

    q.objectid,

    q.number,

    q.encrypted,

    q.[text]

from 

    (select top 50 

        qs.plan_handle, 

        qs.total_worker_time,

        qs.total_worker_time/execution_count AS avg_cpu_cost ,

        qs.execution_count

    from 

        sys.dm_exec_query_stats qs

    order by avg_cpu_cost desc) as highest_cpu_queries

    cross apply sys.dm_exec_sql_text(plan_handle) as q

    where dbid = 6 -- koubei = 6

order by highest_cpu_queries.avg_cpu_cost desc

 

 

SELECT TOP 10 total_worker_time,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) where dbid = 6 -- koubei = 6

)AS query_text

FROM sys.dm_exec_query_stats

ORDER BY execution_count DESC

 

改进版本：

select 

    highest_execution_count.plan_handle, 

    highest_execution_count.total_worker_time,

    highest_execution_count.execution_count,

    q.dbid,

    q.objectid,

    q.number,

    q.encrypted,

    q.[text]

from 

    (select top 50 

        qs.plan_handle, 

        qs.total_worker_time,

        qs.execution_count

    from 

        sys.dm_exec_query_stats qs

    order by execution_count desc) as highest_execution_count

    cross apply sys.dm_exec_sql_text(plan_handle) as q

    where dbid = 6 -- koubei = 6

order by highest_execution_count.execution_count desc

 

SELECT TOP 10 plan_generation_num,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) where dbid = 6 -- koubei = 6

)AS query_text

FROM sys.dm_exec_query_stats WHERE plan_generation_num>1 ORDER BY plan_generation_num DESC

 

改进版：

select 

    highest_plan_count.plan_handle, 

    highest_plan_count.total_worker_time,

    highest_plan_count.execution_count,

    highest_plan_count.plan_generation_num,

    q.dbid,

    q.objectid,

    q.number,

    q.encrypted,

    q.[text]

from 

    (select top 50 

        qs.plan_handle, 

        qs.total_worker_time,

        qs.execution_count,

        qs.plan_generation_num

    from 

        sys.dm_exec_query_stats qs WHERE plan_generation_num>1

    order by plan_generation_num desc) as highest_plan_count

    cross apply sys.dm_exec_sql_text(plan_handle) as q

    WHERE  dbid = 6 -- koubei = 6

order by highest_plan_count.plan_generation_num desc

 

SELECT TOP 10 (total_logical_reads/execution_count)AS avg_logical_reads,(total_logical_writes/execution_count)

AS avg_logical_writes,(total_physical_reads/execution_count)AS avg_phys_reads,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) where dbid = 6 -- koubei = 6

)AS query_text,plan_handle

FROM sys.dm_exec_query_stats ORDER BY (total_logical_reads+total_logical_writes)desc

 

改进版：

select 

    highest_reads_count.avg_logical_reads, 

    highest_reads_count.avg_logical_writes,

    highest_reads_count.avg_phys_reads,

    highest_reads_count.execution_count,

     highest_reads_count.plan_handle,

    q.dbid,

    q.objectid,

    q.number,

    q.encrypted,

    q.[text]

from 

    (select top 50 

        (total_logical_reads/execution_count)AS avg_logical_reads,(total_logical_writes/execution_count)

AS avg_logical_writes,(total_physical_reads/execution_count)AS avg_phys_reads,

        qs.execution_count,qs.plan_handle

    from 

        sys.dm_exec_query_stats qs

    order by (total_logical_reads+total_logical_writes) desc) as highest_reads_count

    cross apply sys.dm_exec_sql_text(plan_handle) as q

    WHERE  dbid = 6 -- koubei = 6

order by (highest_reads_count.avg_logical_reads+highest_reads_count.avg_logical_writes) desc

 

 

SELECT t1.object_id,t2.user_seeks,t2.user_scans,t1.equality_columns,t1.inequality_columns

FROM sys.dm_db_missing_index_details AS t1,

sys.dm_db_missing_index_group_stats AS t2,

sys.dm_db_missing_index_groups AS t3

WHERE database_id=6 --koubei库ID，查看某个库的ID，可以切换到那个库下面，然后SELECT DB_ID()

AND object_id=OBJECT_ID('表名')

AND t1.index_handle=t3.index_handle

AND t2.group_handle=t3.index_group_handle

 

改进版本：

SELECT mig.*,statement AS tableName,column_id,column_name,column_usage FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle=mid.index_handle

WHERE database_id=6 AND object_id=OBJECT_ID('表名')

ORDER BY mig.index_group_handle,mig.index_handle,column_id

 

并发问题，查询阻塞和被阻塞的SQL。注意下面waitingsql和blockingsql两个字段。

 

declare @lockinfo TABLE (

         [waiting_time_ms] [bigint],

         [waiting_resource_type] [varchar](60) ,

         [dbanme] [nvarchar](128),

         [objectname] [nvarchar](128),

         [waiting_request_lock_mode] [varchar](60) ,

         [waiting_session_id] [int] ,

         [waiting_loginame] [varchar](128) ,

         [waiting_hostname] [varchar](128) ,

         [waitingsql] [nvarchar](max),

         [blocking_session_id] [smallint],

         [blocking_loginame] [varchar](128) ,

         [blocking_hostname] [varchar](128) ,

         [blockingsql] [varchar](max),

         [createtime] [datetime]

)

 

DECLARE @count int

declare @sql nvarchar(max)

set @sql = N''

select

@sql = @sql + N'union all

select '''+convert(nvarchar(100),database_id)+N''' as dbid,object_id,hobt_id from ['+name+N'].sys.partitions(nolock)

'

from sys.databases(nolock)

where name not in ('model','tempdb') and state_desc = 'ONLINE'

set @sql = substring(@sql,10,len(@sql))

declare @objinfo table(dbid int,object_id int,hobt_id bigint)

insert into @objinfo exec(@sql)

 

 

INSERT INTO @lockinfo

(

waiting_time_ms,

waiting_resource_type,

dbanme,

objectname,

waiting_request_lock_mode,

waiting_session_id,

waiting_loginame,

waiting_hostname,

waitingsql,

blocking_session_id,

blocking_loginame,

blocking_hostname,

blockingsql

)

SELECT DISTINCT t2.wait_duration_ms waiting_time_ms,

        t1.resource_type waiting_resource_type,

        DB_NAME(t1.resource_database_id) dbanme,

        CASE t1.resource_type

      WHEN 'OBJECT' THEN OBJECT_NAME(t1.resource_associated_entity_id,

         t1.resource_database_id)

      WHEN 'DATABASE' THEN 'DATABASE'

      ELSE

         (SELECT OBJECT_NAME(object_id, t1.resource_database_id)

         FROM @objinfo

         WHERE hobt_id = t1.resource_associated_entity_id

         and dbid = t1.resource_database_id)

   END AS ObjectName,

        t1.request_mode waiting_request_lock_mode,

        t1.request_session_id waiting_session_id,

        s1.login_name waiting_loginame,

        s1.host_name+'-'+r3.client_net_address waiting_hostname,

        CASE WHEN st1.objectid IS NULL THEN st1.text ELSE OBJECT_NAME(st1.objectid,st1.dbid) END waitingsql,

        t2.blocking_session_id,

        s2.login_name blocking_loginame,

        s2.host_name+'-'+r2.client_net_address blocking_hostname,

        CASE WHEN st2.objectid IS NULL THEN st2.text ELSE OBJECT_NAME(st2.objectid,st2.dbid) END blockingsql

    FROM sys.dm_tran_locks AS t1 with(nolock)

    INNER JOIN sys.dm_os_waiting_tasks AS t2 with(nolock) ON t1.lock_owner_address = t2.resource_address

    INNER JOIN sys.dm_exec_requests r1 with(nolock) ON t1.request_session_id=r1.session_id

    INNER JOIN sys.dm_exec_sessions s1 with(nolock)  ON s1.session_id=r1.session_id

                   CROSS APPLY sys.dm_exec_sql_text(r1.sql_handle) st1

    INNER JOIN sys.dm_exec_connections r2 with(nolock) ON t2.blocking_session_id=r2.session_id

    INNER JOIN sys.dm_exec_sessions s2 with(nolock) ON s2.session_id=r2.session_id

                   CROSS APPLY sys.dm_exec_sql_text(r2.most_recent_sql_handle) st2

         left JOIN sys.dm_exec_connections r3 with(nolock) ON t1.request_session_id=r3.session_id

         ORDER BY t2.wait_duration_ms DESC

 

select * from @lockinfo

转载于:https://www.cnblogs.com/gered/p/9540887.html

weixin_30271335

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据性能调校——查看最耗资源的各种SQL

从计划高速缓存中清除查询计划DBCC FREEPROCCACHE 清除缓存中的过程DBCC DROPCLEANBUFFERS清除内存中的数据 SELECT DB_ID('你的数据库名') SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost,plan_han...
复制链接

扫一扫