SQL SERVER – 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.

SQL SERVER - Introduction to sys.dm_exec_query_optimizer_info dm_exec_query_optimizer_info

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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance
Next Post
SQL SERVER – 2005 – List All Column With Identity Key In Specific Database

Related Posts

2 Comments. Leave new

  • Sarabpreet Singh
    May 1, 2008 3:48 pm

    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

    Reply

Leave a Reply

Menu