SQL Server query suddenly slow

Question

I have a problem with a SQL database query that suddenly (but regularly about every three weeks) becomes slow.

Setup is the following:

• Windows Server 2008 (not R2) 64 bit, 8 GB RAM
• SQL Server Express 2008 R2
• The database has a size of 6 GB (mdf file size)
• The table (Orders) the query is mainly selecting from has around 24000 records, five other joined tables are small (100 records or less)
• The table Orders has a varbinary(MAX) column Report that contains binary data (PDF documents) with an average size of about 200 to 300 kB (but can be up to 2 MB occasionally). More than 90% of those 24000 orders have this column filled, for the others it is NULL, i.e. more than 90% of the 6 GB database size are binary data.

The query in question has the following structure:

SELECT TOP (30) [Project2].[OrderID] AS [OrderID]
                -- around 20 columns more
FROM ( SELECT [Project2].[OrderID] AS [OrderID],
              -- around 20 columns more
              row_number() OVER (ORDER BY [Project2].[OrderID] ASC) AS [row_number]
       FROM ( SELECT [Filter1].[OrderID] AS [OrderID]
              -- around 20 columns more
              FROM ( SELECT [Extent1].[OrderID] AS [OrderID]
                     -- around 20 columns more
                     FROM [dbo].[Orders] AS [Extent1]
                     INNER JOIN -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     WHERE ([Extent1].[Status] IS NOT NULL) 
                       AND (4 = CAST( [Extent1].[Status] AS int))
                       AND ([Extent1].[SomeDateTime] IS NULL)
                       AND ([Extent1].[Report] IS NULL)
                   ) AS [Filter1]
              OUTER APPLY  (SELECT TOP (1) [Project1].[C1] AS [C1]
                            FROM ( SELECT CAST( [Extent7].[CreationDateTime] AS datetime2) AS [C1],
                                                [Extent7].[CreationDateTime] AS [CreationDateTime]
                                   FROM [dbo].[OtherTable] AS [Extent7]
                                   WHERE [Filter1].[OrderID] = [Extent7].[OrderID]
                                 ) AS [Project1]
                             ORDER BY [Project1].[CreationDateTime] DESC
             ) AS [Limit1]
       )  AS [Project2]
)  AS [Project2]
WHERE [Project2].[row_number] > 0
ORDER BY [Project2].[OrderID] ASC

It is generated from a LINQ-to-Entities query by Entity Framework. The query occurs in a few variations which are only different in the first WHERE clause:

• The five variants

  WHERE ([Extent1].[Status] IS NOT NULL) 
    AND (X = CAST( [Extent1].[Status] AS int))
  

  X can be between 0 and 4. These queries are never a problem.

• And the two variants (*)

  WHERE ([Extent1].[Status] IS NOT NULL) 
    AND (4 = CAST( [Extent1].[Status] AS int))
    AND ([Extent1].[SomeDateTime] IS NULL)
    AND ([Extent1].[Report] IS NULL)
  

  or ... IS NOT NULL... in the last line. I have the problem described below only with those two queries.

The "phenomenon" is:

• The two queries (*) are run 100 to 200 times per day, 5 days per week. They perform with less than a second for around three weeks.
• After three weeks both queries suddenly need more than 60 seconds. (This time actually increases with growing database size.) Users get an error (on the web page; it is a web app) due to a timeout. (Entity Framework doesn't seem to wait longer than 30 seconds for the result by default.)
• If I paste the query into SSMS and let the query run (waiting the 60 seconds) the result is successfully returned and the next same query runs again in less than a second.
• After around three weeks the same happens again (but the time the query runs will be 65 or 70 seconds then)

An additional observation:

• If I restart the SQL Server service process at times when the query performs normal, memory usage of the process increases slowly. It reaches a limit of around 1,5 GB (private working set in Task Manager) step by step within roughly a week.
• If I restart the SQL Server service process when the query is suddenly slow and trigger the query again I can watch in Task Manager that the service loads almost 1 GB within a few seconds.

Somehow I suspect the whole problem has to do with the memory limitation (1 GB) of the Express edition and the varbinary(MAX) column although I just use it in the WHERE clause that checks if the column value is NULL or not NULL. The Report column itself is not one of the selected columns.

Since I am running against the limitations (10 GB mdf file size) of the Express edition next year latest I am considering changes anyway:

• Either move the binary column to another table and store the content externally via FILESTREAM, keep using the Express Edition
• Use one of the "big" SQL Server editions without the Express limitations, keep the binary column in the Orders table
• Do both

Question: What could be the reason that the query is suddenly slow? Could one of the changes I am planning solve the problem or are there other solutions?

Edit

Following bhamby's tip in the comments below I've set SET STATISTICS TIME ON in SSMS before running the query again. When the query is slow again I get a high value for SQL Server parse and compile time, namely: CPU time = 27,3 sec and Elapsed time = 81,9 sec. The execution time for the query is only CPU time = 0,06 sec and Elapsed time = 2,8 sec. Running the query a second time after that gives CPU time 0,06 sec and Elapsed time = 0,08 for the SQL Server parse and compile time.

Municipality

Can you isolate any process(es) that coincide with this 3 week interval? Seems odd that anything other than competition would cause that type of inconsistent performance. – Lionel

@GoatCO: I had checked that several times, but there is no competition with other processes. The frequence isn't exactly 3 weeks, it can be some days more or less and it can happen in the morning or afternoon. When it happens there is neither high CPU nor memory load on the server. And the problem never disappears by itself (which I would expect if some other competing process ends). The only way I found so far is running the query once in SSMS. – Municipality

Ah that's interesting. If you run it in SSMS from time to time can you prevent the slowdown altogether, or does it still happen? – Lionel

First I would de-normalize and put Report in a separate table or external. Sounds like in memory versus read from disk. I you only have 6 GB of data and most of it is the Report. If you take Report out of the query then 1 GB of memory is not so abused. – Anthem

@GoatCO: I "believe", yes. I did this once after two weeks and the next time the problem wasn't a week later but 3 weeks. But I only tested this once. – Municipality

Here is a "generic tip"......... Maybe a better "covering index" is needed. sqlserverperformance.wordpress.com/2011/02/04/… – Toothlike

Here is another rabbit hole you might go down : #9891199 – Toothlike

I wonder if the increased time you're seeing is when the execution plan for your query has to be recompiled. Next time you have the issue, try adding SET STATISTICS TIME ON; before your query in SSMS, and see if the part labeled SQL Server parse and compile time is really high. I'd also be interested to know if the query takes a really long time to execute the first time after you restart... that would be an indicator to me that this may be an issue. – Land

@Land that is what I was thinking, I don't know a great deal about plan caching. Found this question that might be helpful: #10474 – Lionel

@bhamby: The query runs normally fast after restart. But I will keep the statistics time test in mind. Thanks for the tip! – Municipality

The compiler is just probably rebuilding the execution plan. The query (as others have pointed out) can be optimized. Check the execution plan, optimize/remove a bunch of those subqueries, then keep your stats up to date. The SQL Server process will use up any available space without releasing it immediately, so seeing the process grow quickly isn't really suspect (necessarily). Run this before executing in SSMS and see if it takes 60 seconds: DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS – Furniture

@bhamby: I've tried SET STATISTICS TIME ON today when the query was slow again. And the result is as you guessed that the parse and compile time is high (see Edit section at the end of my question for the concrete numbers). However, I'm unsure what I can do against this unwished reparsing and recompiling... – Municipality

Can we upvote this question please? It is chockfull of good information for people to help with the problem. More questions should look like this. – Expeller

Answer 1

This just seems wasteful

SELECT TOP (1) [Project1].[C1] AS [C1]
FROM ( SELECT CAST( [Extent7].[CreationDateTime] AS datetime2) AS [C1],
                    [Extent7].[CreationDateTime] AS [CreationDateTime]
         FROM [dbo].[OtherTable] AS [Extent7]
        WHERE [Filter1].[OrderID] = [Extent7].[OrderID]
     ) AS [Project1]
ORDER BY [Project1].[CreationDateTime] DESC

is

SELECT max( CAST( [Extent7].[CreationDateTime] AS datetime2) ) AS [C1]
  FROM [dbo].[OtherTable] AS [Extent7]
 WHERE [Filter1].[OrderID] = [Extent7].[OrderID]

Why are you not storing dates as datetime?

I don't like that outer apply
I would create a #temp that is run once and join to it
Make sure and declare [OrderID] as PK

SELECT [Extent7].[OrderID], max( CAST( [Extent7].[CreationDateTime] AS datetime2) ) AS [C1]
FROM [dbo].[OtherTable] AS [Extent7]
GROUP BY [Extent7].[OrderID]

You could have loop join going on

Next I would put this in #temp2 so that you are sure it is only run once
Again be sure to declare OrderID as a PK

SELECT [Extent1].[OrderID] AS [OrderID]
                     -- around 20 columns more
                     FROM [dbo].[Orders] AS [Extent1]
                     INNER JOIN -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     WHERE ([Extent1].[Status] IS NOT NULL) 
                       AND (4 = CAST( [Extent1].[Status] AS int))
                       AND ([Extent1].[SomeDateTime] IS NULL)
                       AND ([Extent1].[Report] IS NULL)

If Order is only 24,000 rows then something stupid is going on for you to have queries more than a few seconds.

Anthem

+1: Using max is a good point! I have taken the corresponding LINQ query from another query where I actually select more columns than only the CreationDateTime. But in this version max seems smarter indeed. I will try that! BTW: CreationDateTime is stored as datetime2(0). The (redundant) CAST is generated by Entity Framework for whatever reason. OrderID is PK btw. – Municipality

Then take out the cast as it takes time. – Anthem

Answer 2

If it is a query which is run often, then I would suggest turning it into a Stored Procedure and using the results of the procedure.

In Entity Framework you should be able to import the procedure as a Function Import.

You can then take control of the Stored Procedure's execution plan by giving it query hints or combating Parameter Sniffing.

It smells like your server's execution plans are going out-of-date every 3 weeks, hence the slow-down.

Also, you mentioned that you are using 64bit SQL. My experience has been that 64bit SQL does not tend to perform very efficiently with sub-queries. My advice would be to try and avoid them.

Meistersinger