5

I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this week.

The server is running a web app that receives 5-10 requests per second and each request is translated to the same query to the database, only with different parameters (this is all handled by an ORM). These queries (let's call it the GetProduct query) are kind of large because they retrieve data from 13 tables to build a single JSON response that is sent back to the user. The query usually takes between 800 and 1500ms to run.

This week I noticed that when I executed a query, a simple select top 1000 with a single where condition in a table that is also scanned by the GetProduct query the Database I/O usage reported by SQL Management Studio goes up fluctuating between 30MB/s and 50MB/s even when my query already returned (the normal database I/O is between 0.1MB/s and 1MB/s). I detected that suddenly my GetProduct query is taking now more than 60 seconds to complete! Causing timeouts and all the queries executed to the database becomes really slow. This is fixed if I restart the entire server box, everything goes back to normal until I run a query again which is very weird.

I'm far from a DB expert, I'm a programmer that is also in charge of maintaining this database, none of the team knows much of databases. I have updated statistics and noticed that fragmentation is really high on most of my indexes (between 95% and 99% as reported by SSMS). I'm planning a maintenance window so I can take down the app and rebuild all the indexes.

Does fragmentation cause this kind of behavior? I executed sp_whoisactive and I'm sure there's nothing else running on my DB, just calls to the GetProduct query taking a lot of time to complete.


UPDATE

I got the query plans using the "Recent Expensive Queries" section in the Activity Monitor of SSMS because sp_whoisactive @get_plans = 1 was returning a NULL query_plan for this queries.

enter image description here

I can't use Pastebin because the files are too big so I uploaded them to my Google Drive account:

Here is the query that is being executed, the only parameter that changes is @p__linq__0 that is replaced by different UUIDs: https://pastebin.com/YnrCJVLW

Finally, here you can see the output of sp_whoisactive @get_plans = 1 when the queries are taking a lot of time to complete:

enter image description here

8
  • Have a look there Feb 27 '20 at 15:21
  • Have you confirmed that SQL Server is causing the I/O, or if it's something else? If the system has no available memory and the query is resulting in a lot of memory being swapped to the pagefile, this would cause everything to be slow and a lot of disk activity. This is probably not the issue, but it's easy to check. Feb 27 '20 at 15:34
  • @DenisRubashkin I will add the information mentioned there, thanks :)
    – Andres
    Feb 27 '20 at 15:40
  • @TonyHinkle yes, I confirmed with Resource Manager that all the disk activity is on the MDF file, mostly reads.
    – Andres
    Feb 27 '20 at 15:41
  • 2
    Reindexing isn't likely to help--if that was the issue, it would be slow all of the time. You may need to update statistics--super easy to do and no downtime required. If you can use brentozar.com/pastetheplan to show the query plans when it is fast and when it's slow that would be helpful. Feb 27 '20 at 15:55
0

I wanted to post the solution that apparently worked for my case. I took down the app for maintenance last weekend and did a rebuild of all the indexes of the database (through the SMSS UI) followed by a statistics update and I'm happy to say we didn't experience the issue anymore since then.

I had rebuilt statistics before this and it didn't help and if the problems were related to index fragmentation then the queries should be slow all the time, but for some reason, it worked in my case.

Thank you, everybody, for your time and suggestions!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.