Select query slow in the big table because of expired stats in Microsoft SQL Server

Question

1. I have an application to select/search records from the database (application and database in separate servers).
2. In the database, one table only is fast growing table (more than 2 Millions per day).
3. There is no update will happen in this table all are insert only.
4. Created index for this table and everything is working fine.
5. I try to install another SQL Server instance in the database server.
6. After that, I realized that the select is very slow in the big table.
7. I uninstall the another instances of SQL Server.
8. Still the select is very slow in the particular table.
9. Search in the some forum, they mentioned that need to update stats (EXEC sp_updatestats).
10. After executed this query EXEC sp_updatestats the select query is very fast.
11. But after 10 days, the same issue (slowness in select query) occurred again.
12. I created daily jobs to update the stats, after 10 days, the issue observed again. After update the stats manually, it is very fast.

The whole issue observed after I installed the second instance of SQL server in the same server. Is my observation correct?

Even I uninstall the seconds instance, the slowness issue observed frequently.

Kindly give me your valuable suggestion. Thanks in advance.

Answer 1

Just looking at the details you have provided, there could be quite some reasons. we can't just predict the exact possible reasons unless you share those select query or queries running slow or you share the execution plans.

Looking at first glance it looked you might have issue with skewed stats. But if you are saying update stats is running daily may be you can try with better sampling rate to see if that helps.

However based on what you are saying that things looked better after manual update stats, this is very much possible that process gets recompiled or bad plan might be getting evicted from cache from that update stats.

My personal experience says you might also been seeing something called parameter sniffing-. There are lot of great articles over the web in case you are experiencing the same.

If you can add more details what is slow with execution plans for pre and post changes can confirm above possibilities.