October 14, 2013 at 4:40 am
Hi,
Running the missing indexes DMVs (with Glenn Berry's query) I get two missing indexes that are very alike...
One has:
Equality columns: [Documento], [NumDoc], [Modulo], [Serie]
The other:
Inequality columns: [Modulo]
Include columns: [Documento], [NumDoc], [Serie]
Building an index with ([Modulo], [Documento], [NumDoc], [Serie]) will satisfy both requests?
I know that the 1st index will be used if only [Documento] is filtered and won't be with my index... But DMVs record the "exact" needs for the indexes, right? If a query just needed [Documento] there would be just an entry for that...
Thanks,
Pedro
October 14, 2013 at 8:52 am
My bet would be that if you created the index you specify ([Modulo], [Documento], [NumDoc], [Serie]) then both queries would use the same index. The optimizer picks the best index from the ones available if the cost of using it outweighs a full table scan. There's a lot more to this decision, but that's it in a nutshell.
I would run your queries you're concerned about in test and measure the performance and look at the execution plan. Create the index and then run them again. While there's no way to be absolutely sure ahead of time, my guess is that the optimizer will use the index.
October 14, 2013 at 9:47 am
Ed Wagner (10/14/2013)
My bet would be that if you created the index you specify ([Modulo], [Documento], [NumDoc], [Serie]) then both queries would use the same index. The optimizer picks the best index from the ones available if the cost of using it outweighs a full table scan. There's a lot more to this decision, but that's it in a nutshell.I would run your queries you're concerned about in test and measure the performance and look at the execution plan. Create the index and then run them again. While there's no way to be absolutely sure ahead of time, my guess is that the optimizer will use the index.
Both queries have 3380 reads (full scans on the table) and take 260ms...
I created the index and they add just 2 reads and 0,650ms...
I guess the index serves both queries...
I'll check the DMV for missing indexes 15 days from know and see if one of them, or both, show up again... though I doubt it.
Thanks,
Pedro
October 14, 2013 at 9:51 am
PiMané (10/14/2013)
Ed Wagner (10/14/2013)
My bet would be that if you created the index you specify ([Modulo], [Documento], [NumDoc], [Serie]) then both queries would use the same index. The optimizer picks the best index from the ones available if the cost of using it outweighs a full table scan. There's a lot more to this decision, but that's it in a nutshell.I would run your queries you're concerned about in test and measure the performance and look at the execution plan. Create the index and then run them again. While there's no way to be absolutely sure ahead of time, my guess is that the optimizer will use the index.
Both queries have 3380 reads (full scans on the table) and take 260ms...
I created the index and they add just 2 reads and 0,650ms...
I guess the index serves both queries...
I'll check the DMV for missing indexes 15 days from know and see if one of them, or both, show up again... though I doubt it.
Thanks,
Pedro
Good deal. With results like that, it's pretty clear that the index covered both queries, but you can use the actual execution plan to see the steps. This, by the way, is called a covering index, because the index covers everything that the queries need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply