October 29, 2009 at 2:04 pm
Hi -
I don't know if this will help or not, but it's something I've seen in the past. Is your tempdb configured differently between production and test? Specifically, is production configured at too low an initial setting and is forced to grow to accommodate your request? And is test configured high enough to handle it? You should look at the growth factor on the data and log files too. If it's at 8MB to start and growing by 1MB at a time, it would cause slowness while tempdb grows. I hope that your production environment is configured correctly for tempdb, but you never know and should confirm it.
Best of luck.
October 30, 2009 at 2:40 pm
.
November 3, 2009 at 3:21 pm
I have the same problem, execute query in mgmt studio and in stored proc producing different statistic results. Shouldn't they have the same results since they are both identical queries executed in the same server and db?
The one in stored proc is taking much longer than the mgmt studio.
Can anyone explain to me why? Thanks.
query from mgmt studio:
SQL Server Execution Times:
CPU time = 2497 ms, elapsed time = 4542 ms.
Table 'JS_Vacancy'. Scan count 9, logical reads 39924, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JS_FinalTable'. Scan count 9, logical reads 251245, physical reads 0, read-ahead reads 15054, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 8, logical reads 15428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
same query from stored proc:
SQL Server Execution Times: CPU time = 1243750 ms, elapsed time = 189595 ms.
Table 'JS_FinalTable'. Scan count 7700, logical reads 297912779, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JS_Vacancy'. Scan count 9, logical reads 40700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 15598, 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.
November 12, 2009 at 9:23 pm
Can't tell just from what you posted but it has all the smackings of "parameter sniffing".
--Jeff Moden
December 15, 2009 at 7:15 am
Too bad CRICHARDSONS last reply was in September.
This sounds related to a problem I've having w/ SQL 2008. We upgraded last month (Db > 1TB).
A query that used to take about 2 minutes now takes 8. When I look at IO Stats, one thing that really stuck out was:
Table 'Worktable'. Scan count 7116008, logical reads 53304197, physical reads 0, read-ahead reads 0
The rest of my scancounts and logical reads are much much smaller.
Ala...
Table 'tbldEncntr'. Scan count 2, logical reads 29960, physical reads 53, read-ahead reads 14975
Table 'tbldAdj'. Scan count 1, logical reads 72430, physical reads 166, read-ahead reads 72407
Table 'tbllAdjCd'. Scan count 1, logical reads 8, physical reads 2, read-ahead reads 5
Table 'tbldChrg'. Scan count 4, logical reads 123750, physical reads 189, read-ahead reads 61863
Table 'tbllSite'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0
Table 'tbllSiteGrp'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0
Table 'tbldPaym'. Scan count 1, logical reads 78286, physical reads 197, read-ahead reads 78262
Table 'tblsSitePaycl_Perf'. Scan count 1, logical reads 138, physical reads 5, read-ahead reads 134
December 15, 2009 at 8:02 am
BobMcC (12/15/2009)
Too bad CRICHARDSONS last reply was in September....
euhm .... September 2008
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2009 at 8:11 am
ALZDBA (12/15/2009)
BobMcC (12/15/2009)
Too bad CRICHARDSONS last reply was in September....
euhm .... September 2008
ha ha
Hey, I said september! So I off by a year. 🙂
December 15, 2009 at 10:49 am
Ever calculated how many milliseconds that are ???
Milliseconds because that's what we are used to take into account 😀
:hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2009 at 3:42 pm
Try to rebuild the indexes on the tables that are used in that query
(If it suddenly starts working slower and recompiling doesn't help, set statistics io shows big numbers it is very likely that indexes on some of the tables used in the query are heavily fragmented)
January 12, 2010 at 10:00 am
It can happen that you have HIGH logical reads for empty table (or small table), and query processing time is way too long. The reason is your table has so many empty pages. You simply drop and re-create your table.
Optimizer does not use index when table is empty or has only few rows. Instead, it does logical reads for thousands and thousands of empty pages. I believe Microsoft is still not aware of this problem.
DBCC checktable(<tablename>) will tell you how many pages.
Hope this helps,
Thinh Ho
June 12, 2013 at 10:59 pm
I had same problem. The same query does massive logical reads on 2008 R2.
Stats on 2008.
Table 'Worktable'. Scan count 5, logical reads 5763460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '******'. Scan count 1, logical reads 1295, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Stats on 2005
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 '*******'. Scan count 1, logical reads 1306, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
When I compared the two execution plans. On 2008, it uses merge join and there is a sort. On 2005, it uses hash join.
So I forced hash join on 2008. sql then generated same query plan as 2005 and there was no sort no logical reads any more. My query was then 3 times faster.
Not sure why there is such a difference. I can confirm index fragmentation on tables involved was very low. I also even updated stats with full scan before enforcing hash join. 2008 still uses different query plan.
In a nutshell, in my case, I had to force a hash join to resolve the issue.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply