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 

  • Install latest SQL Server Service Packs and Cumulative Updates 

http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx  





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