Session 7 - Transactions, Indexes, Optimize Queries, dynamic views, cursors

Question 1

What is the term for when a database searches an indexed value?

Answer 1

SEEK

Question 2

What is the term for when a database searches for a non indexed value?

Answer 2

SCAN

Question 3

What are the characteristics of a transaction?

Answer 3

• Atomicity - Single unit of work
• Consistency - Everything has to be done
• Isolation - Each transaction must be isolated (database locking)
• Durability - Once a transaction is done, it is committed - Permanent

Question 4

What is it called when you write a statement that starts and ends without the user telling the computer that it’s a transaction?

Answer 4

Implicit Transaction.

Question 5

What is it called when you tell the computer when you start and end a transaction?

Answer 5

Explicit Transaction.

Question 6

What are the two ways to end a transaction?

Answer 6

COMMIT TRANSACTION

ROLLBACK TRANSACTION

Question 7

How do you assign in the log the name of a transaction?

Answer 7

BEGIN TRANSACTION tranName WITH MARK

ROLLBACK TRANSACTION tranName

Question 8

What is the net effect if you rollback a transaction that has nested transactions?

Answer 8

Any transactions that were committed within the parent transaction will be rolled back as well.

Question 9

What happens if you rollback a child nested transaction and try to commit the parent transaction?

Answer 9

It will fail because a rollback transaction rolls back EVERYTHING.

Question 10

How do you find out the nested level of a transaction?

Answer 10

SELECT @@TRANCOUNT

Question 11

What are the scope and types of locks?

Answer 11

• Intent Shared (IS)
• Shared (S)
• Update (U)
• Intent Exclusive (IE)
• Shared with intent exclusive (SIX)
• Exclusive (X)

Question 12

What are the elements of shared locks? (SELECT query)

Answer 12

1. Allows concurrent transactions to read (SELECT)
2. No other transactions can modify the data
3. Released as soon as the read operation is completed

Question 13

What are the elements of Update Locks?

Answer 13

1. Used to prevent deadlock
2. Transactions reading data - Shared Lock
3. To update data, must convert to exclusive
4. Cannot convert to Exclusive if there is a Shared Lock.
5. Solution - Use Update Lock; then convert to Exclusive Lock.

Question 14

What are the elements of Exclusive Locks?

Answer 14

1. Prevents access by concurrent transactions

2. Used by INSERT, UPDATE, DELETE

Question 15

What are the elements of Intent Locks?

Answer 15

1. Signal intent to place locks at a lower level.
2. Prevent other transactions from modifying higher level resources
3. Detecting lock conflicts

Question 16

What are the scopes where locks are used?

Answer 16

Rows, Pages and Tables

Question 17

What is the order that locks go?

Answer 17

Shared -> Update -> Exclusive

Question 18

How can you delay a transaction from running for a specified amount of time (EX. 10 Seconds)?

Answer 18

WAITFOR DELAY ‘00:00:10’

Question 19

How can you specify when to run a transaction at a specific time (EX. 10am)?

Answer 19

WAITFOR TIME ‘10:00:00’

Question 20

What are the problems with no concurrency control (ie: no locks, etc)

Answer 20

1. Lost Updates
2. Uncommitted dependency (Dirty Read)
3. Inconsistent analysis (nonrepeatable read)
4. Phantom Reads
5. Missing and double reads caused by row updates
6. Missing one or more rows that were not the target of update

Question 21

What are the transaction isolation levels?

Answer 21

1. Read Committed (Default Isolation Level)
2. Read Uncommitted
3. Repeatable Read
4. Snapshot
5. Serializable

Question 22

What are the transaction isolation level effects for:

Read Uncommitted

Answer 22

Dirty Read - YES
NonRepeatable Read - YES
Phantom - YES

Question 23

What are the transaction isolation level effects for:

Read Committed

Answer 23

Dirty Read - NO
NonRepeatable Read - YES
Phantom - YES

Question 24

What are the transaction isolation level effects for:

Repeatable Read

Answer 24

Dirty Read - NO
NonRepeatable Read - NO
Phantom - YES

Question 25

What are the transaction isolation level effects for: | Snapshot

Answer 25

Dirty Read - NO NonRepeatable Read - NO Phantom - NO

Question 26

What are the transaction isolation level effects for: | Serializable

Answer 26

Dirty Read - NO NonRepeatable Read - NO Phantom - NO

Question 27

What is the syntax for changing the isolation level?

Answer 27

SET TRANSACTION ISOLATION LEVEL [Read Uncommitted, Read Committed, Repeatable Read, Snapshot, Serializable]

Question 28

What is a heap?

Answer 28

A set of rows contained in pages that are connected through an Index Allocation Map, not indexed and not ordered.

Question 29

What is the estimated size of a page within a heap?

Answer 29

8000 characters.

Question 30

When would a heap be a better way to store data?

Answer 30

If you have to input large amounts of data at one time and sort through it later.

Question 31

What is the better way to store data than a heap?

Answer 31

B-Tree (Balance tree)

Question 32

How is a B-Tree organized?

Answer 32

It's a hierarchal design with equal rows being stored in pages and balanced all the way around.

Question 33

What is the syntax to create a clustered index?

Answer 33

CREATE CLUSTERED INDEX indexName ON tableName (RowsToSortBy)

Question 34

What's the syntax to drop an index?

Answer 34

DROP INDEX IndexName ON tableName

Question 35

What's the difference between a clustered index and a primary key (Regarding Indexes)?

Answer 35

A clustered index will just sort the data, but it doesn't care about duplicates, whereas a primary key does care about duplicates.

Question 36

What are the two ways to convert a heap to a B-Tree?

Answer 36

Create a primary key or a clustered index on the table.

Question 37

What is the syntax to create a non clustered index?

Answer 37

CREATE NONCLUSTERED INDEX indexName ON tableName(Columns)

Question 38

What is the syntax to create a non clustered index that is filtered on specific data? (Filtered Index)

Answer 38

CREATE NONCLUSTERED INDEX indexName ON tableName(Columns) WHERE ColumnToFilter = 'filter'

Question 39

What are the different nodes in a B-Tree

Answer 39

Root level/node Intermediate Level Branch Node Leaf level/node

Question 40

What node contains actual data in a B-Tree?

Answer 40

Leaf Level/Node

Question 41

What do the Root level/nodes and Intermediate Level branch nodes contain?

Answer 41

The Key Fields that the index was created on.

Question 42

What is the syntax for creating a non clustered index that also contains other data columns, but not storing them as key fields?

Answer 42

CREATE NONCLUSTERED INDEX indexName ON tableName(KeyColumn) INCLUDES(columnsToInclude)

Question 43

What is the benefit to including columns in a nonclustered index, but not as key fields?

Answer 43

The index doesn't have to store the data in the Root and intermediate nodes, only in the leaf node.

Question 44

What are the different join types regarding query plans?

Answer 44

Hash Match - Least Efficient Nested Loop Match (One Small Table to One Large Table) - Efficient Merge Match (Larger Tables, sorted on join) - Very Efficient

Question 45

Do Hash Match and Nested Loop Match require indexes?

Answer 45

No.

Question 46

How does a nested loop work?

Answer 46

It will query the small table first, then for the rows that are returned, it will do the select query for every row returned. Much more efficient if the bigger table is indexed.

Question 47

When can you get a merge join?

Answer 47

When the two larger tables that are queried are indexed and the data is found from a clustered index scan.

Question 48

What is a SARG and why is it important?

Answer 48

A SARG is a Search ARGument. It is a where clause which can use an index. (Do not use functions in the where clause) A proper SARG will improve your query performance immensely.

Question 49

What are the query cost implications of sorting your query?

Answer 49

It will slow down your query significantly. Do not sort unless you absolutely have to.

Question 50

What are the query cost implications when you run a stored procedure? How can you overcome this?

Answer 50

The first time you run the procedure, the system creates an execution plan and then will reuse that every time after. Use CREATE PROC procName WITH RECOMPILE That will recompile the procedure and create a plan every time the procedure is called.

Question 51

What are all of the hints that you can use with a query?

Answer 51

WITH () --separated with comma: * NOLOCK * READUNCOMMITTED * UPDLOCK * REPEATABLEREAD * SERIALIZABLE * READCOMMITTED * TABLOCK * TABLOCKX * PAGLOCK * ROWLOCK * NOWAIT * READPAST * XLOCK * SNAPSHOT * NOEXPAND * FORCESEEK * FORCESCAN

Question 52

How would you force SQL to use a different join type (Hash, Merge, Loop)

Answer 52

put it before JOIN: LEFT HASH JOIN RIGHT MERGE JOIN INNER LOOP JOIN

Question 53

What is the syntax used to show statistics about the disk usage that happens from a query?

Answer 53

SET STATISTICS IO ON | GO

Question 54

What does STATISTICS IO show you when you run a query?

Answer 54

The tables read Scan (or seek) count (after leaf level in any direction) Logical Reads - Number of pages from cache Physical reads - How often gone to disk read-ahead reads - Pages placed in cache lob logical reads - Large object blocks of data lob physical reads lob read ahead reads

Question 55

What is the syntax used to see the query plan in a table format?

Answer 55

SET SHOWPLAN_ALL ON | GO

Question 56

What are the other SHOWPLANS that can be used?

Answer 56

SHOWPLAN_XML SHOWPLAN_TEXT Both are for use outside of SSMS

Question 57

What does Client Statistics show you?

Answer 57

The number of statements, rows affected, select statements, transactions run by the client. Network statistics Time Statistics

Question 58

What is the syntax to show the CPU time used for a query?

Answer 58

SET STATISTICS TIME ON | GO

Question 59

What is the stored procedure used for running dynamic SQL? What are the parameters?

Answer 59

SYS.sp_executesql @statement, @params, @ParamName = 'value', @Param2 = 'value2' @statement and @params both need to be NVarchar datatypes

Question 60

What is a DMV?

Answer 60

Dynamic Management View or Dynamic Management Function. (SYS.)

Question 61

How can you run a dynamic SQL without running sys.sp_executesql?

Answer 61

Just run: exec ('query statement') Drawback is that this is prone to SQL injection. DO NOT USE!

Question 62

What is the DMV used for seeing information about how an index is used, including seeks, scans, lookups and so forth?

Answer 62

SYS.dm_db_index_usage_stats

Question 63

What is the function used to get a database name?

Answer 63

db_name(database_id)

Question 64

What is the function used to get the database id you are currently using?

Answer 64

db_id()

Question 65

What is the function used to get the object name from an object ID?

Answer 65

object_name(object_id)

Question 66

What is the DMV for getting physical statistics for an index? What are the parameters?

Answer 66

sys.dm_db_index_physical_stats DatabaseID ObjectID (or null if you want all) IndexID (or null if you want all) PartitionNumber (or null if you want all) Mode (Detailed, null, limited or sampled) -- scan level This shows tons of information about indexes including fragmentation, usage, disk and so forth

Question 67

What DMV would I use to be able to show Indexes that the system suggests should be created?

Answer 67

sys.dm_db_missing_index_details | Shows Key columns to include as well as columns that should be included

Question 68

What DMV can you use that shows you indexes that are missing columns?

Answer 68

sys.dm_db_missing_index_columns(indexName)

Question 69

What is a cursor?

Answer 69

It is a row-based operator (allowing you to go RBAR) | FOR or WHILE loop

Question 70

What are the steps for using a cursor? How do you know when the cursor is finished?

Answer 70

1. Declare cursorName CURSOR FOR SELECT statement FROM table 2. OPEN cursorName 3. FETCH NEXT FROM cursorName INTO @VAR WHILE @@FETCH_STATUS = 0 BEGIN STATEMENT FETCH NEXT FROM cursorName INTO @VAR END 4. CLOSE cursorName @@FETCH_STATUS - returns 0 if it is still returning data, anything else if it's failed

Question 71

What is the drawback to scalar functions and what is one way to resolve that?

Answer 71

They can be a large drag on resources. Don't use the functions unless there isn't an alternative

Question 72

What are ways that you can combine multiple DML operations?

Answer 72

``` Merge Use of OR and AND in WHERE clause Other uses of WHERE clause JOINs Procedures ```