- I have an application to select/search records from the database (application and database in separate servers).
- In the database, one table only is fast growing table (more than 2 Millions per day).
- There is no update will happen in this table all are insert only.
- Created index for this table and everything is working fine.
- I try to install another SQL Server instance in the database server.
- After that, I realized that the select is very slow in the big table.
- I uninstall the another instances of SQL Server.
- Still the select is very slow in the particular table.
- Search in the some forum, they mentioned that need to update stats (EXEC sp_updatestats).
- After executed this query EXEC sp_updatestats the select query is very fast.
- But after 10 days, the same issue (slowness in select query) occurred again.
- 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.