Some time this problem occurs due to compiled query plan became corrupt, or invalid.
Stored procedure slow when called from web, fast from Management Studio becuase Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not.
The main difference when running with or without this setting, a different query plan is created for the both stored procedure calls. In case of ARITHABORT was OFF, the SSMS command would use the pre-compiled cached query plan that the ADO.NET connection was using (compiled query plan became corrupt, or invalid.), and therefore timeout.
Use following Command to refresh the query Plans.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. This is called Parameter Snifting.
Sql-server using Parameter Snifting (Default Enabled).
You can change the Behaviour of Parameter snifting, is you have knowledge of Parameter snifting.
Stored procedure slow when called from web, fast from Management Studio becuase Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not.
The main difference when running with or without this setting, a different query plan is created for the both stored procedure calls. In case of ARITHABORT was OFF, the SSMS command would use the pre-compiled cached query plan that the ADO.NET connection was using (compiled query plan became corrupt, or invalid.), and therefore timeout.
Use following Command to refresh the query Plans.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. This is called Parameter Snifting.
Sql-server using Parameter Snifting (Default Enabled).
You can change the Behaviour of Parameter snifting, is you have knowledge of Parameter snifting.
