Is there more to the way a query is executed other than the execution plan itself ?

• Marc Kuppens

  SSC Enthusiast

  Points: 152

  More actions

  September 5, 2008 at 8:19 am

  #123306

  Hi,

  I'm currently investigating performance problems and I want to be sure not to make any misinterpretations.

  I've got following query

  SELECT T1.OBJECT_ID,

  T2.U24_CURRENTSTATECASUS,

  T2.U1F_STARTDATE,

  T2.U20_ENDDATE,

  T2.U3E_CASUSINWAITSTATUS,

  T2.U47_CONTRACTNRBEGIN,

  T2.U48_CONTRACTNREND,

  T1.OBJECT_CLASS_ID,

  T1.SECURITY_ID,

  T1.U88_DEPARTMENTMARKING,

  T1.OBJECT_ID,

  T2.OBJECT_CLASS_ID,

  T2.SECURITY_ID,

  T2.U2C_DEPARTMENTMARKING,

  T2.OBJECT_ID

  FROM DOCVERSION T1

  INNER JOIN CONTAINER T2

  ON (T1.OBJECT_ID = T2.U26_MAINDOCUMENTID)

  WHERE T1.HOME_ID IS NULL

  AND T1.OBJECT_CLASS_ID IN ('{EBDE04E8-3E45-4057-99EF-220847443EBC}','{764059AB-9CD1-4AB6-898B-0FC00D4CABD9}','{68A04E64-3D92-4A5C-B3E9-E15752F4AA95}','{756BA1CD-39B5-4CD8-9127-AA1DECD71270}',

  '{2B8D5EDA-DE86-4E16-8D4B-9990ABB16892}')

  AND T2.HOME_ID IS NULL

  AND T2.OBJECT_CLASS_ID IN ('{4435F4E2-8E07-4919-8517-C8CEA9E1233E}')

  AND ((T1.U9D_CLASSIFICATIONSUBTYPE = 'OFFER_DEMAND')

  AND (T1.IS_CURRENT = 1))

  Container has got 591736 rows and docversion 388567, as the first got 96 columns and the second 205 columns I prefer not to post the create statements (yet).

  This is the execution plan according to SQL Profiler

  Execution Tree

  --------------

  Parallelism(Gather Streams)

  |--Filter(WHERE: ((([T1].[u9d_classificationsubtype]='OFFER_DEMAND' AND [T1].[is_current]=1) AND [T1].[home_id]=NULL) AND (((([T1].[object_class_id]=2B8D5EDA-DE86-4E16-8D4B-9990ABB16892 OR [T1].[object_class_id]=756BA1CD-39B5-4CD8-9127-AA1DECD71270) OR [T1].[object_class_id]=68A04E64-3D92-4A5C-B3E9-E15752F4AA95) OR [T1].[object_class_id]=764059AB-9CD1-4AB6-898B-0FC00D4CABD9) OR [T1].[object_class_id]=EBDE04E8-3E45-4057-99EF-220847443EBC)))

  |--Bookmark Lookup(BOOKMARK: ([Bmk1000]), OBJECT: ([EdossierPRD].[dbo].[DocVersion] AS [T1]) WITH PREFETCH)

  |--Nested Loops(Inner Join, OUTER REFERENCES: ([T2].[u26_maindocumentid]) WITH PREFETCH)

  |--Sort(ORDER BY: ([T2].[u26_maindocumentid] ASC))

  | |--Clustered Index Scan(OBJECT: ([EdossierPRD].[dbo].[Container].[I_Container24] AS [T2]), WHERE: ([T2].[object_class_id]=4435F4E2-8E07-4919-8517-C8CEA9E1233E AND [T2].[home_id]=NULL))

  |--Index Seek(OBJECT: ([EdossierPRD].[dbo].[DocVersion].[PK__DocVersion__76CBA758] AS [T1]), SEEK: ([T1].[object_id]=[T2].[u26_maindocumentid]) ORDERED FORWARD)

  PS changed added a blank between ":" and "(" to prevent interpretation as smiley

  The result set contains 168 rows and it took (still according to SQL Profiler) 832337 Logical reads and a duration of 22141 milliseconds

  I then executed the exact same query with various intervals, the result set and execution plan do not change

  The logical logical reads count and duration however :

  logical reads : 697489 Milliseconds : 13000

  logical reads : 494762 Milliseconds : 3046

  logical reads : 612920 Milliseconds : 10015

  logical reads : 496827 Milliseconds : 4985

  As for the Elapsed time I can understand but should the same query on the same data using the same execution plan not give the same number of logical reads ?(give or take a few because it's on LIVE data)

  In other words is there more to the way a query is executed other than the execution plan itself ?

  Thanks for any hints or thoughts

  Marc