SQL Saturday #29 Presentations & Twittering

presentations_2010-03-26.jpg

Tomorrow, I’m presenting a couple of sessions at SQLSaturday #29 in Birmingham, Alabama. Here are the presentation materials I’ll use.

Reaching Out to the Twitterverse

While I was in Seattle last fall for the PASS Community Summit, I saw a couple of presenters effectively reach out to the Twitter community while giving their sessions. They had someone tweet important points for them as they presented their session. Then, as the Twitterverse posted questions in response, the designated liaison would voice them and tweet the presenters answers. It seemed to work out really well.

So, I’m considering trying that for tomorrow’s sessions – if I can find an attendee who doesn’t mind acting as the liaison.

Question: Would that be of interest to you? Would you like to follow some of the sessions for tomorrow’s SQLSaturday via Twitter?

I’ll let you know what the Twitter tag will be if it works out.

I hope to see you there, or at least hear from you via Twitter during the session.

Advertisements

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.

Viewing Missing Indexes in Management Studio 2005

phonebook_2010_02_22.jpg

If you’ve used SQL Server 2008 Management Studio (SSMS 2008), you may have noticed that Microsoft added a pretty neat little feature when looking at Execution Plans, the Missing Index message. This subtle message may appear when you’re looking at either the Actual or Estimated Query Execution Plan in graphical mode. It can be seen written in green just below the query text.

showplan_xml_2010_02_22_b.jpg

The message tells you that the optimizer would have been able to resolve the query faster if only it had another index. It’s even tells you what that index should be. I’ve heeded its advice on more than one occasion with very good results.

But I’m Running SQL Server 2005

Unfortunately, not everyone has access to SSMS 2008. And Management Studio in SQL Server 2005 (SSMS 2005) doesn’t provide this kind of information – at least not in an automatic and graphical. The image below shows the same query as run in SSMS 2005.

showplan_xml_2010_02_22_c.jpg

You can, however, retrieve the missing index information for a query another way – viewing the Execution Plan as XML.

Viewing the Execution Plan as XML

To view the Execution Plan in XML, open a query window in SSMS 2005 and use the SHOWPLAN_XML option as shown below.

SET SHOWPLAN_XML ON;

Execute the statement to set the option. Setting the SHOWPLAN_XML option causes SQL Server to return the estimated execution plan in XML for subsequent queries on this connection instead of actually executing the queries. For more information about the SET SHOWPLAN_XML statement, visit Books Online.

Once the SHOWPLAN_XML option has been set, “execute” the query whose execution plan you wish analyze. You’ll see something similar to the follow image. Note the SHOWPLAN_XML works best when the output is set to “Result to Grid.”

showplan_xml_2010_02_22_d.jpg

Double click the XML hyperlink to open the execution plan. Scroll down until you see (or search for) the MissingIndexes node. In the example below, there’s a single missing index identified, an index on the phone number. In this case, the optimizer is suggesting that simple, nonclustered index would improve performance. The impact attribute estimates the improvement that adding the index would have. The optimizer may also recommend nonclustered indexes with included columns and composite indexes.

showplan_xml_2010_02_22_e.jpg

Use Your Head

The Missing Index feature in Management Studio can be really nice. It’s another tool available to us as database professionals to help us do our job. However, as helpful as it sometimes can be, it’s no substitute for using your own skills and knowledge when considering which indexes to create, or not create.

Consider what the message recommends in a broader context. What effect will the new index have on inserts and updates? On maintenance plans? Then decide if the index is worthwhile.

Additional Information

To learn more about the Missing Index feature, visit some of these site.

Have you used this feature? Have you found it useful? I’d like to hear you experiences.

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.

Locking Scripts

Thanks to everyone who attended my TechNet Thrive! and Nashville SQL Server User Group presentations this week! I hope it was worth your while. As promised, here is the presentation.

And here’s the script I used to review the locks.

–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’;

Locking & Blocking

On the gridiron, two opposing teams are contending for one very limited resource, the football. One team has it; the other team wants it. The team that has it, tries very hard to protect it by securing the football and blocking all others from getting to it.

The same can be said about SQL Server. When one process needs to access a resource, say a table, it secures the resource (locking) which prevents other processes from accessing it (blocking).

Locking in SQL Server is a good thing. Locking helps to maintain data integrity by ensuring that two different processes are not going to use a single resource in a way that is considered incompatible.

Blocking, a natural by-product of locking, is not so good of a thing.

If a resource is locked by one process and another process needs access to that resource, the second process will be blocked until the resource is released by the first process.  The effect? The second process experiences a longer wait time and performance suffers.

But locking and blocking are two sides of the same coin.

Locking & Blocking Made Simple

Okay, if locking is good and blocking is bad, what can be done about locking? I’m glad you asked! Tomorrow, I’m speaking at the Nashville SQL Server User Group. In the session, I’ll cover those topics and more. Here’s the abstract:

A good working knowledge of how Microsoft SQL Server makes use of locking and transaction isolation levels can help you greatly improve an application’s performance. In this session, we explore the SQL Server locking methodology and share techniques for enhancing query response times.

If you’re in the Middle Tennessee area, come on out and join us for the meeting. For more information on time, location, etc, visit the Nashville SQL Server User Group web site.

If you’re not in the Nashville area, I believe the meeting will be available via LiveMeeting provided by PASS. Stay tuned and follow me or NashSQL on Twitter for up to the connection information.

Thanks and I hope to see you there!