sys.dm_exec_query_stats not dynamic?

EmeraldDBA

We noticed that after several months of heavy OLTP last_elapsed_time column
is no longer updated. DBCC FLUSHPROCINDB causes recompiles and restores the
updates. This is happening on several servers of 9.0.3152 build. We are
keeping track of our key SP performance by monitoring the last_elapsed_time
column. Does anyone have any words of wisdom?

Thanks,
Paul

jason

How do you know it is not updated? What about last_execution_time?

--
Jason Massie
www:
rss:

"EmeraldDBA" <> wrote in message
news:...
> We noticed that after several months of heavy OLTP last_elapsed_time
> column
> is no longer updated. DBCC FLUSHPROCINDB causes recompiles and restores
> the
> updates. This is happening on several servers of 9.0.3152 build. We are
> keeping track of our key SP performance by monitoring the
> last_elapsed_time
> column. Does anyone have any words of wisdom?
>
> Thanks,
> Paul
>

Uri Dimant

Hi
In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data
management view, look at the column last_user_update, you can also see when
the table was last accessed (last_user_seek and last_user_scan - do a MAX on
them).


"EmeraldDBA" <> wrote in message
news:...
> We noticed that after several months of heavy OLTP last_elapsed_time
> column
> is no longer updated. DBCC FLUSHPROCINDB causes recompiles and restores
> the
> updates. This is happening on several servers of 9.0.3152 build. We are
> keeping track of our key SP performance by monitoring the
> last_elapsed_time
> column. Does anyone have any words of wisdom?
>
> Thanks,
> Paul
>

Uri Dimant

Hi
In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data
management view, look at the column last_user_update, you can also see when
the table was last accessed (last_user_seek and last_user_scan - do a MAX on
them).


"EmeraldDBA" <> wrote in message
news:...
> We noticed that after several months of heavy OLTP last_elapsed_time
> column
> is no longer updated. DBCC FLUSHPROCINDB causes recompiles and restores
> the
> updates. This is happening on several servers of 9.0.3152 build. We are
> keeping track of our key SP performance by monitoring the
> last_elapsed_time
> column. Does anyone have any words of wisdom?
>
> Thanks,
> Paul
>