26 performance tuning questions and solutions, bad statistics, bad statistics in SQL Server, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, Performance issues due to bad statistics?, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases
Performance issues due to bad Statistics?
1. Statistics can be out of date – means we don’t have enough changes that have triggered Auto update stats.
2. In this case we check plan cache for queries where CPU usage is very high. E.g. Heavy index scan, Index seek with heavy key lookup.
3. Under execution plan check estimated and actual number of rows. If there is a huge different between these two then possibly the problem is there. E.g. Actual no of rows – 10000 and Estimated No of rows is 1. Like in table variable case.
4. Missing Statistics in execution plan – This we will get when we check the execution plan. Warnings can be seen in green colour over the graphical execution plan. There we will get a warning about the missing statistics. We normally find this with Indexed views. In this case we can create user defined statistics.
5. Statistics can be corrupted but very rare. In this case we have to rebuild the Index.
6. Statistics are very important for excellent query performance. Always try to keep them up to date.
7. Enable Auto create statistics on.
8. On larger tables we should create a schedule to update stats.
9. Also check queries which are suffering from out of date statistics issue. We can easily check this by comparing estimated and actual number of rows.