Post reply

Index requirement for logical operations

  • March 4, 2011 at 1:32 am

    #1294495

    Data type look poorly thought out to me, why are year and qtr varchar's ?

    SELECT TOP 20 PrimSSN, SecSSN, [Year], DocType, [PageCount], ReturnId, IMAGEPATH

    FROM finals WHERE [Year] = '2009' AND

    (PrimSSN IN ('123', '234') OR SecSSN IN ('241', '354'))

    Should be able to use the indexes.


    Clear Sky SQL
    My Blog[/url]

  • March 4, 2011 at 1:53 am

    #1294501

    @Gail- You hit the bull's eye.Spot on with the solution.The plan now shows seek with the two indexes.

    @Dave-Thanks for this query.Works perfectly.

    I realized today,execution plans are not just to look for seeks and scans.There is more to it.I was not able to see the bigger picture.

    You guys are truly dedicated to the community.

    By the way,does it mean that if a column has a particular data type in the structure of table and the way it is searched in the select query matters.

    In my case the columns were var char as design but being read as integers and this it was converted to integer all the time the query ran.Is it true that design matters and can make indexes look poor?

  • March 4, 2011 at 2:15 am

    #1294512

    Glad to help ,

    The plan is indeed a window into exactly what sqlserver has done ( and to a certain extent, why) to resolve the query.

    Design is everything , its the foundations of your "sky scrapper" system you need to build on.

    If the foundations are wrong , the system will be wrong. There can be certain corrective measures you can take, as here, but really you should have to .


    Clear Sky SQL
    My Blog[/url]

  • March 4, 2011 at 9:23 am

    #1294738

    chandan_jha18 (3/4/2011)

    Is it true that design matters and can make indexes look poor?

    Yes! Absolutely!

    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

  • March 7, 2011 at 12:23 am

    #1295188

    Dave,Gail!

    My CPU time for this query came down to 0 ms from 3125 ms earlier.This is something awesome and you guys deserve the credit.

    I have one question here.My server has 30 GB of RAM and the total databases staying on it sum up to 20 GB.So indirectly all of my data stays in the memory.

    So even if there is a bad design and inefficient index,my data is still in the memory.So why an index scan will cause my CPU to go up.If the clustered index data is stored in memory,even for scan why CPU has to work this much.Sorry if I framed my question incorrectly,but thought to ask you about this doubt.

  • March 7, 2011 at 1:13 am

    #1295194

    chandan_jha18 (3/7/2011)

    So even if there is a bad design and inefficient index,my data is still in the memory.

    So? The query processor always operates on data in memory. All you avoid by having large amounts of memory is the IO cost of pulling it off disk.

    So why an index scan will cause my CPU to go up.If the clustered index data is stored in memory,even for scan why CPU has to work this much.

    Because scans and the subsequent filters take CPU time. The fact that it's in memory doesn't change that.

    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

  • March 7, 2011 at 1:43 am

    #1295200

    Thanks Gail! I got your point.Index scans are costly.Its just that physical ones are more as compared to logical one.Scan is a Scan.thanks for explaining so clearly!

    One thing which is not clear is:

    Suppose I do not tune the query and let the scans happen,when I write Set statistics i\o on and watch for reads and writes,the query shows logical scans and high CPU but physical reads as 0.I can understand that if i have sufficient memory and a static table it can stay in memory for long but since this is an OLTP database where changes occur frequently, why there are no physical reads at all.There will surely be some pages which has changed since last read and the earlier table cache is no longer correct.So why no physical reads at all?

  • March 7, 2011 at 2:35 am

    #1295212

    Please go and do some reading on how SQL uses the data cache.

    Writes are also done to the cache and are written to disk later, not written to disk directly

    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

  • March 7, 2011 at 2:45 am

    #1295214

    GilaMonster (3/7/2011)

    Please go and do some reading on how SQL uses the data cache.

    Writes are also done to the cache and are written to disk later, not written to disk directly

    Thanks for the idea.Unfortunately not all the blogs write correct things.there was one blog where it was written that 'OR' operator will not use indexes and you later corrected me.I will do some research on this topic.Thanks for all the help you provided!

  • March 7, 2011 at 2:51 am

    #1295217

    Start with Books Online and see if there are any white papers from microsoft

    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

Viewing 10 posts - 16 through 24 (of 24 total)

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