Post reply

Severe Performance Problem

  • December 9, 2011 at 12:47 am

    #104103

    I am having sever problems with a large database that has been running fine for a number of years. Two days ago all the running processes ground to a halt and queries, stored procs etc started taking ages to run. The database stats are updated regularly and last night all fragmented indexes were re-built. Queries which were taking milliseconds previously are now taking hours to run.

    Our hardware guys assure us there are no hardware problems but our Quest tool is complaining about I/O and memory issues.

    Has anyone got any suggestions?

    I have 300 people sat twiddling there thumbs as the system is unusable.

  • December 9, 2011 at 1:56 am

    #1419117

    Hi Steve,

    Can you check what other activities are hampering box?

    Do you see any other process on the box?

    Have you tried rebooting once as the page file might cause this issue?

    What is size of your temp db?

    Did you noticed any blockings?

    Thanks,


    - Pradyothana     http://www.msqlserver.com

  • December 9, 2011 at 2:08 am

    #1419125

    steve.clark 39713 (12/9/2011)

    I am having sever problems with a large database that has been running fine for a number of years. Two days ago all the running processes ground to a halt and queries, stored procs etc started taking ages to run. The database stats are updated regularly and last night all fragmented indexes were re-built. Queries which were taking milliseconds previously are now taking hours to run.

    Our hardware guys assure us there are no hardware problems but our Quest tool is complaining about I/O and memory issues.

    Has anyone got any suggestions?

    I have 300 people sat twiddling there thumbs as the system is unusable.

    This pretty much sounds as an I/O issue. What is the average row cont in sys.dm_io_pending_io_requests ?? If row count is continuously high, you have got the I/O issue.

    Sujeet Singh

  • December 9, 2011 at 3:26 am

    #1419161

    Divine Flame (12/9/2011)

    What is the average row cont in sys.dm_io_pending_io_requests ??

    On SQL 2000?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2011 at 3:29 am

    #1419162

    steve.clark 39713 (12/9/2011)

    Two days ago all the running processes ground to a halt and queries, stored procs etc started taking ages to run.

    What changed 2 days ago? Any patches, any hardware changes, any changes of any form whatsoever?

    If you query sysprocesses, what are the queries waiting on (it's lastwaittype if I recall). What's the top waits in DBCC SQLPERF (waitstats)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2011 at 3:42 am

    #1419169

    GilaMonster (12/9/2011)

    Divine Flame (12/9/2011)

    What is the average row cont in sys.dm_io_pending_io_requests ??

    On SQL 2000?

    lol :-D, No way . He has not mentioned that he is using SQL Server 2000, so I assume he is using SQL Server 2005 or 2008.

    Sujeet Singh

  • December 9, 2011 at 3:51 am

    #1419174

    Hi All,

    Thanks for you comments so far. The TempDB is fine and there is plenty of disk space on the server. Yep i'm using 2005.

    The only thing I can think that changed is adding an index to one table two days ago. i've just dropped this after putting the DB in single user mode( couldn't get a lock on the table due to so many blocked processes) and this hasn't improved it.

    I've now cleared the cache as I am running out of ideas.

    Our hardware guys insit it is missing indexes which is causing the I/O issues but I can't believe the system can go from fully functional to a halt in one day!

  • December 9, 2011 at 4:03 am

    #1419182

    average row cont in sys.dm_io_pending_io_requests

    is 80-90

    I usually run this routinely

    SELECT sqltext.TEXT,

    req.session_id,

    req.status,

    req.command,

    req.cpu_time,

    req.total_elapsed_time / (60*060*60 *60) as [hours]

    FROM sys.dm_exec_requests req

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

    order by req.total_elapsed_time desc

    usually there are maybe 2-3 processes running at any one time, I now have over 70

  • December 9, 2011 at 4:05 am

    #1419185

    Divine Flame (12/9/2011)

    GilaMonster (12/9/2011)

    Divine Flame (12/9/2011)

    What is the average row cont in sys.dm_io_pending_io_requests ??

    On SQL 2000?

    lol :-D, No way . He has not mentioned that he is using SQL Server 2000, so I assume he is using SQL Server 2005 or 2008.

    This is the SQL Server 7,2000 forum.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 9, 2011 at 4:07 am

    #1419186

    steve.clark 39713 (12/9/2011)

    Hi All,

    Thanks for you comments so far. The TempDB is fine and there is plenty of disk space on the server. Yep i'm using 2005.

    The only thing I can think that changed is adding an index to one table two days ago. i've just dropped this after putting the DB in single user mode( couldn't get a lock on the table due to so many blocked processes) and this hasn't improved it.

    I've now cleared the cache as I am running out of ideas.

    Our hardware guys insit it is missing indexes which is causing the I/O issues but I can't believe the system can go from fully functional to a halt in one day!

    Did you caheck what resources your queries are waiting for as instructed by Gail ?

    SELECT SESSION_ID,COMMAND,WAIT_TYPE,LAST_WAIT_TYPE,* FROM SYS.DM_EXEC_REQUESTS

    Also, check the data from above provided DMV (sys.dm_io_pending_io_requests) to check if it is an I/O issue.

    Sujeet Singh

  • December 9, 2011 at 4:14 am

    #1419191

    It's mostly

    RESOURCE_SEMAPHORE

    and a few are

    PAGEIOLATCH_SH

  • December 9, 2011 at 4:22 am

    #1419196

    steve.clark 39713 (12/9/2011)

    Yep i'm using 2005.

    So why did you post in the SQL 2000 forums? Doing so means we're going to assume you're actually using SQL 2000 and limit solutions and suggestions to ones that work on SQL 2000.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2011 at 4:24 am

    #1419198

    steve.clark 39713 (12/9/2011)

    It's mostly

    RESOURCE_SEMAPHORE

    and a few are

    PAGEIOLATCH_SH

    Memory and IO respectively.

    Have data volumes been increasing?

    Is this by any chance a virtual machine?

    Yes, missing indexes can cause high IO, but it's a slow degradation as data volumes increase, not a 'fall off a cliff'

    Is everything slow or are there a few queries that are causing all the other problems?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2011 at 4:38 am

    #1419208

    I posted this morning after being up all night trying to resolve the issue.

    My mistake, my apologies

  • December 9, 2011 at 4:40 am

    #1419210

    I posted this morning after being up all night trying to resolve the issue.

    My mistake, my apologies

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply