Blog Pages

▼

SQL Sentry Plan Explorer

SQL Sentry Plan Explorer (sqlSentry) is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio to make query plan analysis more efficient.

It is a Recommended tool to more comfortable view and for better understand of execution plans:
  • You can see the relevant icons on the execution plan by sign a part of the query.
  • You can see lists of top operations by costs and more, and move from an operation in the table to the plan.
  • You have few views of the execution plan: diagram, XML, etc.
  • And more...





No comments:

Find out Page-splits

select Operation, AllocUnitName, COUNT(*) as OperationCount
from sys.fn_dblog(null,null)
where Operation = N'LOP_DELETE_SPLIT'
group by Operation, AllocUnitName
No comments:

Performance statistics for cached query plans

SELECT
DB_NAME(st.[dbid]) AS DBName,
OBJECT_NAME(st.objectid, st.[dbid]) as ObjectName,
qs.plan_generation_num,
qs.execution_count,
( SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1,
 ((CASE qs.statement_end_offset 
 WHEN -1 THEN DATALENGTH(st.[text])
 ELSE qs.statement_end_offset END 
 - qs.statement_start_offset)/2) + 1)
) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE DB_NAME(st.[dbid]) = 'DataBaseName' 
AND OBJECT_NAME(st.objectid, st.[dbid]) = 'ObjectName'

plan_generation_num - number of plan generation for this Object
execution_count - number of executions of the current plan

if plan_generation_num is more than 1 and execution_count 1:
new plan is generated for each execution --> probably not a good situation.
if plan_generation_num is 1 and execution_count is big:
probably good situation.
No comments: