Tag: configuration

SQL Server : Instance Configuration and Management

Recycling Logs

By default SQL Server retains six logs are only recycles a log when the instance is restarted. As a result, the logs can grow really big, wasting a lot of space and making them really hard to open and read. You should probably consider recycling the logs on a regular basis and altering the number of logs retained.

In this example, we will recycle the logs every day and retain 30 days worth of logs.

  • Expand “Management”.
  • Right-click on the “SQL Server Logs” node and select the “Configure” menu option.
  • Check the Limit option and enter the maximum number of error log files to “30”.
  • Click the “OK” button.

Recycle the error log every day.

  • Expand “SQL Server Agent”.
  • Right-click on “Jobs”.
  • Select the “New Job…” menu option.
  • Set Name to “RecycleErrorLog” and Owner to “sa”.
  • Click on the “Steps” page and click the “New” button.
  • Set the “Step name” to “Step_1” and set the Command to “sp_cycle_errorlog”.
  • Click the “OK” button.
  • Click on the “Schedules” page and click the “New” button.
  • Set the Name to “DailyMidnight”.
  • Change “Occurs” to “Daily” and leave the default “Occurs once at” as “00:00:00”.
  • Click the “OK” button.
  • Click the “OK” button on the “New Job” dialog.

For more information see:

Patch Management

  • RTM – Release to Market : The initial release.
  • Hotfix : Specific fixes.
  • CU – Cumulative Update : Rollup hotfixes.
  • SP – Service Pack : A large number of patches combined together.

Check out the Update Center for Microsoft SQL Server.

Trace Flags

Where “n” is a trace flag number. To alter a trace flag at session level do the following.

dbcc traceon(n)

To alter the trace flag globally do the following. It will not persist past a restart of SQL Server.

dbcc traceon(n, -1)

To make a persistent change to a trace flag do the following.

  • Open “SQL Server Configuration Manager”.
  • Double-click on the “SQL Server Service”.
  • Click the “Startup Parameters” tab.
  • Add a startup parameter “-T3226” and click the “Add” button.
  • Click the “Apply” button and click “OK” on the warning message.

The trace flag will be applied on subsequent restarts.

For more information see:

SQL Server : Basic Database Configuration

AutoClose

Never enable AutoClose. According to the documentation, “If TRUE, the database is closed, and its resources are freed when no user connection accesses the database.” It’s all opened again when the next user connects. This feature is being removed in future releases.

Check the AutoClose status of the databases.

SELECT name, is_auto_close_on FROM sys.databases

Turn it off for any database where it is turned on.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_CLOSE OFF

AutoShrink

Never enable AutoShrink. When enabled the server will occasionally check the data files and attempt to release unused space. It represents a was of resources and will be removed form a future release.

Check the AutoShrink status of the databases.

SELECT name, is_auto_shrink_on FROM sys.databases

Turn it off for any database where it is turned on.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF

Recovery Model

The recovery model determines how transaction logs are managed. For most important databases you will want to use FULL, allowing point in time recovery.

Check the recovery model for all databases.

SELECT name, recovery_model, recovery_model_desc FROM sys.databases

Set the recovery model to the required level.

USE master
GO

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
ALTER DATABASE MyDatabase SET RECOVERY BULK_LOGGED
ALTER DATABASE MyDatabase SET RECOVERY FULL

Statistics Collection

Statistics are stale when a sufficient number or percentage of rows of the data have changed. The amounts vary by version, so double check if you want to be accurate. You can leave SQL Server to automatically collect statistics. Statistics collection can be done in synchronous or asynchronous mode.

  • Synchronous – Queries wait for stats collection to be completed.
  • Asynchonous – Allows queries to continue with old stats until the new stats are avaiable.

Typically you will want to use asynchronous statistics gathering.

Check the current statistics settings for all databases.

SELECT name,
       is_auto_create_stats_on,
       is_auto_update_stats_on,
       is_auto_update_stats_async_on
FROM   sys.databases

Amend the statistics settings.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON

Generate the commands to alter the statistics settings for all user-defined databases.

SELECT 'ALTER DATABASE ' + name + ' SET AUTO_UPDATE_STATISTICS_ASYNC ON'
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')

You can also manually gather statistics for tables, indexes and views using the UPDATE STATISTICS command.

UPDATE STATISTICS mytable

Maintenance Mode

Set the desired maintenance mode.

ALTER DATABASE MyDatabase SET multi_user (normal state)
ALTER DATABASE MyDatabase SET single_user
ALTER DATABASE MyDatabase SET restricted_user

Parallelism

Setting the ‘max degree of parallelism’ (MAXDOP) is dependent on a number of factors. Check out this post.

The default value for the ‘cost threshold for parallelism’ is 5, which most people consider is too low. You will see recommendations of 50 as a reasonable starting point.

exec sp_configure "show advanced options", 1
reconfigure

exec sp_configure "cost threshold for parallelism", 50
reconfigure

exec sp_configure "show advanced options", 0
reconfigure

For more information see: