When looking at a production SQL server, I usually ask a few questions. What is currently running? Are there a lot of waits? How much memory is used for the current queries? Using DMVs, we can now look into the SQL server to see what is happening. Here is what I do:
See what is currently running: select * from sys.dm_exec_requests where session_id>50. This will show me what is currently running in SQL. Session_ids below 50 are system processes. Since this shows live queries, I run it a couple of times to make sure there is only one row (my session) or occasionally 2/3. I look at the status, blocking_session_id, wait_time, wait_type, last_wait_type. If the wait_time is low (below 20), I don’t look into the other columns.
Now, if there are some long running queries, or suspended queries, I run: select * from sys.dm_os_waiting_tasks where session_id>50. I look at the wait_duration_ms and the wait_type. This will tell me why the queries are taking their time to complete.
The cache plans are stored in memory. To see how much memory is used to store these cache plans: select sum(size_in_bytes/1024/1024) as ‘Size in MB’ from sys.dm_exec_cached_plans.
To micromanage the individual T-SQL, I can get the plan_handle from the current task using sys.dm_exec_requests. Put this plan_handle into select * from sys.dm_exec_sql_text(insert_plan_handle_here). This will give me the T-SQL query that was run in the text column of the result set. To get a fancier/visual XML plan, use: select * from sys.dm_exec_query_plan(insert_plan_handle_here). Neat eh, thank Microsoft. The plan_handle can also be found in sys.dm_exec_cached_plans, use the above to methods to see the query plans of the cached queries.