Guest Blog
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.
Never, Ever Use Clustered Indexes
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…
Is Dynamic T-SQL a Good Design Pattern?
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…
Which SELECT * Is Better?
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT   * FROM   Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Avoiding Bad Query Performance
There’s a very old saying, “When you find yourself in a hole, stop digging.†And my evidence today is: That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard. If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows. Please, stop digging.
Execution Plans, What Do I Look At?
The question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6: Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s…
Extended Events and Performance Tuning Knowledge
I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Statistics in Execution Plans
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,…
Probe Residual on Hash Match
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…
Optimizer Timeouts with XQuery
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)…