SQL Server 診斷查詢-(4)

来源:http://www.cnblogs.com/Joe-T/archive/2016/02/04/5180978.html

Query #41 Memory Clerk Usage -- Memory Clerk Usage for instance -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) SELECT TOP(10) mc.[ty

Query #41 Memory Clerk Usage

-- Memory Clerk Usage for instance

-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)

SELECT TOP(10) mc.[type] AS [Memory Clerk Type],

CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]

FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)

GROUP BY mc.[type]

ORDER BY SUM(mc.pages_kb) DESCOPTION (RECOMPILE);

-- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory

-- CACHESTORE_SQLCP SQL Plans

-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers

-- Watch out for high values for CACHESTORE_SQLCP

-- CACHESTORE_OBJCP Object Plans

-- These are compiled plans for stored procedures, functions and triggers

 

Query #42 Ad hoc Queries

 -- Find single-use, ad-hoc and prepared queries that are bloating the plan cache

 SELECT TOP(50)[text] AS [QueryText],cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB]

 FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

 WHERE cp.cacheobjtype = N'Compiled Plan' 

 AND cp.objtype IN (N'Adhoc', N'Prepared') 

 AND cp.usecounts = 1

 ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

 -- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache

 -- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)

 -- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this

 -- Enabling forced parameterization for the database can help, but test first!

 -- Plan cache, adhoc workloads and clearing the single-use plan cache bloat

 -- http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

 

Query #43 Top Logical Reads Queries

-- Get top total logical reads queries for entire instance

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.text, 50) AS [Short Query Text],

qs.total_logical_reads AS [Total Logical Reads],

qs.min_logical_reads AS [Min Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.max_logical_reads AS [Max Logical Reads],   

qs.min_worker_time AS [Min Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 

qs.max_worker_time AS [Max Worker Time], 

qs.min_elapsed_time AS [Min Elapsed Time], 

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 

qs.max_elapsed_time AS [Max Elapsed Time],

qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]

--,t. AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 

ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

-- Helps you find the most expensive queries from a memory perspective across the entire instance

-- Can also help track down parameter sniffing issues

 

Query #44 File Sizes and Space

-- Individual File Sizes and space available for current database (Query 44) (File Sizes and Space)

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],

CAST((f.size/128.0) ASDECIMAL(15,2)) AS [Total Sizein MB],

CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') ASint)/128.0 ASDECIMAL(15,2))

AS [Available SpaceIn MB], [file_id], fg.name AS [Filegroup Name],

f.is_percent_growth, f.growth

FROM sys.database_files AS f WITH (NOLOCK)

LEFTOUTERJOIN sys.data_spaces AS fg WITH (NOLOCK)

ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

   

-- Look at how large and how full the files are and where they are located

-- Make sure the transaction log isnotfull!!

 

Query #45 IO Stats By File

-- I/O Statistics by file for the current database

SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,

df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Size on Disk (MB)],

vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,

CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],

CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],

(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads], 

CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 

CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],

CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],

CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],

CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],

CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]

FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs

INNER JOIN sys.database_files AS df WITH (NOLOCK)

ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);

-- This helps you characterize your workload better from an I/O perspective for this database

-- It helps you determine whether you has an OLTP or DW/DSS type of workload

 

Query #46 Query Execution Counts

-- Get most frequently executed queries for this database

SELECT TOP(50) LEFT(t., 50) AS [Short Query Text], qs.execution_count AS [Execution Count],

qs.total_logical_reads AS [Total Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.total_worker_time AS [Total Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 

qs.total_elapsed_time AS [Total Elapsed Time],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 

qs.creation_time AS [Creation Time]

--,t. AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 

WHERE t.dbid = DB_ID()

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

 

#47 SP Execution Counts

-- Top Cached SPs By Execution Count

SELECT TOP(100) p.name AS [SP Name], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],  

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

-- Tells you which cached stored procedures are called the most often

-- This helps you characterize and baseline your workload

 

Query #48 SP Avg Elapsed Time

-- Top Cached SPs By Avg Elapsed Time

SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 

qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count, 

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 

qs.total_worker_time AS [TotalWorkerTime], qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

-- This helps you find high average elapsed time cached stored procedures that

-- may be easy to optimize with standard query tuning techniques

 

Query #49 SP Worker Time

-- Top Cached SPs By Total Worker time. Worker time relates to CPU cost

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective

-- You should look at this if you see signs of CPU pressure

 

Query #50 SP Logical Reads

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a memory perspective

-- You should look at this if you see signs of memory pressure

 

Query #51 SP Physical Reads

-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 

qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 

qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 

AS [avg_elapsed_time], qs.cached_time 

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

AND qs.total_physical_reads > 0

ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a read I/O perspective

-- You should look at this if you see signs of I/O pressure or of memory pressure

 

Query #52 SP Logical Writes

-- Top Cached SPs By Total Logical Writes

-- Logical writes relate to both memory and disk I/O pressure 

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], 

qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 

qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

AND qs.total_logical_writes > 0

ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a write I/O perspective

-- You should look at this if you see signs of I/O pressure or of memory pressure

 

Query #53 Top IO Statements

-- Lists the top statements by average input/output usage for the current database

SELECT TOP(50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name],

(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], qs.execution_count AS [Execution Count],

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) AS [Query Text]    

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.[dbid] = DB_ID()

ORDER BY [Avg IO] DESC OPTION (RECOMPILE);

-- Helps you find the most expensive statements for I/O by SP

 

Query #54 Bad NC Indexes

-- Possible Bad NC Indexes (writes > reads)

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND user_updates > (user_seeks + user_scans + user_lookups)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

-- Look for indexes with high numbers of writes and zero or very low numbers of reads

-- Consider your complete workload, and how long your instance has been running

-- Investigate further before dropping an index!

 

Query #55 Missing Indexes

-- Missing Indexes for current database by Index Advantage 

SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,

OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON p.[object_id] = mid.[object_id]

WHERE mid.database_id = DB_ID() 

ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

 

Query #56 Missing Index Warnings

 -- Find missing index warnings for cached plans in the current database

 -- Note: This query could take some time on a busy instance

 SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName], 

                query_plan, cp.objtype, cp.usecounts, cp.size_in_bytes

 FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

 WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'

 AND dbid = DB_ID()

 ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

 -- Helps you connect missing indexes to specific stored procedures or queries

 -- This can help you decide whether to add them or not


您的分享是我們最大的動力!

更多相關文章
  • 正如文章《通用的業務編號規則設計實現(附源碼)》 文章里需要一個多實例和線程安全的序列化生成器,在SQL Server 2012+ 版本 有一個通過.NET程式集的序列號transact-sql 函數 http://msdn.microsoft.com/zh-cn/library/ff878091.
  • 上一篇,我們對hive的數據導出,以及集群Hive數據的遷移進行描述。瞭解到了基本的hive導出操作。這裡,我們將對hive的CLI及JDBC這些實用性很強的兩個方便進行簡要的介紹。 下麵我們開始介紹hive的CLI和JDBC。
  • 一、安裝 目前,官方最新穩定版本為3.0.7 # wget http://download.redis.io/releases/redis-3.0.7.tar.gz # cd /usr/local/ # tar xvf /root/redis-3.0.7.tar.gz # cd redis-3.0.
  • 以後我們會看到越來越多的機器人。春晚上也有。
  • 關於正則表達式
一周排行
  • 一、課程介紹 曾經有一位不知名的講師說過這麼一句名言: 一門RPC技術不會,那麼千萬萬門RPC技術將都不會!在今天移動互聯網的時代,作為攻城師的我們,誰不想著只寫一套API就可以讓我們的Web, Android APP, IOS APP, iPad APP, Hybired APP, H5 Web共 ...
  • MVC也好,WebAPI也好,據我所知,有部分人是因為複雜的路由,而不想去學的。曾經見過一位程式猿,在他MVC程式中,一切皆路由,url中是完全拒絕"?"和“&”。對此,我也不好說什麼,搞不好是個人風格。路由雖然重要,但其實也只是實現MVC的一種手段,並非你用的路由越多,你的url完全不使用參數,你 ...
  • 基於Ubuntu安裝redis, 我找的一個很好的網站: https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-redis-on-ubuntu-16-04設置redis密碼登錄, 編輯redis.c ...
  • 原文鏈接 一.Has方法: 二.With方法: ...
  • 本文的概念性內容來自深入淺出設計模式一書 項目需求 這是一個糖果機的需求圖. 它有四種狀態, 分別是圖中的四個圓圈: No Quarter: 無硬幣 Has Quater 有硬幣 Gumball Sold 糖果賣出 Out of Gumball 沒有糖果了 這個圖很像一個狀態圖. 每個圓圈就是一個狀 ...
  • 最近有個統計分佈的需求,需要按統計本周,上周,本月,上月,本季度,上季度,本年度,上年度等時間統計分佈趨勢,所以這裡就涉及到計算周,月,季度,年度等的起止時間了,下麵總結一下C#中關於根據當前時間獲取周,月,季度,年度等時間段的起止時間的方法,廢話不多說,直接貼代碼,如果你覺得有用,請多多推薦。 ...
  • 本文是一篇介紹net同步非同步的文章,是為張四火同學原創的。請張四活同學及廣大讀者指出,文章不通順的地方。後續應當還有兩篇文章敬請期待 ...
  • 在WPF里用MediaElement控制項,實現一個迴圈播放單一視頻的程式,同時可以控制視頻的播放、暫停、停止。 一種方式,使用MediaElement.MediaEnded事件,在視頻播放結束後,自動重新播放; 另一種方式,使用WPF定時器,在定時器事件里寫入視頻播放代碼。 後者優點是可以控制迴圈時 ...
  • 最近因為公司業務需要,又有機會擼winform了,這次的需求是因為公司有項目申報的這塊業務,項目申報前期需要關註政府發佈的相關動態信息,政府部門網站過多,人工需要一個一個網站去瀏覽和查閱,有時候還會遺漏掉,因此呢,我們打算用爬蟲+移動端web來做,我呢主要負責爬蟲和web Api。 爬蟲篇 爬蟲主要 ...
  • AspNetCoreApi 跨域處理 如果咱們有處理過MV5 跨域問題這個問題也不大。 (1)為什麼會出現跨域問題: 瀏覽器安全限制了前端腳本跨站點的訪問資源,所以在調用WebApi 介面時不能成功訪問資源,原因“同源策略”的存在: 同源指以下幾點相同 (1) IP地址/功能變數名稱 (2) 埠號 (3) ...