SQL DBA Guru

 General DBA Best Practices

• Check and evaluate the proper SQL Server Edition and the pre-requisites before you install SQL Server.
• Install Service Packs and Cumulative Hotfixes.
• Do NOT grant sysAdmin permissions to anyonce except the Database Administration team.
• Keep the System databases, temp database, SQL Data Files, SQL Log Files and SQL backups in separate directories.
• Keep the recovery mode as Simple unless you intend to do a transaction log backup for Point in Time recovery.
• Try to minimise granting permission to SQL Login IDs and individual Windows ID too.
• Setup SQL Alerts.
• Keep a check on database and transaction log space. Also note the free disk space available on the disks.
• Make a baseline for the performance level of all the SQL Servers.
• Document each step in case you do any changes in SQL Server.
• Run SQL Server best practices analyzer on regular intervals on each SQL instances.
• Only install the necessary SQL Services. Turn off any services which are not needed.
• If you are upgrading a SQL Server, run the Upgrade Wizard first.
• After upgradation, update all the statistics in all the databases.
• Create separate individual logins for each DBA.
• Change the SA password on regular intervals.
• Use Windows authentication wherever possible.
• Check and remove orphaned users and unmapped logins at regular intervals.
• Avoid creating network shares on SQL Servers.
• Do not browse internet from a SQL Server.
• Perform maintenance jobs on regular basis and monitor it.
• Change the memory settings as per the memory requirement of the SQL instance.
• Shrink database manually, don't choose the 'Auto-Shrink' option.

Day to Day

• Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
• Verify that all scheduled jobs have run successfully.
• Confirm that the backups have been made and successfully saved to a secure location.
• Monitor disk space to ensure your SQL Servers won’t run out of disk space.
• Throughout the day, periodically monitor performance using both System Monitor  and Profiler.
• Use Enterprise Manager/Management  Studio to monitor and identify blocking  issues.
• Keep a log of any changes you make  to servers, including documentation of  any performance issues you identify and  correct.
• Create SQL Server alerts to notify you of potential problems, and have them  emailed to you. Take actions as needed.
• Run the SQL Server Best Practices Analyzer on each of your server’s instances on a periodic basis.

 Database Level

• Unless you know exactly what you are doing and have already performed impartial experiments that prove that making SQL Server configuration changes helps you in your particular  environment, do not change any of the  SQL Server configuration settings.
• In almost all cases, leave the “auto  create statistics” and “auto update  statistics” options on for all user  databases.
• In most cases, the settings for the  “maximum server memory” and the “minimum server memory” should be left to their default values. This is because the default values allow SQL Server to dynamically allocate memory in the server for the best overall optimum performance. If you use AWE memory, then this recommendation is to be ignored, and maximum memory needs to be set manually.
• Many databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the “auto shrink” database option, as it can waste SQL Server resources unnecessarily. Instead, shrink databases manually.
• Don’t rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth.

 Full-Text Search

• If using SQL Server 2005, full-text catalogs can be attached and detached along with their database. This is the best way to move a database and  catalog together.
• If using SQL Server 2005, use BACKUP and RESTORE to backup and restore  your full-text catalogs. Catalogs should be backed up when the database is  backed up.
• Generally speaking, limited hardware  resources contribute most to poor Full-Text Search performance. Check for CPU, disk I/O, and memory bottlenecks, and if they are identified, they often must be corrected with the purchase of larger hardware.
• Regularly defragment the index on  the base tables that feed the Full-Text Search engine. In addition, regularly reorganize the full-text catalog.
• Keep full-text key columns as narrow as possible to speed performance.

  Installation

• Always fully document installs so that  your SQL Server instances can easily be reproduced in an emergency.
• If possible, install and configure all of  your SQL Server instances consistently, following an agreed-upon organization  standard.
• Don’t install SQL Server services you don’t use, such as Microsoft Full- Text Search, Notification Services, or  Analysis Services.
• For best performance of SQL Server  running under Windows, turn off any operating system services that aren’t  needed.
• For optimum SQL Server performance,  you want to dedicate your physical  servers to only running a single instance of SQL Server, no other applications.
• For best I/O performance, locate the  database files (.mdf) and log files (.ldf) on separate arrays on your server to isolate potentially conflicting reads and writes.
• If tempdb will be used heavily, also put it  on its own separate array. 
• Do not install SQL Server on a domain  controller. 
• Be sure that SQL Server is installed on an NTFS partition. 
• Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.

  Upgrading  

• Run the Upgrade Advisor before  upgrading. Make any necessary  changes before performing the upgrade.
• Perform a test upgrade of your test  SQL Servers before you upgrade your production servers. And don’t forget to test your applications with the new version also.
• Before you upgrade, be sure you have  a plan in place to fall back to in case the  upgrade is problematic.
• Don’t upgrade SQL Server clusters in  place. Instead, rebuild them on new  hardware.
• If you upgrade from a previous version  of SQL Server, you should update all of  the statistics in all your databases using either UPDATE STATISTICS or sp_updatestats. This is because statistics are not automatically updated during the upgrade process.

  Maintenance Jobs

• Avoid overlapping jobs on the same SQL  Server instance. Ideally, each job should run separately at different times.
• When creating jobs, be sure to include error trapping, log job activity, and set up  alerts so you know instantly when a job fails.
• Create a special SQL Server login  account whose sole purpose is to run  jobs, and assign it to all jobs.
• If your jobs include Transact-SQL code, ensure that it is optimized to run  efficiently.
• Periodically (daily, weekly, or monthly)  perform a database reorganization on all the indexes on all the tables in all your database. This will rebuild the indexes so that the data is no longer logically fragmented. Fragmented data can cause  SQL Server to perform unnecessary  data reads, slowing down SQL Server’s performance. Reindexing tables will also  update column statistics.
• Don’t reindex your tables when your database is in active production, as it can lock resources and cause your users performance problems.  Reindexing should be scheduled during down times, or during light use of the  databases.
• At least every two weeks, run DBCC CHECKDB on all your databases to verify database integrity.
• Avoid running most DBCC commands  during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.
• If you rarely restart the mssqlserver  service, you may find that the current  SQL Server log gets very large and takes a long time to load and view. You can truncate (essentially create a new log) the current server log by running DBCC ERRORLOG. Set this up as a weekly job. 
• Script all jobs and store these scripts in  a secure area so they can be used if you need to rebuild the servers.

  Replication

• Replication needs should be clearly  defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
• Ideally, publishers, distributors, and  subscribers should be on separate  physical hardware.
• Create, document, and test a backup  and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
• Script the replication topology as part of  your disaster recovery plan so you can  easily recreate your replication topology  if needed.
• Use default replication settings, unless  you can ensure that a non-default setting will actually improve replication  performance or other issues. Be sure  that you test all changes to ensure that they are as effective as you expect.
• Fully understand the implications of  adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
• Periodically, validate data between publishers and subscribers.
• Regularly monitor replication processes  and jobs to ensure they are working.
• Regularly monitor replication  performance, and performance tune as  necessary.
• Add alerts to all replication jobs so you are notified of any job failures.

 Indexing

• Periodically, run the Index Wizard or Database Engine Tuning Advisor against current Profiler traces to identify  potentially missing indexes.
• Remove indexes that are never used.
• Don’t accidentally create redundant indexes.
• As a rule of thumb, every table should  have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column, or some other column where the value is increasing — and is unique. In many cases, the primary key is the ideal column for a clustered index.
• Since you can only create one clustered index per table, take extra time to  carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an educated guess as to which query (the most common one run against the table, perhaps) is the most critical, and if this query will benefit from having a clustered index.
• If a column in a table is not at least  95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don’t want to add non-clustered indexes to columns that  aren’t at least 95% unique.
• Keep the “width” of your indexes as  narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to  read the index, boosting performance.
• If possible, avoid adding a clustered  index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
• Indexes should be considered on all columns that are frequently accessed by  the JOIN, WHERE, ORDER BY, GROUP  BY, TOP, and DISTINCT clauses.
• Don’t automatically add indexes on a table because it seems like the right  thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• When creating indexes, try to make  them unique indexes if at all possible.  SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
• If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have appropriate  indexes.
• Don’t automatically accept the default value of 100 for the fill factor for your indexes. It may or may not best meet your needs. A high fill factor is good for seldom changed data, but highly modified data needs a lower fill factor to reduce page splitting.
• Don’t over index your OLTP tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of indexes (for SELECTs) and the ideal number to minimize the overhead that occurs with indexes during data modifications.
• If you know that your application will be performing the same query over and over on the same table, consider creating a non-clustered covering index on the table. A covering index, which is a form of a composite index, includes all  of the columns referenced in SELECT,  JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

 Performance Tuning  

• Although heavy-duty hardware can help  SQL Server’s performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing  good money after bad on server hardware does not always fix SQL Server performance problems. Before getting faster hardware, be sure you have thoroughly tuned your applications,  Transact-SQL, and database indexing.
• In many cases, adding RAM to a server  is the cheapest and fastest way to boost hardware performance of a SQL Server. But before adding more RAM to a SQL Server, ensure first that it will be used by SQL Server. Adding more RAM doesn’t mean that SQL Server will always use it. If the current Buffer Hit Cache Ratio is consistently above 99% and you have well more than 10 MB of Available RAM,  your server won’t benefit from adding  additional RAM.
• If your SQL Server’s total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
• If the Physical Disk Object: % Disk Time counter exceeds 55%, and the  Physical Disk Object: Avg. Disk QueueLength exceeds a count of 2 for each individual disk drive in your disk storage subsystem, then you most likely  experiencing a disk I/O performance  issue and need to start looking for solutions.
• Don’t run any applications on your  server other than SQL Server, with the exception of necessary utilities.
• NTFS-formatted partitions should not exceed 80% of their capacity. For  example, if you have a 100GB logical  drive, it should never be fuller than 80GB. Why? NTFS needs room to work, and when you exceed 80% capacity, NTFS become less efficient and I/O can suffer for it.
• If your SQL Server database is mostly  reads, then a RAID 5 array offers good protection and adequate performance.  If your SQL Server database is mostly  writes, then use a RAID 10 array for best protection and performance.
• If your SQL Server’stempdb database is heavily used by your application(s),  consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server’s overall performance.
• The more spindles you have in an array,  the faster disk I/O will be.
• Ensure that all hardware is running the latest, approved drivers.

 SSIS Best Practices

• Schedule large data imports, exports,  or transformation on your production servers during less busy periods of the day to reduce the impact on your users.
• Consider running large, resource- intensive SSIS packages on a dedicated  physical server.
• Rename all default name and  description properties using standard  naming conventions. This will make it  easier for you and others to debug or modify your packages.
• Include annotations in your packages  to make it easier for you, and others, to  understand what is going on.
• Use Sequence Containers to organize package structures into logical work  units.
• Use Namespaces for your packages.
• Only scope variables for the containers  for which they are needed.
• If you know that data is already pre- sorted, set IsSorted=TRUE. Doing so can help prevent unnecessary sorts, which use up resources unnecessarily.
• When selecting columns from a table  to return, return only what is needed. In addition use a Transact-SQL statement in an OLE DB Source component, or the Lookup Component, instead of selecting an entire table. This way, you prevent unnecessary data from being processed.
• When INSERTing data, use the SQL Server Destination instead of the OLE DB Destination to boost performance.

 Notification Services  

• Run Notification  Services to a dedicated SQL Server.
• Notification Services makes heavy use  of the tempdb database. Because of this,  you should consider two things: First, consider allocating a minimum size for  the tempdb database, so that when SQL  Server is restarted, it will create a new tempdb database of an appropriate size.  This prevents SQL Server from having to expand the tempdb database size automatically, which can lead to short bursts of poor I/O performance as the database is expanded. Second, consider installing the tempdb database on its own dedicated physical disk drive or array in order to reduce I/O contention.
• Notification Services also makes heavy  use of the transaction log. To reduce I/O contention, consider locating the transaction log file on its own dedicated physical disk drive. In addition, the Full
• Backup Recovery model should be used to ensure a minimum of lost data, should there be any problem. Also, because the log is heavily used, be sure that it is backed up (log truncated) often so that it  does not fill up your disk space and stop  your server.
• While many indexes are created  automatically by Notification Services when the Notification Services database is created, not every potentially useful index is created. Once your system is in production, consider taking a Profiler Trace during a very busy time of the server and use the Index Wizard or the Database Engine Tuning Advisor to identify potential new indexes.

 Analysis Services

• Always run OLAP applications on their  own dedicated servers, never sharing a server running OLTP applications. The two types of applications are mutually exclusive when it comes to performance tuning.
• When designing OLAP cubes, don’t include measures or dimensions that  your users won’t use. Unused data will increase the size of your cubes and slow performance.
• When using the star schema design, at a minimum, you will create a non-clustered  index on the primary key of each  dimension table and a non-clustered  index on each of the related foreign- keys. From there, you can create non-clustered indexes on additional columns that will be queried on frequently. You don’t need to create composite indexes to create covering indexes because SQL Server will use index intersection to do this for you automatically.
• When you create indexes on your data warehouses and datamarts, use a  FILLFACTOR of 100 to ensure that the index pages are as full as possible. This reduces unnecessary I/O, speeding up performance.
• Schedule cube updates on your  production servers during less busy  periods of the day to reduce the impact  on your users.

Reporting Services

• Ideally, dedicate one or more physical  servers for Reporting Services.
• Manage all report code and .rdl files  using a Source Control system.