Developing Matt

My Technical Journal

Optimizer activity

leave a comment »

You can (in 2005 and later) view the optimizer general activity with sys.dm_exec_query_optimizer_info.  There are three columns:

Counter: what optimizer event

Occurrence: the number of times it is used

Value: some of them use this to give more information. 

For example, query this dmv and look for trivial plan.  You will see how many times a trivial plan was used.  How often was each optimization phase used?  Check the search 0, search 1, and search 2 counters.  The value column for these shows the average count of tables referenced.

Pretty fun stuff.  Handy in gleaning general information on how well your queries are structured and/or how complicated they are (search 2 indicates that a trivial plan or simplification could not be used…and that the first to phases of full optimization could not be used either)

(Also, keep in mind the query to this dmv itself will have a plan as well, spoiling some of the results)

See sql server books online for “sys.dm_exec_query_optimizer_info” for more info. 

This information was gathered from Itzik Ben-Gan’s book “Inside Microsoft t-Sql programming”


Written by matt

December 29, 2008 at 10:31 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: