SQL Server Performance

SQL 2k SP inconsistancies

Discussion in 'Performance Tuning for DBAs' started by tomhasquestions, Jan 21, 2003.

  1. tomhasquestions New Member

    Greetings again,
    I am plagued by a new problem that is driving me crazy and I would appreciate any suggestions. I have a stored procedure on win2k SP3 SQL Server 2000 SP3 (existed in SP2) that typically (99% of time) runs in 20 seconds - 2 minutes. This time differnce is expected due to variances in the data load. Ocassionally, typically every few days, in an unpredictable fashion, the job will not complete. Normally this is noticed after the job has been running for 1-2 hours at which point we must stop and restart it because the data is very time sensitive. Presumably if we allowed the merger job to run indefinatley it would finish, although we have waited in excess of 2 hours with no completion. I had hoped applying SP3 would correct the problem but it has not. Yes, i have read the 'SP with different run times - help, please ' post from a few days ago.

    - I do not see any locks that could account for the delay
    - the data is the same type during these periods
    - the outage periods are not times of particularly high volume
    - normally the previous job run will be within normal time excpectations, although it is occasionally very high (14 min today)
    - I upgraded to sql server SP3 and the problem has reoccured
    - due to the inconsistant natuere of the problem I have not been able to isolate it in profiler, although that is my next step
    - there are no errors in the SQL logs or in the event viewer
    - CPU usage is usually low even during these points

    Environment details:
    Win2k, Standard SQL server 2k sp3. There are 3 database servers running seperate instances of the same application. For simplified anaysis of the data a few key tables (5) are tranfered every 2 minutes (servers are offset from each other) to a single analysis server. The tranfer is done via DTS and appends a database ID (tinyint) column to the start of each row, allowing identification of the source. The data ends up in a 'temporary' holding database. A seperate job, running every minute, performs a few additional calculations adding a few extra rows and then moves the data to the 'real' analysis database. once data is moved it is purged from the temporary holding database. Only a few users connect to the system (< 5) and their workload does not seem to correlate to the problems.

    If the job is stoped and restarted it will complete in a 'normal time' - slightly longer due to the accumpulated data.

    Any Thoughts?

    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  2. satya Moderator

    Well by default if you run PROFILER you will get to know what kind of activity on the server (during slow process of the SP job). This would be the starting point for you to define the status.

    Also assess about other process/jobs during this slow performance of that SP.

    HTH

    Satya SKJ
  3. tomhasquestions New Member

    aside from this SP during the problem times there is very little activity on the system - only 'background noise'. The server is dedicated to this process so it continues to receive data from each server and approximately every 15 minutes somone will run a few queries that take 2-6 minutes to examine new, and occasionally old, information. These queries usually return in regular, possibly slighly longer, times during the trouble periods. If examining old data uses would not see a problem at all. So the server itself seems healthy and responsive, but the SP is DOA.

    -Tom

    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  4. bradmcgehee New Member

    As satya suggests, a Profiler trace of the fast and slow activity would be useful. In addition, what does your execution plan look like for this query?

    In addition, how often to you rebuild your indexes? Also, do you update your statistics at all manually?

    I would need more information to help diagnose this problem. My best guess is that (assuming that it is not a bug) for some reason, indexes are not being uses correctly all the time by the optimizer. If this is the case, you need to identify which indexes aren't being use properly, and you may need to consider using appropriate hints to ensure that they are used as they should be.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. tomhasquestions New Member

    The indexes have just been built aprroximately two weeks ago (I had a question on use of profiler, which helped alot BTW) and have not been defraged since. The problem has happened several times since then, and density is still high, so fragmentation is not likely the cause. The data processed by this job/SP is very consistant, the behavior *shoul* be the same on every run as it does not change actions.

    Any tips on what I should be looking for within Profiler? It has now happened twice today (unusual) and I had profiler running, but I did not have the execution plan selected (I have corrected this)

    The execution plan is complex and difficult to generate as the SP uses several temp tables and loops (one run for database1, then db2, then db 3). At over 300 lines it is not immense, but not simple either - but there is not a single execution plan for the entire thing. what specifically would be of interest? (sorry, I am new to SQL)

    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  6. tomhasquestions New Member

    Interesting - it happened yet again! I noticed that under currenct activity is a IOPAGELATCH_EX error. Following the advice athttp://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=7164 I checked the disk queue and the average is 2.5, which doesnt seem terribly high.. (max is 33)

    I started profiler with batch completed, statement completed, and executaion plan all selected for 5-10 minutes and have not seen any activity related to my procedure - a little bnackground noise but thats all. CPU 1%.


    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  7. satya Moderator

    I was dealt with that post and do refer to the points stated on the link and work on PERFMON counters too.

    HTH

    Satya SKJ
  8. bradmcgehee New Member

    Sometimes, when stastics get out of date, this can cause problems (even when you have the database set for auto update). So you might give this a try.

    If this doesn't help, then we still need to identify which part of the query is causing the problem. Perhaps you can send us (or me) a copy of the execution plan when things are running correct, and if possible, an execution plan of when things are not working. Also, send a copy of the Profiler log when the problem is happening.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  9. tomhasquestions New Member

    Thank you for your help - i think <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I've gone back and re-examined my indexes and found a few key indexes badly fragmented that probably are responsible for my dilema. I am not sure why fragmented indexes would have caused the problem to go from every few days to several times a day overnight (as it did), but this seems a good bet. I am dropping all indexes now and will completely rebuild them. I will dig more into the profiler if the problem persists beyond. <br /><br />As my database is used primarily for writes - I am going to change the default fill factor to 80% and hopefully that will also reduce the problem's impact.<br /><br />As a related but OT question.. in the SQL i created to capture all my indexes i noticed several entries like the following.. Do i really need to do these or will statistics be sufficiently generated via other means?<br /><br />IF ((@@microsoftversion &gt; 0x07000000)<br />EXEC (CREATE STATISTICS [hind_6786786786_2a .. ] ON ... <br />GO<br /><br />The table/field combinations are not ones selected by indexes, so why create statistics on them? legacy from old indexes perhaps?<br /><br /><br /><br />-- Tom<br />Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  10. bradmcgehee New Member

    Increasing your fillfactor may help, as writes are the biggest factor in your situation.

    Any index that begins with "hind" is a hypothetical index used by the Index Wizard for tuning. Normally they are deleted, but not always. If you want, you can delete them and not worry about them.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page