[Case study] Stored procedure is now running 181 times faster. How did we do it?

Here is another significant improvement.

Problem summary

Slow procedure killing SQL Server performance.

The client was experiencing severe issues with the application stability.

What we done

To improve it, we created an indexed view to improve the store procedure performance.

Total improvement

Sometimes changes can make TSQL run 20,000% faster, sometimes 50%, other times slicing 20% off the cost is a great help.

Tuning results vary. They vary based on the query’s complexity, how much tuning has gone into it already, etc.

But we rarely cannot make T-SQL perform faster.

Technical Background

SQL Server Views can be a great helpful tool for database developers. It provides a backward compatible interface to emulate a table whose schema has changed (simplicity), business logic consistency, and security.

Indexed Views (also called Materialized Views) is a view that has a unique clustered index on it. In the right situations, they can drastically improve the performance of queries.

But, as with all features in SQL Server, there are some limitations to indexed views. Here a few of them:

  • This view is created with the WITH SCHEMABINDING option. So, you can not modify the underlying columns and tables;
  • It can not reference tables in other databases and others views;
  • User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

Indexed views are a nice feature, but you have to specify the WITH (NOEXPAND) hint to get it used.  Remember to be cautious when using any hint.

See more SQL Server Performance Optimization examples here.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *