Sql server has a database option called parameterization. If the option is set to forced, sql server automatically parametrizes the queries and saves the query plan, so that it can be re-used for similar kind of queries. This is very helpful especially for ad-hoc kind of work load.
At my work, we have this option on as our work load is primarily ad hoc and this has helped in keeping down our cpu utilization. Recently our database cpu usage started hitting 70-80%. During that time, the queries with IN clause seem to be taking the longest time to execute.The query plan looked fine. On further analysis setting the time statistics on, it was the compilation that was taking nearly 3-4 seconds.
When the same query was executed against database with simple parameterization, the compile time was between 100ms to 200ms.
The query had more then 1500+ elements in the IN clause. The process of parameterization these 1000+ elements was taking nearly adding 3-4 seconds to the compile time.
There are couple of options to tackle this.
1. create a plan guide for this specific kind of query and override the forced parameterization to simple.
2. Rewrite the query so as to not to use the IN clause. Move the IN clause elements into a table and convert the IN clause to a join.
First option was not feasible as its the elements in the IN clause was completely user controlled and the plan guide had to match the query exactly. So if the elements in the IN clause change, the plan guide was not useful.
After rewriting the query to a join and deploying the fix, brought down the cpu usage to 30-40%.
So if your cpu usage is high on the database server with forced parameterization option on, look for queries with IN clause.