SQL 2008 Core Studies - Data File Internals

/*
03 - Datafile Internal.sql, various tempdb space usage scenarios
Remark: Learned from sqlskills.com
*/
USE master;
GO

-- Make sure we start with a clean database
RESTORE DATABASE [SalesDB] 
 FROM  DISK = N'C:\download\SalesDBOriginal.bak' 
 WITH  FILE = 1,  
  MOVE N'SalesDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\SalesDB.mdf',  
  MOVE N'SalesDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\SalesDB_1.ldf',  
  NOUNLOAD,  REPLACE,  STATS = 10
GO

/*
RESTORE DATABASE SalesDB
 FROM DISK = N'C:\download\SalesDBOriginal.bak'
WITH STATS = 10, REPLACE;
GO

Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBData.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'SalesDBData' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBData.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBLog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'SalesDBLog' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBLog.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
*/

/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 24472 pages for database 'SalesDB', file 'SalesDBData' on file 1.
Processed 4 pages for database 'SalesDB', file 'SalesDBLog' on file 1.
Converting database 'SalesDB' from version 611 to the current version 655.
Database 'SalesDB' running the upgrade step from version 611 to version 621.
Database 'SalesDB' running the upgrade step from version 621 to version 622.
Database 'SalesDB' running the upgrade step from version 622 to version 625.
Database 'SalesDB' running the upgrade step from version 625 to version 626.
Database 'SalesDB' running the upgrade step from version 626 to version 627.
Database 'SalesDB' running the upgrade step from version 627 to version 628.
Database 'SalesDB' running the upgrade step from version 628 to version 629.
Database 'SalesDB' running the upgrade step from version 629 to version 630.
Database 'SalesDB' running the upgrade step from version 630 to version 631.
Database 'SalesDB' running the upgrade step from version 631 to version 632.
Database 'SalesDB' running the upgrade step from version 632 to version 633.
Database 'SalesDB' running the upgrade step from version 633 to version 634.
Database 'SalesDB' running the upgrade step from version 634 to version 635.
Database 'SalesDB' running the upgrade step from version 635 to version 636.
Database 'SalesDB' running the upgrade step from version 636 to version 637.
Database 'SalesDB' running the upgrade step from version 637 to version 638.
Database 'SalesDB' running the upgrade step from version 638 to version 639.
Database 'SalesDB' running the upgrade step from version 639 to version 640.
Database 'SalesDB' running the upgrade step from version 640 to version 641.
Database 'SalesDB' running the upgrade step from version 641 to version 642.
Database 'SalesDB' running the upgrade step from version 642 to version 643.
Database 'SalesDB' running the upgrade step from version 643 to version 644.
Database 'SalesDB' running the upgrade step from version 644 to version 645.
Database 'SalesDB' running the upgrade step from version 645 to version 646.
Database 'SalesDB' running the upgrade step from version 646 to version 647.
Database 'SalesDB' running the upgrade step from version 647 to version 648.
Database 'SalesDB' running the upgrade step from version 648 to version 649.
Database 'SalesDB' running the upgrade step from version 649 to version 650.
Database 'SalesDB' running the upgrade step from version 650 to version 651.
Database 'SalesDB' running the upgrade step from version 651 to version 652.
Database 'SalesDB' running the upgrade step from version 652 to version 653.
Database 'SalesDB' running the upgrade step from version 653 to version 654.
Database 'SalesDB' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 24476 pages in 5.466 seconds (34.982 MB/sec).
*/

USE SalesDB
GO

-- First off: large query spill to tempdb
--
-- Nasty query
--
SELECT S.*, P.*
 FROM Sales S JOIN Products P
 ON P.productID = S.ProductID
 ORDER BY P.NAME;
GO

-- What tasks are taking up space right now?
--
SELECT 
 t1.session_id,
 t1.request_id,
 t1.task_alloc,
 t1.task_dealloc,
 t2.sql_handle,
 t2.statement_start_offset,
 t2.statement_end_offset,
 t2.plan_handle
FROM ( SELECT session_id, request_id,
  SUM (internal_objects_alloc_page_count) as task_alloc,
  SUM (internal_objects_dealloc_page_count) as task_dealloc
 FROM sys.dm_db_task_space_usage
 GROUP BY session_id, request_id) AS t1,
 sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
 (t1.request_id = t2.request_id) AND
 (t1.task_alloc + t1.task_dealloc > 0)
ORDER BY t1.task_alloc DESC;
GO
/*
session_id request_id  task_alloc           task_dealloc         sql_handle                                            statement_start_offset statement_end_offset plan_handle
---------- ----------- -------------------- -------------------- ----------------------------------------------------- ---------------------- -------------------- -------------------------------------------------------
57         0           66496                504                  0x02000000C473422048B523B93F30F55A04C2B881820087A1    138                    -1                   0x06001000C4734220B8805117000000000000000000000000

(1 row(s) affected)
*/

-- And what's the task doing
--
SELECT * FROM sys.dm_exec_sql_text 
 (0x02000000C473422048B523B93F30F55A04C2B881820087A1);
GO
/*
dbid   objectid    number encrypted text
------ ----------- ------ --------- --------------------------------------------------------------------------
NULL   NULL        NULL   0         
-- First off: large query spill to tempdb
--
-- Nasty query
--
SELECT S.*, P.*
 FROM Sales S JOIN Products P
 ON P.productID = S.ProductID
 ORDER BY P.NAME;

(1 row(s) affected)
*/

-- Get query plan in XML format
SELECT * FROM sys.dm_exec_query_plan
 (0x06001000C4734220B8805117000000000000000000000000)
GO

-- Get query plan in TEXT/XML format
SELECT * FROM sys.dm_exec_text_query_plan
 (0x06001000C4734220B8805117000000000000000000000000,0,-1)
GO

-- Secondly: version store
--
ALTER DATABASE SalesDB SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Go to perfmon and watch version storage 
-- added from $SQLINSTANCE$:Transactions\
-- Version store size scale = 0.0001
--
UPDATE Sales SET Quantity = 1;
GO

-- RAW totals for all tempdb data files
-- How big is the version store?
-- Each page is 8k
--
SELECT * FROM sys.dm_db_file_space_usage;
GO