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 ).
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:
- We have setup a different customized update stats job based on rows modified with sample percent for each indexes in that huge table.
- 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.