Used Often in My Experience with New Data Models

-- Used often in my experience with new data models

-- Helps get me up to speed much quicker.

use AdventureWorks2008

GO

DECLARE @table_schema sysname

DECLARE @table sysname

DECLARE @column sysname

DECLARE @datatype sysname

DECLARE @designed_length int

DECLARE @all_count int

DECLARE @sql nvarchar(4000)

DECLARE @origfillfactor varchar(10)

SETNOCOUNTON

--EXEC sp_updatestats

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED-- Will speed things up a bit

CREATETABLE #table_info

(table_schema sysnameNOTNULL

,table_name sysnameNOTNULL

,column_name sysnameNOTNULL

,data_type sysnameNOTNULL

,origfillfactor varchar(10)null

,designed_length intNULL

,max_length intNULL

,distinct_count intNULL

,all_count intNOTNULL

,cardinality AS

CASE

WHEN distinct_count ISNULLTHENCAST(data_type ASvarchar(7))

WHEN all_count = 0 THENCAST('No rows'ASvarchar(7))

ELSECAST(

CAST(CAST(distinct_count ASdecimal)/CAST(all_count ASdecimal)ASdecimal(18,4))ASvarchar(7))

END

)

DECLARE c CURSORFAST_FORWARDFOR

SELECT

ist.table_schema,

isc.table_name,

isc.column_name,

isc.data_type,

si.OrigFillFactor,

COALESCE(isc.character_maximum_length, isc.numeric_precision),

si.rowcnt

--si.name as IndexName,

FROMinformation_schema.columns isc

INNERJOINinformation_schema.tables ist

ON isc.table_name = ist.table_name

INNERJOINsysindexes si

ON isc.table_name =OBJECT_NAME(si.id)

WHERE ist.table_type ='base table'

AND ist.table_name notlike'dt%'

AND si.indid IN(0,1)

ORDERBY ist.table_schema, isc.table_name, isc.column_name

OPEN c

FETCHNEXTFROM c INTO @table_schema, @table, @column, @datatype, @origfillfactor, @designed_length, @all_count

WHILE@@FETCH_STATUS= 0

BEGIN

IF @datatype IN('text','ntext','image','xml','geography')

BEGIN

SET @sql ='SELECT '''+ @table_schema +''', '''+ @table +''', '''+ @column +''', '''+ @datatype +''', '''+ @origfillfactor +''''

SET @sql = @sql +', '+CAST(@designed_length ASvarchar(10))+', MAX(DATALENGTH(['+ @column +']))'

SET @sql = @sql +', NULL'+', '+CAST(@all_count ASvarchar(10))+' FROM ['+ @table_schema +'].['+ @table +']'

END

ELSE

BEGIN

SET @sql ='SELECT '''+ @table_schema +''','''+ @table +''', '''+ @column +''', '''+ @datatype +''', '''+ @origfillfactor +''''

SET @sql = @sql +', '+CAST(@designed_length ASvarchar(10))+', MAX(LEN(CAST(['+ @column +'] AS VARCHAR(8000))))'

SET @sql = @sql +', COUNT(DISTINCT ['+ @column +'])'

SET @sql = @sql +', '+CAST(@all_count ASvarchar(10))+' FROM ['+ @table_schema +'].['+ @table +']'

END

PRINT @sql

INSERTINTO #table_info(table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count)

EXEC(@sql)

FETCHNEXTFROM c INTO @table_schema, @table, @column, @datatype, @origfillfactor, @designed_length, @all_count

END

CLOSE c

DEALLOCATE c

--SELECT table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count, cardinality

--FROM #table_info

selecttab.name as TableName, idx.name as IndexName, idx.fill_factor, idx.type_desc,

col.name as columnname, col.is_computed, idxc.is_included_column, ius.user_seeks, ius.user_scans, ius.user_lookups,

ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update, ius.last_system_update

INTO #index_info

FROMsys.tables tab

INNERJOINsys.indexes idx on tab.object_id= idx.object_id

INNERJOINsys.index_columns idxc ON idxc.index_id = idx.index_id and idxc.object_id= tab.object_id

INNERJOINsys.columns col ON col.column_id = idxc.column_id and col.object_id= tab.object_id

INNERJOINsys.dm_db_index_usage_stats ius ON idx.object_id= ius.object_idAND idxc.index_id = ius.index_id

SELECTdistinct a.table_schema, a.table_name, a.column_name, a.data_type, a.origfillfactor, a.designed_length, a.max_length,

a.distinct_count, a.all_count, a.cardinality

--, b.indexName, b.Fill_Factor, b.Type_desc, b.is_computed, b.is_included_column,

--b.user_seeks, b.user_scans, b.user_lookups, b.user_updates, b.last_user_seek, b.last_user_scan, b.last_user_lookup, b.last_user_update,

--b.last_system_update

FROM

#table_info a

--LEFT JOIN #index_info b ON a.table_name = b.tablename AND a.column_name = b.columnname

SETTRANSACTIONISOLATIONLEVELREADCOMMITTED

DROPTABLE #table_info

DROPTABLE #index_info

-- Let's look at these results:

-- Key results :

-- Person.Person.Firstname (1018), LastName (1206), MiddleName (70)

-- Context... Name often searchable.

-- Cardinality of only 5 and 6 percent; will yield poor selectivity

selectdistinctCount(*)as Quantity, lastname from Person.Person groupby LastName havingCOUNT(*) 2 orderby Quantity DESC

-- out of 20k people, 211 have last name of "Diaz". 1%? not statistically accurate

-- Likely closer to .001

-- result record #179; why have an nchar datatype with a designed_length of 1, a max length of one and a distinct count of 3?

-- let's look

selectdistinct transactiontype from Production.TransactionHistory

-- is that Nchar datatype truly required? why isn't it an int back to a reference table? etc...

-- back to slide deck. results / takeway

------

use AdventureWorks2008

GO

DECLARE @table_schema sysname

DECLARE @table sysname

DECLARE @column sysname

DECLARE @datatype sysname

DECLARE @designed_length int

DECLARE @all_count int

DECLARE @sql nvarchar(4000)

DECLARE @origfillfactor varchar(10)

SETNOCOUNTON

--EXEC sp_updatestats

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED-- Will speed things up a bit

CREATETABLE #table_info

(table_schema sysnameNOTNULL

,table_name sysnameNOTNULL

,column_name sysnameNOTNULL

,data_type sysnameNOTNULL

,origfillfactor varchar(10)null

,designed_length intNULL

,max_length intNULL

,distinct_count intNULL

,all_count intNOTNULL

,cardinality AS

CASE

WHEN distinct_count ISNULLTHENCAST(data_type ASvarchar(7))

WHEN all_count = 0 THENCAST('No rows'ASvarchar(7))

ELSECAST(

CAST(CAST(distinct_count ASdecimal)/CAST(all_count ASdecimal)ASdecimal(18,4))ASvarchar(7))

END

)

DECLARE c CURSORFAST_FORWARDFOR

SELECT

ist.table_schema,

isc.table_name,

isc.column_name,

isc.data_type,

si.OrigFillFactor,

COALESCE(isc.character_maximum_length, isc.numeric_precision),

si.rowcnt

--si.name as IndexName,

FROMinformation_schema.columns isc

INNERJOINinformation_schema.tables ist

ON isc.table_name = ist.table_name

INNERJOINsysindexes si

ON isc.table_name =OBJECT_NAME(si.id)

WHERE ist.table_type ='base table'

AND ist.table_name notlike'dt%'

AND si.indid IN(0,1)

ORDERBY ist.table_schema, isc.table_name, isc.column_name

OPEN c

FETCHNEXTFROM c INTO @table_schema, @table, @column, @datatype, @origfillfactor, @designed_length, @all_count

WHILE@@FETCH_STATUS= 0

BEGIN

IF @datatype IN('text','ntext','image','xml','geography')

BEGIN

SET @sql ='SELECT '''+ @table_schema +''', '''+ @table +''', '''+ @column +''', '''+ @datatype +''', '''+ @origfillfactor +''''

SET @sql = @sql +', '+CAST(@designed_length ASvarchar(10))+', MAX(DATALENGTH(['+ @column +']))'

SET @sql = @sql +', NULL'+', '+CAST(@all_count ASvarchar(10))+' FROM ['+ @table_schema +'].['+ @table +']'

END

ELSE

BEGIN

SET @sql ='SELECT '''+ @table_schema +''','''+ @table +''', '''+ @column +''', '''+ @datatype +''', '''+ @origfillfactor +''''

SET @sql = @sql +', '+CAST(@designed_length ASvarchar(10))+', MAX(LEN(CAST(['+ @column +'] AS VARCHAR(8000))))'

SET @sql = @sql +', COUNT(DISTINCT ['+ @column +'])'

SET @sql = @sql +', '+CAST(@all_count ASvarchar(10))+' FROM ['+ @table_schema +'].['+ @table +']'

END

PRINT @sql

INSERTINTO #table_info(table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count)

EXEC(@sql)

FETCHNEXTFROM c INTO @table_schema, @table, @column, @datatype, @origfillfactor, @designed_length, @all_count

END

CLOSE c

DEALLOCATE c

--SELECT table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count, cardinality

--FROM #table_info

selecttab.name as TableName, idx.name as IndexName, idx.fill_factor, idx.type_desc,

col.name as columnname, col.is_computed, idxc.is_included_column, ius.user_seeks, ius.user_scans, ius.user_lookups,

ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update, ius.last_system_update

INTO #index_info

FROMsys.tables tab

INNERJOINsys.indexes idx on tab.object_id= idx.object_id

INNERJOINsys.index_columns idxc ON idxc.index_id = idx.index_id and idxc.object_id= tab.object_id

INNERJOINsys.columns col ON col.column_id = idxc.column_id and col.object_id= tab.object_id

INNERJOINsys.dm_db_index_usage_stats ius ON idx.object_id= ius.object_idAND idxc.index_id = ius.index_id

SELECTdistinct a.table_schema, a.table_name, a.column_name, a.data_type, a.origfillfactor, a.designed_length, a.max_length,

a.distinct_count, a.all_count, a.cardinality

, b.indexName, b.Fill_Factor, b.Type_desc, b.is_computed, b.is_included_column,

b.user_seeks, b.user_scans, b.user_lookups, b.user_updates, b.last_user_seek, b.last_user_scan, b.last_user_lookup, b.last_user_update,

b.last_system_update

FROM

#table_info a

LEFTJOIN #index_info b ON a.table_name = b.tablename AND a.column_name = b.columnname

SETTRANSACTIONISOLATIONLEVELREADCOMMITTED

DROPTABLE #table_info

DROPTABLE #index_info

-- Results / Take-away

-- Let's look at these results:

-- Key results :

-- Person.Person.Firstname IX_Person_LastName_FirstName_MiddleName

-- Context... Name often searchable.

-- Cardinality of only 5 and 6 percent; will yield poor selectivity

-- PER dm_db_index_usage_stats DMV, this index yields user_scans, not the expected seeks

-- Stats from an existing client show this way for their "Customer" table::

-- Distinctallcard..IDX_NameFFType Seek Scan

-- 50586849618520.1020nci2_LastnameFirstName90NONCLUSTERED 19090 634

-- result record #200; transactiontype from Production.TransactionHistory

-- Seems odd to me that the transactiontype is not utilized in an index with some other columns... like actual cost perhaps?

-- let's look at the procs in the db to see if there are any....

-- select * from sysobjects where id in (select id from syscomments where text like '%transactiontype%')

-- there aren't any; but reporting would probably utilize this for cross sections of transaction types.

-- result record #237; PurchaseOrderDetailID -- Clustered Index. Scans, not seeks.

-- perhaps if the PK wasn't clustered the cardinality of PurchaseorderdetailID would provide seeks in the clustered index instead of scans.

-- back to slide deck

------

use tempdb

-- create a table with 2000 rows. 1000 of them have the values 1 to 1000 each once (no

-- duplicates). Then we have 1000 rows with the value 5000.

--drop table t

createtable t(col1 int)

declare @i int

set @i = 0

while @i 1000

begin

insertinto t(col1)values (@i)

set @i = @i + 1

end

set @i = 0

while @i 1000

begin

insertinto t(col1)values (5000)

set @i = @i + 1

end

select*from t

selectcount(*),col1 from t

groupby col1 havingCOUNT(*) 1

-- Let's create some fullscan statistics on the column in our table

createstatistics t_col1 on t(col1)withfullscan

-- compile with no value to sniff.

dbcc freeproccache

declare @p int

select*from t where col1 = @p

-- (look at the output plan to see the estimate)

-- estimated # of rows = 2... 2?

-- same scenario but set a value. The value 5000 has 1000 instances, but we estimate 2 rows.

-- Why? Well, we compile the batch before we execute it, so the optimizer in 2005 does not see

-- the parameter value and we treat this the same as the previous case because it hasn’t been

-- actually set when the query is compiled

dbcc freeproccache

declare @p int

set @p = 5000

select*from t where col1 = @p

-- Let's use the option recompile as a workaround.

-- The first optimization has the same problem as before - estimates 2 rows

dbcc freeproccache

declare @p int

set @p = 1

select*from t where col1 = @p

option(recompile)

-- Another (better) workaround is to use the new optimize for hint - it avoids the recompile

-- and we estimate 1 row

dbcc freeproccache

declare @p int

set @p = 1

select*from t where col1 = @p

option (optimize for (@p = 1))

-- last workaround - use a stored procedure. This will create a new context in the

-- server and lets the optimizer "see" the parameter and sniff it during compilation.

createprocedure foo(@p int)

as

select*from t where col1 = @p

-- compile and examine the plan for this - estimates 1 row instead of 2

dbcc freeproccache

execute foo1

--back to slide deck

------

USE AdventureWorks2008

GO

CREATEPROCEDURE MyProc( @d datetime)

AS

SELECTCOUNT(*)

FROM Sales.SalesOrderHeader

WHERE OrderDate @d+1

GO

exec myproc'1/1/06'-- est. # of rows 1

USE AdventureWorks2008

GO

CREATEPROCEDURE MyProc2( @d datetime)

AS

BEGIN

-- creating a local variable... value unknown, defaults, in this case to 30% selectivity.

DECLARE @d2 datetime

SET @d2 = @d+1

SELECTCOUNT(*)

FROM Sales.SalesOrderHeader

WHERE OrderDate @d2

END

exec myproc2'1/1/06'-- est. # of rows 9439