SQL Profiler and very high reads

  • Hi

    I am facing a strange situation with a prepared query sent from Java application, SQL profiler is showing a very high reads about 250 000 pages (CPU:63155, duration 63640), when I look in the Query plan captured by SQL profiler there is no table or index scan, there are around 5 main tasks with:

    cost between 15 and 20%

    Actual number of rows : between 1 and 400

    Physical access : Clustered or Index seek

    I am trying to figure out what is causing the 250 000 pages reads without success, since this a prepared query this is hard to reproduce the issue

    Here is what looks like the query : exec sp_execute 95503,325138,...

    with the handle 95503 I am able to find the original query and when run the query on Management studio the number of reads is low just 380 pages and duration is only 345 ms compared to the original one 63640 ms

    the reads in SQL profile will incude the read from the cache but this will not explain the very high of the reads, any idea how to track the root cause of this veru high reads ?

  • Can you post the query at least? The execution plan you captured would also be rather useful

    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
  • Hi Gail

    Unfortunately the query plan is very big to post, but here is what looks like the query:

    Also please note that the query plan when I ran the query in Management Studio is different from the one captured in SQL Profiler

    SELECT

    o.CMID,

    p0.FORMAT,

    p1.REFCMID

    FROM dbo.CRMOBJECTS o

    LEFT OUTER JOIN dbo.CRMOBJPROPS p0 ON o.CMID=p0.CMID

    LEFT OUTER JOIN dbo.CRMREFNOORD p1 ON o.CMID=p1.CMID

    LEFT OUTER JOIN dbo.CRMOBJPROPS p2 ON o.CMID=p2.CMID

    LEFT OUTER JOIN dbo.CRMLOCALES p5 ON p5.LOCALEID=p0.OLOCALEID,

    #CRMTMP t

    WHERE (((

    NOT (p2.RECIPSEMAIL IS NOT NULL)

    AND NOT (exists

    (SELECT *

    FROM dbo.CRMREF1 p3

    LEFT OUTER JOIN dbo.CRMOBJECTS d0 ON p3.REFCMID=d0.CMID

    WHERE o.CMID=p3.CMID

    AND (p3.PROPID=26)

    AND (d0.CLASSID!=140)

    ))

    )

    OR exists

    (SELECT *

    FROM dbo.CRMREF1 p4

    LEFT OUTER JOIN dbo.CRMOBJECTS d0 ON p4.REFCMID=d0.CMID

    WHERE o.CMID=p4.CMID

    AND (p4.PROPID=26

    OR p4.PROPID=25)

    AND (d0.CLASSID!=140)

    AND p4.REFCMID IN (343831,89,92,1782,1786,1813,1817,5653,6322,7327,7869,88,478))))

    AND o.CMID=t.CMID

    AND t.QUERYID=69

    Temp table will contain about 500 rows and the query will return 1 rows

  • Zip the .sqlplan file and attach the zip. Since it's XML, it compresses very well.

    The one you captured from Profiler please.

    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
  • Thanks Gail for trying to help

    I have sent you the quey plan, please let me know if you can see any issue or what is causing the high reads

    one think I will test is to add an index on CRMOBJECTS(OLOCALEID), all other joins are done on clustered index

    statistics are showing:

    Table 'CRMOBJECTS'. Scan count 553, logical reads 1764, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    this is very far from the 250 000 pages generated by the query and captured by SQL Profiler

    Best regards

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

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