Menu
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?
313
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
If you encounter a connection-timeout error, follow the steps: Increase the connection-timeout parameter. If you use an application to connect to SQL Server, increase the relevant connection-timeout parameter values and check whether the connection eventually succeeds. For example, if you use System.
The CommandTimeout property sets or returns the number of seconds to wait while attempting to execute a command, before canceling the attempt and generate an error. Default is 30.
It seems Changing the "Remote Server Connection" option from the SQL Server instance properties to zero, helped: Right click on the instance name from SSMS, go to Properties, then choose Connections, set the "Remote Query timeout in seconds" to 0 (default seemed to be 600), then I ran the report from the front end app, it took only 1 second.
In SQLCMD and SSMS, the time-out value is set to 0 (no time limit) and the queries can be tested and investigated. If the queries are fast in SQLCMD and SSMS, but slow on the application side, change the queries to use the same set options used in SQLCMD and SSMS.
Here's how to check queries: Use Extended Events or SQL Trace to identify the queries that cause the time-out errors. You can trace the attention event together with the sql_batch_completed and rpc_completed extended events, and correlate them on the same session_id.
Query time-out is different from connection time-out or login time-out. The connection or login timeout occurs when the initial connection to the database server reaches a predefined time-out period. At this stage, no query has been submitted to the server.
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.
132
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.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
