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
22Sep 2011 by Grant Fritchey 5 Comments

SQL Server Execution Plans

PASS, Professional Development, SQL Server
I write quite frequently about SQL Server Execution Plans. I started in that area just because that’s how you figure out what a query is doing and sooner or later, we all have to tune a query. I found I was doing it sooner and more frequently. When the opportunity came up to write a book , I jumped on it. Now I find myself presenting, rather frequently, on execution plans. One of the people I’ve learned from over the last several years is Gail Shaw (blog|twitter). I first saw Gail on stage at the PASS Summit, I think it was 2007. A co-worker of mine was picked, along with Gail, to go on stage for the Quiz Bowl. Gail was answering all the questions. If you go over to…
Read More
21Sep 2011 by Grant Fritchey 1 Comment

MicroTraining: Performance Tuning Checklist

SQL Server
I’ve been playing a lot with Google+ and the Hangouts there. I love them. I think they’re opening up a whole new way of interacting and building community. Today Andy Leonard (blog|twitter) and Ken Watson (blog|twitter) were trying out the new features in G+, including desktop sharing. We were talking about how great this was and suddenly the idea of short, sharp training for small groups of people that can interact came into being. Andy termed it MicroTraining, and an idea was born. So, Friday, 9/23/2011, at 11:00AM EDT, I’ll host a Google+ hangout. It will be public and accessible to anyone. I’ll make it one of the Air ones that gets recorded. That means that 10 people can attend & ask questions, but an unlimited number can watch. It’s…
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
20Jun 2011 by Grant Fritchey 9 Comments

Make the Optimizer Work Harder

SQL Server
One of my favorite indicators for whether or not you have a good execution plan is when you see the “Reason for Early Termination” property in the TSQL operator like this: The optimizer considered this particular plan “Good Enough.” which is what you want to see. When you see “Timeout” as the reason, that’s an indication that the plan you have may be sub-optimal. The question is, can you make the optimizer spend more time on your queries. Well, actually, the question is, should you make the optimizer spend more time on queries. During my session on SQL Cruise I answered the original phrasing of that question, no. As usual when I present in front of people smarter than I am, I was wrong. Brent Ozar (blog|twitter) pointed out that…
Read More
30May 2011 by Grant Fritchey 7 Comments

Optimizer Timeouts with XQuery

SQL Server
I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out? This sounds like a job for XQuery! There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code. WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),  QueryPlans AS  (  SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,         RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,         --dest.text,         SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,                   (deqs.statement_end_offset - deqs.statement_start_offset)                   / 2 + 1) AS StatementText,         deqp.query_plan,         deqp.dbid,         deqs.execution_count,         deqs.total_elapsed_time,         deqs.total_logical_reads,         deqs.total_logical_writes FROM    sys.dm_exec_query_stats AS deqs         CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)…
Read More