.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

Query Plan handle is not matching in the query related system DMV's

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

Ent : SQL Server 2008 SP17

OS  : Windows Server 2008 Ent.Edt

While troubleshooting a performance issue for a Query (SP) I came across this weired thing that the Query Plan handle is not matching across all the Query related system DMV’s.


Senario with a simple query

For each executing Query a plan handle is generated in sys.dm_exec_connections (Column most_recent_sql_handle).Based on the sql_handle the q

View Complete Post

More Related Resource Links

Query execution plan problem

Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

Weird query plan problem where total is over 100%

Hi All, I am looking at the query plan for a stored procedure and I am seeing things like 1400% on the execution plan, the stored procedure I believe is called within a loop quite a lot of times, but I don’t quite understand why the percentages will be over 100 for one section of the execution plan. Any explanation for this ? Thanks in advance.



I have two tables, and i want to return all records where both fields match for example below is a sample table

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

There is insufficient system memory in resource pool 'internal' to run this query.



I have a .NET application executing a stored procedure on a SQL Server 2008 SP1 64 bit instance (version 10.0.2531.0), and getting the following exception:

There is insufficient system memory in resource pool 'internal' to run this query.

Maximum server memory is set to 4096MB. The server has 4GB of physical RAM available, and is running Windows Server 2008 R2 x64.

The SQL Server instance is only consuming approx 1.5GB RAM and there is 15+% physical RAM unused.

The messages in error.log are as follows:

2010-09-25 03:28:38.15 spid51    Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2010-09-25 03:28:38.15 spid51   
Memory Manager                  KB
---------------------------------------- ----------
VM Reserved                 4277792
VM Committed                1532212
Locked Pages Allocated              0
Reserved Memory                1024
Reserved Memory In Use              0
2010-09-25 03:28:38.15 spid51   
Memory node Id = 0                KB
---------------------------------------- ----------
VM Reserved                 4276704
VM Committed                1531184
Locked Pages Allocated              0
MultiPage Allocator              38992
SinglePage Allocator            1371648
2010-09-25 03:28:38.15 spid51   
---------------------------------------- ----------
VM Res

How to estimate "Minimum query plan threshold for considering queries for parallel execution(cost es



We have 4 QUAD core processors and we have set max degree of parellelism to 4(as suggested by Microsoft). As of now we are using the default value for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)".

Please suggest how to estimate the best value for for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)"?

Thanks in advance.



Parallel plan for simple query ! Any solutions


I am getting a parallel plan for a simple query below. Any workarounds

	UPDATE tblProfile
	SET PlanName = NULL, PlanValue =NULL
	FROM tblProfile pfl
	INNER JOIN tblCustomerPlan ts 
		ON pfl.planID = ts.planID
	WHERE (ts.Name = 'VOID' OR ts.Name = 'IPDC')
	AND ProfileURN = @ProfileURN 

query: last login record without a matching logoff record


Hello. I am creating a login/logoff audit program to track / restrict users on our network.

I have a table containing log on/off records so : loginid, userid, logindatetime, state, computername (state is either: 1 logon, -1 logoff or 0 attempted logon)

its probably quite simple but i can't quite get my head around it, i need a query to tell me who is currently logged on, ie the most recent logon (state=1) which has no logoff (state=-1) to match. the trouble is that this must cater for users being able to log on to multiple computers so somewhere the computer names must match too when eliminating records.

i currently have the following query for selecting the most recent logon, but can't think how to eliminate the ones which have a matching logoff

with cte as (
	select *, ROW_NUMBER() over (partition by UserID order by a.LoginDateTime desc) as seq from LoginAuditTable a where a.[State] = 1

There is insufficient system memory in resource pool 'internal' to run this query.


Somebody know what kind of Error is "There is insufficient system memory in resource pool 'internal' to run this query."


Reason as why should we read Graphical Query Execution plan from right to left?


Reason as why should we read Graphical Query Execution plan from right to left?

What is the reason for the right to left order?

Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

A query plan issue


There are 3 paths in stored procedure p2:

create proc p2
 @n int
 declare @sql varchar(100)
 if @n=1
 set @sql='select *from ta where id='+cast(@n as varchar(10))
 else if @n=2
 set @sql='select *from tb where id='+cast(@n as varchar(10))
 set @sql='select *from tc where id='+cast(@n as 

System query



I found "FETCH adjust_identity INTO @objid" has big logic reads in my system. Is it a system query?  is it a problem to have big # logic reads for this? Please advice.


SaveFileDialog Related Query


Hi All,

I have a Winform which uses SaveFileDialog to save a file. Everything is fine except that file is saved with only one sheet & the name same as filename.

I want to have the sheet name as Sheet1 and also want to install extra sheets into excel with names Sheet2,Sheet3 etc.

Please suggest something with example. Thanks in advance!!!

Why do some XML functions have such high query plan cost?


Does anyone know why this generates an expensive query plan:


SELECT @XML = '<XML Id="Hello"></XML>'

SELECT c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML') T(c);

The queryplan includes two "Table Valued Function [XML Reader]"'s to occur with a cost of 61.08 each on my 2005 sp3 developer server.  If I select out an attribute then it is considerably lower:

c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML/@Id') T(c);

This results in a [XML Reader] and a [XML Reader wuth XPath filter] and the cost is 1.004 for each operator.

Running tests show that they are both quick, but I am writing a stored proc for a high transaction system and always try to keep the query plan as low as possible and would not n

how to create a query in gridview selectcommand without the code behind for getting the system date.


how to query the system date in selectcommand in gridview without using the codebehind. using c# .net


my code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM tablename where date_today = <SYSTEMDATE>" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

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
name Disp settext
A and E Bank Issue
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