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.