Module 4 - Monitor and Optimize Operational Resources Flashcards
What is the Azure Monitor
Platform Monitor that captures and displays standard metrics
How can additonal metrics be captured with Azure Monitor
Using Monitoring Insights to capture additional data from you VM
How can Azure Monitor show detailed metrics appertaining to SQL Server
It can’t, Azure Monitor will show VM level information only
What additional Data does Azure Monitoring Insights capture from the VM
Azure Monitoring Insights add the ability to capture Storage Latency, Available Memory and Disk capacity
Where is data from Monitoring Insights stored and how can you query it
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
What does the SQL VM Resource Provider, provide and how/when can it be installed?
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
What does Perf Mon on a SQL VM allow?
Where can Perf Mon data be stored
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
Metrics gathering by Azure Monitor are a gateway for alerting process, describe Azure Monitor Metrics
Azure Monitor Metrics is a powerful subsystem to analize/visualise and alert on metrics
Alerts can notify admins or trigger automatic runbooks/webhooks
How can Azure Monitor Metrics alerts for VMs be scoped
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
Give an overview of how to create an Azure Monitor Metrics alert?
Create New Alert rule - specify rule conditions
Create action group - specify actions to take upon conditions being met
How long do Azure Monitor Metrics get retained for and what should you do if you want them for longer
Azure Monitor Metrics get retained for 93 days at which point they are purged unless archived in AZure Storage
There a lots of metrics in SQL Server, where can you get OS level metrics of interest, within a VM
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.
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
InfluxDB,
CollectD
Grafena
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
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
What does “Processor Total % Time” do and why is that useful for baselining SQL Server performance
Processor Total % Time measures CPU utilization and is good indicator of workload, can identify query performance issues
What does “Paging File Usage” do and why is that useful for baselining SQL Server performance
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.
What does “Physical Disk Avg Read/Write per second” do and why is that useful for baselining SQL Server performance
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)
What does “Sys/Processor Queue length” do and why is that useful for baselining SQL Server performance
Sys/Processor Queue length - number of threads waiting on processor, anything greater than 0 indicates CPU pressure
What does “SQL Buffer Manager/PLE” do and why is that useful for baselining SQL Server performance
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.
What does “SQL Batch Requests per sec” do and why is that useful for baselining SQL Server performance
SQL Batch Requests per sec - helps evalute is system is consistently busy, use with “Proccessor Total % Time” to understand workload and baselines
What does “SQL Compilations or Recompilations per sec” do and why is that useful for baselining SQL Server performance
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
Describe Wait Statistics?
SQL tracks threads forced to wait on unavailable resources.
Can help when tracking specific query problems or looking at baselines
Which DMV holds wait stats info
Wait Stat info available via sys.dm_os_wait_stats
What is Azure SQL Database Intelligent Insights
Azure platform built in performance collection allowing analysis of queries
What does Azure SQL Database Intelligent Insights use behind the scenes to get it’s data
Query Store
What are the storage options for Intelligent Insights
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)
Which tool in the Azure portal allows quick identification of expensive queries
Query Performance Insight allows the administrator to quickly identity expensive queries.
Why was it previously difficult to capture data related to query executions and what tool has made it easier
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
What 2 types of data does Query Store collect?
Query Store collects Data about queries (plan, query text and # of executions) as well as data about performance (runtime stats for each execution)
Query store is designed to us minimal over head - what about the architecture of query store achieves this?
Query store writes data to memory which is then flushed to disk on a schedule
What are the default settings related to query store
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
Describe plan forcing in query store
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.
What is the purpose of locking
Locking is used to maintain Atomicity and Consistency?
When does blocking occur?
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
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
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.
What is deadlocking?
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.
Deadlocking would in theory cause an infinite wait - why doesn’t that happen
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.
Describe Auto Commit Mode
SQL by default is in auto commit mode meaning changes made by a statement would be written to the T-Log (committed) automatically
Describe Begin and Complete Tran
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.
Describe Row Versioning
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.
Define the Isolation level READ UNCOMMITED
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)
Define the Isolation level READ COMMITTED
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)
Define the Isolation level REPEATABLE READ
REPEATABLE READ keeps Read and Write locks on selected data until the end of the transaction
Define the Isolation level SERIALIZABLE
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
Define the Isolation level READ COMMITTED SNAPSHOT
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
Define the Isolation level SNAPSHOT
Provides read consistency through versioning however vulnerable to update conflicts
Which DMVs would you use to gather blocking information (and what would be a better approach)
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.
What are the two typical categories that the reasons for blocking could be caused by?
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)
Define Fragmentation in SQL Server
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.
What setting could reduce the amount of fragmentation that occurs
FillFactor, as it leaves extra space on the page for this type of operation
How can fragmentation degrade performance
By requiring additional IO to retrieve the data to which the index points - especially true for Index Scans
What is TempDB used for?
Storing of Temporary Objectcs Work tables with intermediate results Sorting Operations Row Versioning And More
What type of storage should you use for TempDB
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)
Where can you implement Resource Governor
VM, IaaS, MI
What is a Resource Governor, Resource Pool and what are the different default pools
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
When do changes to a resource pool come into affect
A change on a resource pool will only effect new sessions (except external pools for ML)
All resource pool limits are hard limits with one exception…
All limits are hard limits except CPU MIN/MAX % which only applies if there is contention.
Which command is used to set; MaxDOP, Legacy Cardinality Esitmation, Last Query Plan Stats and Optimize for AdHoc Worklods
Alter Database Scope
Which command is used to set; DB Recovery Model, Auto Tuning Option, Auto Create/Update Statistics, Query Store Options, Snapshot Isolation.
ALTER DATABASE
Which compatability level is Adaptive Query Processing enabled in by default and what does it do?
Adaptive query processing is automatically enabled in compatabiility mode 150.
It tries to make query processing more dynamic based on execution context of query
With regards to intelligent query processing what are Adaptive Joins
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)
With regards to intelligent query processing what does Interleaved Execution do?
Interleaved execution gathers and uses the actual row count of a MSTVF before rest of plan is generated
With regards to intelligent query processing what does memory grant feedback do?
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
What is Table Variable deferred compilation
Table Variable Deferred compilation is similar to interleaved execution except done at 1st compilation of query not within the execution plan.
What is batch mode on row store and when was it introduced
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
With regards to intelligent query processing what is Scalar UDF in-lining improvements
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
Describe Approx Count Distinct
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.
Is Query Store available for the PaaS offerings of MySQL and PostgreSQL
Yes, but it’s implementation is slightly different
What schema is the Query Store data stored in for MySQL and PostgreSQL
Data stored in MySQL Schema DB or azure_sys for postgreSQK
What is an Index Reorganisation
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.
What are Index pages compacted based on
Index pages are compacted based on fill factor
What is an index rebuild
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
What is the fragmentation range used for the types of index de frag operations
5-30% Reorganise
30% + Rebuild
What features came in 2017 and 2019 SQL for index rebuilds
SQL 2017 brought resumable rebuilds
SQL 2019 brought a MAXDop settings to provide more control
How can you schedule tasks on a SQL VM
Windows Task Scheduler or SQL Agent
How can you schedule tasks on Azure SQL DB
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
How can you schedule tasks with Azure SQL MI
Using SQL Agent, no access to OS
It is critical to maintain statistics, where are statistics stored and what do they contain
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
How does the Query Optimizer use statistics
The query optimizer uses statistics to help determine cardinality.
What are cardinality estimates used for
Cardinality Estimates are used to generate exec plan and determine the type of operation (Seek/Scan) used to get data
When and where was Automatic Tuning release
on SQL 2017 on-prem or in cloud
Auto Tuning uses machine learning against performance metrics to suggest or carry out improvements, what sort of issues can be identified
Issues caused by plan regression
In the cloud what additional functionality can automatic tuning carry out
Automatic tuning in the cloud can create and drop indexes
What are the parameters Auto tuning uses when it forces a plan
# 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
When will a plan revert back to the last known good plan
After 15 executions
How can you enable Automatic plan correction and what pre req is there
Automatic tuning can be enabled via T-SQL and requires the use of the Query Store
Where are automatic tuning recommendations stored and where can auto tune be checked to see if it is enabled
Auto Tuning recommendations are stored in sys.dm_db_tuning_recommendations
Check auto tune is enabled at sys.database_automatic_tuning_options
Where can automatic index management be enabled
Only in Azure SQL DB
When is automatic implementation carried out
At low workload times
Describe the mitigations in place should an index have no benefit or if a degradation is seen after index dropped
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
