Index Scan instead of index seek
-
Hello guys,
I have the following query:
SELECT COUNT(*)
FROM "dbo"."STXL"
INNER JOIN "dbo"."STXL_RET"
ON "STXL_RET"."MANDT" = "STXL"."MANDT"
AND "STXL_RET"."RELID" = "STXL"."RELID"
AND "STXL_RET"."TDOBJECT" = "STXL"."TDOBJECT"
AND "STXL_RET"."TDNAME" = "STXL"."TDNAME"
AND "STXL_RET"."TDID" = "STXL"."TDID"
AND "STXL_RET"."SRTF2" = "STXL"."SRTF2"
AND "STXL_RET"."SPRAS_ISO" = "STXL"."SPRAS_ISO"
WHERE "STXL_RET".ProcessName = 'ISAM_EKKO_T1' AND "STXL_RET".BoName = 'MM_EKKO'And the following indexes:
STXL_RET = No indexes, its often deleted to zero and filled with new data, usually will have just few thousand records.
CREATE INDEX DM_IX_STXL_dbo_1289613674 ON STXL (MANDT,TDOBJECT,TDNAME) -- This was created automatically by the application integrated tool for different queries.
CREATE CLUSTERED INDEX IX_STXL_CLS ON STXL (TDOBJECT,TDNAME,MANDT) -- This was created only for the purpose of having clustered, was recommended to me that this always is better than HEAP
CREATE INDEX IX_RM_INSERT on STXL (MANDT, TDOBJECT, TDNAME) INCLUDE (RELID, TDID, SRTF2, SPRAS_ISO) WITH (DATA_COMPRESSION = PAGE) -- This was created for different query where the data is inserted into STXL_RET to make it faster.
CREATE INDEX IX_RM_INSERT2 on STXL (MANDT, TDOBJECT, TDNAME) INCLUDE (RELID, TDID, SRTF2, SPRAS_ISO) WHERE MANDT = '400' WITH (DATA_COMPRESSION = PAGE) -- This was just "test/idea" of mine to make the insert faster.
CREATE INDEX IXRM_INSERT1 on STXL (MANDT, RELID, TDOBJECT, TDNAME, TDID, SRTF2, SPRAS_ISO) WITH (DATA_COMPRESSION = PAGE) -- This is the last index I created, with the idea that in the index are all fields from the JOIN with the same order, and the results are terrible.Short story - STXL_RET keeps data for short time, and this count is used to count how many records are there, this will happen every day, where first STXL_RET will be deleted to zero records. All data from the table will be read each time. Considering that will be few thousand rows, I am not worried.
I have the same problem with lots of tables where the logic is 1:1 with this one, in _RET table data is saved, then the main table joined with RET does a count.
I get the table scan in STXL_RET basically we read the whole table (all fields, all rows). I don't understand why STXL does Scan + Seek on the same index, on other tables i noticed that there are 2 seeks on the same index, with different indexes.
We are not able to change the query, so unfortunately what we can work is workarounds using indexes and so on.
The query will remain the same and will run every day, multiple times, and we need to make it quicker.
Currently table STXL_RET has 656831 rows and STXL has 76689970 rows.
Using index IXRM_INSERT1:
Statistics:
(1 row affected)
Table 'STXL'. Scan count 1, logical reads 121508, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'STXL_RET'. Scan count 1, logical reads 10111, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 68531 ms, elapsed time = 68632 ms.
Here is without me picking the index, leave all the work to the SQL Server:
And the statistics:
(1 row affected)
Table 'STXL'. Scan count 1, logical reads 121337, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'STXL_RET'. Scan count 1, logical reads 10111, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 69141 ms, elapsed time = 69200 ms.
My question is what i am doing wrong, and how to make this query and other similar quires quicker - so far the only thing that came up to my mind is that actually _ret tables will the whole table read and that's why the execution plan does SCAN instead of seek on the other table.
Can someone recommend a index/solution? Thank you
-
give us the actual explain plan so we can look at it - images do not help us identify potential issues.
-
Do you have the supporting foreign key constraints in place?
😎
If the table joins are supported by foreign key constraints with the proper indices, the engine can "trust" the statistics instead of scanning!
-
Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes. If those key columns are not inherently unique, and you can add a single column that will make it unique, then add that column. If necessary, you can always add an $IDENTITY and use that to make the key unique.
Cluster STXL_RET on ( ProcessName, BoName ) /* or vice-versa */ first. It likely makes sense to then add MANDT, TDOBJECT, TDNAME to the clustered index, i.e., in the same order as STXL is clustered (then, with any luck, SQL will be able to do a merge join, if you forced STXL to have a unique clus key). You can test the index both ways on STXL_RET.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:
Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes. If those key columns are not inherently unique, and you can add a single column that will make it unique, then add that column. If necessary, you can always add an $IDENTITY and use that to make the key unique.
Cluster STXL_RET on ( ProcessName, BoName ) /* or vice-versa */ first. It likely makes sense to then add MANDT, TDOBJECT, TDNAME to the clustered index, i.e., in the same order as STXL is clustered (then, with any luck, SQL will be able to do a merge join, if you forced STXL to have a unique clus key). You can test the index both ways on STXL_RET.
why suggest changing the cluster index to something else without knowing what other queries use the main cluster? just making a suggestion because of a single query is likely to be a bad idea. An inexperienced DBA/Developer that follows this suggestion could potentially bring down every other major query on their system.
I could accept changing the clustered index based on analysis of all the indexes usage over a long period of time - but the info supplied here would never indicate a need to change this particular clustered index.
of more interest here would be to find out why the second index seek is required - and image supplied does not allow us to determine that.
- frederico_fonseca wrote:ScottPletcher wrote:
Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes. If those key columns are not inherently unique, and you can add a single column that will make it unique, then add that column. If necessary, you can always add an $IDENTITY and use that to make the key unique.
Cluster STXL_RET on ( ProcessName, BoName ) /* or vice-versa */ first. It likely makes sense to then add MANDT, TDOBJECT, TDNAME to the clustered index, i.e., in the same order as STXL is clustered (then, with any luck, SQL will be able to do a merge join, if you forced STXL to have a unique clus key). You can test the index both ways on STXL_RET.
why suggest changing the cluster index to something else without knowing what other queries use the main cluster? just making a suggestion because of a single query is likely to be a bad idea. An inexperienced DBA/Developer that follows this suggestion could potentially bring down every other major query on their system.
I could accept changing the clustered index based on analysis of all the indexes usage over a long period of time - but the info supplied here would never indicate a need to change this particular clustered index.
of more interest here would be to find out why the second index seek is required - and image supplied does not allow us to determine that.
I give the OP enough credit that they know our recommendations can only be based on this one use case, since that is all we know about. They can override the recommendations if they want based on unexpected exceptions to the requirements they posted.
OP stated that STXL is already clustered on the three columns I specified, I just changed the order. There could be queries that require the first order, but since there are 3 or 4 non-clus indexes based on the recommended order, I don't think so. Just my experience with db tuning.
And, per OP, the other table is clustered "randomly" just to have a clustering index, so it cannot now depend on a specific clustering.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Hello,
I will try to answer with an single answer to all of the questions.
- The database is not used often, the only sure thing that will run often(every day) is exactly this query a many other similar queries, so our goal is to optimize what is most often used. The database is there for 2 years, and so far, it was used once... And in addition, those tables are tables not used often, and not so interesting for the customer when they need to read some data, so here we are trying to optimize as much as possible.
- STXL_RET table is 100% handled by the application, the Applikation creates it, delete data and insert data to it. First we start the application which runs the query above and many other queries, this table does not exists and it's created. Therefore we cannot create index because does not exists. We do not have access to the configuration files where we can put index creation. And we definitely cannot create indexation on the go.
- Another thing about the indexes for STXL_RET is that some column has varchar(max). Again, the developers of the application did that, and we are not able to change it.
- The first run will work with a lot of data, millions of records, afterwards will run every day, and will work with just few thousand of records, so the table wont be so big, and my hope is that this table is not going to need indexes, since will have like 3-4000 records.
- I added here the .xml execution plan. I hope that's what you need/meant when you said provide the Actual Execution plan. I have run the query, once finished the "Actual execution plan" showed and that's what is in the .xml file. I don't know if there is a big difference when I click show execution plan vs the shown plan after actual run of the query, so I rather provided the one after run.
Thank you all
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply