Case study – Stored procedure 181x faster

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

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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