Ian Harrison
Ian Harrison DBmarlin technical writer and consultant

Why is my stored procedure suddenly slow?

Why is my stored procedure suddenly slow?

We have all had those days when your much-needed cup of coffee is interrupted by cries of “My query was fine yesterday but today it is soooo slow!”.

Why can the performance of a well-known query suddenly change? One reason is connected to skewed data where some values have a high cardinality - i.e. they occur frequently, whilst other values have a very low cardinality i.e. very few identical values. Imagine the sales figures from a very successful product line vs an unpopular one.

The Microsoft SQL Server query planner is very good at examining queries passed in and the execution path and the memory allocation are chosen based on the predicate values passed in.

Where this can go wrong is where the query being run is held within a stored procedure. In this case, the execution path and the memory grant will be based on the predicate values when the query is run for the first time. The path and grant will be used going forward no matter what values are passed in until the query is re-parsed.

Performance-wise, this can go wrong in two ways:

  • Firstly, if the initial query run selects a few rows, the query may pick nested loops and have a small memory grant. The next run will use both the nested loop plan and have a small memory grant which could cause performance issues when a nested loop join is used on millions of rows and the small memory grant causes sorts to spill to disk.
  • Conversely, if the initial query run selects a large number of rows, it may use a hash join and have a large memory grant. If it runs again with different parameters which only return a small data set, this will allocate a wasteful amount of memory and if the query is run frequently, can cause memory congestion on the server.

This re-parsing may happen for a number of reasons - server restart, SQL Server restart, index rebuilds, carrying out DDL on the tables involved in the query, or even a targeted removal from the cache.

This can mean that performance of your stored procedures can vary from one day to the next. For example, you might have done one of those maintenance tasks, and then the first execution of the stored procedure after that had parameters that forced SQL Server to choose an execution plan and memory grant that wasn’t a good fit for the majority of subsequent executions.

New in SQL Server 2022 - Query Store Hints!

Whilst SQL Server Plan Guides have been available for some time, they are not easy to use and so Microsoft has responded in SQL 2022 with a feature now in public preview: Query Store Hints.

In short - Query Store Hints allow you to add an execution hint to a specific query from within the database. This is useful where you need to change the behaviour of a query delivered by an application that DBAs have no control of e.g. from a COTS application or one built dynamically in a webserver.

All you need to know is the query cache ID and the hint you want to apply. You can use T-SQL to add Query Plan Hints via sp_query_store_set_hints and cleared via sp_query_store_clear_hints They can be viewed in the sys.query_store_query_hints catalog view. You can also add and remove hints through the Management Studio.

Typical uses for Query Plan Hints are to:

  • Force the query to recompile on each execution
  • Cap the size of the memory grant
  • Limit the Degree of Parallelism to control parallel query consumption
  • Use a Hash Join instead of Nested Loops
  • Set a specific lower compatibility level for one query whilst running everything else and the current level.

Query Plan Hints are persistent and will survive a restart so once set, the hints do not have to be re-applied.

Only a subset of hints are supported with this initial public preview and further hints will be supported in future releases although Microsoft are saying that not all hints are guaranteed to be covered.

This is a really useful addition to the DBA toolkit but it should be treated with caution and used as a temporary solution to get you out of a hole until the code can be fixed and an optimized query delivered by the application.

Visit this article from Microsoft on Plan Hints to learn more.

DBmarlin captures execution plans for your top SQL statements, and helps you to understand if Query Store Hints could help. And if you do try them, DBmarlin will help you compare the results and demonstrate that you have made an improvement.

Ready to try DBmarlin?

If you would like to find out more about DBmarlin and why we think it is special, try one of the links below.

dbmarlin-community-join