Stored procedure is running slow but it used to perform well

Question

http://pastebin.com/index.php?e=1I know you might have heard about this issue so many times or at least once in your career but this has become a huge performance pain in our environment.

Database size: 1 TB after compression Table Size: 800 GB with more than billion records

Application usually queries the stored proc, When they run the report, it usually calls a stored proc and it eventually queries huge view ( it has close to 900 columns and one of the table in the view is above mentioned 800GB table ).

Environment Overview:

SQL 2014 enterprise, Always On No table partitioning ( planning to implement this year ) Windows 2012 Standard Memory : 512, 490 GB allocation to SQL Server CPU : 16 cores

Issue: when a report was run, it calls a stored proc and the stored proc ultimately generates dynamic SQL based on the user select dynamic parameters. So, problem here is for every execution it generates new plan in the plan cache.

when ever the issue arise, we tweak the code inside by adding/removing some filters as a temporary work around but what we are looking here is to find a definitive solution to this problem.

Things we have done:

1. We have setup a different customized update stats job based on rows modified with sample percent for each indexes in that huge table.
2. We don't rebuild indexes on this huge table because of the amount of time but we do reorganize based on page count. Created filtered statistics on the problem table ( large table ) based on the recommendation from SQLSkills because of skewed data ( we are working in parallel with SQL Skills team )

Things to do:

Table Partitioning - I'm thinking of creating filtered indexes. If you have encountered any of these issues, please let me know your thoughts or suggestions.

Answer 1

I'm curious about what the plans look like even performance is bad. Hopefully you can tune those plans, and then make sure the dynamic SQL produces the right queries for those conditions.

Some things you may want to look for though:

Make sure your view is set up to simplify out joins that are not needed. Watch http://bit.ly/Simplification to get a picture of how.

Consider using additional predicates that could be used by the QO if particular plan shapes are used. Watch http://bit.ly/Sargability at the section about inverted predicates. But also, consider things such as looking out for year(thedate) = @y so you can change it to thedate >= datefromparts(@y,1,1) and thedate < datefromparts(@y+1,1,1) instead (or as well, in case you have an indexed computed column).

Additional IS NOT NULL predicates can be useful for helping some joins to be simplified out.

Check that you're avoiding OR and IN where possible, or at least considering what the impact is. You may want to change someval in (1,2,4) (which is three seeks within one operator) to someval >= 1 and someval <= 4 and someval != 3 (one seek over a range) in some situations, but be wary of the impact of that on composite indexes. Read http://sqlperformance.com/2016/06/sql-plan/whats-actually-going-on-with-that-seek for more on that.

When a filter isn't used, make sure you're completely leaving it out rather than using catch-all predicate. You may find you're asking it to check values you really don't care about. Hopefully you're doing this already.