Post reply

Query freezes Server: SQL Server 2005 Windows 2003

  • April 8, 2009 at 10:15 am

    #132677

    I have a server freezing issue which I have no clue where to start looking for.

    Environment:

    SQL Server 2005 Enterprise SP3, Max server memory : 15Gb, Windows Server 2003 Enterprise SP2, AWE enabled, 32Gb RAM

    Here is the description of the issue:

    Database with only 1 table: TableA

    TableA has more than 400 million records (45Gb) – no indexes

    Query that causes the server to freeze every time: select count(*) from TableA

    The server has other databases being accessed by users to run reports.

    Within few seconds after starting the execution, the page file usage increases gradually and reaches the maximum 15Gb and then the server freezes.

    The server Physical-Disk performance counters all read 0’s after the server freezes. I cannot remote desktop to the server, cannot connect to any databases on the server.

    I can however ping the server and get response instantly. The only option to get the server back is a hard reboot.

    I tried copying the database to a different, but similar server and I don’t have any issue there when running the same query.

    Any suggestions on why this might be happening and how to resolve this issue on the first server?

    Thank you

  • April 8, 2009 at 10:38 am

    #974506

    Other than "Don't do that", not a clue at the moment. For clarification, are there ANY differneces between the servr that freezes and the one that it didn't freeze. CPU's, OS, SQL Server build, disk configuration, anything. If so, what.

  • April 8, 2009 at 11:18 am

    #974533

    Do you see anything in Error logs just before this happened?

  • April 9, 2009 at 11:10 am

    #975251

    The only difference i could find between the two servers :

    Server that freezes: Intel(R) Xeon(R) CPU X5355,@2.66GHz, 32 Gb Ram

    Server that didn’t freeze: Intel(R) Xeon(R) CPU E5345 @2.33 Ghz 16.0 Gb of Ram

    OS, SQL Server build(9.00.4035.00) are the same.

    Thanks

  • April 9, 2009 at 11:46 am

    #975308

    Noting in the error log and SQL Server Event logs about the error.

    I checked the defaut trace file - pasting below the record that appears to be around the time when the server hung up.

    Server Memory Change 2 2009-04-07 16:32:35.557 1 - Increase

    I can also see another record around the time frame when the query execution started: it appears to be accessing the tempdb.

    Thanks

  • April 9, 2009 at 12:13 pm

    #975333

    Single Proc? Dual Proc? Quad Proc? Single Core? HyperThreaded? Dual Core? Quad Core?

    Disk subsystems? Are they then same/similar?

  • April 9, 2009 at 1:53 pm

    #975403

    What are you min/max memory settings on both systems?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 9, 2009 at 3:42 pm

    #975468

    Lynn,

    Our infrastructure guy said that there is not difference in the disk configurations between both the servers.

    However I found other differences:

    Server that freezes (32 bit)

    AWE enabled

    SQL Log on as: Local System

    Lock Pages In Memory – no accounts added

    Min/Max memory: 5Gb/15Gb

    Server that didn’t freeze (32 bit)

    AWE enabled

    SQL Log on as: ServiceAccountSA

    Lock Pages In Memory enabled for ServiceAccountSA

    Min/Max memory: 6Gb/10Gb

    Is it possible that this setting could have affected how memory is being handled..

  • April 10, 2009 at 8:34 am

    #975797

    RK (4/9/2009)

    Lynn,

    Our infrastructure guy said that there is not difference in the disk configurations between both the servers.

    However I found other differences:

    Server that freezes (32 bit)

    AWE enabled

    SQL Log on as: Local System

    Lock Pages In Memory – no accounts added

    Min/Max memory: 5Gb/15Gb

    Server that didn’t freeze (32 bit)

    AWE enabled

    SQL Log on as: ServiceAccountSA

    Lock Pages In Memory enabled for ServiceAccountSA

    Min/Max memory: 6Gb/10Gb

    Is it possible that this setting could have affected how memory is being handled..

    You may want to see if you can reconfigure the "freezing" server to use the same service account and set Lock Pages In Memory .

  • April 10, 2009 at 9:36 am

    #975849

    Hi,

    This is a long shot but you may wish to try running your select query using the nolock hint to see if that executes without issue.

    If so, it would suggest that locking contention may be the source of contention on your production server.

    select count(*) from TableA with nolock

    Table Hints reference: http://technet.microsoft.com/en-us/library/ms187373.aspx

    Given that there are no indexes on the table in question it is quite likely that an index scan is occuring but it may be worth reviewing the execution plans from each server in order to be 100% certain that the two queries are being executed in identical fashion.

    John Sansom (@sqlBrit) | www.johnsansom.com

  • April 16, 2009 at 3:32 pm

    #979279

    RK

  • April 16, 2009 at 3:33 pm

    #979281

    I kept trying by changing the configurations on the server and noticed the following behavior:

    When SQL Min/Max memory is changed to 0Mb/16Mb the query worked fine. BUt i cannot use this config. b'coz it slowes down the SSRS server running on the server.

    But when I change it to 0Mb/5Gb, the query freezes. The page file usage shoots up from 0 to 5 Gb gradually and then the server goes to a drag and then finally no response. I am begenning to think that this is more of a memory configuration issue - but haven't had any luck with a workable configuration. I have PAE/AWE enabled with 32Gb of RAM.

    Thanks

  • April 16, 2009 at 3:34 pm

    #979282

    I kept trying by changing the configurations on the server and noticed the following behavior:

    When SQL Min/Max memory is changed to 0Mb/16Mb the query worked fine. BUt i cannot use this config. b'coz it slowes down the SSRS server running on the server.

    But when I change it to 0Mb/5Gb, the query freezes. The page file usage shoots up from 0 to 5 Gb gradually and then the server goes to a drag and then finally no response. I am begenning to think that this is more of a memory configuration issue - but haven't had any luck with a workable configuration. I have PAE/AWE enabled with 32Gb of RAM.

    Thanks

  • April 16, 2009 at 3:35 pm

    #979283

    I kept trying by changing the configurations on the server and noticed the following behavior:

    When SQL Min/Max memory is changed to 0Mb/16Mb the query worked fine. BUt i cannot use this config. b'coz it slows down the SSRS server running on the server.

    But when I change it to 0Mb/5Gb, the query freezes. The page file usage shoots up from 0 to 5 Gb gradually and then the server goes to a drag and then finally no response. I am beginning to think that this is more of a memory configuration issue - but haven't had any luck with a workable configuration. I have PAE/AWE enabled with 32Gb of RAM.

    Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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