List all heap tables of a given database

-- List all heap tables 
-- Try this with tempdb and see how many temp tables or table variables have been created there, you will be amazed with large olap databases.  
SELECT s.name + '.' + t.name AS TableName

FROM sys.tables t INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id INNER JOIN sys.indexes i

ON t.object_id = i.object_id

AND i.type = 0 -- = Heap

ORDER BY TableName

Database external fragmentation overview

use MyDatabase




go


select top 50

avg_fragmentation_in_percent*s.page_count/100.00 fragmented_page_count

,o.name as TableName

,i.name as IndexName

,s.partition_number

,s.avg_fragmentation_in_percent

,s.page_count

,s.index_depth

,s.index_level

,s.index_type_desc

,d.name as DataBaseName

,s.*

from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') s

--sys.dm_db_index_physical_stats(db_id(), object_id('MyTableInCurrentDB'), null, null, 'limited') s

inner join sys.databases d on d.database_id=s.database_id

inner join sys.objects o on o.object_id=s.object_id

left join sys.indexes i on i.object_id=s.object_id and i.index_id=s.index_id

where -- have large external fragmentation

s.avg_fragmentation_in_percent>10

-- ignore small table/index

and s.page_count>1

-- ignore heap table

and s.index_id>0

order by avg_fragmentation_in_percent*s.page_count desc

Checking SQL server stats

-- use dm_db_stats_properties to get stats info
-- for SQL 2012 and above
SELECT
 OBJECT_NAME([sp].[object_id]) AS "Table"
 ,[sp].[stats_id] AS "Statistic ID"
 ,[s].[name] AS "Statistic"
 ,[sp].[rows_sampled]
 ,[sp].[modification_counter] AS "Modifications"
 ,[sp].[last_updated] AS "Last Updated"
 ,[sp].[rows]
 ,[sp].[unfiltered_rows]
 FROM [sys].[stats] AS [s]
   OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
 --WHERE [s].[object_id] = OBJECT_ID(N'Sales.TestSalesOrderDetail');
 ORDER BY 1,2

--
-- quick way to check which stats objects are used by query

DBCC FREEPROCCACHE

SELECT 

    p.Name,

    total_quantity = SUM(th.Quantity)

FROM AdventureWorks.Production.Product AS p

JOIN AdventureWorks.Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

WHERE

    th.ActualCost >= $5.00

    AND p.Color = N'Red'

GROUP BY

    p.Name

ORDER BY

    p.Name

OPTION

(

    QUERYTRACEON 3604,

    QUERYTRACEON 9292,

    QUERYTRACEON 9204

)

--
-- checking auto created stats which are never updated or updated before 1 week
DECLARE @NumOfDays int, @StateCheckDateTimeDeadLine datetime
SET @NumOfDays=7
SET @StateCheckDateTimeDeadLine=DATEADD(hour, -24*@NumOfDays, getdate())
--SELECT @StateCheckDateTimeDeadLine

SELECT object_name(object_id) as TableName
,name AS StatisticsName
,STATS_DATE(object_id, stats_id) AS StatisticsUpdatDate
,auto_created AS IsAutoCreated
FROM sys.stats
WHERE auto_created=1
-- never updated or updated before dead line
AND (STATS_DATE(object_id, stats_id) IS NULL
OR STATS_DATE(object_id, stats_id)<=@StateCheckDateTimeDeadLine)
order by 1,2

-- after this check detail of each stat
DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

DBCC SHOW_STATISTICS ("MyTable", MyPrimayKey);
DBCC SHOW_STATISTICS ("MyTable", MyStatsName);

reference:
1)
http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx
2)
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/

Sizing database/partition/partitioned table with [dm_db_partition_stats]

--
-- whole database size
SELECT   
        (SUM(reserved_page_count)*8192)/1024000 AS [TotalSpace(MB)]
        ,(SUM([used_page_count])*8192)/1024000 AS [UsedSpace(MB)]
        ,(SUM([row_count])) AS [RowCount]
        FROM sys.dm_db_partition_stats

--
-- size of each partition scheme       
select    s.name as [partition_scheme]
        ,sum(ps.[reserved_page_count])*8192/10240000  AS [TotalSpace(MB)]
        ,sum(ps.[used_page_count])*8192/10240000 AS [UsedSpace(MB)]
        ,sum([row_count]) AS [RowCount]
    from    sys.indexes i inner join sys.partition_schemes s
            on i.data_space_id = s.data_space_id 
            inner join
                (
                SELECT   
                    [object_id]
                    ,[index_id]
                    ,sum([used_page_count]) as [used_page_count]
                    ,sum([reserved_page_count]) as [reserved_page_count]
                    ,sum([row_count]) as [row_count]
                    FROM [sys].[dm_db_partition_stats]
                    group by [object_id],[index_id]
                ) ps
            on ps.object_id=i.object_id and ps.index_id=i.index_id
    group by s.name
    order by 1,2

--
-- size of partitioned table
select
        object_schema_name(i.object_id) as [schema]
        ,object_name(i.object_id) as [object]
        ,i.name as [index]
        ,ps.[used_page_count]
        ,ps.[reserved_page_count]
        ,ps.[row_count]
        ,s.name as [partition_scheme]
        ,f.name as [patition_function]
    from    sys.indexes i inner join sys.partition_schemes s
            on i.data_space_id = s.data_space_id 
            inner join sys.partition_functions f
            on f.function_id = s.function_id
            inner join
                (
                SELECT   
                    [object_id]
                    ,[index_id]
                    ,sum([used_page_count]) as [used_page_count]
                    ,sum([reserved_page_count]) as [reserved_page_count]
                    ,sum([row_count]) as [row_count]
                    FROM [sys].[dm_db_partition_stats]
                    group by [object_id],[index_id]
                ) ps
            on ps.object_id=i.object_id and ps.index_id=i.index_id
    order by 1,2,3

Inmon vs Kimball – Architecture Comparison and Data Valut Architect

1) A good slide show for reference:
http://www.slideshare.net/kgraziano/introduction-to-data-vault-modeling#btnNext

Inmon states that the data warehouse is:

Subject-oriented

The data in the data warehouse is organized so that all the data elements relating to the same real-world event or object are linked together.

Non-volatile

Data in the data warehouse are never over-written or deleted — once committed, the data are static, read-only, and retained for future reporting.

Integrated

The data warehouse contains data from most or all of an organization's operational systems and these data are made consistent.

Time-variant

For An operational system, the stored data contains the current value.

Kimball: DW is where we publish used data.

Data Valut by Dan:
Hub: Business Key
Link: represent 3nf many to many relation for Business key
Satellite: description for business key

2)
From:

http://oracledwbi.wordpress.com/2010/03/25/inmon-vs-kimball-architecture-comparison/

	Inmon – Hub & Spoke	Kimball – Bus Architecture
Methodology & Architecture
Overall approach	Top-down	Bottom-up
Architectural structure	Enterprise wide (atomic) data warehouse “feeds” departmental databases	Data marts model a single business process; enterprise consistency achieved through data bus and conformed dimensions
Complexity of the method	Quite complex	Fairly simple
Comparison with established development methodologies	Derived from the spiral methodology	Four-step process; a departure from RDBMS methods
Discussion of physical design	Fairly thorough	Fairly light
Scalability	Growing scope and changing requirements are critical	Need to adapt to highly volatile needs within a limited scope
Application	Strategic solution	Tactical solution
Information interdependence	Supported	Supported
Deployment cost	Higher start-up costs, with lower subsequent project development costs	Lower start-up costs, with each subsequent project costing about the same
Time to deploy	Longer start-up time	Quick start-up time
Development skills required	Larger team(s) of specialists	Small teams of generalists
Persistency of data	High rate of change from source systems	Source systems are relatively stable
Data modelling
Data orientation	Subject or data-driven	Process oriented
Tools	Traditional (ERDs, DISs)	Dimensional modelling
End-user accessibility	Low	High
Philosophy
Primary audience	IT professionals	End users
Place in the organisation	Corporate wide facility	Growth subject to departmental demand
Objective	Deliver a sound technical solution based on proven database methods and technologies	Deliver a solution that makes it easy for end users to directly query the data and still get reasonable response times

rebuild index and update stat for Sql server

USE DatabaseName
 GO
 EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
 GO
 EXEC sp_updatestats
 GO

Parition Info (which tables are partitioned?)

--
-- ref: http://www.sqlservercurry.com/2009/08/find-partition-information-in-sql.html

SELECT DISTINCT
    OBJECT_NAME(part.object_id) as TableName
    ,psch.name as PartitionScheme
    ,idx.name as IndexName
    ,pfun.name as FunctionName
    ,idx.type_desc as IndexType
    --,part.object_id as ObjectID
    FROM    sys.partitions part    INNER JOIN sys.indexes idx
            ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
            INNER JOIN sys.data_spaces dsp
            ON idx.data_space_id = dsp.data_space_id
            INNER JOIN sys.partition_schemes psch
            ON dsp.data_space_id = psch.data_space_id
            INNER JOIN sys.partition_functions pfun
            ON psch.function_id = pfun.function_id
    --WHERE    idx.is_primary_key=1
    ORDER BY 1,2

Some useful blocking (or i/o blocking) useful query

use tempdb
--DBCC SQLPERF (LOGSPACE);
--DBCC SQLPERF (WAITSTATS)
--DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
--CHECKPOINT
--DBCC FREEPROCCACHE
--DBCC DROPCLEANBUFFERS
--sp_whoisactive
--sp_who
--sp_who2
--dbcc opentran
--
go
--
-- real time ddl bottleneck
select  
    session_id
    ,wait_type
    ,wait_duration_ms
    ,resource_description
    from    sys.dm_os_waiting_tasks
    where    wait_type like 'PAGE%LATCH_%'
            and resource_description like '2:%'
    --sp_whoisactive
go
sp_spaceused
go
--
-- check dirty pages
SELECT    
    db_name(database_id) AS 'DatabaseName'
    ,count(page_id) AS 'DirtyPages'
    ,count(page_id)*8/1024 AS 'TotalSize(MB)'
    FROM    sys.dm_os_buffer_descriptors
    WHERE    is_modified =1
    GROUP BY db_name(database_id)
    ORDER BY count(page_id) DESC
go
--
-- temp db i/o wait stat
-- switch to any database and run stat for that db
SELECT
    DB_NAME(fs.database_id)    as    [Database]
    ,f.[Name]                  as    [LogicName]
    ,f.[filename]              as    [FileName]
    ,fs.[size_on_disk_bytes] / 1024 /1024  as    [Size(MB)]
    -- this seems to be off! data could be written by dropped later
    -- this is NOT space used
    ,fs.[num_of_bytes_written]/1024/1024  as    [TotalWrite(MB)]
    ,fs.[io_stall_write_ms]/fs.[num_of_writes] as [AverageWriteDelay(ms)]
    ,1024.00*1024*fs.[io_stall_write_ms]/fs.[num_of_bytes_written] as [AverageWrite Delay(ms/MB)]
    ,fs.[num_of_bytes_read]/1024/1024  as    [TotalRead(MB)]
    ,fs.[io_stall_read_ms]/fs.[num_of_reads] as [AverageReadDelay(ms)]
    ,1024.00*1024*fs.[io_stall_read_ms]/fs.[num_of_bytes_read] as [AverageReadDelay(ms/MB)]
    --,fs.*
    FROM    -- 2(tempdb database id), NULL(all the files related to tempdb)
            --sys.dm_io_virtual_file_stats(2, NULL) fs inner join sys.sysfiles f
            -- current database
            sys.dm_io_virtual_file_stats(db_id(), NULL) fs inner join sys.sysfiles f
            on fs.file_id = f.fileid
    WHERE [num_of_writes]>0 AND [num_of_reads]>0
    order by 3,6
go
-- reset all counters to 0
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
--
-- i/o related wait stat
select
    ([signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageSignalWait
    ,([wait_time_ms]-[signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageOprationWait
    ,*
    from sys.dm_os_wait_stats
    where    (wait_type like 'PAGEIO%'
            or wait_type like '%IO_COMPLETION'
            or wait_type like 'DISKIO%'
            or wait_type like 'BACKUPIO%'
            or wait_type like 'WRITE%'
            ) and
            waiting_tasks_count>0
    order by wait_time_ms desc
go
--
-- temp table list
select
    crdate
    ,name
    ,refdate
    ,*
    from    tempdb.dbo.sysobjects  with (nolock)
    where    type='U'
            and name like '#%'
    order by 1 asc
go
SELECT
    [create_date]
    ,[modify_date]
    ,[name]
    ,[object_id]
    ,[principal_id]
    ,[schema_id]
    ,[parent_object_id]
    ,[lock_escalation]
    ,[lock_escalation_desc]
    ,[is_tracked_by_cdc]
    /*
    ,[type]
    ,[type_desc]
    ,[is_published]
    ,[is_schema_published]
    ,[lob_data_space_id]
    ,[filestream_data_space_id]
    ,[max_column_id_used]
    ,[lock_on_bulk_load]
    ,[uses_ansi_nulls]
    ,[is_replicated]
    ,[has_replication_filter]
    ,[is_merge_published]
    ,[is_sync_tran_subscribed]
    ,[has_unchecked_assembly_data]
    ,[text_in_row_limit]
    ,[large_value_types_out_of_row]
    */
    FROM    [tempdb].[sys].[tables]
    WHERE type='U' and is_ms_shipped=0
    order by 1,2
go
--
-- get session for temp table

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces 
WHERE is_default = 1;

SELECT 
    o.name, 
    o.OBJECT_ID,
    o.create_date,
    gt.NTUserName,
    gt.HostName,
    gt.SPID,
    gt.DatabaseName,
    gt.TEXTData
    FROM    sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
            JOIN tempdb.sys.objects AS o 
            ON gt.ObjectID = o.OBJECT_ID
    WHERE    gt.DatabaseID = 2
            AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
            --AND o.create_date >= DATEADD(ms, -100, gt.StartTime) 
            --AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
            and o.name='#53FF5DCD'
            --#023D5A04
    ORDER BY o.create_date
go
select
    ([signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageSignalWait
    ,([wait_time_ms]-[signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageOprationWait
    ,*
    from sys.dm_os_wait_stats
    where    waiting_tasks_count>0
    order by [wait_time_ms]
    desc 
go
--
-- what are we waiting for recently?
select * into #BufferWaitStats from sys.dm_os_wait_stats

-- wait ten seconds or so
select ws.wait_type,
      ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count,
      ws.wait_time_ms - bws.wait_time_ms as wait_time_ms,
      ws.max_wait_time_ms,
      ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
      on ws.wait_type = bws.wait_type
order by wait_time_ms desc

--clean up
drop table #BufferWaitStats
go


select 
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count
from 
    sys.dm_os_schedulers
where 
    scheduler_id < 255


select top 50 
    sum(qs.total_worker_time) as total_cpu_time, 
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements, 
    qs.plan_handle 
from 
    sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

sp_whoisactive

select top 50
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements,
    qs.plan_handle
from
    sys.dm_exec_query_stats qs inner join dm_exec_text_query_plan qp on
    qs.plan_handle=qp.plan_handle
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

go

--
-- monitor real time i/o stall
select
    database_id,
    db_name(database_id) as databaseName,
    file_id,
    io_type,
    io_stall,
    io_pending_ms_ticks,
    scheduler_address
from    sys.dm_io_virtual_file_stats(NULL, NULL) fs inner join
        sys.dm_io_pending_io_requests as ir on
        fs.file_handle = ir.io_handle
where    io_pending_ms_ticks>0
order by io_pending_ms_ticks desc


go
sp_helpfile


sp_whoisactive @get_plans=1


select * from sys.dm_io_pending_io_requests