October 30, 2005 at 6:52 am
I needed to run the qeruy below against a production server via query analyzer. Being the conscientious sort, I first ran it through the graphical showplan to determine the approximate cost before doing so. The cost came out at 0.042, and used index seeks almost exclusively. This seemed ideal, so I executed the command:
select top 20
accountnumber,cli,crt.description,fve.description,bbprs.error
from bbprs
inner join bbpr on bbprs.bbprovisionrequestid = bbpr.bbprovisionrequestid
inner join crt on bbpr.requesttypeid = crt.requesttypeid
inner join fve on fve.code = cast(bbprs.responseid as varchar(10)) and fve.carrierid = 1000020
inner join cc on cc.clicarrierid = bbpr.clicarrierid
inner join c on cc.cliid = c.cli_id
inner join a on c.accountid = a.accountid
order by bbpr.bbprovisionrequestid desc
The various table sizes are:
a: 1,651,367
c: 1,730,160
cc: 2,465,781
bbpr: 289,423
bbprs: 937,088
fve: 1,890
When I actually ran it, it continued running for several minutes. I checked for blocks - there were none. I killed the process and tried again - same result.
I then had a thought, and removed the TOP 20 clause in the SELECT statement. I re-checked the query plan, which had now changed completely. Instead of using index seeks everywhere, it was showing clustered index scans, and the final subtree cost had increased from 0.042 to 242.
With TOP 20
StmtText
|--Top(20)
|--Bookmark Lookup(BOOKMARK: ([Bmk1003]), OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T] AS [fve]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([bbprs].[ResponseID]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([c].[AccountID]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([cc].[CLIID]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[CLICarrierID]) WITH PREFETCH)
| | | |--Bookmark Lookup(BOOKMARK: ([Bmk1000]),
OBJECT: ([Reach_roms].[dbo].[BBProvisionResponse_T] AS [bbprs]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[BBProvisionRequestID])
WITH PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[RequestTypeID]))
| | | | | |--Clustered Index Scan
(OBJECT: ([Reach_roms].[dbo].[BBProvisionRequest_T].[PK_NEW_BBProvisionRequest]
AS [bbpr]), ORDERED BACKWARD)
| | | | | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CarrierRequestTypes_T].[PK_D_CPSRequestTypes_T] AS [crt]), SEEK: ([crt].[RequestTypeID]=[bbpr].[RequestTypeID]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT: ([Reach_roms].[dbo].[BBProvisionResponse_T].[IX_NCL_BBProvisionRequestID]
AS [bbprs]), SEEK: ([bbprs].[BBProvisionRequestID]=[bbpr].[BBProvisionRequestID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CLICarrier_T].[PK_CLICarrier_T] AS [cc]), SEEK: ([cc].[CLICarrierID]=[bbpr].[CLICarrierID]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CLI_T].[PK_CLI] AS [c]), SEEK: ([c].[CLI_ID]=[cc].[CLIID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[Account_T].[PK_ROMS_Account] AS [a]), SEEK: ([a].[AccountID]=[c].[AccountID]) ORDERED FORWARD)
|--Index Seek(OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T].[IX_FileValidationErrorCodes_T] AS [fve]), SEEK: ([fve].[Code]=Convert([bbprs].[ResponseID]) AND [fve].[CarrierID]=1000020) ORDERED FORWARD)
Without TOP 20
StmtText
|--Bookmark Lookup(BOOKMARK: ([Bmk1003]), OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T] AS [fve]))
|--Sort(ORDER BY: ([bbprs].[BBProvisionRequestID] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([bbprs].[ResponseID]) WITH PREFETCH)
|--Hash Match(Inner Join, HASH: ([bbpr].[BBProvisionRequestID])=([bbprs].[BBProvisionRequestID]))
| |--Hash Match(Inner Join, HASH: ([crt].[RequestTypeID])=([bbpr].[RequestTypeID]))
| | |--Clustered Index Scan(OBJECT: ([Reach_roms].[dbo].[CarrierRequestTypes_T].[PK_D_CPSRequestTypes_T] AS [crt]))
| | |--Hash Match(Inner Join, HASH: ([c].[AccountID])=([a].[AccountID]))
| | |--Hash Match(Inner Join, HASH: ([cc].[CLIID])=([c].[CLI_ID]))
| | | |--Hash Match(Inner Join, HASH: ([bbpr].[CLICarrierID])=([cc].[CLICarrierID]))
| | | | |--Index Scan(OBJECT: ([Reach_roms].[dbo].[BBProvisionRequest_T].[IX_NCI_statusid_covered] AS [bbpr]))
| | | | |--Index Scan(OBJECT: ([Reach_roms].[dbo].[CLICarrier_T].[IX_NCL_CLI_Index] AS [cc]))
| | | |--Index Scan(OBJECT: ([Reach_roms].[dbo].[CLI_T].[IXNCL_CLI_T_AcctIDCLI] AS [c]))
| | |--Index Scan(OBJECT: ([Reach_roms].[dbo].[Account_T].[IX_ROMS_Account] AS [a]))
| |--Clustered Index Scan(OBJECT: ([Reach_roms].[dbo].[BBProvisionResponse_T].[IX_CL_DateCreated] AS [bbprs]))
|--Index Seek(OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T].[IX_FileValidationErrorCodes_T] AS [fve]), SEEK: ([fve].[Code]=Convert([bbprs].[ResponseID]) AND [fve].[CarrierID]=1000020) ORDERED FORWARD)
The statistics IO output seems to confirm that the optimizer is indeed using these index seeks when trying to select TOP 20, and that this is the cause of the inefficiency. Just look at the scan count and logical reads required when specifying TOP 20 - it clearly is using lots of bookmark lookups to visit the main tables.
With TOP 20
Table 'fve'. Scan count 868938, logical reads 1792092, physical reads 0, read-ahead reads 0.
Table 'a'. Scan count 937070, logical reads 3493363, physical reads 10567, read-ahead reads 25192.
Table 'c'. Scan count 937070, logical reads 3260033, physical reads 8445, read-ahead reads 14520.
Table 'cc'. Scan count 937070, logical reads 3046175, physical reads 4228, read-ahead reads 5991.
Table 'bbprs'. Scan count 289423, logical reads 4002106, physical reads 1958, read-ahead reads 4371.
Table 'crt'. Scan count 289423, logical reads 578846, physical reads 0, read-ahead reads 0.
Table 'bbpr'. Scan count 1, logical reads 7392, physical reads 2, read-ahead reads 7409.
Without TOP 20
Table 'fve'. Scan count 868938, logical reads 1792229, physical reads 1, read-ahead reads 2.
Table 'bbprs'. Scan count 1, logical reads 14503, physical reads 2, read-ahead reads 14546.
Table 'a'. Scan count 1, logical reads 4051, physical reads 2, read-ahead reads 4061.
Table 'c'. Scan count 1, logical reads 6662, physical reads 2, read-ahead reads 6692.
Table 'cc'. Scan count 1, logical reads 4119, physical reads 2, read-ahead reads 4129.
Table 'bbpr'. Scan count 1, logical reads 947, physical reads 2, read-ahead reads 947.
Table 'crt'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Not surprisingly, the query without the TOP 20, index scans and all, completes in a fraction of the time that the TOP 20 query requires.
My question is, why doesn't the optimizer recognize that all the data will still need to be merged and sorted before displaying the top 20 records? In other words, the underlying cost will still be virtually the same, whether the TOP 20 is specified or not, so how does the optimizer get it so badly wrong and choose a totally inappropriate INDEX SEEK strategy, when index scans are so much more efficient in this case? Furthermore, it reports the cost of this strategy as very low - 0.042 - when in reality the cost is enormous, as proved by the statistics IO results.
October 30, 2005 at 7:23 am
Please post the output from these statements:
What is the version of SQL Server ?
exec master.dbo.xp_msver 'ProductVersion'
When were statistics last updated ?
SELECT o.name as TableName
, i.name as IndexName
, STATS_DATE(i.id, i.indid) as StatsDate
FROM sysobjects o
join sysindexes i
on o.id = i.id
WHERE o.name in ('bbprs', 'bbpr' , 'crt' , 'fve','cc', 'c' , 'a')
SQL = Scarcely Qualifies as a Language
October 30, 2005 at 8:29 am
Version is:
Index, Name, Internal_Value, Character_Value
2, ProductVersion, 524288, 8.00.760
For what it's worth, I've included the stats output too. I've ordered the stats output by date. The columns with the least-recently updated statistics are static data tables, so I'm not worried about that. We have autoupdate statistics set on all tables in our database. However, the key thing for me is that the showplan changes so markedly by the simple inclusion of a TOP 20 statement. If the stats were bad, I would expect the showplan to be bad for both forms of the query.
TableName | IndexName | StatsUpdated |
crt | PK_D_RequestTypes_T | 2005-05-01 13:37:15.220 |
crt | IX_CRequestTypes_CodeCarr | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_CarrID_5B795DF5 | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_DateArchived_5B795DF5 | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_CodeAlt_5B795DF5 | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_Welcome_5B795DF5 | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_Description_5B795DF5 | 2005-05-01 13:37:15.233 |
crt | _WA_Sys_CreatedBy_5B795DF5 | 2005-05-01 13:37:15.267 |
crt | _WA_Sys_DateCreated_5B795DF5 | 2005-05-01 13:37:15.267 |
fve | PK_FileValidationErrorCodes_T | 2005-05-01 13:43:53.813 |
fve | IX_FileValidationErrorCodes_T | 2005-05-01 13:43:53.830 |
fve | _WA_Sys_Description_0D10B989 | 2005-05-01 13:43:53.843 |
fve | _WA_Sys_CarrierID_0D10B989 | 2005-05-01 13:43:53.843 |
fve | _WA_Sys_FileTypeID_0D10B989 | 2005-05-01 13:43:53.860 |
fve | _WA_Sys_DateArchived_0D10B989 | 2005-05-01 13:43:53.860 |
fve | _WA_Sys_CodeAlt_0D10B989 | 2005-05-01 13:43:53.877 |
fve | _WA_Sys_FinalStatus_0D10B989 | 2005-05-01 13:43:53.877 |
fve | _WA_Sys_HowToAvoid_0D10B989 | 2005-05-01 13:43:53.890 |
fve | _WA_Sys_Reason_0D10B989 | 2005-05-01 13:43:53.890 |
fve | _WA_Sys_Resolution_0D10B989 | 2005-05-01 13:43:53.907 |
bbpr | _WA_Sys_OrderReference_58B39691 | 2005-07-07 23:27:42.053 |
bbpr | _WA_Sys_UpdatedBy_58B39691 | 2005-07-07 23:27:42.177 |
bbpr | _WA_Sys_DateUpdated_58B39691 | 2005-07-07 23:27:42.240 |
bbprs | _WA_Sys_Error_6DAEB377 | 2005-07-07 23:28:08.460 |
bbprs | _WA_Sys_OrderAction_6DAEB377 | 2005-07-07 23:28:08.630 |
fve | _WA_Sys_DateCreated_0D10B989 | 2005-07-08 16:59:16.330 |
bbpr | _WA_Sys_CreatedBy_58B39691 | 2005-08-01 10:25:22.740 |
bbprs | _WA_Sys_PRequestID_6DAEB377 | 2005-09-08 10:52:08.300 |
bbprs | _WA_Sys_ResponseID_6DAEB377 | 2005-09-08 10:52:08.410 |
bbprs | IX_NCL_PRequestID | 2005-09-08 10:52:08.470 |
bbprs | IX_CL_DateCreated | 2005-09-08 11:13:06.543 |
bbprs | _WA_Sys_DateCreated_6DAEB377 | 2005-09-08 11:13:06.603 |
bbprs | _WA_Sys_PResponseID_6DAEB377 | 2005-09-08 11:16:20.860 |
bbprs | PK_PResponse | 2005-09-08 11:16:20.953 |
bbprs | _WA_Sys_LastModifiedDate_6DAEB377 | 2005-09-09 05:35:14.680 |
bbprs | _WA_Sys_CustomerExpectedDate_6DAEB377 | 2005-09-21 05:35:26.463 |
bbprs | _WA_Sys_ISPExpectedDate_6DAEB377 | 2005-09-21 05:35:26.587 |
bbprs | _WA_Sys_CreatedBy_6DAEB377 | 2005-09-22 09:31:22.620 |
bbpr | _WA_Sys_CID_58B39691 | 2005-10-12 16:03:37.973 |
bbpr | IX_NCI_statusid_covered | 2005-10-12 16:03:38.397 |
bbpr | _WA_Sys_StatusID_58B39691 | 2005-10-12 16:04:54.757 |
bbpr | _WA_Sys_ProductID_58B39691 | 2005-10-12 16:04:54.820 |
bbpr | _WA_Sys_RequestTypeID_58B39691 | 2005-10-12 16:17:05.940 |
bbpr | PK_NEW_PRequest | 2005-10-12 16:20:20.930 |
bbpr | _WA_Sys_Username_58B39691 | 2005-10-12 16:20:21.040 |
bbpr | _WA_Sys_ModifyOrder_58B39691 | 2005-10-12 16:44:43.723 |
bbpr | _WA_Sys_DateArchived_58B39691 | 2005-10-12 16:50:42.340 |
bbpr | _WA_Sys_UpgradeOrder_58B39691 | 2005-10-13 11:30:18.630 |
bbpr | _WA_Sys_ServiceAddressID_58B39691 | 2005-10-13 12:37:40.207 |
bbpr | _WA_Sys_ServiceContactID_58B39691 | 2005-10-13 14:02:11.570 |
bbpr | _WA_Sys_OnHoldUntil_58B39691 | 2005-10-13 14:02:16.620 |
bbpr | _WA_Sys_BillingAddressID_58B39691 | 2005-10-13 14:29:32.760 |
bbpr | _WA_Sys_PersonalAddressID_58B39691 | 2005-10-13 14:29:32.820 |
bbpr | _WA_Sys_StopDate_58B39691 | 2005-10-18 19:14:48.027 |
bbpr | _WA_Sys_DateCreated_58B39691 | 2005-10-20 00:16:14.310 |
c | PK_CLI | 2005-10-20 09:32:01.440 |
c | _WA_Sys_AccountID_2AF6222B | 2005-10-20 09:32:01.707 |
c | IXNCL_C_T_AcctIDCLI | 2005-10-20 09:32:01.893 |
c | IX_NCL_AccountID_DateArchived | 2005-10-20 09:32:02.033 |
c | CLI_T23 | 2005-10-20 09:32:02.207 |
c | IX_ROMS_C | 2005-10-20 09:35:25.973 |
c | _WA_Sys_StatusID_2AF6222B | 2005-10-20 09:35:43.100 |
c | _WA_Sys_AddressID_2AF6222B | 2005-10-20 10:18:29.837 |
c | _WA_Sys_LTypeID_2AF6222B | 2005-10-20 10:18:29.993 |
c | IX_NCL_AddressID | 2005-10-20 10:18:30.120 |
c | _WA_Sys_DateArchived_2AF6222B | 2005-10-20 10:19:48.510 |
c | _WA_Sys_TrafficID_2AF6222B | 2005-10-20 11:15:26.980 |
c | _WA_Sys_UseID_2AF6222B | 2005-10-20 11:15:27.403 |
c | _WA_Sys_LTransactionTypeID_2AF6222B | 2005-10-20 11:15:27.857 |
c | _WA_Sys_StartDate_2AF6222B | 2005-10-21 01:13:58.457 |
c | _WA_Sys_EndDate_2AF6222B | 2005-10-21 01:13:58.567 |
cc | _WA_Sys_DateCreated_42A2C333 | 2005-10-21 05:18:44.190 |
cc | _WA_Sys_DateUpdated_42A2C333 | 2005-10-21 05:18:44.363 |
cc | PK_C_T | 2005-10-21 10:13:29.183 |
cc | _WA_Sys_CID_42A2C333 | 2005-10-21 10:13:29.403 |
cc | IX_NCL_C_Index | 2005-10-21 10:13:30.060 |
cc | _WA_Sys_CTypeID_42A2C333 | 2005-10-21 10:13:30.370 |
cc | _WA_Sys_CurrentState_42A2C333 | 2005-10-21 10:13:30.620 |
cc | _WA_Sys_CID_42A2C333 | 2005-10-21 10:13:38.920 |
cc | _WA_Sys_DateArchived_42A2C333 | 2005-10-21 10:13:47.840 |
cc | IX_NCL_Covered | 2005-10-22 22:06:51.353 |
c | _WA_Sys_DateCreated_2AF6222B | 2005-10-24 12:13:43.173 |
c | _WA_Sys_DateOfFirstC_2AF6222B | 2005-10-25 05:14:40.727 |
c | _WA_Sys_DateOfLastC_2AF6222B | 2005-10-25 05:14:40.870 |
c | _WA_Sys_DateUpdated_2AF6222B | 2005-10-25 05:14:41.120 |
a | PK_ROMS_Account | 2005-10-25 18:24:01.690 |
a | _WA_Sys_BillAddressID_44B5F42E | 2005-10-25 18:24:13.267 |
a | IX_NCL_BillAddressID | 2005-10-25 18:24:13.360 |
a | _WA_Sys_CustomerID_44B5F42E | 2005-10-25 18:24:37.970 |
a | IX_NCL_CustomerID | 2005-10-25 18:24:38.097 |
a | IX_ROMS_Account | 2005-10-25 18:25:31.487 |
a | _WA_Sys_CustomerStatusID_44B5F42E | 2005-10-25 18:25:31.690 |
a | IX_NCI_Covered | 2005-10-25 18:25:31.970 |
a | _WA_Sys_BProviderID_44B5F42E | 2005-10-25 18:28:59.773 |
a | _WA_Sys_Title_44B5F42E | 2005-10-25 18:31:17.527 |
a | _WA_Sys_ForeName_44B5F42E | 2005-10-25 18:31:17.633 |
a | _WA_Sys_SurName_44B5F42E | 2005-10-25 18:31:17.743 |
a | _WA_Sys_TariffID_44B5F42E | 2005-10-25 18:35:18.623 |
a | _WA_Sys_AccountTypeID_44B5F42E | 2005-10-25 18:35:18.763 |
a | IX_AccountTypeID | 2005-10-25 18:35:18.983 |
a | _WA_Sys_BContactID_44B5F42E | 2005-10-25 18:35:43.920 |
a | _WA_Sys_NextBD_44B5F42E | 2005-10-25 18:39:54.973 |
a | _WA_Sys_BTID_44B5F42E | 2005-10-25 18:42:59.553 |
a | _WA_Sys_GStartDate_44B5F42E | 2005-10-25 19:11:03.353 |
a | _WA_Sys_BDateSet_44B5F42E | 2005-10-25 19:11:03.510 |
a | _WA_Sys_RefundStatusId_44B5F42E | 2005-10-25 19:12:25.150 |
a | _WA_Sys_PaymentTypeID_44B5F42E | 2005-10-25 22:13:11.230 |
a | _WA_Sys_BHandlingCodeID_44B5F42E | 2005-10-25 22:13:11.323 |
a | _WA_Sys_ThresholdItemisationID_44B5F42E | 2005-10-25 22:13:11.430 |
a | _WA_Sys_VatInvoice_44B5F42E | 2005-10-25 22:13:11.587 |
a | _WA_Sys_PromoId_44B5F42E | 2005-10-26 02:30:03.900 |
a | _WA_Sys_BFrequencyID_44B5F42E | 2005-10-26 04:17:52.070 |
cc | _WA_Sys_OptionID_42A2C333 | 2005-10-26 04:21:56.247 |
bbpr | _WA_Sys_Password_58B39691 | 2005-10-26 06:05:01.420 |
a | _WA_Sys_UserUpdated_44B5F42E | 2005-10-26 16:29:14.050 |
a | _WA_Sys_DCUpdated_44B5F42E | 2005-10-26 23:05:11.123 |
a | _WA_Sys_DateCreated_44B5F42E | 2005-10-27 03:15:33.723 |
a | _WA_Sys_FileID_44B5F42E | 2005-10-27 03:19:42.120 |
a | _WA_Sys_BusinessName_44B5F42E | 2005-10-27 05:10:08.153 |
a | _WA_Sys_DateOfBirth_44B5F42E | 2005-10-27 05:10:08.293 |
a | _WA_Sys_CreditCheckDate_44B5F42E | 2005-10-27 05:10:08.433 |
a | _WA_Sys_BBusinessName_44B5F42E | 2005-10-27 05:10:08.607 |
a | _WA_Sys_BCntctSurname_44B5F42E | 2005-10-27 05:10:08.730 |
a | _WA_Sys_BankHolder_44B5F42E | 2005-10-27 05:10:08.870 |
a | _WA_Sys_EUpdated_44B5F42E | 2005-10-27 05:10:08.980 |
a | _WA_Sys_FUpdated_44B5F42E | 2005-10-27 05:10:09.090 |
a | _WA_Sys_DateUpdated_44B5F42E | 2005-10-27 05:10:09.200 |
a | _WA_Sys_DateArchived_44B5F42E | 2005-10-27 05:10:09.327 |
a | _WA_Sys_WelcomeSent_44B5F42E | 2005-10-27 05:10:09.433 |
a | _WA_Sys_RecommendedBy_44B5F42E | 2005-10-27 07:30:03.783 |
a | _WA_Sys_CreditLimit_44B5F42E | 2005-10-27 11:24:50.903 |
bbpr | _WA_Sys_MAC_58B39691 | 2005-10-27 12:36:48.423 |
bbpr | _WA_Sys_TargetDate_58B39691 | 2005-10-27 12:57:37.103 |
bbpr | _WA_Sys_SentDate_58B39691 | 2005-10-27 13:33:34.597 |
a | _WA_Sys_ESV_44B5F42E | 2005-10-27 14:48:54.403 |
a | _WA_Sys_CCheckReq_44B5F42E | 2005-10-27 14:48:54.590 |
a | _WA_Sys_CCheckRes_44B5F42E | 2005-10-27 14:48:54.717 |
a | _WA_Sys_CPassRef_44B5F42E | 2005-10-27 14:48:54.857 |
a | _WA_Sys_CScore_44B5F42E | 2005-10-27 14:48:54.967 |
a | _WA_Sys_CRisk_44B5F42E | 2005-10-27 14:48:55.090 |
a | _WA_Sys_CCheckDone_44B5F42E | 2005-10-27 14:48:55.217 |
a | _WA_Sys_BCntctTitle_44B5F42E | 2005-10-27 14:48:55.357 |
a | _WA_Sys_BCntctForename_44B5F42E | 2005-10-27 14:48:55.497 |
a | _WA_Sys_ContactNumber_44B5F42E | 2005-10-27 14:48:55.653 |
a | _WA_Sys_BankName_44B5F42E | 2005-10-27 14:48:55.793 |
a | _WA_Sys_BankAccount_44B5F42E | 2005-10-27 14:48:55.933 |
a | _WA_Sys_BankSortcode_44B5F42E | 2005-10-27 14:48:56.073 |
a | _WA_Sys_BankAddress1_44B5F42E | 2005-10-27 14:48:56.217 |
a | _WA_Sys_BankAddress2_44B5F42E | 2005-10-27 14:49:14.013 |
a | _WA_Sys_BankAddress3_44B5F42E | 2005-10-27 14:49:14.153 |
a | _WA_Sys_BankAddress4_44B5F42E | 2005-10-27 14:49:14.280 |
a | _WA_Sys_CCID_44B5F42E | 2005-10-27 14:49:14.403 |
a | _WA_Sys_CCHolder_44B5F42E | 2005-10-27 14:49:14.530 |
a | _WA_Sys_CCAcNo_44B5F42E | 2005-10-27 14:49:14.683 |
a | _WA_Sys_CCExpDate_44B5F42E | 2005-10-27 14:49:14.823 |
a | _WA_Sys_CCStartDate_44B5F42E | 2005-10-27 14:49:14.980 |
a | _WA_Sys_CCIssueNumber_44B5F42E | 2005-10-27 14:49:15.137 |
a | _WA_Sys_CompanyNumber_44B5F42E | 2005-10-27 14:49:15.280 |
a | _WA_Sys_VATNumber_44B5F42E | 2005-10-27 14:49:15.467 |
a | _WA_Sys_EAccountName_44B5F42E | 2005-10-27 14:49:15.637 |
a | _WA_Sys_UserCreated_44B5F42E | 2005-10-27 14:49:15.763 |
a | _WA_Sys_RID_44B5F42E | 2005-10-27 14:49:15.933 |
a | _WA_Sys_CurrentCustomerBalance_44B5F42E | 2005-10-27 14:49:16.043 |
a | _WA_Sys_MarketResearch_44B5F42E | 2005-10-27 14:49:16.153 |
a | _WA_Sys_CCASlipRec_44B5F42E | 2005-10-27 14:49:16.280 |
cc | _WA_Sys_CreatedBy_42A2C333 | 2005-10-27 14:49:23.700 |
cc | _WA_Sys_UpdatedBy_42A2C333 | 2005-10-27 14:49:23.903 |
c | _WA_Sys_NumOfLinPerC_2AF6222B | 2005-10-27 14:49:24.340 |
c | _WA_Sys_CurrentAccessCode_2AF6222B | 2005-10-27 14:49:24.497 |
c | _WA_Sys_Number_2AF6222B | 2005-10-27 14:49:24.670 |
c | _WA_Sys_BNumber_2AF6222B | 2005-10-27 14:49:24.840 |
c | _WA_Sys_FaceValueUpdated_2AF6222B | 2005-10-27 14:49:24.967 |
c | _WA_Sys_BillingLabel_2AF6222B | 2005-10-27 14:49:25.107 |
c | _WA_Sys_CreatedBy_2AF6222B | 2005-10-27 14:49:25.233 |
c | _WA_Sys_UpdatedBy_2AF6222B | 2005-10-27 14:49:25.403 |
c | _WA_Sys_RetentionStatusID_2AF6222B | 2005-10-27 14:49:25.513 |
October 30, 2005 at 1:01 pm
Looks like a SQL Server bug to me. As your version is Service Pack 3 without any patchs, recommend you try applying Service Pack 4.
You can find a list of fixes at http://www.aspfaq.com/SQL2000Builds.asp
Here are some of the Query plan bugs fixed by Service Pack 4:
FIX: SQL Server underestimates the cardinality of a query expression and query performance may be slow. Caused by Service Pack 3
http://support.microsoft.com/kb/831302
FIX: A Large Number of NULL Values in Join Columns Result in Slow Query Performance
http://support.microsoft.com/kb/813759
FIX: SQL Server Optimizer May Underestimate the Cardinality of Range Queries
http://support.microsoft.com/kb/816883
FIX: Some complex queries are slower after you install SQL Server 2000 Service Pack 2 or SQL Server 2000 Service Pack 3
http://support.microsoft.com/kb/890942
FIX: SQL Server 2000 may underestimate the cardinality of a query expression under certain circumstances
http://support.microsoft.com/kb/841627
SQL = Scarcely Qualifies as a Language
November 1, 2005 at 7:15 am
Still see the same behaviour on SP4
November 1, 2005 at 8:20 am
Just to reinforce the point, BOL states the following:
"If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned."
This seems further evidence to me that the optimizer should be evaluating the query as a whole, and that the presence or absence of the TOP n clause is irrelevent as far as the total cost is concerned.
Does anyone disagree? In other words, am I completely mis-interpreting how TOP is supposed to work, or is this a potentially big problem (our application has many complex queries that use TOP n)
My next plan is to try this on SQL2005 to see if anything has changed there.
November 1, 2005 at 5:21 pm
"The entire result set is built in the specified order and the top n rows in the ordered result set are returned.
Does anyone disagree?"
Well possibly but I need to run a test case for when there is an index that has leading columns that are the same as the sort order. Then, the query plan could be to get the first row, do any remain table accesses and if the row qualifies, that that is one of the "top".
Can someone run this SQL versus Northwind:
select top 2 *
from Orders
join [Order Items] as OrderItems --damm spaces!!!
on OrderItems.OrderId = Orders.OrderId
where OrderItems.ProductID = 1
order by CustomerId
Does the plan use the index on Orders.CustomerId ?
SQL = Scarcely Qualifies as a Language
November 2, 2005 at 3:28 am
Yes, it SCANS the NC index on Orders.CustomerID. If I remove the TOP 2, then it uses an index SEEK on OrderDetails.ProductID.
I can see how this might be beneficial - it is only retrieving data from one table, and that data has to be sorted in a particular order (which can be expensive), so scan an index where the data is already sorted. That much I can understand. However, the predicted cost is lower for the TOP 2 query, although the actual cost is higher when it runs:
With TOP 2 (estimated cost = 0.0194)
Table 'Order Details'. Scan count 64, logical reads 130, physical reads 0, read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 145, physical reads 0, read-ahead reads 0.
Without TOP 2 (estimated cost = 0.0298)
Table 'Orders'. Scan count 38, logical reads 87, physical reads 0, read-ahead reads 0.
Table 'Order Details'. Scan count 1, logical reads 78, physical reads 0, read-ahead reads 0.
So, back to my original question - why does the optimizer get the cost estimates so wrong when a TOP clause is specified (in my own case, very badly wrong)?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply