Post reply

query using most pages

  • June 6, 2011 at 10:05 am

    #252267

    is there a way to tell which query is using or pulling most pages??

    thanks

  • June 6, 2011 at 10:08 am

    #1334496

    SET STATISTICS IO ON, then run the query.

    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

  • June 6, 2011 at 10:12 am

    #1334500

    My personal favorite is to run in profiler and choose the statement completed. So even for 1 proc you can see which statment is really hurting performance.

    I like statistics IO but when you get into the 100s of tables it becomes totally impossible to use.

  • June 6, 2011 at 10:18 am

    #1334503

    Profiler for aggregated totals (for statement or batch depending on event), Statistics IO for a per-table breakdown.

    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

  • June 6, 2011 at 5:10 pm

    #1334738

    well let me rephrase the question. how do I tell which query is eating up all the free pages?

    thanks

  • June 6, 2011 at 11:36 pm

    #1334790

    That's easy to answer - none.

    Queries don't use up pages in the buffer pool. Data pages from tables do. SQL doesn't track (or care) what queries bring those data pages in, it just cares that the pages are in the buffer pool and available for queries to use.

    Say you have 10 different queries that run against TableA. The first of those to run (after a restart of the SQL instance) may bring a large portion of TableA's pages into memory. The rest just use the cached data pages and don't have to pay the cost of going to disk. That's the whole point of a data cache, the cost of getting pages from disk is high, better not to pay it more often than absolutely necessary.

    Maybe we should take a few steps back here. What's the problem that you're trying to resolve?

    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 6 posts - 1 through 5 (of 5 total)

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