Query Plan SQL Server 2008

Apr 30, 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 2 Replies

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

SQL Server 2008 :: Query Plan Output List (IsBaseRow)

May 6, 2015

Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.

View 3 Replies View Related

SQL Server 2008 :: Query Execution Plan Of Stored Procedure

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 8 Replies View Related

SQL Server 2008 :: Left Joins And Query Plan Compile Times

Mar 8, 2015

We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.

So rather then the following exemplary fragment

select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id

Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.

I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.

View 9 Replies View Related

SQL Server 2008 :: White Spaces Not Getting Ignored For Plan Guide Matching?

May 1, 2015

In sp_create_plan_guide documentation, it's written:

When SQL Server matches the value of statement_text to batch_text and @parameter_name data_type [,...n ], or if @type = 'OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.
Comments (-- or /* */).
Trailing semicolons

On SQL Server 2008 SP3, I created a plan guide for a query. Now, if I execute the query exactly how it was defined in the plan guide, SQL Server match it and use the plan guide to optimize the query.

However, if I add just a space between a column name and an operator in the WHERE clause, the plan guide is ignored. How come it doesn't ignore the extra space, like mentioned in the documentation?

View 3 Replies View Related

SQL Server 2008 :: Newly Created Index And Execution Plan

Jun 17, 2015

I run a query

select col1, col2, col3, col4
from Table
where col2=5
order by col1

I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan

The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?

View 7 Replies View Related

SQL Server 2008 :: Graphical (Include Execution Plan) Left On By Default?

Jun 25, 2015

Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where

View 2 Replies View Related

SQL Server 2008 :: Execution Plan Ignoring Index Insisting On Key Lookup

Sep 15, 2015

I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.

I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.

View 3 Replies View Related

SQL Server 2008 :: Changed Database From Full To Simple - Maintenance Plan Failing

Mar 6, 2015

One of my programmers changed their database from full to Simple recovery. Saw that my job that backs up the Full Recovery mode databases failed, so I moved that database to my Simple database backup job plan and removed it from the Full Recovery job. I am unable to remove the db from the Transaction Log task on the Full Plan because when I try to edit that job "Databases with Simple Recovery will be excluded"

My transaction log backups are still failing with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Just want to remove that database so my Full Recovery backup job does not try to back it up.

View 2 Replies View Related

SQL Server 2008 :: Failed Task In Maintenance Plan Not Being Reported By Database Mail

Apr 29, 2015

I have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.

I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.

The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.

View 1 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 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 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 2012 :: How To Force Server To Generate A New Query Plan

Oct 30, 2015

Select A.* from A inner join B on ( A.ID= B.ID )

I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?

View 7 Replies View Related

SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should

Mar 29, 2015

I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:

- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.

To explain wat I'm doing is this:

1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:

SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:

SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'

Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.

View 9 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

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

SQL Server 2008 :: Display One Row For The Query?

Jan 29, 2015

For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.

I am using the below code get to the above logic:

ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum

select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]

[code]....

where rownum in (1,2)

View 6 Replies View Related

SQL Server 2008 :: XML Query Parsing

Mar 9, 2015

I want to take this XML and put it into a table with CustomerId and MatchingSetId. With this SQL, each MatchingSetId gets assigned to each CustomerId instead of retaining the relationships in the XML.

declare @myXML XML = '<CustomerMatchings>
<CustomerRecord CustomerId="10600">
<MatchingSetId>11</MatchingSetId>
<MatchingSetId>13</MatchingSetId>
<MatchingSetId>18</MatchingSetId>
<MatchingSetId>23</MatchingSetId>

[code]....

View 3 Replies View Related

SQL Server 2008 :: Query The Same Field Name

Oct 20, 2015

3 table join query

Problem : city name the same

View 6 Replies View Related

SQL Server 2008 :: Query Within A Query?

Mar 5, 2015

I have a query:

SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM dbo.tblGP_Practices AS gp INNER JOIN

[Code] ....

I wanted to add anohter column where it counts the results of the followign query:

SELECT dbo.tblPatient.NHSnumber, dbo.tblPatient.DateOfBirth, dbo.tblPatient.DateReferralReceived, dbo.tblGP_Practices.OrganisationCode
FROM dbo.tblPatient INNER JOIN
dbo.tblGP_PatientLink ON dbo.tblPatient.PatientID = dbo.tblGP_PatientLink.PatientID INNER JOIN
dbo.tblGP_Practices ON dbo.tblGP_PatientLink.GPPracticeID = dbo.tblGP_Practices.GPPracticeID LEFT OUTER JOIN
dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID
WHERE (dbo.tblAppointments.PatientID IS NULL)

is this possible?

View 4 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 :: Linked Server Tests Fine But Query Does Not Work

Apr 16, 2015

Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.

Here is my linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null

Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.

SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')

OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2

An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".

View 0 Replies View Related

SQL Server 2008 :: Error Loading A Query Into DTA

Nov 12, 2012

I am trying to look into a query thats taking 5 mins to execute on our production. Its an LinQ generated 6000 Line SQL query that the dev team is trying to put into production without any testing. They want me to add indexes to the database( brand new) so that the query will run fast. The execution plan really looks weird. So Just as a starter I tried to tune it using the DTA and I am getting the below error. I am using a .sql query file as the input.

The minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least 441 MB.Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <Tuning Options>

My question is what value is the maximum value that I should consider adding per recommendation (2) in the above error.

View 1 Replies View Related

SQL Server 2008 :: FEFO Query If Condition

Jan 28, 2015

I wish to make a query with if condition Implemented in a database sql server 2008 R2.

I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:

if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request).
else if amount of movement (QTEMVT) <amount entered by the user
then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions:
the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0
by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.

Basically I want to set up an item management query based on batch number and expiration dates.

ps: QTEMVT = quantity of the item stored in my database
NUMLOT = batch number items
DATFABRIC = manufacturing date items
DATPEREMP = expiry date items

Here is my request:

SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT
FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Query Pulling Too Many Records

Feb 11, 2015

My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.

Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Query A Column Of XML Files?

Feb 12, 2015

I have a table with lots of xml files in one column(more than 1000), like this

1. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
2. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
3. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...each is big

I want to query some values for all to see the duration but now I can only query one of them

declare @bp xml
select @bp=xml
from bloodpressureohneschema
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from(
select
m.c.value('(./bp:time/bp:value)[1]','date') as time,
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as value
from @bp.nodes('/bp:content/bp:data/bp:events') as m(c)
)m

is there somewhere I can make better?

View 5 Replies View Related

SQL Server 2008 :: Query Grouping By Month

Mar 5, 2015

I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these

MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010

SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],

[Code] ....

View 2 Replies View Related

SQL Server 2008 :: Update Table Query

Mar 11, 2015

I have run into a perplexing issue with how to UPDATE some rows in my table correctly.I have a Appointment table which has Appointment Times and a Appointment Names. However the Name is only showing on the Appt start Time line. I need it to show for its duration. So for example in my DDL Morning Appt only shows on at 8:00 I need it to show one each line until the next Appt Starts and so on. In this case Morning Appt should show at 8:00,8:15, 8:30.

CREATE TABLE #TEST ( ApptTime TIME, ApptName VARCHAR (20), DURATION TINYINT)
INSERT INTO #TEST VALUES ('8:00', 'Morning Appt', 45), ('8:15', NULL, NULL),('8:30', NULL,NULL),('8:45', 'Brunch Appt', 45),('9:00', NULL,NULL),('9:15', NULL,NULL),
('9:30', 'Afternoon Appt', 30),('9:45', NULL,NULL),('10:00', NULL,NULL).

View 3 Replies View Related

SQL Server 2008 :: Remove Duplicates From Query?

Oct 6, 2015

I am working with a bunch of records that have duplicates on the Persid and the intPercentID where there are duplicates I want to remove when I stick them in the temp table, I tried join on tempo table and doing not exists but still inserts, so now I am trying a merge but same thing. how can I keep duplicates from being inserted in the temp table. I made a cursor as well but its slow as heck, but it does work. trying better ways.

Create table #TempStr (STRId int not null Identity(1,1) primary key, Persid int, percentId int, dtCreated datetime, CreatedBy int)

Create table #NewStr (STRId int, Persid int, percentId int, dtCreated datetime, CreatedBy int)

INSERT #TempStr (Persid, percentId, dtCreated, CreatedBy)
select intPersonnelID, intPercentID, dtSubmitted, intSubmittedBy from tblSTR
whereintpercentId in (61,62) group by intPercentID, intPersonnelID, dtSubmitted, intSubmittedBy
UNION ALL

[code]....

View 3 Replies View Related

SQL Server 2008 :: Scripting A Query That Needs All Values?

Oct 26, 2015

This is the database structure I setup:

--Main Table

CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)

--Mapping Table

CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED

[Code] ....

This a result set of the Items Map table so far:

TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475

Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?

View 6 Replies View Related