SQL Server Performance Forum – Threads Archive
Hash Matching Over BookmarkingHello I have a stored proc which sometimes is using a hash/match rather than a bookmark lookup depending on the quantity of data in the database (or so I think).
I am trying to figure out why.
In one copy of the database I have 56231 rows in an account table, and 83980 rows in a user permission table, the proc does a join on primary key or indexed fields. In this instance it uses a bookmark lookup, and under a load test the proc executes in 15-46 milliseconds. In the other copy of the database I have 79337 rows in an account table and 84672 rows in a user permission table, again same joins and schema. In this instance it uses a bookmark lookup/hash and a hash match/inner join , and under the load test the proc executes in 3456 milliseconds. I cant understand why the extra 23000 rows in the account table force a different execution plan/strategy and why the time difference is so huge. I have reindexed the tables involved and done other performance tuning but cant figure out whats going on. ANY Help greatly appreciated Thanks in advance
Got it sorted, I tried removing one index on the user permission table and this has changed the proc back to optimal performance. Thanks
It’s true that SQL Server Optimizer is not the best in the world and MS SQL Server team is continuosly striving to make it perfect. But, there are certain things that we can do, such as creating proper indexes, to help it to choose the proper execution plan. If all else fails, Optimizer hints are there for your rescue.
I used the index tuning wizard using a typical "dry run" of the system and it showed to remove the index. Worked a treat, and the article on this site showing what to do is quite good.