Does index rebuild reset the existing connections?

Question

Hi All,

We are facing an interesting issue for a while now? The database view is used by both internal and external users. Sometimes the external users who are using .Net SQL Provider are getting query slowness but on the same time the same queries get run very fast via SSMS. While investigating those slowness we observed the wait type is "SOS_SCHEDULER_YIELD".

We have no idea how to fix this. One work around which is very weird by the sound of it is Index Rebuild job. When such thing happens we run the SQL Index Rebuild the external user can access the apps with expected performance. And slowly when time goes by again the slowness comes in. So the questions are two here:

• Why index rebuild fixes those specific queries even before the rebuild there is no fragmentation identified? Does it reset the user connection?
• What should we look into to mitigate this kind of slowness?

Version: SQL 2008

Thanks in advanced.

Answer 1

> Version: SQL 2008 I hope you know, that version is out-of-support since a very long time.

Without seeing any peace of code it's difficult to assist. Check the execution plan of the SP for different parameter; it could be a parameter sniffing problem. https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Answer 2

one important feature of index rebuild, is that it updates the stats for the indexes. this will change the query plans and can make them much faster. you can update the stats without a rebuild.