SQL query tuning front end Website slowness
Overview of the issue. There is a health care website which used by doctors, in a day which runs slow sometimes and runs fine most of the time. The slowness is about 1 to 3 seconds and it sometimes timeout as well, when it reaches timeout setting.
I have configured server side trace, whoisactive and Perfmon counters as well to correlate with anything when needed.
As a basic check, the website is fetching data from two databases one is 300 MB and other is 500 MB and no maintenance job been run in place. Have configured maintenance job, still same issue after a re index etc run. In the initial, I thought it is tiny database is not going to cause anything, it could easily fit into memory, my prediction is wrong.
I have got the stored procedure and runtime and other metrics from trace and got captured from GUI profiler as well.
There was a stored procedure which has high cost in three operators, they are key lookups and scans, have made them to seek, after that it has an optimal runtime. It has parameters as well, let us look any PSP issue as well in the future.
Comparison of both run time. Cannot attach the query plans.
Here is CPU Perfmon data, top one is when we had issue and there was no maintenance job in place and bottom one is after tuning and every Saturday there was a spike cause of checkDB.
Do not guess by looking the database size 🙂
