How do I effectively handle very skewed data? Statistics up to date but don't seem to help

_{Community Wiki answer generated from comments left on the question}

If you are running the query with @operator as a variable, SQL Server cannot 'sniff' the value in the variable, so it will use the statistics' average density value to calculate an estimate. No matter what value you assign to the variable, this average value estimate will always be the same.

One way to resolve this is by using an OPTION (RECOMPILE) query hint. This will compile a fresh plan on each execution, with the plan optimized for the specific value in the variable at that time. This comes at the cost (usually small) of a statement recompilation each time.

You could also modularize the code. You could use an IF statement and check the value of operatorid and if it's "user1" call one stored proc, let's say sp_user1. If it is not "user1" call a different procedure. The first sp would be optimized for "user1" and the other for the rest of the value. You could also use option (recompile) in the second sp for non-"user1" values if required.

It might also be a decent use-case for dynamic SQL. That would turn your @operator variable into a literal value, and would customize the plan for each user. Since there's only 7 users in that table I don't think that would really pose an issue for you.

For more information, see:

• Local Variables vs. Parameterized Stored Procedures in SQL Server (Video) by Kendra Little