SQL Server Performance

sudden query slowdown

Discussion in 'Performance Tuning for DBAs' started by crichardson, Sep 7, 2005.

  1. crichardson New Member

    Yesterday, the phone started to ring about an application that was suffering time-out problems. I narrowed the problem down to one query and it used to take 2 seconds to run and still takes 2 seconds on any server apart from the very well resourced production server (the most powerful server we have). It now takes 18 seconds on the production server and, for a comparison, only takes 2 seconds on my PC against a copy of the live database.

    In an attempt to find the problem, I displayed the execution plan on the live server and compared this to the execution plan on my PC... Both the same. I also did 'SET STATISTICS io ON' and the query on both live and my PC were doing the same amount of logical reads. When I ran Profiler traces, I could see that all the extra time (18 seconds instead of 2 seconds) was taken up with CPU time.

    Then I tried the 'nolock' query hint to see what would happen and the query duration came down again on the live server to 2 seconds. It still took 2 seconds on my PC. I tested this again today and it's exactly the same. Our business analysts are trying to work out if the application can take the risk of dirty data in this particular case. Meanwhile, I don't know what caused the sudden performance slowdown on this query. I also don't know why the same query does not suffer a performance slowdown running on different servers against the same data. Activity on the live server has been fairly low and I certainly don't think there is exceptional contension going on. There are no bottlenecks in terms of server resources.

    Here is the simplified query that is at the heart of the issue. I have introduced a temporary table to cut out a lot of unrelated stuff that is going on in the live query so do not take any notice of the unidexed temp table in this.

    SELECT y.FundID, y.LatestAssetsInStrategyUSD,
    (
    SELECT TOP 1
    USDRate
    FROM myDb..FXRates -- with (nolock)
    WHERE Currency = y.Portfolio_Ccy
    AND Date <= DATEADD(D, -DAY(GETDATE()), GETDATE())
    ORDER BY Date DESC
    ) * IsNull(y.[Holding], 0)
    [Holding_USD]
    FROM #yyy y

    Here is the index info...
    index_901578250nonclustered, unique located on PRIMARYrowguid
    PK_FX_Ratesclustered, unique, primary key located on PRIMARYDate, Currency


    Can anyone suggest a way forward in terms of analysing and diangosing the precise cause of the performance slowdown? Of course, I am hoping that by understanding the exact cause of the problem I can establish the most appropriate solution.

    Thanks in advance,
    Clive

  2. Adriaan New Member

    If you say you have a copy of the production database on your workstation, I assume you loaded the database recently? And you probably have an index on the Date column of your FXRates table? I'd try if rebuilding that index in the production database might help.
  3. crichardson New Member

    Yes, a recent backup was used.

    The table in question is relatively small (47,000 rows) and unlikely to benefit much from index defragging but, for the sake of it, I just checked and it was quite fragmented so I rebuilt the indexes. The query is down to 17 seconds instead of 18. So, thanks, it helped but it's not at the heart of the issue.

    Clive
  4. deepakontheweb New Member


    Hi: can you execute 'sp_lock' and check locks information for tables?

    have you added new report/query that uses any of both tables recently?

    Was there any new changes on production server database, that could increase CPU utilization?

    -- if you have similar execution plans on both production server and local pc with similar data, but duration is higher on production then it could be reason of workload that majorly is caused by locks happening on objects.



    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  5. mmarovic Active Member

    Just to summarize suggestions by Adriaan and deepak:

    1. Check locking that's the most probable reason.
    2. Proper indexing may mitigate locking problem. Do you have index on fxRate table starting with date or currency and date colum? The best index would be covering one: currency, date and usdRate in that order.
  6. crichardson New Member

    Yes, I checks sp_lock regularly but nothing indicated that the query should be delayed because of locking. Only shared locks in place most of the time. I can run the query regularly throughout the day and even at night... it still takes 18 seconds. Sometimes it might take less for a short period but 18 seconds is the norm. Workload is a reasonable conclusion but I don't think that is the explanation. If it took 2 seconds to complete sometimes and then longer periods occasionally, I would say it was temporary workload issues. However, it's consistently taking 18 seconds even when the server is not highly utilized. Overall, this database/server is not heavily utilised.

    Indexing is in place already. Clustered on Currency and date. However, the table is not so big for indexing to be a real issue (47,000 rows). Just to be sure, I rebuilt the index anyway but it didn't make any difference as expected.

    Does anyone know where to look to see what SQL Server is doing when it is chewing up CPU on a select query when nocheck is not specified?

    Clive
  7. joechang New Member

    is there a difference in the execution plan between good and bad?
    in any case, i think the better index is: Currency, Date, USDRate
    just try creating a nonclustered index for now
  8. crichardson New Member

    No diff in execution plans.
    If indexing were an issue, it would be poor performance on any server where the same db is restored to. That isn't the case. It's a smallish table so isn't an issue for that reason too.

    Clive
  9. crichardson New Member

    To make this clear... It runs for 18 seconds on the high-spec live server and 2 seconds even on a crappy desktop. No diffs in execution plans. Indexing not an issue. Server resources not an issue. Workload doesn't look to be an issue.

    Using 'nolock' query hint on live gets the duration down to what it was - 2 seconds. Using or not using nolock on any other box doesn't make any significant difference although that isn't conclusive because the other boxes aren't loaded. HOWEVER, up to yesterday, the query duration on the live box was ok even when it was highly loaded - it's never been a problem before.

    When running a trace on the query, the extra time is all cpu time. So, that's why I asked if there was a way to dig down to see what SQL Server was actually doing with that extra cpu time?

    Clive

  10. deepakontheweb New Member


    Have you added new report/query that uses any of both tables recently?

    Was there any new changes on production server database, that could increase CPU utilization?

    -- My mind is stuck on one and only one doubt.. if you have got new things or change in database/ or server .

    -- I can not find any good reason for this sudden increment in execution time..as you said earlier it used to take 2 sec and now 18 sec..having NO difference in execution plans.

    --it's fine that indexes can help in getting better performance but question is earlier without indexes there was best performance.



    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  11. crichardson New Member

    I'm not aware of an new reports/queries that would affect this. I asked the developers the same question and they said no. In any case, if there were another query/report, it would have to be running basically all the time because the 18 second duration of the query I am talking about happens consistently - day or night.

    I don't think there were any other changes that would have caused this but I can't rule it out. I need to rule out change or identify the change that is causing it. I'm not sure how to do that because there's nothing I can think of that would be causing this.

    I agree there isn't a clear reason why the performance should suddenly change but it has and I can't identify any changes that might have led to it.

    As for indexes, they've been in place for ages. I will stick to my thought which is that indexing isn't an issue one way or the other.

    Clive
  12. deepakontheweb New Member

    <br /><br />Can you check server application and system events along with SQL error logs..if you can find anything strange or critical error? Another please verify tables using these 2 statements to be sure: -<br /><br />DBCC CHECKTABLE<br />Checks the integrity of the data, index, text, ntext, and image pages for the specified table or indexed view.<br /><br />DBCC CHECKALLOC<br />Checks the consistency of disk space allocation structures for a specified database. <br /><br /><br />ok, Another suggestion for short time until you get permanent solution, because you said table is not big enough:-<br /><br />DBCC PINTABLE<br />Marks a table to be pinned, which means Microsoft® SQL Server™ does not flush the pages for the table from memory.<br /><br /><br />-- Make yourself confirm about statements before running any one on production server <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br /><br />Deepak Kumar<br /><br />--An eye for an eye and everyone shall be blind
  13. mmarovic Active Member

    It might be the problem with tempDB. Is that query part of stored procedure?
  14. crichardson New Member

    There aren't any consistency problems. I run these overnight as part of db maintenance. Point noted about PINTABLE but I don't think it will help because there's enough free RAM for this table not to be flushed and, even if it is occasionally flushed, the performance hit should only be on the first time the query is run... not all the subsequent executions.

    Clive
  15. deepakontheweb New Member


    I am not sure.. but there could be something wrong which we can not see right now. this situation we can not simulate here at our side to help you finding correct solution.

    BTW, have you tried restarting SQL Server anytime, after when you have started facing problems?

    -- if it was tempdb issue..then it could create problem somewhere else also! are you facing issue only with this query or in others also?

    Mind blowing !!

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  16. joechang New Member

    i am not saying the index explains the sudden slow down,
    i am saying the correct index for the above query is what i said it should be.

    a possible explanation for the difference is that SQL post-sp2 is erratic in taking row or table locks in scan ops, which could explain the difference
  17. crichardson New Member

    The execution plan shows no table scans on the table (it scans on the temp table but that isn't an issue as the temp table is just for the simplified code used in the test). Thanks anyway.

    Deepak, I haven't restarted SQL Server for a few weeks. It's always possible that problems like this go away on a restart but it doesn't help to identify the problem of course. I was reading a few other posts where people noticed a speed up after simply detaching and re-attaching a database! Yes, it's mind blowing. I hate these kind of problems where it just seems to be 'the way it is' with regard to something SQL Server is doing (or not doing) under the covers. I am sure that there are people in Microsoft who have the tools/knowledge to get to the bottom of it but raising calls with Microsoft can be a time consuming business!

    Clive
  18. deepakontheweb New Member


    This is fine..if you have not restarted sql server from weeks..even i don't believe restarting sql server frequently.. BUT if there is a problem and you have chance to restart server..there is NO harm as well in doing so.

    if not restart, can you try to empty cache and check difference..

    DBCC dropcleanbuffers
    DBCC freeproccache


    Well, any idea if you have changed SQL Memory settings recently? what are the current settings?

    -- Sometime back, I got call from a client that thier server that had 12 Gb of RAM..was facing almost smiliar issues.. it was resolved when i checked found their DBA booked all 12 GB for SQL (in max memory settings)leaving NO room for OS.




    Deepak Kumar

    --This posting is provided “AS IS” with NO rights for the sake of knowledge sharing.
  19. crichardson New Member

    No differences made re. dropcleanbuffers freeproccache. Ran the query several time after.

    Memory settings are unchanged and they are properly configured.

    Today, it's consistently taking 16 seconds. Two days ago it was consistently taking 18 seconds.

    I will plan for a service restart but I would love to know what the issue is.


    Clive
  20. Adriaan New Member

    Did you check if the production server has anti-virus software running? Excluding all database files can help.
  21. crichardson New Member

    Adriaan, that's a good point but I went through this last year with the server management team and made sure that the db files were excluded. However, I will ask them to re-check this in case anything has changed.

    Clive
  22. deepakontheweb New Member


    Hi Clive: Is problem got resolved or you are still facing issues? I am little qureious to know what was the reason.

    --Thanks


    Deepak Kumar

    --This posting is provided “AS IS” with NO rights for the sake of knowledge sharing.
  23. bcamp New Member

Share This Page