SQL SERVER – 2005 – Introduction to sys.dm_exec_query_optimizer_info

Many times when I am just bored I surf Book On Line for SQL Server 2005. Almost all the time I find something new which makes me believe that I have lot to learn and there are so many things I am not aware of. Today I found system catalog view sys.dm_exec_query_optimizer_info. I just enjoyed reading about it and now I will share this with you.

If my readers have found something like this please post it here and I will share that with everybody on this blog. I believe in community development and you can be part of it.

sys.dm_exec_query_optimizer_info returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements. For example, you can use the total number of optimizations, the elapsed time value, and the final cost value to compare the query optimizations of the current workload and any changes observed during the tuning process. All occurrence values are cumulative and are set to 0 at system restart. All values for value fields are set to NULL at system restart.

SELECT *
FROM sys.dm_exec_query_optimizer_info

Resultset will contain three columns:

counter - Name of optimizer statistics event.

occurrence
– Number of occurrences of optimization event for this counter. This many times the counter specified in counter column is optimized.

value - Average property value per event occurrence.

I found reading about this very interesting. Friends, if you know something which interests you post here or point me and I will write about it.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

2 thoughts on “SQL SERVER – 2005 – Introduction to sys.dm_exec_query_optimizer_info

  1. Hi,

    I am working on SQL Server 2000, and i want to know after a user session gets created with my database,
    How much memory space gets reserved by this session.
    or
    Is there any default value to this????????

    and my second question is
    How to retreive the checkpoint duration of the server.
    I dont want to set any value but lets suppose someone has already changed it and now i want to know whats that……

    Regards,
    Sarabpreet Singh
    MCTS, MCITP

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s