Configuring SQL Server Flashcards
What is the ‘optimize for ad hoc workloads option’ meant to improve?
improves efficiency of plan caches for workloads with many single use ad hoc batches. After a batch is compiled for the first time, it stores a small compiled plan stub that will relieve memory pressure by preventing the plan cache to fill with those which are not reused.
What is SQL parallelism?
the number of processor cores employed to run a single statement for each parallel plan execution.
What does a value of 0 mean in regards to parallelism?
that the SQL server uses all available processors, up to 64.
What are the recommended MaxDOP for a server with single NUMA node? (2 answers
a) 8, if the total number of CPU is greater than >= to 16 CPU.
b) half the number of available CPU, if less than 16
What is parallel plan execution in SQL, and what is its purpose?
aims to separate big tasks into more than one small task, which will be completed by the discrete threads.
This allows response time to be reduced since more than one task will be performed at once.
If a SQL server has 100 concurrent users executing queries, and 16 CPU, why might you want to suggest MaxDOP 4 instead of 8?
To support the simultaneous long running queries to run more efficiently, instead of throwing everything at them all at once.
What is the ‘cost of threshold for parallelism option’ used for, what does cost mean, and what is it’s recommended value by Relativity?
-it specifies the threshold at which SQL Server created and runs parallel plans for queries.
-cost refers to an estimated elapsed time required to run the serial plan,
-50
When does the ‘cost of threshold for parallelism option’ come into play when a query is to be run in SQL?
SQL Server creates a parallel plan when the estimated cost to run a serial plan for the same query is higher than the value set (50).
What is the tempDB used for?
database for temporary use and internal objects created by SQL Server
How many tempDBs should be created in an instance?
one tempDB per CPU, up to 8. (most environments work well with 8, but additional tempDBs can be added if contention is still observed)
How should a tempDB be configured?
-initial size should be 10GB and either set autogrowth to 10% or disable it.
Finish the statement:
As the environment grows, in an optimized setup, the Tempdb system database _______________.
should be larger than your biggest active Rel workspace eddsdbo.Document table.
How is it suggested to store tempDB files?
on a fast RAID array, and not the OS partition (RAID 10 suggested)
What is a RAID?
redundant array of independent disks
Why is RAID useful?
- it is a way of storing the same data in different places on multiple hard disks or sold state drives (SSDs) to protect data in case of drive failure.
- appears on the OS as a single logical drive, while actually employing disk mirroring/stripping which allows data to exist in more than one place.
Where and how should the log file (.ldf) be stored?
on the same array as the other DB log files,
set initially as 25% of the total of all tempDB files
Why should maximum SQL server memory be set, and what should it be set to?
-to prevent the SQL server buffer pool from using more than it’s specified amount of memory, so that the remainder can be used for OS
-set to 90% total available RAM
How should the max SQL server memory be set for an active/active SQL cluster on the same box?
set each SQL instance to half of 90% the total RAM.
ex - 32 GB total, split to 14/14 which leaves 4 GB for OS on the shared box
Why are data and log files initialized?
to overwrite any existing data left on disk from previously deleted files.
What actions cause data and log files to become initialized (4), and how can this be a potential problem?
-creating a database,
-add log or data file to existing database
-increase the size of an existing file (including autogrowth operations)
-restore a database of filegroup
when overwriting existing data, this can take longer to complete.
What is the purpose of instant file initialization?
creates faster execution of file operations and reclaims used disk space without populating that space with 0s. instead, disk content is overwritten as new data is written to the files.
What files cannot be instant initialized?
LDFs
File Allocation unit size is also called _________.
The cluster size
What is an allocation unit?
smallest amount of space that a file can consume.
