What you should know if you are using Logical IOs to measure query plan quality

re: What you should know if you are using Logical IOs to measure query plan quality

Lubor — Thu, 28 Jun 2012 15:54:50 GMT

GulliMeel - you are correct. And the number of CPU operations per row with scan depends on many things - the predicate, number of data types of the columns in the row, data types in the comparison, need for implicit data type conversion, etc. Therefore it is impossible to give generic answer “it takes x CPU cycles to process each row”.

The “seeks” cost also depends on many factors. Usually we are seeking for all matching values – so we have first “locate” the start of the match and then continue walking forward while the compared values match.

I think it is almost impossible to come up with general rule “what is better” covering the various combinations. The best is to measure…

re: What you should know if you are using Logical IOs to measure query plan quality

GulliMeel — Thu, 28 Jun 2012 06:04:59 GMT

In the case of table scan the CPU time was more as it has to check for ach and every row whether that is matching with one of the passed value. Thus you could say 10 cpu opertaion. But 10 cpu opertaion should be cheaper than 18 logical IO's(through index seek). Also, I am wondering whether sql server uses 100 CPU opertaion or 10 cpu opertaion or 55 cpu opertaions for those 10 rows.SO it should be included as well. The index is covering index.If it were not things might have been different (i have not tried it yet) but I will try it later.

re: What you should know if you are using Logical IOs to measure query plan quality

LuborK — Mon, 25 Aug 2008 21:00:08 GMT

I agree - the example is totally artificial and its goal is only to demonstrate the point. The point about tuning queries with large amounts of I/Os first is correct unless you are dealing with queries executed tens of thousands of time a minute. That is frequent scenario for web backends. There shaving off several I/Os from each execution may cause a miracle.

re: What you should know if you are using Logical IOs to measure query plan quality

colin leversuch-roberts — Thu, 21 Aug 2008 10:37:37 GMT

It's a bit of an artificial example and of the "it depends" scenario. I usually try and make sure I use a table of several million rows and varying widths when I do this type of test. In fairness I'm usually looking to tune queries which take thousands ( and sometimes millions ) of io - I rarely get down to looking at queries under 100 io. Good points though.

re: What you should know if you are using Logical IOs to measure query plan quality

MoansNogood — Mon, 14 Apr 2008 08:38:25 GMT

Good one, Lubor.

Something else regarding LIO: As with most other things in databases, the number of times something happens has no correlation to its duration.

Anjo Kolk found that there are many different kinds of LIO, some fast, some slow, some with serialisation, some not - all depending on their purpose when called upon from the source code.

So if you have 100 LIOs taking a total of 100 seconds (that would be a horrible day, indeed, but let's pretend for the sake of clarity) then we don't know if each of them took 1 second each or whether two of them took 42 seconds each and the rest (98 LIOs) took 16 seconds in total.

If there's heavy skew like the above, we should be able to focus on the two really bad ones and go for their source.

So that's ANOTHER level we have to dive into one day, my friend :-)).

best regards,

Mogens

PS: This has no relevance here, but inside Oracle Anjo found close to a 1000 different LIO's - I don't think it's much different inside SQL Server.

Airline Travel » What you should know if you are using Logical IOs to measure query plan quality

Thu, 10 Apr 2008 20:22:08 GMT

PingBack from http://www.travel-hilarity.com/airline_travel/?p=2584