x

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.

more ▼

asked Sep 13, 2016 at 11:41 PM in Default

avatar image

Sagar Bhargava
638 25 28 34

Edited for better readability.

Sep 14, 2016 at 06:31 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Sep 14, 2016 at 06:41 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x74
x50

asked: Sep 13, 2016 at 11:41 PM

Seen: 50 times

Last Updated: Sep 14, 2016 at 06:41 AM

Copyright 2017 Redgate Software. Privacy Policy