Maintenance Checks on SQL Server Database
Failure to perform routine maintenance on a SQL Server database may result in poor performance that is outside the scope of a Total Office Manager support agreement.
In our support department, we first verify the Microsoft SQL Server configuration is done in accordance to the installation manual. While some things like allowing a port through a disabled firewall may seem trivial, it is necessary as outlined by Microsoft. It is much like the Microsoft feature to run an installation/application as Administrator even though the default Windows Administrator account is disabled. Typically during a configuration review, we find the Named Pipes and TCP/IP are not enabled on the server and the communication ports are not through the firewall on either the server or the workstations. Step one would be to correct these configuration errors. If after this is done and performance issues continue, we look a step further.
The next thing to review would be the maintenance of the database. Our recommendations are to ensure routine maintenance is being done on a regular basis. If your IT professional is not extremely knowledgeable in the functions needed for maintaining a Microsoft SQL Server database engine, please seek a qualified SQL DBA to perform these tasks. We do have a couple of referrals we can make if you would like their information.
Once the database maintenance has been performed, the next thing to review is what is occurring in the Microsoft SQL Server database during the time the performance issues are being reported. Microsoft does provide analysis tools to assist with troubleshooting Microsoft SQL Server performance. SQL Profiler is installed on your local server and may be utilized to troubleshoot activity within Microsoft SQL Server. Our office does not provide support services for this tool as it is a Microsoft product. A SQL DBA would also have tools to analyze performance as well. We have worked with DBAs in the past to pinpoint load heavy queries and development has addressed many of these.
The more finite information I have to troubleshoot the issues you are having the better. A mismanaged log file will cause a report not to run, will result in query timeouts, and cause performance issues within the software. I would like to have you do:
- Run a full backup of your Transaction Log.
- Ensure maintenance plans are run and in place for statistics.
- Work with your IT professional or SQL DBA to review the Microsoft SQL performance on the server.
- Enable a SQL Profile trace on your instance for a day and provide our office with the results for review.
You may review information within your database by checking for the following:
- Are statistics and indexes being properly maintained?
(object_id, stats_id) as LastStatsUpdate
Declare @fragmentation_minimum float
Set @page_count_minimum = 2
Set @fragmentation_minimum = 30
sys.objects.name as Table_Name,
sys.indexes.name as Index_Name,
avg_fragmentation_in_percent as frag,
page_count as page_count,
sys.dm_db_index_physical_stats.object_id as objectid,
sys.dm_db_index_physical_stats.index_id as indexid,
partition_number AS partitionnum
From sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
inner join sys.objects
on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
inner join sys.indexes
on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
Where avg_fragmentation_in_percent > @fragmentation_minimum
AND sys.dm_db_index_physical_stats.index_id > 0
AND page_count > @page_count_minimum
Order By page_count Desc
- Is the database receiving a full backup?
d AS 'Last Full Backup' ,
i AS 'Last Differential Backup' ,
l AS 'Last log Backup'
From ( Select db.name ,
From master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a on a.database_name = db.name
) as Sourcetable
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) as MostRecentBackup
- Is the log file being properly backed up?
If after checking each of the above and updating statistics, backing up/truncating the log file there are still performance issues, it is recommended to contact a SQL DBA to assist in further troubleshooting.