DYDavid Yardy PE

Analysis and Insights on #sqlserver

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.

Back to All Articles

Articles tagged sqlserver:

SQL Execution (Enterprise Manager-QA) VS. .NET Execution

I have been chasing an issue for quite a while where the query would timeout when executed from an ASP.NET interface.  If I ran that exact same query through Query Analyzer the results would be returned in less than 2 seconds.  I have for a while been struggling with why it is different between those two interfaces.  I thought about connection pooling issues, command time outs and connection timeouts and was focused on that for a while.  Even after extending those values from the default the query would still time out.  It was often I would see an exception similar to --------------------------- SQL Exception Information: ErrorId: -2 Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Line Number: 0 Procedure: --------------------------- To put this post in the proper context think about one of your application search stored procedures.  The interface shows a dozen or more optional textboxes for user entry to narrow down the results.  The stored procedure in this case often has many parameters that could be used with the actual query.  This is the case for the query that I am discussing herein. When you execute a stored procedure for the first time the query processor constructs a query plan based on the the state of the statistics and the input parameters.  So, the query plan is created upon first execution and is cached in case some other invocation of the same stored procedure comes along before the cache is cleared.  So, when executing through the web interface SQL Server is using the same execution plan that was created by the prior user.  This can look significantly different between users and what parameters they provide before they run the search. So, to have SQL Server generate a new query plan upon each execution the "WITH RECOMPILE" can be used.  By adding this parameter SQL Server regardless of the parameters passed in it will create a new optimal plan.

Read More
sqlserver