The accidental DBA

Recently chatted with a colleague where they aren't quite big enough for a full time DBA, but they definitely needed a little push in the right direction.

He asked about his TLOG (which was rather large) and his indexes (which were rather fragmented).

I pushed him toward the same toolsets I pointed out in previous posts (Ola Hollangren for backups and index maintenance  and sp_Blitz for other odds and ends).  I gave him the following as well:

 

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
order by 9 desc

Which is nothing more than a snippet out of Ola Hollangren's indexing scripts.  When he ran them and was showing indexes which were 99% fragmented, I asked him if any shrinking had been going on :).. I then instructed him not to do that ever again.

Then I pointed him here and here.  The amount of things one can do with SQL Server out of the box by just setting up a couple of rudimentary jobs is very useful.  And the fact that much of this stuff is so readily available with so many examples makes having a happy SQL box in a short amount of time very doable.