SQL Server Performance

Intermittent speed issues with Query - HELP

Discussion in 'ALL SQL SERVER QUESTIONS' started by KINGED, Jul 29, 2013.

  1. KINGED New Member

    Below is the query I’m having issues with. Basically the query runs fine and the execution plan has all Seeks as seen in the attached file. The problem is other queries block it that I have no control over. I try to use WITH NOLOCK because this query is basically a filter that displays a number and doesn’t need to be exact so dirty reads are okay. My question to you guys is why if I’m using NO LOCK this query still gets locked.

    CPU\IO seem fine and to be honest it’s a hit and miss with this query. I will run it and it will take 0 seconds and then I run it again and it will take 4-5min! By stopping the service that just reads data as well the query runs fine. I just need to understand how I can better right this query to not be the victim or at least run even if it’s all dirty reads. Basically I just need it to finish :)

    Select
    Count(*)
    From
    dbo.tblEmail WITH(NOLOCK)

    Where
    ( /* -- USER DEFINED GROUPS -- */
    (
    (tblEmail.[SiteCode] <> N'IMS' OR tblEmail.[SiteCode] Is Null)
    AND (tblEmail.[TotalOrders] > 0)
    AND (DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[LastOrderDate])) >= DateAdd(Year, -2, DateDiff(Day, 0, GETDATE())) And DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[LastOrderDate])) < DateAdd(DAY, 1, DateDiff(Day, 0, GETDATE())))
    AND (tblEmail.[EmailID] Not In ( Select tblWFMember.[EmailID] From tblWFMember WITH(NOLOCK) Where tblWFMember.[WorkflowID] = 7 ) )
    AND (tblEmail.[EmailFrequency] Is NULL OR ltrim(tblEmail.[EmailFrequency]) = '' )
    AND (tblEmail.[EmailID] Not In ( Select tblSentLog.[EmailID] From tblSentLog WITH(NOLOCK) Where DateAdd(DAY, 0, DateDiff(Day, 0, tblSentLog.[CreateDate])) = DateAdd(DAY, 0, DateDiff(Day, 0, GETDATE())) ) )
    AND (tblEmail.[EmailID] In ( Select tblImpressionLog.[EmailID] From tblImpressionLog WITH(NOLOCK) Where DateAdd(DAY, 0, DateDiff(Day, 0, tblImpressionLog.[CreateDate])) >= DateAdd(Month, -3, DateDiff(Day, 0, GETDATE())) ) )
    )
    ) /* -- Internal Groups Below -- */

    AND (
    /* -- INTERNAL 'Only Active Emails' -- */
    (tblEmail.[StatusEnum] = 0)
    )

    Attached Files:

  2. Luis Martin Moderator

    Welcome to the forums!
    I can't see your query. Could you try again?
  3. KINGED New Member

    Holy Wingding I've been wingdinged!! When I posted it and reviewed it the query showed up fine. I will tray again!
  4. FrankKalis Moderator

    IN and NOT IN are easy and convenient to use, however, I would try to avoid them. Here is a good explanation as to why: http://www.componentworkshop.com/blog/2009/06/26/sql-server-basics-avoiding-in-and-not-in

    Maybe it already helps to rewrite them to something like:
    Code:
        NOT EXISTS ( SELECT
                        *
                    FROM
                        tblWFMember WITH (NOLOCK)
                    WHERE
                        tblWFMember.[WorkflowID] = 7 AND
                        tblEmail.[EmailID] = tblWFMember.[EmailID] ) AND
    If not, you could try to rewrite them as JOINs, as the article above explains.
  5. KINGED New Member

    So you mean like this??

    Code:
    Select Count(Distinct tblEmail.EmailID) As TotalCount From tblEmail WITH(NOLOCK)
    Where
    ( /* -- USER DEFINED GROUPS -- */
        (
              (tblEmail.[SiteCode] <> N'IMS' OR tblEmail.[SiteCode] Is Null)
              AND (tblEmail.[TotalOrders] < 1)
              AND NOT EXISTS ( Select tblWFMember.[EmailID] From tblWFMember WITH(NOLOCK) Where tblWFMember.[WorkflowID] = 7 AND tblEmail.[EmailID] = tblWFMember.[EmailID] )
              AND (tblEmail.[EmailFrequency] Is NULL  OR ltrim(tblEmail.[EmailFrequency]) = '' )
              AND NOT EXISTS ( Select tblSentLog.[EmailID] From tblSentLog WITH(NOLOCK) Where DateAdd(DAY, 0, DateDiff(Day, 0, tblSentLog.[CreateDate])) = DateAdd(DAY, 0, DateDiff(Day, 0, GETDATE()) ) AND tblEmail.[EmailID] = tblSentLog.[EmailID])
              AND (DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[ActivityDate])) >= DateAdd(Year, -4, DateDiff(Day, 0, GETDATE())) And DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[ActivityDate])) < DateAdd(DAY, 1, DateDiff(Day, 0, GETDATE())))
              AND EXISTS (Select tblImpressionLog.EmailID From tblImpressionLog WITH(NOLOCK) Where DateAdd(DAY, 0, DateDiff(Day, 0, tblImpressionLog.[CreateDate])) >= DateAdd(Month, -3, DateDiff(Day, 0, GETDATE())) AND tblEmail.[EmailID]=tblImpressionLog.[EmailID]  )
        )
          OR (
              (DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[3Screen])) >= '6/24/2013')
        )
    ) /* -- Internal Groups Below -- */
    
          AND (
              /* -- INTERNAL 'Only Active Emails' -- */
              (tblEmail.[StatusEnum] = 0)
        )
    I will try the JOINS but with the above I get the same execution plan and the same results.
  6. davidfarr Member

    If you say that you don't mind an occasional 'dirty read' then the most effective way to avoid query locking is to run the query in transaction isolation.
    You can then also remove all the NOLOCK syntax, as follows;

    Code:
    
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    Select
    Count(*)
    From
    dbo.tblEmail
    
    Where
    ( /* -- USER DEFINED GROUPS -- */
    (
    (tblEmail.[SiteCode] <> N'IMS' OR tblEmail.[SiteCode] Is Null)
    AND (tblEmail.[TotalOrders] > 0)
    AND (DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[LastOrderDate])) >= DateAdd(Year, -2, DateDiff(Day, 0, GETDATE())) And DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[LastOrderDate])) < DateAdd(DAY, 1, DateDiff(Day, 0, GETDATE())))
    AND (tblEmail.[EmailID] Not In ( Select tblWFMember.[EmailID] From tblWFMember Where tblWFMember.[WorkflowID] = 7 ) )
    AND (tblEmail.[EmailFrequency] Is NULL OR ltrim(tblEmail.[EmailFrequency]) = '' )
    AND (tblEmail.[EmailID] Not In ( Select tblSentLog.[EmailID] From tblSentLog Where DateAdd(DAY, 0, DateDiff(Day, 0, tblSentLog.[CreateDate])) = DateAdd(DAY, 0, DateDiff(Day, 0, GETDATE())) ) )
    AND (tblEmail.[EmailID] In ( Select tblImpressionLog.[EmailID] From tblImpressionLog Where DateAdd(DAY, 0, DateDiff(Day, 0, tblImpressionLog.[CreateDate])) >= DateAdd(Month, -3, DateDiff(Day, 0, GETDATE())) ) )
    )
    ) /* -- Internal Groups Below -- */
    
    AND (
    /* -- INTERNAL 'Only Active Emails' -- */
    (tblEmail.[StatusEnum] = 0)
    )
    
    
    Note that you might get an error that snapshot isolation is not configured for your database. You might need to first enable snapshot isolation, as follows;
    ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON
  7. FrankKalis Moderator

    Hang on. Where does this extra bit
    Code:
          OR (
              (DateAdd(DAY, 0, DateDiff(Day, 0, tblEmail.[3Screen])) >= '6/24/2013')
    
    now come from. That wasn't in your original question, was it?
    Anyway, I reread your question and if "all you need" is, that the quey finishes then David's suggestion to use SNAPSHOT ISOLATION is good. Sometimes it's also a good idea to turn parallelism off on the query level. So, maybe by putting a
    Code:
    OPTION (MAXDOP 1)
    at the end of your statement, you achieve at least consistent query execution time with less volatility.
  8. KINGED New Member

    Ahh my fault. This client has 4 queries which suffer from the same thing but each are slightly different. I must of been working on a different one when you had me try the NOT EXISTS method.

    Unfortunately I have tried that plus the snapshot isolation and there is no difference in speed. sometimes it runs in less then a second and the next time it can take 6min.
  9. FrankKalis Moderator

    Is that query running as part of a stored procedure or as a direct SQL statement? Are there any parameters that you've replaced in your sample with hardcoded values? What is the size of the tables involved? Is there also such a great variance in the number of rows returned?

Share This Page