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

The Five Horesmen Of The SQL Server Appocolypse!

Ok, so it should be four horsemen, but there was a two-way tie for fourth place. What can I say?

Thank you all from you votes from the What is the WORST Performing Feature Of SQL Server Poll (see this post)
You can see the results and percentages in a nice little chart at this link, and they are certainly interesting, so lets look at the winners in a bit more detail...

The clear winner is Auto shrink / Scheduled shrink. But why is this so bad? Well basically it kills performance, both during the shrink operation, and worse still, after the operation has finished. When you shrink a file, starting at the end of the file, and moving backwards, it takes each page, and moves it to the earliest available space within the file.

This is a resource intensive operation, and can cause performance issues whilst it is running, but also causes your indexes to become highly fragmented. I have already blogged about this in detail, so I won't repeat myself too much here, but please check out this post.

Next in the ranking, is Autogrow logs by 1MB . Why is this so bad? Well, once again, there is a two-fold problem. Firstly, if you log is coming under space pressure, and therefore needing to grow, then it can cause huge performance problems, as it needs to grow the Log file (in some circumstances) for almost every transaction. Remember, depending on the action you are performing, SQL may need to record before and after images of the data. I have also experienced issues in the past, where during large operations, your database suddenly ends up in Recovery, because it has not been able to keep up with the number of log grows required, and eventually resulted in the log becoming corrupt, resulting (in my case - the DB was nearly 1TB) in over an hour of downtime, and of course the transaction being rolled-back. See this post for some tips on Log File Optimization.

The other issue is Log File Fragmentation. Log Fragmentation? What on earth is that? Well, basically if you grow your log file in tiny chunks, then you end up with a massive amount of VLFs (or Virtual Log Files). On the VLDB I mentioned above, the client ended up with over 2000. The rule of thumb recommendation, is not to have more than 50! See this post for more info.

Very close behind, in 3rd place, came Functions in Select list. Why not put a function in a SELECT list? That is what they are there for isn't it? No! No, no, no, no, no, no, no! NO! :) If you put a function in a SELECT list, it needs to be evaluated separately, for every single row of the query, which is causing a "cursor like" effect, and often can not be included in the same execution plan. Lets have a look at an example, and how we could perform the task better...

Using the AdventureWorks2008DW database, I have created the following example, which is a reproduction of one of the worst SQL implementations I have seen. (The code has been changed to protect the guilty!) :)

The first thing I am going to do, is create some "dirty data" in the FactResellerSales table, to demonstrate why the code existed, with the following script...

  UPDATE [AdventureWorksDW2008R2].[dbo].[FactResellerSales]
  SET OrderQuantity = 0
  WHERE ProductKey = 351

  UPDATE [AdventureWorksDW2008R2].[dbo].[FactResellerSales]
  SET UnitPrice = 0
  WHERE ProductKey = 223

...Now to create the Function, which I will call SaftyDiv, and will basically stop a divide by 0 error occuring...

CREATE FUNCTION dbo.SaftyDiv(@FirstNumber DECIMAL, @SecondNumber DECIMAL)
RETURNS DECIMAL
AS
BEGIN
     DECLARE @ReturnValue DECIMAL

     IF (@FirstNumber = 0)
     BEGIN
          SET @ReturnValue = 0
     END
     ELSE IF (@SecondNumber = 0)
     BEGIN
          SET @ReturnValue = 0
     END
     ELSE
     BEGIN
          SET @ReturnValue = @FirstNumber / @SecondNumber
     END
     RETURN @ReturnValue
END

...So, there are so many things wrong about this function, I will not patronise you, or drive myself insane by listing them all, but lets see how it performs when we use it to select values from the table...

SELECT
       [SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      , [dbo].[SaftyDiv] ([OrderQuantity], [UnitPrice])
 
  FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

 SQL Server Execution Times:
   CPU time = 438 ms,  elapsed time = 1559 ms.

...If you look at the Execution plan for this, it looks very clean, but that is in fact, because SQL was unable to include the function in the same plan, and actually had to create a second plan for it!

So lets see what performance we get, if we tear down the buffer cache, and then rewrite this query to use a CASE statement in the Select list. To be honest, there are more elegant ways of doing it than this, but it's getting late, and this demonstrates the point...

SELECT
       [SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      , CASE WHEN ([OrderQuantity] = 0 OR [UnitPrice] = 0) THEN (SELECT 0)  ELSE (SELECT [OrderQuantity] / [UnitPrice] ) END 

  FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 1177 ms.

...So you can see, we got a 78.5% improvement in processor time (because it only needed to compile 1 plan, instead of 2) and we also got a 24.5% improvement in execution time. Remember, in this example, we are only dealing with aprox. 65,000 rows. Imagine if we started scaling that up to millions of rows!

Tied for fourth place, were Encrypt all data with cell level encryption and Cursors. Now I must be honest, this was a surprise for me, I though that cursors would be right up there with Auto/Scheduled shrink. Why did I think that? Well, they are the bain of my life! With no disparity meant what so ever to .NET developers, you can always tell when somebody who is experienced in writing .NET code, but less so in SQL, has been writing SQL code. That is because they love cursors! And it makes perfect sense, in .NET languages, looping is often the best way to achieve your goals, but T-SQL is a SET-Based language, meaning it is optimized for performing an operation on multiple rows at the same time, as opposed looping over a set of rows, which is exactly what a Cursor does.

There is a place is T-SQL for the use of Cursors, but these days, it is a very small, limited place, and basically only shows itself in situations where you need to iterate over a series of DDL objects, such as looping over indexes in a dynamic rebuild scenario.

For almost all other purposes, we have a better way of doing things. For example, if we need to produce a cross-tabulated query, then we have the Pivot and UnPivot operators. If we need to implement recursive logic, then we have Recursive CTEs. For concatenating rows into a string, or vice versa, we have tricks we can use with XML (see this post for an example), and for very complex logic, or string manipulation, we have CLR integration.

Lets look at an example of using a simple Cursor, versus a simple SET-Based solution to perform the same task, and see how they perform...

CREATE TABLE #Sales
(
SalesAmount DECIMAL,
RunningTotal DECIMAL
)

DECLARE @SalesAmount DECIMAL,
        @RunningTotal DECIMAL
SET @RunningTotal = 0

DECLARE myCursor CURSOR
FOR
SELECT SalesAmount
FROM FactResellerSales

OPEN myCursor
FETCH NEXT FROM myCursor INTO @SalesAmount
WHILE @@FETCH_STATUS = 0
 BEGIN
      SET @RunningTotal = @RunningTotal + @SalesAmount
      INSERT #Sales VALUES (@SalesAmount,@RunningTotal)
      FETCH NEXT FROM myCursor INTO @SalesAmount
 END

CLOSE myCursor
DEALLOCATE myCursor

SELECT * FROM #Sales
ORDER BY RunningTotal

...To be honest, I don't know how long this would take to complete, because I got bored of watching it run after about 19 minutes, (remember there are only 65,000 rows in this table) and killed it, so I could run my SET-Based version!

So, how long did the SET-Based version take? Drumb roll...

SELECT a.SalesAmount,
       SUM(b.SalesAmount) AS RunningTotal
FROM FactResellerSales a
INNER JOIN FactResellerSales b
ON (a.SalesOrderNumber = b.SalesOrderNumber
        AND a.SalesOrderLineNumber = b.SalesOrderLineNumber)
GROUP BY a.SalesAmount
ORDER BY RunningTotal

...Less than 1 Second! I rest my case! :)

So finally, what is wrong with using cell-level encryption? Well nothing, if used in moderation, and it is often a necessary evil, in order to meet regulatory requirements, but if you over-indulge, then you have a problem.

If you need to encrypt a CreditCardNumber column to meet a regulatory requirement, then this is fine. Use a symmetric key, and avoid encrypting that key with an asymmetric key, and that with a certificate, and so on! But do not go right on ahead and encrypt the entire table, despite there being no requirement to do so, other than a manager "thinks it might be a good idea". As a technical person, it is your responsibility to point out the limitations of technology to the business, so if you, as I have, had a Director telling you that they want the salaries encrypted, to avoid the slightest risk that somebody might find out what a disgustingly large bonus they get, the correct answer is something along the lines of my standard reply... "Yes Sir, certainly, you are the customer, and if that's what you want, then I can make it happen. However, please be warned that depending of the encryption algorithms used, then this can cause a performance degradation of up to 45-50% when accessing that data, and can cause a data-bloat of up to 4000%!" That is normally enough to make people see it your way! ;-)

I hope you find these results as interesting as I did.


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

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

SQL Server Schedulers and CPU Performance

For most applications, Windows handles (as you would expect) the scheduling of work onto each CPU. This uses a preemptive scheduling approach, which means that Windows will give each process a priority based on a combination of resource usage, interactivity, activity and user preference.

Once a process has reached its turn, Windows assigns it to a core and it begins, but the preemptive approach, means that Windows is able to forcibly relinquish this process and let another process take over.

This works well for most applications, but not so well for SQL Server. This is because it treats SQL Servers worker threads with the same priority algorithms as any other processes within Windows, and this can lead to lots of context switching.

To get around this issue, SQLOS has it's own CPU Scheduler, called the SOS Scheduler. This allows SQL Server to schedule it's own processor workload, using a cooperative scheduling algorithm. This means that instead of forcing a thread to yield, it allows to threads to yield voluntarily.

But hold on a moment, surely, this could lead to a situation where a runaway thread just takes control of the entire system and does not let anything else get a look in, right? Thankfully, no! Instead, SQL has a process called the Scheduler Monitor, which is a thread that will periodically check to make sure that active schedulers have yielded since the last time it checked. Unlike other SQL threads, this thread works in preemptive mode, so it can not cause the above issue itself.

So how many schedulers do you have? Is there a one-to-one mapping with CPU cores? Well, not exactly. There will be one scheduler for each CPU core in the server (even if SQL has been configured not to use them, by setting processor affinity), there will be one scheduler for the DAC (so that even if something has gone badly wrong with the schedulers, you can still connect) and then one scheduler for each Resource Monitor.

Resource Monitor is a process that helps control memory pressure in the cache, and there will be one for every NUMA Node within the system, regardless if you have hard or soft NUMA. (Non-Uniform Memory Access is a way improving performance by splitting memory between processors or groups of processors, to avoid cache misses - but I digress further and further, so I will talk about this another time!)

You can see your schedulers by querying the sys.dm_os_schedulers DMV. This will give you lots of information, but there are some columns that are especially worthy of note.

First up, is a column called Status. Here you may see various options made up of ONLINE/OFFLINE and VISIBLE/HIDDEN. If a scheduler is Online, it means the SQL Instance has access to it. Offline, means that you have set CPU Affinity, and SQL is not allowed to use it. The reason it is set to Offline, rather than removed, is because it allows you to change your CPU affinity, without the need to restart the Instance. (Very cool!)

If a scheduler is Visible, it means that it is mapping directly to a CPU Core and will accept user requests. If it is set to hidden, it means it is associated with a Resource Monitor. The scheduler associated with the DAC is easy to identify, as it ends with (DAC) ! ;-)

The next interesting column is preemptive_switches_count. Now, you will remember me telling you earlier that SQL threads are cooperative, rather than preemptive, so why do we have this counter? Well, if SQL needs to run anything outside of the SQL Server environment (such as distributed queries, or certain CLR objects, etc), then it must switch to preemptive mode, so that Windows can schedule it to run.

runnable_tasks_count brings some context to our understanding of SQL Waits. A request in SQL can be in one of three states. RUNNING, RUNNABLE or SUSPENDED. Running is fairly obvious, Suspended means that it is waiting for a resource to become available, (this is where you see wait types associated with disc such as IO_LATCH_STATS, network waits, such as NETWORKIO and transaction log bottlenecks, such as WRITELOG see this post, etc), and Runnable, means that it is ready to run, but waiting for a scheduler to become available.

work_que_count shows you the number of tasks that are assigned to the scheduler and waiting to run. pending_io_count shows the number of IOs that the scheduler has that are waiting to be completed. These can be helpful for diagnosing resource pressure.

Whilst we are on the subject of resource pressure, it is quite common in multi-processor servers to see a large number of CXPACKET Waits. These waits can often be a symptom of badly tuned queries, and can be resolved by introducing proper indexing strategies, etc, but sometimes they can occur as a result of parallelism being a bottleneck. But parallelism is good right? Well not for OLTP systems. It would be nice if there was a way to tell SQL the type of workload to expect, but there isn't. If your queries are tuned, and you are still getting lots of CXPACKET Waits, then basically a query is running in parallel, and one core has already finished it's work, and is waiting to receive a packet from another core, so that it can synchronise, etc. The way to get around this, is to reduce the MAXDOP setting for the query (or the whole instance in some cases).

If however, you reduce your MAXDOP setting, then you can suddenly see your CXPACKET Waits being replaced by SOS_SCHEDULER_YIELD Waits. This is because now you have reduced the parallelism, then you are coming under CPU pressure on the cores that are still being used. Basically it is telling you that a thread has voluntarily yielded, so that another thread can take over, and is now waiting for it's next turn on the Scheduler.

So if you are coming under CPU pressure, what can you do about it? Well, that is another long blog for another time, but here are a few suggestions.

1) Check your benchmarks to see if you can establish a usage increase pattern (You are taking regular benchmarks, right?) You may find that it is a natural curve, or you may be able to correlate a sharp spike to a particular release, and then go troubleshooting...

2) Run SELECT * FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_query_plan(a.Plan_Handle) ORDER BY total_worker_time DESC You can then click through to the query plans of the most expensive queries to see if they can be optimized.

3) Run a Perfmon trace simultaneously with a Profiler trace. You can then correlate the two traces in Profiler to identify the queries causing CPU pressure and investigate.

I hope you enjoy reading this post as much as I enjoyed writing it. After a week spent playing with a very large FastTrack at a Microsoft Lab, I felt the overwhelming urge to write something geeky! :)

Is Dynamic SQL ALWAYS Bad? - Follow Up

Last week, after a chat with Kimberley Tripp, I wrote this post. Kimberley has turned this conversation into two SQLMag articles, which probably explain the situation slightly more elequantly that I did...

See here and here. :)

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!

Optimizing You Log File

Optimizing your transaction log could easily fill a 50 page white paper, or a week's consultancy, but here are a few points to get you started:

How many physical log files?
This one is easy...One! Exactly one, always one, the answer is one! There is an urban myth that having multiple log files will improve performance, but this is just a myth. Log files are written sequentially, so if you have multiple files, it fills one, then fills the next one, etc. Pointless!

How many virtual log files?
This one is a bit more complicated. Your physical log file is managed internally with a series of virtual log files. See this post for more details. If you have too few, then the active portion of your log may be too large, leading to longer backup times. If you have too many VLFs however, this leads to "log fragmentation" and cause performance issues.

The general rule of thumb is to have VLFs sized at around 500MB. To achieve this, grow your log in 8000MB chunks. If you already have log fragmentation, the way to resolve it, is to shrink your log file (thus deleting the existing VLFs) and then grow it again, using the desired chunk size.

I/O Subsystem
This breaks down into two parts. Firstly, make sure that you put the log file on a separate physical disc to your data files. Otherwise you will get I/O contention.

The second part is a little more complex, and that is what RAID level should you use? Well, this depends on what features of SQL you are using. Before we begin, it may be beneficial to bring some context, by looking at the standard RAID levels, and how many IOs they generate.

RAID 1 - IOs = R + (W*2) / 2
RAID 5 - IOs = R + (W*4) / No Of Discs
RAID 10 - IOs = R + (W*2) / No Of Discs

If you are just using SQL Server at a basic level, then RAID 1 is a good choice. This is because almost all activity on the log will be write activity. The only read activity will be backups and restores. In this scenario, RAID 1 is better than RAID 5 because for every one write, there will be two physical writes to the volume with RAID 1, as opposed to 4 writes with RAID 5. It is also better than RAID 10 in this scenario, because RAID 1 will perform the operations as sequential IO, where as RAID 10 will perform them as random IO.

However, this recommendation changes if you are using some of the more advanced feature of SQL, especially the HA features. If you are using Database Mirroring, Replication or Log Shipping, then you are liable to have a much higher read to write ratio, and hence need better read performance, whilst maintaining write performance. In this scenario, RAID 5 is still a no-go, because you will double the number of physical writes for every write operation, from 2 to 4, as discussed above, so your best choice would be RAID 10. RAID 10 will maintain the 2 writes per write operation ratio, but also give you improved (and more scalable) read performance. This is because the the overall IOs will be divided by the number of discs in both RAID 1 & RAID 10, but in RAID 1, you always have exactly 2 discs, where as with RAID 10, you will have a minimum of 4, and as many as you require.

Log Manager Bottlenecks
One reason that your log can become a bottleneck, is because in SQL Server 2008, the Log Manager will only allow you to have 3840K of data that has been sent to be written, but where no acknowledgement of the write has been received back. Before SQL 2008, this limit was even lower.

Monitoring Log Performance
Within SQL, keep an on the sys.dm_os_wait_stats dmv. If you see lots of WRITELOG waits, then the log is probably coming under pressure.

In Perfmon, you should be monitoring the obvious disc counters for you log drive, such as Avg Disk Queue Length, Disk Writes/Sec, etc, but you should also keep track of the Log specific SQL counters. In particular, pay attention to Log Flushes/Sec and Log Flush Wait Time.

Together, these counters should give you an all-round idea of how your log is performing. From a performance aspect, the Log tends to be the forgotten relative, but be careful, as forgotten relatives have a nasty habit of turning up unexpectedly, and making a nuisance of themselves!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom