To Forceseek or not to Forceseek

GokhanVarol 2013-06-20 03:29:06

This is something I am running into a bit. We have a fairly large database with tables (200+) with 3300+ partitions with some billion or rows +, data is not well balanced between partitions (it's mortgage data, there are packed counties/cities/states and there are counties with few home etc). There is the type of query that joins to a data not already in the system, some temp table created and hopefully indexed property and joined against this large tables. Unless a literal is passed in the query and filtered on that, when the driver tables are used optimizer does not chooses a index seek. Today another user (whose job is run adhoc queries to do matches) asked me this question, should I let a table scan with hash plus memory grant or should I forceseek. I asked him to try it and this is what came out. Of course it is not cold buffer (but it can never be fully hot either). This 53+million driver table hitting a table which has over 430million rows returned the results at similar times with or without forceseek hint. The total logical reads with forceseek query is over 330million+ and 11million+ for the scan. The storage is SAN, many luns (30+), server has 64 core 4 socket server with 4 core intel E5 cpus. There are many, a lot more complex queries running on the server , few times a day queries wait on resource_semaphore for 15 mins or so and today I noticed Page Life Expectancy lower than 150 on a 1TB memory server.
Should I forbid the users from using forceseek because it will cause additional trouble that I cannot see on a query plan which will affect the whole server or should I tell them if the driver table is less than 10 million records or 10% of the largest table use forceseek?
If many queries used forceseek hint would that create any other issue that I may not have seen yet?
Thank you

link text

SQLkiwi 2013-06-20 10:25:40
It's hard to offer very specific advice without knowing the broader system well, but here are my general comments:

The hash join query has one primary advantage: it employs effective read-ahead using large I/Os. On the other hand, that query requires a 22GB memory grant. This 22GB has to come from somewhere, resulting in cached data being evicted from cache, tending to increase I/O.

With a number of high-grant queries running, you also risk grant waits as you have noticed. The hash join plan is also quite inefficient in passing very large numbers of rows across repartitioning exchanges. Parallel hash join is a good choice where one input is much smaller than the other, and that is not the case here.

The nested loops plan requires more logical I/O, and does not use read-ahead as effectively. In fact, your plan shows no pre-fetching at all on the joins, which is a little surprising. Nevertheless, the proportion of logical reads that resulted in a physical I/O is still quite low. Overall, I would prefer the NLJ plan here, from a concurrency and efficiency point of view.

Both plans perform significant work in the final Compute Scalar that runs on a single thread over 53 million rows. You may find an improvement in moving those calculations into the parallel zone – an OUTER APPLY with NULL rejection is the trick I most commonly use to achieve that.

Selecting 4.1 GB of data into a table on a single thread is also a bottleneck, but without using CLR to parallel bulk load, you will have to wait for a future SQL Server version to improve that aspect of the execution. You could choose to select into a (global) temporary table instead, primarily for the logging efficiency in tempdb, but you may have good reasons for not doing that. As I say, specific advice is tough without detailed knowledge of the wider system.