Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

infrastructure exam 2024 > Configuring SQL Server > Flashcards

Configuring SQL Server Flashcards

1
Q

What is the ‘optimize for ad hoc workloads option’ meant to improve?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is SQL parallelism?

A

the number of processor cores employed to run a single statement for each parallel plan execution.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What does a value of 0 mean in regards to parallelism?

A

that the SQL server uses all available processors, up to 64.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the recommended MaxDOP for a server with single NUMA node? (2 answers

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is parallel plan execution in SQL, and what is its purpose?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

If a SQL server has 100 concurrent users executing queries, and 16 CPU, why might you want to suggest MaxDOP 4 instead of 8?

A

To support the simultaneous long running queries to run more efficiently, instead of throwing everything at them all at once.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the ‘cost of threshold for parallelism option’ used for, what does cost mean, and what is it’s recommended value by Relativity?

A

-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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

When does the ‘cost of threshold for parallelism option’ come into play when a query is to be run in SQL?

A

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).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the tempDB used for?

A

database for temporary use and internal objects created by SQL Server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How many tempDBs should be created in an instance?

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How should a tempDB be configured?

A

-initial size should be 10GB and either set autogrowth to 10% or disable it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Finish the statement:
As the environment grows, in an optimized setup, the Tempdb system database _______________.

A

should be larger than your biggest active Rel workspace eddsdbo.Document table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How is it suggested to store tempDB files?

A

on a fast RAID array, and not the OS partition (RAID 10 suggested)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a RAID?

A

redundant array of independent disks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Why is RAID useful?

A
  • 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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Where and how should the log file (.ldf) be stored?

A

on the same array as the other DB log files,
set initially as 25% of the total of all tempDB files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Why should maximum SQL server memory be set, and what should it be set to?

A

-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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How should the max SQL server memory be set for an active/active SQL cluster on the same box?

A

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

19
Q

Why are data and log files initialized?

A

to overwrite any existing data left on disk from previously deleted files.

20
Q

What actions cause data and log files to become initialized (4), and how can this be a potential problem?

A

-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.

21
Q

What is the purpose of instant file initialization?

A

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.

22
Q

What files cannot be instant initialized?

A

LDFs

23
Q

File Allocation unit size is also called _________.

A

The cluster size

24
Q

What is an allocation unit?

A

smallest amount of space that a file can consume.

25
Q

What is the default allocation size by Windows, and what does Rel recommend it to be set to?

A

4096 bytes,
data and tempDB drives should be formatted with 64k allocation unit size.

26
Q

SQL Server automaticallt increases ______ and ______ files throughout the life of a workspace.

A

database (.mdf) and transaction logs (.ldf)

27
Q

Relativity data files are by default set to autogrow by _________ .

A

10 percent and their logs by 512 MB

28
Q

What kind of databases cannot take advantage of Instant File Initialization?

A

databases enabled for Transparent Data Enryption (TDE)

29
Q

How should databases enabled for TDE be enabled for autogrowth, and why?

A

4096BMB instead of 10% because a larger database can result in application timeouts

30
Q

What happens with autogrowth settings of a new workspace?

A

the template workspace is backed up and restored as the new workspace database, carrying over the autogrowth settings to the new workspace.

31
Q

What kind of tool is recommended to be installed on every Rel SQL server?

A

SQL Server 2012 best practices analyzer

32
Q

The tempdb data files should reside on _______________, separate from that of all other user databases

A

the fastest disks available

33
Q

What kind of files should reside on their own disks, and why?

A

Database log files, so that nothing can interfere with the sequential writes for each database log file.

34
Q

Describe how RAID level 0 works, and potential downside

A
  • disk stripping, data is divided into blocks and spread in a fixed order. Improves read and write performance so that operations can be done independently and at the same time.
  • no fault tolerance; does not provide redundancy
35
Q

Describe how RAID level 1 works, and potential downside

A

Disk mirroring; which provides a redundant, identical copy of a selected disk by writing all data written to the primary disk to the mirror disk.
Has fault tolerance, and generally improves read performance
- may degrade write performance.

36
Q

What can RAID level 0 be used for?

A

pagefile, tempDBs

37
Q

What can RAID level 1 be used for?

A

OS and Logs (.ldf)

38
Q

Describe how RAID level 5 works, and potential downside

A

striping with parity ; data is striped in large blocks across disks in the array, but it writes the parity across all disks which provides data redundancy (2 types of info are always on different disks)
better performance overall than RAID 1, but read performance is decreased when one of the stripes fails.

39
Q

What can RAID level 5 be used for?

A

databases (.mdf) and full text indexes (.ndf)

40
Q

What can RAID level 10 (1+0) be used for?

A

mirroring with striping that uses a stiped array of disks that are then mirrored to another identical set of striped disks.
provides the performance benefits of disk striping with disk redundancy of mirroring (highest read and write performance at expense of using twice as many disks)

41
Q

What RAID type?
Good for non-critical data or stagnantly updated data that is backed up regularly or any data requiring fast write performance at very low cost. Great for testing.

A

RAID 0

42
Q

What RAID type?
Good for data that requires high fault tolerance at relatively low hardware cost (redundancy using parity requires more expensive hardware). Best for log files.

A

RAID 1

43
Q

What RAID type?
Very good for Read only data.

A

RAID 5

44
Q

What RAID type?
Data requiring high performance for both read and write and excellent reliability while trading off storage efficiency and cost.

A

RAID 10

infrastructure exam 2024 (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions