Missing Indexes
-
We are noticing a lot of I/O latency on our SQL server. Is it common to have a lot of missing indexes? We are trying to narrow down what could be causing all the I/O latency we are seeing. Thanks for any help!
-
There could be a lot of different reasons for latency. Did this just start recently? Here are some possible things; (1) should be done first, the others are in no particular order. If you need more detail on these, we can flesh that out later.
(1) verify physical components to make sure it's not a problem there, such as: bad network card; SAN issues, include bad/slow paths/channels (I'm not sure of the correct technical terms for SAN issues, I'm a DBA, not a SAN person); etc.
(2) verify that log waits are not causing the issues, especially if you use FULL or BULK-LOGGED recovery model (rather than SIMPLE) (check the wait status in sys.databases)
(3) look for memory pressure (memory error messages) in SQL Server. Lack of necessary RAM can cause I/O issues
(4) (to be continued...)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
We have looked at our SAN and run diagnostics and there doesn't appear to be any hardware issues. I am more of a hardware/networking guy and SQL novice, so if you could help give me more instruction on the steps you listed out, I would greatly appreciate it.
Here are the types of waits we are seeing. Not sure if this helps give you more info or not?
-
Your post is a bit confusing. You start by talking about I/O Latency but show a chart for CPU usage and then ask if it's common to have a lot of missing indexes on a thread titled Missing Indexes and then show a wait type for Query Store (see the following link for what Paul Randal has to say about that).
https://www.sqlskills.com/help/waits/qds_async_queue/
With all that being said, what is your REAL question? Are you actually having any performance issues or are you looking at a bunch of monitoring indicators and trying to figure out if it actually means anything or not?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. You might also want to read the following about Query Store. Personally, I'll never enable it and use other methods to figure out what I need to but I'm a minority in that opinion. The last sentence in the article seems important to me.
https://docs.microsoft.com/en-us/archive/blogs/shreyasgowda/what-is-qds_async_queue-wait-type
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry my post is confusing. I wasn't really sure how elaborate on it. Yes, we are having performance issues and it mainly looks like disk IO latency. We have checked all our hardware for disk issues and have found none. So trying to find the cause for the disk IO latency.
- Hawkins_IT wrote:
Sorry my post is confusing. I wasn't really sure how elaborate on it. Yes, we are having performance issues and it mainly looks like disk IO latency. We have checked all our hardware for disk issues and have found none. So trying to find the cause for the disk IO latency.
Got it.
If you look at the last sentence of the article at the link in my previous post, I strongly believe you might discover the answer, especially since the largest wait type by a high margin is Query Store. Try turning it off and see what happens to your performance issues. My bet is that they'll subside quite a bit..
Don't let that be the end of it if it helps. I could be wrong but having high Query Store waits like that would indicate to me that a whole bunch of stuff is recompiling nearly every time it runs. You need to find and fix those issues, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How are you identifying the issue as disk IO latency? This is important - because most tend to look at the wrong counters when working with SQL Server to determine I/O issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Havin extremely high IO_Completion waits is what made us think it was disk IO.
I think turning off the Query Store will help alot. Now just need to figure out what else needs to be fixed.
-
The missing indexes still seems like this could be an issue for latency. Does anyone agree? How could we end up with so many missing indexes? Would fixing these indexes improve performance? Right now, it shows 87 missing indexes, and this number continually grows throughout the day.
-
Well have you identified which queries are actually running to create those missing index statistics? You would then need to see how expensive those queries actually are beyond just they don't have an index.
-
My opinion - missing indexes report is only useful if you know the usage counts for those missing indexes AND the performance gain/hit by creating it.
For example, if you look at index_handle 5 and 7, they are on the same object (ie table) and have similar columns. If you created the one from index_handle 5, index_handle 7 MAY go away. That being said, if the query that made index_handle 5 pop up in that list happens 1 time per year and the one for index_handle 7 happens thousands of times per day, I would skip making index_handle 5 and just make 7. Now, the performance gain/hit, it could be that creating that index makes your select 1.2 times faster but makes your inserts, updates, and deletes 2 times slower. Is that performance gain/hit acceptable?
The missing index recommendation that SQL gives is to improve performance of the one individual query. My advice would be to look at your clustered index and see if it makes sense for the queries and foreign keys on the tables indicated by the missing index report. having a good clustered index usually means you need fewer non-clustered indexes.
What I would recommend if you are seeing (or thinking) IO is the problem, get some metrics around IO with perfmon or similar tools. Don't treat the symptoms - investigate and prove what the root cause is and fix that. Looking at the database ID column in that screenshot, do you have all of your databases on the same disk? I see 3 databases in that list, plus the 4 system ones, so 7 databases sitting on the same physical disk with both the data and log file on one physical disk would be my guess (again, I can't see your setup) as to what is causing IO issues and what I would look at fixing.
On top of that, adding indexes is not likely to help your IO_Completion wait type. BOL says that this wait "Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits." This site has a good writeup on that wait type - https://www.sqlskills.com/help/waits/io_completion/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
You can't ever look at missing index stats in isolation. You also need to look at existing indexes and their usage stats (and often I/O counts as well).
First, before doing any other index tuning, review the existing clustering indexes, because, by far, the single most important factor for overall table performance is having the best clustered index. (Hint: that means never just defaulting the clus index to an identity column). You can determine the best clus key(s) by looking at current index usage stats and missing index stats. Btw, do not be afraid of having multiple clus keys if stats and/or your knowledge of the table's uses indicate that is what's best for the table.
Whether that clus index is a formal primary key or not doesn't matter from a performance standpoint. It could matter for other things, such as replication, CDC, etc., but not for performance.
Only after the best clus index is in place should you do other index tuning.
After that, you still need to look at existing indexes alongside missing index stats because, for example, say SQL "said" that you were missing an index on ( A, B ) INCLUDE ( C, D, E ). But you see that you already have an index on ( A, B ) INCLUDE ( C, E ). You would just want to change the existing index to INCLUDE D, obviously not create a whole new index of ( A, B ) INCLUDE ( C, D, E ).
Also, you'll often need to consolidate nonclustered indexes to reduce the total number of them, even if that means that sometimes an index must be scanned rather than just "seek'ed". There's art as well as science in tuning indexes.
If you'd like to do a more thorough review of your existing indexes, I can give you a script to run, then we can analyze the results. Let me know if that's something you're willing to pursue.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I appreciate all the feedback, it is very helpful, as I am a SQL novice.
Yes, please send me the script in regards to indexes and then we can analyze the results. Thank you!
-
Where else can I look or are there scripts to run to help find out what is causing the IO_COMPLETION waits? That is what our main issue is, I believe.
From what I am learning, I don't believe this is related to the missing indexes. Thansk again!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply