1. Instance Settings
- Min and Max server memory can be set for each instance.
- A bigger “minimum memory per query”(2048 KB) can result in better query performance.
- Processor configuration should be left alone, otherwhise you could get a performance decrease.
- “Default index fill factor” specifies how much data will be placed on a “data page”. You should know your database before changing this setting.
2. File Groups and Data Files
Grouping Data Files into File Groups and placing them on different storage devices could increase performance.
.MDF – this is a primary data file and is part of the PRIMARY file group.
.NDF – this secondary data file is usually associated with secondary file groups, but can also be part of the PRIMARY file group.
.LDF – this is the transaction log data file, it holds the log information that is used to recover the database. It does not belong to any file group, and there can be multiple .ldf files for a database.
3. Contained Database
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
The following are some of the benefits and characteristics that contained databases have:
- They make easier to migrate databases from one server to another. Errors related to orphan users are no longer an issue with contained databases, since a contained database user can now be created without an associated login.
- Authentication can now occur at the database level.
- Contained database users can be Windows and SQL Server authentication users.
- A contained database user can access only contained database objects. They cannot access system databases and cannot access server objects.
- Metadata is stored on the contained database and not stored on system databases. This makes contained databases more portable than the databases we know.
4. Data Compression
Data compression can be applied to tables or indexes and can be one of two types:
- Row – requires less cpu
- Page – requires more cpu and offers better compression.
The data compression execution can be scheduled through the SQL Server Agent.
5. Data Ecryption
Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE (Transparent Data Encryption) does not increase the size of the encrypted database.
TDE encrypts the sensitive data in the database and protects the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data.
TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data “at rest”, meaning the data and log files.
To use TDE, follow these steps.
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
6. Partition Tables
SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data.
Creating a partitioned table or index typically happens in four parts:
- Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
- Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
- Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
- Create or modify a table or index and specify the partition scheme as the storage location.
7. Log File Autogrow
Best practices for managing file size growth:
- Ideally this should be handled manually, but this requires to closely monitor the SQL server, so in case you don’t want to worry about this just leave autogrow ON. (autogrow is suitable for small databases)
- Don’t set a small initial size for the file; when file autogrowth occurs performance is impacted.
- Set file growth to Megabytes and not to Percent. (256MB for log files, 1024MB for data files)
- Only use Shrink when the log file has grown to a much larger size than normally needed, and you need to reclaim that space for some other use.
- If the Maximum File Size has been reached you are in trouble. Transaction won’t be able to commit and you will see errors in SQL.
- You should not set the Maximum File Size to Unlimited.
8. Database Mail
Database Mail can be configured together with SQL Server Agent to send alerts and notifications to Operators. To do this you need to configure:
- Database Mail
- SQL Server Agent Operators
- SQL Server Agent Alerts
9. Error Logs
There are 2 areas where you can find logs: “Database Engine Logs” and “SQL Server Agent Logs”. You can specify how many logs do you want to keep before they are recycled.
- SQL Server Profiler
- Extended Events
- SSMS Activity Monitor
- Windows Performance Monitor
- Memory – Available Bytes
- Memory – Pages/sec
- Access Methods – Page Splits/sec
- Buffer Manager – Buffer cache hit ratio
- Buffer Manager – Page reads/sec
- Buffer Manager – Page writes/sec
- Memory Manager – Target Server Memory
- Memory Manager – Total Server Memory
- PhysicalDisk – Disk Time
- PhysicalDisk – Curent Disk Queue Length
- Processor – Privileged Time
- Processor – User Time
- System – Processor Queue Length
Auditing can be done at the Database level or at the Instance level. Audit logs can be saved to the following locations:
- Application Log
- Security Log – you need to allow the SQL service account access to write to the security log.
- File Log
The Audit Action Types are explained HERE
With Policy-Based Management we can create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects. Policy-Based Management offers:
- The Ability to Define Standard Policies
- The Ability to Selectively Enforce Policies
- The Ability to Automate Policy Checking and Enforcement
- The Ability to Fix Out of Policy Conditions with the Click of a Button