Sunday, January 16, 2011

SQL 2008 Core Studies - Optimizer Statistics


/*
07 - Statistics.sql
Remark: Learned from sqlskills.com
*/

-- Restore Credit database, downloaded from past events, sqlskills.com
RESTORE VERIFYONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak';
GO
/*
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditData.mdf" is not in a valid directory.
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditLog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
*/

RESTORE FILELISTONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak';
GO
/*
LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint
-------------- ------------------------------------------------------------------------------------- ---- --------------- ----------- -------------------- ------- ------------- ---------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------------------- ------------------------------------ ---------- --------- ---------------
CreditData C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditData.mdf D PRIMARY 1048576000 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 163708928 512 1 NULL 32000000119200089 0C50A096-EAE8-4F54-B482-774964509C60 0 1 NULL
CreditLog C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditLog.ldf L NULL 419430400 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

(2 row(s) affected)
*/


RESTORE VERIFYONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak'
WITH MOVE N'CreditData' TO N'C:\SQLskills\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQLskills\CreditLog.ldf' ;
GO
/*
The backup set on file 1 is valid.
*/

RESTORE DATABASE Credit FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak' WITH --REPLACE,
MOVE N'CreditData' TO N'C:\SQLskills\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQLskills\CreditLog.ldf',
NOUNLOAD, NOREWIND, STATS = 10
GO
/*
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 19984 pages for database 'Credit', file 'CreditData' on file 1.
Processed 1 pages for database 'Credit', file 'CreditLog' on file 1.
RESTORE DATABASE successfully processed 19985 pages in 4.086 seconds (38.211 MB/sec).
*/

USE Credit;
GO

EXEC sp_helpindex Member
GO
/*
index_name,index_description,index_keys
member_corporation_link,nonclustered located on PRIMARY,corp_no
member_ident,clustered, unique, primary key located on PRIMARY,member_no
member_region_link,nonclustered located on PRIMARY,region_no
*/

EXEC sp_helpstats Member;
GO
/*
This object does not have any statistics.
*/

CREATE INDEX MemberName
ON Member (LastName, FirstName, MiddleInitial);
GO

EXEC sp_helpstats Member;
GO
/*
This object does not have any statistics.
*/

DBCC SHOW_STATISTICS('Member', 'MemberName');
GO

/*
Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows
----------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ----------------------- --------------------
MemberName Jan 16 2011 10:57AM 10000 10000 26 0 21.5526 YES NULL 10000

(1 row(s) affected)

All density Average Length Columns
------------- -------------- --------------------------------------------------
0.03846154 5.6154 lastname
0.0001 16.5526 lastname, firstname
0.0001 17.5526 lastname, firstname, middleinitial
0.0001 21.5526 lastname, firstname, middleinitial, member_no

(4 row(s) affected)

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
--------------- ------------- ------------- -------------------- --------------
ANDERSON 0 385 0 1
BARR 0 385 0 1
CHEN 0 385 0 1
DORR 0 385 0 1
EFLIN 0 385 0 1
FLORINI 0 385 0 1
GOHAN 0 385 0 1
HAHN 0 385 0 1
INFANTE 0 385 0 1
JONES 0 385 0 1
KAHRMANN 0 385 0 1
LING 0 385 0 1
MAKAI 0 385 0 1
NELSON 0 385 0 1
OLPHANT 0 385 0 1
PAYNTER 0 385 0 1
QUINT 0 384 0 1
RYAN 0 384 0 1
STEIN 0 384 0 1
TOLSKY 0 384 0 1
UNAKLET 0 384 0 1
VANN 0 384 0 1
WOLFMULE 0 384 0 1
XAVIER 0 384 0 1
YEONG 0 384 0 1
ZUCKER 0 384 0 1

(26 row(s) affected)
*/

SET STATISTICS IO ON
GO

SELECT m.LastName, m.FirstName, m.MiddleInitial,
m.phone_no, m.City
FROM dbo.member AS m
WHERE m.FirstName like 'kim%';
GO
/*
LastName FirstName MiddleInitial phone_no City
--------------- --------------- ------------- ------------- ---------------
ANDERSON KIMDQ

(1 row(s) affected)

Table 'member'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/


exec sp_autostats 'Member';
GO
/*
Global statistics settings for [Credit]:
Automatic update statistics: ON
Automatic create statistics: ON

settings for table [Member]

Index Name AUTOSTATS Last Updated
-------------------------------- --------- -----------------------
[member_ident] ON 2008-08-26 17:18:12.593
[member_corporation_link] ON 2008-08-26 17:18:12.673
[member_region_link] ON 2008-08-26 17:18:12.793
[MemberName] ON 2011-01-16 10:57:07.027
[_WA_Sys_00000003_0CBAE877] ON 2011-01-16 16:21:47.097

(5 row(s) affected)
*/

UPDATE STATISTICS [dbo].[Member]
WITH FULLSCAN
GO

exec sp_autostats 'Member';
GO
/*
Global statistics settings for [Credit]:
Automatic update statistics: ON
Automatic create statistics: ON

settings for table [Member]

Index Name AUTOSTATS Last Updated
-------------------------------- --------- -----------------------
[member_ident] ON 2011-01-16 19:30:23.740
[member_corporation_link] ON 2011-01-16 19:30:23.753
[member_region_link] ON 2011-01-16 19:30:23.763
[MemberName] ON 2011-01-16 19:30:23.783
[_WA_Sys_00000003_0CBAE877] ON 2011-01-16 19:30:23.823

(5 row(s) affected)
*/