SQL Server 2012 Performance Sensitive Configuration Parameters
SQL server has plethora of configuration options and mainly can be divided into three levels – Server, Database and Connections. Many options overlap each other and hence will be separated by topics. This article doesn't cover all options but only the most important one in terms of performance. Startup Parameters Flag -T834 » Enable SQL Server to use larger pages Flag –x » Disable SQL Server Performance counters and avoid potential overhead Startup Stored Procedures Scan for startup procs » Mark for automatic execution of stored procedures at SQL Server startup » Try enabling it when having an expensive query that takes a long time to run at first execution Memory Configurations Dynamic Memory Minimum » The goal is to have enough memory available so that windows don’t need to swap pages from physical memory to virtual memory support file (pagefile.sys) » I