Use SQL 2016 Query Store to Remove Ad-hoc Plans

Ad-hoc query plans consume memory and can be of limited use. It is a good idea to remove ad-doc query plans if they are not being recused. The query below demonstrates how to use Query Store metadata to identify and remove unwanted ad-hoc query plans from the cache. 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =

(

        SELECT 'EXEC sp_query_store_remove_query ' 

                + CAST(qsq.query_id AS NVARCHAR(6)) + ';' AS [data()]

        FROM sys.query_store_query_text AS qsqt

        JOIN sys.query_store_query AS qsq

               ON qsq.query_text_id = qsqt.query_text_id

        JOIN sys.query_store_plan AS qsp

               ON qsp.query_id = qsq.query_id

        JOIN sys.query_store_runtime_stats AS qsrs

               ON qsrs.plan_id = qsp.plan_id

        GROUP BY qsq.query_id

        HAVING SUM(qsrs.count_executions) = 1

               AND MAX(qsrs.last_execution_time) < DATEADD (HH, -24, GETUTCDATE())

        ORDER BY qsq.query_id

        FOR XML PATH('')

) ;

EXEC(@SQL) ;

You can find out more about the Query Store in SQL Server 2016, in my new book, Expert Scripting and Automation for SQL Server DBAs, available from Apress at www.apress.com/9781484219423?gtmf=s or at Amazon at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1

How Do Join Operaters In SQL Server Work?

I was asked a question this week, that I could only answer at a very high level, so of course my instant reaction was to research a more detailed answer and blog about it! The question was "How do the join operators in SQL Server work?"

So first for a little background... In SQL Server, if you join two table together, in the old days, there was only one way that the optimizer could perform this task, and that was with an operation called Nested Loops (or loop join). In more recent versions however, in order to support larger databases, with larger tables, two additional operators have been added. These are Merge Join or Hash Match (Hash join). But how do these operators work?

Well, first up is Nested Loops. This operator will use statistics to decide which is the smallest of the two tables (in terms of rows) and will mark this table as the "outer table", and mark the larger table as the "inner table". For each row in the outer table, it will loop through every row in the inner table, looking for a matching key, based on the join column(s), and join the tables based on this value.

Next is Hash Match. This operator works by choosing one of the tables and running a Hash Function against the key column(s). It then puts these hashed values into an in-memory hash table, although if this becomes to large, it can be written to TempDB. It then runs the Hash Function against the second table, and joins the rows based on a match of the two hashed values.

Lastly is Merge Join. This operator relies on both of the tables being sorted by the join key. Because both keys are sorted in the same way, SQL can look at the first value in the first table and the first value in the second table. If they match, then it can output them to the result set, if not, then it can take the next row from the first table and repeat the process, until all of the rows from the first table have been matched.

So when is each operator best? Well Nested Loops is generally better for smaller tables, and also needs to be able to perform an index seek on the "inner table", to ensure any kind of performance.

Hash Match can be better for larger tables, because it requires less IO. This is because each key only needs to be read once, rather than multiple times. However, the hash function causes a fair bit of CPU, and hash table can require a lot of memory, and if you are coming under memory pressure, it may need to write the table to TempDB, which can cause more IO, as well as the memory pressure, making it a less desirable option.

Merge Join is generally regarded to be the most efficient of the operators for larger datasets, because it only needs to read the keys once, and also does not require a lot of CPU cycles. However, it does require both tables to be sorted. If they are (especially by the Clustered Index) then the operator is very fast, but if they are not, then SQL needs to perform a sort operation on them, before they enter the join operation. This, of course, is very inefficient.

It is possible to "override" the optimizer's choice of operator, by using OPTION(Loop Join), OPTION(Hash join) or OPTION(Merge Join) after the join in your query. However, remember that optimizer is very smart, and does not make that many mistakes. Updating Statistics and rebuilding indexes is usually the best way to ensure the optimizer uses the correct option. If you really do need to use these hints, however, then you should always work with SQL, rather than against it.

What do I mean by that? Well, if we take the example that SQL is deciding to use a Merge Join, but we know that this will never be the most effective option, because one of the tables (for some reason) will never be sorted by the join key, then instead of telling the optimizer that it must use a Hash Match, we should tell it to use anything it wants except a Merge Join. We can do this, by comma separating the list of operators that it can use. In this example, we would say OPTION(Hash join, Loop Join)


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Is Dynamic String Execution ALWAYS bad?

I have always worked on the principle that DSE is always evil, and should only be used as a very last resort. However, I have been having a really interesting conversation with Kimberley Tripp (find her Blog here) this weekend, and I may have changed my mind...

So first off, lets set the scene... Imagine that you have a multi use stored procedure, that accepts multiple parameters, and different queries pass in widely different values, or even similar values with highly variable selectivity.

The first time you run the stored procedure, or the first time it needs to recompile, (due to stats updates, being kicked out of the cache, etc) SQL will use "parameter sniffing". This is where it builds the plan based on the first set of values you pass in. If, the next time you run the procedure, you give a value with a very different level of selectivity (i.e. The first time, you pass in 'Banana Custard' and the second time, you pass in 'B%') then SQL will use the original execution plan, which will obviously not be the optimal one.

To get around this issue, you can use local variables in the stored procedure. The problem with this, is that the local variables are unknown at compilation, which means that when SQL will use statistics that are based on an overall average of values, rather than from the histogram. This means that once again, you can end up with a sub-optimal execution plan.

So, to get around this, we could use OPTION(RECOMPILE), which means that we force SQL to use a "one-use" plan, and you will get a new plan every time it runs. This issue here, is that OPTION(RECOMPILE) has several issues including (depending on service pack level) returns incorrect query results.
So to get around this, (and yes, we are finally there...!) use dynamic string execution with EXEC(@Query). Although it is possible for this plan to be reused, it is pretty unlikely, and it is essentially treated like an ad-hoc query.

Using this technique comes with it's own challenges, not least the risk of SQL Injection, but you can mitigate these risks with simple techniques, such as ensuring you use the "principle of least privilege", using EXECUTE AS clause in your procedures, using the QUOTENAME() function of parameters you pass into the procedure, etc.

So in conclusion, yes there are times, when dynamic string execution is the best method. You learn something new every day!

A big thank you to Kimberley for helping me get my head around this one!

Using System Tables to Analyse CI Scans for Specific Table

I had a situation this week, where I wanted to see what queries were causing CI scans and table scans. Now this sounds fairly straight forward. You can find CI Scans by looking in sys.dm_db_index_usage_stats, and query text can be found by using sys.dm_exec_sql_text. But how to join the 2 together, as there is no plan handle in the index dmvs?

Well, this is the solution I came up with. I'm sure there is a better way of doing it, and if you know of one, then please feel free to leave a comment. (Using XQuery against a table holding the query plans and then using a CTE to find the query text does not count as a better way!!!) ;-)

Basically, what I am doing in the query below, is using sys.dm_exec_query_stats as a base to find the query handle and plan handle, and then using CROSS APPLY to run the sys.dm_exec_query_plan and sys.dm_exec_sql_text functions, to retrieve the actual plan and text, respectively.

I am then simply dumping the query and it's associated plan into a temp table and running a simple query to pull out the results I'm after. You can simply tinker with the WHERE clause to get a whole variety of useful information.

I have converted the XML to NVARCHAR, not because I am scared of XQuery (Please see the XML category of my BLOG if you don't believe me!!) :-) It's simply because the XML is schema bound to a very complex schema, that is a bit of a nightmare to work with, as I required nodes from multiple namespaces, although I will try this method and post about it at a later date.

Anyway, I digress, here is the query...

...I hope you find this a useful edition to your script library.

SELECT [text], cast(query_plan AS NVARCHAR(MAX)) AS qplan INTO ##qplans
FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

SELECT *
FROM ##qplans
WHERE [text] like '%PUT TABLE NAME HERE%'
     AND [qplan] like '%clustered index scan%'

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom