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...

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

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

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.