27Jun 2012 by Grant Fritchey 5 Comments

The New Phone Book Is Here!

PASS
I can't help it. I get really terribly excited when I publish a book. Maybe it should be old hat. Maybe I should be jaded. But I'm just a 12 year old (it's been argued 10) in reality so I get really, really jumping up & down excited when I get that wad of paper and my name is on the cover. It just doesn't seem to get old. What am I talking about? Oh, sorry. Let me explain. After about nine months of work, my new, revised edition, of the Query Performance Tuning book is available. I want to publicly, and loudly, thank Joe Sack(blog|twitter) for the incredible job he did as tech editor. His hard work, and ruthless criticism, made this book what it is. Despite the scar…
Read More
26Jun 2012 by Grant Fritchey 29 Comments

Problems with my new book

Professional Development, SQL Server
First off, I apologize. As if writing a book wasn't hard enough, now we get new problems because of on-demand printing. Here's the story. Book. Nine months of writing. Excellent technical editing. Great copy editing. Book complete about six weeks ago. Yay! Now things get fun... Here's how it works. Everyone these days uses digital copies of the book and prints on demand. So Apress is printing some copies of the book, but not all. They send a file out to places like Amazon. Amazon uses that file to print some copies of the book, as needed, on-demand. Everyone is, in theory, printing from the same digital file, creating exactly the same book. Or are they? What happens if, oh, let's just say that a file was corrupted somehow prior to…
Read More
25Jun 2012 by Grant Fritchey 3 Comments

Does Encryption Affect Seeing Statements in Deadlock Graphs?

Uncategorized
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: CREATE PROCEDURE DL2e WITH ENCRYPTION AS BEGIN TRANSACTION UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 2 WHERE ProductID = 448 AND PurchaseOrderID = 1255; Then I’ll execute things in the following order. From one connection this query: UPDATE Purchasing.PurchaseOrderHeader SET Freight = Freight * 0.9 --9% discount on shipping WHERE PurchaseOrderID = 1255; From a second connection, my stored procedure: EXEC dbo.dl2e; Then, back on the first connection, this query: UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 4 WHERE ProductID = 448 AND PurchaseOrderID = 1255; That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: <deadlock> <victim-list> <victimProcess id="process472310928" />…
Read More
19Jun 2012 by Grant Fritchey 2 Comments

PASS Summit 2012

PASS
I sure hope you’re planning on attending the PASS Summit this year. If you’re not, you might want to reconsider. They’ve just released the list of sessions. You should click this link and check them out. There’s going to be some magnificent instruction available in all aspects of SQL Server. You don’t want to miss it. On a personal note, I’m doing an all-day pre-conference seminar on query performance tuning. It’s called “Query Performance Tuning: Start to Finish.” The pre-con’s, as we call them, are an additional cost on top of the cost of the Summit itself. But you’re flying out to Seattle anyway. Why not attend a full day of learning, walk away with a little more knowledge under your belt. I’m going to give you as much information…
Read More
13Jun 2012 by Grant Fritchey 2 Comments

PASS Nomination Committee

PASS
For some reason, which isn’t completely clear to me, we’re voting for part of the members of the Nomination Committee who will work their buns off (make no mistake, this is not a glamor posting) to pick and choose from the people applying to be possible candidates for the PASS Board to arrive at a final slate. This voting opens today, although you can’t seem to vote from the web site, but have to wait for an email to show up (not yet in my Inbox). Regardless of the confusion I feel about this, I’m going to vote for the people I think will do a good job. You should too if you’re a member of PASS. I know almost every one of these people personally. Any of them will…
Read More
05Jun 2012 by Grant Fritchey 5 Comments

Guest Blog

SQL Server
I was given the opportunity to put together a guest blog post for the MVP blog. I did a little something on determining whether or not you have high memory use through the use of a DMO. Check it out.
Read More
04Jun 2012 by Grant Fritchey 10 Comments

How to Drop One Plan from Cache

T-SQL
While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is: DBCC FREEPROCCACHE(0x05000700618F532C40E190CE000000000000000000000000) ; Not much to it is there? The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don't need to drop the entire procedure cache as people so frequently…
Read More
31May 2012 by Grant Fritchey 21 Comments

Never, Ever Use Clustered Indexes

SQL Server
This whole concept of the clustered index as a foundational structure within SQL Server is just plain nuts. Sure, I get the concept that if a table has a clustered index, then that index actually becomes the table. When you create a clustered index on a table, the data is now stored at the leaf level of the Balanced Tree (b-tree) page distribution for that index, and I understand that retrieving the data using a seek on that index is going be extremely fast because no additional reads are necessary. Unlike what would happen with a non-clustered index on a heap table. Yes, I get that if I store my data in a heap, the only way to access the data is through the Index Allocation Mapping (IAM)  pages that…
Read More
28May 2012 by Grant Fritchey 3 Comments

Rolling out Windows 8 Client: First Impressions

Misc
A virus found it’s way on to my system through a trojan horse, past all the security & anti-virus software. I went through the process of cleaning up, but I kept finding pieces of functionality that had been negatively impacted. Further, I was getting lots of Blue Screen of Death (BSOD) crashes. This was all on my beautiful Lenovo W510 that I use constantly and that I need for demo’s while on the road. It needed a rebuild. When it came to doing the work, I planned on a Windows 7 install. It had been working on the machine for about 18 months without any issues at all so I figured I’d just go back to it… But, Windows 8 is coming out. So, for deep technical reasons that I’ll…
Read More
21May 2012 by Grant Fritchey 11 Comments

Is Dynamic T-SQL a Good Design Pattern?

T-SQL
In a recent discussion it was suggested to me that not only is dynamic T-SQL useful for things like catch-all queries or some really hard to solve problems involving variable table lists, but is, in fact, a perfectly acceptable design pattern for all queries against a database. Note, in this case, we’re not talking about an ORM tool which takes control of the system through parameterized queries, but rather an intentional choice to build nothing but dynamic T-SQL directly on the system. To me, this was immediately problematic. I absolutely agree, you’re going to have dynamic T-SQL for some of those odd-ball catch-all search queries. But to simply expand that out to include all your queries is nuts. There really is a reason that stored procedures exist, and it’s not…
Read More