I'm seeking for a resolution into wait types I observe on one of my servers.
I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX with 29% and 20% respectively of total waits of the instance.
There is only one database in the (only) instance.
I have searched into the resolution online but what I need is some solid steps to take and advice on where and what to look.
Thank you all.
SQL Server – Understanding PAGEIOLATCH_SH and LCK_M_IX Wait Types
sql serversql-server-2008sql-server-2008-r2sql-server-2012wait-types
Related Question
- SQL Server Performance – PREEMPTIVE_OS_DELETESECURITYCONTEXT Dominant Wait Type
- How to Collect Wait Stats in SQL Server 2012
- SQL Server Performance – Troubleshooting High Signal Waits
- SQL Server 2008 R2 – Resolving Biggest Wait WriteLog Performance Issue
- Sql-server – Scalability of concurrent log restore for different databases on SQL Server
- SQL Server – High Lock Wait Time on Primary Key Index
Best Answer
There are a few causes of this, the top ones are: storage subsystem, bad t-sql, missing/bad indexes. Paul Randall talks about this on his blog and at the sqlperformance blog. I like the scripts from Glenn Berry to find missing indexes and also SP_BlitzIndex.
How do you know if you can trust the recommendations? Well I examine the existing indexes first to see what I have and if they can be altered to accommodate the missing columns. I also like to look in the plan cache to get an idea of what some of the top queries are on that table to see if it would actually make a difference. If you have a test/dev instance, you can implement there first which will give you an idea of the size any possibly usage pattern if you can generate a workload. If you are going right into prod, monitor sys.dm_db_index_usage_stats (check the index reads/writes query in Glenn Berry's DMV queries)and check the reads/writes to ensure it is used. You could also use SQL Sentry's Plan Explorer, but I think you need the pro version for the indexes.
I'm sure someone has a better way to do it, but this has been working for me so far.