3

I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table.

How can I verify that partition elimination is actually occurring in the DB?

I tried checking it with this query. But I saw only that "partitioned = true" is mentioned, but no how many partitions were accessed.

SELECT 
  eqs.execution_count,
  CAST((1.)*eqs.total_worker_time/eqs.execution_count AS NUMERIC(10,1)) AS avg_worker_time,
  eqs.last_worker_time,
  CAST((1.)*eqs.total_logical_reads/eqs.execution_count AS NUMERIC(10,1)) AS avg_logical_reads,
  eqs.last_logical_reads,
    (SELECT TOP 1 SUBSTRING(est.text,statement_start_offset / 2+1 , 
    ((CASE WHEN statement_end_offset = -1 
      THEN (LEN(CONVERT(nvarchar(max),est.text)) * 2) 
      ELSE statement_end_offset END)  
      - statement_start_offset) / 2+1))  
    AS sql_statement,
  qp.query_plan
FROM sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text (eqs.sql_handle) AS est 
JOIN sys.dm_exec_cached_plans cp on 
  eqs.plan_handle=cp.plan_handle
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
WHERE est.text like '%<TABLE_NAME>%'
OPTION (RECOMPILE);

Do you have any other query that can help me identify if partition elimination occurs in my DB?

Thanks !

Roni.

1

Use Extended Events to capture actual execution plans, via the query_post_execution_showplan Event Library. Be aware of the following warning, found in the New Session Wizard, when creating the session:

query_post_execution_showplan

Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.

The following T-SQL will create the required Extended Events session. Do not run this on a production SQL Server unless you want to slow it to a crawl.

CREATE EVENT SESSION [CapturePlans] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
WITH (STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [CapturePlans] ON SERVER STATE = START;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.