Query Store and Recompile

Azure, SQL Server 2016, T-SQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; If this procedure is called with the value of 'Mentor' you get…
Read More

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

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

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

Execution Plan Compile Termination

SQL Server, T-SQL
Recently I've been seeing a lot of people with bad execution plans, desperately trying to tune them, but they were unable to explain why they had such bad plans. More often than no these were larger queries, with a number of derived tables, CTE's, CROSS APPLY, etc. In most cases the statistics appeared to be fine (this is usually checked by comparing estimated & actual rows within the operations in the execution plan) and the plans themselves didn't look crazy, but the execution plans were flat out, not good. If you're looking at a plan and it doesn't make much sense, one option that most people don't check... SQL Server didn't have enough time to complete optimization. The optimizer is a pretty amazing bit of code. The scary volume of…
Read More

Performance Studio

Tools
I just found out about some new functionality coming out in SQL Server 2008 called Performance Studio. It's actually largely a framework around which you can build performance monitoring routines for an entire enterprise. This sounds terrific. I'm going to dig into a bit and make it my presentation for the Heroes {Community} Launch event at SNESSUG next week. Here's a Technet webcast on the topic. Here's a very nice blog entry over at SQLTeam (I suppose I should ad them to my blog roll) discussing the function of the Data Collector, the foundation for this new framework. Performance Studio only works with 2008 systems though, so that's something to take into account. Although I see an interview with Brad McGehee that says it's not enterprise ready. Another something to take…
Read More