Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DLTaylor
Posting Yak Master
136 Posts |
 Posted - 2014-01-08 : 05:51:07
|
| Hi,We have virtualised our Physical SQL server and running performance tests on the 2.The virtual server was allocated the same memory/cpu/ processors etc as the physical server.When I compare a basic select query on the 2 servers the Virtual server returns the records quicker (see elapsed time).I would expect it to be a bit quicker … BUTWhen I look at the IO and TIME STATISTICS I can see the big difference is in physical reads and wondered why? and if this is part reason for the speed difference. Can anyone explain why the physical reads would be lower on the Virtual compared to the physical server?Any help would be great!ThanksI have attached the IO & TIME stats output from the 2 queries:virtualSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(433204 row(s) affected)Table 'TestData'. Scan count 9, logical reads 909372, physical reads 466, read-ahead reads 909527, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 8078 ms, elapsed time = 28680 ms.PhysicalSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(433563 row(s) affected)Table 'TestData'. Scan count 9, logical reads 909372, physical reads 8840, read-ahead reads 901376, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 9923 ms, elapsed time = 91779 ms. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-08 : 12:49:35
|
| Run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before each test.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-01-08 : 17:49:11
|
| Yes, any physical reads could definitely skew the results. Physical reads are necessary only when the data is not already in a SQL buffer.To get around that, simply run the query(s) twice on each server, and ignore the first run, since it might involve physical I/O.A few logical reads won't hurt either query much. But a large difference in logical reads should indicate a better query plan, possibly because of index and/or row estimate differences. |
|
|
|
DLTaylor
Posting Yak Master
136 Posts |
Posted - 2014-01-09 : 08:32:18
|
| good tip - tho i havent run the DBCC commands as that is our production DBGood idea to run multi times. I have tried this and the reads level out.The “Elapsed Time” is proving intriguing as while cpu time is similar. The elapsed time is often +50% faster on the virtual server.Any pointers on what elapsed time is made up of?I have tried seardhing the web but results steer me to “date time …elapsed time” queries and to the STATISTICS elapsed time (I know cpu time is the Sum of the cpu and can be more than elapsed time ;-)) |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-01-09 : 10:21:40
|
| "Elapsed time" really can't be accurately compared for a single query, since it can be affected by everything else running on the server at that time.Yes, I would never use those DBCC commands just to reduce physical I/O for a single query. But, if you do plan to run DBCC DROPCLEANBUFFERS, be sure to do a CHECKPOINT in the database at issue first so that all its pages will be clean. It'd be a real waste to run that command and still have a difference in physical I/O due to dirty page(s). |
|
|
|
|
|
|
|
|
Physical Reads