Saturday, January 15, 2011

SQL 2008 Core Studies - Index Internals


/*
05 - Index Internal.sql, by review Employee table
Remark: Learned from sqlskills.com
*/


USE MCM2008;
GO

-- Creating the Employee table, its clustered index and the
-- index on SSN
CREATE TABLE Employee
(
EmployeeID INT NOT NULL IDENTITY,
LastName NCHAR(30) NOT NULL,
FirstName NCHAR(29) NOT NULL,
MiddleInitial NCHAR(1) NULL,
SSN CHAR(11) NOT NULL,
OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk'
);
GO

-- Add the clustered index
ALTER TABLE Employee
ADD CONSTRAINT EmployeePK
PRIMARY KEY CLUSTERED (EmployeeID)
ON [PRIMARY];
GO

-- Add the nonclustered unique key
ALTER TABLE Employee
ADD CONSTRAINT SSNUK
UNIQUE NONCLUSTERED (SSN);
GO

SP_HELP EMPLOYEE
GO
/*
Name Owner Type Created_datetime
---------- ------ ------------ -----------------------
Employee dbo user table 2011-01-15 15:31:25.047


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
---------------- ------- --------- ----------- ----- ----- --------- ----------------------- ----------------------------------- ------------------------------
EmployeeID int no 4 10 0 no (n/a) (n/a) NULL
LastName nchar no 60 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
FirstName nchar no 58 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
MiddleInitial nchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
SSN char no 11 no no no SQL_Latin1_General_CP1_CI_AS
OtherColumns char no 258 no no no SQL_Latin1_General_CP1_CI_AS


Identity Seed Increment Not For Replication
----------------- ---------- -------------- -------------------
EmployeeID 1 1 0


RowGuidCol
------------------------------------
No rowguidcol column defined.


Data_located_on_filegroup
-----------------------------------
PRIMARY


index_name index_description index_keys
-------------------- --------------------------------------------------------------------------------- ------------------------
EmployeePK clustered, unique, primary key located on PRIMARY EmployeeID
SSNUK nonclustered, unique, unique key located on PRIMARY SSN


constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
------------------------------------ ---- ------------------------------------- ------------- ------------- -------------- ---------------------- ---------------------
DEFAULT on column OtherColumns DF__Employee__OtherC__00551192 (n/a) (n/a) (n/a) (n/a) ('Junk')
PRIMARY KEY (clustered) EmployeePK (n/a) (n/a) (n/a) (n/a) EmployeeID
UNIQUE (non-clustered) SSNUK (n/a) (n/a) (n/a) (n/a) SSN


No foreign keys reference table 'EMPLOYEE', or you do not have permissions on referencing tables.
No views with schema binding reference table 'EMPLOYEE'.
*/

USE IndexInternals;
GO

SELECT index_depth AS D,
index_level AS L,
record_count AS 'Count',
page_count AS PgCnt,
avg_page_space_used_in_percent AS 'PgPercentFull',
min_record_size_in_bytes AS 'MinLen',
max_record_size_in_bytes AS'MaxLen',
avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
(DB_ID('IndexInternals'),
OBJECT_ID('IndexInternals.dbo.Employee'),
1,
NULL,
'DETAILED');
GO
/*
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
3 0 80000 4000 99.3081294786261 400 400 400
3 1 4000 7 91.7540400296516 11 11 11
3 2 7 1 1.09957993575488 11 11 11

(3 row(s) affected)
*/

USE master;
go

IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
DROP TABLE sp_tablepages;
go

CREATE TABLE sp_tablepages
(
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
CONSTRAINT sp_tablepages_PK
PRIMARY KEY (PageFID, PagePID)
);
GO

------------------------------------------------------------------------------
-- How do you use sp_tablepages?
-- Just truncate the table before insert and then select!
------------------------------------------------------------------------------
USE IndexInternals;
GO

TRUNCATE TABLE sp_tablepages;
INSERT INTO sp_tablepages
--EXEC ('DBCC IND (AdventureWorks2008, [Sales.SalesOrderDetail], -1)');
EXEC ('DBCC IND (IndexInternals, Employee, 1)');
GO

SELECT IndexLevel
, PageFID
, PagePID
, PrevPageFID
, PrevPagePID
, NextPageFID
, NextPagePID
FROM sp_tablepages
ORDER BY IndexLevel DESC, PrevPagePID;
GO


sp_helpindex 'Employee'
GO
/*
index_name index_description index_keys
----------------- ----------------------------------------------------------- ---------------
EmployeePK clustered, unique, primary key located on PRIMARY EmployeeID
EmployeeSSNUK nonclustered, unique, unique key located on PRIMARY SSN
*/

SELECT index_depth AS D,
index_level AS L,
record_count AS 'Count',
page_count AS PgCnt,
avg_page_space_used_in_percent AS 'PgPercentFull',
min_record_size_in_bytes AS 'MinLen',
max_record_size_in_bytes AS'MaxLen',
avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
(DB_ID('IndexInternals'),
OBJECT_ID('IndexInternals.dbo.Employee'),
2,
NULL,
'DETAILED');
/*
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
2 0 80000 179 99.3661106992834 16 16 16
2 1 179 1 44.2055843834939 18 18 18

(2 row(s) affected)
*/

SET STATISTICS IO ON
SELECT e.*
FROM dbo.Employee AS e
WHERE e.SSN = '123-07-9980';
GO
/*
EmployeeID LastName FirstName MiddleInitial SSN OtherColumns
----------- ------------------------------ ----------------------------- ------------- ----------- -----------------------
37281 Ugo A. Scott 123-07-9980 Junk

(1 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/