SQL Server Maintenance

Brad Vossler

7 years ago

SQL Server Maintenance

Keeping SQL Server running in top condition requires some work. There is no silver bullet to make sure things are always perfect. Instead, SQL Server Administrators are tasked to make sure SQL Server is working up to expectations. Let’s face it, SQL Server Maintenance is an important part of keeping everyone happy!

Tips and Tricks

We won’t try to tell you we know every way possible to make SQL Server work at its best. But we have acquired some knowledge, tips and tricks along the way that will help. We will break these into two general categories. First, we will go over some generalized tips that will work on most any SQL Server. These are not specific to docMgt but can make docMgt run faster as well as other applications.Second, we will discuss some tips and tricks that can help docMgt specifically.

General Tips

These tips were not created or discovered by us. They are a culmination of concepts, ideas and tips that have been used by many SQL administrators over the years. Whenever possible we will give you links to the original location of the tip so you can get more detailed information if you desire.

Rebuild Fragmented Indexes

Probably the best thing you can do as an admin is to manage SQL Server’s indexes. Over time the indexes will become fragmented and less efficient. There is no way to stop that – it is just a natural byproduct of the way SQL Server works. However, you can rebuild the indexes to make them fresh and efficient once again.

Here is a script you can run to see the fragmentation of all your indexes. Run this and it will show any indexes that are fragmented more than 20%. Just change YOURDBNAMEHERE to your database name.

SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
 DB_ID(N'YOURDBNAMEHERE')
 , NULL
 , NULL
 , NULL
 , NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 20

Keep in mind, however, that a large fragmentation by itself is not bad. If the table/index is small then the negative effects of fragmentation are not as big as they may seem. This article [icon name=”external-link-square” class=”” unprefixed_class=””] explains a little more about fragmentation.

You could go one at a time and rebuild the indexes manually form the admin tool. Or if you script all the indexes yourself so you can delete and recreate them from a master script. Or, as is our preference, you can leverage a script like the one below to rebuild all your indexes at once. The nice thing about this script is that as the tables and indexes change you do not have to update this script.

 
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
 
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' 
OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
    DBCC DBREINDEX(@TableName,' ',90) 
    FETCH NEXT FROM TableCursor INTO @TableName 
END 

CLOSE TableCursor 
DEALLOCATE TableCursor

Source: https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754.

Clean Out Unnecessary Data

One thing everyone should think about is cleaning out data you don’t need. Some system generate logs or tracking information that can be backed up into a secondary database or just deleted. If it is possible to clean out some old data then this script can help. The following script will show you which tables in your database are taking up the most space. You get a list of all tables and their size in rows and in KBs/MBs and more.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Source: https://stackoverflow.com/a/7892349/4283480

Don’t Shrink Your Database Files

I know that most SQL administrators probably still think about shrinking their database files after large delete operations. It sounds logical – “I now have XX MBs of unused space. I should give it back to the OS.” I am guilty of this thinking. I can’t tell you how many SQL databases on which I have run the shrink operation. However, the more I read more about what happens during the shrink operation the more I dislike the practice unless I run into an extreme case. In a nutshell, shrinking your database actually makes things worse. Here are a few articles that explain it in more detail:

Take Care of your Transaction Log

Transaction logs are a wonderful feature of SQL server. They allow you to restore your data back to a point in time. The quickest way to explain transaction logs is to remember that every time any data changes in the database (insert, update, delete), the transaction log makes a record of that change. The log is a sequential file that records all transactions. It will just keep writing and writing until it is told to empty itself.

Some administrators will look at the transaction log size and try to truncate it when it gets too big. While there are cases where you may need to do this, they are usually not necessary. If you manage your database correctly the transaction log takes care of itself. This Stack Exchange posting by Mike Walsh [icon name=”external-link-square” class=”” unprefixed_class=””] explains in great detail the transaction log and how to manage it. I won’t try to recount all that he goes through in his post but, in short, if you are running in Full Recovery mode (the default for SQL Server editions other than Express) then you must make transaction log backups. Transaction log backups will trigger the transaction log to be cleared out. If you don’t care about point-in-time restore and only want to do full backups nightly or weekly then you should consider changing your database to Simple Mode. In Simple Mode the transaction log is not used so it won’t grow.

docMgt Specific Tips

These tips were adapted to work specifically for the docMgt database. There are certain tables that grow and can get quite large. Keeping those cleaned out can keep performance up while keeping database size down.

Audit Table

The Audit table holds all the information about what changed, when and by whom. Any time a Record is added or a data value is changed or a document is uploaded that table adds new rows. If you can live without older audit information then you can clean out anything older that N months and save some space. This query will clean out anything older than 12 months.

DELETE From Audit Where Date < DateAdd(Month, -12, getdate());

Logins Table

The Logins table records each login for each user. Over time this can grow albeit at to a lesser degree than the Audit table. If you can live without older login information then you can clean out anything older than N months. This query will clean out anything older than 6 months.

DELETE From Logins Where LoginDate < DateAdd(Month, -6, getdate())

Deleting large amounts of data will cause your transaction log to grow quickly. Review the ‘Take Care of your Transaction Log’ section above to make sure you know how to deal with that.

Summary

Before you follow any SQL Server maintenance tips at all you MUST review your needs and your capabilities. Read the links above and do more research to understand what you are doing before making any changes. There are times when it is wise to hire an expert to help get things set up properly. We encourage you to take control of your database. Remember, just because you are storing data for a specific vendor in a particular SQL Server does not mean it takes care of itself.