May 20, 2011 at 3:05 am
We're running a SQL 2008 (standard edition) active/passive cluster for approx 40 application databases, plus another 30 SharePoint 2007 content databases. Would it make any sense to organize the server into multiple instances, putting the SharePoint dbs on a separate instance? Would this have any positive effect on performance or just create competing resources on the server?
thanks for any advice
May 20, 2011 at 4:10 am
Performance can be effect and its depends on the Processor physical or logical ,RAM and Storage controller
Operating System Version ?
Total Processors Logical and Physical ?
Total RAM ?
Hardware Numa total Groups if its available ?
Total Drives ? Not Partition ? using SAN or local Drives ?
Using SAN with multi-pathing ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 20, 2011 at 4:32 am
Syed Jahanzaib Bin hassan (5/20/2011)
Performance can be effect and its depends on the Processor physical or logical ,RAM and Storage controllerOperating System Version ? Windows Server 2008 R2 Enterprise
Total Processors Logical and Physical ? 16 / 2
Total RAM ? 24GB total (~13GB used by sql at the moment, ~9GB available)
Hardware Numa total Groups if its available ? Dunno. What's this?
Total Drives ? Not Partition ? using SAN or local Drives ? 4 SAN volumes, C (opsys) D: logs E: data F: disk backups
Using SAN with multi-pathing ? Not sure. Have to check with SysAdmins
thanks!
May 20, 2011 at 4:33 am
Putting performance aside for a moment, creating a separate instance for Sharepoint would provide a couple of benefits. The first is that you can perform maintenance on your Sharepoint databases without affecting the other databases. The second is that any enhanced permissions you grant to your Sharepoint account (for example the ability to create databases) also won't affect the databases on the other instance.
John
May 20, 2011 at 5:21 am
I would leave them on the same instance if you've not seeing any performance issues to save extra administration.
If you are having issues then it would might be worth installing an additional instance running it on the passive node. Note that in the event of a failover the performance issues will reoccur.
May 20, 2011 at 5:34 am
Be aware that if you go active-active then your licensing costs double, since you have to pay for a licence for each node that SQL Server runs on.
John
May 20, 2011 at 5:54 am
John Mitchell-245523 (5/20/2011)
Be aware that if you go active-passive then your licensing costs double, since you have to pay for a licence for each node that SQL Server runs on.John
I had the impressions that would only be the case in active/active setups because in that case (e.g. on a 2-node clustere) both nodes would actually run sqlinstances at the same time whereas in a active/passive setup, only one node will be running the instance(s) and the other node would only wait until failover time.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2011 at 5:57 am
ALZDBA (5/20/2011)
I had the impressions that would only be the case in active/active setups
You're quite right - I said active-passive but I meant to say active-active. I'll amend my previous post now. Thanks for spotting!
John
May 20, 2011 at 6:22 am
John Mitchell-245523 (5/20/2011)
ALZDBA (5/20/2011)
I had the impressions that would only be the case in active/active setupsYou're quite right - I said active-passive but I meant to say active-active. I'll amend my previous post now. Thanks for spotting!
John
That was my "good deed for the day" 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2011 at 6:47 am
you would create competing resources for memory so may have a slight degradation there.
On the other hand you would have to have sperate disk resources for the new instance so that separation may help you, in particular with a new tempdb. IIRC sharepoint benefits from a maxdop setting 1, your other apps may not.
so from a performance point of view - perhaps
security - yes
administration and complexity - definitely worse
---------------------------------------------------------------------
May 20, 2011 at 9:24 am
george sibbald (5/20/2011)
you would create competing resources for memory so may have a slight degradation there.On the other hand you would have to have sperate disk resources for the new instance so that separation may help you, in particular with a new tempdb. IIRC sharepoint benefits from a maxdop setting 1, your other apps may not.
so from a performance point of view - perhaps
security - yes
administration and complexity - definitely worse
I have installed Microsoft WSRM on my clustered servers to avoid a single instance consuming all cpu capacity for a long period.
ref: http://technet.microsoft.com/en-us/library/cc755056.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2011 at 9:44 am
Check coreinfo software a very small application available on
www.sysinternals.com
then paste the output of this sofware this will be helpful for hardware Numa also and select distinct memory_node_id from sys.dm_os_memory_clerks
Check delays on the files of the database then check with this query
select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc
execute this query on the required databae and paste result of this query
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply