2.Script to find missing indexes in all databases of a server using DMVs
USE msdb
GO
--Deletes backup history only for all history older than date below
sp_delete_backuphistory '1/1/08';
GO
--Deletes job history only for all history older than the date below
sp_purge_jobhistory @oldest_date = '1/1/08';
GO
--Deletes maintenace plan history only for all history order than date below
sp_maintplan_delete_log NULL, NULL, '1/1/08';
GO
-------------------------------------------------------------------------
--Script to find missing indexes in all databases of a server using DMVs
--Data is based on when the query optimizer determines that a query is missing
--Data is reset each time SQL Server is restarted
--Query may suggest duplicate indexes, or offer incomplete advice, so you need to carefully evaluate each recommendation
--Many variations of this script exist on the Internet
--Recommend using Profiler Trace and DTA instead.
--SQL Server 2005 Performance Dashboard also provides this same data, but easier to read
--SQL Server 2008 Data Collector also provides this same data, but easier to read
--Missing index recommendations also appear in 2008 graphical query exectution plans
--Run this query in order to generate demo data for missing index
USE AdventureWorks2012;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO
--Run script to find missing indexes
USE AdventureWorks2012
GO
SELECT migs.avg_user_impact AS AvgPercentageBenefit,
'CREATE INDEX missing_index_' +
CONVERT (VARCHAR, mig.index_group_handle) +
'_' +
CONVERT (VARCHAR, mid.index_handle) +
' ON ' + mid.statement + ' (' +
ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND
mid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END +
ISNULL(mid.inequality_columns,
'') + ')' +
ISNULL(' INCLUDE (' +
mid.included_columns +
')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
-------------------------------------------------------------------------
--Script to Identify Unused indexes using a DMV
--Data is reset each time SQL Server is restarted.
--Be sure that server has been up for some time before running this query
--Many variations of this script exist on the Internet
USE [AdventureWorks2012]
GO
SELECT OBJECT_NAME(i.object_id) AS ObjectName,
i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND
i.index_id = s.index_id AND
s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id,
'IsIndexable') = 1 AND
OBJECTPROPERTY(i.object_id,
'IsIndexed') = 1 AND
s.index_id IS NULL -- and dm_db_index_usage_stats has no reference to this index
OR
(
s.user_updates > 0 AND
s.user_seeks = 0 AND
s.user_scans = 0 AND
s.user_lookups = 0
) -- index is being updated, but not used by seeks/scans/lookups
ORDER BY OBJECT_NAME(i.object_id) ASC
-------------------------------------------------------------------------
--Query identifies duplicate indexes
--http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx
USE AdventureWorks2012
GO
--Index to identify duplicate indexes
WITH indexcols AS
(
SELECT object_id AS id, index_id AS indid, name,
(SELECT CASE keyno WHEN 0 THEN NULL ELSE colid END AS [data()]
FROM sys.sysindexkeys AS k
WHERE k.id = i.object_id
AND k.indid = i.index_id
ORDER BY keyno, colid
FOR XML PATH('')) AS cols,
(SELECT CASE keyno WHEN 0 THEN colid ELSE NULL END AS [data()]
FROM sys.sysindexkeys AS k
WHERE k.id = i.object_id
AND k.indid = i.index_id
ORDER BY colid
FOR XML PATH('')) AS inc
FROM sys.indexes AS i
)
SELECT
object_schema_name(c1.id) + '.' + OBJECT_NAME(c1.id) AS 'table',
c1.name AS 'index',
c2.name AS 'exactduplicate'
FROM indexcols AS c1
JOIN indexcols AS c2
ON c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;
-------------------------------------------------------------------------
--Display Fragmentation Levels for all indexes for a database
--Ordered by Average Fragmentation
--This DMF can take some time to run on large databases unless you use 'limited' option
USE AdventureWorks2012
GO
SELECT d.name,
s.OBJECT_ID,
s.index_id,
s.index_type_desc,
s.avg_fragmentation_in_percent,
s.avg_page_space_used_in_percent
FROM sys.databases AS d
INNER JOIN sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')
AS s ON d.database_id = s.database_id
WHERE d.NAME = 'AdventureWorks2012'
ORDER BY s.avg_fragmentation_in_percent DESC
-----------------------------------------------------------------------------
--Code to identify what indexes, in a database, need to be defragmented, and then to rebuild
--or reorganize them based on their level of fragmentation
--Code example below is from Books Online: "sys.dm_db_index_physical_stats (Transact-SQL)"
--Also see this code sample: http://sqlfool.com/category/sql-scripts/
--Another code example is: http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/
USE AdventureWorks2012
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
--------------------------------------------------------------------------------------------------
--How to update only those statistics in a database
--that need updating using the default sampling rate
--and the ALL option, which updates both indexes and columns.
--The default sampling rate is determined by the query optimizer.
USE AdventureWorks2012
GO
EXEC sp_updatestats
GO
--If you use UPDATE STATISTICS, you must go table by table
--But this command offers more options
USE [AdventureWorks2012]
GO
UPDATE STATISTICS [dbo].[DatabaseLog] WITH FULLSCAN
---------------------------------------------------------------------------------------------------
--Sample DBCC CHECKDB code
DBCC CHECKDB ('AdventureWorks2012')
WITH NO_INFOMSGS, ALL_ERRORMSGS
---------------------------------------------------------------------------------------------------
|