August 28, 2011 at 7:17 am
I deployed SQL server cluster for 2 servers.The storage is iSCSI.
The problem is that I have long running queries. Performance is very low. Queries running on cluster takes twice time that queries running on a stand alone SQL server installed on a slower system.
I monitored storage network , it doesn't use it total bandwidth. No CPU bottleneck and no memory bottleneck. But when I use activity monitor , I see a little buffer I/O waits, which is not obvious.
What's the problem for?
August 28, 2011 at 12:09 pm
exactly which wait type are you seeing, can you provide a sample?
can you provide more detail on your iscsi setup, what initiator software are you using, do you have multiple paths, etc?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2011 at 11:17 pm
I don't Use multipathing. Just a single 1 Gbps link. The initiator is microsoft iSCSI initiator and the target is starwind. I monitored network, because I thought the problem is for low network bandwidth. But the network usage is about 10Mbps.
I used SQL server activity monitor, and in "resource waits" I saw Buffer I/O cumulative wait wait time was 41 s . But it doesn't seemed to be the problem. Because a 2min query takes about 5 Min to run.
August 29, 2011 at 1:47 am
parissa_bakhshi (8/28/2011)
I deployed SQL server cluster for 2 servers.The storage is iSCSI.The problem is that I have long running queries. Performance is very low. Queries running on cluster takes twice time that queries running on a stand alone SQL server installed on a slower system.
I monitored storage network , it doesn't use it total bandwidth. No CPU bottleneck and no memory bottleneck. But when I use activity monitor , I see a little buffer I/O waits, which is not obvious.
What's the problem for?
August 29, 2011 at 1:49 am
it is working not come to error or not low to speed
you use it CLUSTER INDEXING
Regards
Ramdas
9096168075
August 29, 2011 at 2:26 am
Would you please explain more?
What I know is that "clustered indexes" and "sql server clustering" are two completely different concepts!
August 29, 2011 at 2:45 am
parissa_bakhshi (8/28/2011)
I don't Use multipathing.
doing so would provide more performance and redundancy.
parissa_bakhshi (8/28/2011)
a single 1 Gbps link. The initiator is microsoft iSCSI initiator and the target is starwind. I monitored network, because I thought the problem is for low network bandwidth. But the network usage is about 10Mbps.
firstly check the switch and the NIC to ensure that they are configured for full 1Gbps connectivity, the network usage may be 10Mbps if thats what the connection speed has dropped to. You would see IO waits with this.
What OS version and iSCSI Initiator versions are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2011 at 3:06 am
Sure, I 'll use multi pathing. But this is a test procedure. Then if I encountered no problem, I 'll Use fiber. But it seems that it's slower than standalone!
I've checked network links. They have the capacity they claim. When I use file copy to shared storage, it uses 800 Mbps!!Maybe SQL server is unable to use the link speed correctly!!!
OS is windows server 2008 r2 and initiator is a service of it!
September 1, 2011 at 2:40 am
Check the fragmentation of the tables that are involved in this SP or process. By large compare the processes and other tasks that are running at the same time between the CLUSTERED and STANDALONE instances.
A reference to execution plan gives much more insight to the problem, I don't think the performance loss may be related to FAILOVER CLUSTERING alone.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
September 1, 2011 at 2:47 am
More importantly the OP hasn't provided details of the storage on the stand alone server which i'm asuming is using local disks unless told otherwise
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 1, 2011 at 12:48 pm
What information does the following query show for your database with trouble?
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_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) AS fs
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_read_stall_ms DESC OPTION (RECOMPILE);
And, immediately after the longrunning query has finished, what is the result of the following query?
SELECT [object_name],[counter_name],[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
How much memory do you have on your standalone and cluster server?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply