System Hanging - SQL Server Forums

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

SQL Server Administration (2005)

System Hanging

Author

Topic

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-10 : 21:09:41

SQL Experts,

We are experiencing a system hanging in one of our clients with a data of 60GB. Everytime they are generating a ad-hoc type of reports or open a module that retrieves a big volume of data they're experiencing a system hanging. Somehow I know the reason of this concern, what I wanted to ask is can you give me some suggestions or an alternative way to improve the retrieval of the data? Specially for those ad-hoc type of reports. I alrady tried to use the Database Engine Tuning Advisory to fine tune some SQL queries and views used in our system. I also appy the recommended creation of Indexes and SQL Statistics.

Server Specifications
Intel Clone server 3.06 ghz dual core
8g memory
146 sata hd
2 gigabit lan cards

Database version
Microsoft SQL Server 2005

Operating System
Windows 2003 Server R2 Standard x64 Edition SP2

Size of Current Data58gb ang data + 2gb na transaction log = total of 60 gb

Others:
Referrential Integrity is not fully implemented.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 22:48:36

Do you have a separate reporting environment? If not, get one as soon as possible. This type of scenario is common on largish databases where reports run on the same database as production. Even if you can't afford the hardware, point the reports to a different database on the same instance. To keep the reporting data current, use transactional replication.

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

Subscribe to my blog

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-11 : 00:51:32

quote:
Originally posted by tkizer

Do you have a separate reporting environment? If not, get one as soon as possible. This type of scenario is common on largish databases where reports run on the same database as production. Even if you can't afford the hardware, point the reports to a different database on the same instance. To keep the reporting data current, use transactional replication.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server

No we dont have.
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

www.itchytech.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-11 : 00:53:38

Then make it your number one priority to create one.

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

Subscribe to my blog

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-11 : 01:21:08

The possible problem for this is the availability of the data if we seperate into two server the generation of the reports and the transaction..?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-11 : 13:19:05