Intro
I was recently tasked with making a quick review of setup
and high level recommendations for the setup of a MS SQL Server based
application with a client. The following
contains several generic points for any MS SQL Server based application, but is
specifically appropriate for New World Systems Logos.NET application.
TempDB
In many implementations of Logos.NET, there are multiple
databases on the Live or Production server.
All these databases will use the same tempdb. Microsoft best practices[1]
indicate that tempdb should be associated with a number of data files equal to
the number of cores on the machine.
Though this recommendation is out of date[2]
(being accurate for SQL Server 2000), it remains accurate for machines up to 8
cores.
The Logos.NET database server under my review was running on
2 quad-core processors with 64 GB of RAM and the Tempdb was associated with one
data file (~1GB Init size, 10% growth)
and one log file (~147MB Init size, 10%
growth).
In order to optimize performance, tempdb should be altered
to have eight 1GB data files associated to it.
If this is not attainable due to disk space constraints, four 1GB data
files will still provide improved performance.
It is important that the data files be the same size, as there is an
existing issue with the algorithm which causes auto-grow to only grow a single
file and create an I/O hotspot. The size
and number of the data files, in either case, should be sufficient to prevent
the need for auto-growth, which can also have short-term negative performance
implications. Therefore, it is not
sufficient to have the data files be significantly smaller with room to
auto-grow.
Logos.NET relies heavily on stored procedures for the
overwhelming majority of its functionality.
These stored procedures, in turn, rely heavily on temporary tables
which, in turn, rely on tempdb. Due to
the large number of databases sharing the single tempdb (by design) and a
single data file (against best practice recommendations), performance is likely
to degrade during times of heavy use.
Parallelism
The
parallelism was set at 5 at the time of my review, but 5 is generally
too low for all except pure OLTP (which Logos.NET is not, due to clients heavy use of
reporting in the application). 50 is a
good initial setting and then monitor and adjust from there.[3]
Max Degree of Parallelism
Whatever the optimal setting for this, it is almost
certainly not 0 (likely to cause thrashing if many queries running at once) or
1 (hurt performance of any big queries as no parallelism). Need to load test, but best recommendations
is start with 4 and adjust.[4]
Memory
Max Server Memory
Best practice would be 80% of available. The machine under consideration had 64 GB available, and assuming 8GB for Windows I would recommend setting at 56GB.
Best practice would be 80% of available. The machine under consideration had 64 GB available, and assuming 8GB for Windows I would recommend setting at 56GB.
[2] http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
