How can two sql servers have different disk read time in same SAN?

Question

I have two sql servers in same SAN. The servers host the databases added in alwayson group. One of the servers is responding well but the other server is responding slowly while running queries. When I look at the disk read time one server has below 1 ms but the slow server has above 4 ms. Is it possible to be so in the same SAN?
Please help.

Answer 1

Good day @Surendra Adhikari ,

This topic might be related to advance topics (level 500) which include discussion about the internals of SQL Server and how SQL Server stores and manages the data behind the scenes. I will not open the discussion at this time fully but focus on the basics only

Is it possible to be so in the same SAN?

The short answer is YES it is possible.

I am familiar with someone in the forum who have this issue named @Surendra Adhikari , which proof that this is possible :-)

Is this make sense? and can we explain the source of the different behavior? In some cases it make sense and it is the expected behavior but to fully understand the source we need more information.

Is it possible to be so in the same SAN?

Let's put aside SQL Server for a second and think about managing a file in SAN or any other storing system...

Try to manage a file of 1 TB in your disk -> Edit the file every several seconds

Do the same in second file in the same machine or different machine under the same storing system (SAN for example)

Check where the files are stored physically on the Disk after a day or so...

Will the files we stored and as one physical unit on the disk?!? Or maybe a file is actually a set of physical units on the disk which are managed as one by the operating system and the storing system?

The theatrical experiment we conducted above should give you the understanding that even if you take two exact same hardware and software and you manage the exact same data and execute the exact same task - THE SYSTEMS WILL NOT BE EXACTLY THE SAME after awhile.

Did you not see the Terminator or any movie where the AI have a glitch and start to fight the human?!? You cannot really manage two system in the exact way for the long range without spending your resources on managing the data instead on using the data.

Moreover, SQL Server has no goal to manage the disk exactly in specific way. It is a muti-threads system which manage using the data in very complex way in order to provide best performance on using the data and not in the goal of having two identical physical disks...

The servers host the databases added in alwayson group.

An availability group supports a replicated environment which mean replicate from the "management of the data" point of view. It does not guarantee replicate of the physical storage and in fact it cannot provide this goals since the data is not managed in exactly the same way. The data is copied from one server to the others and it is not really managed identically. You can have a set of primary databases and one to eight sets of corresponding secondary databases.

Are the servers have the same roles? What is the relation between the servers? Are both in read-only mode?

Can you provide more information about your system architecture?

One of the servers is responding well but the other server is responding slowly while running queries.

There are multiple parameters which can impact the time of the execution (even more parameters when using Always ON).

In order to put the behavior of servers in the right perspective we need more information and we must fully familiar with the system. For example differences between 1 ms and 4 ms in small "identical" systems (using small tables) might point on an issue but using the same scenario on big data, even if the client layer is the same, behind the scenes these two "identical" systems might store the data totally different.

If this behavior is consistency + the servers have the same role + the storage system is the same (as you already said), then in first glance (before we get more information) it sound like the servers are not maintenance the same way, not used the same way, or the configuration of the servers is not the same.

What next?

Please go over my message and provide more information is this issue still not clear. Try to provide as much information as you can including exact architecture of the Always On Availability Groups and the servers roles.

Try to monitor why the execution is slow like any other monitoring of slow execution. Check locks and waits while the execution is slow, check server resources, and so on

Answer 2

Hi @Surendra Adhikari ,

You cannot compare the disks from the value of a certain counter, you should analysis it based on a comprehensive statistical analysis.

Regarding disks, there are two sets of counters: LogicalDisk and PhysicalDisk. LogicalDisk records the read-write counter of each logical disk according to the logical drive letter. From these counters, we can understand how much workload the system and application send to different drive letters. PhysicalDisk takes the physical disk as the unit. If a physical disk is a disk array composed of RAID, Windows will only list a whole physical disk. If there are several logical partitions on the physical disk, these partitions will appear as a group. From the counter under PhysicalDisk, you can know the response speed of the disk. So if you want to analyze the performance of the disk, the main analysis is that the LogicalDisk counter under PhysicalDisk can only serve as a reference.

The key counters are as follows.

1. % Disk Time
2. % Idle Time
3. Disk Bytes/sec
4. Avg. disk sec/transfer
5. Avg. disk sec/read
6. Avg. disk sec/write
7. Avg. Disk Queue Length

If you want to monitor and compare the disks' performance , you can reference this firstly: https://www.sqlshack.com/sql-server-disk-performance-metrics-part-1-important-disk-performance-metrics/

BR,
Mia
If the reply helped, please “Accept Answer” and upvote it.--Mia

Answer 3

Hi @Surendra Adhikari ,

Is the reply helpful?

BR,
Mia
If the reply is helped, please do "Accept Answer" .--Mia