SQL Server

Question 1

This database mostly known as “the SQL Server Agent database” because it stores information of all SQL Agent jobs, configurations and execution histories. Also used to store information about all backups and restores that are executed and histories

Answer 1

msdb database

Question 2

This database is a template on which all user-created databases are based. All databases must contain a base set of objects known as the database catalog. When a new database is created, the —- is copied to create the requisite objects. Conveniently, objects can be added to the — database.

Answer 2

model database

Question 3

Ultimately, all temporary database objects are removed when the SQL Server service is restarted.

Answer 3

tempdb database

Question 4

The —- database is the primary configuration database in SQL Server. It contains information on all the databases that exist on the server, including the physical database files and their locations.

Answer 4

master database

Question 5

Name the name of the encryption keys in order of hierarchy.

Answer 5

Creating a master key = SMK = Service Master Key.
Creating a certificate in the master database (DMK) (Sometimes ALSO called “master key” = confusing!!)
Creating database encryption key (DEK)
Enable encryption on the database, hence enabling TDK = Transparent Data Encryption (at rest, background encryption).

Question 6

What are the resources that can be locked in SQL Server? These are all the lock “types” in SQL

Answer 6

RID (Row Identifier), Key (column), Page 8kb, Extent (8 consecutive pages), Table (both data and indexes are locked), and enire DB

Question 7

What are the lock modes and name each one.

Answer 7

S = Shared Lock = not modifying data (ie: SELECT) or the Schema
U = Update = prevents deadlock = causes a WAIT
X = exclusive = transaction changes and say, oh, I am actually making an update, so cancel the shared lock, cancel the U lock, and make this an X lock = everyone else will experience a WAIT this is my object now
I = Intent
Sch - Schema ie: a table is being dropped
BU = Bulk Update = database lock

Question 8

describe statements to insert values

Answer 8

INSERT INTO dbo.TableName
VALUES
(‘Fred’,’Andy’,’343’,’453-58-8883’),

Question 9

describe statements to create a table

Answer 9

CREATE TABLE TableName
(
Column1 varchar(20),
Column2 varchar(25),
Column3 int,
SSN varchar(12),
)

Question 10

how to move a table

Answer 10

SELECT * INTO VallesCaldera.dbo.Shippers FROM NorthwindTransfer.dbo.Shippers;

Question 11

What are the 3 main System Views to use

Answer 11

Catalog Views - metadata, objects
Schema Views - ascii compatible (so version free)
Dynamic Management Views - system state

Question 12

Types of triggers?

Answer 12

They are type of stored procedure designed to run automatically after 3 types of data modifications. insert, update, and delete modifications (before and after)

Question 13

cross join and natural join?

Answer 13

cross join is combining 2 tables regardless of whether any columns match; a cross product. A natural means that they are joined based on same name and data types

Question 14

how to get the row count from a table

Answer 14

SELECT COUNT(*) FROM Table1

Question 15

how to get the names from a table that start with “J”

Answer 15

SELECT * FROM dbo.MoreInfo WHERE Store LIKE ‘J%’

Question 16

what are monitoring/tuning tools for a DBA

Answer 16

DMV, extended events, perf mon, standard (pre-built) reports, query execution plan, activity monitor, Query Store

Question 17

How to get the execution plan without running the query?

Answer 17

Execution Plan ( ctrl L )

Question 18

a list of all databases on the SQL Server instance, along with information about the database files, their paths, and names
✑ a list of the queries recently executed that use most of memory, disk, and network resources
What should you use?

Answer 18

SQL Server Data Tools

Question 19

You collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.
You need to examine disk usage, query statistics, and server activity without building custom counters.

Answer 19

SS Data Collector

Question 20

You need to identify missing indexes.

What should you use?

Answer 20

SS Data Collector

Question 21

You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session.
What should you use?

Answer 21

EXEC SP_who3

Question 22

You need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure. LONGEST QUERIES ON INSTANCE

Answer 22

Activity Monitor

Question 23

What does sql do with a query plan after constructing?

Answer 23

puts it in the plan cache

Question 24

what is a transaction that a developer made that is left open=a BEGIN clause with no COMMIT or ROLLBACK

Answer 24

rogue transaction

Question 25

how do you prevent data corruption

Answer 25

enable checksum page and run DBCC commands such as CHECKDB, CHECKCATALOG, CHECKALLOC

Question 26

how can you view the top resource consuming sessions

Answer 26

right click instance name, reports, standard reports, and a list appears

Question 27

explain each checkpoint type

Answer 27

automatic, internal, manual, indirect All of these are based on Recovery Time

Question 28

An --- ---- occurs each time the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval server configuration option.

Answer 28

automatic checkpoint

Question 29

This can be configured recovery time database options.

Answer 29

Indirect checkpoints

Question 30

what is this; in the course of a transaction, new rows are added or removed by another transaction to the records being read

Answer 30

phantom transaction

Question 31

what is a transaction wrap arround

Answer 31

the database transaction counter is cyclic, and the database can grow the transaction number until they have to return to 0, and rather than do that, the database shuts down to maintain data integrity

Question 32

To discover what is preventing log truncation in a given case, use ----

Answer 32

the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view

Question 33

what are the steps if the log is full and db has 9002 error (in read only mode)

Answer 33

Perform a trans log backup, if not enough space for that then change the recovery plan to simple. (After one of those, shrink the log file size (make sure to select "log file" and NOT database) AND take a full backup because there are now no trans logs. Another option is to change the log file size to "Unlimited" if there is a limit set (percentage or max size in MB. Also, an additional log file can be added.

Question 34

Keys that all could be a PK are;

Answer 34

Candidate Keys

Question 35

Entities

Answer 35

are the tables (cats, dogs, users)

Question 36

Attributes

Answer 36

are the columns; payment type, black or grey cat, been to the vets

Question 37

a primary key that is a database produced random number

Answer 37

surrogate key

Question 38

a primary key that is a real world attribute, like email address, social security number

Answer 38

natural key

Question 39

What are CRUD permissions?

Answer 39

Create, Read, Update, Delete

Question 40

When viewing an execution plan, each step is represented by an icon and is called what?

Answer 40

plan operator

Question 41

What is the default path of the default SQL Server trace data file?

Answer 41

\mssql\log

Question 42

Which query store retention option controls how long statistics on inactive queries will be kept?

Answer 42

Stale Query Threshold

Question 43

Columnstore Index

Answer 43

Used on OLAP (IE static data, read only) rather than OLTP

Question 44

OLTP and OLAP

Answer 44

OnLine Transactional Processing, which used clustered and non-clustered indexes, the data is changing, and read (vs OLAP = Analytical which is usually read only or static data)

Question 45

Which statistics component measures the ratio of unique values present in a column?

Answer 45

density, 1 is perfect 0 is none

Question 46

When the database engine needs to retrieve data and looks to cache memory (either cache or disk) it is called what what type of reads

Answer 46

logical read, physical read

Question 47

The _____ (also known as _____ pool) is SQL Server's primary working memory for solving queries.

Answer 47

buffer cache, buffer pool

Question 48

when the database engine finds data in the buffer pool

Answer 48

cache hit

Question 49

The query optimizer tries to estimate the number of rows a query will return, which is called the ______.

Answer 49

cardinality

Question 50

what is another word for session_id?

Answer 50

SPID

Question 51

How to show which databases have TDE implemented?

Answer 51

SELECT [NAME], is_encrypted | FROM master.sys.databases

Question 52

How to show what keys are installed on SQL

Answer 52

SELECT * FROM sys.asymetrical_keys SELECT * FROM sys.symetrical_keys SELECT * FROM certificates

Question 53

What 2 items are set up when building a policy?

Answer 53

a facet and a condition

Question 54

How to create error handling?

Answer 54

Try catch. BEGIN TRY Statement End Try Begin Catch Print ‘This is the error: End Catch

Question 55

What is the ACID test?

Answer 55

Atomictiy, Consitency, Isolation, Durability

Question 56

LCK_M

Answer 56

This wait type happens when a query locks an object while another query is waiting to lock the same object. A common scenario is when a query is trying to modify a row while another query is trying to read the same row.

Question 57

PAGEIOLATCH_SH | PAGEIOLATCH_EX

Answer 57

The most frequently occurring buffer latching situation is when SQL Server is waiting to read a data file page from disc storage SH. and EX is memory (so index is only hope, no disc retrieval, it's in RAM already).

Question 58

OLEDB

Answer 58

This wait type indicates that a SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands, or full-search queries.

Question 59

WRITELOG

Answer 59

it’s waiting to write the contents of the log cache (user delete/update/inserts operations) to the disk where the transaction log is stored and before telling the end user his or her transactions have been committed. Disabling unused indexes will help, but the disk is the bottleneck here, and the transition log should be moved to more appropriate storage.

Question 60

CXPACKET

Answer 60

This wait type is involved in parallel query execution and indicates the SPID is waiting on a parallel process to complete or start. Excessive CXPacket waits may indicate a problem with the WHERE clause in the query. Look at researching and changing Maximum Degree of Parallelism (MAXDOP).

Question 61

ASYNC_NETWORK_IO

Answer 61

The async_network_io wait types point to network-related issues. They are most-often caused by a client application not consuming and processing results from the SQL Server quickly enough. From a SQL Server point of view, there’s nothing you can tune. Engage the network team if there’s a long distance between servers, or the application team to check on application/server resources.

Question 62

DTC

Answer 62

This wait type is not on the local system. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time, and the transaction cannot be concluded until it’s been completed on all of the systems.

Question 63

SOS_SCHEDULER_YIELD

Answer 63

SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type. This doesn’t mean the server is underpowered; it means further research is needed to find which individual task in a query needs more CPU time. Check the Max Degree of Parallelism (MAXDOP) to ensure proper core usage. Ensure high CPU performance from both within Windows and the system BIOS.

Question 64

What is CTE?

Answer 64

``` Common Table Expressions; WITH cte_name column1, column2 AS ( SELECT column2 statement ) , ( SELECT statment ) ```

Question 65

aggregate function

Answer 65

Performs a calculation from a table, such as SUM(column1 + column2)

Question 66

literal

Answer 66

a difinitive value, like when a query uses WHERE USERID = 67

Question 67

allocated memory based on SQL estimate

Answer 67

memory grant

Question 68

When SQL sees (predicts) a lot of processing will be needed, it does this

Answer 68

parallelism

Question 69

how do you clear the plan cache

Answer 69

DBCC CLEARPROCCACHE (shotgun approach though, clears all plans) use sp_recompile 'stored proc name' to clear plan for just that query

Question 70

if an index can be used the query is said to be "---"

Answer 70

sargable

Question 71

show statistics for an object

Answer 71

DBCC_SHOWSTATISTICS(table1)

Question 72

MAXDOP

Answer 72

Maximum degree of parallelsim which is the number of processors to use in a parallel, configured in instance properties, but is over ridden by MAXDOP specification pointer in query.

Question 73

The total number of rows processed, or estimated, at each level of a query plan. (level of ? Microsoft definition....)

Answer 73

cardinality

Question 74

what is the threshold for a column to update statistics when "Auto Update Statistics" is enabled for a database?

Answer 74

20% plus 500 total rows updated (OLD VERSION, newer version is less)

Question 75

TUNNING1 find waiting queries step 1

Answer 75

select * from sys.dm_os_waiting_tasks | where session_ID = 99

Question 76

TUNNING2 find waiting queries step 2 and 3

Answer 76

sys.dm_exec_sessions sys.dm_exec_requests (gets the sql handle)! (add a where session_id = 99)

Question 77

TUNNING3 find waiting queries (plug in the sql handle)

Answer 77

select * from sys.dm_exec_sql_text(SQL Handle) and this will show the query

Question 78

TUNNING4 one useful dbcc for session queiries

Answer 78

DBCC INPUTBUFFER (99) --99 session ID

Question 79

TUNNING5 find query another method

Answer 79

select * from sys.dm_exec_connections

Question 80

This wait type is harmless, and non-tunable. It occurs when the "robot overlord" process hands out work to the other processors during parallelism.

Answer 80

CXCONSUMER (usually with CXPACKET listed also)

Question 81

parameterization

Answer 81

When variables are being used instead of literals (this is better for indexing and reusing plans).

Question 82

Define Async_Network_IO

Answer 82

SQL is keeping up just fine, but is waiting for the app on the other end of a slow pipe/connection.

Question 83

Define BACKUP_*

Answer 83

The backups are running into business time, or perhaps interfering with other nightly maintenance tasks.

Question 84

CXPACKET

Answer 84

Something has gone parallel and some processors are still working while the others remain idle (they aren’t released until ALL are finished). Adjust Maxdop or raise the cost for parallelism

Question 85

LCK*

Answer 85

Could be good lock or bad lock depending on the type. Causes for bad are missing indexes, unused indexes, too aggressive indexing that has a ton of updates. Look at the usage to determine adjustments. X, for exclusive is the red flag because nothing else can occur with that RID, Key, page, extent, table or the whole table.

Question 86

PAGELATCHIO

Answer 86

SQL server is waiting to read data from storage instead of memory. Fix this by looking at which queries are doing the most reads and look for missing indexes on those queries.

Question 87

SOS_SCHEDULER_YEILD

Answer 87

The SQL server is waiting for a CPU scheduled to yield more time. It’s like a referee that allocates processes fairly, but in this case it is taking too much time. This is usually related to processing power, because not all queries are happyWith their access to the CPU

Question 88

WRITELOG

Answer 88

This has to do with your log file not being able to keep up. This could be the discard too slow, or too many rights are taking place. Or it could be that the checkpoints are too large and need to be increased so that just usage can be spread out.

Question 89

What is IO affinity?

Answer 89

Setting specific processes to specific processors, like specific queries or anything that uses high IO.

Question 90

Why not use auto-shrink?

Answer 90

Increases index fragmentation, performance goes down, nightly maintenance takes longer. You will retrieve space, but this should not prevail over not having control. You can manually shrink (ie after a large truncation).

Question 91

What are the replication types?

Answer 91

Publisher and distributor are needed for the subscriber. Use articles. Snapshot - delays are ok, captures a specific moment in time, doesn’t monitor for updates, migrates the whole thing to secondaries. Use when data doesn’t change frequently and outdated info is ok, database is not large, great for reporting instance. Transactional - real time data is needed, so it is immediate Merge - this keeps both servers exact synchronization, it’s two way replication.

Question 92

How can you ensure a sp or query will create a new plan?

Answer 92

Option recompile is added as a “hint”

Question 93

How can I free the plan cache?

Answer 93

DBCC FREEPROCCACHE

Question 94

How can I update statistics?

Answer 94

Sp_updatestats [table, index or view name | Statistic Name] Use FULLSCAN option to have SQL scan the entire table rather than take samples. keep in mind that SQL server does this after a change threshold on its own. Updating stats will cause plans to recompile, so there will be a performance hit.

Question 95

ISO 3100

Answer 95

International Organization for Standardization, is an international standard that provides principles and guidelines for risk management. It outlines a comprehensive approach to identifying, analyzing, evaluating, treating, monitoring and communicating risks across an organization.

Question 96

dd

Answer 96

In the venerable Unix command dd, the disk/data duplicator (or, sometimes, disk destroyer) allows us to copy raw data from one source to another. It's not used to copy individual files like cp. Instead, it lets us read from and write to block devices — for example, physical hard drives.