Databases Reference
In-Depth Information
SQL Server cumulative update, as you would want to i rst install it on a node that does not own the
cluster resources as part of a rolling update.
Next, you want to start gathering some additional information about how your SQL Server instance
is coni gured, which you can do with the query shown in Listing 15-11.
LISTING 15-11:
Instance confi guration values
-- Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Focus on
-- backup compression default
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism
-- max server memory (MB) (set to an appropriate value)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)
This query returns current coni guration values for a fairly large number of instance-level proper-
ties. Some of these properties can be changed using the SSMS graphical user interface, but they can
all be changed using the
sp_configure
system stored procedure.
You should focus on a few key coni guration values, which include backup compression default, clr
enabled, lightweight pooling, max degree of parallelism, max server memory (MB), optimize for ad
hoc workloads, and priority boost. Of these, the i rst two values that you typically want to change
from their default values are backup compression default and optimize for ad hoc workloads (see
Chapter 3), both of which should be enabled by setting them to 1. Next, I suggest setting max server
memory (MB) to an appropriate, non-default value, taking into account which SQL Server com-
ponents are installed and running on your SQL Server instance. The idea behind this is to ensure
that memory is reserved for the operating system and other SQL SERVER components, like SQL
Server Integration Services (SSIS), so there is sufi cient memory for them to operate properly.
In SQL Server 2008 R2 and earlier, the max server memory (MB) setting controlled only the mem-
ory used by the SQL Server buffer pool, but in SQL Server 2012 it controls overall memory usage by
most other Database Engine components, which means you can probably set the max server memory
(MB) setting a little bit higher on SQL Server 2012 than in previous versions (See Chapter 3).
If any databases running on your instance use CLR assemblies, you will have to enable CLR
integration by setting clr enabled to 1. Otherwise, it should not be enabled, as it uses some resources
on your database server and will increase your attack surface. Most other instance coni guration
options should usually be left at their default values, unless you have a good reason to change them
and you know what you are doing.
Search WWH ::
Custom Search