11Jan 2012 by Grant Fritchey 10 Comments

Database Backups: Things You Need To Do Now

SQL Server, T-SQL
I want to say a few things about database backups that you need to know. Wait a minute, haven’t you written about backups before? Why, yes. Yes I have. Aaand… you’re doing it again because? Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out. Oh, good point. Carry on. Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate…
Read More
25Oct 2011 by Grant Fritchey 6 Comments

SQL Server XQuery Against Execution Plans

SQL Server, T-SQL
One of the greatest things about all the DMOs is how you can combine the information they present to you with execution plans. It allows you to see what a query is doing, even as it’s executing, because the plan is created first. I couldn’t possibly emphasize enough how important that’s going to be in your day-to-day troubleshooting. Even better is the fact that you’re going to be able to make use of XQuery to pull useful information out of the execution plans that are in cache. Or are you? Here’s a query to pull some information out of the procedure cache: SELECT  deps.type_desc, deps.last_execution_time, deps.execution_count, deps.total_logical_reads, dest.encrypted AS EncryptedText, dest.text, deqp.query_plan, deqp.encrypted AS EncryptedPlan FROM    sys.dm_exec_procedure_stats AS deps CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp WHERE  …
Read More
20Sep 2011 by Grant Fritchey 17 Comments

Statistics in Execution Plans

SQL Server, T-SQL
I was presenting on execution plans when another question came up that I didn’t know the answer to immediately. Yes, I know you’ve seen that phrase before on this blog. I love presenting because you get exactly the kinds of questions that make you think and make you learn. I’m presenting, in part, to learn, just as much as I am to teach. It was the same with kenpo. The more I taught, the better I learned the art. Wait, this isn’t supposed to be a blog post about learning. This one is about statistics. The question was, does the execution plan have the statistics that were used by the optimizer to decide on the execution plan. And no, what was meant, was not does it show the estimated rows,…
Read More
15Sep 2011 by Grant Fritchey 6 Comments

Probe Residual on Hash Match

SQL Server, T-SQL
I have to say, I only recently noticed this on a tool tip: and this in the property sheet: The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn't remember having seen one before. Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter: SELECT  soh.PurchaseOrderNumber, soh.AccountNumber, p.Name, sod.OrderQty, sod.LineTotal, cc.CardNumber FROM    Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Production.Product AS p ON sod.ProductID = p.ProductID…
Read More
22Aug 2011 by Grant Fritchey 3 Comments

Expert? Ha!

SQL Server, T-SQL
How do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book. Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before. I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew…
Read More
05Jul 2011 by Grant Fritchey 1 Comment

PASS SQL Rally 2011 Evaluations

PASS, SQL Server, T-SQL
  In keeping with the all my other major speaking engagements, I’m posting the results of my pre-con and session evals at the first-ever SQL Rally. I’m posting this really late and I apologize. The good news, I did a fill-in when someone cancelled and I was placed in the Grab Bag category where I had the highest rated session although it was a far cry from the actual highest rated sessions from the whole Rally. There is no bad news. Query Performance Tuning: Start to Finish I was pretty happy with this considering it’s my first, public, all day training class. I’m even happier with the feedback. I received some excellent constructive criticism that I will attempt to take into account when I present in the future (including at…
Read More
15Jun 2011 by Grant Fritchey 9 Comments

Rebind and Rewind in Execution Plans

T-SQL
Ever looked at an execution plan? If you answered no, you can’t possibly have ever tried to tune a query, or, you’re doing it wrong. For every one else, no doubt you’ve looked at the tool tips or the property sheets of an operator and you’ve seen the Rebind & Rewind properties and wondered what the heck they mean. Me too. I learned as much as I could for the book on execution plans and I spent two pages describing it. Then, a little while ago, on the SQL Cruise, someone asked me to describe them and I was flummoxed. Specifically they said the explanation in the book was insufficiently clear, so I promised to put together a blog post on the topic, both to attempt to clarify my explanation…
Read More
02May 2011 by Grant Fritchey 3 Comments

I’ve got 99 Problems, but a disk ain’t one

nHibernate, Object Relational Mapping, SQL Server, T-SQL, Tools
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…
Read More
06Apr 2011 by Grant Fritchey 4 Comments

SQL University: Index Usage

SQL Server, T-SQL
Greetings. Welcome once more to the Miskatonic University branch of SQL University. Does anyone know where to buy some camping equipment, cheap? I’ve been tagged to go an expedition to Antarctica and I need some cold weather gear a bit more substantial than my LL Bean boots. Evidently the last expedition found some caves in some mountains down there. Sounds like the perfect place to get away from all the crazy stuff that goes on here at Miskatonic. I mean, what could happen? Anyway, our last several talks have all been about indexes and indexing. One of the things that we haven’t talked about is how to tell if, how or when your indexes are being used. Starting with SQL Server 2005, and continuing to 2008 and R2, there has…
Read More
04Apr 2011 by Grant Fritchey 6 Comments

SQL University–Recommendations for a Clustered Index

SQL Server, T-SQL
Welcome, SQL University Students to another extension class here at Miskatonic University, home to the Fighting Cephalopods (GO PODS!). Never mind the stains on the floor, or the walls…or those really nasty ones on the ceiling. There was a… oh what did the dean call it… an incident last week when one of the students had a little accident after reading Die Vermiss Mysteriis one too many times. But we’re not here to talk about arcane tomes and unspeakable horrors today. No, today we’re here to talk about clustered indexes. SQL Server storage is really predicated around the idea of clustered indexes. Don’t believe me? Let’s list a few places that require a clustered index: Partitioning. A table in SQL Azure In order to create XML indexes What about the…
Read More