Transcript

-We’ve covered a lot of ground in this course. But one of the cool things about SQL Server is, there’s just about always more cool stuff to learn!

The tools that we covered in this course are things that I think are super practical and I want you to get out there and start using them right away.

The more you use them, the more comfortable you’ll be running them, the more quickly you’ll understand the output and the better you’ll get at solving blocking problems in SQL Server.

Sys.dm_os_wait_stats

I love using sys.dm_os_wait_stats to quickly tell me: does this instance have a blocking problem?

SQL Server Agent performance alerts

The SQL Server Agent, the alerts in there are really easy to create a super simple alert to let you know when blocking strikes. That won’t record the details of the blocking.

sp_WhoIsActive and sys.dm_os_waiting_tasks

If the blocking’s still going on, you can jump in, you can use sp_WhoIsActive to see what’s running, how long has it been running, is somebody blocking someone else, what do the wait types look like? And if needed, you can dig into details on the locks with @get_Locks=1.

Sp_WhoIsActive uses sys.dm_os_waiting_tasks to tell you things about who’s waiting in the SQL Server right now.

Tracing the Blocked Process Report and Deadlock Graphs

You can also use the Blocked Process Report to capture information about blocking that happened while you weren’t at the keyboard and deadlock graphs.

Tracing those is your inside information for why SQL Server stepped in and killed off one of the queries and the lock conflicts involved that meant that one query had to go.

All of these tools are core, critical tools for fighting blocking in SQL Server.

There’s a ton more to learn

One of my favorite topics for advanced fighting of blocking is optimistic locking in SQL Server: Read Committed Snapshot Isolation and Snapshot Isolation levels.

There can be risks to enabling these for a database and these settings are enabled on a per-database basis. You can use one or the other. Figuring out: is one of them right for you? Or is BOTH of them right for you? That is a bit of a research project, but, well worth the effort and can help prevent a lot of blocking, prevent deadlocks from happening, as long as you’ve invested in the system resources and the monitoring to make sure that they work well for you.

There’s also a lot more to learn about specific wait types on blocking and specific locked conflict types, of course, as well as different ways that you can design indexes and different ways that you can tune queries so that you have higher concurrency and less blocking in a very busy SQL Server.

I hope that you’ve enjoyed this course

I really, really enjoy making courses for SQL Server. If you liked this course, I would really appreciate if you took the time to write a review for it on the Shop page on SQLWorkbooks.com. Just click on the course name. There’s a review tab right there and you can leave a star rating and a note for the course. Thank you so much for joining me. I hope to see you in another course soon!