I have a query that has a very costly INDEX SEEK operation in the execution plan. In order to track down the cause i set IO STATISTICS on and ran it. In the problem section it gave the following statistics:
Table
'#TempStudents_Enrollment2_____________________________________000000004D5F'. Scan count 0, logical reads 60,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0,
logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table
'#TempRace2______________________________________________000000004D58'. Scan count 1, logical reads 1,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0,
logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table 'RefRace'. Scan count 120,
logical reads 240, physical reads 1,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table 'RefFedEnctyRaceCatg'. Scan
count 18, logical reads 36, physical
reads 2, read-ahead reads 0, lob
logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#43B0BA0F'. Scan count 1,
logical reads 60, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table '#42BC95D6'. Scan count 1,
logical reads 60, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table '#41C8719D'. Scan count 1,
logical reads 60, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table '#40D44D64'. Scan count 1,
logical reads 60, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table
'#LEA2_________________________________________________000000004D56'. Scan count 1, logical reads 60,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table '#39332B9C'. Scan count 1,
logical reads 60, physical reads 0,
read-ahead reads 0, lob logical reads
0, lob physical reads 0, lob
read-ahead reads 0.
Table
'#School2________________________________________________000000004D57'. Scan count 1, logical reads 29164,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table
'#GenderKey______________________________________________000000004D5A'. Scan count 1, logical reads 29164,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table
'#LangAcqKey_____________________________________________000000004D5B'. Scan count 1, logical reads 29164,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table
'#TransferCatKey___________________________________________000000004D5C'. Scan count 1, logical reads 29164,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table
'#ResCatKey______________________________________________000000004D5D'. Scan count 1, logical reads 29164,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table
'RPT_SnapShot_1_4_StuPgm_Denorm'. Scan
count 2344954, logical reads 4992518,
physical reads 16, read-ahead reads 8,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table '#3FE0292B'. Scan count 1,
logical reads 2344954, physical reads
0, read-ahead reads 0, lob logical
reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table
'RPT_SnapShot_1_4_StuEnrlmt_Denorm'.
Scan count 20, logical reads 87679,
physical reads 0, read-ahead reads
87425, lob logical reads 0, lob
physical reads 0, lob read-ahead reads
0.
Table
'#GradeKey_______________________________________________000000004D59'. Scan count 1, logical reads 1,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
What should I look for in here when i'm looking to improve the performance? The line with over 2 million for the Scan count looked suspicious to me but I really don't know. Does anyone see anything here that i should look into in more detail?
