dm_os_wait_stats

-- http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
-- sql server 2K
--DBCC SQLPERF (N'waitstats')
--go
-- reset wait stats
--DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
go
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
  -- to avoid divide by zero erro right after wait stats cleanup
  -- very rare: no stats for user wait at all
  AND wait_time_ms>0
    )
SELECT
 [W1].RowNum,
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
-- percentage threshold
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95;
GO

Check/Disable/Enable FK

SELECT
 (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS STATUS
 ,OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME
 ,OBJECT_NAME(FKEYID) AS TABLE_NAME
 ,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME
 ,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME
 ,COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
 FROM SYSFOREIGNKEYS
 ORDER BY TABLE_NAME
    , CONSTRAINT_NAME
    , REFERENCED_TABLE_NAME
    , KEYNO


http://www.jasinskionline.com/TechnicalWiki/Disable-Every-Foreign-Key.ashx?AspxAutoDetectCookieSupport=1

For SQL 2005 and above:
-- This SQL generates a set of SQL statements to disable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] NOCHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
order by 1
-- This SQL generates a set of SQL statements to re-enable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] CHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 1
order by 1

Quick way to remove all data from a database (remove FK, truncate and add FK back)

The following script can be used to generate script for these 3 steps which can be used to remove all data from a database quickly and recover FK inclusing check option.


--
-- the following is intresting but has some limitation
-- 1) can not handle the case when FK has multiple columns
-- 2) can not handle the case when FK refers to unique index

SET NOCOUNT ON
 DECLARE @FkDefination TABLE(
  Id INT PRIMARY KEY IDENTITY(1, 1),
  FKConstraintName VARCHAR(255),
  FKConstraintTableSchema VARCHAR(255),
  FKConstraintTableName VARCHAR(255),
  FKConstraintColumnName VARCHAR(255),
  PKConstraintName VARCHAR(255),
  PKConstraintTableSchema VARCHAR(255),
  PKConstraintTableName VARCHAR(255),
  PKConstraintColumnName VARCHAR(255),
  FkIsEnabled TINYINT   
 )
 --
 -- get FK definitions and property
 INSERT INTO @FkDefination(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName, FkIsEnabled)
  (
  SELECT
 kcu.CONSTRAINT_NAME
 ,kcu.TABLE_SCHEMA
 ,kcu.TABLE_NAME
 ,kcu.COLUMN_NAME
 ,1-ObjectProperty(fk.constid,'CnstIsDisabled') as FkIsEnabled
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
     ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
  INNER JOIN sysobjects o on o.name=tc.CONSTRAINT_NAME
  INNER JOIN sysforeignkeys fk
  ON fk.constid=o.id 
   WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
  )
--
-- get PK and unique constrains name
 UPDATE @FkDefination
 SET PKConstraintName = UNIQUE_CONSTRAINT_NAME
     FROM @FkDefination tt
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tt.FKConstraintName = rc.CONSTRAINT_NAME
--
-- get reference schema and table name
-- this seems to be working with unique constraint and PK, but not unique index?
 UPDATE @FkDefination
 SET PKConstraintTableSchema  = TABLE_SCHEMA,
   PKConstraintTableName  = TABLE_NAME
  FROM @FkDefination fkd INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON fkd.PKConstraintName = tc.CONSTRAINT_NAME
--
-- get reference column name
 UPDATE @FkDefination
 SET PKConstraintColumnName = COLUMN_NAME
  FROM @FkDefination fkd
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON fkd.PKConstraintName = kcu.CONSTRAINT_NAME
--select * from @FkDefination
 -- step 1: drop constraint:
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']  DROP CONSTRAINT ' + FKConstraintName AS DropFkStatement
     FROM @FkDefination
 -- step 2: truncate tables
 SELECT 'TRUNCATE TABLE '+s.name+'.'+t.name as 'TruncateStatement'
   FROM sys.tables t inner join sys.schemas s
  on t.schema_id=s.schema_id
  WHERE t.type='U'
  ORDER BY s.name, t.name
 -- step 3:
 -- add fk back
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] WITH '+CASE WHEN FkIsEnabled=1 THEN 'CHECK' ELSE 'NOCHECK' END +' ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')' AS CreateFkStatement
     FROM @FkDefination
  WHERE PKConstraintTableName IS NOT NULL



reference:
http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints