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.

Speaking at the PASS DBA Virtual Chapter

clouds_2010_02_18.jpg

Next week, I’ll be “in the cloud” as I give a presentation for the PASS DBA Virtual Chapter. The topic is SQL Server Locking & Blocking Made Simple. You can download the presentation materials here.

Join me from the comforts of your own home or office on February 24th, 2010, at noon EST.

I hope to talk with you then.

Interactive Sorting Using a Tablix in Reporting Services

Many organizations collect data, lots and lots of data. We have the opportunity and ability today to collect more data than ever before. The challenge is turning the massive amounts of data into usable and actionable information and putting that information into the hands of those who need it when they need it. To do that effectively requires some forethought and planning. Unfortunately, many organizations stop at the data collection phase. Transforming the data into information and reporting that to users is almost an afterthought. The result is static and hard-to-interpret reports similar to the financial ledger books of the days of old.

Business Intelligence Developers Studio for Microsoft SQL Server 2008 Reporting Services, BIDS for short, makes creating engaging and dynamic reports nearly trivial in many respects. You can easily create drill-down reports that allow users to initially see a high-level overview of the information they need and then drill down into specific areas of interest with just a few clicks of the mouse. You can also create drill-through reports that allows users to seamlessly navigate from one report to another.

One dynamic feature that many users find very useful is known as Interactive Sorting. With Interactive Sorting, users can click on column headings in the Tablix data region and have the report automatically sort by that column. Let’s walk through a graphical example to demonstrate.

Sample Customer Report

Here’s an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a Tablix data region.

Right now, the report shows the list of customers in no particular order. Let’s provide a way for our users to sort the list as they wish. In the Layout view, click the Tablix header cell for the Last Name column to select it, right click, and then choose Text Box Properties from the context menu.


A new window will open. Click on the Interactive Sort link on the left hand side of the window.


To allow users to dynamically sort the report, check the Enable interactive sort on this text box check box. For this simple example, we’ll sort on the Detail Row by the LastName column.

Click the Ok button to save the changes and close the window. Repeat the process for each column you would like to have sortable. In this example, we’ll enable it for every column in the report.

When we preview the report again, you’ll notice that each column now has a couple of arrows in the header.


By clicking on one of the column header arrows, the sort order of the rows will dynamically change.


And there you have it. With only a few mouse clicks, we’ve enriched the experience our users have while viewing this report. Of course, this technique only works when the report is rendered in formats that support interactivity. When being viewed as a TIFF, for example, the user will not be able alter its presentation.


Hello, I’m a Mac

Years ago, I decided to move my SQL Server and Visual Studio work into Virtual Machines. The impetus was to make it easier for me to quickly create and maintain specific environment for specific projects and clients.

You see before I went virtual, I would load the latest and greatest version of a tool or application on my laptop. Sometimes I would keep a couple of revisions back, too. And as storage space became an issue, I would uninstall the oldest version to make room. However this technique effectively made it very difficult for me to support clients that still used older versions.

Enter Virtual Machines

So, I decided to move my entire development, administration, and presentation environment to virtual machines. This would allow me to have self-contained and dedicated environments that I could copy off to cheap external storage as drive space dictated.

I started with VirtualPC, but that proved to be lacking in many ways so I quickly moved to VMWare Workstation. It was good. I liked it. It met my needs.

With all of my SQL and development stuff in virtual machines, I soon realized that the only activities that I used my host operating system for was email, browsing the web, and blogging. And I could do that in most any operating system I wanted. I was no longer tied to Windows as my only choice.

I had had some previous experience with Linux and found it to be pretty user friendly, as stable as Windows, and faster than Windows on a given set of hardware. So I made the switch. Linux became my operating system of choice. That was four years ago; I’ve since blogged about the virtualization setup here.

Think Different

Using Linux has been a good experience. It’s fast, free, and I haven’t had to worry about viruses, trojans, and the like. Those who have the knowledge to write those kinds of destructive programs typically use their skills to make Linux better not tear it down.

But Linux is no nirvana; there have been some bumps along the way. Sometimes a kernel upgrade will cause the sound to stop working or the window manager to freak out and not display anything. The Linux community is great and usually these issues take only a couple of hours to resolve.

Apart from upgrades there are some other relatively minor inconveniences when using Linux. One is that many applications won’t run on Linux. iTunes is a good example of this; there are Mac and Windows versions of the application but Linux users are left out in the cold. (Some have successfully gotten some Windows versions of applications to run in Linux using Wine but that’s a non-trivial ordeal.) Fortunately, there are frequently very good Linux equivalents to these applications. Another annoyance is that there maybe some incompatibility with other software applications. For example OpenOffice does a very poor job with Office 2007 file formats.

These annoyances have relatively minor over the years. And I believe better than running the traditional Windows operating system. However this time when it came time to replace my laptop, I decided to “Think Different”. I bought a MacBook Pro.

I’ve had it a week now and even though I’m a complete newbie, it’s been a very pleasant experience. The learning curve hasn’t been steep. The hardware is solid, the software is intuitive, and the graphics are great.

I’m still getting use to some of the ways that Macs do things. For example, clicking on the X doesn’t actually close the application, only the window. I’m an avid user of keyboard shortcuts and there are some different mappings on the Mac. I’m still getting use to those. My MacBook Pro doesn’t have the Home, End, PageUp and PageDown keys and that is taking quite a bit of getting use to.

I’ll blog more about my virtualization setup in another post. (Thanks to Aaron Bertrand (blog, twitter) for sharing his setup here.)

For now, I’m well on my way to joining the ranks of Brent Ozar (blog, twitter) and Aaron in the SQL/Apple Fanboy club.

Isn’t Consulting Risky?

Small Business Ownership. It’s one of the Great American Dreams. Our forefathers, the founders of the country that I call home, were entrepreneurs. They were merchants, farmers, and woodworkers. Many, such as the blacksmiths and cobblers, were in the high tech industries of their times. And like those forefathers, the call of self-employment persists in many of us today. We long to “hang a shingle out” and see if we have what it takes to make it as a small business owner.

And the desire is not limited to Americans, either. No, people from around the world want to join the ranks of the self-employed. It seems to be a common trait in most everyone.

So, what’s stopping us? Why don’t more of us break away from the employee-employer relationship and go to work for ourselves?

Risky Business

As I’ve talked with developers, database administrators, project managers, and others in the IT field, a recurring theme continues to surface when I asked that question: RISK! It may not be expressed so concisely, but that’s what it frequently comes down to. Risk.

Many would-be consultants are concerned that launching a career in consulting would be like setting a course straight into stormy weather. And that understandably gives them cause for concern. There’s just too much on the line in many people’s lives: a mortgage payment to make, mouths to feed, and a certain level of creature comforts to maintain. If a career in consulting doesn’t pan out, we stand to loose much of that. Nobody wants to risk their livelihood.

And there’s nothing inherently wrong with risk aversion. We all, at some level, want to minimize our exposure to risk. That’s why we buy insurance, to help protect us from uncertainty. We invest in a diverse portfolio to help protect us from a volatile free market. (Although both of those are probably not the best examples given what’s happened in the world’s economy of the past couple of years.)

We all have different tolerance levels for the amount of risk we find acceptable. And for many of us, that line is drawn before what we reach the self-employment point.

The Risk of Staying Employed

But to be fair, let’s consider the alternative; it’s not risk-free either.

The employer-employee relationship has been described by some as a sort of marriage where there is a bit of give and take on both sides for the good of the overall relationship. It’s full of compromise and trust. The employer trusts that you’ll work hard and you trust that they’ll pay you for working there.

But employment really isn’t like a marriage at all. There’s no commitment. There’s no loyalty. There’s not a “till death do us part” clause in an employment contact. When you receive an offer to go work for another company, you can. If the organization believes they can outsource your job and save money, they will. The employer’s priority is maximizing returns for its shareholders. The employee’s priority is making a good living for himself. If anything, it’s more of a marriage of convenience than a life long commitment.

sailing_into_a_storm_2010_02_09.jpg

I don’t mean to sound cynical about employment. I’m not really. There are a lot of really good companies out there to work for, a lot of good managers to have as a boss. And finding one can mean many years of contentment in the workplace.

However, you must realize that employment is not a safe harbor from risk. On the contrary, I’d even say in some ways it’s even more risky than consulting.

No matter how hard we work, no matter how well we do our jobs, we are only one downsize, one merger or acquisition away from the unemployment lines. And it really doesn’t have anything to do with us as employees. It’s completely out of our hands. If a decision is made to sell off our division, to close this factory, to off-shore our department, we’re out of a job. Even though we were best in our job, we’ve lost it because of something that had nothing to do with us as employees.

You Have the Power

On the contrary, in consulting our success depends solely on our performance. Our jobs cannot be out-sourced or taken off shore. We cannot be downsized or re-organized out of a job. Our livelihood depends solely on how well we perform our role as a consultant.

Oh sure, some of our clients will no longer need our services. But if we’re actively engaging multiple clients at one time or we have other clients in the pipeline, our livelihood is not affected. We simply move from helping one client to assisting another client with little disruption to our streams of income. And I’ve found that if I do a good job for a client that generally leads to either more work from them or glowing recommendations to others.

It’s No Cake Walk

But consulting does require effort, not just in areas where we consider ourselves strong either. To be successful, we must nurture our client relationships; we must arrange to have another engagement lined up after this one winds down; and we must be willing to talk about money and negotiate agreements.

In short, consulting requires that we expand our skill set beyond our technical abilities. We must learn to become project managers, bookkeepers, salespeople, executives, and janitors. Our success in consulting relies not only on our technical prowess, but on the skills that will get us to a point where we can exercise our technical skills to help clients.

And can be a scary proposition. One that we should not enter into lightly. But more about that in another post.

Now it’s your turn

I’d like to hear your thoughts.

  1. What lessons have you learned about employment? About consulting?
  2. Do you want to start consulting? What’s holding you back?
  3. Is consulting really as risky as it sounds? Why?
  4. How can remove some of the risk associated with consulting? Employment?

And finally, I’m thinking of doing a series of blog postings on consulting. Interested?

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.

Follow

Get every new post delivered to your Inbox.