The
journey starts with the SQL Server installation. Installing a SQL Server is key
for a stabilized and healthy database system. A properly installed and
configured SQL Server rarely shows any issues. So let’s get started.
 |
Service Account
recommendation:
Although
a single DOMAIN\ACCOUNT can be used for the SQL Server and the SQL Agent
service, it is not desirable and not recommended as both services have different
execution context. Use separate domain accounts for each service.
Installation recommendation:
If
a DOMAIN\ACCOUNT is used for the SQL Server service account, then
(a) Add the DOMAIN\ACCOUNT to
the local administrator group.
(b) Log on to the intended
server with this account (optional).
(c) Run the SQL Server setup
executable as “run as administrator” privileges.
(d) Installation media can be
resided in a network share or in a DVD.
Once
installation is done, you need to remove the SQL Server account from the local
admin group; this is recommended for security reasons. However, please note that
you may still prefer the SQL Server Service account to be a part of the local
admin group to avoid permission related issues.
Local policy rights to SQL
Server Service Account:
SQL
Server service account rights varies from edition to edition. Do not assign any
local policy rights to SQL Server and SQL Agent service account in advance
which are not recommended. Each edition of the SQL Server installation process
will determine the required local policy rights and will be added automatically
during the installation of SQL Server. To validate appropriate service rights, review
the MSDN recommendations from the following link:
Windows consideration:
(a)
Network Binding order:
1.
Make sure that the domain network is the first bound network. The
miss-ordering issue usually reports in SQL Server fail-over cluster
installation.
2.
Usually, TCP/IP will be the most used protocol. Consider changing the
order of “network protocol bindings” to make network communications faster.
(b)
Disk alignment and block
size:
1.
NTFS Allocation Unit (formerly known as cluster
size) must be 64K. When formatting the partition that will be used for SQL
Server data files, it is recommended that you use a 64-KB allocation unit size
for data, logs, and tempdb.
2.
Ensure that Volume Alignment
(also known as disk, partition or sector alignment) has been set correctly.
Review and align the sector to 1024KB (or 2048KB max) before formatting the
disk.
3.
HBA Disk Queue Depth: In the SAN, a queue depth
of 64 is generally accepted as a good starting point in the absence of any
specific vendor recommendations.
(c) Windows Power Policy option: In Windows 2008, there are three power consumption
options (power plan), where “Balanced” is set to default. Make sure that the
“High Performance” has been selected. To make “power plan” configuration
effective, the server BIOS power management needs to be changed to “OS Control”
mode.
(d) TCP Chimney setting: Disable the TCP Chimney offload in Windows level. “TCP
Chimney Offloads” functionality does not work with virtualized Windows OS
(VMware and hyper-v), therefore it can be disabled permanently.
(e) SQL Server Port: Create an appropriate inbound rule in Windows firewall for SQL Server
ports (e.g. 1433, 5022) instead of disabling the firewall service.
Instant file initialization: To take advantage of the
instant file initialization feature, assign the “Perform volume maintenance
tasks” windows right to the SQL Server Service Account. When it is enabled, the
data file (mdf and ndf, but not ldf) will be initialized instantaneously without
filling any empty spaces with zeros.
Tempdb consideration: It is not necessary to
create multiple tempdb data files without understanding the workload nature and
concurrency in a Server. If the OLTP server experiences high concurrencies,
latch contention, row versioning, a high number of sort/hash operations, then
adding multiple data files will be helpful.
(a) In a SMP based system, 8
tempdb data files can be added initially. If more data files are required, then
add two tempdb data files at a time.
(b) In a NUMA based system, add
data files based on the number of NUMA nodes detected by the SQL Server Engine.
For a 4-NUMA system, the tempdb might have 8 or 12 data files. If more data
files are needed, then increase the tempdb data files by the number of NUMA
nodes.
(c) Make sure that all tempdb
data files are equal in size.
(d) Tempdb data files and log must
be isolated and resided in a single dedicated drive. Log files of tempdb can be
placed in the Log drive, but it is not necessary.
MAXDOP: The default value of MAXDOP
is 0 (zero). It works best for a well written OLTP application and if the
database is maintained properly. However, in some workloads, MAXDOP = 1 works
best, such as SharePoint database. There is no appropriate settings for MAXDOP
and it varies from server to server, and sometimes even in the same server time
to time (review http://support.microsoft.com/kb/2806535). Following are some considerations:
(a) In a SharePoint
implementation, it is highly recommended by Microsoft to use MAXDOP=1.
(b) If parallelism introduces
blocking on CXPACKET, lowering the MAXDOP setting may help.
(c) In a system, if there is an
enormous amount of spinlock and latch contention occurring, lowering the MAXDOP
value will help to improve the performance.
(d) Consider increasing “cost threshold
for parallelism” to fine tune the parallelism along with or instead of MAXDOP.
(e) In case of high CAXPACKET,
consider redesigning indexes, removing fragmentations and updating statistics
instead of changing MAXDOP.
(f) Please remember that
creating or rebuilding an index will suffer from lower values of MAXDOP.
Transaction Log: Following are the recommendations for the Transaction
Log:
(a) Place the transaction log in
a dedicated drive.
(b) Do not create multiple
transaction logs for a single database.
(c) Pre-size the log file well
in advance; use 4G or 8GB increments per growth.
Database Integrity: Make sure that the “Page
Verify” option for all databases is set to “CHECKSUM”. Running DBCC CHECKDB in
production server is resource intensive and it does not help to protect the
database from corruption. Instead of running DBCC CHECKDB, consider configuring
SQL Server “Event Alert Service” for database fatal error (Severity from 022 to
025) and get notifications right away.
No comments:
Post a Comment