Tag Archive for #TSQL2sdays

| July 9, 2013
One comment

T-SQL Tuesday #44 – Whoa. Déjà Vu…It Happens When They Change Something

 

Bradley Balls  (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 Second Chances. Bradley has asked us to write about something we would like to have changed if we were given a second chance. I’m going to write about something that I actually was given a second chance to do differently.
Setting the stage

[Neo sees a black cat walk by them, and then a similar black cat walk by them just like the first one]
Neo: Whoa. Déjà vu.
[Everyone freezes right in their tracks]
Trinity: What did you just say?
Neo: Nothing. Just had a little déjà vu.
Trinity: What did you see?
Cypher: What happened?
Neo: A black cat went past us, and then another that looked just like it.
Trinity: How much like it? Was it the same cat?
Neo: It might have been. I’m not sure.
Morpheus: Switch! Apoc!
Neo: What is it?
Trinity: A déjà vu is usually a glitch in the Matrix. It happens when they change something.

The Matrix (1999)

First past

This is an ironic topic. Today is the last day at my current job and the topic I’ve chosen to write about has to do with one my first assignments four and half years ago. I first arrived at my company in January. Do you know what happens in January? The Sales Team has a new structure that needs to be applied to all of their reports as of yesterday. That particular year, they added a new layer to their hierarchy. The database model couldn’t handle it and neither could the reports. I proposed a new model using recursion, both in the database model and in the reports and it was approved. It proved to provide flexibility in the years to come. It had one flaw remaining though. It had maintained the current practice of assigning Clients to Sales People. That doesn’t sound too bad, until you know that when a Sales Person leaves, all of their Client records have to be updated… one by one by someone in sales. It also caused problems when there wasn’t a Sales Person available to assign to the clients right away.

Changing direction

This past January I had an opportunity to improve upon my original design. I simply changed directions. In the past, each Client had a Sales Person and each Sales Person had a Territory. Now, each Client has a Territory and each Territory has a Sales Person. If someone leaves, only ONE Territory record needs to be updated with a new Sales Person. If a new Sales Person is not available, then the Territory still shows up in the reports. This change was completely transparent to the report users.

I created a sample database model to show the relationships.

TSQLTuesday44 - DB Model

Data Model Created in Erwin

The cherry on top

The best part came a month after the new model was implemented. The Sales Team needed to have a single Sales Person represent different Territories in different Parent Territories. That was not possible with the old model. A Sales Person could only have one Territory, but with the new model it was possible… and it was already in place.

Thanks for all the fish

Thanks go out to Bradley Balls for hosting this months T-SQL Tuesday blog party. Please visit his website at http://www.sqlballs.com.

Category: Community, Database Modeling, T-SQL Tuesday | Tags: , ,
| June 11, 2013
One comment

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.

 

Category: Execution Plans, T-SQL, T-SQL Tuesday | Tags: , , , ,
| May 14, 2013
4 comments

T-SQL Tuesday #42 – My Journey on a Roller Coaster

Wendy Pastrick (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 the Long and Winding Road.

Anticipation of the roller coaster

Today is T-SQL Tuesday, literally. I’m sitting in Mother’s Market Cafe, drinking my coffee and succumbing to the fact that I should be part of this month’s blog party. You see, I wasn’t going to write this month. I didn’t know what to say, but then I read Pat Wright’s (b|t) T-SQL Tuesday’s post and I was inspired to write about my journey, because it has been a tough one.

Getting on the roller coaster

I went to Cal Poly, San Luis Obispo to study Mathematics in the early 90’s. The first thing I did was sign up for a computer programming class because I knew it was going to be important, but I had no idea how important. My third year there I realized that I didn’t like my major. (Too much theory.) I struggled with the decision to study computer science or statistics. I took what I thought was the easier path, Statistics with a concentration in computer science. Pro tip: It was not the easier path, but I don’t regret it.

My first dataset I worked with was on a 10 year study of lobsters. I found that I liked data. I also found that I absolutely loved the one whole database design class that was available. I graduated in 1994 without a job. Why? Because jobs were scarce in the field of statistics where I was moving to and Dice and Monster had yet to be written. I did however have an opportunity to teach… computer programming. Specifically, Microsoft Visual Basic and I liked it.

The roller coaster goes up

As time went on I went from teaching to consulting. I liked programming, but I wasn’t in love with it. I did love writing SQL and I was good at it. I kept finding myself on the teams who wrote the business objects. Once in a while I would even get a say in the database design.

The roller coaster goes down

While I did enjoy writing business objects and SQL, I did not like the way I was treated by some of my colleagues and even a manager that I had. As my self-esteem was walked all over by these people, I got more and more dejected. After I had my second beautiful daughter I decided I was through with IT and that I would never return. (Ominous music played here.)

I went back to college to be a high school Mathematics teacher… And that didn’t work out too well for me, so I went back to what I knew best, computer programming. With my self-esteem low I took a position I was over qualified for. I met some great programmers at this new company and some real big jerks whom we shall not talk about. After three years I left the company.

The roller coaster banks left

I had always wanted to pursue being an artist and this seemed to be an opportunity to do so. I was able to pursue being a jewelry designer and a glass artist for three years before reality started to set back in that the private education my daughters were enjoying cost money. Money that my art was not bringing in. Pro tip: This is not the profession to get into during a recession. I had to go back… and I didn’t want to.

The roller coaster goes up

I refused to go back to being a VB.net programmer. What else was there? A friend of mine had a position at his company authoring reports. He needed someone to come in and convert old Crystal Reports to SSRS reports and to author new ones.  I had worked with both Crystal Reports and SSRS in the past so I accepted the job. The most amazing thing happened. I enjoyed my work and there wasn’t anyone tearing me down. In fact, there was someone on the team who took the time to rebuild my self-esteem and I am so happy he did.

The roller coaster goes faster

A year ago I realized that I wanted to learn more about writing better queries, data warehouse design, SSAS, and everything else about the SQL Server stack that I could absorb. I started attending a few conferences. I started looking for user groups and blogs to read. And the cherry on top was finding our SQL Family who encouraged me to start teaching again. This is the best ride ever.

Retrospective

How does this tie into technology? Well, it’s more about how we tie into technology. Different personalities tie into different professions. Twelve years of my career were spent working with the wrong technology. I shouldn’t have pursued computer programming for all those years, but database programming. Unfortunately, back then you had to do the database administration as well as the database programming and the administrators life is not for me.

As I move forward with my career I am eager to learn more about the SQL Server stack and to build relationships within the SQL Server community. I also look forward to how the SQL Server stack continues to change to the meet the needs of the world.

Thanks for all the fish

Thanks go out to Wendy for hosting this months T-SQL Tuesday blog party. Please visit her website at http://wendyverse.blogspot.com.

Category: T-SQL Tuesday | Tags: ,
| April 9, 2013
4 comments

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.)

Category: Speaking, SQL PASS, SQL Saturday, T-SQL Tuesday | Tags: , , , ,
| November 13, 2012
Comments Off on T-SQL Tuesday #36 – What Community Means to a Newbie

T-SQL Tuesday #36 – What Community Means to a Newbie


The 4th quarter of 2012 has marked many firsts for me. I attended my first SQL Saturday and my first PASS Summit. I joined my first SQL User’s Group. I started a blog, and now I’m participating in T-SQL Tuesday. This month’s topic is about what community means to me. Since I have only recently found this amazing community, I’m blogging about the community from a Newbie’s perspective.

In the beginning…..

I knew no one in the SQL community outside of my IT department. The database side of my IT department has less than 5 people. I desperately searched for a local SQL Users group, but I couldn’t find an active one, so I widened my search criteria. I found I was half way between the LA SQL PASS chapter and San Diego SQL PASS Chapter, both of which are 50 miles away and a 1.5 to 2 hour drive on a Thursday night. What to do, what to do.

I lucked out. On Sept 15th, 2012 the San Diego SQL PASS Chapter (SDSQL) was hosting a SQL Saturday (#157).

It changed my life.

I drove down to San Diego (by myself) not expecting much. I took Jason Strate’s (B|T) session on Discovering the Plan Cache. His session was so good, that I changed my schedule and attended his other 2 sessions. By the end of the day, I was happy with the day, but I had hardly said two words to anyone. I had really wanted to network, but my shy side had stopped me (yes I do actually have a shy side. It’s very small, but it’s there). Phil Robinson, the president of the chapter, invited everyone to a local sports bar for dinner and I went.

This is where the magic happened.

Before I even went into the building I was greeted by someone from Quest. She happens to work in the building next to me. She introduced me to others and I finally began to network. As the night moved on, I met Phil Helmer (B|T) and everything really changed. He introduced me to other frequent members of the local chapter, as well as some of the speakers. We talked about the PASS Summit 2012, which I wasn’t going to be able to attend until 2013. After talking to Phil for a couple of hours, I decided I wanted to go this year, so I convinced my manager to let me go.

Phil was also the one who convinced me to set up a Twitter account, which I’m now an active member of. Twitter has provided me a way to stay in touch with the SQL community. It has also provided a way to help others with SQL questions.

That night I also struck up a conversation with Benjamin Nevarez (B|T). I told him how I use to be a Microsoft Certified Trainer and I wanted to start speaking on SQL topics in a year or two. He thought that goal was too far away and that I should speak at the next Huntington Beach SQL Saturday coming up in early 2013. He was very encouraging and has convinced me to submit a proposal.

But it gets better.

Fast forward to a couple of weeks prior to SQL PASS Summit 2012. I received an email from Joe Fleming (T). He was to be my mentor for Summit 2012. He was so helpful, answering all my questions and making sure I met people at the Summit. He’s not the only one either. Before the Summit even started, I had met new people on Twitter who would be attending the conference. Once I arrived at the Summit, I didn’t sit still for a week. I met so many fabulous people, and they accepted me, just the way I am. I never felt like the ugly stepsister. I never felt like the eccentric artist. I was never shut out because I’m Christian. I was accepted.

I was part of the SQL community.

Update: After reading the other T-SQL Tuesday #36 blog posts. I realized that I hadn’t thanked the host, Chris Yates (B|T) for hosting this month’s SQL blog party. Thank you Chris.

Category: Community, SQL PASS, T-SQL Tuesday | Tags: , , , ,
  Recent Entries »
%d bloggers like this: