SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan

May 15, 2006

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 10 Replies

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

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 the database integrity and 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 5 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

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

View 3 Replies View Related

Internal Query Processor Error: The Query Processor Encountered An Unexpected Error During Execution.

Nov 26, 2007

I am running a query on a SQL Server 2005 database and encounter the following error message

"Internal Query Processor Error: The query processor encountered an unexpected error during execution."

There is a join between a table on the 2005 database and another on a 2000 database. I have run DBCC CHECKTABLE and found no errors on the two tables.

Anybody with ideas?

Thanks

View 3 Replies View Related

The Query Processor Ran Out Of Stack Space....Please Simplify The Query.

Oct 9, 2007

I received the following error today while migrating some code from SQL 2000 standard to SQL 2005 standard:

The query processor ran out of stack space during query optimization. Please simplify the query.

From what I gather, this error is usually an indication that one of the SQL Server 2005 maximum capacity specifications has been exceeded. However, I'm not sure which one. The only one that seems suspect is the number of nested subqueries (32), however I believe the numerous subqueries in my query would be classified as correlated rather than nested.

An example of my code is below. It is necessarily messy, as the output needs to be denormalized somewhat. Note that this runs just fine on SQL 2000, and it retrieves the 1500 or so rows of data in about 30 seconds. Thanks in advance for any ideas as to what may be causing this error. (Sorry for not using code-block, but all the html tags it threw in were exceeding the 50,000 character limit for the message)

BEGIN

SELECT

(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 1) AS A5,

(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 2) AS A6,

(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 3) AS A7,

(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 4) AS A8,

dbo.f_formatpseudodate(Accrued_Interest_To) AS AI,

dbo.f_getmiscdescription('AM', c.Card_Number, Amended) AS AM,

Basis_Shareblock_1 AS B1,

Basis_Shareblock_2 AS B2,

Basis_Shareblock_3 AS B3,

Basis_Shareblock_4 AS B4,

Basis AS BS,

dbo.f_getmiscdescription('C1', c.Card_Number, Price_Code_1) AS C1,

dbo.f_getmiscdescription('C2', c.Card_Number, Price_Code_2) AS C2,

dbo.f_getmiscdescription('C3', c.Card_Number, Price_Code_3) AS C3,

dbo.f_getmiscdescription('C4', c.Card_Number, Price_Code_4) AS C4,

dbo.f_getmiscdescription('C5', c.Card_Number, Price_Code_5) AS C5,

dbo.f_getmiscdescription('C6', c.Card_Number, Price_Code_6) AS C6,

CAST(Card_Date AS smalldatetime) AS CD,

c.Card_Number AS CN,

dbo.f_getisocurrencycode(Currency_Code) AS CR,

Card_Status AS CS,

dbo.f_formatpseudodate(Record_Date) AS CT,

Currency_Description AS CU,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 1) AS D1,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 2) AS D2,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 3) AS D3,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 4) AS D4,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 5) AS D5,

(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 6) AS D6,

dbo.f_formatboolean(Dutch_Auction) AS DA,

dbo.f_formatpseudodate(Dated_Date) AS DD,

dbo.f_getmiscdescription('DI', c.Card_Number, Distribution_In) AS DI,

Dealer_Manager_2 AS DL,

Dealer_Manager_1 AS DM,

dbo.f_getmiscdescription('DV', c.Card_Number, Dividend_In) AS DV,

Basis_Surrender_1 AS E1,

Basis_Receive_1_1 AS E2,

Basis_Amount_1_1 AS E3,

Basis_CUSIP_1_1 AS E4,

Basis_Receive_1_2 AS E5,

Basis_Amount_1_2 AS E6,

Basis_CUSIP_1_2 AS E7,

Basis_Receive_1_3 AS E8,

Basis_Amount_1_3 AS E9,

dbo.f_getmiscdescription('EC', c.Card_Number, Record_Date_Code) AS EC,

Equity_Debt AS ED,

dbo.f_getmiscdescription('EF', c.Card_Number, Effective_Date_Code) AS EF,

dbo.f_getmiscdescription('EX', c.Card_Number, Expiration_Date_Code) AS EX,

dbo.f_getmiscdescription('F1', c.Card_Number, Refer_Code_1) AS F1,

dbo.f_getmiscdescription('F2', c.Card_Number, Refer_Code_2) AS F2,

dbo.f_getmiscdescription('F3', c.Card_Number, Refer_Code_3) AS F3,

dbo.f_formatpseudodate(Effective_Date) AS FD,

File_Activity_Type AS FT,

dbo.f_getmiscdescription('G1', c.Card_Number, Rights_Ratio_1) AS G1,

dbo.f_getmiscdescription('G2', c.Card_Number, Rights_Ratio_2) AS G2,

dbo.f_getmiscdescription('G3', c.Card_Number, Rights_Ratio_3) AS G3,

RTRIM(LTRIM(Original_Card_Number)) AS GC,

dbo.f_getmiscdescription('GD', c.Card_Number, Ex_Rights_Date_Code) AS GD,

dbo.f_getmiscdescription('GT', c.Card_Number, Rights_To) AS GT,

Basis_Amount_2_3 AS H1,

Basis_CUSIP_2_3 AS H2,

Basis_Surrender_3 AS H3,

Basis_Receive_3_1 AS H4,

Basis_Amount_3_1 AS H5,

Basis_CUSIP_3_1 AS H6,

Basis_Receive_3_2 AS H7,

Basis_Amount_3_2 AS H8,

Basis_CUSIP_3_2 AS H9,

Information_Agent_1 AS I1,

Information_Agent_2 AS I2,

Interest_Rate AS [IN],

dbo.f_getmiscdescription('J1', c.Card_Number, Subscription_Ratio_Code_1) AS J1,

dbo.f_getmiscdescription('J2', c.Card_Number, Subscription_Ratio_Code_2) AS J2,

dbo.f_getmiscdescription('J3', c.Card_Number, Subscription_Ratio_Code_3) AS J3,

Basis_CUSIP_4_2 AS K1,

Basis_Receive_4_3 AS K2,

Basis_Amount_4_3 AS K3,

Basis_CUSIP_4_3 AS K4,

Basis_Receive_3_3 AS L1,

Basis_Amount_3_3 AS L2,

Basis_CUSIP_3_3 AS L3,

Basis_Surrender_4 AS L4,

Basis_Receive_4_1 AS L5,

Basis_Amount_4_1 AS L6,

Basis_CUSIP_4_1 AS L7,

Basis_Receive_4_2 AS L8,

Basis_Amount_4_2 AS L9,

dbo.f_formatpseudodate(Withdrawal_After) AS LA,

dbo.f_getmiscdescription('LC', c.Card_Number, Withdrawal_After_Code) AS LC,

dbo.f_getmiscdescription('LG', c.Card_Number, Eligibility) AS LG,

Withdrawal_After_Time AS LT,

dbo.f_getmiscdescription('LZ', c.Card_Number, Withdrawal_After_Zone) AS LZ,

Option_Expiration_Time AS M1,

Rights_Expire_Time AS M3,

Expiration_Date_Time AS M4,

dbo.f_formatpseudodate(Maturity_Date) AS MD,

Maximum_Eligibility AS ME,

dbo.f_validatecardnumber(Refer_Card_1) AS N1,

dbo.f_validatecardnumber(Refer_Card_2) AS N2,

dbo.f_validatecardnumber(Refer_Card_3) AS N3,

dbo.f_getmiscdescription('NT', c.Card_Number, Interest_Rate_Code) AS NT,

DTC_Match_Card_Number AS O3,

Offer_By AS OB,

Odd_Lot AS OD,

dbo.f_getmiscdescription('OV', c.Card_Number, OverSubscription) AS OV,

dbo.f_formatpseudodate(Option_Expiration_Date) AS OX,

(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 4) AS P1,

Information_Agent_Telephone_1 AS P2,

Information_Agent_Telephone_2 AS P3,

Dealer_Manager_Telephone_1 AS P4,

Dealer_Manager_Telephone_2 AS P5,

CUSIP_Number AS PN,

dbo.f_formatpseudodate(Pro_Ration_Date) AS PO,

dbo.f_getmiscdescription('PY', c.Card_Number, Payable_To) AS PY,

dbo.f_getmiscdescription('PZ', c.Card_Number, Protect_Period) AS PZ,

Price_1 AS R1,

Price_2 AS R2,

Price_3 AS R3,

Price_4 AS R4,

Price_5 AS R5,

Price_6 AS R6,

dbo.f_getmiscdescription('RB', c.Card_Number, Rights_Transferable) AS RB,

Rights_CUSIP AS RC,

Issuer_Description AS RD,

dbo.f_getmiscdescription('RI', c.Card_Number, Pro_Ration_Date_Code) AS RI,

Record_Type AS RT,

dbo.f_formatpseudodate(Rights_Expire_Date) AS RX,

CAST(Subscription_Ratio_1 AS varchar(12)) + Subscription_Ratio_1_Unit AS S1,

CAST(Subscription_Ratio_2 AS varchar(12)) + Subscription_Ratio_2_Unit AS S2,

CAST(Subscription_Ratio_3 AS varchar(12)) + Subscription_Ratio_3_Unit AS S3,

dbo.f_getmiscdescription('SC', c.Card_Number, Source_Name) AS SC,

Service_Type AS SE,

dbo.f_getmiscdescription('ST', c.Card_Number, Subscription_To) AS ST,

(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 1) AS T2,

(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 2) AS T3,

(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 3) AS T4,

(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 4) AS T5,

(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 1) AS T7,

(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 2) AS T8,

(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 3) AS T9,

Tickler_Date AS TK,

dbo.f_getmiscdescription('TP', c.Card_Number, To_Purchase) AS TP,

dbo.f_getmiscdescription('TS', c.Card_Number, Transaction_Status) AS TS,

dbo.f_gettxtext(c.Card_Number) AS TX,

Basis_CUSIP_1_3 AS U1,

Basis_Surrender_2 AS U2,

Basis_Receive_2_1 AS U3,

Basis_Amount_2_1 AS U4,

Basis_CUSIP_2_1 AS U5,

Basis_Receive_2_2 AS U6,

Basis_Amount_2_2 AS U7,

Basis_CUSIP_2_2 AS U8,

Basis_Receive_2_3 AS U9,

RTRIM(Issue_Description) AS UD,

Voluntary_Mandatory AS VM,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 1) AS W1,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 2) AS W2,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 3) AS W3,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 4) AS W4,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 5) AS W5,

(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 6) AS W6,

dbo.f_formatpseudodate(Withdrawal_Prior_To) AS WD,

Withdrawal_Prior_To_Time AS WH,

dbo.f_getmiscdescription('WO', c.Card_Number, Withdrawal_Prior_To_Code) AS WO,

dbo.f_getmiscdescription('WZ', c.Card_Number, Withdrawal_Prior_To_Zone) AS WZ,

(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =3) AS X1,

(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number = 3) AS X2,

dbo.f_formatpseudodate(Expiration_Date) AS XD,

dbo.f_formatpseudodate(Ex_Rights_Date) AS XR,

dbo.f_getmiscdescription('XT', c.Card_Number, Rights_Expire_Code) AS XT,

Payable_Rate_Shareblock AS Y1,

Payable_Rate_Surrender AS Y2,

(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y3,

(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y4,

(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y5,

(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y6,

(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y7,

(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y8,

(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =3) AS Y9,

dbo.f_formatpseudodate(Payment_Date) AS YD,

dbo.f_getmiscdescription('YR', c.Card_Number, Payable_Rate) AS YR,

dbo.f_getmiscdescription('Z1', c.Card_Number, Option_Expiration_Zone) AS Z1,

Rights_Expire_Zone AS Z3,

Expiration_Date_Zone AS Z4

FROM COMMON c

INNER JOIN DC_DailyCard dc ON c.Card_Number = dc.Card_Number

WHERE c.Card_Status = 'R' AND c.Card_Date BETWEEN @start_date AND @end_date

END

View 2 Replies View Related

Query Plan In SQL Server 2005 SP2

Aug 14, 2007

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 10 Replies View Related

SQL Server 2005 Error 8630: Internal Query Processor Error

Jan 22, 2007

I have a SProc that runs across many clients without any problems. Every now and then, though, I get the following error:

Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630).

All I am doing is populating Temp tables with some data and then joining them together to create a Global Temp table that is being BCP'd to a network share.

Has anyone come across this error in SQL Server 2005? I cannot find anything on Google or Microcsoft.

Thanks,
Robert

View 1 Replies View Related

Query To Produce A Report In Apex

Jan 24, 2014

I am trying to add to a query to produce a report in Apex. Currently I have this

SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,

[Code] ....

I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?

View 3 Replies View Related

T-SQL (SS2K8) :: Join Query Produce Duplicates?

Aug 12, 2014

join three tables and wont be duplicate records.

I have tried and attached the computed results and also expecting results.

IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3

[Code]....

View 4 Replies View Related

DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan

Oct 6, 2015

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?

View 3 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

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?

View 0 Replies View Related

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

I need a query to produce permutation combination.

declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')

declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),

View 3 Replies View Related

Date Query - Produce Unique Line By Month

Oct 21, 2013

Aim –To produce a unique line by month for the FDMSAccountNo

I have created the following query (see below) which produces the following result set

FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000

SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate

[Code] ....

Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08

[Code] ....

View 1 Replies View Related

Transact SQL :: SELECT Query To Produce UPDATE Command

Nov 18, 2015

How I can get the desired result using query. I don't want any

'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22

View 9 Replies View Related

Transact SQL :: Query Plan Shows Table Not Even In Query?

Jul 22, 2015

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.

View 10 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

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.

View 2 Replies View Related

Need To Produce A Query That Show The Cities And The Number Of People Per City?

Jan 31, 2008

Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location

location shows the dif cities and the sql query will show cities will a count of people per city

how do you do this?

View 3 Replies View Related

Sproc To Determine If Query Will Produce Invalid SqlNotificationEventArgs Event

Feb 7, 2007

I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.

I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...

exec sp_QueryInvalidForNotification "exec myTestSproc 99"

Does such a sproc exist? Or does anyone know how to write one?

View 1 Replies View Related

Internal Query Processor Error

Mar 31, 2008

Hi,I am trying to execute rather complex query, and I got query processorerror. I read the Microsoft support page and it said I should install SP4,and I did, but the error is still there.I am running MSDE with SP4 installed. Everything else works like a charm.Zvonko

View 1 Replies View Related

The Query Processor Ran Out Of Internal Resources

Oct 17, 2006

Hi,

I have MS SQL Express installed on my local machine and I have problems runnig some unit tests. Precisely, an exception is thrown as follows:

System.Data.SqlClient.SqlException : The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Nevertheless, everythin is ok on the server, where MS SQL Developer Edition is installed.

What can be the reason for such behavior?

View 1 Replies View Related

Internal Query Processor Error

Jan 15, 2008

Hi All,

I am hoping somebody can help with this. I have searched other posts and it seems like my problem should have been fixed with the hotfix but it wasn't. I am using SQL Express (SQL Server 9.0.3161) and have been using this database for quite some time and have never experienced this problem till yesterday. I am getting the error message "Internal Query Process Error: The query processor could not produce a query plan." This error appears when trying to add a new record to a table either when using a form through a MS Access front-end or when entering data directly into a table using MS SQL Server Managemnet Studio Express. It happens on different tables but not all tables. If I run a INSERT INTO query I can add data to the table. At the same time this has happened, when I run some of my stored procedures I get the same error.

This morning I installed the Cumulative Update Package (build3161) for SQL Server Service Pack 2, KB# 935356 and it didn't make any differance. I have attached the database to another SQL server (9.0.3042) and it did the same thing. This leads me to believe that the problem may be in the database itself. But I can't for the life of me figure out where to start as the database was working fine when used the day before and no changes had been made to the structure of it recently. Can anybody tell me what I should be looking for or where the problem might be?

Ernie

View 1 Replies View Related

Internal Query Processor Error

Nov 9, 2007

Ok, I'm getting an issue with SQL Server and I'm at a bit of a loss to understand why it's happening.

Situation: We have an existing database, and we're running a migration script over it to bring it up to a new version.

We get partway through the script when we get the error:
Internal Query Processor Error: The query processor ran out of stack space during query optimization.

I've searched around the web and it seems that people get this when they do silly things like have 300 Foreign Keys, or 17 pages of where clauses, or something else rediculous to hit the boundary conditions of SQL Server.

this is what i've gathered so far about our situation, using SQL Server 2000 SP4
1. I've recreated/changed a clustered index using the drop_existing clause
/****** Object: Index [IX_BM_SYNCHRONIZATION_1] Script Date: 08/28/2007 18:42:11 ******/
CREATE CLUSTERED INDEX [IX_BLAH ON [dbo].[TABLE_BLAH]
(
[VECTORID] ASC,
[TRANSID] ASC,
[ORDERID] ASC
)
WITH DROP_EXISTING
GO

2. later on the script, we attempt to delete from this table with a query that doesn't use this index eg
DELETE FROM TABLE_BLAH WHERE PRODUCT_NAME = 'blah'

and we get the error: Internal Query Processor Error: The query processor ran out of stack space during query optimization.

We're running this through osql.exe and not using any transactions

likewise, there is another section where it tries to insert into this table and it gets the same error.

Does anyone have an idea of what we could be running into here - the table only has 3 indexes on it, has only 18 columns (of which one is a 'text' column) and the particular datbase we're running this on is tiny.

Thanks,

View 1 Replies View Related

Random Internal Query Processor Error

Aug 24, 2007

Application folks experiencing recurrence of problem previously reported. This is causing delays with multiple projects. Errors below are occuring when attempting to run a DTS package that will refresh data from prod to pilot.

Exception Details: System.Data.SqlClient.SqlException: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Another example reads:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80004005): Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.)
Step Error code: 8004043B
Step Error Help Fileqldts80.hlp
Step Error Help Context ID:1100

View 1 Replies View Related

Sql Server Express 2005 9.0.2047 - Problem RESTORE VERIFYONLY FROM DISK

Mar 5, 2007

Hi all

every time I try to verify the backup file I get the error:

backup set on file '1' is not valid (translated from italian).

this is my backup script:

BACKUP DATABASE [ahr_sistema] TO DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FORMAT, INIT, NAME = N'ahr_sistema-Completo Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'ahr_sistema' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ahr_sistema' )

if @backupSetId is null begin raiserror(N'Verifica non riuscita. Impossibile trovare le informazioni di backup per il database ''ahr_sistema''.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

View 5 Replies View Related

Query Plan

Jul 23, 2002

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 1 Replies View Related

SQL 6.5 Query Execution Plan .

Sep 24, 2002

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 3 Replies View Related

Query Plan Re-use On Views?

Apr 25, 2006

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 2 Replies View Related

Strange Query Plan

Mar 19, 2008

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 2 Replies View Related

Saving Query Plan

Sep 13, 2005

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 Related

Odd Query Plan For View

Mar 28, 2006

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 4 Replies View Related

Puzzled By Query Plan

Jul 20, 2005

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 4 Replies View Related

Synonym And Query Plan

Oct 23, 2006

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!

View 5 Replies View Related

Incorrect Query Plan

Nov 16, 2007

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 5 Replies View Related