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!
Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
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.
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
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;
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.
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!
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?
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?
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
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??????
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.
I'm using SQL Server 2008R2. I am developing a database which requires access to data from other servers. So far I have been creating views using OPENQUERY (where there's a performance benefit) to select specifically the columns I want. Generally, for my purposes, I find these OPENQUERY based views to perform better (some times significantly so) to simple SELECT <COLUMNS> FROM <SERVER>.<DATABASE>.<SCHEMA>.<TABLE> WHERE <Where clause Statements> format views. My understanding is that this is because an OPENQUERY "pushes" the query processing to the remote server and simply returns the final result set to the local server i.e. there's no cross-server join/synchronization going on.
My question is, if I were to create a Synonym for a table object on the remote server, where does the processing happen if I query from this Synonym or create a join with this synonym to a table in my local database?Essentially, I am trying to understand if there are any "hidden gotcha's" primarily from a performance perspective, to using synonyms.
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
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
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.
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?
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
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 2 Replies View RelatedI 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 4 Replies View RelatedI'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 4 Replies View RelatedI 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?
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
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???
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.
I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.
Is it possible to force SQL Server to use QA plan by giving a hint?
if t-sql query is perfectly run in development and when I execute in production at that time I want to use execution plan which is in development . so how I can do using cache? I know about hint we can use hint USE_PLANE. but I want to do with cache .
View 1 Replies View Relatedhi.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 7 Replies View RelatedHi,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 3 Replies View RelatedThis 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 1 Replies View Related