Sunday, February 9, 2014

SQL Saturday Tampa 2014

Don't forget to come see us at SQL Saturday in Tampa, 2/22. Sign up at SQLSaturday.com.

New this year: I'm doing a pre-con!

This event will be held at USF across from the Sun Dome.  Please check out the event here.

In addition to the main event, we have two pre-cons available on 2/21/2014.

For our DBAs, we have SQL Server Performance Tuning presented by Jeff Garbus.
Topics covered include:
•     Indexing for performance
•     Join performance, self joins, and advanced join techniques
•     Stored procedure performance
•     Advanced session configuration
•     Working with nulls
•     Advanced / new select options
•     Writing dynamic SQL
•     Writing recursive CTEs
               Attend Event


Monday, January 6, 2014

SQL Webinars are now available on YouTube!

You asked for it...

http://www.youtube.com/channel/UC1WvVE0UFVkIGNNXXIJew4Q

We have recorded webinars here including index performance, joi performance, and will be adding with each live webinar we do.

Latest webinar on joins includes:

  • nested loop joins
  • hash joins
  • merge joins
  • recursive joins

... and many variants

Keep an eye out for updates!

Sunday, December 8, 2013

Dallas Ice Storm Odyssey


People sometimes ask me why, when watching John Candy & Steve Martin in  “Planes Trains & Automobiles,” I cried, not understanding that it was supposed to be a comedy. If you read this, you’ll understand. I’m writing this for catharsis, and to amuse. Please delete if you start to cry… this is NOT my worst story.

 

All week, Penny kept saying, “Jeff, watch the weather, Ice Storms coming, you may not make it home.” It’s not that I don’t listen – she’s usually right – it’s that I can’t do anything about it. Ice storm started Thursday night, continued until all the roads & runways were totaled.

 

I have a ticket for Friday afternoon. Friday morning, I’m watching morning flights being cancelled. I attempt to call American Airlines, to see if my flight is likely to go, am told my wait time would exceed 60 minutes. Nope. I wait a little while longer, and as soon as my flight is cancelled, call AmEx travel. Thank goodness for Amex Travel. They wait on hold with AA for me, call me while at lunch & tell me I’m booked out on a later flight which had not been cancelled yet. Watching continues.

 

A little later, 2nd flight is cancelled. Calling Amex Travel back, an hour later I am booked on a still later flight (8:15 this time).

 

I leave about 4pm, begin the 2-hour, 10-mile drive to the airport from the customer site. Dallas doesn’t get much snow & ice, and doesn’t have the equipment to deal with it. Bridges had ice half a foot thick with potholes down to the pavement. Like driving on Swiss cheese, a wonder nobody got stuck. I pitied the car’s suspension.

 

Returning the car to Hertz, no problem … yet… but Dallas doesn’t have Hertz busses, the rental car facility provides busses. Well, not tonight they don’t! there are literally hundreds of people waiting, one fellow tells me he hasn’t seen a bus in half an hour. Well, I’m very early (I have 2 hours), I won’t panic yet.

 

Half an hour later (busses supposedly show up every 5 minutes or so), I get nervous. Pickup is upstairs, Hertz counters are downstairs. Of course, if I go downstairs the bus will come. So, cell phone in hand, I try to call the Hertz counter. 35 minutes later on the phone with Hertz reservations, 3 levels of supervisors up, they still can’t reach the hertz counter or any human being in Dallas who can get me from the rental place to the terminal To add insult to injury, 1) every time they put me on hold I listen to a recording about how if I can’t get to the airport Hertz will pick me up, and each new level of supervisor reminds me that it’s the airports job, to get me to the terminal (I of course remind them that I rent from Hertz, it was their decision & their rent that pays for the busses), but they can’t reach the folks downstairs either. 40 minutes from takeoff for flight 3, I finally give up, go downstairs to the counter (with 4th level supervisor still on the phone with me, explain the situation in a few brief words, tell them I’m missing my flight if we don’t leave NOW. Fortunately, lady manager at the counter details a guy to drive me over.

 

Since he’s taking me in the nice Mercedes crossover, he drives about 5 MPH, but as I picked up my boarding pass at the kiosk, and have tsa prescreen, I am still hopeful. 10 minutes later, we start to approach terminals, and we see miles of tail lights. Not a good sign.

 

As we inch forward, we find that Dallas PD has taken up 3 lanes, for snow removal by bulldozers, and it’s 4 lanes into 1. 3 separate times. Sigh. The driver feels obligated to mention that the airport has run out of the deicing chemical. Oops. I suspect that was a factor in the cancellations.

 

I finally make it to the terminal 7 minutes before doors are to shut on the plane. The  AA app on my cell phone tells me the plane is leaving on time. Great. First flight to leave on time, and I’ve been trying to get to it for almost 4 hours.

 

The driver fortunately knows exactly where to drop me, I grab my bags, and run to security screening. LUCK! I’m the only one there, I can leave my shoes & belt on, I make it through, and…  “Wait Sir! Random screen.” Now, they want to swab my hands to check for Bomb Residue. Isn’t this why I did the prescreen? Ah, well…

 

At this point I have about 2 minutes, but the gate is right around the corner… I hit it at a dead run, looking like OJ in a pre-trial commercial, eyes on the gate door, and see “Flight delayed” … but, oh, fun, EVERYBODY saw me running, I got a round of applause, and half of them gave me a standing ovation. After taking my bow, one of the passengers informs me “Missing a pilot.” Of course, I volunteer to fly the plane, which gets the rest of the passengers to stand up & give me an ovation, until I tell them that since I’m not an AA employee. They probably won’t let me fly the plane. They sit back down, I’ve had my revenge.

 

Hurry up & wait. An hour or 2 later, flight goes from delayed to cancelled, and before I’ve blinked, there are 200 people in line at the kiosk. The announcement: “We have a pilot and flight attendants, but no first officer. Sorry.” Once again I volunteer, once again I am rebuffed.

 

I’m not waiting in that line, I call AmEx travel again (thank goodness for Amex travel). First thing I ask for is a hotel room (in previous calls, I had asked if there was room was told that there were plenty, but reserving one would cost me the night’s stay because I’d go over the 6pm cancellation. I’m too cheap to pay for a room I’m not staying in).

 

As soon as I have the hotel (this took a lot longer than expected), I head down to the taxi stand. (Lisa at AmEx is going to call AA, figure out my flight, and call me back – thank goodness for Amex Travel!) I figure hotel shuttles are going to take forever, because of the inbound issues, and am ready to sleep at that point. Hahahahahaha.

 

I’m told that I have a room at the embassy suites south, 2 miles from the airport, and go to the taxi stand. Did I mention that it was 20 degrees out, and that I only have my lightweight leather jacket? The taxi guy says 30-40 minutes, look at all the people waiting inside (he was a heck of a guy, and did not take tips). He suggested I go downstairs, catch the hotel shuttle which he had seen going by.

 

I find my way downstairs, and am told that I have to call the hotel to ask for the shuttle. I call, and find that they are NOT running shuttles. Sigh. Oh, by the way, what’s your name, we’re overbooked. Sorry, we do not have your reservation in our system. Sigh.

 

On my way back upstairs, I call Amex travel back, and Julie finds me a room at the Omni. Strangely, it’s $100 less than the Embassy (Embassy must be taking advantage of the storm & stranded travelers). She’s going to check on the airline seats. I check my phone, and find AmEx has sent me itineraries with reservations for both the Embassy AND the Omni. Embassy is closer, and I apparently have an 8:24 flight out in the morning, so I decide to take the Embassy reservation, no cab coming back and don’t need to wake up earlier.

 

The taxi guy is putting people in groups based upon destinations, I tell him where I’m going, the Embassy. Two other people get into the cab, then the cab pulls up about 100 feet, and the cab driver starts screaming into his cell phone, there are two passengers who are supposed to join us, and he’s not sure where they are, but from the screaming & what we can overhear, it sounds like they’re at Love field, not DFW. After about 10 minutes of this, a young couple shows up, climbs into the back.

 

About this time, Julie calls me back. She tells me that Lisa has been on with the airline, and since I’m going to the Omni, has canceled the Embassy reservation. Oops. I tell her to hang on, tell the cab driver I’m going to the Omni in Las Colinas. He says no worries. She also tells me Lisa is still on with the airline and will call me back.

 

(it is not until this writing that I realize the two ladies helping me are named the same as my sisters.)

 

Hotel is nice! About 11pm I am called by Lisa with good news / bad news. Bad News: the 8:24 flight is also cancelled but Good news: I’m back to first Class on a 5:30pm flight. More bad news: going to Miami, then TPA, home after midnight (and of course back to Dallas about 12 hours later).

 

I’m going home, darn it, sleeping one night with my wife. Sleep in, have a nice brunch at the hotel, they give me a late checkout, and I am relaxed on my way to the airport a few hours early.

 

Get to the airport, through security (hooray for tsa precheck!), to the gate, look at monitors, and there are 2 TPA flights. At the gate, I ask for one of them, PRESTO! I’m on it.

 

Grab lettuce wraps as a snack, then back to the gate. We have pilot, first officer, but we’re a few stewardesses short. They might be on their way, but haven’t checked in yet. We’ll get updated but for now we’re delayed.

 

I’m happy to offer to fly, but I’m not going to offer to serve coke.

 

Checking the previous flight, Miami flight is 2 hours late, I’d have missed e connection, been stuck in Miami. No help for anybody.

 

Finally, everybody shows up, boards… and I’m on my way home. I’ll be there 9ish, may even catch the tail end of the boat parade party, back to Dallas tomorrow.

 

Thursday, October 24, 2013

What's a more recent stored procedure, Development or Prod?

select

do.name, do.create_date, do.modify_date, po.create_date, po.modify_date


from Development.sys.objects do join Production.sys.objects po on do.name = po.name

where

do.type = 'P'

and

do.name not like 'asp%'

and

abs(datediff (hh, do.modify_date, po.modify_date)) > 2

and

do.modify_date > po.modify_date

order

by do.name

Friday, October 4, 2013

Find an index across databases

Some tools report back the name of an index without the name fo the database it's in... other folks may have an index in some of their SaaS databases, but not all. Here's one way to find them all.
 

 

declare dbs cursor for

select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb') order by name

go

open dbs

 

declare @name varchar(1000), @string varchar(2000)

 

fetch dbs into @name

 

while @@FETCH_STATUS = 0

begin

 

fetch dbs into @name

set @string = 'select * from [' + @name +'].sys.indexes where name like ''_dta%'''

set @string = 'if exists ( ' + @string + ') begin ' + 'select '''+ @name + '''' + @string + ' end'

 

print @string

 

end
 

close DBs

 
Deallocate dbs

Tuesday, July 30, 2013

Upcoming speaking events

Everything you wanted to know about Stored Procedures!  (Webinar)
 




SQL Saturday NYC, August 17, Indexing for Performance (www.sqlsaturday.com)

SQL Saturday Orlando, September 14th, Indexing for Join performance (www.sqlsaturday.com)


Come say "Hi!"

Advice for newbies

Here's a list of things I talk to new IT folks about; they are things I didn't always get right over time, or things that new folks drove me nuts doing:


1)      Accurate is more important than fast

2)      Fast is good as long as it doesn’t violate rule #1

3)      Late is occasionally going to happen; late without warning management should not happen

4)      Try new things. Life is a learning experience.

5)      Ask for help if you’re not sure you’re going to get something right. See #1

6)      Show initiative. You are also being hired for your brain. Use it.
 
7)    You don’t have the whole picture.  If you need more of the picture in order to make smart decisions, ask for it.