7. Enabling Dedicated Administrator Connections
SQL Server 2008 provides a method for members of the sysadmin
group to connect to an unresponsive instance of SQL Server, even when
other connections are failing. This feature improves the database
administrator's ability to troubleshoot problems within SQL Server.
By default, in order to
utilize a dedicated administrator connection, you must connect on the
server itself through SQLCMD or SQL Server Management Studio. Luckily,
Microsoft allows you to enable remote dedicated administrator
connections, which will allow you to connect to the dedicated
administrator connection from across the network. Enable remote
dedicated connections for administrators by executing the following code
from a query window:
EXEC sys.sp_configure 'remote admin connections' ,1
GO
RECONFIGURE
GO
You should now be able to log in
as an administrator, not only locally but across the network as well.
This ability is invaluable when your server is in trouble and normal
logins are not going through.
8. Disabling Default Trace
By default, SQL Server 2008
enables a server-side trace to start when the SQL Server service starts
for an instance. When you start a SQL Server instance, the trace
captures some database options, errors and warnings, full-text
information, objects creation, deletion and alters, as well as security
and memory changes on the server. The trace is light-weight and should
not cause performance problems. However, if for some reason you do not
want this trace running, then you can disable it by executing the
following commands:
EXEC sys.sp_configure 'default trace enabled' ,0
GO
RECONFIGURE
GO
Think twice before
disabling the trace because it can help you capture events when they
occur. How many times have you been asked questions about who performed a
certain action or at what time the action occurred? If you do not have a
trace running at the time the action occurred, you will be unable to
provide an adequate answer to such questions. With the default trace,
however, such questions may be easily answered.
9. Enabling Use of the CLR (Common Language Runtime)
The clr enabled option
within SQL Server 2008 allows SQL Server to run assemblies. By default,
the option is disabled, or set to zero. That means assembly executions
aren't allowed or permitted to run within SQL Server 2008. You can
enable the use of the CLR as follows:
EXEC sys.sp_configure 'clr enabled' ,1
GO
RECONFIGURE
GO
Changes to the clr enabled
option are effective immediately. You do not need to restart your
service. Disabling CLR after it has been enabled causes any application
domains that include user assemblies to unload immediately following the
RECONFIGURE statement.
10. Choosing Lightweight Pooling
Lightweight pooling is
an option available in SQL Server 2008 that can potentially help in
multiprocessor environments that experience heavy context switching.
Lightweight pooling potentially increases throughput, which can increase
performance.
Enable lightweight pooling by executing the following code:
EXEC sys.sp_configure sp_configure 'lightweight pooling', '1'
GO
RECONFIGURE
GO
Unfortunately, you cannot
enable common language runtime after the enabling of lightweight
pooling. So spend a little time contemplating your need for the CLR
prior to enabling lightweight pooling. If you do enable the feature, SQL
Server will generate a message to remind you that you also must disable
the use of the CLR.
|
We have experienced
problems with our linked server queries failing after our having enabled
lightweight pooling. Our particular problems became apparent upon
restart of our services. If you are working in an environment with
linked servers, then make sure you test thoroughly before enabling
lightweight pooling.
|
|
11. Enabling a Query Governor Cost Limit
Have you ever
identified resource-intensive queries on your system that cause all
sorts of performance problems? We especially get annoyed with users who
execute queries and forget the where clause and the nolock
hint on the largest table in our Very Large Databases (VLDB). To avoid
such problems, you can specify a maximum limit in seconds for queries
executed against your server. Such a limit is termed a query governor cost limit. Before you can specify a limit in seconds, you must enable the option as follows:
EXEC sys.sp_configure 'query governor cost limit' ,1
GO
RECONFIGURE
GO
We know this option sounds
great, and it can definitely be a method for improving performance.
However, we encourage you to consider the consequences of preventing
certain processes from running against your server. Believe me, we're on
your side—we would love to make developers write better code, and
preventing a process from running is one way to force developers to
write that code better. Unfortunately, your boss's boss, who really
needs the results from a long-running query, may not be as
understanding. Make sure you set a query limit decision wisely. The
Resource Governor that was added in SQL Server 2008 actually provides
much more flexibility when dealing with resource-intensive queries.
12. xp_cmdshell
xp_cmdshell is a configurable option that allows you to disable or enable the use of the extended stored procedure xp_cmdshell. xp_cmdshell is used to execute command-line statements from within the database engine. If you need it though, you can enable xp_cmdshell by executing the following code:
EXEC sys.sp_configure 'xp_cmdshell' ,1
GO
RECONFIGURE
GO
Be careful! By default, the xp_cmdshell
stored procedure is disabled during install and should remain disabled
if it is not required by the application. Think about it—once a user
gains access to your system, he has the power to cause all sorts of
havoc on your server.
13. Miscellaneous Configuration Options
In this section, we describe a
set of configuration options requiring more expertise before enabling on
your SQL Server. Most of these setting have a direct impact on how your
SQL Server will process queries, and we strongly recommend testing, and
hopefully load testing, before enabling these options in your
production environment. To be honest, we do not tweak all of the
configuration options we describe here, but we think they are important
to know about. Here's the list:
Blocked Process Threshold:
Amount of time a process is blocked before a blocked process report is
generated. The threshold is measured in seconds, and alerts can be
generated when these events are triggered.
EXEC sys.sp_configure 'blocked process threshold' ,1
GO
RECONFIGURE
GO
Cursor Threshold:
Threshold used to determine if the cursor will be processed
synchronously (wait on all the rows to be returned) or asynchronously
(allows users to get data from cursor while it retrieves the data),
depending on the amount of rows returned for the result set.
EXEC sys.sp_configure 'cursor threshold' ,1
GO
RECONFIGURE
GO
Cost Threshold for Parallelism:
Threshold used to figure out when a query should be executed in
parallel versus serially. In other words, when this threshold is
surpassed, the query will be executed using parallelism.
EXEC sys.sp_configure 'cost threshold for parallelism' ,1
GO
RECONFIGURE
GO
Maximum Degree of Parallelism:
Threshold used to determine the maximum number of processors that can
be used to process a single query during a parallel execution.
EXEC sys.sp_configure 'max degree of parallelism' ,1
GO
RECONFIGURE
GO
Query Wait:
Threshold in seconds that determines the amount of time a query waits
on resources before it times out. By default, the query wait time is set
to wait 25 times of the estimated cost of query.
EXEC sys.sp_configure 'query wait' ,60
GO
RECONFIGURE
GO
User Connections: Sets the number of user connections for an instance of SQL Server.
EXEC sys.sp_configure 'user connections' ,1000
GO
RECONFIGURE
GO
