SQL Server Performance

sql job runs forever - no blocking

Discussion in 'Performance Tuning for DBAs' started by NewDBA, Jan 16, 2008.

  1. NewDBA Member

    My stored procedure that takes to execute usually for 4-5 hours suddenly runs for 3 days.
    It happened 2 weeks ago , at first I killed that sp 2 times when it exided 24 hours .
    Now I am waiting for it to execute for the 3 d day.
    Stored procedure query big table ( 3 mln records) and inserts about 10-15 records in little table.(2 000 records)
    there are no blocking on server , memory , cpu is normal. No errors in sql or windows event log.
    I am wondering if some can give me some ideas what I can monitor and what could have happen that query is stuck forever?
    Any ideas would be greatly appreciated - because I have no clue at the moment.
    thank you
  2. Adriaan New Member

    Did you try adding OPTION (MAXDOP 1) at the end of your insert query? SQL may be using too many processors for a relatively simple query.
    How long does it take if you run the SELECT part of the insert query by itself?
    How long does it take if you insert just one record?
    How long does it take if you insert into a table variable (or temp table if you're in SQL 7.0) then insert from there into the target table?
    How long does it take if you use the cursor-based approach? (not normally recommended)
  3. NewDBA Member

    thanks for those questions - I will investigate them now.
    but looks like I found some changes done to the table being queried.
    We had vendor of this application working on the database just the day before problem
    I see that new nonclustered index has been added to the table which I am using in my query.
    I cannot drop the index and cannot reindex for the next 3 weeks - production system , cannot afford downtime too often.
    But I have a feeling that index is connected to my problem
    What should I monitor with profiler to confirm it is true?
  4. Adriaan New Member

    Try the different approaches first, without actually inserting into the proper table. See if there are any differences.
  5. NewDBA Member

    I tested couple of scenario: it is not insert that causing the issue - the select statement is the issue.
    Select statement is selecting records from a big table and suppose to insert the output of select statement into table variable.
    Looks like it cannot select - i just killed the query again it was running 16 hours again since yesterday.
    Not sure what happening ?
  6. NewDBA Member

    I seem to be 100 % sure that index added to the table which I query degrade performance.
    I copied db to other server, run my query : it took 5 hours
    cpu was normal
    I added the identical index ( the one that was added just before issue began to happen) and started to run qury again.
    I is running already more then 10 hours and as I started it to execute CPU on server went to 100% and I had to run
    sp_configure ' max degree of paralelist ', 3
    reconfigure to override
    to change usage of processor from available 4 to 3 .
    Could someone explain or at least recommend some article to me for reading why would adding the index would have such a effect on SELECT statement?
    I did find some material about big composite index (which is the case) can slow performance on UPDATE, INSERT, DELETE statements
    but cannot find anything regarding SELECT statement.
    I just need some logical explanation for myself.
    it is sql 2000 sp3a
    thank you
  7. Adriaan New Member

    The sp_configure call is a server-wide, permanent setting - there's no point in doing something that drastic!
    In a query, the same effect can be had by using OPTION (MAXDOP x). This is not server-wide, and it affects only the query in which you include the option.
    Setting max degree of parallellism to 3 doesn't help you: SQL wants to involve multiple processors, where a single processor will do it a lot quicker - so set the option to 1.
  8. AHFMR New Member

    Have you looked at the Execution Plan on the statement prior to the new Index, and compare it to the Execution Plan after the new index was added. This will let you know if the new index is truly you bottle neck.

Share This Page