.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

procedure cache having two query plans

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

I noticed that a stored procedure takes excessively long when it's called from within an application/management studio. I understand what is actually happening, but I do not have a reason for why this is happening.

The difference between the query plan are as follows




The difference here is that ARITH ABORT is on within one plan and is not on with the other one. ARITH ABORT on tends to be faster. Any ideas on how we are encountering this problem and a long term fix. One fix is to alter the procedure and set ARITH ABORT on, but i am thinking there may be a reason for this.

View Complete Post

More Related Resource Links

selecting between query and stored procedure

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1033506322; mso-list-type:hybrid; mso-list-template-ids:369124590 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> What would prefer to pull data out from sources stored procedure or query? and why?


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Stored Procedure takes longer than the query.



I'm stuck up in a situation where my sp takes longer time but the query takes lesser time. I went through all the posts mentioned in the T-sql Forum and i tried all the possible resolutions mentioned there but it did not help me.

I have a sp which has around 20 parameters and i tried assigning them to local variables as mentioned in various posts but that did not help me.

Could you please help me out with this problem?







Very Low Procedure Cache Hit Ratio and High CPU Utilization


I have a SQL Server 2005 32 bit instance that occassionally has issues with the Procedure Cache.  This instances 200+ databases and is configured with 4 processors and 12 GB of memory.

The Procedure Cache Hit Ratio drops to 25% for an extended period of time.  The size of the procedure cache during this time is very small.  I am able to tell this by quering the DMVs.  Also, DBCC Memory status shows a small procedure cache area.

Using tools such as vmstat.exe or sqbmemory (from Red-Gate) I see that the Commited memory value drops from around 1900 MB to 600 MB and the Reserved memory value increases from 50 MB to 1350 MB.  The memory is being stolen from the Committed bucket and is being reserved by some other SQL process such as XML or CLR.

After we restart the instance, the memory pressure issues go away.

Do any of you know if there is a DMV or a tool that I

Inefficient Query Plans due to Large Amounts of RAM (KB2413549)


Does anyone have any more details on this KB?  MS states this happens when you set max memory to a "large value".  A hard cutoff here would be more helpful.  Also, on the versions list in the KB, SQL2008 Std is not listed, although SQL2005 Std edition is listed.  Any help here would be great.  Thanks.



Can we create Stored Procedure from SSAS MDX Query


HI All,

Is this possible to create STORED PROCEDURE TO FROM MDX QUERY,if yes please tell me how and also can we use that STORE PROCESDURE in SSRS report.



How to execute a concatenated query in a procedure

I have a procedure which is->
create procedure procedure1(@city varchar(20),@department varchar(20),@name varchar(20))
declare @a varchar(50);
declare @b varchar(50);
declare @c varchar(50);
declare @d varchar(500);
if @city=''
set @a='';
set @a='where emp_city='+@city;
if @department=''
set @b='';
set @b='and emp_department='+@department;
if @name=''
set @c='';
set @c='and emp_name='+@name;
set @d='select * from employees where'+@a+@b+@c;
---???????????????????????????WHAT TO DO TO EXECUTE @d?


Now in the procedure, @d holds a select query but it is in varchar format. I cannot execute that as a query in the procedure.
How to execute the query stored in @d?
pls help.

Recursive computation using scope, cache, and query order

I have a situation where I have two queries Q1 and Q2 and the order of execution of these queries produces drastically different behavior. It seems that the cache is playing a strange role here which I do not understand.

Q1 executes fast always. Q2 when executed after Q1 executes fast. However, after dropping the cache (by doing a deploy/process), and when Q2 is executed first,  the query Q2 never returns. Perhaps it is related to the way I have setup a recursive computation using scope. I would appreciate if someone can look at the computation below and offer insights into where the problem may be.

Scenario 1) 
 Execute Q1 - fast response
 Execute Q2 - fast response

Scenario 2)
 Drop the cache, by doing a process.
 Execute Q2 - NO response from the server, SQL Profiler shows millions of cache queries.
 Abort Q2.
 Execute Q1 - fast response.
 Execute Q2 - fast response.


Details of queries, and the context.

Q1: SELECT FutureWeeks ON ROWS,
 {[Product].[Product Name].[CHJ-1062]} * [Measures].[Replenishment]

Cannot find execute any query, stored procedure not found even if it is there


Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?

Ju Lian

Variable names must be unique within a query batch or stored procedure. ???


Hi i am using sql data source to delete first from Child Table and second check if there is no child  go and delete the primary key in Parent table

this is my code

<asp:SqlDataSource ID="SDSOrdersGrid0" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            DeleteCommand="Declare  @OrderIDinWeek
Set @OrderIDinWeek=(Select order_dtl_id from Weeks where IdWeek=@IDweek)
IF (SELECT count(*) FROM  weeks WHERE order_dtl_id =@OrderIDinWeek) =0
   delete from order_dtl where order_dtl_id= @OrderIDinWeek

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Any plans on releasing the source code for WebMatrix itself?


I tried reflecting into WebMatrix.exe but was unable.  I'm interested on how you integrated the WPF Ribbon & the Office-style sidebar.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend