Brainscape's Knowledge GenomeTM

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


See full index

Cool Vocab > SQL Server > Flashcards

SQL Server Flashcards

1
Q

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

A

msdb database

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

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.

A

model database

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

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

A

tempdb database

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

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.

A

master database

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

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

A

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

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

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

A

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

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

What are the lock modes and name each one.

A

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

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

describe statements to insert values

A

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

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

describe statements to create a table

A 
CREATE TABLE TableName
(
Column1 varchar(20),
Column2 varchar(25),
Column3 int,
SSN varchar(12),
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

how to move a table

A

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

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

What are the 3 main System Views to use

A

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

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

Types of triggers?

A

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

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

cross join and natural join?

A

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

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

how to get the row count from a table

A

SELECT COUNT(*) FROM Table1

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

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

A

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

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

what are monitoring/tuning tools for a DBA

A

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

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

How to get the execution plan without running the query?

A

Execution Plan ( ctrl L )

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

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?

A

SQL Server Data Tools

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

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.

A

SS Data Collector

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

You need to identify missing indexes.

What should you use?

A

SS Data Collector

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

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?

A

EXEC SP_who3

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

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

A

Activity Monitor

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

What does sql do with a query plan after constructing?

A

puts it in the plan cache

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

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

A

rogue transaction

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

how do you prevent data corruption

A

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

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

how can you view the top resource consuming sessions

A

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

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

explain each checkpoint type

A

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

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

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.

A

automatic checkpoint

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

This can be configured recovery time database options.

A

Indirect checkpoints

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

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

A

phantom transaction

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

what is a transaction wrap arround

A

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

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

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

A

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

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

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

A

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.

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

Keys that all could be a PK are;

A

Candidate Keys

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

Entities

A

are the tables (cats, dogs, users)

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

Attributes

A

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

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

a primary key that is a database produced random number

A

surrogate key

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

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

A

natural key

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

What are CRUD permissions?

A

Create, Read, Update, Delete

40
Q

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

A

plan operator

41
Q

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

A

\mssql\log

42
Q

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

A

Stale Query Threshold

43
Q

Columnstore Index

A

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

44
Q

OLTP and OLAP

A

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)

45
Q

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

A

density, 1 is perfect 0 is none

46
Q

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

A

logical read, physical read

47
Q

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

A

buffer cache, buffer pool

48
Q

when the database engine finds data in the buffer pool

A

cache hit

49
Q

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

A

cardinality

50
Q

what is another word for session_id?

A

SPID

51
Q

How to show which databases have TDE implemented?

A

SELECT [NAME], is_encrypted

FROM master.sys.databases

52
Q

How to show what keys are installed on SQL

A

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

53
Q

What 2 items are set up when building a policy?

A

a facet and a condition

54
Q

How to create error handling?

A

Try catch.

BEGIN TRY
Statement
End Try

Begin Catch
Print ‘This is the error:
End Catch

55
Q

What is the ACID test?

A

Atomictiy, Consitency, Isolation, Durability

56
Q

LCK_M

A

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.

57
Q

PAGEIOLATCH_SH

PAGEIOLATCH_EX

A

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

58
Q

OLEDB

A

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.

59
Q

WRITELOG

A

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.

60
Q

CXPACKET

A

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

61
Q

ASYNC_NETWORK_IO

A

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.

62
Q

DTC

A

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.

63
Q

SOS_SCHEDULER_YIELD

A

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.

64
Q

What is CTE?

A 
Common Table Expressions;
WITH cte_name column1, column2
AS
(
SELECT column2 statement
)
,
(
SELECT statment
)
65
Q

aggregate function

A

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

66
Q

literal

A

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

67
Q

allocated memory based on SQL estimate

A

memory grant

68
Q

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

A

parallelism

69
Q

how do you clear the plan cache

A

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

70
Q

if an index can be used the query is said to be “—”

A

sargable

71
Q

show statistics for an object

A

DBCC_SHOWSTATISTICS(table1)

72
Q

MAXDOP

A

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.

73
Q

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

A

cardinality

74
Q

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

A

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

75
Q

TUNNING1 find waiting queries step 1

A

select * from sys.dm_os_waiting_tasks

where session_ID = 99

76
Q

TUNNING2 find waiting queries step 2 and 3

A

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

77
Q

TUNNING3 find waiting queries (plug in the sql handle)

A

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

78
Q

TUNNING4 one useful dbcc for session queiries

A

DBCC INPUTBUFFER (99) –99 session ID

79
Q

TUNNING5 find query another method

A

select * from sys.dm_exec_connections

80
Q

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

A

CXCONSUMER (usually with CXPACKET listed also)

81
Q

parameterization

A

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

82
Q

Define Async_Network_IO

A

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

83
Q

Define BACKUP_*

A

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

84
Q

CXPACKET

A

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

85
Q

LCK*

A

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.

86
Q

PAGELATCHIO

A

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.

87
Q

SOS_SCHEDULER_YEILD

A

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

88
Q

WRITELOG

A

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.

89
Q

What is IO affinity?

A

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

90
Q

Why not use auto-shrink?

A

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

91
Q

What are the replication types?

A

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.

92
Q

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

A

Option recompile is added as a “hint”

93
Q

How can I free the plan cache?

A

DBCC FREEPROCCACHE

94
Q

How can I update statistics?

A

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.

95
Q

ISO 3100

A

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.

96
Q

dd

A

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.

Cool Vocab (4 decks)

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