Clustered environment considerations

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

High Availability (2005)

Clustered environment considerations

Author

Topic

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-26 : 14:28:05

All, I am new to clustering and I have a couple of questions. This is regarding a 2 node cluster.
For basic housekeeping (backups, index rebuilds, update statistics etc) is the approach different for a clustered environment? Is it typical to duplicate the work across both nodes. And are there any Gotchas that I should be aware of when doing performance tuning in a clustered environment?

I have been asked to look into a clustered environment next week and come up with ways of improving the performance and establish some housekeeping jobs (there has been no DBA involvement prior).

I want to make sure that I have all my ducks in a row before I approach this.

Thanks!

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 14:40:21

Clustering does not impact performance. It is not a load balancer.

I don't do anything different as far as maintenance jobs go on a cluster.

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-26 : 14:55:58

Thanks Tara, I am reading the Clustering white papers from Microsoft now. I think I was confused about what a Clustered environment entailed but I am starting to understand that it relatively transparent to the SQL Server. I was getting Clustering confused with Database mirroring.

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:16:49

If your management thinks that clusering or database mirroring will help performance in any way, then they need to be educated on those technologies.

Let us know if you have more specific questions about clustering as there are a bunch of us with a lot of experience with it.

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-26 : 15:22:03

I think I may have communicated poorly. I have been asked to look at performance for a customer that has SQL Server on a two node cluster. The customer is having performance issues, not related to the cluster - but since I am new to clustered environments I just wanted to make sure I understood before I took on the project. It is more likely, based on what I have been told, that they are not doing ANY maintenance at this site which would explain the performance degradation over time.

Thanks!

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:26:59

I'd start with this:


SELECT 
    OBJECT_NAME([object_id]) AS ObjectName,
    index_id AS IndexId, 
    avg_fragmentation_in_percent AS FragPercent, 
    record_count AS RecordCount, 
    index_type_desc AS IndexType,
    alloc_unit_type_desc AS AllocUnitType
FROM sys.dm_db_index_physical_stats (DB_ID('DatabaseNameGoesHere'), NULL, NULL, NULL, 'SAMPLED')
WHERE 
    avg_fragmentation_in_percent > 10 AND 
    avg_fragmentation_in_percent < 100 AND 
    index_id > 0
ORDER BY FragPercent DESC

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:27:56

I had that code handy as I'm working on this type of thing too right now!

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-26 : 16:03:38

Thanks Tara, I will try it out next week.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources