04Oct 2010 by Grant Fritchey 1 Comment

How to Tell if Execution Plans are Reused

SQL Server, T-SQL
I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I've seen a lot lately is along the lines of "how do you know if an execution plan is being reused." Since compiling an execution plan can be an extremely expensive operation, it's worth your time to understand how well a given plan is getting reused. If you've seen me present, I'll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren't getting plan reuse it would have been an even worse system than it was. There are…
Read More
27Sep 2010 by Grant Fritchey 3 Comments

Parameter Name Size And Performance

SQL Server, T-SQL
I saw an odd statement the other day, "The size of the name of the parameter does not affect performance." My first thought was, "Well, duh!" But then, I had one of those, "Ah, but are you sure" thoughts. And you know what, I wasn't sure. If size of the parameter name did affect performance, I figured, the one sure place where that would be evident is in the size of the execution plan. Right? I mean, if there was an impact on memory, and hence on performance, that's probably where you'd see evidence of it. I wrote two queries: DECLARE @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance int SET @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance = 572 SELECT soh.SalesOrderID ,sod.SalesOrderDetailID FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance DECLARE @v int SET…
Read More
11Aug 2010 by Grant Fritchey No Comments

Viva Las Vegas!

SQL Server, T-SQL
I won't be going to Las Vegas, but I will be presenting to the Las Vegas SQL Server Users Group, S3OLV. The sesssion will be "Introduction to Execution Plans." Please swing by if you're in the area. I'm not sure if they'll make the LiveMeeting available to the public or if they'll record the session. But if they do, please attend that way too.
Read More
23Jul 2010 by Grant Fritchey 6 Comments

SQL University: Introduction to Indexes, Part the Third

SQL Server, T-SQL
Nice to see most of you have managed to fight your way through the shoggoths outside to attend another lecture at the Miskatonic branch of SQL University. This will be the third and final part of the introduction to indexes lecture. Please, if you're going mad, step out into the hall. Our previous two lectures introduced the concept of indexes and then talked about two types of indexes, clustered and nonclustered. This lecture will cover the concept of statistics as they relate to indexes. If you followed the previous lecture then you know that indexes are stored in a Balanced Tree or B-Tree structure. You know that this storage mechanism is intended to provide fast retrieval of data. But, how can the query engine inside SQL Server know which index…
Read More
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
13Jul 2010 by Grant Fritchey 10 Comments

Red Gate SQL Source Control

SQL Server, T-SQL, Tools
You just have to love Red Gate tools. They find the small area that they want to cover and then they cover it extremely well. I rave regularly about SQL Prompt and SQL Compare and SQL Search (free one, btw). I've got SQL Data Compare and SQL Data Generator open & working on my desk regularly. I'm dabbling in their other tools fairly often as well. I just like Red Gate tools. I guess my constant & consistent praise is why I'm a "Friend of Red Gate." I like to mention that before I start praising their tools some more, just so no one thinks I'm hiding it. Why would I hide it? I'm proud to say it. I am a Friend of Red Gate! ... anyway... where was I... right, new software.…
Read More
24May 2010 by Grant Fritchey 2 Comments

Slick New Software

SQL Server
I'm lazy. And frankly, I'm not ashamed to admit it. When a software comes along that can do the work for me, even work I can do just fine on my own, I'm interested. When that software is inexpensive, even better. When it's free... I just got word that Confio is putting together a free version of Ignite, their performance monitoring software. It's basically going to be the current view of performance and not have all the historical tracking and nifty trend reports. Yeah, it's a tease, but it's a tease on a product that focuses on monitoring wait states, something you should be doing. Just remember, reference above, I'm lazy. The thing is, they need some people to help them beta test. Here's the word I received: If you…
Read More
06Apr 2010 by Grant Fritchey 2 Comments

Confio Ignite: First Impressions

SQL Server
One of the national sponsors for SQL Satuday is Confio Software. Just because they sponsor SQL Saturday, it's worth checking out their product, but they also host the speaker's dinner at these events. So, having eaten off their dime, I felt obligated to take a look. I'm glad I did. Confio Ignite is a monitoring software that keeps real-time and historical track of the performance of your SQL Server (and Oracle and DB2) database servers. It's focus is on wait states and queues, a very common method for troubleshooting performance. You can get a trial download from their web site to run for a couple of weeks yourself. Everyone's first impression of a software, after the web site of course, is when go to do the install. The install routine for Ignite was very…
Read More
18Mar 2010 by Grant Fritchey 5 Comments

Undocumented Virtual Column: %%lockres%

SQL Server, T-SQL
One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don't ask). I'd never tried doing that before. Obviously if you hit the DMV sys.dm_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented "virtual" column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash…
Read More