Thought I’d write a quick post about managing error logs within SQL Server. Good error log management is essential for investigating issues that occur in SQL Server. The last thing you want is a very large error log file that takes ages to open when you are trying to determine the cause of a problem.

I always change the maximum number of error logs to 30 (at a minimum) and then recycle the error log each night. This way I can keep a month’s worth of logs in easily manageable portions (this does depend on the system but one day’s worth of logs in each file is generally OK).

So to change the maximum number of error logs, go to Management > SQL Server Logs and right-click and select Configure. Change the maximum number of error logs (I changed it to 30) and then hit the script button which will give you the following:-

USE [master];
GO

EXEC xp_instance_regwrite 
	N'HKEY_LOCAL_MACHINE', 
	N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	N'NumErrorLogs', 
	REG_DWORD, 
	30;
GO

Microsoft has provided the stored procedure sys.sp_cycle_errorlog so this can be setup to run nightly as a SQL Server Agent job. However I have found that the following error can occur on busy systems (running SQL Server 2005 I’m afraid):-

“Unable to cycle error log file from ‘…\ERRORLOG’ to …\ERRORLOG.1′ due to OS error ’32(The process cannot access the file because it is being used by another process.)”

When this happens the old log is archived but the new log is not initialised, meaning that no information will be written to the log until it is recycled manually (once you’re in the office). Not good, especially if an issue has occurred in that time.

So to try and combat this, I created the following stored procedure to try to recycle the error log a maximum of three times if an error occurs:-

CREATE PROCEDURE [dbo].[RecycleErrorLog]
AS

DECLARE @Tries TINYINT
SET @Tries = 1
WHILE @Tries <= 3

BEGIN

  BEGIN TRY
		EXEC master.sys.sp_cycle_errorlog
		BREAK
  END TRY

  BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber
		SET @Tries = @Tries + 1
		CONTINUE
  END CATCH;

END;

GO

I set the maximum number of re-tries to 3 as if the job fails three times in a row I would say that there is something more severe going on which would need to be investigated.

So I’m feeling pretty clever, the stored procedure is running fine until one day:-

“Unable to cycle error log file from ‘…\ERRORLOG’ to …\ERRORLOG.1′ due to OS error ’32(The process cannot access the file because it is being used by another process.)”

Ah, it’s no problem I tell myself. The error will be generated but the procedure will re-run and recycle the SQL Server Error Log. So…..I stroll into work, check the logs and…..they haven’t recycled. Not only that, nothing has been recorded since the attempt to cycle, I’m back to where I started!

I check the SQL Server Agent Job’s history and the job is telling me that it has run successfully. It seems that the error is not returned to the procedure, there’s a record in the SQL Server Error Log:-

“Attempting to cycle error log. This is an informational message only; no user action is required.”

I look back at the original error, there was an important part that I missed!

“…due to OS error ’32…”

It’s an OS error, it’s not going to be recorded in SQL. The only place the error is recorded is in the Application Event Log. So it looks like I’m going to have to run the stored procedure and then check the Application Event Log for errors. The best way I can think of doing this is using powershell, so here’s the script I wrote:-

#Set variable values to zero
$MaximumRetries = 0
$ErrorChecker = 0

#Open connection to SQL Server
$SQLServer = "MYSERVER"
$SQLDBName = "master"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True"
$SqlConnection.Open()

$SQLCmd = new-Object System.Data.SqlClient.SqlCommand("sp_cycle_errorlog", $SQLConnection) 
$SQLCmd.CommandType = [System.Data.CommandType]'StoredProcedure' 

#Run sp_cycle_errorlog
$SQLCmd.ExecuteReader()

#Check application log for errors
$datetime = (Get-Date).AddSeconds(-5)
$events = get-eventlog application -EntryType Error -After $datetime | where-Object {$_.source -like "MSSQL*"}	

do
{
	#If the application log has an error
	if ($events)
		{	
			#Set maximum retries to 3
			if ($MaximumRetries -lt 4)
				{	
					#Re-execute stored procedure
					$sqlCmd.ExecuteReader()		
					
					#Check application log
					$datetime = (Get-Date).AddSeconds(-5)
					$events = get-eventlog application -EntryType Error -After $datetime | where-Object {$_.source -like "MSSQL*"}	
			
					$MaximumRetries = $MaximumRetries + 1		
				}
	#If the application log has no errors, close connection			
	elseif (!$events)
		{
			$SqlConnection.Close()
			$ErrorChecker = 1
		}
		}	
}
while ($ErrorChecker = 0)

I saved this as a .ps1 file and then created a SQL Server Agent Job to run it. I still get alerts for the error but each time the script has done its job and re-run.

Has anyone else seen this behaviour? If so, I’d be interested in hearing how you dealt with it, my email is dbafromthecold@gmail.com

So you’ve setup a bunch of indexes in your database to tune the queries coming in. They’re all running much faster than before and you’re leaning back in your chair feeling pretty pleased with yourself. You’re done right?

Well…no. As data gets inserted into and deleted from tables within a database, the indexes on the tables become fragmented. Fragmentation is defined as:-

“Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.”

But does it matter that the logical ordering does not match the physical ordering? Microsoft published a white paper here which showed that with increased fragmentation, the time taken for each read from disk increased whilst the average size of each read decreased. I know it was performed using SQL Server 2000 but it’s still relevant. They summarise the results as:-

“The workload performance increase realized in the small-scale environment ranged from 60 percent at the low level of fragmentation to more than 460 percent at the highest level of fragmentation. The workload performance increased realized for the large-scale environment ranged from 13 percent at the low fragmentation level to 40 percent at the medium fragmentation level”

Note – This is the summary of the DSS workload test, complex queries against multiple levels of fragmentation

To summarise:-
Small-scale environment
After defragmenting the indexes an increase in performance of 60% was gained when there was a low level of fragmentation and there was a gain of 460% at a high level of fragmentation
Large-scale environment
After defragmenting the indexes an increase in performance of 13% was gained when there was a low level of fragmentation and there was a gain of 40% at a high level of fragmentation

Wow, removing the fragmentation in the indexes gave a performance increase between 13% and 460% (across the two environments).

The paper also discusses why the performance in the large-scale environment was less affected by fragmentation…basically the environment had a better IO subsystem…which means that throwing money at index fragmentation can appear to solve the problem but you are really just papering over the cracks.

But, what DBA doesn’t like improving the performance of a system without having to spend any money? “Look, I’ve improved the performance by X%!”. (cue smug face)

So a plan to reduce the fragmentation of the indexes needs to be implemented. But how do you approach this? Rebuild every index nightly? Will your system allow you to do that? What if you are running Standard Edition and you cannot rebuild indexes online?

The way I approach index maintenance is to customise for each database I manage. To do this, the indexes that require either rebuilds or reorganisation need identifying. Microsoft have provided the DMV sys.dm_db_index_physical_stats which will allow us to do so:-

SELECT
	[schemas].name AS [SchemaName],
	[objects].name AS [TableName], 
	[indexes].name AS [IndexName],
	[dm_db_index_physical_stats].index_depth,
	[dm_db_index_physical_stats].avg_fragmentation_in_percent,
	[dm_db_index_physical_stats].fragment_count,
	[dm_db_index_physical_stats].avg_fragment_size_in_pages,
	[dm_db_index_physical_stats].page_count,
	GETUTCDATE() AS [CaptureDate]
FROM 
	[sys].[dm_db_index_physical_stats](DB_ID(),NULL,NULL,NULL,'LIMITED' ) AS [dm_db_index_physical_stats]
INNER JOIN 
	[sys].[objects] AS [objects] ON ([dm_db_index_physical_stats].object_id = [objects].object_id)
INNER JOIN 
	[sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id])
INNER JOIN 
	[sys].[indexes] AS [indexes] ON ([dm_db_index_physical_stats].object_id = [indexes].object_id
								AND  [dm_db_index_physical_stats].index_id = [indexes].index_id )
WHERE 
	[dm_db_index_physical_stats].index_type_desc <> 'HEAP'
AND 
	[dm_db_index_physical_stats].avg_fragmentation_in_percent > 20;
GO

Note – this will pick up data on all indexes that are over 20% fragmented.

By adding GETUTCDATE() we can monitor and track the fragmentation over time, allowing us to identify which tables’ indexes are being fragmented the quickest. Nice and simple to setup, first create a table to hold the data:-

CREATE TABLE dbo.[IndexAnalysis]
		(AnalysisID					INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		SchemaName					SYSNAME,
		TableName					SYSNAME,
		IndexName					SYSNAME,
		IndexDepth					INT,
		AvgFragmentationInPercent	FLOAT,
		FragmentCount				BIGINT,
		AvgFragmentSizeInPages		FLOAT,
		PageCount					BIGINT,
		CaptureDate					DATETIME);
GO

Then create a stored procedure to capture the data and insert it into the table:-

CREATE PROCEDURE dbo.[CaptureIndexFragmentation] AS

INSERT INTO dbo.[IndexAnalysis]
SELECT
	[schemas].name AS [SchemaName],
	[objects].name AS [TableName], 
	[indexes].name AS [IndexName],
	[dm_db_index_physical_stats].index_depth,
	[dm_db_index_physical_stats].avg_fragmentation_in_percent,
	[dm_db_index_physical_stats].fragment_count,
	[dm_db_index_physical_stats].avg_fragment_size_in_pages,
	[dm_db_index_physical_stats].page_count,
	GETUTCDATE() AS [CaptureDate]
FROM 
	[sys].[dm_db_index_physical_stats](DB_ID(),NULL,NULL,NULL,'LIMITED' ) AS [dm_db_index_physical_stats]
INNER JOIN 
	[sys].[objects] AS [objects] ON ([dm_db_index_physical_stats].object_id = [objects].object_id)
INNER JOIN 
	[sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id])
INNER JOIN 
	[sys].[indexes] AS [indexes] ON ([dm_db_index_physical_stats].object_id = [indexes].object_id
								AND  [dm_db_index_physical_stats].index_id = [indexes].index_id )
WHERE 
	[dm_db_index_physical_stats].index_type_desc <> 'HEAP'
AND 
	[dm_db_index_physical_stats].avg_fragmentation_in_percent > 20;
GO

This can then be run as a nightly job so that you can track the fragmentation of the indexes over time.

SELECT 
	SchemaName AS [Schema Name],
	TableName AS [Table Name],
	IndexName AS [Index Name],
	AvgFragmentationInPercent AS [Current % Avg Fragmentation],
	LAG(AvgFragmentationInPercent,1) OVER (PARTITION BY TableName, IndexName ORDER BY CaptureDate) AS [Previous % Avg Fragmentation],
	AvgFragmentationInPercent -
	LAG(AvgFragmentationInPercent,1) OVER (PARTITION BY TableName, IndexName ORDER BY CaptureDate) AS [Difference],
	CaptureDate AS [Capture Date]
FROM 
	dbo.IndexAnalysis

Note – This SELECT statement uses the LAG() function which is only available in SQL Server 2012 onwards.

Once you have the data you can then decide how to approach the maintenance. You could use something like:-

SELECT 		   
	'ALTER INDEX [' + IndexName + '] ON [' + SchemaName + '].[' + TableName +'] ' +
    (CASE	
     WHEN (avgfragmentationinpercent >= 20 AND avgfragmentationinpercent < 40) THEN 'REORGANIZE'
     WHEN avgfragmentationinpercent > = 40 THEN 'REBUILD' END) AS [SQL Statement]
FROM 
	dbo.[IndexAnalysis] 

This will generate the required ALTER INDEX statements, you could then use sp_executesql to perform the operation. Once you have the data, you can proceed with the best approach that suits your environment. One thing that I have found is that, with large indexes in a busy system, catching fragmentation early and performing frequent REORGANIZE operations instead of less frequent REBUILDs is preferable as REORGANIZE statements are less resource intensive.