Datarealized.files.wordpress.com
-- 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)
SET NOCOUNT ON
--EXEC sp_updatestats
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Will speed things up a bit
CREATE TABLE #table_info
(table_schema sysname NOT NULL
,table_name sysname NOT NULL
,column_name sysname NOT NULL
,data_type sysname NOT NULL
,origfillfactor varchar(10) null
,designed_length int NULL
,max_length int NULL
,distinct_count int NULL
,all_count int NOT NULL
,cardinality AS
CASE
WHEN distinct_count IS NULL THEN CAST(data_type AS varchar(7))
WHEN all_count = 0 THEN CAST('No rows' AS varchar(7))
ELSE CAST(
CAST(CAST(distinct_count AS decimal)/CAST(all_count AS decimal) AS decimal(18,4)) AS varchar(7))
END
)
DECLARE c CURSOR FAST_FORWARD FOR
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,
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
INNER JOIN sysindexes si
ON isc.table_name = OBJECT_NAME(si.id)
WHERE ist.table_type = 'base table'
AND ist.table_name not like 'dt%'
AND si.indid IN (0,1)
ORDER BY ist.table_schema, isc.table_name, isc.column_name
OPEN c
FETCH NEXT FROM 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 AS varchar(10)) + ', MAX(DATALENGTH([' + @column + ']))'
SET @sql = @sql + ', NULL' + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table_schema + '].[' + @table + ']'
END
ELSE
BEGIN
SET @sql = 'SELECT ''' + @table_schema + ''',''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''', ''' + @origfillfactor + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(LEN(CAST([' + @column + '] AS VARCHAR(8000))))'
SET @sql = @sql + ', COUNT(DISTINCT [' + @column + '])'
SET @sql = @sql + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table_schema + '].[' + @table + ']'
END
PRINT @sql
INSERT INTO #table_info (table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count)
EXEC(@sql)
FETCH NEXT FROM 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
select tab.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
FROM sys.tables tab
INNER JOIN sys.indexes idx on tab.object_id = idx.object_id
INNER JOIN sys.index_columns idxc ON idxc.index_id = idx.index_id and idxc.object_id = tab.object_id
INNER JOIN sys.columns col ON col.column_id = idxc.column_id and col.object_id = tab.object_id
INNER JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idxc.index_id = ius.index_id
SELECT distinct 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
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DROP TABLE #table_info
DROP TABLE #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
select distinct Count(*) as Quantity, lastname from Person.Person group by LastName having COUNT(*) > 2 order by 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
select distinct 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)
SET NOCOUNT ON
--EXEC sp_updatestats
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Will speed things up a bit
CREATE TABLE #table_info
(table_schema sysname NOT NULL
,table_name sysname NOT NULL
,column_name sysname NOT NULL
,data_type sysname NOT NULL
,origfillfactor varchar(10) null
,designed_length int NULL
,max_length int NULL
,distinct_count int NULL
,all_count int NOT NULL
,cardinality AS
CASE
WHEN distinct_count IS NULL THEN CAST(data_type AS varchar(7))
WHEN all_count = 0 THEN CAST('No rows' AS varchar(7))
ELSE CAST(
CAST(CAST(distinct_count AS decimal)/CAST(all_count AS decimal) AS decimal(18,4)) AS varchar(7))
END
)
DECLARE c CURSOR FAST_FORWARD FOR
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,
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
INNER JOIN sysindexes si
ON isc.table_name = OBJECT_NAME(si.id)
WHERE ist.table_type = 'base table'
AND ist.table_name not like 'dt%'
AND si.indid IN (0,1)
ORDER BY ist.table_schema, isc.table_name, isc.column_name
OPEN c
FETCH NEXT FROM 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 AS varchar(10)) + ', MAX(DATALENGTH([' + @column + ']))'
SET @sql = @sql + ', NULL' + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table_schema + '].[' + @table + ']'
END
ELSE
BEGIN
SET @sql = 'SELECT ''' + @table_schema + ''',''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''', ''' + @origfillfactor + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(LEN(CAST([' + @column + '] AS VARCHAR(8000))))'
SET @sql = @sql + ', COUNT(DISTINCT [' + @column + '])'
SET @sql = @sql + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table_schema + '].[' + @table + ']'
END
PRINT @sql
INSERT INTO #table_info (table_schema, table_name, column_name, data_type, origfillfactor, designed_length, max_length, distinct_count, all_count)
EXEC(@sql)
FETCH NEXT FROM 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
select tab.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
FROM sys.tables tab
INNER JOIN sys.indexes idx on tab.object_id = idx.object_id
INNER JOIN sys.index_columns idxc ON idxc.index_id = idx.index_id and idxc.object_id = tab.object_id
INNER JOIN sys.columns col ON col.column_id = idxc.column_id and col.object_id = tab.object_id
INNER JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idxc.index_id = ius.index_id
SELECT distinct 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
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DROP TABLE #table_info
DROP TABLE #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::
-- Distinct all card.. IDX_Name FF Type Seek Scan
-- 505868 4961852 0.1020 nci2_LastnameFirstName 90 NONCLUSTERED 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
create table t(col1 int)
declare @i int
set @i = 0
while @i < 1000
begin
insert into t(col1) values (@i)
set @i = @i + 1
end
set @i = 0
while @i < 1000
begin
insert into t(col1) values (5000)
set @i = @i + 1
end
select * from t
select count (*),col1 from t
group by col1 having COUNT (*) > 1
-- Let's create some fullscan statistics on the column in our table
create statistics t_col1 on t(col1) with fullscan
-- 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.
create procedure 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 foo 1
--back to slide deck
----------------------------------------------------------------------------------
USE AdventureWorks2008
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1
GO
exec myproc '1/1/06' -- est. # of rows 1
USE AdventureWorks2008
GO
CREATE PROCEDURE 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
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END
exec myproc2 '1/1/06' -- est. # of rows 9439
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post