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 2000 Forums
 SQL Server Administration (2000)
 Need Database Configuration Help

Author  Topic 

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-21 : 02:57:29
Hi friends,

First I will give the details of Server.

1) Model - LH600 (HP)
2) 6 GB RAM
3) 6 Xeon Processor.
4) 3*36 GB SCSI HDD - RAID 5 (OS- Windows 2000 Advanced server Installed)
5) External Storage 400 GB - Hitachi 905 Series (40 GB Partition)
(SQL Server Enterprise Edition installed).

Memory configuration is dynamic.

After the database has shrank it came to 8369.81 MB (8 GB).
I made to 19 GB to avoid unnecessary growth.
Database setting “Torn page detection” has been disabled.
Recovery mode is made to "Simple".
AWE has been enabled.

CPU utilization is going to 90-100% on peek time.
Sometime buffer cache hit ratio drops to 5%.

Need help.

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-21 : 06:40:06
I missed one thing to say.

Here we have clusters configured (2 instances) on our server.
Two instances (Servers) running simultaneously and connected to the database server installed in Storage box.
If one is down the next can take care the user request automtically. This is the only benifit here. Availability is made high.

I need the also performance advantage from it. User request has to be balanced through the clusters. But it is not happening here.

Can anyone give some idea?
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-22 : 02:30:58

Does SQL Server support Load balance on clusters?
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-29 : 07:46:14
Please anybody tell me why the CPU utilization going to 100% constantly.

Why don't you answer my questions? Is there any problem with my questions?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-04-29 : 08:15:04
What speed are your network cards?

Sre you running any replication? If so, what type of replication?
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-29 : 08:20:52
No replication.
speed of network cards are 100 Mbps.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-04-29 : 08:39:14
The fact that they are 100Mbps will slow things down as your SQL Server will always be trying to deal with network traffic...
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-29 : 08:45:01
quote:
Originally posted by bobanjayan


Does SQL Server support Load balance on clusters?


It sort of supports load balancing. You have to set up an active/active cluster. Microsoft actually has a good white paper on this. Go to the SQL Server section on Microsoft and search for active/active clustering.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-29 : 08:47:00
quote:
Originally posted by bobanjayan

I missed one thing to say.

Here we have clusters configured (2 instances) on our server.
Two instances (Servers) running simultaneously and connected to the database server installed in Storage box.
If one is down the next can take care the user request automtically. This is the only benifit here. Availability is made high.

I need the also performance advantage from it. User request has to be balanced through the clusters. But it is not happening here.

Can anyone give some idea?






You have to have four instances. Basically you have two on each server (four nodes). Active/Active in SQL Server is actually four node (2 passive/2active). You segregate your data and achieve the load balancing that way. Make sense?

You just need to make sure each box can handle both instances in a failover scenario. Be careful with this part.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-29 : 08:51:55
quote:
Originally posted by bobanjayan

Hi friends,

First I will give the details of Server.

1) Model - LH600 (HP)
2) 6 GB RAM
3) 6 Xeon Processor.
4) 3*36 GB SCSI HDD - RAID 5 (OS- Windows 2000 Advanced server Installed)
5) External Storage 400 GB - Hitachi 905 Series (40 GB Partition)
(SQL Server Enterprise Edition installed).

Memory configuration is dynamic.

After the database has shrank it came to 8369.81 MB (8 GB).
I made to 19 GB to avoid unnecessary growth.
Database setting “Torn page detection” has been disabled.
Recovery mode is made to "Simple".
AWE has been enabled.

CPU utilization is going to 90-100% on peek time.
Sometime buffer cache hit ratio drops to 5%.

Need help.






You need to set up profiler and analyze what's going on. Set up two profilers to start out with (making sure you save the results somewhere else).

Set up one to detect anything running over a few seconds. Start at 10, you can lower from there.

Set up one to look at high CPU. You need to determine what's high. I would say during the day anything over 3 seconds is high with that many processors.

Is SQL Server the only thing running? Have you done a diagnostic on the server memory? A full diagnostic takes awhile on the HP server, and would probably have to be scheduled at night.

Last thing, like Rick said 100 mbps is kind of slow. It doesn't sound from what you are saying though that it's your problem. What's your network utilization setting at?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2004-04-29 : 08:54:58
100Mbps would make it slow as from the looks of the setup SQL Server is running on the external drive...
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-04-29 : 09:16:53
Clustering...load balancing...no...never done it....but it's meant for failover...

Both boxes are not "live"

You need to help us with what processes you are running...

A 6 way box with 6gb of ram...damn, most of the db would be in memory....

My guess is your doing some serious data imports or exports...

Also how many transaction a minute are you looking at?

What are the length of the transactions?

Are you experiencing blocking (well that would decrease cpu I guess...)



Brett

8-)
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-29 : 09:28:43
RickD,
Out client/server network is 100mbps. The external storage box is connected through Fiber channel.

derrickleggett,
SQLServer is the only thing running on the server.

Will it support load balancing like Oracle RAC does?

Anyway I am taking a print of this follow waht ever you said.
And give you the comments.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-04-29 : 12:36:49
SQL Server doesn't do load balancing. Clustering is used for fail over purposes. Not sure about SQL Server 2005 though.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-29 : 14:28:49
The only way you can achieve load balancing is by an active/active cluster, which isn't really load balancing at all. It's just division of databases.

I haven't read anything about 2005 supporting load balancing. That would be an interesting thing to find out though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2004-04-29 : 14:43:40
You mention a 40GB partition on your Fibre Channel setup.

Are your TX-Logs and Data all on this one 40GB partition?
Is it one big RAID Array?
What type of RAID array is it?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-30 : 02:16:13
As I said, We are using External Storage 400 GB - Hitachi 905 Series
and it is partioned by 40 GB. Rest of the space is not used now.
Transaction logs and Data files are stored in the same partition.

2 Servers connected to this shared disk.

Database Server 1(Users are connected here So CPU is 80-100%)
-----------------
X86 Family 6 model Stepping 1
AT/AT COMPATIBLE
6061352 KB RAM
Physical Address Extention

Database Server 2 (Users are not connected)
--------------------
X86 Family 6 model Stepping 0
AT/AT COMPATIBLE
6298920 KB RAM
Physical Address Extention


I am not sure how many transaction is done in a minute. But around 270 - 300 users are connected simultaneously.
All users are connected through Citrix.

We are not doing any serious data exports or imports.
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-04-30 : 02:22:08
And also,
Server have 3 Adaptors,
D-Link DGE-550T Gigabit Ethernet Adapter
HP Net Server 10/100TX PCI LAN Adapter
HP Net Server 10/100TX PCI LAN Adapter #2
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-30 : 08:54:26
Bob, have you set up any profilers at all? I really think this is the only way you are going to get to the bottom of the issue. If you want to approach it from hardware, you could fail over to the other node and see it it makes a difference. That could lead you to doing diagnostics on the box.

Otherwise, you need to set up profiler and find out what's taking a long time to run and eating up CPU time. Let me know if you need help setting up profiler.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

bobanjayan
Yak Posting Veteran

67 Posts
Posted - 2004-05-03 : 01:42:59
Dear Derrick,
Yes sir, if you can, it will be great help to me.

Thanks a lot.
Boban.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site