I have been blogging for over 13 years and working as a SQL Server Performance Tuning Specialist for now over 10 years. There is no end to learning new stuff every day. Yesterday, we had a very interesting scenario with one of my large eCommerce organizations during the Comprehensive Database Performance Health Check. We had an issue where lots of active background jobs consuming lots of important resources.
Brief History of Active Background Jobs
One of my regular clients reached out to me suddenly to help them with SQL Server Performance tuning and we had optimized their server to run with great efficiency. Now the server was running all fine for a few days and suddenly it got slow one day. We got online again via online meetings to look deeper inside their system.
When we ran various diagnosis queries we were not able to find anything which was troubling in the system still we noticed that the server was consuming the high resources. This leads me to believe that there might be some background or system processes that are running and are using the resources.
Out of curiosity I ran the following T-SQL script and found that there are many background jobs was running for one of their API database which is constantly updated.
SELECT time_queued JobCreationTime, session_id SessionID, job_id JobID, database_id DatabaseID, request_type RequestType, in_progress InProgress FROM sys.dm_exec_background_job_queue
The query above was able to tell me what time the background job started and its type and if it was in the progress or not. Carefully looking at the rows of this job, we found a long-running job about the asynchronous statistics update for their API database.
After deliberating with the team, we decided that as it was a busy time it would be a good idea to kill the job right now and let it run next time completely when the load of the server is less. Here is how we can kill the job.
KILL STATS JOB 60 -- Change 60 to your sessionid
You should replace the 60 with your own session id from the script above.
Reference: Pinal Dave (https://blog.sqlauthority.com)
