As part of our daily ETL process, we drop before table load and recreate indexes after the load. Stored Procedures tend to run slow when we execute them in SSMS. But after one run in the same session when we run again it runs quick. I have 2 questions.
- Is the Execution plan for SP getting updated during first run and it will hold good for further connections?
- As part of my ETL process, if i execute the procedure for certain set of input parameters, when the users access will it be quick.
Will statistics get updated when we drop and recreate indexes.
You have a performance problem. You need to measure where the problem is and identify the bottleneck. Read How to analyse SQL Server performance.
Anything else would be, at best, a guess. Is it slow because blocking on a concurrent update from the app? Is it slow because of bad plans? Is it slow because of buffer warm up? Who can know? This is why is important to use a methodical approach and measure.