Database Reference
In-Depth Information
•
Does the shared environment, whether VM or platform, have adequate resources, or am I
dealing with a configuration issue there or even resource contention from outside forces?
•
Is the database connection between SQL Server and the database application efficient?
•
Does the database design support the fastest data retrieval (and modification for an updatable
database)?
•
Is the user workload, consisting of SQL queries, optimized to reduce the load on SQL Server?
•
What processes are causing the system to slow down as reflected in the measurement of
various wait states, performance counters, and dynamic management objects?
•
Does the workload support the required level of concurrency?
If any of these factors is not configured properly, then the overall system performance may suffer. Let's briefly
examine these factors.
Having another resource-intensive application on the same server can limit the resources available to SQL Server.
Even an application running as a service can consume a good part of the system resources and limit the resources
available to SQL Server. For example, applications may be configured to work with the processor at a higher priority
than SQL Server.
Priority
is the weight given to a resource that pushes the processor to give it greater preference when
executing. To determine the priority of a process, follow these steps:
1.
Launch Windows Task Manager.
2.
Select View
➤
Select Columns.
3.
Select the Base Priority check box.
4.
Click the OK button.
These steps will add the Base Priority column to the list of processes. Subsequently, you will be able to determine
that the SQL Server process (
sqlservr.exe
) by default runs at Normal priority, whereas the Windows Task Manager
process (
taskmgr.exe
) runs at High priority. Therefore, to allow SQL Server to maximize the use of available
resources, you should look for all the nonessential applications/services running on the SQL Server machine and
ensure they are not acting as resource hogs.
Improperly configuring the hardware can prevent SQL Server from gaining the maximum benefit from the
available resources. The main hardware resources to be considered are processor, memory, disk, and network. If the
capacity of a particular hardware resource is small, then it can soon become a performance bottleneck for SQL Server.
While I'm not covering hardware choices, as a part of tuning queries, you do need to understand how and where
you may see performance bottlenecks because of the hardware you have. Chapters 2, 3, and 4 cover some of these
hardware bottlenecks in detail.
You should also look at the configuration of SQL Server, since proper configuration is essential for an optimized
application. There is a long list of SQL Server configurations that defines the generic behavior of a SQL Server
installation. These configurations can be viewed and modified using a system stored procedure, sys.configurations
.
Many of these configurations can also be managed interactively through SQL Server Management Studio.
Since the SQL Server configurations are applicable for the complete SQL Server installation, a standard
configuration is usually preferred. The good news is that, generally, you need not modify the majority of these
configurations; the default settings work best for most situations. In fact, the general recommendation is to keep
most SQL Server configurations at the default values. I discuss the configuration parameters in detail throughout this
book and make a few recommendations for changing some. The same thing applies to database options. The default
settings on the model database are adequate for most systems. You should probably adjust autogrowth settings from
the defaults, but many of the other properties, such as autoclose or autoshrink, should be left off, while others, such as
the automatic creation of statistics, should be left on in most circumstances.
