sql 2000 a/p cluster on SAN slow performance CPU 65-100.

Question

sql 2000 a/p cluster on SAN slow performance CPU 65-100.

AlexSQLForums

SSChampion

Points: 14277
More actions
August 4, 2006 at 7:46 am

#191773

I've got a sql 2000 EE cluster active/passive with 4CPU 4GB RAM attached to EMC CLARION SAN with fiber connection.
Yesterday our developers rolled out a huge amount of updates to one of the websites.
Now sqlcluster is acting very very slow. CPU is jumping between 65 and 100.
I run a trace and see most queries duration is less than 1 second.
I don't know what to do.
Should i change dynamic memory allocation to fixed and give it 3GB.
Or should i raise the minimum query memory.

How would one of you troubleshoot a slow acting cluster?
Which trace filters should i be using?
Thanks for all your help

Alex S
August 7, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 1

First things first. If this cluster is dedicated to SQL Server then you can configure your SQL Server cluster nodes to use more memory. 3 Gb fro SQL Server and 1 Gb fow Windows

Add the /3GB and /PAE flags to the boot.ini file on each node
exec sp_configure 'awe enabled',1
exec sp_configure 'max server memory', 3072
exec sp_configure 'min memory per query', 2048
exec sp_configure 'min server memory', 3072
restart the passive node
restart the active node (it will fail over)
fail back to the original active node

Next, since the developers made changes (you do not specify whether or not they affect the database) I'll include my 'short list' for tuning there:

Round I

DBCC UPDATEUSAGE

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round II

DBCC DBREINDEX

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round III

Profiler

Query Hints

Now for the SAN ... check your disk configuration. The database data should reside on it's own set of LUNs.The database transaction logs should resdie on it's own set of LUNs and the database backups on its own set of LUNs. Based on how large and how heavily tempdb is used you may want it on its own set of LUN(s).

Also, for a SANs Meta-Luns are better for performance than just plain old LUNs.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

Thank you very much for providing detailed steps.

Alex S

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Rudy

PAE/AWE won't have any effect if you have 4GB of RAM or less. Only the /3GB switch is required here.

I will also add that if Alex does decide to use AWE, he should remember to apply the post-SP4 hotfix before proceeding.

John

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

You are correct John. I copied the build steps from one of my clusters that has 8 Gb of RAM. However the SP4 issue is something to look at on a case by case basis though ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

Actually all is fine with our cluster and SAN.

It was lack of indexes on some (50 million row) tables and one operation in particular that was created with a cursor instead of a SELECT CASE.

Developers tested it in dev but didn't realize that this operation would be fired 200 times a minute.

thanks for all your replies

Alex S