Category Archives: SQLSERVER DBA

srangadoddi 1:23 PM on June 23, 2015  

SQLSERVER DBA Best Practises

1.       Security Basics

 

  • Don’t give users more permissions than they need to perform their job. (Critical, Sounds simple, often hard.)

 

  • Don’t use the SA account for anything. Assign it a complex password, and keep it handy just in case. Use a domain account that is a member of the sysadmin role.

 

  • Don’t allow an application to use the SA or a sysadmin account to access SQL Server.

 

  • Use Windows Authentication security whenever possible. (Applicable for in house development).

 

  • Don’t give vendors sysadmin access to your servers.

 

  • Log off or lock your SQL Server (or workstation) when done.

2.       General Server Configuration

 

  • Ideally, SQL Server instances should run on a stand-alone server (physical or virtual) with no other apps running on it.

 

  • Avoid multiple instances unless you have a really good reason to use them. Consider virtualization instead.

 

  • Unnecessary SQL Server services should be uninstalled or turned off.

 

  • Ideally, don’t run antivirus/antispyware software locally.

 

  • If your organization’s policy requires running antivirus/antispyware software locally, exclude MDF, NDF, LDF, BAK, and TRN files.

3.       SQL Server Property Settings

 

  • Don’t change any of the default SQL Server instance -wide configuration property settings unless you thoroughly understand the implication of making the change. Examples of Server Property settings include:

 

  • Memory
  • Processors
  • Security
  • Connections
  • Database Settings
  • Advanced
  • Permissions

4.       Memory Configuration

 

  • Ideally, use 64-bit hardware and the 64-bit version of the OS and SQL Server.
  • Generally speaking, if using 64-bit memory, turn on “Lock Pages in Memory,” and let the instance dynamically manage its own memory (especially 2008).
  • If using the 32-bit version of SQL Server, and if using 4 GB or more of RAM, ensure that /3GB switch and AWE memory are correctly configured. Correct settings depend on available RAM.

 

5.       Data and Log File Management

 

  • Remove physical file fragmentation before creating new MDF or LDF files.
  • When creating new MDFs and LDFs, pre-size them to minimize auto growth events.
  • MDF files should be located on their own disks.
  • LDF files should be located on their own disks.
  • BAK and TRN backup files should be located on their own disks

 

6.       Instant File Initialization

 

  • Enable instant file initialization, which prevents MDF files from being zeroed out when they are grown, which allows MDF files to be created quickly. LDF files are not affected.
  • Speeds up CREATE DATABASE, ALTER DATABASE, RESTORE DATABASE, Autogrowth.
  • Requires SQL Server 2005/2008, and Windows Server 2003/2008 (or higher version).
  • Instant file initialization is turned on if the SQL Server (MSSQLSERVER) service account has been granted the SE_MANAGE_VOLUME_NAME permission by adding the account to the Perform Volume Maintenance Tasks security policy

. Members of the local Windows Administrator group automatically have this right

 

 

 

7.       Tempdb Management

 

  • Pre-size tempdb so autogrowth doesn’t have to happen often (8MB is default, which is very low).
  • Set autogrowth to avoid many growth spurts, use a fixed amount that minimizes auto growth use. (10% is default, which causes lots of auto growth).
  • If tempdb is very active, locate it on its own disks.
  • If very active, consider dividing the tempdb into multiple physical files so that the number of files is ¼ to ½ the number of CPU cores, up to 8 files. Each physical file must be the same size

 

 

 

8.       Database Property Settings

 

  • Don’t change database property settings unless you have a very good reason.
  • Auto Create Statistics: On
  • Auto Update Statistics: On
  • Auto Shrink: Off
  • Auto growth: Leave on. Use mainly for catching mistakes. File growth should be managed manually. Use fixed amount that minimizes auto growth occurrences.
  • Recovery Mode: Set to full for all production databases so transaction log backups can be made.
  • Page Verify: Use Checksum (2005/2008), don’t turn off.
  • Compatibility Level: Should be set to match current server version, unless there are compatibility problems

 

Some more below

 

SET ANSI_NULL_DEFAULT OFF

SET ANSI_NULLS OFF

SET ANSI_PADDING OFF

SET ANSI_WARNINGS OFF

SET ARITHABORT OFF

SET AUTO_CLOSE OFF

SET AUTO_CREATE_STATISTICS ON

SET AUTO_SHRINK OFF

SET AUTO_UPDATE_STATISTICS ON

SET CURSOR_CLOSE_ON_COMMIT OFF

SET CURSOR_DEFAULT  GLOBAL

SET CONCAT_NULL_YIELDS_NULL OFF

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER OFF

SET RECURSIVE_TRIGGERS OFF

SET  DISABLE_BROKER

SET AUTO_UPDATE_STATISTICS_ASYNC OFF

SET DATE_CORRELATION_OPTIMIZATION OFF

SET TRUSTWORTHY OFF

SET ALLOW_SNAPSHOT_ISOLATION OFF

SET PARAMETERIZATION SIMPLE

SET READ_COMMITTED_SNAPSHOT OFF

SET  READ_WRITE

SET RECOVERY FULL

SET  MULTI_USER

SET PAGE_VERIFY TORN_PAGE_DETECTION

SET DB_CHAINING OFF

 

9.       Configuring Jobs—General

 

  • If your server doesn’t have any jobs, then there is a problem, as all servers need jobs.
  • Try to schedule jobs so they don’t interfere with production.
  • Try to prevent jobs from overlapping.
  • Set alerts on jobs so you are notified if they fail.
  • Check jobs daily to verify that they have run correctly (not hung, not run abnormally long, etc).
  • If you use the Maintenance Plan Wizard, be careful to use it properly. If misused, it can create maintenance jobs that hurt performance

 

 

10.   Don’t Shrink Files

 

  • If you properly size your MDFs and LDFs, then you should never have to shrink a file.
  • Don’t schedule database or file shrinking operations.
  • If you must shrink a database:

–Do so manually

–Rebuild the indexes after the shrink is complete

–Schedule these steps during the slow time of the day

  • Benefits of not automatically shrinking files:

–Eliminates grow and shrink syndrome

–Reduces physical file fragmentation

–Reduces resources used for these operations, allowing more important tasks to use them

 

 

 

 

11.   Create Index Rebuilding/Reorganize Job

 

  • Indexes need to be rebuilt or reorganized regularly to minimize fragmentation and reduce wasted space.
  • Consider rebuilding an index if it is heavily fragmented (>30%). In Enterprise Edition, can perform online. If Standard Edition, consider it an off-line job. This automatically updates statistics, so you don’t need to do this again.
  • Consider reorganizing an index if it is not heavily fragmented (>5% and <= 30%). This is an online operation and doesn’t use a lot of resources. You must update statistics afterwards, as this is not automatically done for you.
  • Ideally, you should only rebuild or reorganize indexes that need it. Use sys.dm_db_index_physical_statsto identify what tables/indexes need to be rebuilt/reorganized

 

 

 

 

 

 

12.   Gather Statistics

 

There are different ways statistics are created and maintained in SQL Server:

    • By SQL Server engine itself – There are some database level properties that determine the automatic creation and updating statistics whenever there is a need. For example,
      • AUTO_CREATE_STATISTICS property of the database, if set to TRUE, lets SQL Server (or more specifically SQL Server Query Optimizer) routinely create single-column statistics for query predicate columns as necessary, to improve cardinality estimates for the query execution plan if that specific column does not already have a histogram in an existing statistics object. The name for these statistics starts with _WA as you can see in the figure below, as an example for a table.

You can also use the below query to find out all of those statistics created by SQL Server Query Optimizer for a specific table:

 

SELECT

OBJECT_NAME(stats.object_id) AS TableName,

COL_NAME(stats_columns.object_id, stats_columns.column_id) AS ColumnName,

stats.name AS StatisticsName

FROM sys.stats AS stats

JOIN sys.stats_columns AS stats_columns ON stats.stats_id = stats_columns.stats_id

AND stats.object_id = stats_columns.object_id

WHERE OBJECT_NAME(stats.object_id) = ‘SalesOrderHeader’

AND stats.name like ‘_WA%’

ORDER BY stats.name;

 

AUTO_UPDATE_STATISTICS property of the database, if set to TRUE lets SQL Server (or more specifically SQL Server Query Optimizer) routinely update the statistics being used by the query when they are stale (out-of-date) . Unlike AUTO_CREATE_STATISTICS, which applies for creating single column statistics only? AUTO_UPDATE_STATISTICS updates statistics objects created for indexes, single-columns in query predicates, filtered statistics and statistics created using the CREATE STATISTICS command.

By default, identified stale statistics are updated synchronously, which means the query being executed will be put on hold until the required statistics are updated in order to ensure the query always compiles and executes with up-to-date statistics. Sometimes this wait could be longer, especially when a table involved in the query is bigger in size, and might cause the client request time-out. In order to deal with such a situation, SQL Server has AUTO_UPDATE_STATISTICS_ASYNC property of the database, which if set to TRUE lets the current running query compile with existing statistics even if the existing statistics are stale (chooses suboptimal query plan) and initiates a process in the background asynchronously to update the stale statistics in order to ensure subsequent query compilation and execution uses up-to-date statistics.

There are different ways you can change these properties; for example, you can use a script to change or use the Database Properties dialog box in SQL Server Management Studio to change it as shown below:

ALTER DATABASE [StatisticsTest] SET       AUTO_CREATE_STATISTICS ON

ALTER DATABASE [StatisticsTest] SET       AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [StatisticsTest] SET       AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

 

13.   Create Data Corruption Detection Job

 

  • Ideally, run DBCC CHECKDB as frequently as practical.
  • Create an appropriate job to run this (or similar) command:

DBCC CHECKDB (‘DATABASE_NAME’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Note: Consider using PHYSICAL_ONLY option for large or busy production servers to reduce run time.

  • If you have a problem, you want to find it as soon as possible to reduce the risk of data loss. Don’t use the DBCC CHECKDB repair option unless you fully understand its implications.

 

 

14.   Implement a Backup/Restore Strategy

 

  • Create a job to perform full backups daily on all system and user production databases, plus log backups hourly (or similar variation).
  • If a database uses the bulk or full recovery model, you must back up the transaction log to keep in from growing uncontrollably.
  • Backup using RESTORE WITH VERIFYONLY to help verify backup integrity. (Does not guarantee good backups.)
  • Periodically test backups to see if they can be restored.
  • Set up an appropriate backup retention policy.
  • Store backups securely and off-site (not on same disk array or SAN).
  • If you have a limited backup window, or have limited disk space, use backup compression. Can be a big time saver

 

 

 

 

15.   Set Up Alerts for Critical Errors

 

  • Create a SQL Server Event Alert for all events with a severity of 19 [fatal] and higher.
  • Have alerts sent to you or whoever is responsible for day-to-day monitoring.
  • Consider a third-party alerting tool if SQL Server Alerts doesn’t meet all of your needs.

 

16.   Create a Disaster Recovery Plan

 

  • You must create a document that outlines, step-by-step, in great detail, how you will recover your SQL Servers in the case of any problem, small or large.
  • You need to practice using the plan so you are familiar with it and can easily implement it.
  • Keep Microsoft SQL Server’s Product Support phone number handy. Paste it near your computer.
  • Remember: Most “disasters” are small, such as a corrupted database. Big “disasters” occur very rarely, if ever. But you need to be prepared for both

 

17.   Document Everything

 

  • Yes, documentation is very boring, but it is very critical for business to flow smoothly. Be sure to document:
  • The installation and configuration of each instance.
  • The installation and configuration of any application that uses SQL Server as its back end (as related to SQL Server).
  • Troubleshooting tasks, as the same problem may reoccur, and you don’t want to reinvent the wheel.
  • Any time any change is made to any instance for any reason.
  • Be sure that documentation is easily available to everyone who needs access to it.

 

18.   Test Everything

 

  • Before you make any change on a production SQL Server, be sure you test it first in a test environment.