question

Sagar Bhargava avatar image
Sagar Bhargava asked

Database latency and page locks waits

Hi All, 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? database_name avg_read_latency avg_write_latency avg_total_latency ServiceManager.mdf 69 9 55 ServiceManager.ldf 129 0 1 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.
performance-tuninglocking
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Edited for better readability.
0 Likes 0 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.