February 21, 2018 at 8:04 am
I wantedto know the main things to look out for when looking into Execution plans e.g.I have seen some plans which have Table scans on table variables with 100* ofcost so I want to know if I should be worrying about that if yes how to removeit from execution plans as you cannot create indexes on table variables. Or anyother tips would be useful.
Thanks in advance
February 21, 2018 at 8:43 am
(1) You can have an index on a table variable, provided it supports a primary key constraint that's created at the same time as the variable declaration
(2) Table scans aren't necessarily bad
(3) The numbers have to add up to 100%, and if your query is relatively simple, it's not unusual to see it all assigned to a single operator
(4) Use temp tables instead of table variables, unless you are sure there will be a very small number of rows. And even then, better to use a temp table anyway, just to be safe
Performance tuning with execution plans isn't something that can be covered in a single forum topic. I would recommend that you buy a book on it or research it online, and post back if there's anything in particular you son't understand.
John
February 21, 2018 at 9:17 am
Thank you for your reply. Regarding the use of temp table being better than table variable, is it mainly because you can only create Primary Keys and on temp tables you are able to create indexes therefore the required data could be retrieved faster?
February 21, 2018 at 9:26 am
That's one reason, but the main reason is that you don't have statistics on temp tables (except, I assume on indexes created at declaration time). That means that the query optimizer assumes the same number of rows are in the table no matter what the reality. That number was 1 in older versions; I think it's changed now, maybe to something like 100.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply