CODEWITHSUNDEEP
sql.netsql-serverlinq-to-sqlsql-server-2008-r2
Baahubali
Baahubali

Reputation: 4830

SQL View timing out used by .NET Application

We have a .NET Application using LINQ to SQL (ORM) to call a view which contains joins from multiple objects in different databases. The .NET Application times out calling this view, however our DBA runs the following statement:

sp_refreshview on the view and the subsequennt sql views

the application starts running again.

This application starts timing out again on the same view after close to 20 minutes. So our DBA has scheduled a job to run the above statement every 30 minutes. There has been no structural changes to the view and we are trying to figure why sp_refreshview fixes this problem and what could be the underlying issue that we could fix?

Upvotes: 9

Views: 401

Answers (3)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

Most likely sp_refreshview causes the server to remove the cached execution plan of your query from the cache. When you run the query after a call to sp_refreshview the new (better) execution plan is generated. This is why calling sp_refreshview helps. Apparently, updating statistics doesn't remove cached execution plans, that's why it doesn't help in your case.

There are some types of queries that can't have a good plan for all possible values of parameters, or your data may be significantly skewed.

If you add OPTION(RECOMPILE) to your query most likely you will not need to call sp_refreshview to make it work fast.

I don't know how to add this query hint when the query is generated by your ORM.

I recommend you to read an excellent article Slow in the Application, Fast in SSMS by Erland Sommarskog.

Upvotes: 1

kubasnack
kubasnack

Reputation: 91

Metadata is information about the tables used by the view. Statistics updates the information about the data. It might help to see your View definition. For example, having a select * in the view could really cause you problems. As a refresher, a view is just a statement until it is executed. If you are using tables that are always changing their structure or are dropped and re-added, you will need to be running sp_refreshview every time. If you want additional help, you will need to provide the query and any information on the underlying tables, like processes the refresh them. All of the above comments were within reason correct.

Upvotes: 0

Jacob Lambert
Jacob Lambert

Reputation: 7679

The reason that sp_refreshview is fixing the issue is that the view is not schema-bound. SQLServer keeps metadata about the view to aid in execution, and since the view is not schema-bound the metadata becomes outdated as the base objects are updated (think DML statements). What sp_refreshview does is update that metadata for non-schema-bound views so they can run optimally. Take a look at the documentation for sp_refreshview.

For some clarification on why this works, think about what a view is? A view is just a query. The metadata that is stored relates to that query.

Whenever you run a query, the server will figure out the most optimal way to run that query (called a plan), and that depends on the statistics of the tables used in the query. As the data in the tables change, the statistics for the tables will change, and so the plan can change. When you create a view (non-schema-bound), metadata around the optimal execution is stored (most likely the plan). Since a view is just a query, the plan can become outdated and sp_refreshview updates that metadata.

Upvotes: 3

Related Questions