Monday, March 11, 2013

Plan Caching and Reuse


Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:

sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_sql_text
sys.dm_exec_plan_attributes
sys.dm_exec_cached_plan_dependent_object
The following sql query is useful to exploring the current plan cache contents in buffer pool:
 

SELECT DB_NAME( st.dbid) AS DatabaseName,
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY 
 sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY 
 sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.[dbid] = DB_ID()
ORDER BY UseCounts DESC;

Tuesday, March 05, 2013

Rebuilding all indexes on a table and specifying options

If you are planning rebuild index  to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index.  But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)

-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO


UPDATE STATISTICS (Production.Product)
GO

(OR)

Alternatively you can use below options

-- By default STATISTICS_NORECOMPUTE = OFF

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = OFF);


--  For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product]   REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)


Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx