Identify and Resolve Concurrency Problems Flashcards
What is Live Locking?
Shared locks prevent another process from acquiring exclusive locks (but one process wins, then the next process wins)
What is Deadlocking?
Two processes compete for the same resource
What is Blocking?
The second connection is blocked
How is a deadlock resolved?
One transaction is decided to be the “victim” by the server and is cancelled which opens up the resource.
How can we see where the deadlocks are occurring?
EXEC SP_WHO2
Column BLKBY shows who the process is being blocked by.
What is the System DM view that shows system locking?
sys.dm_tran_locks
What is the system DM that shows what queries a user is running and the wait_type that is occuring?
sys.dm_exec_requests
How do you see history of deadlocking?
Turn on Trace Flags using: DBCC traceon(1204, -1) DBCC traceon(1222, -1)
When do the settings for trace files get turned off?
If you manually run the DBCC command to shut it off or when you reboot the server.
How would you get SQL Server to automatically start up trace files when you reboot?
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
How do you see the trace log?
Management -> SQL Server Logs
What affects SQL Server Performance?
CPU Memory File I/O Blocking, Deadlocking, Locking Long queries/Stored Procedures
What is the system DM view that shows CPU usage?
sys.dm_os_schedulers
What does Current_Workers_count mean in sys.dm_os_schedulers?
The number of workers associated with each scheduler.
What does Work_Queue_count mean in sys.dm_os_schedulers?
the number of tasks waiting to be run. A high number here means that the disk is being overworked.
What does Pending_Disk_io_count mean in sys.dm_os_schedulers?
Disk activity waiting to be completed.
What is the system DM view that shows the Buffer Pool / data cache?
sys.dm_os_buffer_descriptors
How would you get cache in MB using sys.dm_os_buffer_descriptors?
select count(database_id)*8 / 1024.0 as [Cache in Mb], database_id
from sys.dm_os_buffer_descriptors
group by database_id
What is the DM view you would use for Memory Issues?
sys.sysperfinfo
How would you use sys.sysperfinfo to monitor memory issues?
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.
What tool gives you information about processes that were blocked and how deadlocks are resolved by Sql Server?
SQL Server Profiler Trace
Which tool monitors SQL Server instances over long periods of time (IE to find the most resource consuming queries?
Performance Monitor Data Collector Set
What tool displays information about SQL Server processes and how these processes affect the current instance of SQL Server?
Activity Monitor. This tool does not support saving monitoring information
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?
Extended Events
