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.
SQL SERVER – Finding Expensive Cached Query Statements with DMVs
Finding the Most Expensive Queries starts with choosing a resource measure. I use cached statistics as an investigation aid.
SQL SERVER – Get Latest SQL Query for Sessions – DMV
Want the latest SQL query for sessions on your server? Join sys.dm_exec_connections with sys.dm_exec_sql_text using most_recent_sql_handle.
When Are Statistics Updated? What Triggers an Automatic Update
Statistics updated automatically need two things: enough changed rows, and a query that uses the statistic. This post explains the old 500 plus 20 percent rule and the newer square root threshold, then shows a million-row demo where last_updated changes only after a query compiles. It also covers FULLSCAN, asynchronous updates and when to update by hand.