October 20, 2004 at 5:44 am
Hi there,
I'm trying to improve a beast of a select statement and use Showplan to do this. I notice the use of index seek and index scan. I can find out what they mean, I am unsure tho which operation would be the more efficient 1.
Also, is there a place to find out what every icon in showplan stands for, and what the values shown in the popup mean? For instance, what info do I get from the estimated (subtree) cost? WHat does it tell me to see an estimated COPU or I/O cost?
I seem to recall having seen an in-depth explanation in BOL, but I can't find it anymore...
Greetz,
Hans Brouwer
October 21, 2004 at 1:05 am
Hi Hans,
try this (from BOL for MS SQL 2000): in the Index tab of BOL type "execution plan" and double click on the "Execution Plan pane" in the list below. The "Query Window Execution Plan Pane" topic should appear. The Icons section contains only one link, "Graphically Displaying the Execution Plan Using SQL Server Query Analyzer", which contains a table with icons and a link to each icon's explanation. Below the icon tables there is an explanation of the terms used in step hint popups.
Regards,
Goce Smilevski.
October 21, 2004 at 3:56 am
Tnx for the response, Goce. I had found it already. Now let's hope someone can answer my other question...
Greetz,
Hans Brouwer
October 21, 2004 at 6:28 am
If you are wondering about scan vs seek :
seek will always be the faster than a scan if the index is selective enough (meaning that you have many different values and not just only yes/no or something like that).
Also a clustered index seek will be faster than an index seek and the slowest operation of all will be a table scan.
October 21, 2004 at 4:19 pm
An index scan is not always bad. If you're retreiving a large fraction of the table, an index scan feeding into a merge join would be faster than a loop join looking up one row at a time with an index seek.
Is the (non-clustered) index seek or scan followed by a bookmark lookup? If not, it means you have a "covered index"...all the required fields are in the index and nothing extra has to be looked up in the table. If you have a bookmark lookup, you might consider adding the needed fields to the index (depending on their number and size). Clustered indexes never require a bookmark lookup.
Instead of worrying about what each icon means, focus on the cost percentage associated with each one. The most costly steps are the ones you should try to improve.
Try adding a variation of the query to the batch, and get the execution plan for the whole batch. There will be an overall cost estimate percentage for both plans which will tell you whether the variation is an improvement. Sometimes major rearrangements will be optimized to an identical plan, sometimes a small change makes a huge difference. This method does not require that you know the meaning of any of the plan icons, but if you find a variation that is significantly faster you will learn a lot by comparing the two plans.
October 22, 2004 at 2:11 am
Tnx for the info all. I will sure keep them in mind.
Greetz,
Hans Brouwer
December 15, 2004 at 3:34 pm
I have a related issue that hopefully I can get some help with. On a sql 7 server my query uses a clustered index seek and the same database on sql 2K uses a clustered index scan. The query is listed below. procperiod is part of the index. I've tried updating statistics and recreating the clustered index. Any ideas?
select count(*) from tpa_arch.dbo.taxdata where procperiod=200409
Thanks.
December 15, 2004 at 8:32 pm
If procperiod is the first field in the clustered index, I would think it should always use an index seek. I'm assuming it isn't the first field.
Either the data has changed enough to alter the statistics of the index distribution, or the execution plan optimizers in 7 and 2000 are using different algorithms. I'd bet on the optimizer.
If you think a seek would work better, use an index hint to force the seek and compare execution times. Sometimes hints are required to force the optimum execution plan, but it's extremely rare in my experience.
December 16, 2004 at 10:47 am
Thanks for the reply. procperiod is the first field. I think it is the optimizer also but don't understand why. The query returns the same result but takes about 2 minutes longer on the SQL 2K server (index scan). How do you use an index hint to force a seek?
December 16, 2004 at 12:22 pm
Here's an emxemple :
Select col1, col2
FROM dbo.[Bon de travail] INNER JOIN
dbo._Temps_tech_repas WITH (INDEX(IX__Temps_tech_repas_FkBonDeTravail)) ON dbo.[Bon de travail].[N° Bon de travail] = dbo._Temps_tech_repas.[N° Bon de travail]
the part "WITH (INDEX(IX__Temps_tech_repas_FkBonDeTravail))" tells which index to use on the table dbo._Temps_tech_repas
the hint goes like this :
from tablename WITH(INDEX(Indexname from that table))
December 16, 2004 at 12:33 pm
So I used the query below but it still does a scan instead of seek.
select count(*) from tpa_arch.dbo.taxdata with (index(pk_taxdata)) where procperiod=200409
December 16, 2004 at 12:43 pm
Is "procperiod" the primary key of the table? or actually
Is "pk_taxdata" the index that covers the column "procperiod"?
I doubt you can force the plan to use an irrelevant index.
BTW I don't think you can force a seek if a scan is faster than the seek. I think the indexes hints were there simply to let you choose WHICH index to use not how to use them.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply