Not a sound from the pavement

Have you ever come across a SQL query that used to run faster?  Has that query just recently begun to run considerably slower?

I just ran into one of those situations.  I had a query that should have been running in seven minutes or less (string parsing nearly 4 million records).  When it started running much slower, I decided to pull out some monitoring scripts and start checking a few things on the server.  I wanted to check for the usual suspects first and then find what was causing the alleged problems.

If you touch me you’ll understand what happiness is

When problems arise in a database, it is good to have a toolbelt of scripts available to help in quickly “touching” / assessing the problem.  In this case, I first turned to the following:

Code block

SELECT percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, READS,writes,logical_reads
,session_id,scheduler_id,start_time,status
FROM sys.dm_exec_requests
go

My goal from this query is to quickly assess if the query is still progressing.  For the query in question, I should see that the query is steadily writing to the database.  I can also see really quick the status of the query.  Running this in conjunction with sp_who2 is helpful in identifying, at a high level, issues that may be occurring.  Another possible query to run in this kind of situation would be sp_whoisactive by Adam Machanic (Blog | Twitter), or the who2 script I wrote about here.  The idea, is to get a quick glimpse at this point to see if there is a potential issue.

In this case, I found nothing alarming or noteworthy from the initial queries.  Other queries could be run at this point to try and determine if there is a pressure point somewhere on your resources (memory, cpu).  I decided to take a quick glance at task manager just to get a quick visual of the server.  Looking at it, I saw that I had no memory available – all 96 GB were in use.  I proceeded to check the max memory setting on SQL server.  As expected, it was still set at the default setting.  A little further prodding showed that the SQL server process was consuming most of the memory available to the server.

I changed the max memory to 64GB and immediately saw improvements.  In task manager the effect could also be seen, as shown to in the pic to the left.  NO server restart or service restart was required.  This change took effect immediately.  The poorly performing query was soon performing back to normal.

I can smile at the old days

This should serve as a reminder to check your server settings from time to time and ensure that they are appropriately set.  This little change (and that was the only change), allowed my query to return to normal processing which was > 5 times faster than the problem range.  Happy troubleshooting.