Geek Links

These are my personal notes for SQL Server tuning.  Take from them what you will and research what you think may be valuable.

 

Script – SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

Good starting point for a framework for your database maintenance.

Customize the scripts to fit your needs.

http://ola.hallengren.com/

Script – SQL Server configuration, Troubleshooting and Tuning SQL Server

Is your database secure and optimized?

http://www.brentozar.com/blitz/

DBA Checklist – Great place to start as DBA overview

Need something to guide you through the DBA process or to help make sure you have all your bases covered?

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/

These are  third party monitoring Software that seems to be something clients may want to test with the free trial.

SQL Server Monitoring and tuning for DBAs,  T-SQL understanding or running when virtualized SQL servers with performance issues.

http://www.confio.com/

http://www.idera.com

Hope you find these helpful.

Fine Tuning SQL Server

Virtual SQL Servers are more common now and becoming more so, but I would only recommend running very small SQL Server instances or test instances on virtualized servers.   If the VMWare admin and storage admin are not on top of things, performance will suffer.  And for a large production DB, the cost savings of virtualization are not often worth it.  Most organizations that have large virtual SQL servers pay more for consultants than the server hardware would cost to resolve performance issues.    Although figuring out these types of issues are half my job right now…what am I saying?

http://www.sqlmag.com/article/sql-server/virtualization-tips-145319

SQL Trace flags you may want to use.

DBCC TRACEON (1118,-1)                         — TempDB entire page vs. partial to avoid latch contention
DBCC TRACEON (1222,-1)                        — Deadlocks in the SQL Server Error log
/*

by default, every successful backup operation adds an entry in the SQL Server error log and in the system event log.

If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in
which finding other messages is problematic.
With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

*/
DBCC TRACEON (3226,-1)

–Non Default SQL settings

SP_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE

exec sp_configure ‘fill factor (%)’,90
reconfigure with override
go

Managing the TempDB is important for object creation

One per CPU and sized properly for initial creation with correct sizing.  Should almost never auto grow!

Log file sizing reduces I/O!

/*

, consider creating the
transaction log in 8GB chunks (8GB, then extend it to 16GB,
then extend it to 24GB and so forth) so that the number (and size)
of your VLFs is more reasonable (in this case 512MB).

This should help in the time it takes to preform log backups
depending on your back configuration and needs.
*/

/*

VM memory useage for memory usage

Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool

Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory
that is allocated for the buffer pool. The page size varies depending on the hardware platform,
but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept
throughout the lifetime of the process. Trace flag 834 improves performance by increasing the
efficiency of the translation look-aside buffer (TLB) in the CPU.

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in
memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages
cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated
to SQL Server.

Note If you are using the Column Store Index feature of SQL Server 2012, we do not recommend
turning on trace flag 834.

Drawbacks
Enabling Large Pages might give a boost to performance but does not come without downsides. First, VMware’s
Transparent Page Sharing (TPS) is rendered ineffectual for this virtual machine. A 2MB memory block is almost
guaranteed to be unique, rather than a 4KB block, so do not count on TPS to save any memory on the host. If a
host is overcommitted, this could reduce performance of other guest VMs because the balloon driver could be
more aggressive in ballooning for memory reclamation. However, best practices dictate that a host not be
overcommitted for business critical systems so this should be a nonissue.
Second, verify the boot time of your SQL Server when Large Pages are enabled. SQL Server must claim and zero
out the memory it will be using before the service can completely start up. This process takes time. It can
potentially take quite a while. This configuration can take a SQL Server startup time from a minute or two to over
30 minutes, depending on your environment. Be very careful that by enabling Large Pages you do not
inadvertently violate your SLA during an unplanned outage. Remember, VMware HA requires a restart of a server
on the new host if a host fails. This reboot time could be substantial if large amounts of memory are allocated.
Another problem can come if the service needs to restart and the memory it now requires to be contiguous is no
longer that. If so, SQL Server will fail to start and you will see the following error in the ERRORLOG file.

For more information about large-page support in Windows,
visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/aa366720.aspx
*/
/*
When saying “Large Pages is a trade-off between memory savings
and performance benefits”, I’m not saying that using Large Pages
is a performance impact !!! Large Pages perform better than small
pages if the Guest OS and application can handle them. But,
when using Large Pages the monitoring of your memory usage
with the standard tools,
gives you the wrong impression of memory load on your host.

http://www.vmware.com/files/pdf/large_pg_performance.pdf
*/
DBCC TRACEON (834,-1)
/*

*/
–DBCC TRACEON (836,-1)

/*

*/
–DBCC TRACEON (661,-1)

/*
Trace flag 2301: Enable advanced decision support optimizations

Trace flag 2301 enables advanced optimizations that are specific to decision
support queries. This option applies to decision support processing of
large data sets.

You can turn on trace flag 2301 at startup or in a user session. When you turn
on trace flag 2301 at startup, the trace flag has global scope. When you turn on
trace flag 2301 in a user session, the trace flag has session scope.
DBCC TRACEON (2301,-1)
*/
DBCC TRACEON (834,-1)

/*
VM memory useage for memory usage
Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool

Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory
that is allocated for the buffer pool. The page size varies depending on the hardware platform,
but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept
throughout the lifetime of the process. Trace flag 834 improves performance by increasing the
efficiency of the translation look-aside buffer (TLB) in the CPU.

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in
memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages
cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated
to SQL Server.

Note If you are using the Column Store Index feature of SQL Server 2012, we do not recommend
turning on trace flag 834.

Drawbacks
Enabling Large Pages might give a boost to performance but does not come without downsides. First, VMware’s
Transparent Page Sharing (TPS) is rendered ineffectual for this virtual machine. A 2MB memory block is almost
guaranteed to be unique, rather than a 4KB block, so do not count on TPS to save any memory on the host. If a
host is overcommitted, this could reduce performance of other guest VMs because the balloon driver could be
more aggressive in ballooning for memory reclamation. However, best practices dictate that a host not be
overcommitted for business critical systems so this should be a nonissue.
Second, verify the boot time of your SQL Server when Large Pages are enabled. SQL Server must claim and zero
out the memory it will be using before the service can completely start up. This process takes time. It can
potentially take quite a while. This configuration can take a SQL Server startup time from a minute or two to over
30 minutes, depending on your environment. Be very careful that by enabling Large Pages you do not
inadvertently violate your SLA during an unplanned outage. Remember, VMware HA requires a restart of a server
on the new host if a host fails. This reboot time could be substantial if large amounts of memory are allocated.
Another problem can come if the service needs to restart and the memory it now requires to be contiguous is no
longer that. If so, SQL Server will fail to start and you will see the following error in the ERRORLOG file.

For more information about large-page support in Windows,
visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/aa366720.aspx
*/
/*
When saying “Large Pages is a trade-off between memory savings
and performance benefits”, I’m not saying that using Large Pages
is a performance impact !!! Large Pages perform better than small
pages if the Guest OS and application can handle them. But,
when using Large Pages the monitoring of your memory usage
with the standard tools,
gives you the wrong impression of memory load on your host.

http://www.vmware.com/files/pdf/large_pg_performance.pdf
*/
DBCC TRACEON (834,-1)
/*

*/
–DBCC TRACEON (836,-1)

/*

*/
–DBCC TRACEON (661,-1)

/*
Trace flag 2301: Enable advanced decision support optimizations

Trace flag 2301 enables advanced optimizations that are specific to decision
support queries. This option applies to decision support processing of
large data sets.

You can turn on trace flag 2301 at startup or in a user session. When you turn
on trace flag 2301 at startup, the trace flag has global scope. When you turn on
trace flag 2301 in a user session, the trace flag has session scope.
DBCC TRACEON (2301,-1)
*/

Cary

Advertisements

Please leave a reply and let me know what you think.

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s