This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an important interview question which can be asked in SQL DBA interview.
You should know at least five good points or practices that you are following after the fresh installation of SQL Server.
Please check the below few best practices, and Below is not a complete list so you guys can share more good practices via comments.
Disable Services which are not require
For example, If you installed a default SQL Instance and not going to install any other instance, disable the Brower Service.
Change the Service mode
It is recommended to change the start mode of the SQL Agent so on each server restart; the service will start automatically.
Set the Minimum and Maximum Server Memory
Set Auto growth Size for Database
Set database properties for following options
Right click on database -> go to properties -> Options
Auto Close – False
Auto Create Statistics – True
Auto Shrink – False
Auto Update Statistics – True
Auto Update Statistics Asynchronously – True
Change the default storage location of TempDB and set in another disk
Change the default Index Fill Factor
Open SSMS -> Right click on SQL Server Instance -> Go to properties -> Database Settings -> Change Default Index Fill Factor
Default Index Fill Factor is 0 or 100, means full. If your system is generating more OLTP transactions, you should set it between 80 to 90
Configure Processor for MAXDOP to achieve the parallel processing
Open SSMS -> Right click on SQL Server Instance -> Go to properties -> Advanced -> Change Max Degree of Parallelism
Change the SQL Database default locations
Open SSMS -> Right click on SQL Server Instance -> Go to properties -> Database Settings -> Change Database default locations
Change Remote Query Timeout
Open SSMS -> Right click on SQL Server Instance -> Go to properties -> Connections
The default wait is 600, you can set to 5 or 8 minutes.
Configure SQL Server Network
Open SQL Server Configuration Manager and check for SQL Server Network Configuration
Enable the TCP/IP protocol
Change the password of SA user
Remove the BUILTIN\Administrators group from the SQL Server Logins
Check the Server Authentication Mode and enable C2 Audit tracing
Open SSMS -> Right click on SQL Server Instance -> Go to properties -> Security
C2 audit is used to trace all the user activities like SQL Commands, Login, Logout, and server events
Leave a Reply