Database Reference
In-Depth Information
in SQl Server versions prior to 2012, minimum and maximum Server memory settings controlled only the
size of the buffer pool. You should reserve additional memory for other SQl Server components in versions prior to SQl
Server 2012.
Tip
Finally, check the tempdb configuration and make sure that it is optimal, as we have already discussed in Chapter 12,
“Temporary Tables.”
DataBaSe CONSOLIDatION
it is impossible to avoid discussion about the database consolidation process when we talk about SQl Server
installations hosting multiple databases. even though it is not directly related to the topic of the chapter, i would
like to review several aspects of the database consolidation process here.
there is no universal consolidation strategy that can be used with every project. You should analyze the amount of
data, load, hardware configuration, and business and security requirements when making this decision. however,
as a general rule, you should avoid consolidating oltp and Data Warehouse/reporting databases on the same
server when they are working under a heavy load. Data Warehouse queries usually process large amounts of
data, which leads to heavy i/o activity and flushes the content of the buffer pool. taken together, this negatively
affects the performance of other systems.
listing 27-1 shows you how to get information about buffer pool usage on a per-database basis. moreover, the
sys.dm_io_virtual_file_stats function can provide you with statistics about the i/o activity for each database
file. We will discuss this function in greater detail later in this chapter.
Listing 27-1. Buffer pool usage on per-database basis
Select
database_id as [DB ID]
,db_name(database_id) as [DB Name]
,convert(decimal(11,3),count(*) * 8 / 1024.0) as
[Buffer Pool Size (MB)]
from sys.dm_os_buffer_descriptors with (nolock)
group by database_id
order by [Buffer Pool Size (MB)] desc
option (recompile);
You should also analyze the security requirements when consolidating databases. Some security features, such as
audit, work on the server scope and add performance overhead for all of the databases on the server. transparent
Data encryption (tDe) is another example. even though it is a database-level feature, SQl Server encrypts tempdb
when either of the databases has tDe enabled, which also introduces performance overhead for other systems.
as a general rule, you should avoid consolidating databases with different security requirements on the same
instance of SQl Server. using multiple instances of SQl Server is a better choice, even when such instances run
on the same server.
 
Search WWH ::




Custom Search