SQL Server Locking & Blocking At DevLink 2010

Wow! Thanks to everyone who came to my SQL Server Locking & Blocking session at devLINK this morning! I’m completely humbled by the turnout!

Thanks for hanging around until the end, too! At the beginning of the session, I said we’d have 45 minutes to cover 90 minutes worth of material. Even so, I had in my mind that the session would last until noon. So when I finished at 11:55pm, I thought I had finished 5 minutes early. I didn’t realize until later that I was actually 10 minutes late! I hope it was worth your while.

As promised, here is a link to the presentation slide deck and the demo scripts (Query 1, Query 2). Feel free to ask any follow up questions in the comments area below. And I’d appreciate your feedback on the session in SpeakerRate.

Lots More SQL To Come

If you’re interested in more topics and sessions on SQL Server, the Nashville SQL Server User Group is hosting a SQLSaturday #51 in just a couple of weeks (August 21, 2010). There are over 30 great sessions presented by SQL Server MVPs, authors, and industry experts from around the country. And it’s completely free. Space is limited so register soon.

Thanks again and I hope to see you at SQLSaturday #51!

How to Tell When SQL Server Started?

finger_trap_2010_04_28.jpg

As a consultant, I’m regularly asked to diagnose and solve problems when I have incomplete or conflicting information given to me by the client. They don’t mean any harm, but different people in the organization have different perspectives and different memories. Sometimes it is difficult to know what is really happening.

When I’m working with systems like SQL Server, it is always good to gather as much information from the system itself as I can and not rely too heavily on someone else’s memory. Trust but verify as President Ronald Reagan once said.

When was the server restarted?

SQL Server maintains a lot of information about how it’s doing and when certain events ocurred. Much of this information resides in the Dynamic Management Views (DMVs) of SQL Server 2005 and 2008.

For example, let’s say we want to know when the SQL Server instance was last restarted. When can easily collect this information using a quick query. There are several other methods as well but this one is my preferred way.

SELECT
Login_Time
FROM
sys.dm_exec_sessions
WHERE
Session_Id = 1;

server_restart_dmv.jpg

Additional Resources

SQL Server Books Online has more information about this and other DMVs. Additionally there are some other great resources on the web.

And there are many, many more. Just use your favorite search engine and begin using the DMVs to make your job a little easier.

Question:

  • What’s your favorite DMV?

SQL Server Locking & Block Scripts


A couple of weeks ago, I gave a presentation to the PASS DBA Virtual Chapter on SQL Server Locking & Blocking Made Simple.  The original post had a link to the presentation materials, but I neglected to attach the demo scripts. So, here they are:

To review them, click on the link and open the Microsoft Word document. (For some reason, my blog site won’t allow me to post text files.) Copy and paste the contents of each file into a new query window in SQL Server Management Studio and experiment all you wish. You’ll need the AdventureWorks database; it’s available for download on CodePlex.

If you have any questions or comments, feel free to post them below in the comments section.

Join me at the Memphis SQL Server User Group meeting

memphis_pyramid_2010_02_09.jpg

For almost a year now, I’ve exchanged emails with the leaders of the Memphis SQL Server User Group, trying to find a time when our schedules would align themselves and I’d be able to join them for a meeting. After many attempts and several near misses, we’ve finally set a date!

This Thursday, February 11, 2010, I will be speaking on a SQL Server Locking & Blocking. I’ll talk about:

  • Why databases lock resources
  • The consequences of blocking
  • How SQL Server manages locks
  • Techniques for influencing locking behavior
  • Using Transaction Isolation Levels

You can download the presentation materials here.

During my presentation, I’ll demonstrate how to analyze locks that SQL Server is holding using the sys.dm_tran_locks Dynamic Management View (DMV) as shown below.


–examine the resources
SELECT
resource_type
,(CASE
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
SELECT
object_name(object_id)
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id)
ELSE
‘Undefined’
END) AS resource_name
,request_mode as lock_type
,resource_description
,request_status
,request_session_id
,request_owner_id AS transaction_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> ‘DATABASE’;

If you live in or near Memphis, I hope you come out and join us for the meeting. I’d love to meet you. For more information, visit mem-pass.org.

Speaking at the Baltimore SQL Server User Group

On Monday, the first of February, 2010, I will be speaking at the Baltimore SQL Server User Group meeting at 7:00 PM EST. My topic will be SQL Server Locking and Blocking Made Simple. In it, I will discuss:

  • The need for locking
  • The consequences of blocking
  • How SQL Server locks resources
  • How blocking affects performance
  • Influencing locks with granularity hints
  • Setting Transaction Isolation Levels

Here is a sample slide from the presentation. You can download the entire presentation here.

During the presentation, I will regularly use the following script to view the locks being held by SQL Server.

–examine the resources
SELECT
resource_type
,(CASE
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
SELECT
object_name(object_id)
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id)
ELSE
‘Undefined’
END) AS resource_name
,request_mode as lock_type
,resource_description
,request_status
,request_session_id
,request_owner_id AS transaction_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> ‘DATABASE’;

I hope you’ll join me there.

If you’re interested in having me speak at your local user group, please send me an email or direct message me in twitter. I’d love to talk with you.

Follow

Get every new post delivered to your Inbox.

Join 31 other followers