Query hints are always about tradeoffs—typically you are giving up something, such as flexibility, to achieve more consistency, or very specific performance characteristic. One example of this migt when returning code results, that you would want to see the data as soon as possible, even before the complete set of data has been returned. Did you know that you can return a subset of rows to look at BEFORE the entire result set is returned?

The FAST n query hint allows the optimizer to return a specified number of rows as quickly as possible. Imagine an application result screen where users frequently wait for data to appear. Wouldn’t it make those users happy if you could return rows faster? Using the FAST 75 table hint for example will return the first 75 records of the results set while still working to return the remaining rows. This allows users start working with the data before the rest of the screen loads with data.

Let’s see it in action!

USE AdventureWorksDW2016CTP3
GO
SET STATISTICS TIME, IO ON
GO
SELECT [OrderDate],[UnitPrice],[OrderQuantity]
FROM [dbo].[FactResellerSalesXL]
ORDER BY OrderDate
GO

Note after 30 seconds it is still running and I have no results in the result grid. So, let’s kill it.

The fast hint works by telling the query optimizer to use techniques like nested join in lieu of hash joins to return the first rows more quickly. Always keep in mind that query hints can negatively impact the overall performance of the query, and you should test extensively before using any query hints including this one.

Now let’s run the same thing with the Query Hint.

SELECT [OrderDate],[UnitPrice],[OrderQuantity]
FROM [dbo].[FactResellerSalesXL]
ORDER BY OrderDate
OPTION ( FAST 75);

If you pop over to the results tab you immediately see the first 75 rows and it begins to populate right away. It was actually immediate but I couldn’t screen capture fast enough and it listed 75 rows

You can imagine what impact this can have for users. If you have an application in which users need to start seeing data as soon as possible, this is a great option. Play around with it and see how it can help you.

Speaking Engagements

Check out my new Event Calendar, to see my upcoming and past speaking engagements.

You can catch me at various events throughout the year. If you are interested in having me speak at your event reach out via my contact me page.

I’ve written about statistics in SQL Server a few times now. Through conversations  I am reminded that not everyone keeps their table statistics up to date. Keeping your statistics up to date is one of the more critical components of SQL Server performance issues. Please be sure you are doing more than just index maintenance to maintain your statistics.  Index maintenance  only maintains statistics created by indexes and single field predicate created table statistics. There are also column statistics that need to be maintained that are created by predicates usage in your queries or even manually created. For each insert, update, or delete the distribution of your data changes and can skew the optimizer’s estimates, so ensuring that your execution plans’ number of Actual Rows versus Estimated Rows are aligned will allow SQL Server to generate the optimal execution plan.

Over Estimations of Rows (Actual > Estimated) leads to:

• Selection of parallel plan when a serial plan might be more optimal
• Inappropriate join strategy selections
• Inefficient Index Navigation (scan verses seek)
• Inflated Memory Grants

Under Estimations of Rows (Actual < Estimated) leads to:

• Leads to SPILLS to DISK because of not enough MEMORY was requested
• Selection of serial plan which parallelism would be more optimal
• Inappropriate join strategies
• Inefficient Index selection and navigation strategies

Maintain your statistics by doing the below at least weekly. (Note: some systems may require far more frequent updates–I’ve had to update stats every 10 minutes on a particularly troublesome table)

Set AUTO_UPDATE_STATISTICS =TRUE for each database, however,  this option will only update statistics created for indexes or single-columns in query predicates. Optionally also Set AUTO_UPDATE_STATISTICS_ASYNC =TRUE for performance gains, you can read more on that in my prior blogs.

If you have larger tables in your environment and are not using SQL Server 2016 or higher be sure to examine Trace Flag 2371. This trace flag is available to assist in keeping stats up-to-date in large tables. Currently, the algorithm that is used to automatically update statistics is 20% + 500 rows.  Trace Flag 2371 changes this algorithm to a sliding scale. Using this trace flag will drastically increase the frequency of which statistics updates occur on your larger tables, which in turn give the optimizer much better estimates to work with.

Run EXEC sp_updatestats to update ALL statistics routinely. I suggest creating a SQL Agent job to run routinely.

OR

If you are using Ola’s Index Scripts consider adding the below parameters.

@UPDATESTATS=ALL

@ONLYMODIFIEDSTATICS=Y * (this can create old stats if the fields have not been modified in a while, I am not a huge fan of this option)

Example:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[IndexOptimize] @Databases = ‘USER_DATABASES’,    @UpdateStatistics = ‘ALL’ , @ONLYMODIFIEDSTATICS=Y

More Information

Here are the links to my prior statistics blogs. I recommend reading them in the below order to help you better understand statistics and their role in SQL Server performance. Make sure you do your part as a DBA and remember to keep your statistics up to date as much as possible to help the optimizer better do its job.

Importance of Statistics

Synchronous VS Asynchronous Statistics Updates

Keeping Large Table Statistics Current -TF2371

It is widely known that I am a horrible speller and hate to type code. I tend to use a lot of code snippets and reuse code to avoid writing it. So, when I find little tips that make coding easier for me, I like to share.

Did you know that you can surround your code with a click of a button with IF, BEGIN END, WHILE code blocks? SQL Server Management Studio (SSMS) gives us the basic structure of a Transact-SQL statement code block as a starting point.

Take Look In SSMS

In a query window, Right Click and Choose Surround With (note the hot keys of Ctrl+K or Ctrl+S)

It will bring up a code “window” where you can choose which code block you want to add.

Here is what you get when you choose each one.

I am not sure if this has always been here or it’s a recent add that I just stumbled on to. Regardless,  this feature along with the full list of code snippets, as well as some custom ones I’ve utilized over the years definitely come in handy. You can find more on code snippets inside management studio here.