Post reply

Index question

  • May 10, 2016 at 9:16 am

    #318812

    I have a table with 8 million rows (index size 1.5 GB and Data space 1.3 GB). when I ran index_usage_stats results came with

    Index id = 1 (datatype is decimal (15,0) primary key)

    0 users seek

    60 users scan

    153000 userlookups

    60,000 users updates

    Is this an issue? any suggestions to fix it?

    Thanks

  • May 10, 2016 at 9:45 am

    #1877877

    Tac11 (5/10/2016)

    I have a table with 8 million rows (index size 1.5 GB and Data space 1.3 GB). when I ran index_usage_stats results came with

    Index id = 1 (datatype is decimal (15,0) primary key)

    0 users seek

    60 users scan

    153000 userlookups

    60,000 users updates

    Is this an issue? any suggestions to fix it?

    Thanks

    There needs to be more context before anybody can answer that question. What exactly led you to look at your index? What problems are you experiencing? The only thing I can determine from those stats is that its a clustered index (user lookups). Also, most read operations to the table are coming from 1 or more other non-clustered indexes. That in of itself isn't an issue but since I don't know anything else it's just information.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • May 10, 2016 at 10:46 am

    #1877894

    Tac11 (5/10/2016)

    I have a table with 8 million rows (index size 1.5 GB and Data space 1.3 GB). when I ran index_usage_stats results came with

    Index id = 1 (datatype is decimal (15,0) primary key)

    0 users seek

    60 users scan

    153000 userlookups

    60,000 users updates

    Is this an issue? any suggestions to fix it?

    Thanks

    This table only has/is a clustered index, no issue here as such apart from using the decimal datatype for the primary key/clustered index. 8 million rows are roughly 1/268th of what a signed integer can hold (2147483647) and an integer uses 4 bytes. A decimal(15,0) uses 9 bytes, slightly shy of 50 bits and can hold the value of 999999999999999 which is 465661 times the max value of an integer. Any reason for this?

    One could argue that 153000 userlookups indicates that other indices should be created, key lookup is quite expensive operation.

    😎

    Edit: added key lookup.

  • May 10, 2016 at 11:38 am

    #1877905

    OK so zero seeks on the primary key, and we know it's also the clustered index since it is index_id = 1

    so nothing, really ever says WHERE PK = {somevalue}

    would anyone agree zero seeks implies the clustered index should probably be moved to something that satisfies more of the queries that are actually in use?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 10, 2016 at 11:55 am

    #1877911

    Eirikur Eiriksson (5/10/2016)

    Tac11 (5/10/2016)

    I have a table with 8 million rows (index size 1.5 GB and Data space 1.3 GB). when I ran index_usage_stats results came with

    Index id = 1 (datatype is decimal (15,0) primary key)

    0 users seek

    60 users scan

    153000 userlookups

    60,000 users updates

    Is this an issue? any suggestions to fix it?

    Thanks

    This table only has/is a clustered index, no issue here as such apart from using the decimal datatype for the primary key/clustered index. 8 million rows are roughly 1/268th of what a signed integer can hold (2147483647) and an integer uses 4 bytes. A decimal(15,0) uses 9 bytes, slightly shy of 50 bits and can hold the value of 999999999999999 which is 465661 times the max value of an integer. Any reason for this?

    One could argue that 153000 userlookups indicates that other indices should be created, key lookup is quite expensive operation.

    😎

    Edit: added key lookup.

    I would think to get keylookups that there would have to be nonclustered indexes defined on the table or you are going to get only seeks or scans. Of course, I could be wrong.

  • May 10, 2016 at 11:58 am

    #1877913

    Lowell (5/10/2016)

    OK so zero seeks on the primary key, and we know it's also the clustered index since it is index_id = 1

    so nothing, really ever says WHERE PK = {somevalue}

    would anyone agree zero seeks implies the clustered index should probably be moved to something that satisfies more of the queries that are actually in use?

    I am going to answer that with a "yes, but it depends." There may be an environmental reason it isn't but I know I will get shouted down by a few others on this.

  • May 10, 2016 at 12:16 pm

    #1877921

    IN GENERAL, Since 0 users seek,high users_lookups and high users_update, should PK be removed?

  • May 10, 2016 at 12:24 pm

    #1877925

    Tac11 (5/10/2016)

    IN GENERAL, Since 0 users seek,high users_lookups and high users_update, should PK be removed?

    In general, no. Your tables should have a PK defined. Whether it should be the clustered index or not is a different story.

  • May 10, 2016 at 1:47 pm

    #1877949

    Tac11 (5/10/2016)

    IN GENERAL, Since 0 users seek,high users_lookups and high users_update, should PK be removed?

    You'll end up trading key lookups for RID lookups which are the same as far as the 'users_lookup' stats are concerned. Updates are your inserts, updates and deletes, there is nothing strange about that. Having a high amount of lookups can be avoided by having a covering index which would reduce I/O. Another thing could be to change the key column(s) on your clustered index so that it better fits your queries.

    BUT my question remains...what problem are you trying to actually fix? I'm just wondering if you just looked at the stats and said hey is this normal. The truth is normal just depends on your situation and we don't have any other information. At the very least what does your other indexes on that table look like and how do you typically query that table?

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • May 11, 2016 at 4:05 am

    #1878092

    Eirikur Eiriksson (5/10/2016)

    One could argue that 153000 userlookups indicates that other indices should be created, key lookup is quite expensive operation.

    Since key lookups are only ever done to the clustered index, that value means very little. All you can tell from that value is that there's one or more nonclustered indexes on the table and there's some query/queries that use the nonclustered index but need columns that it doesn't have, hence have to do key lookups.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2016 at 4:06 am

    #1878093

    Lowell (5/10/2016)

    would anyone agree zero seeks implies the clustered index should probably be moved to something that satisfies more of the queries that are actually in use?

    Maybe, but depends on what the other columns in the table are and what the queries usually filter on (and if there's a 'usual' filter)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2016 at 9:23 am

    #1878285

    GilaMonster (5/11/2016)

    Eirikur Eiriksson (5/10/2016)

    One could argue that 153000 userlookups indicates that other indices should be created, key lookup is quite expensive operation.

    Since key lookups are only ever done to the clustered index, that value means very little. All you can tell from that value is that there's one or more nonclustered indexes on the table and there's some query/queries that use the nonclustered index but need columns that it doesn't have, hence have to do key lookups.

    Thanks Gail, meant to say covering indices could be helpful. My thought is that with zero seeks, either there is something wrong with the schema or the usage, seen this many times when bad schemas are forcing Cartesian production suppression by the use of the DISTINCT operator, queries returning handful of rows are working millions of rows to produce the output.

    😎

  • May 15, 2016 at 5:13 pm

    #1879156

    Eirikur Eiriksson (5/11/2016)

    GilaMonster (5/11/2016)

    Eirikur Eiriksson (5/10/2016)

    One could argue that 153000 userlookups indicates that other indices should be created, key lookup is quite expensive operation.

    Since key lookups are only ever done to the clustered index, that value means very little. All you can tell from that value is that there's one or more nonclustered indexes on the table and there's some query/queries that use the nonclustered index but need columns that it doesn't have, hence have to do key lookups.

    Thanks Gail, meant to say covering indices could be helpful.

    Or - could be a waste of space.

    If a non-clustered index seek gives you a couple of rows to look-up it's a perfect plan, and adding (included) columns from PK will only take disk space and slow down data modification queries, without any noticeable benefit on selects.

    The number of key lookups is just a number of key lookups, it does not show how slow and/or ineffective every of those lookups was.

    Zero seeks - yes, that's the clear indicator that the clustered index is chosen incorrectly.

    _____________
    Code for TallyGenerator

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply