January 10, 2017 at 10:11 am
Looking for any results with a scenario I have with 3rd party app, I can't change structure or the Proc which calls it but I can do indexes. It ihas a table which holds vital signs (about 30 fields), usually small values (BMI, Heart, Weight, etc). The proc goes through about 8 checks with these values each portion it's on tsql subset which determines an alert. It shows through bitztcache and pinal dave as a worst performer. I can take each subset which does a RID of course for each subset and then over 60% tied to sort on the timestamp date for each row. My choice is am I better with small covering indexes for each subset or try to combine them into a large covering (maybe include) index to deal with all the fields. Every patient visit will have a row for this table so we are looking at over 400K a year. What I would appreciate is anyone with close to dilemma if there was a threshold on how many small indexes tilts the scale towards the large more covering index.
January 10, 2017 at 10:21 am
I specialize in tuning, particularly index tuning, and I tend strongly toward the larger, shared index rather than separate indexes. Be aware that if the index needs scanned, multiple processes can "share" the scan I/O if they are running at the same time. That is, if processA and processB both need to scan the index, the index can be scanned only once and the rows read passed to both A and B from the same scan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 10, 2017 at 11:59 am
thanks that was kind of my first thought, also a little worried about the inserts and updates generating lots of IO if I have 7-8 indexes
January 10, 2017 at 12:55 pm
1) I'm with Scott that one "larger" index with a number of INCLUDEd columns can be a win from a number of perspectives over a bunch of small ones. There are definitely access patterns (both read and write) that make multiple indexes a win though.
2) With regards to "merry-go-round" shared IO (aka Advanced Scan), do note that is an Enterprise Edition only feature, thus the majority of installs won't be able to take advantage of that.
https://technet.microsoft.com/en-us/library/ms191475(v=sql.105).aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 10, 2017 at 1:31 pm
ScottPletcher (1/10/2017)
I specialize in tuning, particularly index tuning, and I tend strongly toward the larger, shared index rather than separate indexes. Be aware that if the index needs scanned, multiple processes can "share" the scan I/O if they are running at the same time. That is, if processA and processB both need to scan the index, the index can be scanned only once and the rows read passed to both A and B from the same scan.
Of course pages read from an index scan can be logical reads, if the pages are already buffered to the cache by another process. However, this is the first time I've heard of a feature that allows multiple processes to share and index scan in-flight.
As a side question: Could this feature potentially create something like a cross-process CXPACKET wait state, where one process is waiting for another process to complete it's portion of the index scan?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 10, 2017 at 1:56 pm
Eric M Russell (1/10/2017)
ScottPletcher (1/10/2017)
I specialize in tuning, particularly index tuning, and I tend strongly toward the larger, shared index rather than separate indexes. Be aware that if the index needs scanned, multiple processes can "share" the scan I/O if they are running at the same time. That is, if processA and processB both need to scan the index, the index can be scanned only once and the rows read passed to both A and B from the same scan.Of course pages read from an index scan can be logical reads, if the pages are already buffered to the cache by another process. However, this is the first time I've heard of a feature that allows multiple processes to share and index scan in-flight.
As a side question: Could this feature potentially create something like a cross-process CXPACKET wait state, where one process is waiting for another process to complete it's portion of the index scan?
Follow the link I posted Eric (and there are lots of other coverage of the feature available). It is very slick and has been around for quite some time now so I would think it is pretty bullet proof. I can't recall the low-level details, but any overhead can't possibly be anywhere near the raw cost of processing the same set of pages completely separately even if they were all memory-resident logical IO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 10, 2017 at 2:01 pm
sorry on standard edition. Tried wider index which it used, took 3 of the 7 steps and got rid of RID, not for the other 4. Going to look at possible use of hidden indexes learned from Kendra Little class
January 11, 2017 at 2:19 am
http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/
Hidden indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2017 at 6:42 am
Went through Kendra little's index training video again and ran sp_blitzindex against the table (oh yeah no clustered index on their tables that is a whole other discussion). When I added wide index it did RID on 4 of 7 statements in proc and cut CPU down about 20%. bliztindex did show the RID as hidden so much for small victories
January 12, 2017 at 1:25 pm
A very skinny index can read data faster, because more records fit per page. However, every insert/update/delete will need to be propagated to all of the indexes containing the affected columns.
Although it is an oversimplification, it can help to conceptualize each index as a separate copy of the table, with (usually) fewer columns and in a different sort order. I've seen tables with 50+ indexes. Realizing that any record inserted to that table will have to be inserted into the other 50 copies (indexes) as well can surprise people sometimes.
If reads speed are your top priority, skinnier indexes will perform better.
If space is a concern or if insert/update/delete speed is important, a wider covering index will have less I/O and require less space overall than many focused skinnier indexes.
Wes
(A solid design is always preferable to a creative workaround)
January 12, 2017 at 2:18 pm
There are times when multiple, narrower indexes would overall perform better. But that's more difficult to accurately determine and code for. You must also continue to monitor it, because adding a single column to a query can render the previously-covering index obsolete and force a table scan instead. In theory SQL can intersect multiple indexes, but it doesn't always do that, even when you think it should (and the optimizer's usually right in those cases too anyway!). [Edit: Therefore, unless you're proficient in tuning indexes, it could be better to stick with fewer wider indexes, even if that wouldn't be the absolute best for overall performance in certain situations.]
If you have a mix of "=" comparisons on a column(s) and something other than "=" on other column(s), make the "=" column(s) the lead column(s) in the index. For example:
WHERE a = 1 and b > 0 and c > 100
then "a" should be first in the index keys. For:
WHERE a = 1 and b > 1000 and c = 45
then "a" and "c" should be first, in whichever order is best for overall performance, followed by b.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 13, 2017 at 2:33 am
whenriksen - Thursday, January 12, 2017 1:25 PMA very skinny index can read data faster, because more records fit per page. However, every insert/update/delete will need to be propagated to all of the indexes containing the affected columns.
However very skinny indexes are only really useful when you have queries that filter on very small numbers of columns and return very small numbers of columns. Most of the time when I see very narrow indexes in client systems, they're not used because they don't support the workload.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply