Monitoring and Managing SQL Server

Question 1

VIEW SERVER STATE

Answer 1

enables the ability to view current activity, including user activity and recent expensive queries

Question 2

db_accessadmin

Answer 2

fixed database role. Members of this group can grant or remove access to a database for SQL Server Logins, Windows Logins and Windows Groups

Question 3

db_securityadmin

Answer 3

fixed database role. Members of this role can manage role membership and permissions. Care should be exercised when assigning this role because it can inadvertently lead to privilege escalation for security principles added to the role

Question 4

db_ddladmin

Answer 4

fixed database role. This role enables users assigned the role to run any DDL Statement in the database. This includes CREATE, ALTER, DROP, ENABLE TRIGGER, DISABLE TRIGGER, TRUNCATE TABLE, and UPDATE STATISTICS

Question 5

serveradmin

Answer 5

fixed server role. Members of this role can change server-wide configuration options. Members also have the ability to shut down the server

Question 6

sysadmin

Answer 6

fixed server role. Members of this role can perform any activity on the server

Question 7

securityadmin

Answer 7

fixed server role. Members of this role manage logins and their properties including the ability to change passwords for SQL Server Logins

Question 8

VIEW ANY DEFINITION

Answer 8

Grants the ability to view server-level definitions such as server level audit views

Question 9

VIEW DEFINITION

Answer 9

grants the ability to view database-level definitions such as database level audit views for a specific database

Question 10

Role required to run DBCC

Answer 10

User must either be sysadmin or db_owner

Question 11

REBUILD option with ONLINE = ON will not work on any table with one or more of the following:

Answer 11

XML indexes
Spatial indexes
Columnstore indexes

Question 12

Which is an online defragmentation method?
DBCC DBREINDEX or DBCC INDEXDEFRAG
Also, why should neither be used?

Answer 12

DBCC INDEXDEFRAG can be used for online defragmentation. Neither should be used due to the commands being scheduled for deprecation

Question 13

In order for Auto Update Statistics Asynchronously to take effect, what must also be set?

Answer 13

Auto Update Statistics

Question 14

What does AUTO_UPDATE_STATISTICS_ASYNC do?

Answer 14

When enabled, the query optimizer will not wait for the update of statistics but will run the query first and update the outdated statistics afterwards. The query will run with the current statistics and a background process will start to update the statistics in a different thread.

Question 15

What is a synchronous statistic update?

Answer 15

The query optimizer will update the outdated statistics before compiling the query therefore possibly getting a better plan based on the most current statistics

Question 16

When would you use AUTO_UPDATE_STATISTICS_ASYNC?

Answer 16

When a statistic update due to synchronous statistics requires a significant amount of time and your query cannot wait for it to complete

Question 17

When should you not use AUTO_UPDATE_STATISTICS_ASYNC?

Answer 17

When a sql execution will significantly change the distribution of your data such as truncating a table or running bulk updates

Question 18

When are statistics considered out of date for versions up to 2014?

Answer 18

Threshold is based on the percent of rows changed. If table cardinality was 500 or less, then update for every 500 modifications. Above 500, for every 500 modifications plus 20%

Question 19

When are statistics considered out of date for versions 2016 and beyond?

Answer 19

It is determined by the square root of the product of 1000 and the table cardinality. If a table has 2 million rows then sqrt(1000*2,000,000) = 44721.359. So every 44,721 modifications, the stats will update. Previously, it would have updated every 400,500 modifications.

Question 20

What are the database compatibility levels for each sql server version from 2008 to 2017?

Answer 20

2008 (100)
2012  (110)
2014  (120)
2016  (130)
2017  (140)

Question 21

Describe the difference between Server Audit, Server Audit Specification and Database Audit?

Answer 21

Server Audit is the outcome or output of the auditing operation. Server Audit Specifications are used to specify which objects need to be audited at the server level. Database Audit Specification is at the database level.

Question 22

sys.database_query_store_options

Answer 22

used to set the global configuration of the query store

Question 23

sys.query_store_query

Answer 23

shows information about the aggregated runtime execution statistics for a given query

Question 24

sys.query_context_settings

Answer 24

show details about the query semantics, including language, date format, cursor options, and schema ID

Question 25

sp_query_store_flush_db

Answer 25

stored procedure used when you want to write the in-memory portion of the query store to a physical disk

Question 26

sys.sp_xtp_control_query_exec_stats

Answer 26

used with natively complied stored procedures for a database instance. It enables per-query statistics information to be collected

Question 27

sys.query_store_plan

Answer 27

used to discover the execution plan used by a specific query. Because execution plans for a query can change over time and the Query Store stores historical data, you can potentially identify optimal execution plans for queries to improve performance by used this DMV

Question 28

sys.query_sotre_runtime_stats_interval

Answer 28

enables you to see the start and stop time of an interval during which query execution plan statistics were collected. You can correlate this data with other performance measures to potentially improve database performance