SQL Profiler and very high reads

Question

SQL Profiler and very high reads

bouarroudj Mohamed

Old Hand

Points: 378
More actions
July 1, 2015 at 8:52 am

#319507

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 ?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

bouarroudj Mohamed Old Hand Points: 378 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

bouarroudj Mohamed Old Hand Points: 378 More actions · Answer 4

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