Brainscape's Knowledge GenomeTM

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


See full index

DP-300 > Module 4 - Monitor and Optimize Operational Resources > Flashcards

Module 4 - Monitor and Optimize Operational Resources Flashcards

1
Q

What is the Azure Monitor

A

Platform Monitor that captures and displays standard metrics

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

How can additonal metrics be captured with Azure Monitor

A

Using Monitoring Insights to capture additional data from you VM

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

How can Azure Monitor show detailed metrics appertaining to SQL Server

A

It can’t, Azure Monitor will show VM level information only

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

What additional Data does Azure Monitoring Insights capture from the VM

A

Azure Monitoring Insights add the ability to capture Storage Latency, Available Memory and Disk capacity

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

Where is data from Monitoring Insights stored and how can you query it

A

Monitoring Insights data is stored within Azure Log Analytics and can be queried via KQL (a SQL like langues)
Azure Log Analytics is the primary tool for storing log files

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

What does the SQL VM Resource Provider, provide and how/when can it be installed?

A

SQL VM resource provider provides a dashboard showing a view of data and log file space consumption on disk as well as management of Auto patching and Storage Config.

Installed Automatically when deploying a SQL VM from market place or you can install it seperately on an existing VM

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

What does Perf Mon on a SQL VM allow?

Where can Perf Mon data be stored

A

Perf Mon allows easy monitoring of performance metrics at an OS and SQL level
SQL has its own counters in perfmon
Perform data can be stored locally or forwarded to Azure monitor so you can have single view of multiple servers

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

Metrics gathering by Azure Monitor are a gateway for alerting process, describe Azure Monitor Metrics

A

Azure Monitor Metrics is a powerful subsystem to analize/visualise and alert on metrics
Alerts can notify admins or trigger automatic runbooks/webhooks

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

How can Azure Monitor Metrics alerts for VMs be scoped

A

Azure Monitor Metrics alerts for VMs can be scoped in 3 ways
All VMs in a region in a subscription
All VMs in a region in one or more resource groups in a subscription
A list of VMs in a region in a subscription

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

Give an overview of how to create an Azure Monitor Metrics alert?

A

Create New Alert rule - specify rule conditions

Create action group - specify actions to take upon conditions being met

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

How long do Azure Monitor Metrics get retained for and what should you do if you want them for longer

A

Azure Monitor Metrics get retained for 93 days at which point they are purged unless archived in AZure Storage

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

There a lots of metrics in SQL Server, where can you get OS level metrics of interest, within a VM

A

Some OS Level metrics are available from within SQL (SSMS) others are just available at the OS level.

E.G. Combine Volume stats (data and log file read/write latency) from sys.dm_os_volume_stats DMV with the OS metric seconds per disk read/write which is only available at the OS level.

The above gives a better understanding if an issue is due to storage bottle neck or DB structure issue.

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

Its important to correlate SQL Stats with OS Stats, if you are using a Linux VM what tools can you use to get similar data that is available in PerfMon

A

InfluxDB,
CollectD
Grafena

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

There are various OS metrics that can be used to capture a good baseline with regards to SQL Workload, what are some of these metrics

A 
Processor Total % Time
Paging File Usage
Physical Disk Avg Read/Write per second
Sys/Processor Queue length
SQL Buffer Manager/PLE
SQL Batch Requests per sec
SQL Compilations or Recompilations per sec
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does “Processor Total % Time” do and why is that useful for baselining SQL Server performance

A

Processor Total % Time measures CPU utilization and is good indicator of workload, can identify query performance issues

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

What does “Paging File Usage” do and why is that useful for baselining SQL Server performance

A

Paging File Usage - If SQL Config is correct memory should not be paged out however often apps may cause this to happen resulting in performance degradation.

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

What does “Physical Disk Avg Read/Write per second” do and why is that useful for baselining SQL Server performance

A

Physical Disk Avg Read/Write per second metric to check storage sub system (latency should not be above 20ms - with Premium Storage should be less than 10ms)

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

What does “Sys/Processor Queue length” do and why is that useful for baselining SQL Server performance

A

Sys/Processor Queue length - number of threads waiting on processor, anything greater than 0 indicates CPU pressure

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

What does “SQL Buffer Manager/PLE” do and why is that useful for baselining SQL Server performance

A

SQL Buffer Manager/PLE - How long SQL expects a page to be in memory, whilst there is no ‘good’ number here, if you monitor over time you will be able to identify if the number suddenly drops which would suggest memory pressure.

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

What does “SQL Batch Requests per sec” do and why is that useful for baselining SQL Server performance

A

SQL Batch Requests per sec - helps evalute is system is consistently busy, use with “Proccessor Total % Time” to understand workload and baselines

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

What does “SQL Compilations or Recompilations per sec” do and why is that useful for baselining SQL Server performance

A

SQL Compilations or Recompilations per sec - When SQL has to (re)compile executions plans due no plan being in the cache (or because the plan was invalid) can indicate recompile query hints, memory pressure on plan cache due to lots of ad-hoc queries or memory pressure

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

Describe Wait Statistics?

A

SQL tracks threads forced to wait on unavailable resources.

Can help when tracking specific query problems or looking at baselines

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

Which DMV holds wait stats info

A

Wait Stat info available via sys.dm_os_wait_stats

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

What is Azure SQL Database Intelligent Insights

A

Azure platform built in performance collection allowing analysis of queries

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

What does Azure SQL Database Intelligent Insights use behind the scenes to get it’s data

A

Query Store

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

What are the storage options for Intelligent Insights

A 
Azure Storage (Stored as extended events session files), only viewable on server that created them
Events Hub (Stored in Avro format a Binary of JSON for event reporting)
Log Analytics (Queried using KQL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Which tool in the Azure portal allows quick identification of expensive queries

A

Query Performance Insight allows the administrator to quickly identity expensive queries.

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

Why was it previously difficult to capture data related to query executions and what tool has made it easier

A

It was previously difficult to capture data related to query executions due to it’s transient nature being held in memory. It is now much easier with Query Store

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

What 2 types of data does Query Store collect?

A

Query Store collects Data about queries (plan, query text and # of executions) as well as data about performance (runtime stats for each execution)

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

Query store is designed to us minimal over head - what about the architecture of query store achieves this?

A

Query store writes data to memory which is then flushed to disk on a schedule

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

What are the default settings related to query store

A

Max Size MB = 100MB (often changed to 1 - 2 GB)
Time Based Clean Up = 30 days
Query Capture Mode = Auto (meaning that queries with insignificant times are ignored), old default was ALL

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

Describe plan forcing in query store

A

Plan forcing drives the new Auto tune feature of (Use last good plan)
You would use plan forcing to allow quick mitigation whilst you investigate properly.

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

What is the purpose of locking

A

Locking is used to maintain Atomicity and Consistency?

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

When does blocking occur?

A

Blocking occurs when one process holds a lock on a specific resource (row, page, table, database), and a second process attempts to acquire a lock with an incompatible lock type on the same resource

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

SQL attempts to take the smallest lock possible to allow maximum concurrency, however each lock has a performance overhead - at what point is lock escalation used and what is lock escalation

A

SQL Server tries to balance concurrency with cost. One technique used is called lock escalation. If SQL Server needs to lock more than 5000 rows on a single object in a single statement, it will escalate the multiple row locks to a single table lock.

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

What is deadlocking?

A

When 2 transactions have locks on individual resources and each transaction then tries to take a lock (and ends up being blocked) on the other transcations locked resource.

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

Deadlocking would in theory cause an infinite wait - why doesn’t that happen

A

Deadlocking is resolved by SQL choosing a deadlock victim to be killed and rolled back, this is decided based on the transaction that would require the least amount of rollback.

38
Q

Describe Auto Commit Mode

A

SQL by default is in auto commit mode meaning changes made by a statement would be written to the T-Log (committed) automatically

39
Q

Describe Begin and Complete Tran

A

To give more granular control instead of auto commit.
Once a transaction has been begun it wont be commited to the T-Log until a COMMIT TRAN has been specified - if someone forgets to COMMIT TRAN this can lead to Long running transactions and lots of blocking.

40
Q

Describe Row Versioning

A

When a row versioning isolation level is enabled the database, engine maintains versions of each modified row in TempDB. This is typically used in mixed use workloads, in order to prevent reading queries from blocking queries that are writing to the database.

41
Q

Define the Isolation level READ UNCOMMITED

A

Read Uncommited is the lowest isolation level and allows dirty reads (i.e. one transaction may see changes made by another transaction that are not yet committed)

42
Q

Define the Isolation level READ COMMITTED

A

READ COMMITTED is the default level and allows transactions to read data previously read but not modified by another transaction whilst waiting for the 1st transaction to finish (i.e. releases read lock as soon as select is performed)

43
Q

Define the Isolation level REPEATABLE READ

A

REPEATABLE READ keeps Read and Write locks on selected data until the end of the transaction

44
Q

Define the Isolation level SERIALIZABLE

A

SERIALIZABLE is the highest level with transactions being completed isolated. READ and Write locks on selected data are not released until the end of the transaction

45
Q

Define the Isolation level READ COMMITTED SNAPSHOT

A

Read operations take no row or page locks, each transaction gets a consistent snapshot of data as it was at the start of the query

46
Q

Define the Isolation level SNAPSHOT

A

Provides read consistency through versioning however vulnerable to update conflicts

47
Q

Which DMVs would you use to gather blocking information (and what would be a better approach)

A

Join sys.dm_tran_locks on sys.dm_exec_requests to get detail on blocking however a better approach would be to use extended events.

48
Q

What are the two typical categories that the reasons for blocking could be caused by?

A

Poor Transactional design (forgetting to commit, doing to much in one TX or using a slow linked server)
Poor Schema Design (i.e. update on a column missing an index or poorly designed update query)

49
Q

Define Fragmentation in SQL Server

A

Indexes having pages in which logical ordering of index, based on data value of index key, does not match physical order of pages
Page modifications due to Inserts,Updates,Deletes causing no space on the page and thus causing a page split.

50
Q

What setting could reduce the amount of fragmentation that occurs

A

FillFactor, as it leaves extra space on the page for this type of operation

51
Q

How can fragmentation degrade performance

A

By requiring additional IO to retrieve the data to which the index points - especially true for Index Scans

52
Q

What is TempDB used for?

A 
Storing of Temporary Objectcs
Work tables with intermediate results
Sorting Operations
Row Versioning
And More
53
Q

What type of storage should you use for TempDB

A

Use lowest latency storage with correctly configured files (i.e.
One file per CPU core up to 8 cores - unless you have a workload that will really utilise TempDB)

54
Q

Where can you implement Resource Governor

A

VM, IaaS, MI

55
Q

What is a Resource Governor, Resource Pool and what are the different default pools

A

A resource pool represents resources available.
There is an Internal Pool which is reserved for critical SQL operations as well as another default pool.
User defined pools can also be created

56
Q

When do changes to a resource pool come into affect

A

A change on a resource pool will only effect new sessions (except external pools for ML)

57
Q

All resource pool limits are hard limits with one exception…

A

All limits are hard limits except CPU MIN/MAX % which only applies if there is contention.

58
Q

Which command is used to set; MaxDOP, Legacy Cardinality Esitmation, Last Query Plan Stats and Optimize for AdHoc Worklods

A

Alter Database Scope

59
Q

Which command is used to set; DB Recovery Model, Auto Tuning Option, Auto Create/Update Statistics, Query Store Options, Snapshot Isolation.

A

ALTER DATABASE

60
Q

Which compatability level is Adaptive Query Processing enabled in by default and what does it do?

A

Adaptive query processing is automatically enabled in compatabiility mode 150.
It tries to make query processing more dynamic based on execution context of query

61
Q

With regards to intelligent query processing what are Adaptive Joins

A

Adaptive joins are when the DB engine defers choice of join between HASH and Nested Loop based on # of rows (only when in batch execution mode)

62
Q

With regards to intelligent query processing what does Interleaved Execution do?

A

Interleaved execution gathers and uses the actual row count of a MSTVF before rest of plan is generated

63
Q

With regards to intelligent query processing what does memory grant feedback do?

A

Memory grant feedback detects over or under granted memory and increases or decreased memory accordingly.
Memory Grants are based on row count estimations from statistics

64
Q

What is Table Variable deferred compilation

A

Table Variable Deferred compilation is similar to interleaved execution except done at 1st compilation of query not within the execution plan.

65
Q

What is batch mode on row store and when was it introduced

A

Batch Mode on row store was introduced on SQL 2012 and allows data to be processed in batches and not rows - High CPU usage due to calculations and aggregations will see imporovements

66
Q

With regards to intelligent query processing what is Scalar UDF in-lining improvements

A

Previously Scalar UFD inline had no paralellism and no proper cost in the execution plan - they were also executed row by row.
Now function is transformed into scalar subqueries in the execution plan which creates gains

67
Q

Describe Approx Count Distinct

A

Approximate Count Distinct.
Executing a Distinct Count of rows is expensive on a large table
Approximate Count Distinct is a faster approach by grouping rows - it has a 2% error rate with a 97% probability.

68
Q

Is Query Store available for the PaaS offerings of MySQL and PostgreSQL

A

Yes, but it’s implementation is slightly different

69
Q

What schema is the Query Store data stored in for MySQL and PostgreSQL

A

Data stored in MySQL Schema DB or azure_sys for postgreSQK

70
Q

What is an Index Reorganisation

A

An Index Reorganisation is an operation to defrag leaf level of index by phyiscally re-ordering pages to match logical order from left to right.

71
Q

What are Index pages compacted based on

A

Index pages are compacted based on fill factor

72
Q

What is an index rebuild

A

An Index Rebuild can be performed either online or offline dependant on command and edition of SQL,
When performed offline an index is dropped and recreated
When performed online a new index is created in parallel, the old index is then dropped and the new one renamed

73
Q

What is the fragmentation range used for the types of index de frag operations

A

5-30% Reorganise

30% + Rebuild

74
Q

What features came in 2017 and 2019 SQL for index rebuilds

A

SQL 2017 brought resumable rebuilds

SQL 2019 brought a MAXDop settings to provide more control

75
Q

How can you schedule tasks on a SQL VM

A

Windows Task Scheduler or SQL Agent

76
Q

How can you schedule tasks on Azure SQL DB

A

No access to OS or SQL Agent so automation jobs need to originate from outside DB via
AZ Automation Runbook
Agent Job from another VM
AZ SQL Elastic Jobs

77
Q

How can you schedule tasks with Azure SQL MI

A

Using SQL Agent, no access to OS

78
Q

It is critical to maintain statistics, where are statistics stored and what do they contain

A

Statistics are stored in user db as BLOBS (Binary Large Objects) containing information about the distribution of data values in one or more column of a table or indexed view

79
Q

How does the Query Optimizer use statistics

A

The query optimizer uses statistics to help determine cardinality.

80
Q

What are cardinality estimates used for

A

Cardinality Estimates are used to generate exec plan and determine the type of operation (Seek/Scan) used to get data

81
Q

When and where was Automatic Tuning release

A

on SQL 2017 on-prem or in cloud

82
Q

Auto Tuning uses machine learning against performance metrics to suggest or carry out improvements, what sort of issues can be identified

A

Issues caused by plan regression

83
Q

In the cloud what additional functionality can automatic tuning carry out

A

Automatic tuning in the cloud can create and drop indexes

84
Q

What are the parameters Auto tuning uses when it forces a plan

A 
# of errors in previous plan is higher than recommended plan
CPU gain is > 10 seconds
Plan was forced and continues to be better than previous plan
85
Q

When will a plan revert back to the last known good plan

A

After 15 executions

86
Q

How can you enable Automatic plan correction and what pre req is there

A

Automatic tuning can be enabled via T-SQL and requires the use of the Query Store

87
Q

Where are automatic tuning recommendations stored and where can auto tune be checked to see if it is enabled

A

Auto Tuning recommendations are stored in sys.dm_db_tuning_recommendations
Check auto tune is enabled at sys.database_automatic_tuning_options

88
Q

Where can automatic index management be enabled

A

Only in Azure SQL DB

89
Q

When is automatic implementation carried out

A

At low workload times

90
Q

Describe the mitigations in place should an index have no benefit or if a degradation is seen after index dropped

A

If an index is created and no benefit is yielded the index will be dropped quickly - Indexes that are added are monitored to validate there is only an improvement
If an index is dropped and performance degrades the index will be recreated

DP-300 (4 decks)

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