Databases Reference
In-Depth Information
even web servers. Unfortunately, power management is not such a great option for database servers.
That's because when the processor sees a sudden increase in workload, it responds by increasing the
clock speed back to full speed. That sounds good so far, but this response to the spike in the work-
load does not happen quickly enough to avoid a negative effect on query performance. Some short
duration, relatively inexpensive queries may not even trigger the throttle-up mechanism, so they are
executed while the processor is still running at reduced speed.
In my experience, I have typically seen a 20-25% hit to performance for OLTP workloads when
using the Windows default Balanced power plan instead of the High Performance power plan.
It depends on which processor you are using, with Intel Nehalem and Westmere processors
(see Chapter 2, “Demystifying Hardware”) being particularly vulnerable. Even with the High
Performance power plan, it is still possible that your database server is being affected by hardware
power management, controlled from the main system BIOS.
To avoid this problem, i rst make sure your database servers are using the High Performance
power plan, not the Balanced power plan. This setting can be changed dynamically, with no
mine the actual clock speed at which your processors are running. If you are using the High
Performance power plan and your processor is still not running at full speed, you need to go into
the main system BIOS and change its power management settings to either OS control, or to be
disabled completely. Depending on your organization, you may have to get your system adminis-
trator to make this change for you, as it requires a reboot of the server to get into the main BIOS
coni guration.
Now that you know a little more about your hardware and whether you are getting the full benei t
of the money you spent on it, it is time to collect some information about the SQL Server Services
that are installed on the instance. In SQL Server 2008 R2 Service Pack 1 and later, and in SQL
Server 2012, you can learn quite a bit about which SQL Server Services are installed and how they
are coni gured and running from the query shown in Listing 15-7.
LISTING 15-7:
SQL Server Services information
-- SQL Server Services information from SQL Server 2012
SELECT servicename, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
-- Gives you information about your installed SQL Server Services,
-- whether they are clustered, and which node owns the cluster resources
The preceding query tells you exactly which SQL Server Services are installed, their startup type,
whether they are running, which account credentials they are using, when they last started, whether
they are clustered, and what node they are running on in the cluster. This is all good information to
know, and it is very easy to i nd out with this new DMV. Next, you can get some information about
the SQL Server Error Log with the query shown in Listing 15-8.
Search WWH ::
Custom Search