1

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.

  • 1
    For sure you don't have proper indexes applied – aasim.abdullah Nov 25 '14 at 12:20
  • Hi Aasim. Thank you. Any suggestion into how to identify them? I have some queries at hand for missing indexes but not quite sure if I should trust their results. – Stackoverflowuser Nov 25 '14 at 14:18
2

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.

0

A good place to start would be to identify and disable (remove at a later stage) unused indexes, you can do this by using the standard SQL Server reports in SSMS. Another option is to investigate if the Server have enough memory (RAM), a qualitatively cheap improvement now a day. Like Brent Ozar said; the correct amount of memory for SQL Server is MORE!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.