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