Database Reference
In-Depth Information
You should check what software is installed and what processes are running on the server. Non-essential
processes use memory and contribute to server CPU load. Think about anti-virus software as an example. It is better
to protect the server from viruses by restricting user access and revoking administrator permissions, rather than to
have anti-virus software constantly running on the server. If company policy requires that you have anti-virus up and
running, make sure that the system and user databases are excluded from the scan.
Using development and troubleshooting tools locally on the server is another commonly encountered mistake.
Developers and database administrators often run Management Studio, SQL Profiler, and other tools on a server
during deployment and troubleshooting. Those tools reduce the amount of memory available to SQL Server and
contribute to unnecessary load. It is always better to access SQL Server remotely whenever possible.
Also check if SQL Server is virtualized. Virtualization helps reduce IT costs, improves the availability of the
system, and simplifies management. However, virtualization adds another layer of complexity during performance
troubleshooting. Work with system administrators, or use third-party tools, to make sure that the host is not
overloaded, even when performance metrics in a guest virtual machine appear normal.
Another common problem related to virtualization is resource over-allocation. As an example, it is possible
to configure a host in such a way that the total amount of memory allocated for all guest virtual machines exceeds
the amount of physical memory installed on the host. That configuration leads to artificial memory pressure and
introduces performance issues for a virtualized SQL Server. Again, you should work with system administrators to
address such situations.
SQL Server Configuration
It is typical to have multiple databases hosted on a SQL Server instance. Database consolidation helps lower IT costs
by reducing the number of servers that you must license and maintain. All those databases, however, use the same
pool of SQL Server resources, contribute to its load, and affect each other. Heavy SQL Server workload from one
system can negatively impact the performance of other systems.
You can analyze such conditions by examining resource-intensive and frequently executed queries on the
server scope. If you detect a large number of such queries coming from different databases, you may consider
optimizing all of them or to separate the databases among different servers. We will discuss how to detect such
queries later in this chapter.
■
Starting with SQl Server 2008, you can throttle Cpu activity and query execution memory for sessions using
resource governor. in addition, SQl Server 2014 allows you to throttle i/o activity. resource governor is available in the
enterprise edition only, and it does not allow you to throttle buffer pool usage.
Tip
You can read more about resource governor at:
http://msdn.microsoft.com/en-us/library/bb933866.aspx
.
You should also check if multiple SQL Server instances are running on the same server and how they affect the
performance of each other. This condition is a bit trickier to detect and requires you to analyze various performance
counters and DMOs from multiple instances. One of the most common problems in this situation happens when
multiple SQL Server instances compete for memory, introducing memory pressure on each other. It might be beneficial
to set and fine-tune the minimum and maximum memory settings for each instance based on requirements and load.
It is also worth noting that various Microsoft and third-party products often install separate SQL Server instances
without your knowledge. Always check to see if this is the case on non-dedicated servers.
