Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Synonym And Query Plan

I may just be completely missing something here but, when I view a query plan from a SQL statment that involves a join with a synonym I do not see any reference to the synonym or the underlying tablereferenced by itin the query plan? Any thoughts?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SYNONYM Question
Is it possible to create a SYNONYM that does not require you to use the owner prefix? Similar to an Oracle PUBLIC synonym.
for example:

CREATE SYNONM MySynonym FOR dbo.myfunction.

SELECT MySynonym FROM dbo.mytable;

View Replies !   View Related
Synonym Question
Suppose that a synonym foobar exists pointing to the table I also have a table with the same name in my schema. (mpswaim.foobar)

If I do a select

select * from foobar

Which table does the select run against? mpswaim.foobar, or

In Oracle, mpswaim.foobar would win, and we used this to all ow individual developers to have their own version of application tables during development.

View Replies !   View Related
Create Public Synonym
I get an error in query analyzer when running (parsing query):


with an error of:

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PUBLIC'."

Can anyone help me at all please!


View Replies !   View Related
Synonym List Not Displaying
I added several synonyms to my database. The synonyms reference tables in another database. When I am building a query I cannot see the list of synonyms when I am in the Add Table window. I see all of the database's tables and views but none of the synonyms. This happens in SSRS's query builder, when building a View or building a query directly in Management Studio.

However, if I join the table in the SQL by typing it in, the table will then appear in the graphical view. And the queries perform just fine.

Am I doing something wrong?


View Replies !   View Related
Synonym Does Not Work On Functions?
I ran the following command to create a synonym for a function -
create synonym testfunc for myschema.myfunc

Then testfunc will be created in the dbo schema. WhenI call this function from my stored procedure by 'testfunc', I received an error indicating 'testfunc' is not a recognized build-in function name. If I call it by 'dbo.testfunc' then it will work.

If I create a synonym for a table, I can access the table using the synonym in my stored procedure without any problem.

Is it true that synonym works differently on tables vs. functions?

View Replies !   View Related
SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan
Hi Everyone:

*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error

"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I would havesearched the forums myself, but at this moment in time, search is broken :(

If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.

It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them whenappropriate.

fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message least I don't recall ever seeing it.

Thanks in advance for other suggested examination paths.

View Replies !   View Related
How To Load Data Into A Table Using A Synonym?
I'd like to use a data flow task to load data into a table by specifying the synonym name of the destination table, instead of the actual table name.

The OLE DB Destination is forcing me to pick an actual table or view from a drop down list. Any ideas on how to get around this?

Thank you.

View Replies !   View Related
Setting Up A Synonym In SQL Server 2005
Can anyone tell me why I am getting this error when I try to select * from a table through a newly created synonym? I have admin rights to both db, but they are on separate servers.


OLE DB provider "SQLNCLI" for linked server "srvDEV" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18456, Level 14, State 1, Line 0

Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.



CREATE SYNONYM ARContractTerms_syn FOR srvDEV.EricsAdeptCastle.dbo.tblARContractTerms

SELECT * FROM ARContractTerms_syn


Am I running into schema problems?

Thanks all

Microsoft SQL Server Management Studio9.00.2047.00
Microsoft Analysis Services Client Tools2005.090.2047.00
Microsoft Data Access Components (MDAC)2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer6.0.2900.2180
Microsoft .NET Framework2.0.50727.42
Operating System5.1.2600

View Replies !   View Related
Is It Possible To Timestamp A Synonym Name To Avoid Conflicts
I am working with SQL Server 2005, here is what I am doing:

declare @cx as varchar(100)

set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

exec ('CREATE SYNONYM tblsynonym FOR ' + @cx + '..TableName')


drop synonym tblsynonym

The application i'm working on uses stored procedures that will at some point be called by more than one user at a time. At the start of the stored procedure the synonym is created and then it is dropped when the procedure completes, the issue is this: if two users access the same stored procedure at the same time then the first procedure will create the synonym and the second will fail because the syonym already exists.

Here is what I would like to do:

declare @cx as varchar(100)

declare @timestamp as datetime

set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

exec ('CREATE SYNONYM tblsynonym' + @timestamp + ' FOR ' + @cx + '..TableName')


drop synonym tblsynonym

Any ideas??????

View Replies !   View Related
Getting Query Plan From VB
When I set Query plan "On" from query options in SQL Query Tool, it gives me the query plan of the query fired.

Can I get the same (query plan) of a query fired from VB using RDO?

Thanks for any help,

View Replies !   View Related
Query Plan
I am noticing a discrepency in query plans when a process is run in Analyzer as either a proc or as straight sql.

I have a query that uses a view of 5 tables that have a check constraint on the year. When I run my query in query analyzer and state year = 1999 along with over parameters then the query plan only looks at the one table.

When I take that query and make a stored proc and run the process passing the year = 1999 along with other parameters the plan states that it is looking at all of the tables in the partitioned view.


Here is a copy of the proc

create procedure testproc
@CUST_I varchar(6),
@FISCAL_DD_D tinyint,
@FISCAL_MM_D tinyint,
@FISCAL_YY_D smallint,
@invoice varchar(9)


AND cont_c = @cont_c

AND (REF_TEXT LIKE '%' + @CUST_I + '%' or REF_TEXT LIKE '%' + @invoice + '%' )

order by cust_i, pay_code

View Replies !   View Related
Saving Query Plan
I would like to save a query plan (estimated or actual)created in Query Analyzer -- paste it into a document,or simply print. It doesn't seem to be possible toselect and copy the Execution Plan window, and printingit creates microscopic gibberish which is a waste ofpaper. Is it possible to do this?Set showplan_text is of limited help for the SP I'mlooking at -- while analyzing the SP, it reads aheadand complains that a temp table created inside the SPdoesn't exist (yet) and exits. Using Ctrl-K to capturethe query plan allows the SP to complete, but saving theplan is the problem.Thanks,Jim Geissman

View Replies !   View Related
MS SQL 2000 SP Query Plan
I have a couple of complex stored procedures that work well and quicklyonce they have compiled. The problem I am running into is that everyonce in a while they want to refresh thier execution plans, and whenthat happens it takes about 1 minute and 30 seconds for them torebuild, well of course my application is set up to time out commandsafter 30 seconds so basicly the stored procedure never completes andhangs up all of my subsequent stored procdures.I have tried to useOPTION KEEP FIXEDPLANon all of my select statments but I was wondering what else could bedone to stop a stored procedure from it's need to rebuild.-Adam

View Replies !   View Related
Odd Query Plan For View
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.

View Replies !   View Related
Puzzled By Query Plan
I'm hoping somebody can explain exactly what's going on here - I can'tfind it documented anywhere.Go to the Northwind database, and run the following SQL:create index IX_UnitPrice on [order details](unitprice)Now, turn on SHOWPLAN (either graphical or text, it doesn't matter),and run the following query:select * from [order details]where unitprice = 2Output:StmtText|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([OrderDetails].[UnitPrice]=Convert([@1])) ORDERED FORWARD)Now, alter the SARG slightly by making it a float:select unitprice from [order details]where unitprice = 2.000Output:StmtText|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],[Expr1005]))|--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@1]))-1.00,[Expr1004]=Convert(Convert([@1]))+1.00, [Expr1005]=If(Convert(Convert([@1]))-1.00=NULL) then 0 else 6|If(Convert(Convert([@1]))+1.00=NULL) then 0 else 10))| |--Constant Scan|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >[Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:(Convert([Order Details].[UnitPrice])=Convert([@1])) ORDERED FORWARD)Right. I understand that in both cases the SARG datatype is differentfrom the column datatype (which is money), and that in the firstexample the SARG constant gets implicitly converted from int -> money(following the datatype hierarchy rules), and so the index can stillbe used.In the second example, the datatype hierarchy dictates that money islower than float, so the table column gets implicitly converted frommoney -> float, which strictly speaking disallows the use of the indexon that column.What I DON'T understand is what exactly all that gubbins about theexpressions (especially the definition of [Expr1005] is all about; howdoes that statement decide whether Expr1005 is going to be NULL, 6, or10?I'm soon going to be giving some worked tutorials on index selectionand use of Showplan to our developers, and being a bolshi lot they'rebound to want to know exactly what all that output means. I'd ratherbe able to tell them than to say I don't actually know!How about it someone?Thanks,Phil

View Replies !   View Related
Query Plan Re-use On Views?
Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?

View Replies !   View Related
SQL 6.5 Query Execution Plan .
Hello ,

I wanted to know whether we have an execution plan enabled in SQL 6.5 as we have it in SQL 7.0 and SQL 2000 .
I.e when we execute a query and if we enable ' show execution plan 'then it creates a map and shows the vital statistics .
If that is available on SQL 6.5 then i am missing that tool .

How can i have it installed on my SQL 6.5 server ??


View Replies !   View Related
Strange Query Plan
I have a query like below .. if i add where Served = 1 , the query takes foreever... if i remove it, it takes only 6 sec...

I am not sure why this is hapening?

select distinct a.Episode_Key,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'
end as [Category],
ag.Agreement_Type_Name as [Agreement],
from dbo.Assessment a
select distinct Episode_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Episode_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
select distinct Episode_Key, p.ServiceProvider,
1 as [Served]
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '01/01/2008' and '01/31/2008'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Episode_Key = s2.Episode_Key
and p.ServiceProvider = s2.ServiceProvider
????---where Served = 1
group by a.Episode_Key, ag.Agreement_Type_Name, p.ServiceProvider, Served,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'

View Replies !   View Related
Incorrect Query Plan
I was doing a demo last night, something that I've done hundreds of times already. Last night was the first time that it has failed to work. I was trying to show what the sys.dm_db_missing_index_* DMVs can provide.

AdventureWorks database

I'm running the following query:

select city from person.address where city like 'A%'

This is supposed to produce a table scan which in turn will obviously cause SQL Server to detect that an index could be beneficial. However, it does a clustered index scan (yes, I know, basically the same thing) instead and I see absolutely nothing appear in the DMVs. I pulled the data out into a dummy table that did not have a primary key either using the following:
select * into person.tmpaddress from person.address

I then execute the same query and get a table scan which is expected:

select city from person.address where city like 'A%'

However, it does not matter how much I execute that query or any other permutation of explicit query, absolutely nothing at all gets logged into the sys.dm_db_missing_index_* DMVs. I have also tried this same type of thing with several other tables in the AW database and can not find a single query which will cause anything to be logged to these DMVs. It seems that something is broken, but for the life of me, I can't figure out what is wrong. No weird settings, I'm running as sa, etc.

I can run queries like this in other databases and stuff gets immediately logged to the DMVs as expected. Any ideas?

View Replies !   View Related
Estimated Query Plan


I am writing a client application that shows estimated queries plans and statistics. I know how to obtain estimated plans by using SQL Server Management Studio. But is it possible to obtain by using database functions?

I have found sys.dm_exec_query_plan, but it seems that this function can only be used for executed (or executing) queries...


View Replies !   View Related
Query Plan In Clear Text
hi.coming from postgresql, i am used to textual references to most of thethings i do with the database. i feel a little lost with all the graphical.i have few questions regarding MS SQL 20001. what is the best (or easiest) way of getting a table definition in text?it could be either a CREATE TABLE sql-query or a just a definition,something like:TABLE thisTableidintegervaluevarchar(10)etc.etc.2a. how do i get a query plan and how do i get it in text.2b. are there planner modes that show more or less of what actuallyhappened, verbose mode perhaps?2c. if i ask for a query plan, will SQL server actually run the query orwill it only produce a plan. if the query is run, does it commit orrollback by default?stig

View Replies !   View Related
Query Plan Tool On My Web Site
I'd like to announce a free tool that is now available from my web site.It's not something that I've written myself, but it's a small utilitywritten by Ivan Arjentinsky that permits you to view the query plan for aquery in tree format.You find it on Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server 2005 at Online for SQL Server 2000 at

View Replies !   View Related
Looking For Query Plan Determination Information
Hi,I was just helping a coworker optimize a query. He had two versions:one which used UNION for each value for which he was tallying resultsand another query which used GROUP BY. Here is an aproximation of whatthey were:Query #1:---------SELECT 12 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 5UNIONSELECT 13 AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateAND T.col2 = 6Query #2:---------SELECT R.row_num AS [Row],ISNULL(SUM(CASE WHEN T.my_date BETWEEN @week_start_date ANDDATEADD(d, 1, @week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,@week_start_date) AND DATEADD(d, 2, @week_start_date) THEN 1 ELSE 0END), 0) AS [Tuesday]FROM My_Table TINNER JOIN Another_Table T2 ON T2.col1 = T.col1INNER JOIN Report_Rows R ON R.col2 = T.col2WHERE T.my_date BETWEEN @week_start_date AND @week_end_dateGROUP BY ALL R.row_numORDER BY R.row_numThe Report_Rows table in this case would have had two rows mapping row12 to a column value of 5 and row 13 to a column value of 6. Thesecond query was performing horribly until I noticed the ALL keywordin the GROUP BY, which I didn't think was necessary. When I removedthat it performed more like I expected it to perform.Before I had noticed that I was scouring over the query plans andcouldn't figure out why in one instance the query optimizer chose tojoin My_Table and Another_Table, yet when the ALL keyword was there itchose to return all of the records from Another_Table (a rather largetable) and join it to the Report_Rows table before then joining toMy_Table, which had the date criteria in the WHERE clause.So, if you've read this far without giving up...1. Why would the ALL keyword cause this? I understand thefunctionality of ALL, but I still don't see why that caused thereordering of the joins.2. (more importantly) Are there any good resources that you know ofthat explain how the query optimizer choices its query paths? Do the"Inside SQL Server" books go into that much detail? Any good onlineresources?Thanks!-Tom.

View Replies !   View Related
Avoid Reusing Query Plan..

I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?



View Replies !   View Related
Poorly Chosen Query Plan
I'm running the same query on two computers but getting a different query plan. On one of the servers, the query returns in 10 seconds, on the other server it takes over a minute. What the heck!

1. same hardware configuration on both computers
2. same user databases on both computers
3. same NT setup on both computers
4. same software installed on both computers

I can only imagine that MSSQL7 was setup differently and the query optimizer is making a stupid choice. I've compared numerous SQL server options and they are both the same.

1. sp_configure (same on both computers)
2. properties sheet on each server from enterprise manager (same on both computers)

any ideas???

View Replies !   View Related
Query Plan SQL Server 2008
I have a person table with 1 billion rows on it, partitioned equally at 10 million rows per partition. The primary key constraint is a composite of an identity column and ssn( char(11) ) with the partitioning column built on the SSN.

This is built on my home grown workstation:

Microsoft 2008 Server 64 bit, Microsoft SQL server 2008 64 bit, Intel 2.66 quad core, 8 gb ram, Os/ raid 1, data on 6 drives hardware/software raid 50, transaction logs on 4 drive raid 10, all drives sata II/ 3gb burst.

I have updated statistics on the table and I have 2 queries that give clustered index seek , one never comes back before I get impatient, the other comes back instantly, and the showplan looks the same for both queries.


FROM Person

WHERE PersonKey > -1 and SSN = '219-09-3987'



FROM Person

WHERE PersonKey > -1 and SSN = '219-09-3987'

Incidentally the query with the top 100 percent is the one that returns instantly.

I am puzzled

1) Why the estimated plan looks the same

2) Why a top 100 Percent query is faster than one without it

Timothy A. Vanover

View Replies !   View Related
Query Cost In Execution Plan
what does query cost(retrivetothebatch) mean in execution plan?
what is the differeence between query cost :100% and 65%?

View Replies !   View Related
Query Plan Utilization Vs CPU Time...
I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
SELECT Index Seek
Cost:0% <------- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
SELECT Index Scan
Cost:0% <------- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

------------------------------------------------STAT TIME-----------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

------------------------------------------------STAT TIME-----------------------------------------------------------

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

View Replies !   View Related
Execution Plan In Query Analyzer
This is probably a very stupid question. I have been out of the SQL Server arena for awhile and am now getting re acclimated. It was my understanding that using execution plan in query analyzer does not really execute the query against the query's database tables. Is this right? Tom.

View Replies !   View Related
View Query Execution Plan


I am developing an application (VB)that should present a query estimated execution plan.

Using the SQL Server Management Studio, I should execute the following commands to see the query's estimated execution plan:






The query is not executed. The result is the query execution plan.

In my application, I call Connection.Execute toexecute the 'SET SHOWPLAN_XML ON'. Then, I use a Resultset submit the query. The query is executed and the execution plan is not returned.

Does anyone have any ideas?


View Replies !   View Related
Query Plan In SQL Server 2005 SP2

The query included at the end of this post seems to use the 'wrong' index when executing and takes2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint= 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms
CPU = 111141 ms
Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms
CPU = 687 ms
Reads = 5558

Statistics are updated every night (w/Fullscan)
Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?
Doesn't Profiler take into account the"Subtree Cost" for the Index Seek(which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,

select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,

INDEX Definition:
CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

View Replies !   View Related
Obtain The Query Plan Of A Running Process
Hi,Is there a way to findout the query plan of the executing process usingthe SPID/KPID information.Thanks in advance,Thyagu.D

View Replies !   View Related
DBCC Memory Status And Query Plan
When I executed DBCC Memory Status following are the out putBuffer DistributionStolen187191Free1026Procedures6698Inram0Dirty154661Kept0I/O0Latched772Other436084Buffer CountsCommitted786432Target786432Hashed591517Internal Reservation510External Reservation2291Min Free 1024Visible200408Procedure CacheTotal Procs563TotalPages6698InUsePages6576Dynamic Memory ManagerStolen192631OS Reserved920OS Committed898OS In Use894General 5017Query Paln183216Optimizer0Utilites658Connection4110Global Memory ObjectsResource3428Locks477SQLCache850Replication2LockBytes2ServerGlobal22Xact260Query Memory Manager6Grants6Waiting0Maximum5922Available2389Now I am getting the following error2006-10-23 14:34:20.91 spid904 BPool::Map: no remappable addressfound.2006-10-23 14:34:20.94 spid465 BPool::Map: no remappable addressfound.2006-10-23 14:34:20.94 spid62 AWE mapping status:2006-10-23 14:34:20.94 spid62 There are 200408 pages in the AWEwindow2006-10-23 14:34:20.94 spid62 193325 pages have a bstat of 0000002006-10-23 14:34:20.94 spid62 3043 pages have a bstat of 00000b2006-10-23 14:34:20.94 spid62 2844 pages have a bstat of 0000092006-10-23 14:34:20.94 spid62 633 pages have a bstat of 0000202006-10-23 14:34:20.94 spid62 563 pages have a bstat of 0010012006-10-23 14:34:20.94 spid62 BPool::Map: no remappable addressfound.2006-10-23 14:34:20.96 spid530 BPool::Map: no remappable addressfound.2006-10-23 14:34:20.97 spid240 BPool::Map: no remappable addressfound.2006-10-23 14:34:20.99 spid275 BPool::Map: no remappable addressfound.2006-10-23 14:34:21.02 spid465 BPool::Map: no remappable addressfound.System is configured with AWE and 6.5 GB to SQL Server. Total Mem: 8 GBQuestions1. I see QueryPlan very high . What is the difference between QueryPlanand Procedures Counter . My experiment indicates QueryPlan Value isalways greater than Procedures . Is it correct ? Can Some one explainquery plan correctly . It is confusing from the MS Article2. I suspect QueryPlan counter is high ? Is the problem is inBufferPool or MemtoLeave ?3. Statements executed mostly are prepared statements

View Replies !   View Related
Difference In Query Plan For Constants/variables (bug)
I have a piece of code in a stored procedure which I've tested under SQL 7. I discovered something interesting, and I'm wondering whether it is a known bug.

The code is an update statement, and updates various items depending on a criteria which is in a variable. When I run this under 7, I get a different query plan if I use the code with the variable than if I replace the variable with a constant. What is worse, the plan including the variable is an order of magnitude slower than that without.

I worked around this by building a query on the fly and embedding the value of the constant in the query string.

Has anyone else run in to this? Is it a known bug?


Bruce W Cassidy
Senior DBA Consultant, Datacom Systems Limited

View Replies !   View Related
Query Optimizer/Wrong Execution Plan
I have SQL 7.0 SP2 on NT 4.0 SP5. My database is 180GIG. 23 Tables. It has been up and running for 2 years without any problems. All of a sudden my queries have started taking a long time to run. The optimizer has decided that table scans are better than indexes. If I use query hints they work just fine, but I can't modify all of our code to make these changes.

This is happening on all tables. Records counts are the in the same range they have always been.

Statistics and indexes are all fine and current. Have dropped and rebuilt both.

Has anybody else seen this behavior.

View Replies !   View Related
Query Execution Plan Tutorial / Links
Hi All,

I have to get some knowledge in Query Execution Plan.

Is there any links which i can refer for the same


View Replies !   View Related
Partinioned View And Query Plan: Oddity?
Hello all,

I'm going to implement an inverted index data structure that relies, for performance consideration, on partinioned views.

I've created a set of tables with a column named "PartitionKey" that deserve to partition the view.Then I've created a view using the UNION ALL as prescribed. The partinion works.

Now I'm devising the right SELECT query to benefit from the partition but I'm getting strange query plan.

"word" is the partinioned view and "vocabulary" is a referenced table.

If I write something like this:

select * from

vocabulary v

inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey


word = 'foobar'

and w.partitionkey = 1 -- It works correctly even with the condition v.partitionkey = 1

The query plan, correctly, shows that the only table actual accessed is "word1", the one related to the partinionkey value 1. ==> OK

If I write something like this, that appears more intuitively:

select * from

vocabulary v

inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey


word = 'foobar'

I get a concatenation operation that involved all the "wordXX" tables. I deduce that the engine is not so "clever" to benefit from the join hint.

So, I rewrite the previous as the following, thinking to feed a better infotmation to the engine:

select * from

vocabulary v

inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey


word = 'foobar'

and w.partitionkey = (select partitionkey from vocabulary where word = 'foobar')

That resembles the first example but... it doesn't work: I still get a concatenation operation.

I'm very frustrated and than rewrite again the query in a T-SQL script such this:

declare @partitionkey as int

set @partitionkey = (select partitionkey from vocabulary where word = 'foobar')

select * from

vocabulary v

inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey


word = 'foobar'

and w.partitionkey = @partitionkey

And I still get a concatenation operation.

I can understand the behaviour of the second and third example (the query optimizer doesn't have an actual value for the partition key) but the fourth is unbeliavable. Even if I'm feeding a query with a fixed value it cannot benefit of it at all.

Where am I wrong? Is so "silly" the qwery optimizer? How can I force the expected behaviour?

I would say more that the "partitionkey" is generated randomly when a new word is initially inserted into the parent table "vocabulary" so I cannot compute it before to access the "word" table.

I've tested on SQL2005. If anyone is interested I can provide the queryplans generated for all the above examples.

View Replies !   View Related
Error Loading Query Execution Plan
I am trying to tune a very long running query (18 minutes on an Axim X51, 8secs on my laptop), but I can't get the query plan file that is generated on the device to load in the Sql Server Management Studio.  I am using the Sql Everywhere CTP on the device, and version 9.00.2047 of the management studio shell.

FWIW, when I try to create the execution plan by running the same query on a .sdf file local on my laptop, I get a similar error trying to view the query plan.

Apart from the query plan issues, it would appear (just from the query execution time) that the indexes defined on the sdf file are not being used when executing the query on the device, but are being used when executing the query on the laptop.  This is pure SWAG on my part, though.

I can't figure out how to attach a file to the post, unfortunately.

Thanks for any help you can offer.

Matthew Belk, BizSpeed, Inc.

View Replies !   View Related
Query Plan For Stored Procedure Not Being Generated
I have a stored procedure that I want to test for performance , however,I cannot view the estimated query plan execution. I am not using any nested subqueries. Any ideas why the plan will not generate?

Thanks in advance!

View Replies !   View Related
Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.
SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to thedatabase integrityand should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View Replies !   View Related
Accessing Estimated Query Execution Plan (QEP) Statisitics
Hi,I have a question about estimated query execution plans that aregenerated in QA of MSSQL.If I point at an icon/physical operator in the estimated QEP, it showsmesome statistics about the operator.Is there a way to retrieve these statistics through a query, i.e., canthese statistics be available to the user?Also, is there a way to generate these statistics on my own?thanks in advance-TC.

View Replies !   View Related
Index Distribution Statistics And Show Query Plan
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.

select orderID, custId, intertcsi from tblorders
where intertcsi = '2815'

The type of query is SELECT
Nested iteration
Index : indxInterTCSI
orderID custId intertcsi
----------- ----------- ---------
1015245 1011313 2815
2556392 2556392 2815

Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0
Then I use the same select statement to force a table scan:

select orderID, custId, intertcsi from tblorders (index=0)
where intertcsi = '2815'

The type of query is SELECT
Nested iteration
Table Scan
orderID custId intertcsi
----------- ----------- ---------
60472 61084 2815
102184 102333 2815
Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980

When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run.
Any idea or comment would be very appreciated.

View Replies !   View Related
Incorrect Query Plan With Partitioned View On SQL 2000
I have a partitioned view containing 4 tables (example follows at end)

The query plan generated on a select correctly accesses just one of the tables

The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area

Note that the behaviour is the same with SP1 on SQL2000

I would be very grateful for any advice


Stefan Bennett

Example follows

--Create the tables and insert the values
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)

INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')

--create the view that combines all sales tables
CREATE VIEW Sales_National
FROM Sales_West
FROM Sales_North
FROM Sales_East
FROM Sales_South

--Look at execution plan for this query
-- This correctly only accesses the South partition
FROM sales_national
WHERE region = 'south'

-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;

View Replies !   View Related
Accessing Query Execution Plan Results Programmatically
Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample


but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.

Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.

I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.

Thanks -- MT

View Replies !   View Related
How To Find Query Plan For A Stored Procedure Using Temporary Tables
This post is related to SQL server 2000 and SQL Server 2005 alleditions.Many of my stored procedures create temporary tables in the code. Iwant to find a way to find the query plan for these procsRepro--***********************************use pubsgoCREATE PROCEDURE Test @percentage intASSET Nocount on--Create and load a temporary tableselect * into #Temp1 from titleauthor--Create second temporary tablecreate table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ordint, rolaylityper int)--load the second temporary table from the first oneinsert into #Temp2 select * from #Temp1goset showplan_Text ONgoEXEC Test @percentage = 100GOset showplan_Text OFFgo**************************************I get the following errorServer: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp2'.Server: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp1'.I do understand what the error message means. I just want to know abetter way of finding the query plan when using temp objects.My real production procs are hundreds of lines with many temp tablesused in join with other temp tables and/or real tables.Regards

View Replies !   View Related
Funny Results In Display Execution Plan In Query Analyzer
Working with SQL2000 SP2, I get strange results whenever I try to use the Display estimated execution plan command in Query Analyzer.

Many operations appear as 0% (if only this were true!) whereas others can appear with 300% or 500%, and this doesn't necessarily have any connection with the real entity of work involved.

I haven't found any reference to this problem on the Microsoft site nor in these discussion groups, am I the only person in the world to have this problem?

Or maybe I am the only one who uses this functionality?

Anybody know any fixes?


David Westmore

View Replies !   View Related
Getting Error When Running Display Estimated Query Plan In Mgmt Studio 2005
When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727csc.exe" /noconfig /fullpaths @"C:Documents and SettingsxxxxxxxLocal SettingsTempzcxrpqb0.cmdline".

View Replies !   View Related
Cached SQL Plan Vs. Stored Proc Plan
We have a debate in our team about embedded SQL vs. Stored Procs.

The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?

I can't find any definitive information on pros and cons between the two methods.

If there are no major performance issues, or gotchas, I guess it comes down to developer preference.

SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.

Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic

Any info would be appreciated.



View Replies !   View Related
Equivalent To Informix/DB2 &"synonym&"
Hi all. Informix and DB2 support something called synonyms that allow you to basically create sort of an alias for a table at the database level. Think of it sort of as a shortcut or link to a table. Does SQL Server 2000 have a similar ability and if so how?

I know someone will ask why you want to do this, so heres a quick example:
If you have one legacy application that expects to write to one particualr table, but you wish to partition that table across several tables, you can break table1 up into tablea,tableb,tablec and then create a synonym called table1 that would point to only the appropriate table at the appropriate time. This way you can break a huge HUGE table up into logically discreet smaller tables and manage the creation of the appropriate synonym in some wrapper that sits in front of the legacy application...thus allowing you to retool a table that has outgrown its original design without having to crack open dreaded legacy code.

So, anyone?

View Replies !   View Related
How Can I Create A Database Synonym For The Actual Database Name
how can I create a database synonym for the actual database name?

View Replies !   View Related
Prepared SQL Plan Vs. Procedure Plan
I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.com

View Replies !   View Related

Copyright 2005-08, All rights reserved