Check if partition elimination works 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 '%&lt;TABLE_NAME&gt;%'
OPTION (RECOMPILE);


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

Thanks !

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