The DMV sys.dm_exec_query_optimizer_info keeps statistics of optimizer activity. My simple query shows optimizations, elapsed time, final cost and more.
SQL SERVER – Finding Last Backup Time for All Database
A quick script I use to find the Last Backup Time for all the databases on my server, joining sys.sysdatabases with msdb.dbo.backupset.
SQL SERVER – Find Total Number of Transactions on Interval
How many Transactions run on your server per minute? A handy script reads sys.dm_os_performance_counters twice with a WAITFOR DELAY between the reads.
SQL SERVER – Find Queries using Parallelism from Cached Plan
A script by Feodor Georgiev that finds queries using parallelism from the cached plan, along with their plans. Please avoid running it on a busy production server.
SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats
How many pages does an index use, and how many B-Tree levels does it have? sys.dm_db_index_physical_stats shows index levels, page count and record count.
SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache
What is in your memory cache? A query on sys.dm_os_buffer_descriptors shows the data pages in buffer pool for each table and index.
SQL SERVER – List All the DMV and DMF on Server
“How many DMV and DVF are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Training.