Post reply

Scan Count and Logical Reads

  • December 18, 2014 at 3:44 am

    #299577

    Hi,

    if i fill records in temp table and then i use this temp table in Join. Its Scan Count and Logical Reads are ok.

    but i write everything as whole Query its Scan Count 31000 and Logical Reads 100000, is too high.

    what should be the reasons.

    Please guide me.

    Thanks in Advance...

  • December 18, 2014 at 3:52 am

    #1765390

    Ignore scan count. It is not the number of times a table has been scanned.

    As for the high logical reads:

    Large table

    No indexes

    Non-optimal execution plan

    Poorly written query

    Poor table design

    Pick any one, or more.

    With no context, no query, no execution plan, no tables, there's no way anyone here can do anything other than guess blindly.

    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

  • January 2, 2015 at 4:23 pm

    #1767500

    I totally agree with Gail, but also, off the top of my head I'd say you may be limiting the records you put in the #table which makes a smaller join.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 4, 2015 at 11:56 pm

    #1767664

    Hi Gila, Kenpo,

    🙂

    thanks for ur replies..

    i have limit the logical reads and it works fine now.. 🙂

    //

  • January 9, 2015 at 3:27 am

    #1769005

    It looks like a scan count > 0 occurs when access occurs against a non unique index or a table scan.

    Probably because a seek against a non-unique non-clustered index is not guaranteed to bring back one record so the engine may be programmed to explicitly iterate the subset returned.

    I have been able to get scan count =0 when seeking for 1 record against a unique index when using the full key of that index

    e.g. WHERE COLUMN13 =1 ,and the unique index is only on COLUMN13

    When I use something like

    Select ..... WHERE COLUMN13 IN (1,2,3)

    I get a scan count of 3 even if the index is unique on COLUMN13.

    Because of the last point, I would not use scan count to diagnose issues.

    Test scenarios below, against AdventureWorks2008R2

    DROP INDEX [AK_Employee_NationalIDNumber] ON [HumanResources].[Employee] --Get rid of this existing index

    GO

    create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)

    go

    set statistics io on

    select NationalIDNumber from humanresources.Employee where nationalidnumber = '295847284'

    set statistics io off

    go

    drop index humanresources.Employee.[AK_Employee_NationalIDNumber]

    go

    create nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)

    go

    set statistics io on

    select NationalIDNumber from humanresources.Employee where nationalidnumber = '295847284'

    set statistics io off

    go

    drop index humanresources.Employee.[AK_Employee_NationalIDNumber]

    go

    create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)

    go

    set statistics io on

    select NationalIDNumber from humanresources.Employee where nationalidnumber in ('295847284',

    '245797967',

    '509647174',

    '112457891',

    '695256908',

    '998320692',

    '134969118',

    '811994146')

    set statistics io off

    go

    drop index humanresources.Employee.[AK_Employee_NationalIDNumber]

    go

    create nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)

    go

    set statistics io on

    select NationalIDNumber from humanresources.Employee where nationalidnumber in ('295847284',

    '245797967',

    '509647174',

    '112457891',

    '695256908',

    '998320692',

    '134969118',

    '811994146')

    set statistics io off

    go

    drop index humanresources.Employee.[AK_Employee_NationalIDNumber]

    go

    create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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