By Franck Pachot . In the previous post we have seen the Index Scan, where a where clause condition uses the index structure to find rows and then fetches remaining columns from the table. The case was an optimal one where the correlation/clustering factor was good. Now I’ll do the opposite: the worst clustering factor. Index with good correlation /…
Did you miss aggregate pushdown capability shipped with columnstore index? Well, I remember the first time I heard about it is was when I read the very interesting blog post of Niko Neugebauer here with a very good covering of the topic (principle, advantages and limitations). So why to write a new blog post here? Well because since the last…
Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is at least what I could…
One of the most new interesting features that will be introduced by SQL Server 2016 is certainly the query store that will complete the performance tuning toolbox of database administrators. Basically, the query store will track queries, their query plans and runtime statistics as well. It will be a great tool to detect regressing queries. I remember a real customer…
Last night, I had an interesting discussion with one of my MVP French friend that faces a weird situation where a query that uses a statement level RECOMPILE hint produces an execution plan that disappointed him. He told me we can simulate the same situation by using the AdventureWorks database and Sales.SalesOrderHeader table. First, we have to add a nonclustered…
Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure you had to deal with the following questions: When will this query finish? What percentage of total workload is performed by…
By Franck Pachot . I have recently posted on the way I like to extract execution plans. When we have Tuning pack, I suggest to get them with SQL Real-Time Monitoring as an active report. However, Martin Preiss said in a comment that he prefers the text format – easier to search, copy, and paste. And that’s a very good…
By Franck Pachot . You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if…
