Sr. No.
|
SQL Best Practices
|
Reason
|
1
|
Enabling Trace Flags
Globally | |
1117
|
To Enable Data Files grow equally in case of Autogrow.
| |
1118
|
To Reduce Allocation Contention on TempDb Database
when the TempDb database is heavily used. | |
4199
|
Enables all the fixes that were previously made for the
query processor under many Trace Flags. | |
834
|
All Buffer pool memory will be allocated during startup.
It Increases performance of queries. | |
2
|
Max Server memory
|
Its value should be kept 70-80% of physical memory. It sets the upper limit to the amount of memory used by the buffer pool of the database engine.
|
3
|
Min Server memory
|
It can be set to any mimimum value. . The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload.
|
4
|
Backup Compression
should be enabled |
By enabling backup compression disk space can be
saved as the size of backup file reduces. |
5
|
TempDB as per cores
|
When the tempdb database is heavily used, SQL Server
may experience contention when it tries to allocate pages. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4. It “Load Balances” the thread. |
1. Enabling TraceFlags:
2. Setting Server memory
sp_configure 'show advanced options',1
reconfigure
sp_configure 'max server memory (MB)',40960
reconfigure
sp_configure 'min server memory (MB)',512
reconfigure
sp_configure 'backup compression default',1
reconfigure
4. Tempdb Contention
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_1', FILENAME = N'H:\Tempdb\tempdev_1.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_2', FILENAME = N'H:\Tempdb\tempdev_2.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_3', FILENAME = N'H:\Tempdb\tempdev_3.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_4', FILENAME = N'H:\Tempdb\tempdev_4.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_5', FILENAME = N'H:\Tempdb\tempdev_5.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_6', FILENAME = N'H:\Tempdb\tempdev_6.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_7', FILENAME = N'H:\Tempdb\tempdev_7.ndf' , SIZE = 32768KB , FILEGROWTH = 153600KB )
GO
