I am looking at a database which is experiencing slow query executions. Digging into, I found some metrics around the page locks and file level IO.
Average Page Lock waits on Table A & B are very high : 34 and 50 mins respectively. I know that it can be caused by the Isolation level being ‘Serializable’ used in queries which if different from the default ‘Read Committed’. Is there more that I can check the reason for the high waits?
The database only has 1 data and 1 log file. I think adding additional data files to this database and then moving them to separate disks should help in this case. Some of the busy tables can be moved to these new data files to spread the IO, improving the performance. The instance sits on a Hypervisor and has about 42 GB of memory and is a Windows 2008 R2 Enterprise. SQL Server is 2008 R2 SP3.
If you have +30 minutes lock Waits on the database due to queries running in Serializable isolation level, I doubt adding more data files will do much good. It might reduce the execution time for the blocking queries, but it will not take it down to seconds from +30 minutes.
With that said, I Think your avg_read_latency looks very high. Before adding files on separate disks, I would (have someone) analyze the disk configuration to see what can be done to improve things there.
answered Sep 14, 2016 at 06:41 AM