Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: SQL server 2005 query timeout on production

    I have a strange issue in the production server.
    An sql select query for generating reports causes 100% CPU and timeouts only if the Start date is equal to or above 2nd March 2012.
    Whereas all the reports can be generated perfectly if the start date is less than 2nd March.

    Also this issue is faced in the production server only. I had taken a dump of database and restored the whole environment on local and was able to generate all reports with any date range.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish the complete query statement of which you got such incredible results.

    Though more informations may be necessary, but this would be a starting point.

  3. #3
    Join Date
    Jul 2012
    Posts
    6
    Below is the query

    ================================================== ===

    SELECT EMPLOYEEID, BSCCCODE, VENDORNAME, EPURSEID, SHORTDESC, SUM(RECCOUNT) AS TRANSCOUNT, SUM(CREDIT) AS CREDITAMT, SUM(DEBITCARD) AS DEBITCARDAMT,
    SUM(DEBITCASH) AS DEBITCASHAMT, SUM(CASH) AS CASHAMT, SUM(VOID) AS VOIDAMT

    FROM

    (SELECT TOP (100) PERCENT ET.EMPLOYEEID, ET.BSCCCODE, VENDORNAME, ET.EPURSEID, SHORTDESC, COUNT(*) AS RECCOUNT,
    CASE WHEN TRANSACTIONTYPE = 1 THEN SUM(EPURSEAMOUNT) ELSE 0 END AS CREDIT,
    CASE WHEN TRANSACTIONTYPE = 2 THEN SUM(EPURSEAMOUNT) ELSE 0 END AS DEBITCARD,
    CASE WHEN TRANSACTIONTYPE = 2 THEN SUM(CASHAMOUNT) ELSE 0 END AS DEBITCASH,
    CASE WHEN TRANSACTIONTYPE = 3 THEN SUM(TRANSACTIONAMOUNT) ELSE 0 END AS CASH,
    CASE WHEN TRANSACTIONTYPE = 4 THEN SUM(EPURSEAMOUNT) ELSE 0 END AS VOID
    FROM EPURSETRANSACTIONSVIEW ET, EPURSES EP, EMPLOYEES EM
    WHERE(LOCATIONID = 1) AND ET.EMPLOYEEID = EM.EMPLOYEEID AND EM.EMPLOYEETYPE = 3 AND
    (TRANSACTIONDTS BETWEEN CONVERT(DATETIME, '2012-03-02 00:00:00', 120) AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)) AND ET.EPURSEID = EP.EPURSEID
    GROUP BY ET.EMPLOYEEID, ET.BSCCCODE, VENDORNAME, TRANSACTIONTYPE, ET.EPURSEID, SHORTDESC ORDER BY EMPLOYEEID, TRANSACTIONTYPE) AS A

    GROUP BY EMPLOYEEID, BSCCCODE, VENDORNAME, EPURSEID, SHORTDESC

    ORDER BY EMPLOYEEID, VENDORNAME

    ================================================== =====

    But not sure if the query is the issue here as the same is working in the local machine.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that one grouping might be enough, then a subquery might be unnecessary.

    Please try...
    Note: Removed unnecessary parentheses and reorder some predicates, for readability.
    Code:
    SELECT ET.EMPLOYEEID
         , ET.BSCCCODE
         , VENDORNAME
         , ET.EPURSEID
         , SHORTDESC
         , COUNT(*)      AS TRANSCOUNT
         , SUM(CASE TRANSACTIONTYPE WHEN 1 THEN EPURSEAMOUNT      ELSE 0 END) AS CREDITAMT
         , SUM(CASE TRANSACTIONTYPE WHEN 2 THEN EPURSEAMOUNT      ELSE 0 END) AS DEBITCARDAMT
         , SUM(CASE TRANSACTIONTYPE WHEN 2 THEN CASHAMOUNT        ELSE 0 END) AS DEBITCASHAMT
         , SUM(CASE TRANSACTIONTYPE WHEN 3 THEN TRANSACTIONAMOUNT ELSE 0 END) AS CASHAMT
         , SUM(CASE TRANSACTIONTYPE WHEN 4 THEN EPURSEAMOUNT      ELSE 0 END) AS VOIDAMT 
     FROM  EPURSETRANSACTIONSVIEW ET
         , EPURSES                EP
         , EMPLOYEES              EM 
     WHERE 
           ET.EMPLOYEEID   = EM.EMPLOYEEID
       AND ET.EPURSEID     = EP.EPURSEID
       AND LOCATIONID      = 1
       AND EM.EMPLOYEETYPE = 3
       AND TRANSACTIONDTS
           BETWEEN CONVERT(DATETIME, '2012-03-02 00:00:00', 120)
               AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)
     GROUP BY
           ET.EMPLOYEEID
         , ET.BSCCCODE
         , VENDORNAME
         , ET.EPURSEID
         , SHORTDESC
     ORDER BY
           EMPLOYEEID
         , VENDORNAME 
    ;
    Last edited by tonkuma; 07-05-12 at 06:36.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or, grouping only EPURSETRANSACTIONSVIEW, then add information of EPURSES and EMPLOYEES.

    Example 2:
    Note: Assumed all unqualified columns are in EPURSETRANSACTIONSVIEW.
    Code:
    SELECT ET.EMPLOYEEID
         , ET.BSCCCODE
         , VENDORNAME
         , ET.EPURSEID
         , SHORTDESC
         , TRANSCOUNT
         , CREDITAMT
         , DEBITCARDAMT
         , DEBITCASHAMT
         , CASHAMT
         , VOIDAMT
     FROM (SELECT EMPLOYEEID
                , BSCCCODE
                , EPURSEID
                , COUNT(*)   AS TRANSCOUNT
                , SUM(CASE TRANSACTIONTYPE WHEN 1 THEN EPURSEAMOUNT      ELSE 0 END) AS CREDITAMT
                , SUM(CASE TRANSACTIONTYPE WHEN 2 THEN EPURSEAMOUNT      ELSE 0 END) AS DEBITCARDAMT
                , SUM(CASE TRANSACTIONTYPE WHEN 2 THEN CASHAMOUNT        ELSE 0 END) AS DEBITCASHAMT
                , SUM(CASE TRANSACTIONTYPE WHEN 3 THEN TRANSACTIONAMOUNT ELSE 0 END) AS CASHAMT
                , SUM(CASE TRANSACTIONTYPE WHEN 4 THEN EPURSEAMOUNT      ELSE 0 END) AS VOIDAMT
            FROM  EPURSETRANSACTIONSVIEW ET
            WHERE 
                  LOCATIONID   = 1
              AND EMPLOYEETYPE = 3
              AND TRANSACTIONDTS
                  BETWEEN CONVERT(DATETIME, '2012-03-02 00:00:00', 120)
                      AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)
            GROUP BY
                  EMPLOYEEID
                , BSCCCODE
                , EPURSEID
          )          ET
         , EPURSES   EP
         , EMPLOYEES EM 
     WHERE
           ET.EMPLOYEEID = EM.EMPLOYEEID
       AND ET.EPURSEID   = EP.EPURSEID
     ORDER BY
           EMPLOYEEID
         , VENDORNAME 
    ;
    Last edited by tonkuma; 07-05-12 at 07:00.

  6. #6
    Join Date
    Jul 2012
    Posts
    6
    Tried the updated query on the local machine.It gives the correct output.
    Thanks

    Will update & check the same in the production servers.

    But could please explain me why the following behavior was observed in the production server only.

    I had tried to run my query through SSMS and observed following :

    When the start date was 1st March or less the output was shown immediately with normal cpu usage.
    but when i changed the date to 2nd March or more the cpu usage jumped to 100% and the query did not return any results(execution time was 5+ mins).

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any differences between local machine and production server?
    - Hardware
    - SQL Server Settings/Environments
    - DDLs including Indexes
    - Table's data volume, Matched data volume
    - ?

    ... i changed the date to 2nd March or more ...
    Please state more concretly.
    Your query includes
    Code:
              AND TRANSACTIONDTS
                  BETWEEN CONVERT(DATETIME, '2012-03-02 00:00:00', 120)
                      AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)
    How did you changed? And What results(execution time) did you got?
    Last edited by tonkuma; 07-05-12 at 09:03.

  8. #8
    Join Date
    Jul 2012
    Posts
    6
    I had created a similar local environment for debugging.

    And am using database dump taken from production on 14th June.

    So i guess diff between local & production are close to none.

  9. #9
    Join Date
    Jul 2012
    Posts
    6
    missed the second part

    Changed start date as below

    1. Output Success with execution time < 2sec
    AND TRANSACTIONDTS
    BETWEEN CONVERT(DATETIME, '2012-03-01 00:00:00', 120)
    AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)
    2. 100% cpu usage & No Output till 4 mins
    AND TRANSACTIONDTS
    BETWEEN CONVERT(DATETIME, '2012-03-02 00:00:00', 120)
    AND CONVERT(DATETIME, '2012-04-01 00:00:00', 120)

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    That's really strange.
    Sorry, no idea now.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Considering from the name, EPURSETRANSACTIONSVIEW might be a view.
    If so, was TRANSACTIONDTS column derived column(calculaed from other columns)?

  12. #12
    Join Date
    Jul 2012
    Posts
    6

    Issue resolved

    Above mentioned issue resolved by Updating statistics for transactions table.


    The time-out was because the TransactionDTS was showing last update dts as
    1st March 2012. So any value of startdate beyond it was timing out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •