Identify and Resolve Concurrency Problems

Question 1

What is Live Locking?

Answer 1

Shared locks prevent another process from acquiring exclusive locks (but one process wins, then the next process wins)

Question 2

What is Deadlocking?

Answer 2

Two processes compete for the same resource

Question 3

What is Blocking?

Answer 3

The second connection is blocked

Question 4

How is a deadlock resolved?

Answer 4

One transaction is decided to be the “victim” by the server and is cancelled which opens up the resource.

Question 5

How can we see where the deadlocks are occurring?

Answer 5

EXEC SP_WHO2

Column BLKBY shows who the process is being blocked by.

Question 6

What is the System DM view that shows system locking?

Answer 6

sys.dm_tran_locks

Question 7

What is the system DM that shows what queries a user is running and the wait_type that is occuring?

Answer 7

sys.dm_exec_requests

Question 8

How do you see history of deadlocking?

Answer 8

Turn on Trace Flags using: 
DBCC traceon(1204, -1)
DBCC traceon(1222, -1)

Question 9

When do the settings for trace files get turned off?

Answer 9

If you manually run the DBCC command to shut it off or when you reboot the server.

Question 10

How would you get SQL Server to automatically start up trace files when you reboot?

Answer 10

Go into SQL Server Configuration Manager
Right click the instance and go to Properties and then go to Startup Parameters

In the startup parameter type: -T1204 or -T1222

Question 11

How do you see the trace log?

Answer 11

Management -> SQL Server Logs

Question 12

What affects SQL Server Performance?

Answer 12

CPU
Memory
File I/O
Blocking, Deadlocking, Locking
Long queries/Stored Procedures

Question 13

What is the system DM view that shows CPU usage?

Answer 13

sys.dm_os_schedulers

Question 14

What does Current_Workers_count mean in sys.dm_os_schedulers?

Answer 14

The number of workers associated with each scheduler.

Question 15

What does Work_Queue_count mean in sys.dm_os_schedulers?

Answer 15

the number of tasks waiting to be run. A high number here means that the disk is being overworked.

Question 16

What does Pending_Disk_io_count mean in sys.dm_os_schedulers?

Answer 16

Disk activity waiting to be completed.

Question 17

What is the system DM view that shows the Buffer Pool / data cache?

Answer 17

sys.dm_os_buffer_descriptors

Question 18

How would you get cache in MB using sys.dm_os_buffer_descriptors?

Answer 18

select count(database_id)*8 / 1024.0 as [Cache in Mb], database_id
from sys.dm_os_buffer_descriptors
group by database_id

Question 19

What is the DM view you would use for Memory Issues?

Answer 19

sys.sysperfinfo

Question 20

How would you use sys.sysperfinfo to monitor memory issues?

Answer 20

select * from sys.sysperfinfo
where object_name like ‘SQLServer:Buffer Manager%’
order by counter_name

Look at Pages and Target pages and make sure that those values don’t change over time.

Buffer cache hit ratio shows the amount of times cache was able to retrieve values. The higher the better.

Page Life Expectancy shows how long a page is active. Anything under 5 minutes in a Prod system can show issues.

Question 21

What tool gives you information about processes that were blocked and how deadlocks are resolved by Sql Server?

Answer 21

SQL Server Profiler Trace

Question 22

Which tool monitors SQL Server instances over long periods of time (IE to find the most resource consuming queries?

Answer 22

Performance Monitor Data Collector Set

Question 23

What tool displays information about SQL Server processes and how these processes affect the current instance of SQL Server?

Answer 23

Activity Monitor. This tool does not support saving monitoring information

Question 24

What tool enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem but requires extra development and setup?

Answer 24

Extended Events

Question 25

Why would you use DELAYED_DURABILITY = ON (in WITH statement within Update transaction)?

Answer 25

This will allow memory optimized data to delay the writes to the transaction log by writing to the log asynchronously and as a batch

Question 26

How do you allow a database to use DELAYED_DURABILITY?

Answer 26

SET DELAYED_DURABILITY = ON

Question 27

How can you change statement-terminating errors into batch terminating errors?

Answer 27

SET XACT_ABORT ON