How to see, what's *really* going on in a query execution?

In SQL Server 2008, I would like to examine what is really going on behind the scenes when a query is executed. Can I somehow see the following?

1. The number of (and maybe even the content of) the log records "produced" by a query.
2. When the contents of the log is actually applied to the real data structures. For example:
   1. when are new rows actually added to the (clustering) index structure?
   2. and did the update/insertion imply a B-tree re-organization (and if so, how large part of that B-tree (how many pages)开发者_如何学编程 were affected in that)? Hmm, maybe I should have posted a separate question about this...
3. Number of buffer misses/hits for each accessed table.
4. The contents of the buffer (which, or how many, pages from the table are currently in the buffer)?

BTW: I considered splitting this question up in multiple smaller questions, but I guess they are somewhat quite related.

Here is a simple demo script to start your journey into learning about the SQL Server Internals. To see the individual actions of a query go to Paul Randal's SQLSkills Blog.

Other posts on this blog will cover topics like DBCC PAGE which will allow you to see the contents of the PAGE and DBCC IND which will show you the allocation maps for tables/indexes. I recommend reading a copy of Microsoft SQL Server 2008 Internals which covers all of these topics in detail.

Transaction Log Records Demo Script

For the two Selects the misses are shown as the physical reads and read-ahead reads, these are the reads from disk into the buffer pool. The query engine then uses these pages and this is the logical reads. So for the io stats on the warm select it has no physical reads or read-ahead reads so all the pages required where already in the the buffer pool.

/* -- Cleanup Script
USE master
DROP DATABASE ExampleDB
*/
SET NOCOUNT ON
SET STATISTICS IO OFF
GO
CREATE DATABASE ExampleDB
GO
USE ExampleDB
GO
-- Force a CHECKPOINT to CLEAR the Transaction Log
CHECKPOINT
GO
-- Show Empty Transaction Log
SELECT * FROM fn_dblog(NULL,NULL)
GO
-- CREATE SIMPLE TABLE
CREATE TABLE NewTable
(
    A   int identity NOT NULL PRIMARY KEY,
    B   char(8000) NOT NULL DEFAULT ('')
)
GO
-- Show Transaction Log With CREATE TABLE records
SELECT * FROM fn_dblog(NULL,NULL)
GO
-- Force a CHECKPOINT to CLEAR the Transaction Log
CHECKPOINT
GO
PRINT 'INSERT Start'
GO
INSERT  NewTable
VALUES (DEFAULT)
GO 1000
PRINT 'INSERT End'
GO
-- Show Transaction Log With INSERT records
SELECT * FROM fn_dblog(NULL,NULL)
GO
-- Turn On IO Statistics (these appear in the messages tab)
SET STATISTICS IO ON
GO
-- Force a CHECKPOINT to CLEAR the Transaction Log and modified buffer pages
CHECKPOINT
GO
-- DROP CLEAN BUFFERS to empty cache
DBCC DROPCLEANBUFFERS
GO
PRINT 'SELECT Start Cold Cache'
GO
SELECT * FROM NewTable
GO
PRINT 'SELECT End Cold Cache'
GO
PRINT 'SELECT Start Warm Cache'
GO
SELECT * FROM NewTable
GO
PRINT 'SELECT End Warm Cache'
GO
-- Force a CHECKPOINT to CLEAR the Transaction Log
CHECKPOINT
GO
DROP TABLE NewTable
-- Show Transaction Log With DROP TABLE records
SELECT * FROM fn_dblog(NULL,NULL)
GO

For the contents of the buffer pool the starting point is this DMV

SELECT      *
FROM        sys.dm_os_buffer_descriptors

You have the options of SQL Profiler and viewing a stored procedure execution plan.