SQL Saturday #34

January 27, 2010 at 11:59 am (PASS) (, , )

The event is this Saturday. Take a look at our sponsors, speakers and the program. It’s going to be a great opportunity to learn about SQL Server and things around SQL Server. If you’re in the New England area, please register and take advantage of this excellent event. 450 of your peers are already pledging to show up. This is going to be a good time for networking too.

Advertisements

Permalink 8 Comments

A Call to Arms

January 25, 2010 at 10:07 am (SQL Server 2005, SQL Server 2008, TSQL) (, , )

Phil Factor’s most recent guest editorial over at SQL Server Central has, to a degree, pointed out that the emporer’s spiffy new outfit… well, it’s not exactly there. That’s why he looks so nekkid.

But seriously, the very idea of naming objects inside of the database with Hungarian-style notation really should end. Phil’s right. There’s absolutely no reason why you should name a unique index ixuTableName when UniqueTableName or TableNameUnique would do the job just as well and not be nearly as obscure. I confess to using this type of naming convention all the time, but I’m realizing that I don’t need it and it’s largely just habit.

There are possible exceptions, for instance you want to group all lookup tables in your database so you name them lkTableName, but why not LookupTableName? It’s a little more typing, but with typeahead available, for free, in Management Studio and excellent add-ons like Red Gate’s SQL Prompt, who types out the full name of any of the tables anyway?

I don’t know any developers that are using Hungarian notation in their code these days. Everyone is working with objects and dealing with them like columns in a database table as far as names go (yeah, I know people put Hungarian notation in column names too, but that’s pretty rare). Why keep doing that kind of thing in databases?

Go over, read Phil’s editorial, and the comments about it as well. See if you’re not on board with breaking this bad habit. No more tibbling!

Permalink 3 Comments

PowerShell Script for Creating Indexes

January 21, 2010 at 12:14 pm (PowerShell, SQL Server 2005, SQL Server 2008, TSQL) (, , , )

I needed to create an identical index on a bunch of tables within one of my projects (yes, I know this is problematic on multiple levels and I’m working on that too). Rather than sitting around typing this up, I decided to use PowerShell to do the work for me. I’m still very much learning how to do things in PowerShell so this took me almost as long as it would have to type them up, but now I know more than I did.

Having gone through the pain of trying to find a good example on the web that did exactly what I wanted (they’re out there, just hard to find), I decided I’d add this one in so the next person had at least one more source of information.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Smo") | out-null
Set-Location C:\Scripts
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
$database = $server.Databases["MyDB"]
foreach($table in Get-Content "tables.txt")
{
    #Set the table to the latest one from the list need to make this more generic
    $currenttable = $database.Tables.Item($table.TrimStart("SCH."),$table.substring(0,3))
    
    #create an index, linked to the table
    $index = new-object Microsoft.SqlServer.Management.Smo.Index #($currenttable)
    $index.name = ("ix_"+$table.Replace(".","_"))
    $index.Parent = $currenttable
    
    #create the columns & set their properties
    $col = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
    $col2 = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
    $col.Name = "MyFirstColumn"
    $col2.Name = "MySecondColumn"
    $col.Parent = $index
    $col2.Parent = $index
    
    #add the columns to the index
    $index.IndexedColumns.Add($col)
    $index.IndexedColumns.Add($col2)
    
    #add the index to the table
    $currenttable.Indexes.Add($index)
}

Basically the script opens a file and walks through the entries in the file. Make sure you don’t have extra carriage returns or blank lines or it’ll try to find blank tables which will raise an error (error handling is my next task). You have to set the $currenttable using either the Item or use the Where-Object function. The documentation on that is either flat out wrong, or there’s a bug. Once you’ve got the table set, it’s pretty simple. Create an Index object, IndexedColumn objects and add the IndexedColumns to the Index and the Index to the table. You’re done.

I created about 50 indexes in almost no time with the script and it would have taken quite a bit longer to do manually. That’s not counting the time pounding my head against the wall because the documented syntax wouldn’t work.

Easy stuff, totally straight-forward, but if you’re trying to learn PowerShell, things like this seem daunting.

Thanks to @BenchmarkIT, @Laertejuniordba (who has a new article on Simple Talk and will be writing one for SQL Server Standard) and @cmille19, who all contributed to getting me over the hurdle on create the table object. Read everything that Allen White writes and commit it to memory.

Permalink 1 Comment

What three events brought you here?

January 18, 2010 at 2:18 pm (Misc) (, , , )

Another one of the DBA bloggers games of tag is occurring. I’ve been asked by Tom LaRock to answer Paul Randal’s question; What three events brought you here. Well, mom was a cheerleader and dad was a football player, so… Oh, wait. I guess I misunderstood the question. He means what three events lead me to becoming a data geek. Well, that’s completely different. Luckily, no (further) cheerleaders will be harmed in making this (part of the) story.

Geek

When I was 16 years old and Jimmy Carter was President, Radio Shack was still considered to be the place for aspiring geek wannabe’s. It just so happened that I hit $500 in my bank account about the same time they started pushing this radical thing called a “personal computer.” I bought the base model TRS-80 with a whopping 4k of memory, an OS, a keyboard and a monitor. I supplied the cassette tape player (I’m providing links to some of the more arcane bits of technology since you young punks won’t know what I’m talking about) for storage. I also got one game, Space Warp. I started learning basic. I wrote up a random encounter generator for the Traveler role playing game that used all 4k of memory. I was hooked.

I want to Direct

But, instead of going to MIT & pursuing computers, as I should have, and unlike Paul Randal, I joined the Navy. While there I became great friends with a guy that was sold on getting out & going to film school. That sounded like great fun. So I did that. I went to film school and I started doing indie work in NYC. But, unfortunately for my film career and fortunately for my database career, NYC was not the place to be for indie film or to really break into the film business in the late 80’s. I should have been in LA or maybe up in Canada or down in the Carolina’s, but NYC was largely over as place to get started (very strong community, but few opportunities for up & comers). I supplemented my film jobs with temp work, mainly typing letters & doing data entry. One day I was asked what I knew about databases. The correct answer was nothing, but what I said was, “What do you need?” They needed a Paradox database that could store names for a mailing list. “I can do that.” I ran out & bought a Paradox book and stayed about two chapters ahead of what they needed until I had a fully (mostly) functional system up & running. I shudder to think what the thing must have looked like now, but at the time I was a hero. I got a lot more work from these guys and stopped worrying about my film career, because, I still loved computers.

You need to do something

I had been working in IT full time for about 10 years. I’d spent most of that time doing development, first in Paradox, later in VB. I was working for my first dot com. Our DBA had quit, but we’d just kept going, but after a while, we were really feeling the pain of not having someone spend all day, every day, looking at the database. Finally, I went into the bosses office and went on a total rant. “We need backups. We need consistency checks. We need someone to vet the design and validate the code.”  Blah, blah, blah. He waited until I ran out of steam and then said, “OK, what are you going to start with first?” A DBA was born.

That’s about it. You could pick any number of events, but these are the ones that kind of stand out for me.

Tagging:
Gail Shaw (because I always do, and she’s interesting)
Tim Mitchell (because he’s interesting)
TJay Belt (because he’s interesting too)

Permalink 4 Comments

Database Design Process

January 18, 2010 at 11:35 am (Misc, Tools) (, )

Buck Woody recently asked a question; how do you design a database. He outlined the process he followed and asked four questions about how each of us do our work:

  1. What process do you follow?
  2. How important are the business requirements?
  3. What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?
  4. What’s your biggest pain-point about designing?

Funny enough, I haven’t done a full on database design in over a year. My company just finished about 6 years of very hard-core engineering work, designing and building or redesigning and building, the majority of our data input and collection systems. Then, I was doing lots of design work. Now, we’re either largely in maintenance mode for most of those systems, or the few new major projects we’re working on are using CRM, no database design, or hoping that database design will never, ever matter and using nHibernate to build the database on the fly, based on the object model (this, by the way, is still in super-double-secret probation development mode. I haven’t seen what they’re producing). All that means we’re doing very little design work now. That will change.

Process

The process I follow isn’t radically different from Buck Woody’s. I get the business requirements, which are hopefully written on something more substantial than a cocktail napkin, and with more detail than will fit on one, and I read them. Or I meet with the business people and ask lots and lots of questions, which I’ll probably do even if I have written requirements. Sometimes, especially at my current company, I’ll get a full logical diagram from another team. I’ll still basically do the same design work, even if I have a logical model, but I’ll use it as a reference point to double-check my understanding of the requirements. From there:

  1.  Identify the nouns. Figure out what kinds of objects, or things, or widgets that we’re talking about needing to store.
  2. Figure out the relationships between the widgets. What’s a parent and what’s a child and which of the widgets is related to many other widgets or to one other widget, etc.
  3. Lay out the attributes for the widget, meaning the columns in the table. Big points here include getting the data type correctly identified and figuring out which of the attributes are required and which are not. Further, which of the attributes come from pick lists, which means look-up tables.
  4. Identify the natural key. I’m with Buck, most of the time I use an alternate, artificial key (yeah, frequently an identity column), but I want to know the natural key so that I can put a unique constraint on the table, in addition to the primary key. This is a vital, but often missed step, in terms of the business processes being modeled.
  5. Figure out where I’m going to put the clustered index. Usually, but not always, this will be the primary key, but I do it as a fundamental part of the design. That means, as a fundamental part of the design, I think about the most common access path for the data. That’s going to be where the cluster needs to be.

How Important Are the Business Requirements

Buck, I respect you and I like you and I hate to do this, but you’re kidding with this question, right? No requirements, no database. The requirements are all. The most important reason you want the requirements written down is because that means that business at least thought them through, all the way, one time. It’s your best bet that you’re going to deliver something that slightly resembles what the business wants and needs. I live and breathe by the requirements. When someone from the business corrects my design, “Oh, that widget is related this thingie, not that watchamacallit,” I get them to change the requirements to reflect that new information. This way, when someone wonders why I did what I did, they’ll always be able to see the requirements the way I saw them.

Tools

I use Embarcadero’s ERStudio. I know there are other ER tools on the market, but I fell in love with this one on the day I needed to change the data type on a column that was in about 30 different tables and I did it in a couple of minutes using their scripting tool. I’ve been using it ever since, and we’re talking well more than ten years now. It’s just great. I only use it for the initial design and for documentation after the design. Once the design is done, the first time, and a physical database is constructed, I don’t work from the ER diagram to do builds and deployments, I work from source control. Do I have to do it like this? No, but I really enjoy the power of an ER tool while I’m doing the design because it lets you do a lot of changes, quickly and easily without having to rebuild a database over & over.

Biggest Pain Point

The largest pain point has to be changing requirements. Change happens. I embrace it. I’ve worked on agile projects and I like the general approach and mind set. And yet, changing databases is hard. It’s not so bad when you’re in the strict, isolated, ER diagram only stage, but as soon as you’ve built the database, even one time, change gets difficult. It’s not so bad if you work closely with the design, test & development teams and get their buy-in, early, that test data must be tossed & rebuilt with each fundamental design change. But that’s a hard agreement to get and so you end up spending a lot of time trying to retain the test data AND fundamentally redesign the data structure. This is not an enjoyable combination.

That’s about it. I would say the one thing I try to do, and it’s not easy, is be open to doing silly stuff. I try, and I don’t always succeed, to let the business or developers or logical modelling team make silly choices after I carefully tell them why they’re silly and the likely outcome. I do this because fighting them on every single silly decision is a losing proposition. Plus, it saves you for the fights against the stupid, as opposed to silly, things that come along occasionally.

Permalink 6 Comments

SQL Server Standard Volume 7 Issue 1

January 13, 2010 at 3:16 pm (sql server standard) ()

Kathi Kellenberger‘s fantastic new article is available in the latest issue of SQL Server Standard. There are a lot more articles in the hopper. Keep an eye out for them. We’re providing you with the best writers giving us some of their best stuff. Go and check it out. If you’re not a member of SQL PASS, it’s free to join, and you’ll get access to this article, lots more like it, as well as other stuff.

Permalink Leave a Comment

Excel Within Management Studio

January 13, 2010 at 2:43 pm (SQL Server 2008) (, , , )

Aaron Bertrand has put in a Connect request for a feature whereby data returned as a grid from a query can just go right into an Excel spreadsheet. All in favor? Opposed? Motion passed. Go and vote this one up, up, up.

Permalink Leave a Comment

Connections Sessions Evals

January 13, 2010 at 9:09 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , )

I’ve kind of been embarassed to post these despite the fact that I received them a couple of weeks ago. Overall, I’d say they’re very good, and I’m quite proud of them, but one comment still has me upset. Anyway, here we go:

DMV’s For Performance Tuning (same session as PASS): 7 responses

Q1. Speaker’s knowledge of topic
Your average score for this session: 4.0
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.74
Lowest score (all SQL speakers for this question): 3.0

Q2. Speaker’s presentation skills
Your average score for this session: 3.86
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.47
Lowest score (all SQL speakers for this question): 2.0

Q3. Content of Speaker’s slides/visual aids
Your average score for this session: 3.86
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.48
Lowest score (all SQL speakers for this question): 2.5

Q4. Speaker’s ability to control discussions and keep session moving
Your average score for this session: 3.86
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.53
Lowest score (all SQL speakers for this question): 2.33

Q5. Accuracy of session description
Your average score for this session: 4.0
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.33

Q6. Overall evaluation of this session
Your average score for this session: 4.0
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.25

Comments:
• I loved the session. Excellent information presented in a “fun” format.

My comments? What’s not to like? I love how you see the highest speaker score, the average speaker score and the lowest speaker score. This really allows you to place yourself in context to the other speakers (PASS, please take note).  Based on this, the only area that I think I need work is on controlling discussions… except I really like to have more discussions, so I’ll just have to watch the balance on this one.

Scouting Out Execution Plans: 18 responses

Q1. Speaker’s knowledge of topic
Your average score for this session: 4.0
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.74
Lowest score (all SQL speakers for this question): 3.0

Q2. Speaker’s presentation skills
Your average score for this session: 3.83
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.47
Lowest score (all SQL speakers for this question): 2.0

Q3. Content of Speaker’s slides/visual aids
Your average score for this session: 3.82
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.48
Lowest score (all SQL speakers for this question): 2.5

Q4. Speaker’s ability to control discussions and keep session moving
Your average score for this session: 3.72
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.53
Lowest score (all SQL speakers for this question): 2.33

Q5. Accuracy of session description
Your average score for this session: 3.88
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.33

Q6. Overall evaluation of this session
Your average score for this session: 3.88
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.25

Comments:
• Good session and overview of Performance Point.
• Please have Grant back next year!
• Great examples. Hope these are part of the slides that we will have access to. One of the best sessions at the conference.

Again, I’m pleased as punch by the evals and the comments. Assuming it’s not the week after the PASS Summit again, yes, please have me back next year. I think at least one eval here was mislabeled. Performance Point? Again, comparing my ability to control sessions with the average & max, I can work on this a bit. Now for the embarassment.

More Unnecessary Query Tuning: 16 Evals

Q1. Speaker’s knowledge of topic
Your average score for this session: 3.94
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.74
Lowest score (all SQL speakers for this question): 3.0

Q2. Speaker’s presentation skills
Your average score for this session: 3.69
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.47
Lowest score (all SQL speakers for this question): 2.0

Q3. Content of Speaker’s slides/visual aids
Your average score for this session: 3.56
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.48
Lowest score (all SQL speakers for this question): 2.5

Q4. Speaker’s ability to control discussions and keep session moving
Your average score for this session: 3.69
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.53
Lowest score (all SQL speakers for this question): 2.33

Q5. Accuracy of session description
Your average score for this session: 3.5
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.33

Q6. Overall evaluation of this session
Your average score for this session: 3.63
Highest score (all SQL speakers for this question): 4.0
Mean average score (all SQL speakers for this question): 3.5
Lowest score (all SQL speakers for this question): 2.25

Comments
• Very rude to participants.
• Good focus on useful topics.
• Hope you feel better, Grant.
• Clear examples that illustrated the problems and solutions.
• Great examples of common pitfalls to watch out for before there is a problem.

Very rude. This could be three things, only two that are under my control. I did actually have to walk out of this session, right in the middle of it for about 2 minutes because I was physically unwell (and that’s all I’m going to say). If that was how I was rude, I’m sorry, but things happen. But, maybe it was because I joke with audience. I talk about cowboy developers and hyper-control freak DBA’s and stupid support calls, really ignorant management decisions, nHibernate & Oracle. I make fun of all of them. Lastly, it could have been this topic. I’m calling your baby ugly in this one. I’m telling you that using NOLOCK hints, DISTINCT operators, WHILE loops & CURSORS, and any number of other silly crutches are hurting your performance and making it necessary to tune queries when they should just be written correctly to begin with. It’s one of these three. If it’s one of the two I can control, I’d sure like to do better. I’m frankly bothered by this.

This was my lowest eval of the three and I’m just bummed because I put the most work into this session. Still, it appears it was helpful for the majority of the audience, so I’d say it was successful, but that “rude” comment stings a bit.

That was my Connections conference. I did enjoy presenting there and I hope I get invited another time. I mentioned it before, but I’ll say it again, having the high, low & average so that you can compare your performance to others really makes these much more useful.

Permalink 6 Comments

TSQL Tuesday #2

January 12, 2010 at 9:12 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , )

I’ve wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2 and I finally came up with a bit of something.

When you’re looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you’re looking at? Is this a trivial plan, meaning it’s the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it’s prescribed cycles and simply take the best plan it had generated up to that point?

These questions can be answered directly from information available to you in the execution plan. Let’s take two queries, one incredibly simple and one mildly complex:

SELECT a.StateProvinceID
FROM Person.Address AS a
WHERE a.StateProvinceID = 42
SELECT soh.AccountNumber
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.SalesOrderID = 43933

When you run the first query the execution plan looks like this:

It’s clearly a simple plan for a simple query, but what did the optimizer do?

Let’s take a quick look at the next query and it’s execution plan:

A slightly more complicated plan, but again, the question is, what did the optimizer do? Was it able to do a complete and full optimization or did it timeout? How can you tell?

Tune in next week…

Kidding. It’s pretty easy. Take a look at the SELECT operator in both queries. Right click on that operator and open up the Properties window. Here you’ll get a lot of interesting information about the execution plan itself, how long it took, the plan hash & query hash, how much memory it used and, most interesting for this little mystery, the Optimization Level and, depending on that level, the Reason for Early Termination. Take a look at the properties for the first query:

As you can see, the Optimization Level for the first query is TRIVIAL. This means it’s a Trivial plan, no other possible plan could be created for it. Notice also that there is a Parameter List, consisting of one parameter, @1, despite the fact that no parameters were used. This is evidence of simple parameterization. Yet more information that can be found in the Properties.

If we then look at the second query’s  properties:

The Optimization Level in this case is FULL, meaning that it went through a complete cycle within the query optimizer. Looking down, below the Query Hash & Query Plan Hash you can see the Reason For Early Termination Of Statement Optimization, slightly truncated. This shows that a “Good Enough Plan Found.” Which means that the optimizer feels that it’s done everything for this that it thinks it can. Generally, this is a good sign. The other thing you’ll usually see here is Timeout, meaning that the optimizer gave up and used the best plan it had at the moment. That’s generally a bad sign and an indication that you may have tuning opportunities in the query. However, neither of these are hard and fast rules. You can get a full optimization with a good enough plan and still have a pretty stinky query plan. This is just another bread crumb on the trail, another piece of information that you can use to figure out if you’ve got a good execution plan or not.

Permalink 4 Comments

Horror… sort of

January 11, 2010 at 11:18 am (Misc) ()

You must read this post from Tim Ford to understand why I might do this on my technical blog. But Tim’s a friend and the chance to branch out, at least a little, could not be avoided. I hope I don’t lose too many of my eight readers from this….

The sign read “Live Nude Cats,” so of course, I had to go and take a look. It wasn’t the best part of town and I certainly didn’t have any reason to be there, but the sign read “Live Nude Cats.” How could I resist? How could anyone?

I parked and locked the car, scanning the parking lot. Not good. There must be 50 different ways a person could get jumped in here, and did I mention this part of town is less than savory? “But,” I thought to myself, “I’ve started, so I’ll finish!”

I walked up to the entrance. The obligatory muscle at places like this was sitting on a stool outside the door, despite the cold. Why do these guys always look slightly overweight and on steroids. “No cover he says.” I’m shocked. No cover with “Live Nude Cats.” I step in to the dark room, out of the light, momentarily blind. The sound & smell hit first, thumping music like, well, one of those places, the smell of old beer and feces… this is not going to be good.

My eyes adjusted and then I saw it, animals, all types of animals, but cats everywhere. Don’t get me wrong, nothing unnatural was occuring, but there were animals all over the place and guys, all guys, drinking between them, just watching. “Live Nude Cats” indeed. Live Nude Animals. What the heck is this place?

I notice one guy put his beer on the floor for a moment, but as soon as he looks down he’s going to be very sad about the pony, because the silly thing kicked it over. He notices. H jumps to his feet screaming, “What the heck is the point? Just ask George “Let’s Have Padme Die Of A Broken Heart Instead Of Anakin Crushing Her To Death” Lucas. It’s all pointless and meaningliness.”

Maybe that was the message behind “Live Nude Cats.” That there wasn’t a message at all. That it was all as much drivel as the last three Star Wars movies. Let’s face it the script must have been written by simply using some magical tool, library, or bong to bash against a keyboard over & over again.

At that point, my nerve left me and I ran, as fast as I could out of… of… whatever the heck that place was, exploding out the door bursting into the sun, blinded as much as I was when I went in…

Permalink Leave a Comment

Next page »