Without going into details, I have a query that creates an execution plan using all clustered and non-clustered indexes (sounds promising). Unfortunately, the request does not work well, and I'm afraid to understand why.
I use set statistics io onand see that one of the tables produces many scans and logical / physical readings:
SET statistics io ON
go
SELECT order_number,
audit_id,
orderadmission_net_paid_delta / 100.00,
'Admission',
orderadmission_net_paid_delta / 100.00,
performance_gl_description1,
section_data1,
performance_gl_code,
price_type_data1,
year(performance_start_date),
month(performance_start_date),
paymentmethod_type,
paymentmethod_name,
''
FROM JCRProdReplication.dbo.ts_audit WITH (NOLOCK)
JOIN JCRProdReplication.dbo.ts_order_admission WITH (NOLOCK)
ON orderadmission_audit_id = audit_id
LEFT JOIN JCRProdReplication.dbo.ts_order WITH (NOLOCK)
ON order_id = orderadmission_order_id
LEFT JOIN JCRProdReplication.dbo.ts_performance WITH (NOLOCK)
ON performance_id = orderadmission_performance_id
LEFT JOIN JCRProdReplication.dbo.ts_seat WITH (NOLOCK)
ON seat_id = orderadmission_seat_id
LEFT JOIN JCRProdReplication.dbo.ts_section WITH (NOLOCK)
ON section_id = seat_section_id
LEFT JOIN JCRProdReplication.dbo.ts_price_type WITH (NOLOCK)
ON price_type_id = orderadmission_price_type_id
LEFT JOIN JCRProdReplication.dbo.ts_order_payment WITH (NOLOCK)
ON orderpayment_audit_id = audit_id
LEFT JOIN JCRProdReplication.dbo.ts_payment_method WITH (NOLOCK)
ON paymentmethod_id = orderpayment_paymentmethod_id
WHERE audit_time >= '20140107'
AND audit_time < '20140108'
(72174 row(s) affected)
Table 'ts_payment_method'. Scan count 0, logical reads 4180, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_price_type'. Scan count 0, logical reads 4184, physical reads 26, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_section'. Scan count 0, logical reads 4184, physical reads 28, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_seat'. Scan count 0, logical reads 6276, physical reads 2240, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_performance'. Scan count 0, logical reads 4184, physical reads 50, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_order'. Scan count 0, logical reads 8368, physical reads 820, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_order_admission'. Scan count 71877, logical reads 288490, physical reads 44104, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_audit'. Scan count 1, logical reads 252, physical reads 5, read-ahead reads 246, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
What will be my next step in understanding why an index search will really show a lot of crawls / reads in statistics? (I googled all day and did not find an explanation).