Script Out My Custom Applied Indexes

When I apply my custom indexes or missing indexes, I put _fmcb_ in the name of the index. This way, I can run the below query and extract all of them out for a separate build script.

http://social.technet.microsoft.com/wiki/contents/articles/19598.how-to-generate-index-creation-scripts-for-all-tables-in-a-database-using-t-sql.aspx

SELECT  ' CREATE ' +
 CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
 I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
 I.name  + ' ON '  +
 Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
 KeyColumns + ' )  ' +
 ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
 ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( MAXDOP=0, ' +
 CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
 -- default value
 'SORT_IN_TEMPDB = OFF '  + ','  +
 CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
 CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +
 -- default value
 ' DROP_EXISTING = ON '  + ','  +
 -- default value
 ' ONLINE = OFF '  + ','  +
 CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
 CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
 DS.name + ' ] '  [CreateIndexScript]
 FROM sys.indexes I
 JOIN sys.tables T ON T.Object_id = I.Object_id
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
 JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 ORDER BY MAX(IC1.key_ordinal)
 FOR XML PATH('')), 1, 2, '') KeyColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
 LEFT JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 FOR XML PATH('')), 1, 2, '') IncludedColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp1
 WHERE IncludedColumns IS NOT NULL ) tmp2
 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
 WHERE i.name like '%fmcb%'
 --I.is_primary_key = 0 AND I.is_unique_constraint = 0
 --AND I.Object_id = object_id('Person.Address') --Comment for all tables
 -- AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Fragmentation in the T-Log: VLFs

Problems happen when you have fragmentation in the t-log. You can find this by running DBCC LOGINFO. This usually happens when autogrow kicks in and the database is set at the defaults… which are very small.

In the ERRORLOG, you start to see this:

Database x has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

More info here:

http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx

http://support.microsoft.com/kb/2653893

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

To get rid of it (from the above link):

If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn’t occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

1. Wait for an inactive time of day (ideally, it would be best to put the database into single user mode first) and then clear all transaction log activity through a regular transaction log backup. If you’re using the simple recovery model then you don’t need to do a log backup… Instead, just clear the transaction log by running a checkpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don’t know the logical filename of your transaction log use sp_helpfile to list all of your database files.

3. Alter the database to modify the transaction log file to the appropriate size – in one step

ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize
)

Free Space In Data Files

Quick way to find free space in SQL Server data files.

This helps find skewed and lumpy data distribution.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359

select
 a.FILEID,
 [FILE_SIZE_MB] =
 convert(decimal(12,2),round(a.size/128.000,2)),
 [SPACE_USED_MB] =
 convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
 convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = left(a.NAME,15),
 FILENAME = left(a.FILENAME,30)
 from
 dbo.sysfiles a

Missing Index Script

This script is from Bart Duncan (bartd) who was a Microsoft SQL Server Escalation Engineer for a long time.

His blog entry:

http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' 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,
 migs.*, mid.database_id, mid.[object_id]
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
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC