Tag Archive for T-SQL Tuesday

T-SQL Tuesday #54 – How LinkedIn and Red Gate Landed Me My Job

My friend Boris Hristov (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of interviewing and hiring. This happens to be an apropos topic for me, since I have a new job.

It was a dark and stormy night…

OK, it was 8:30 am on a Monday morning and it was a beautiful January morning. It is Southern California after all…

I was checking my email like I always do after I get my morning coffee. There was yet another LinkedIn request. I’ve gotten to the point in my career where I’m picky about who I connect with. Basically, I always keep my LinkedIn account up to date, I’ve stopped connecting with people whom I couldn’t possibly help because our professions are so different, and I don’t connect with recruiters unless they are on my good side. This particular LinkedIn request was from a CEO named Richard at a company called DeskSite. Little did I know it would change my professional life.

A String of Events

Let’s step back a few days. I was already at a relatively new job. I had been there for six months, but I had also been frustrated. It was not the position I was expecting. I decided to pray about it the whole weekend and figure out if I wanted to start interviewing again or if I wanted to stick it out for another six months. By Monday I had decided to start looking.

So, we’re back to the LinkedIn request sitting in my email box on a Monday morning asking if I want to connect. Normally I would have said no. “He’s a CEO. He’s not a SQL person. We have no common connection.”, I would tell myself. But I was in a good mood. I thought, “Sure. Why not.”

Within an hour, I had an email from Richard, and it said:
Hey, we’re looking for a Database Architect. I was wondering if you could spend 10 minutes on the phone with me to see if you could help us find one.

I happen to be a chapter leader of a local BI user group called BIG PASS Community. While our group is BI focused, I know we have database professionals that cover the board, so I agreed to speak with him to see if I could find a match.

Side Story: I went down to my car to speak with Richard so that my colleagues wouldn’t know. I didn’t want them to think I was looking for a new job. Unfortunately, I have a blue tooth speaker at my desk and it was close enough to my car that my phone started transmitting through it. (Face Palm) So, I had to move my car. Problem solved.

Richard painted a picture for me of his startup company, DeskSite. He then told me where it currently stood technically and where he wanted to take it over the next three years. He then asked if I knew anyone who might be interested in joining his team or if he could possibly lure me away from my current company.

I kind of stumbled over my words, “I…I’m available. I just decided…literally, this weekend to start looking for a new position.” I cannot tell you how happy I made him. By time we got off the phone, DSC_3187I had an in-person interview scheduled for the next night at their office.

Side Story: When Richard called to verify that I could still make it, he told me that he had seen my SQL earrings that I wore in one of my Avatars. He loved them! So I wore them to the interview.

When I arrived at their office, I was greeted with a lot of enthusiasm. They were so happy to meet me. You see, they had Googled me. They knew that I’m heavily involved in the PASS Community. They had even seen the YouTube video that Red Gate published of me speaking at one of their SQL in the City events in 2013. They absolutely loved my enthusiasm and my obsession with SQL. They had already decided they wanted me on their team. They just had to convince me that I wanted to be on their team.  (You see, I was at a startup when the dot com bubble burst. It makes me leery of startups.)

My half hour interview ended up being three hours. At some point I was offered a job. Richard then wanted to know what it would take to have me on his team. Normally I don’t bring up the fact I like to speak and attend conferences in the first interview, but I’m also not normally sought after. So I asked if they would send me to conferences. Richard didn’t even blink. He gave me an allotment of days AND a budget. Wow. I did tell him I needed it in writing. I learned the hard way by taking something like that on good faith.

I was not prepared to have left with a verbal offer in hand. I was definitely thinking this offer was too good to be true. So like a good data professional, I started researching.

Over the next week or so, I asked many of my SQL friends what they thought. I sent Richard quite a few questions about the position, the company, the stock options, the offer, and even the culture of the company. (If you have never worked at a startup, they are VERY different than a mid-size or larger company.) I also made two more visits to their office.

The first trip was at lunch. You see, Richard’s ideal company is more like a family and families eat together. For those of you who don’t know me, I’m a people person. I despise eating by myself. It depresses me. Now that I have a Kindle, I have gotten more used to it, but I still prefer to eat with people. The bottom line is, I loved the culture of the company and Richard was one step closer to getting me on his team.

The second trip was a technical trip. They wanted me to meet with one of the consultants they use, to make sure I knew what I said I knew, and to talk deeper about the technical environment I would be working in. This one hour interview ended up being three hours. I think the technical part was only an hour, the other two hours was about the company… And my acceptance of their offer. (Aaacckkkk!)

Side note. I don’t do anything without talking to my awesome husband. I did take a bathroom break and talked to him on the phone for a bit and he was completely supportive of me taking the position.

The money dance

This is the part that has always been hard for me. Making sure I get paid fairly. I was asked what I wanted for a salary. I spent several hours researching what my salary should be. I have a wonderful friend who I got to talk real numbers with. I knew that I had been undervalued 2 jobs ago, but that had to do with the growth I experienced at that company. I grew professionally so fast when I first got involved with PASS that my salary soon became disproportionate to my knowledge, but because of red tape, my salary could not be fixed.

Anyhow, I finally came up with a number and sent it in. They made me an offer based on that number. It was made up of cash and options in the company. Unfortunately the cash portion was much too low. I was crest fallen. I know I could make a lot of money when the company goes public, and I really do think it will, but I have a daughter who will be heading to college in two years. I can’t risk her education or her younger sister’s education.

So what did I do? I talked to my friend who was also crest fallen for me. He offered some great advice and helped me devise a counter offer. I thought for sure it wouldn’t happen.  A week went by with no word. I prepared to start interviewing with other companies.

Then the clouds broke

I then received an email apologizing for the delay. They were in the processes of acquiring a larger office space do to the growth of the company and it had taken up much of their time. They really wanted me on their team. They understood my financial needs, but they had to discuss my counter offer.

In the end we came to an agreement and I became a member of an amazing team. I have been at DeskSite for a month and a half as of this writing. I’m very excited about the challenges ahead of me and I’m happy about being part of an amazing team.

So you see, I owe my awesome job to LinkedIn and Red Gate. I suppose Google should be added to the list since it was used to find my SQL in The City video on YouTube. So, YouTube should be added to the list as well……………

Thanks for all the fish

Thanks go out to Boris Hristov for hosting this month’s T-SQL Tuesday blog party. I always love and appreciate Boris’ enthusiasm about participating in T-SQL Tuesday, so please visit his website at http://borishristov.com/.

T-SQL Tuesday #51- Don’t Crap Out While Betting On Table Functions

My good friend Jason Brimhall (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. As a compliment to the upcoming debut of the Las Vegas SQL Saturday, Jason has taken up a betting theme. He wants to know our stories of when we bet it all on a risky solution and won or lost.

Instead of telling you about the past, I want to help you win big at the table today. I really don’t want you to crap out while betting on the wrong table functions.

Snake Eyes

There are two types of table functions Multi-line Table Functions and In-Line Table Functions. There is a huge difference between the two of them.

Multi-line table functions sound great. You write as much code as you need in them and they will return all the data in a table variable. This is where the weighted dice rolls snake eyes every single time. You see, the statistics for a table variable always, always says there is only one row in the table being returned. It doesn’t matter if there are a hundred, a thousand, or a million rows. The statistics will say one. Which means the optimizer has a good chance of loosing when it picks the execution plan for that query.

Let’s Take a Look at the Bets

For my example, I have a simple query that returns 43 rows out of a Tally table. Notice that the index estimates 43 rows will be returned, which is great, because that is exactly on the money!

TSQLTuesday51_1

 

If we put that same query inside of a multi-line table function, we get an estimated number of rows of 1 (snake eyes!).

 

TSQLTuesday51_2

 

TSQLTuesday51_3

 

Double Down

An in-line table function will return the same result set, but there are some limitations on its construction. The entire query within the in-line table function needs to be done in only one statement.

Note: You can get very creative with Common Table Expressions (CTE) if need be.

There are two benefits to using an in-line Table Function. One, is that the Estimated Number of Rows will be accurate (or as accurate as the statistics on the table), and two, the “inside” of the in-line table function is not masked in the Execution Plan. It is plopped right into the middle of the calling query. (Yes, “plopped” is a technical term. )

 

TSQLTuesday51_4

 

TSQLTuesday51_5 

Last Call

SoRemember to double down on in-line table functions and don’t crap out on the snake eyes of the multi-line table function.

Thanks for all the fish

Thanks go out to Jason Brimhall for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://jasonbrimhall.info/, or better yet come to Las Vegas for their SQL Saturday and thank him in person.

T-SQL Tuesday #50 – Automation Equates To Saved Time

SqlTuesday50Hemanth D (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009 and we hit the 50th “episode” this month. Could this be coincidentaly occurring during the 50th anniversary of Doctor Who?? I think not…

Anyhow, the topic this month is automation.

I can play that in 3 notes

There used to be a game show called Name That Tune. A contestant had to underbid the other contestant on what the minimum number of (musical) notes they needed in order to recognize a piece of music. Automating tasks remind me of this game show. First, you automate step one, then step two. Each time you tweak it and tune your automated process, you’re seeing just how far you can go… and how little manual work you are left with.

The first step

I’m going to talk about automating the first step of creating an SSRS report. I’ll be working with SSRS 2012, but the steps are the same for 2008 and 2008R2. The only difference is WHERE you store the templates so that they can be easily leveraged. Since that is the last thing you do, you’ll have to wait for the end of this post. So, hold on to your Tardis, and let’s go!

The first thing you need to know, is what are all the common elements to all of your reports. Here is a list of the elements I found to be the same on my reports:TSQL Tuesday 50 - Image 1

  • The location of the title: While my title was different each time, the font, font color, font size, and location remained static. So I created a placeholder for my title.
  • The company logo: The company logo was static as well as its location.
  • The color scheme: Since I used the same five colors, I created a temporary “pallet” for my colors. This was a mini Tablix control, with each of the five cell’s background color set to a different color in my pallet. I left it on the report until I was done setting all the properties of the report, then I deleted the Tablix. (No more looking up the colors in my documentation. Win!)
  • The location of the parameters: I personally think that the parameters should always be displayed on the report. This helps when troubleshooting a paper/pdf copy of a report. It also lets the users know the boundaries of the data they are looking at. (Note: While not depicted in my image, I put my parameters under the logo.)
  • The company address: Static location.
  • The confidentiality notice: Static location.
  • The page numbers: Static location.
  • The report identifier: This is a special number that helps you identify your report. Mine always has three parts.
    • TX or DW to mark the report as having transactional or data warehouse based data. This allows me to speak intelligently about a report that someone shows me in a meeting, especially since our data warehouse data was always older than our transactional data.
    • A number that corresponded to the documentation for the report. In our case, it was the TFS (Team Foundation Server) number.
    • An iteration number. This iteration number was specific to how many times the report was re-introduced into production. This allowed me to verify that the user was looking at the latest copy of the report, and it allowed me to document how many times the owner had requested changes to the report.
Creating the templates

A template is an RDL file saved in the templates folder. I created three templates for my team. Each template was identical to the others, except for two things. The paper size and orientation. I needed to make different templates to accommodate these attributes so that the controls that were centered or right aligned ended up in the correct location for viewing and printing.

  • SSRS 2008(R2) Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
  • SSRS 2012 Location: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But wait! There’s more

If you want to go a bit further with your templates, you can add a watermark that can be leveraged during development all the way through user acceptance testing. The watermark will then be suppressed in production. The watermark says DRAFT. I found it helped with certain end users, who were getting caught up in the data (even though it was fake) and they weren’t focusing on the design and algorithms present in the report.

Here are the steps to add the dynamic watermark:

  1. Create a table in the database that contains the name and ID of the environment you are in, plus a parameter that dictates whether to show or hide the watermark. Note: You can also use this table to point to development file locations for documents and development URLs referenced in your reports.
  2. Add an image to the background of the report body that says DRAFT.
  3. Set the Background Repeat property to Repeat
  4. Create a data source in the report that points to the environment table you created in step 1 (preferably through a stored procedure).
  5. Use the data source to hide or show the background image of the report.

If you use shared data sources, then add the data source to the template directory so that it can be added to new projects the same way report templates are.

There you have it! You just automated quite a few (initial) steps for creating SSRS reports.

Thanks for all the fish

Thanks go out to Hemanth for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://sqlchow.wordpress.com/.

T-SQL Tuesday #47: SWAG And A Hit

Kendal Van Dyke (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is a fun one, just like last month. Kendal wants us to brag about our favorite SWAG.

Wow, my favorite SWAG. Only one? Can’t do that. So I will give you my top 3.

Number 3 on the list

Tim Ford (b|t) gives the best All Inclusive Package of SWAG when you partake in his SQL Cruise conference (b). When I went on SQL Cruise this past January curtsey of SQL Sentry (b) (Thanks guys for paying for my classes!), we received the coolest fabric cooler. Inside this cool bag, was more awesome SWAG. There was a beach towel, several gifts from various vendors, and the coolest little multi-tool. (I unfortunately learned the hard lesson that multi-tools can’t be part of carry on luggage. Sad Panda.)

I use this bag when ever I need a simple cooler, which is ever SQL Saturday. I have so many food allergies, that I usually need to bring something for me to munch on. This bag does the trick.

Number 2 on the list

The PASS Summit 2012 backpack. I love this backpack. It’s sitting next to me right now while I type this post at the Las Vegas airport. This backpack is comfortable to wear and full of great pockets. My absolute favorite pocket, is the little pocket right under the handle. It’s great for putting my phone and wallet.

This bag is my laptop’s home. Timmy my Think Geek monkey gets to ride on the side of the bag, and my laptop gets to ride in the center. I can quickly store all my odds and ends for my speaking needs.

 

And Number 1 on the list

When I spoke at my first SQL Saturday in Silicon Valley (b), the speakers weren’t given speaker shirts, but were given jackets. The jackets were made by Port Authority and are absolutely wonderful.

Being that Southern California doesn’t “really” ever get cold, this jacket is now my new “winter” jacket…which means I wear it a couple of times a year.

 

Thanks for all the fish

Thanks go out to Kendal Van Dyke for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://www.kendalvandyke.com.

T-SQL Tuesday #46 – And I shudder

Rick Krueger (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is a fun one. He has asked us to write about a Rube Goldberg type of solution (aka a hack job) we created at some point in our careers.

Like many of you I worked at a Start-up back in 2000, but I wasn’t a database developer back then. Back then I was a web developer who specialized in the business layer which I wrote in VB and later VB.net. Back then I never thought about consulting with the DBA to see if my idea was good idea from a database point of view. I can tell you that I shudder at the mere thought of the “brilliant” solution I came up with back then…It was actually a great idea…until you put in a database and queried it.

The Plan

The company I worked for was building a shopping cart from the ground up. We needed a way to keep track of the state of each item in an Order and display that information at the Order level. My idea was to keep track of each Order Item state with an alphanumeric letter and we would string them all together into one field at the Order level so that we could query for Orders with Items at a particular state. (This is where we all shudder.)

When I thought of the idea, I thought there would only be a couple of letters. R for return, O for ordered, C for Canceled, etc. As we started going through all the permutations we found a ton of exceptions. I think by the time I handed the responsibilities over for this one field, we had almost 10 different designations.

The Flaw

Since (back then) I could read execution plans as well as I could read Japanese (which means not at all), I had no idea the impact of my design. I didn’t know that indexes on that field would become pretty much useless since a Like operator would have to be used to find a particular part of  the concatenated field.

A better approach for this same solution would be to leave the designations at the item level and for reporting concatenate the values at the Order level.

Second Verse, Same as the First

Since I never learned at the first company that my idea was flawed from a database perspective, I pitched the same plan at the next company…where we implemented it…again. I have learned my lesson since then and advocate to all .net developers that working with the DBA or DBD from the beginning can help achieve a better application.

Thanks for all the fish

Thanks go out to Rick Krueger for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://www.dataogre.com.

T-SQL Tuesday #45 Summary–Follow the Yellow Brick Road

This is the summary of blog posts for T-SQL Tuesday #45 – Follow the yellow Brick Road. I was the lovely host this month with the topic being Auditing. As always, I enjoyed all the posts. It reminds of what Forest Gump once said:

 “It’s like a box of chocolates. You never know what you’re going to get.”

So, let’s meet up with Dorothy to find out what kind of auditing related advice and stories she found on her journey.

TSQLTuesday45 - YellowBricks

Summary

Jason Brimhall had a wonderful post about using the information that SQL Server automatically gathers for us. He used the  sys.FN_TRACE_GETINFO function to find out when and who executed the DBCC SHRINKFILE command  in his environment.

Rob Farley wrote a thought-provoking post about how you should think about your auditing data you do have and how you shouldn’t make excuses when you don’t have it.

Boris Hristov wrote a piece about the impact of external auditing entities on our activities. In this case, it was a story about unintentionally scaring the client about database security levels and how that could have impacted a SOX audit. Side note: I always enjoy Boris’ enthusiasm about T-SQL Tuesday. (keep it up Boris. Smile )

Chris Yates wrote a great summary piece about what to audit and a list of various tools to use to accomplish the auditing.

Steve jones wrote a wonderful piece on why auditing is important and how he caught a “cowboy coder”.

Steven Ormrod wrote an excellent piece on how to use a DDL Trigger to not only find out who modified the database, but from what IP address and application they did the deed. This is definitely one to bookmark.

Glenda Gable shares with us how she learned about writing triggers that audit. She then goes on to share with us how she had an opportunity (and seized it) to learn and implement Change Data Capture (CDC).

Jeffrey Verheul wrote about taking the initiative at a new company and creating a baseline on the system health of the 70 databases he was responsible for… and it paid off in a relatively short amount of time.

Dave Green wrote a thought-provoking post about what to do with the audit data during a data migration. I particularly enjoyed this post, since I get to ask my clients those very same questions next week. (Can we say perfect timing?)

Robert L Davis wrote about a type of auditing that I hadn’t even considered, and I’m happy he did. He wrote about a new feature in SQL Server 2012. SQL Server 2012 will automatically audit the execution of your SSIS package. He goes on to share how he was able to use the feature.

Mickey Stuewe (that would be me), wrote about a simple way to audit at the data level. She also made a plea at the end of her post to all the women bloggers out there. She asked them to contact Adam Machanic (b|t)  about being a T-SQL Tuesday blog party host.

…And That’s A Wrap

Thank you to the 11 bloggers who participated this month. We couldn’t have T-SQL Tuesday without you. Thanks also to Adam Machanic who put this brilliant idea in action and makes sure it continues each month. … Only 14  more days until the next invitation goes out.

T-SQL Tuesday #45–Follow the Yellow Brick Road

This month I’m the lovely host of the T-SQL Tuesday blog party which Adam Machanic (b|t) in December of 2009.  The topic this month is Auditing. I chose this topic because auditing, in some form, is needed for all databases (IMO).

Was it the lion, tiger or bear? Oh my!

One of the databases I use to manage had a schema where each table had four fields to keep track of who originally created the record and who last modified the record. The date was also stored for these two events. These four fields were populated by the .Net application that used the table.

TSQLTuesday45 - Table

We found, over time that this solution had a couple of holes.

1. Values were missing when someone inserted or updated data directly in the database.
2. Values were missing when the .Net programmer forgot to add code to populate the fields.

Luckily, the solution was a quick and easy one.

First, defaults were set on the InsertedBy and InsertedDts fields, SUSER_NAME() and GETDATE() respectively. This provided the user’s account that was used to access the table and the current date.

Second, an update trigger was created to populate ModifiedBy and ModifiedDts. This allowed the application to insert values if they were provided, but default values were available when they weren’t. More importantly, when someone modified the data directly, their login was captured.

CREATE TRIGGER dbo.trg_ClientOrder_iu ON dbo.ClientOrder
	AFTER UPDATE
AS 
	SET NOCOUNT ON 

	UPDATE dbo.ClientOrder 
	SET
		ModifiedDTS = ISNULL(i.ModifiedDTS, GETDATE())
		,ModifiedBy = ISNULL(i.ModifiedBy, SUSER_NAME())
	FROM
		dbo.ClientOrder AS tb
		INNER Join inserted AS i ON tb.ClientOrderID = i.ClientOrderID
GO

Here you can see that when I inserted the first two records, my login was captured. On the third record I provided values for InsertedBy and InsertedDts.

TSQLTuesday45 - Data1

Next I updated the first record without providing a ModifiedBy or ModifiedDts. On the second row, I did provide a ModifiedBy and ModifiedDts with my update.

TSQLTuesday45 - Data2

This simple solution allows you visibility into who and when data was changed. While this is not a comprehensive solution, it did solve questions that we had over the years.

Thanks for all the fish

Usually this is where I thank the host, but as Spock would say, “That would be self-serving.” So instead I am making a plea to all the amazing female bloggers out there. You know who you are. I want you to ask Adam Machanic (b|t) if you can host the T-SQL Tuesday blog party. Why am I making this plea? I’m glad you asked.

Being a data geek, I went through all the past T-SQL Tuesday invitations, which Steve Jones (b|t) so graciously keeps up to date. (Thanks Steve!) I found that out of the 45 invitations, only FIVE hosts were women.  The other amazing fact was that three of us didn’t host until this year!

  • Jes Schultz Borland (b|t)
  • Erin Stellato (b|t)
  • Jen McCown (b|t)
  • Wendy Pastrick (b|t)
  • And yours truly

Ladies, that is only an 11% representation. Please, please, please email Adam Machanic (b|t) and ask him if you can host. The rules are here.

Ok, I’m getting off my soap box. 🙂

T-SQL Tuesday #45 Invitation–Follow the Yellow Brick Road

This month marks the 45th month in Adam Machanic’s (b|t) T-SQL Tuesday blog party which started in December of 2009. Each month an invitation is sent out on the first Tuesday of the month inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week. This month I’m the host and the topic is …

Auditing

An audit trail is needed for various reasons. Some companies need it for compliance, others need it to find out who “accidently” did something stupid last week, and some specialized audit trails can tell you how the data has changed over time.

So, it is time to follow Dorothy and Toto down the yellow brick road and to share your experience with auditing data. If you are new to the T-SQL Tuesday blog party and need some ideas, here are a few:

  • How to implement SQL Server Audit which was introduced in SQL 2008.
  • Your favorite audit pattern.
  • Your worst experience with an implementation of a bad auditing pattern.

Pay no attention to that man behind the curtain!

The Great and Powerful Oz has spoken! … and these are the rules.

Rule 1: Make sure that you include the T-SQL Tuesday image at the top of the post which will help identify your post as a T-SQL Tuesday blog post.  Please include a link back to this invitation too.

Rule 2: Sometime next Tuesday using GMT, here’s a link to a GMT time convertor, publish your blog post.  For example in California, that would cover 5 pm Monday to 5 pm (PDT) Tuesday.

Rule 3: Come back here and post a link in the comments so that I can find all the posts for the round up.

Rule 4: Don’t get yourself fired. Make sure that you either generalize your post or get permission to blog about anything from work.

Rule 5: If you roam the Twitterverse, then don’t forget to Tweet about your blog post with the hashtag #tsql2sday.

Rule 6: Go read someone else’s blog on the subject!

Final Rule: Have fun!

 

T-SQL Tuesday #43: Give Me a Key Lookup Operator for $1200, Please

My good friend Rob Farley (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is on Plan Operators that are used in execution plans to tell us how the Optimizer is going to run our query statements.

What is a Key Lookup Operator?

The Optimizer uses indexes to retrieve the fields needed from a table. If there are missing fields in the index being used, then the Optimizer has to go back to the Clustered Index to get the other fields. This has to be done for every row in the table. This action is noted in the execution plan by the Key Lookup Operator. The RID Lookup Operator is used instead of the Key Lookup Operator when a Clustered Index is not used and a Heap is used instead.

Show Me the Money

For my example I used the AdventureWorks2008R2 database. I ran the following query and looked at the execution plan.

SELECT
	c.CustomerID
   ,c.PersonID
   ,c.StoreID
   ,c.TerritoryID
   ,c.AccountNumber
   ,s.BusinessEntityID AS StoreID
   ,s.Name AS StoreName
   ,s.SalesPersonID
   ,st.Name AS TerritoryName
   ,st.CountryRegionCode
   ,st.[Group]
   ,st.SalesYTD
   ,st.SalesLastYear
   ,st.CostYTD
   ,st.CostLastYear
FROM
	Sales.Customer AS c
	JOIN Sales.Store AS s ON c.StoreID = s.BusinessEntityID
	JOIN Sales.SalesTerritory AS st ON c.TerritoryID = st.TerritoryID
WHERE
	c.StoreID IN (1284, 994, 1356, 1282, 992, 1358, 1280)

 

TSQLTuesday43 - KeyLookup

Two indexes were used to retrieve all the fields needed from the Sales.Customer table. The ix_Customer_StoreID index was missing the TerritoryID field so the Optimizer had to go to the PK_Customer_CustomerID Clustered Index to retrieve it. If you add the cost of both operators, then 66% of the cost of the query was used to retrieve fields from the Sales.Customer table.

For reference I removed the Clustered Index to show you what the execution plan would look like when a Heap is involved.

TSQLTuesday43 - RIDLookup

 

Since there was already a good index for the Customer table, I added the TerritoryID to the INCLUDE part of the index script. This turned the index into a covering index. A covering index is an index that contains all the fields from a table that are needed by a query statement. The INCLUDE part of an index allow extra fields to be part of the index, without the overhead of the data being sorted. Any fields that are part of predicates or filters should be part of the index, all other fields from the table should be part of the INCLUDE. Be cautious though, don’t throw the whole kitchen sink there. Those fields still take up space.

TSQLTuesday43 - Index

When I ran the execution plan again, I saw that the Key Lookup Operator was removed and the total cost to retrieve the fields from the Customer table was now reduced to 12%. This would also be true if the table was using a Heap instead of a Clustered Index.

TSQLTuesday43 - CoveringIndex

The Bottom Line

When you see a Key Lookup Operator or a  RID Lookup Operator, look to see if it makes sense to modify the corresponding index to be a covering index.

Thanks for all the fish

Thanks go out to Rob Farley for hosting this month’s T-SQL Tuesday blog party. Please visit Rob’s blog at http://sqlblog.com/blogs/rob_farley/default.aspx.

Shameless Plug for Grant Fritchey’s (b|t) Book: SQL Server Execution Plans, Second EditionYup, this is a shameless plug for one of my FAVORITE books. Grant did an outstanding job on this book. He goes through the majority of the execution plan operators, discussing what they represent. He also goes into how best to read an execution plan and how changing your query can affect the operators that are shown in the execution plan. I highly recommend adding this book to your collection if you don’t already have it.This book is available from Red Gate. You can download a FREE eBook, buy a hard copy from Amazon, or if you are lucky you can pick up a copy from a Red Gate event.

 

T-SQL Tuesday #41–It All Started with A Flower

T-sql Tuesday
Bob Pusateri (B|T) is hosting this month’s T-SQL Tuesday blog party. (Thank you Bob!) The party was originally started by Adam Machanic (B|T) just over three years ago. The topic this month is “Presenting and Loving It”. This is an interesting question for me, because it touches most of my life.

For those that know me, I’m very artistic, in fact I have an art minor and even tried to be a full time artist for a couple of years. The first time I remember teaching others about a topic, was in 6th grade. I taught my class how to make paper flowers by cutting out individual petals. Answering the class’ questions was my favorite part of the presentation.

Jump to 1994, which is when I graduated from college. Jobs were not the easiest to find. Probably because Dice.com hadn’t been written yet. So my first job out of college was teaching Microsoft Office to professionals, and within six months I became an MCT. I taught Visual Basic for two years before the travel got to me. I had some great classes and some boring classes, but I don’t think it was as exciting as presenting at SQL Saturdays.

At the beginning I found that most of the students in the classes were not very engaging. It was like pulling teeth to get them to ask questions or even to voluntarily answer the review questions. So I devised a plan. I brought a bag of Hershey’s Miniatures and I tossed them to the students who asked questions. Let me tell you, by Friday they were all asking questions. As time went on, I tried other things. I brought in monopoly money and handed out a pink $5 for leading questions. They loved that too, but the best idea was my version of Jeopardy. I divided the class into two teams and they had to name each other. Then they answered the review questions and any questions I made up as a team. Now I had team spirit, cooperation, and fun in my class. The best was the class that named each other “The Banana Slugs” and “The Sage Hens”. (Why sage hens? Because if you scare them, they can die. True story.) That one week class had so much fun that one of them wrote me a letter thanking me. But speaking at SQL Saturday is still better.

In 2002 I was so fed up with the way I was treated as a programmer that I tried to leave the field. I had always wanted to teach math, so I went back to college that spring and taught high school algebra that summer. (What was I thinking?) Presenting at SQL Saturdays is WAY better than that.

Which brings me to 2013 and SQL Saturdays. At SQL Saturday I get to present on whatever I want, providing they pick my abstract of course. At SQL Saturday the only people in my class are people who want to hear what I have to say. When I was an MCT, some of my students were there because their boss made them. Nobody is forced to attend a SQL Saturday. At SQL Saturday’s I can be me because I’m representing myself, not the company I work for. This also means I can give my friends hugs, eat lunch with my friends, and not talk for five days straight.

When it comes right down to it…

The number one reason why I like presenting is the same reason as 30 years ago, I like to answer questions and teach people. (And I still like to bring chocolate to my sessions too…all though I might try bacon.)

%d bloggers like this: