December 20, 2012 at 1:41 am
No, no backup at that particular time. The logs backup every 15 mins but had completed 2 minutes previous to the errors.
December 20, 2012 at 4:11 am
That should rule out backups as a source of disk contention then.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
December 20, 2012 at 8:29 am
The issue of I/Os taking longer than 15 seconds to complete is a very difficult thing to trace down. I had a case open with Microsoft on this issue and they simply said you storage is too slow for the load. My storage team continued to say I/O response was less than 8MS and they showed me this as I was seeing the slow I/Os happening. After two months of this we finally found that the storage team had a script that they used to map space to a server and had accidentally mapped drives from this server to another and vise versa. Once they fixed that our problems went away.
There is really no tuning you can do to overcome this issue... as what I saw Check Integrity jobs on large databases would cause this.
Good luck.
December 21, 2012 at 1:26 am
Hi Markus
My storage team are saying everything is 'fine' but that's all I have got to go on. If there is a disk I/O problem I really need to prove it somehow.
My concern is that I keep tweaking things like I have so far and this happens again 6 months down the line. A patch up job if you like, then the underlying issue comes back to bite me as the load increases.
December 21, 2012 at 2:24 am
Usualy a SAN is very good in persisting and retrieving large amounts of data like files. However SQL Server needs to persist many small amounts of data under very hard constraints. I have encountered the same issues with a SAN and it seems that only super specialists with knowledge about SQL Server on a SAN might be able to solve them. In our case we had to revert to local storage for the databases because further investigation revealed that the SAN did not have enough spindles to deliver the required performance.
By the way, parallel query processing only works really great in combination with simultaneous disk access through multiple channels on different controllers. Then each thread will not only be able to process its own data but also fetch and stash data without interfering with other threads. This requires in your case multiple connections with the SAN and that SAN must have the ability to process multiple requests from a single source in parallel.
You should consider using SQLIO to benchmark your current configuration for the specific types of disk I/O SQL Server is generating under heavy load conditions. It allowed us to show to the SAN guys that their SAN might be fast but it was simply not suitable for this type of disk I/O. It is not a simple tool to use but it pays off because you can simulate high workloads at any time. Good luck!
December 24, 2012 at 5:31 am
Also you need to check your disk space and the future growth of your disk size & memory utilisation by query
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply