Query freezes Server: SQL Server 2005 Windows 2003

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Query freezes Server: SQL Server 2005 Windows 2003

Author

Topic

rkadiam
Starting Member

2 Posts

Posted - 2009-04-08 : 12:28:31

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

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-04-09 : 13:30:36

File defragmentation, maybe? How do you grow the file size?
Can you check the mdf file. I'd use contig - http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Why do you need to run count(*) on a table that size?
Can you use sp_spaceused instead? Read the remarks in BOL to see if you could safely use it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-09 : 14:03:59

Here is your problem: "TableA has more than 400 million records (45Gb) – no indexes"

The simple resolution is to add indexes! It'll be a night and day difference, I promise.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-04-09 : 17:08:39

Partition table will help and in long run you can easily archive data. Also, verify if your pagefile is going to 100% due to some bug. I remember one bug in Windows 2003 which takes off pagefile to 100% usage, hotfix is available for this bug. If that applies to you, get hotfix from Ms.

rkadiam
Starting Member

2 Posts

Posted - 2009-04-09 : 17:51:53

Having an index, partitioning tables, etc. are nice to have in my situation. But i am trying to find why the query would work fine on one server (in 8 min i get the record count value) and completely hang the second server to the extent that the only option i have is a reboot.

Few differences between the servers:
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources