Help! Performance Hell!! - 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)

Help! Performance Hell!!

Author

Topic

powellbound
Starting Member

23 Posts

Posted - 2008-08-27 : 19:52:20

Hi all,

We have a 2005 instance on our primary server running 64 bit WinServer2003 R2 standard. 4 dual core XEON processors, 16gb of ram and the system is regularly being brought to its knees. We do have 2 primary dbs running on the one Sqlserver instance. One has 2 tables with over 300 million rows, and 2 tables with over 42 million rows. the tables have only 7 columns each so the respective dbs are not too crazy in size 100gb and 24gb.

Whenever we do anything, the whole box dies. I mean you cannot even do a simple insert into another table or open another app when it is busy. Personally, I think it is the OS and the box itself and not SQLServer but I am hoping someone out here will have some insight on how to further identify what is really going on and then how to correct it.

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-27 : 20:49:13

Trackit down with

1) Performance monitor
a)Memory counters
b)Disk Counters
c)CPU counters

You need correct indexes for those tables and do you defrag index ? You can do table partitioning across separate FG to different drives
to increase performance of it.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-08-29 : 18:57:11

One other approach, in addition to the ones listed, is to use Profiler to identify the looooonnnng running queries. Next investigate the Execution Plan to see where the time is spent and see if you have an indexing issue. With a table that size, a missing index could easily lead to a table scan which would have the performance impact that you are seeing.

=======================================
It is impossible to imagine Goethe or Beethoven being good at billiards or golf. -H.L. Mencken, writer, editor, and critic (1880-1956)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources