February 7, 2013 at 11:19 pm
Hi Guys,
I have an issue with a select query that has different run times on 2 different servers. The query runs for 50 ms on the DEV environment but 7 seconds on our QA environment. The table sizes are the shame on both environments, the memory configurations are also the same. I have however noticed that the CU versions are different on both instances. I havenot checked with the disk admins about the disk subsystem differences yet.
The io statistics on both environments are as follows:
DEV
Table 'Table1'. Scan count 0, logical reads 27, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 2, logical reads 6, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table3'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table4'. Scan count 0, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 29, physical reads 2, read-ahead reads 2895, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table6'. Scan count 9, logical reads 27, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table7'. Scan count 6, logical reads 18, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table8'. Scan count 0, logical reads 9, physical reads 4, 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 'Table9'. Scan count 0, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
QA
Table 'Table1'. Scan count 1, logical reads 67087, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 209905, logical reads 670069, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table3'. Scan count 1, logical reads 20410, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table4'. Scan count 3, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table5'. Scan count 0, logical reads 39, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table6'. Scan count 9, logical reads 6722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table7'. Scan count 6, logical reads 21, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table8'. Scan count 0, logical reads 9, 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 '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 'Table9'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Can anybody assist me with this? Is there anything else I need to check? Why do I have to Worktables in the QA statistics than the DEV results above? Why is the logical reads so high on the first two tables in QA?
regards
IC
February 7, 2013 at 11:44 pm
following are the factors responsible which make the difference in statistics
1) exec plan cached or not ?
2) data available in buffer or not ?
3) statistics upodated or not ?
4) indexes same or not ? (also values in sys.dm_db_physical_stats )
5 ) amount of data
6 ) other internal resources like RAM, CPU or IO ?
7 ) other backgpound processes runnig at that time ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply