21Jul 2010 by Grant Fritchey 5 Comments

SQL University: Introduction to Indexes, Part the Second

SQL Server, T-SQL
Welcome once more to the Miskatonic branch of SQL University. Please try to concentrate. I realize the whipoorwills singing outside the window in a coordinated fashion that sounds almost like laboured breathing can be distracting, but we're talking about indexes here. We left last class with a general idea what an index is, now it's time for some specifics. There are several different kinds of indexes, as we talked about last class. But the two you're probably going to work with the most are clustered, non-clustered. Each of these indexes is stored in a structure called a B-Tree, a balanced tree, not a binary tree. That's a very important distinction. A B-Tree is a double-linked list that is defined by the keys of the indexes on the top and intermediate pages, and…
Read More
19Jul 2010 by Grant Fritchey 6 Comments

SQL University: Introduction to Indexes, Part the First

PowerShell, Spatial Data, SQL Server, T-SQL
Right, all eldritch tomes are to be closed and Elder Signs are to be put away during this course. Welcome to the History department here at the Miskatonic branch of SQL University. Why the History department? Well, first, because I like history and have frequently thought I would enjoy teaching it. Second, because I needed a hook upon which to hang part of the story I want to tell. What story is that you ask? Why, the story of the Dewey Decimal System. We are interested in studying history and historians must classify our subjects carefully. For advanced students we'll be covering the Library of Congress Classification System and the... Right, I give, this is the introductory class on indexes. If you thought we were covering something exciting and sexy like PowerShell,…
Read More
15Feb 2010 by Grant Fritchey 4 Comments

nHibernate Database, First Look

nHibernate
I'm getting my first look at a full-fledged nHibernate database developed by consultants for our company. I thought I'd share my initial impressions. I'll be capturing trace events from the database over the next couple of weeks, so I'll be following up on the behavior of nHibernate within this database as well. The first thing I saw & thought was, "Foreign key constraints. Thank the gods." That really is good news. I was frankly concerned that they might go with the "let the code handle it" approach. There are quite a few null columns. I'm also seeing tons & tons of nvarchar(255) which must the default string size. Lots of bit fields too. They also used bigint in a lot of places too. None of this is definitively good or bad,…
Read More
01Dec 2009 by Grant Fritchey 4 Comments

How do -You- use SQL Server

nHibernate, SQL Server, T-SQL, Tools, Visual Studio
I've been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That's a tough one. It would make a much shorter, and easier, blog post to describe the things we don't use it for. However, keeping with the spirit of these tags, I'll try to lay out it. For those that don't know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we'll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70's and 80's propped up by ancient men with pocket protectors, spit, bailing wire…
Read More
15Sep 2009 by Grant Fritchey 2 Comments

More Free Training

SQL Server, T-SQL
Quest Connect 2009, taking place in October 21 for 24 hours, looks like it's going to have 64 different sessions, live and recorded, by a variety of the names in the industry. It's another chance to dig in and learn the details on a variety of topics from some of the top names in the business. Can you say Tom LaRock? How about Tim Ford? I know you want to hear from Brent Ozar. Those are just some of the featured speakers. There are a whole slew of others, it's worth pursuing, and did I mention, the price is right. I recorded a session for them last night. It's on the basics of understanding execution plans.
Read More
14Sep 2009 by Grant Fritchey No Comments

Editorial on SQL Server Central

Misc, Tools
My first one over there. It's discussing whether or not you should do two things, build your own monitoring tool, come out in particular favor of one tool or suite of tools from a single vendor. Please read it and watch the video. And, even more importantly, leave a comment in the discussion.
Read More
09Sep 2009 by Grant Fritchey 8 Comments

Spools in Execution Plans

SQL Server, T-SQL
I got the question the other day, when are you likely to see a spool in an execution plan? Easy, whenever SQL Server needs to walk through the data multiple times, usually in JOIN operations... Yeah, well, once again, my flip answers are not quite the entire story. Spool operations are temporary storage of the data for later reuse in a query plan. There are two types of spool operations, eager spool and lazy spool. A spool is basically a temporary table created within the execution of the query that is used when it's likely that data will be needed again, and again during the execution of the query. This is not an explicit #temp temporary table, but a work table for operations within the processing necessary for a given query's…
Read More
08Jun 2009 by Grant Fritchey No Comments

Microsoft SQL Server Premier Field Engineers

SQL Server, T-SQL
Joe Sack has started a new team blog for the Microsoft SQL Server Premier Field Engineers. If you don't know who they are, you should. The first post is just introductory, but this blog is likely to become a great resource. These are the guys that MS zip lines into tough situations with the expectations that they'll improve them. I'd strongly suspect these are fellows worth listening to.
Read More
30Mar 2009 by Grant Fritchey 4 Comments

Reading to Learn

Spatial Data
I just finished chapter 1 of Alastair Aitchison'snew book on SQL Server spatial data, "Beginning Spatial with SQL Server 2008." If this is the beginners book... oh boy. The advanced book must be insane. Seriously though, Mr. Aitchison seems to have written a fantastic book. I'm going to tear through it as fast as I can because I've got two projects that are looking to start using spatial data and quite frankly, I'm a bit lost. There's a great discussiongoing on over at SSC as to the worth of technical books for DBA's. It's based on this editorialby Tony Davis. I'm surprised by the number of people who say they don't use books. It seems that a lot more people use blogs and articles and discussion groups to learn. Maybe…
Read More
19Mar 2009 by Grant Fritchey 13 Comments

Execution Plan Estimated Operator Cost

Uncategorized
I've said it over and over again, the costs on operators in execution plans, even in actual execution plans are estimates.  You need to understand that when looking at your execution plans. It's vital because you need to be able to distinguish between the truly costly parts of a plan and the less costly parts of a plan. Don't believe me? Take a look at this picture and see if you can spot the discrepancy: Spot it yet? Add up the costs for the operators visible in the part of the plan... Yep 125%.  And there's more to the plan that I'm not showing. I think this one must total near 200%. The statistics are up to date and there's no consistency errors in the database. These estimates are just off sometimes.…
Read More