OLD: sp_helpindex rewrites

I realize that I’ve created quite a few posts (and rewrites) around sp_helpindex and recently I’ve been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it’s not so obvious where to get it and/or what to do to install it. To make it easier… I’m just going to do a post like this EVERY TIME I have a new version and so there won’t be anything else to review/read.

HERE IS A LINK TO THE NEW VERSION: http://www.sqlskills.com/blogs/kimberly/use-this-new-sql-server-2012-rewrite-for-sp_helpindex/

All versions use a base procedure that builds the columns needed to produce the detailed output. So, you need to setup TWO procedures.

Step 1: setup sp_SQLskills_ExposeColsInIndexLevels

Create this procedure first: sp_SQLskills_ExposeColsInIndexLevels.sql (6.73 kb).

This is what gives us the tree/leaf definitions. And, this works for both SQL Server 2005 and SQL Server 2008.

Step 2: setup the replacement procedure for sp_helpindex. This IS version specific:

On SQL Server 2005, use: sp_SQLskills_SQL2005_helpindex.sql (10.50 kb) to create sp_SQLskills_SQL2005_helpindex.

On SQL Server 2008, use: sp_SQLskills_SQL2008_helpindex (sp_helpindex9).sql (11.06 kb) to create sp_SQLskills_SQL2008_helpindex.

Step 3: Optionally, setup this procedure to be invoked through a keyboard shortcut using Tools, Options, Environment/Keyboard. I usually make it Ctrl+F1 and I described how to do this here.


Survey: which US city should our next class be in?

One of the things I always find difficult is choosing where to host the next SQLskills Immersion Event. The community is so spread out that it's really hard to please everyone by coming to everyone's home town! We have some ideas of where we'd like to go next but here's your chance to influence us.

We're looking at scheduling another Immersion Event on Internals and Performance in the US in May, but where?

If you're thinking of coming to the next class, where would you like it to be?

(We'll try hard to meet your demands – now we'll know where we're most wanted!) 


We <3 you too!

Just today, Jen McCown (blog|twitter) of the Midnight DBA team blogged about their recent poll – who in the SQL community "does it right" in terms of community involvement. What's great is that the results were just published and SQLskills has come in second to SQL Sentry for community involvement. This is great news and we're happy to see that you think so too! (especially on Valentine's Day Embarassed)

Read Paul's post about our free resources and our exclusive mailing list in this post: Community, community, community, community….

And, along the same lines – we're continuing our Community involvement (with SQL Sentry) by hosting some free evening presentations while we deliver our Master Immersion Events in Dallas. We have somewhat limited space so we've created free "tickets" that you can print for the event. If you're interested check out these events and details:

Tuesday evening (Feb 22 and Mar 15): Query Tuning with SQL Sentry Plan Explorer

Abstract: Learn how to use this revolutionary FREE tool from SQL Sentry to wrangle even the scariest query plans. This session will cover key differences between Plan Explorer and SSMS, what to look for in estimated and actual plans, and various features that will help you get the most out of the tool.

Register for this FREE event (in Dallas, TX) here: http://sqlskillstue.eventbrite.com/


Wednesday evening (Feb 23 and Mar 16): Optimizing Performance with SQL Sentry v6

Abstract: This brand new release from SQL Sentry introduces several unique and cutting edge features, including active query plan capture for heavy SQL, plan analysis using the integrated version of Plan Explorer, additional Plan Explorer features made possible by active capture, historical query plan analysis, VLF problem detection, and more.

Register for this FREE event (in Dallas, TX) here: http://sqlskillswed.eventbrite.com/ 

And, we plan to deliver a session at the North Texas SQL Server User Group on March 17 (I wonder if we can get green beer in Texas?)

We hope to see you at one of our upcoming events and if none of these work for you, be sure to join our exclusive mailing list by clicking here.

Thanks and Happy Valentine's Day!

Using the VHD for our SQL Server 2008 HOLs

VHD stands for Virtual Hard Drive.

A VHD is created with/by programs such as Virtual PC, Virtual Server, Hyper-V, etc. and there's a standard format associated with them. If you’re interested in reading more about VHDs, check out: http://en.wikipedia.org/wiki/VHD_(file_format).

To use a VHD you need to have a program installed to "host" it. Think of it as a "guest" machine within a "host" machine. When you "run" the program that hosts the virtual machine you will be starting the "guest" machine within it. This guest machine is effectively isolated – which is great for testing, security, and restricting/limiting certain resources. To be able to do this, the VHD holds a completely installed OS and all other necessary software. This is part of what can make the actual VHD files extremely large. If you're interested in learning more about creating VHDs, check out this TechNet article: Create and Use a Virtual Hard Disk on Windows 7.

NOTE: Alternatively, if you're running Windows 7, you can also ATTACH a VHD natively without any additional software (I love this). I'll plan on writing up another post on this shortly and I'll link back to this! It's a great way for just quickly grabbing the files without any hassle of launching the VPC (and waiting for it to boot) and then copying files out, etc.

Depending on your operating system, there are a variety of recommendations on what to software to use to "host" the virtual machine:

Using the VHD with Windows Virtual PC (on Windows 7)

The remainder of the instructions here are for using and working with this VHD in Windows Virtual PC (Windows 7). Once Windows Virtual PC has been installed, you can "create" a Virtual Machine with only a few easy steps.

Creating the Virtual Machine 

(1) Launch Windows Virtual PC from the Windows Virtual PC group. This will show any already configured "shell" (or configuration) files in your "C:\Users\username\Virtual Machines" directory. These can be named .VMC (for older implementations of Virtual PC) or .VMCX (with new versions). For you, this list might be empty.

(2) To add a new virtual machine, select Create Virtual Machine from the top menu (see above). Next, you need to enter the details for the name and the configuration file for the VPC. The name is just how you plan to later reference the VPC and it can be helpful when you make copies/different versions of a VPC. You can always change this later as well. The location for the "settings" is stored within a VMCX file. Personally, I like to store this in the same location as the VHD.


(3) For memory and networking, your setting can vary. For memory, you need at least 1GB with a preference for 1.5GB or even higher, if possible. As for networking, you might want to access links or webpages or other resources so you might find this beneficial. If you want to access hard drives on the host machine, you can do this later (and more easily, IMO) through the Integration Features.

(4) Finally, choose "Use an existing virtual hard disk" to USE your expanded VHD as the "virtual hard disk" for your newly created virtual machine.

Using UNDO disks can be helpful as it allows you to change your VM into a secondary location and later choose (possibly days later) whether or not you want to save or discard those changes. Take a few minutes to read the details provided in the link.

Change the settings of the Virtual Machine 

Now that your virtual machine is setup, you can easily change any of the settings defined when creating the VM. You can do this by right-clicking on the VM or by choosing the Settings option from the menu. 

Starting and logging into your Virtual Machine 

To start the Virtual Machine (the "guest" machine on your "host" machine) use OPEN (again, right-click or from the menu):

This may take a few seconds to start as it's performing a full boot of an operating system (in the case of our HOLs VPC it's Windows 2003 Server). Once loaded, you can login. However, this is IMMEDIATELY where things can get confusing, especially if you've never used a virtual environment like this because the keystrokes aren't as you might be expecting. AND, if you have used earlier versions of Virtual PC Microsoft changed a few of the replacement keystrokes (this drove me CRAZY when I first booted with it!). If this is your first time using a virtual environment that's "hosted" then let me explain why it's necessary first. This can seem confusing but, it really does make sense. Many keyboard keystrokes (like Ctrl+Alt+Del) are tied to the host machine and cannot be changed even when changing window focus because they are OS level keystrokes. Ctrl+Alt+Del is an interrupt combination that's been used over the years to interrupt applications causing problems (and get to task manager, or to reboot when necessary). This keystroke combination is essentially always tied to the HOST – not the GUEST. As a result, you can select from the top menu (there's a button for Ctrl+Alt+Del) OR you can review the keystrokes below and use them.

Keyboard Shortcut [Confusion (IMO)]

Function/Keystroke       Virtual PC 2007        Windows Virtual PC (Win 7)
Logon/Ctrl + Alt + Del          Right-Alt + Del             Ctrl + Alt + End
Full screen toggle              Right-Alt + <Enter>         For full screen: Windows+Up Arrow, To toggle back, use the menu

And, now for the password… you can find our SQL Server 2008 SP1 HOLs VPC password in your the lab manual as well as in the 1stReadMe.txt file in the root of your DVD. For someone out there though, you might have lost the DVD, deleted the lab manuals and well… just WANT TO KNOW the password. It's expecting a US Keyboard and the password is Pass@word1. If your HOST machine is not using a US Keyboard, remember that your GUEST is. You will need to remember this as you type your password (especially the @ sign). For example, if you're on a UK Keyboard then "shift+2" is actually " (double-quotes). As a result, you still need to type shift+2 when typing in the password (as if you are on a US keyboard). Once you get into the VPC you can change your keyboard setting and save it. In fact, feel free to change the administrator password, if you'd like!

At this point, you're in. The HOLs VPC has 20 formal labs and dozens of samples/demo scripts for learning the many features of SQL Server 2008. Time to start having fun! Check out the LabManuals subdirectory and go from there.

Have fun!



Exciting news about our upcoming Master Immersion Events in Dallas, TX

We’re getting very close to our start date for our first "Master Immersion Event" and in planning, organizing and handling all of the event details – wow, there's a lot of work involved! However, we've had so much fun at these events that it ends up making it all worth it when they come together so well.

For our first class – which is less than two weeks from today – we have some exciting news!

Our news is that we’re pleased to announce that SQL Sentry is going to be our exclusive sponsor for our first two Master Immersion Events in Dallas!

Several of our staff have attended similar Immersion events and they are, without a doubt, the best SQL Server training you can receive.
Gregory H. Gonzalez
President and CEO, SQL Sentry, Inc.

We are thrilled to have the support of SQL Sentry on these upcoming Master Immersion Training events! SQL Sentry has been pushing the envelope on the SQL Server tools markets for several years now and we are pleased to have them sponsor our efforts to extend MCM level education out to the SQL Server community. We are huge fans of their Plan Explorer product, using and recommending it often. We especially admire their devotion to busy DBAs everywhere by making this product free to the SQL Server community.

What SQL Sentry's involvement allows us to do?

We've added a full American breakfast to the food provided for the week and we've upgraded our lunches – (with all of the food sponsored by SQL Sentry). This is really good because our discounted hotel rate doesn't include breakfast and so this will be a great way to allow folks to get the right start to their day! And, covering lunch as well means that attendees don't need to rush around with only a 1 hour break, find food and return. Instead, attendees can relax and really take a break. They can have a leisurely lunch right at the event… and with coffee/tea provided throughout the day, and with low-carb (and high carb ;-) snacks served mid-afternoon, attendees won't go hungry and won't run out of energy! And, their budget is hit less with breakfast/lunch covered by the event!

We've also added a Monday evening "meet and greet" as well as two evening presentations (Tue/Wed) to give attendees even more information to use in their every day work.

Tuesday evening: Query Tuning with SQL Sentry Plan Explorer, presented by Greg Gonzalez, President and CEO, SQL Sentry

Abstract: Learn how to use this revolutionary FREE tool from SQL Sentry to wrangle even the scariest query plans. This session will cover key differences between Plan Explorer and SSMS, what to look for in estimated and actual plans, and various features that will help you get the most out of the tool.

Wednesday evening: Optimizing Performance with SQL Sentry v6, presented by Steve Wright, Director, Engineering Services, SQL Sentry

Abstract: This brand new release from SQL Sentry introduces several unique and cutting edge features, including active query plan capture for heavy SQL, plan analysis using the integrated version of Plan Explorer, additional Plan Explorer features made possible by active capture, historical query plan analysis, VLF problem detection, and more.

If you’re not already familiar with SQL Sentry’s FREE Plan Explorer, check out Paul’s latest SQL Server Magazine Q&A post “Judging the effect of query plan changes while tuning.”

It's an exciting time in general. For us, it's a new opportunity to show people additional products (including cool and powerful FREE ones!) that exist and can help them with their SQL lives. In fact, I remember the first time I saw a SQL Sentry product – it was in a small conference room at a hotel at an event where I was speaking (I'm pretty sure it was Connections about 6+ years ago). I didn't have a specific use (at the time) for what they showed but I remember thinking – if I had that need, that would be IT! Then, some time passed, SQL Sentry grew and grew and then we started running in it at customer sites. Finally, we met Greg (@SQLsensei) in person at an Immersion Event when he and one of their engineers (Brooke) attended.

We all got to know each other better (and put faces to the twitter accounts :)). Then, it seemed a strange coincidence, we started running into more and more customers that had their products installed… and, we started using them more and recommending them more and more… and then they released Plan Explorer. What a COOL and FREE tool. This is absolutely a huge win for the community and I'm pleased to be a part of spreading the word!

So, after really getting to know them, their products and their team – we’re excited to announce that they’re sponsoring our first two Master Immersion Events in Dallas.

We hope to see you at an event coming up soon!

Compete for a free seat -> the winner has been chosen!

Wow. Choosing a winner was REALLY hard. We really struggled. We read all posts separately – chose our favorites and then came back together (re-reading many) and choosing our top 6. You can read all about it in Paul's post here: http://www.sqlskills.com/blogs/PAUL/post/So-why-do-you-want-to-come-to-our-training-And-the-winner-is.aspx.

Thanks to everyone for having submitted – we're really looking forward to a GREAT class in Dallas!


Master Immersion Event Content – Blog and WIN!

Paul has just blogged about this contest here: http://www.sqlskills.com/blogs/PAUL/post/Master_Immersion_Event_Competition.aspx.

So… may the best man (or woman!) win!

Good luck,

Do I need to be a master for “master immersion events”?

I've been getting this question a lot lately. I've been asked this mainly because numerous folks have told me that they don't think they're ready for this level of training. Many have even said that they don't think they're ready for the "master immersion events" because they think they're too advanced. This is the part that's funny… masters know the same things as everyone else – the difference is that it's second nature to them. They've had more experience with the content. They know best practices inside out. They know the many things on which "it depends" depend :-) They can debate 5 different approaches to solving the problem and based on the design criteria pick the one that meets the demands the best.

Masters are people like you… people that started out like you and had exactly the same experience that you did when you started working with SQL Server.

The difference is that they attended conferences, they took classes, they read blog posts, they got involved in the community and they studied and they worked and they kept studying. They got involved. They immersed themselves in the world of SQL Server and things have become clear.

If you're thinking that our classes are too advanced then you really have the wrong idea about SQL Server. Our classes are definitely fast-paced. Our classes are definitely deep! However, you don't have to be planning to take the certification exams the week after the class ends in order to attend the class (or in order to succeed). In fact, I wouldn't recommend it anyway. To truly "master" anything takes time. People with different levels of experience will get different things out of our classes but everyone walks away with practical and applicable techniques and best practices that (depending on the class) improve performance, minimize downtime, minimize data loss, improve manageability and solve design challenges.

These classes are for everyone and the sooner you learn these best practices and continue to work with these best practices the sooner you'll be on your way to being a master!

Master your SQLskills; immerse yourself in SQL Server.

And, I wish you all the best if your ultimate goal is to take the exam and practical for Microsoft Certified Master – SQL Server. Read Paul's post (Big changes to the MCM program and how SQLskills can help you) for more information on our classes and MCM prep work.

Finally, if you have any questions about our training and/or if you're ready – just drop me an email: KimberlyATSQLskillsDOTcom.


Happy Holidays (and, this is why we’re keeping our day jobs!)

Happy Holidays from everyone at SQLskills!

(although, only Kimberly and Paul were stupid enough to be recorded singing this)…

Listen here (audio only): http://www.sqlskills.com/blogs/paul/content/binary/MerryChristmas.wav

(but don't say I didn't warn you. And, it is completely office safe! ;-)


Intense Immersion Events are back – for 2011

As some of you may have seen from Paul's blog, we've started to announce dates and course curriculums for 2011.

I’m really excited about these events mostly because we just completed a similar event last week in San Diego, CA. Last week’s event was our last for the year and it couldn’t have gone better (or been more fun!). Once again we held our event at a Marriott hotel and this was the perfect spot for us (again!). Each morning we had breakfast in our patio area just outside our classroom. We had coffee/tea/water refreshed throughout the day and then we had a full lunch served on the patio. After class, most folks went over the lounge to relax and chat for a bit after the day packed full with information. We were staying in the hotel (as were quite a few students) and so we had a lot of time to socialize, relax and get to know each other better! And, no one had to run out for a rushed lunch trying to find a place where they could get food, eat and then be back within an hour. The food at the hotels have been really good (don’t get me wrong – it’s not gourmet food but the quality’s been way better than I expected!!).

So, while our primary focus is ALWAYS the technical content – the socializing, networking and extra discussions we’ve had have always been a heck of a lot of fun! Each event has been packed with info but relaxed enough (to a point) in terms of the logistics/venue to make learning (which is absolutely our primary focus) EASY and FUN!

It was with last week's event in mind that I've setup our new "Master Immersion Events" with the same style as our SQL Immersion Events.

For 2011, we’re offering two types of events:

SQL Immersion Events

These are our broader (but still deep) courses that often bridge the gap between beginning DBAs and Developers and Masters. We don’t have any SQL Immersion Events on the schedule yet but we plan to add some soon. Think of these classes as “intermediate to advanced” but more of an end-to-end story.

These classes run from 9-5:30 each day and end early (3pm) on Friday.

Master Immersion Events

These events are based on our concept of SQL Immersion Events (in-depth, focused, “immersion” in a subject) but with content largely derived by the requirements of the Microsoft Certified Master (SQL Server) program. Based on the original *SINGLE* 3-week (15 days) course, we’ve come up with 4 standalone courses. In fact, the content is what's driven these 4 courses into comprehensive, standalone curriculum regardless of whether or not you plan to get certified. And, the best part is that you do NOT need to take these courses back-to-back. The time between the courses can really help to give you time to absorb before coming back for more! If you do want to go on to take the exam and the practical lab, we recommend that you’re familiar with all of the content in our new 4-week, 19 day program.

These classes run from 8:30-6pm each day and end early (4pm) on Friday.

You can take them in any order but our recommended order is to take Internals and Performance first and then any course second/third/forth:

Master Immersion Event on Internals and Performance (5 days)

Data Storage Internals, Designing for Performance, and Indexing for Performance
Instructors: Paul S. Randal, Kimberly L. Tripp

Master Immersion Event on Performance Tuning (5 days)

IO Subsystems, Workload Analysis, and Performance Tuning Methodologies
Instructors: Brent Ozar, Paul S. Randal, Kimberly L. Tripp

Master Immersion Event on HA+DR (4 days)

Virtualization, Consolidation, High Availability, and Disaster Recovery
Instructors: Brent Ozar, Paul S. Randal, Kimberly L. Tripp

Master Immersion Event on Security and Development Support (5 days)

Database Security, Development Best Practices and Optimizing Procedural Code
Instructor: Bob Beauchemin

We're planning to run courses in Dallas, TX (Internals/Design in Feb, Performance in Mar), Chicago, IL (HA/DR in April, Security/Dev in May), London, UK (Internals/Design in April, Performance in May), all four in Bellevue, WA in August (you can take them as a series [if desired] OR you can pick/choose the week(s) that you want…and, it's an absolutely WONDERFUL time to see WA so having an extra day or two to see the sights is not a bad thing!) and then we're planning the entire series in New York, NY and either Atlanta, GA (or FL – probably Tampa area) in the Fall.

For more details, check out Paul's post (here) and then for full details about this training, including in-depth and complete curriculums for each class, along with the locations, schedules, and costs, see HERE. Registration for these courses will begin to open next week.

And, if you have any questions, please don’t hesitate to shoot me (or Paul) an email.

We hope to see you in 2011 and we wish you all the best over the holidays!