TempWorks Database Server

Before SQL Server installation 

  • Verify server disks are configured per the Self-Hosted Database Server Recommendations 
  • Assign local polices to user/account the SQL Server process is running under: 
    • “Lock pages in memory”
    • “Perform volume maintenance tasks”
  • Perform Diskpart align 1024 on volumes used by SQL Server 
C:> Diskpart
list disk 
select disk <DiskNumber> 
create partition primary align=1024 
assign letter=<DriveLetter> 
format fs=ntfs unit=64K label="your_disk_name" nowait

After SQL Server installation 

SQL Server Properties 

  • Best Practice: Move TempDB to its own drive  
    • Verify TempDB data and log files are in the correct location.
      Most installations have TempDB data files on a dedicated disk volume.
        TempDB log file should be on the dedicated SQL-Log disk volume.
    • TempDB should have multiple data files, there should be one file per CPU up to 4
    • TempDB data files setup: Initial Size = 5120 MB, Autogrowth by 5120 MB, unrestricted growth
    • TempDB log file setup: Initial Size = 1024 MB, Autogrowth by 1024 MB, unrestricted growth  
  • Max SQL Server Memory
    • Max SQL Server Memory = Total System Memory – (10% of system memory or 4096 MB, whichever is larger)
  • Advanced Server Properties
    • Optimize for ad hoc workloads: TRUE
    • Cost Threshold for Parallelism: 50
    • Max Degree of Parallelism: start with (CPU Cores / 3) or minimum of 2   

TempWorks Database Properties

  •  Verify TempWorks database data and log files, and filestream folder are on the correct disk volumes  
  • Database Options:
    • Auto Close: False
    • Auto Create Statistics: True
    • Auto Shrink: False
    • Auto Update Statistics: True
    • Auto Update Statistics Asynchronously: True
    • Page Verify: CHECKSUM  
  • Database data file setup: Autogrowth By 5120MB, unrestricted growth
  • Database log file setup: Initial Size = 1024 MB, Autogrowth by 1024MB, unrestricted growth  

Maintenance Plans 
see ola.hallengren.com for script examples

  • Daily (except on day Weekly plan is executed)
    • Check Database Integrity
    • Update Statistics
    • Back Up Databases 
  • Weekly
    • Check Database Integrity
    • Index Maintenance Script    
    • Update Statistics
    • Back Up Databases 

Related Articles

  • None