1.

List down best practices of tempDB configuration?

Answer»
  • Collation of TempDB should be the same as the SQL Server INSTANCE collation.
  • TempDB database should be sa.
  • Guest user should not drop or revoke permissions from TempDB database
  • Keep the recovery model SIMPLE only.
  • Configure tempdb files to automatically grow as required.
  • Ensure TempDB drives is with RAID PROTECTION in order to PREVENT a single disk failure from shutting down SQL Server
  • Keep the TempDB database to separate set of disks
  • TempDB database size should be according to server load.
  • TempDB database data files should be configured as per available C.P.U. Cores
  •  If no of cores < 8 then no. of data files  equals to no of logical processors
  • If no. of cores between 8 to 32 inclusive then no. of data files  equals to 1/2 data files as logical processors
  • If no. of cores > 32 then no. of data files  equals to 1/4 data files as logical processors
  • Ensure each data file should be of the same size to allow optimal proportional-fill performance.
  • Reside tempdb database on a fast I/O subsystem.
  • Configure auto growth if tempDB data  Log files to a reasonable size to avoid the tempdb database files from growing by too small a VALUE.
  • Guidelines for setting the FILEGROWTH increment for tempdb files
  • If TempDB file size > 0 and < 100 MB then the recommended filegroup increment can be 10 MB
  • If TempDB file size > 100 and < 200 MB then the recommended filegroup increment can be 20 MB
  •  If TempDB file size >= 200 MB then the recommended filegroup increment can be 10% OR any fixed value depending on the requirement or on basis of I/O system capabilities
  • Do not shrink TempDB unless necessary.
  • Do not enable auto-create statistics & auto update statistics.
  • Ensure to have auto close OFF


Discussion

No Comment Found

Related InterviewSolutions