June 11, 2012 at 12:35 pm
this is 2008 R2 BTW...
I have a table as such:
Time_Series table:
ID int,
offset_days smallint,
theHour tinyint,
Value numeric (13,2)
primary key is on ID, offset_days, theHour
and a lookup table:
offset_days smallint,
label smallint
primary key is on offset_days
proc simply consists of this query:
SELECT SUM(ts.Value)
FROM Time_Series ts
JOIN Lookup l
ON ts.offset_days = l.offset_days
WHERE ID = @id
AND ts.offset_days between @StartOffset and @EndOffset
GROUP BY 1.label
the Time_Series table is very large, millions of rows
the lookup table is 18,000 rows.
indexes are minimal and fine, stats are updated frequently.
here is the question: the proc usually gets run with a year's worth of records, so around 15K rows from the Time_Series table is selected, and as such, 366 rows are joined to the Lookup table. When I run profiler, 60% of the time the number of reads is very large, like around 30K, 40% of the time Reads is only 5 or 6. Why is that? The 30K Reads usually happens when I run many instances of this same proc at the same time, but that shouldn't matter, should it? On closer inspection using SET STATISTICS IO it appears that the 30K logical reads are coming from the Lookup table. It's hard for me to catch because 99.9% of the time when I run the proc by itself, it behaves properly (very few reads). It almost looks to me like it's the difference between a clustered index seek and some hash join operation.
Would pinning the lookup table help?
Any explanations, please let me know.
June 11, 2012 at 12:44 pm
This sounds like parameter sniffing. Take a look at the links below:
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
PS Many thanks to Gail for these articles.
PPS Pinning tables is a thing of the past as of SQL Server 2005.
June 12, 2012 at 6:12 am
Try getting a look at the execution plan when it's running slow. Compare that to the execution plan when it's running fast. If they're different, that's a pretty good indication that it's bad parameter sniffing.
However, it might not be bad parameter sniffing. It could just be that you have some skewed data in the system that takes more reads doing the same work as the other data. Without the execution plans though, it's hard to be sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply