--DMV to view missing indexes
SELECT
*
FROM
sys.dm_db_missing_index_group_stats AS igs
JOIN
sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
JOIN
sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
-- Ensure a USE statement has been executed first.
SELECT [DatabaseName]
,[ObjectId]
,[ObjectName]
,[IndexId]
,[IndexDescription]
,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)]
,[lastupdated] AS [StatisticLastUpdated]
,[AvgFragmentationInPercent]
FROM (
SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName'
,OBJECT_ID AS ObjectId
,Object_Name(Object_id) AS ObjectName
,Index_ID AS IndexId
,Index_Type_Desc AS IndexDescription
,avg_record_size_in_bytes
,record_count
,STATS_DATE(object_id, index_id) AS 'lastupdated'
,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent'
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed')
WHERE OBJECT_ID IS NOT NULL
AND Avg_Fragmentation_In_Percent <> 0
) T
GROUP BY DatabaseName
,ObjectId
,ObjectName
,IndexId
,IndexDescription
,lastupdated
,AvgFragmentationInPercent
ORDER BY AvgFragmentationInPercent DESC
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY [Indexsize(KB)] desc
SELECT
OBJECT_NAME(ix.OBJECT_ID) AS TableName,
ix.name AS IndexName,
ixs.index_type_desc AS IndexType,
ixs.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs
INNER JOIN
sys.indexes ix ON ix.object_id = ixs.object_id AND ixs.index_id = ixs.index_id
WHERE
ixs.avg_fragmentation_in_percent > 30
ORDER BY
ixs.avg_fragmentation_in_percent DESC
--Stored procedure to display current locks/process
exec sp_lock
--Stored procedure to display current activity/process
exec sp_who2
--DMV to view locking information
SELECT * FROM sys.dm_tran_locks
GO
--DMV to view blocked transactions
SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended'
GO
--Trace flag to log deadlocks
DBCC TRACEON (1222, -1)
GO
select name, definition, type_desc FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
where type_desc like '%function%'
ORDER BY Name
--OR
SELECT *
FROM sys.objects
WHERE RIGHT(type_desc, 8) = 'FUNCTION'
--DMV to View Index fragmentation
SELECT
OBJECT_SCHEMA_NAME(i.object_id),
OBJECT_NAME(i.object_id),
*
FROM
sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) ips
JOIN
sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
--sys.dm_os_ = SQL Server OS Information
SELECT * FROM sys.dm_os_wait_stats
--sys.dm_io_ = i/O Information
SELECT * FROM sys.dm_io_pending_io_requests
--sys.dm_exec_ = Session & Query Information
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests
--sys.dm_db__ = Database & Index Information
SELECT * FROM sys.dm_db_task_space_usage
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
/* Informational Commands */
DBCC SQLPERF(LOGSPACE) --View transaction log size info
GO
DBCC SHOW_STATISTICS('Sales.SalesOrderDetail', 'IX_SalesOrderDetail_ProductID') --View query stats for table or indexed view object
GO
DBCC USEROPTIONS --View current connections user set options
GO
/* Maintenance Commands */
DBCC DBREINDEX('Person.Person') --Rebuilds indexes on a table
GO
DBCC CLEANTABLE(AdventureWorks2012, 'Production.ProductDescription') --Reclaims unused spaced from tables
GO
/* Validation Commands */
DBCC CHECKTABLE('Person.Person') --Integrity check of table pages/structure
GO
DBCC CHECKFILEGROUP --Integrity check of filegroup structure/allocation
GO
DBCC CHECKDB(AdventureWorks2012) --Integrity check of database objects
GO
/* Misc. Commands */
DBCC HELP('CHECKDB') --Syntax information for DBCC statements.. use '?' for list
GO
DBCC TRACEON(610) --Turn on trace flag 610 (minimally logged inserts into indexed tables)
GO
DBCC TRACEOFF(610) --Turn off trace flag 610
GO
DBCC TRACESTATUS(610) --Check trace flag status
GO
