Query freezes Server: SQL Server 2005 Windows 2003

Question

Query freezes Server: SQL Server 2005 Windows 2003

RK-383011

Old Hand

Points: 313
More actions
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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

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

RK-383011 Old Hand Points: 313 More actions · Answer 3

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

RK-383011 Old Hand Points: 313 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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

Disk subsystems? Are they then same/similar?

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 6

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

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

RK-383011 Old Hand Points: 313 More actions · Answer 7

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..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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 .

John.Sansom SSCertifiable Points: 7335 More actions · Answer 9

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

RK-383011 Old Hand Points: 313 More actions · Answer 10

RK-383011

Old Hand

Points: 313

April 16, 2009 at 3:32 pm

#979279

RK

RK-383011 Old Hand Points: 313 More actions · Answer 11

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

RK-383011 Old Hand Points: 313 More actions · Answer 12

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

RK-383011 Old Hand Points: 313 More actions · Answer 13

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