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 table referenced by it in the query plan? Any thoughts?

Thx!



ADVERTISEMENT

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 foo.bar. 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 foo.bar?

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):

CREATE PUBLIC SYNONYM LIB_GROUP_PERMITS FOR LIB_GROUP_PERMITS;

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!

Thanks!

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?
 
Rob
 

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.  When I 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 have searched 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 when appropriate.

fwiw - Not a newbie t-sql guy.  ISV working almost daily with t-sql since MS SQL 2000.  I have never seen this message before...at 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.

<Error>

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'.

 </error>

<code>

 CREATE SYNONYM ARContractTerms_syn FOR srvDEV.EricsAdeptCastle.dbo.tblARContractTerms

 SELECT * FROM ARContractTerms_syn

</code>

Am I running into schema problems?

Thanks all

 

Microsoft SQL Server Management Studio     9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.2900.2180
Microsoft .NET Framework      2.0.50727.42
Operating System      5.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')

---Procedure

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')

---Procedure

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,
Nishant

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.

Thanks,

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,
@cont_cvarchar(1),
@invoice varchar(9)
as
Select
CONT_C,
INVC_I,
DIV_C,
REG_C,
LOC_I,
INVC_D,
CUST_I,
CR_PREF_C,
FISCAL_DD_D,
FISCAL_MM_D,
FISCAL_YY_D,
PAY_CODE,
REF_TEXT,
EC_TYPE,
ADJUST_A,
ALLOWANCE_A,
MAT_A,
TAX_A,
FRT_A,
REEL_A,
OTHER_A,
GST_A,
PRIOR_BAL

from MY_FIVE_YEAR_VIEW

whereFISCAL_YY_D = @FISCAL_YY_D
AND cont_c = @cont_c
AND FISCAL_DD_D = @FISCAL_DD_D
AND FISCAL_MM_D = @FISCAL_MM_D


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 ??

Thanks.

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],
p.ServiceProvider,
s2.Served
from dbo.Assessment a
INNER JOIN (
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
LEFT OUTER JOIN (
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'
End

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

 

Hi,
 
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...
 
Thanks

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 http://www.sommarskog.se/sqlutil/query_plan_tree.html.--Erland Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/pr...oads/books.mspxBooks Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodin...ions/books.mspx

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..

Hi,

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...?

Thanks,

S.

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.

 

SELECT *

FROM Person

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

 

AND

 

SELECT TOP 100 PERCENT *

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(retrive to the batch) 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

 

Hi,
 
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:


SET SHOWPLAN_XML ON

go

MyQuery
go

SET SHOWPLAN_XML OFF

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

In my application, I call Connection.Execute to execute 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?
 
Thanks
 

View Replies View Related

Query Plan In SQL Server 2005 SP2

Hello,
 
The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ 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,
Smitha
------------------------------------------------------------------------

QUERY:
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,
 202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
 26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
 2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
 1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
 1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
 151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
 1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
 1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
 36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
 37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
 375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
 37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
 37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
 37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
 37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
 102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
 1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
 16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
 1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
 1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
 113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
 112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
 4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
 29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
 25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
 8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
 814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
 641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
 6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
 6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
 11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
 94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
 7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
 952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)
 
INDEX Definition:
CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved