Problem summary
A report was taking a long time to complete. It was also causing deadlocks during the execution.
What was done
We started monitoring the SQL Server environment.
A comprehensive methodology was applied to look at various levels SQL depended on: CPU, RAM, networking, storage, VMWare setup, Windows Operating System settings, and few more areas.
It may seem like overkill to look at all the layers, but Red9 practices prove that comprehensive analysis is effective. Especially when stability and speed matter.
After investigation, we created 3 indexed views, which helped to keep the data aggregated.
After creating the views and changing the store procedure code to use those views, the execution plan’s cost was 92% to the old version and only 8% for the new version.
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.
