InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 13:32:43 GMT20http://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]Grant Fritchey (4/2/2009)[/b][hr]Mainly based on the scan count. I'm still assuming, possibly incorrectly, that the second query was able to read from cache where as the first had to go to disk. I think if the second query had to go to disk as well, it'd be quite a bit longer.[/quote]Well they both had to read partially from disk, both have physical IOs.Thing with the logical IOs is (if I'm remembering correctly), they're cumulative over all 'scans'. So a query that does 500 reads in one scan is doing the same number of IOs as a query that does 500 in 100 scans[quote](by the way, do you just memorize Connect bugs or something) [/quote]No. This came up in the private newsgroups a couple weeks back and the connect was referenced there. [quote]Generally, I don't count on the logical IOs as a single measure.[/quote]Likewise, but in this case it's all we have so far.Of course, in this case it's a moot point since the two queries that we're comparing aren't equivalent. One just has a join, the other a join and a filter.Apples and pears.Thu, 02 Apr 2009 07:22:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxYeah, I've read those. I was never completely dependent on IO, but it sure made me second guess even how much I used it.But Gail was write to call me on such an unequivocal statement based on inadequate information. I made some undefined assumptions. I should have asked the OP a couple of more questions first.Thu, 02 Apr 2009 06:09:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxHey Grant,You might well be right - the first query does do more logical IO, but that's not the whole story. If the whole table is scanned in allocation order it might be more efficient in practice - might. The other thing is CPU. 2005 often made a bit of a mess of queries against a single partition - especially if the tables were large enough to warrant an parallel plan (one thread per partition), or if a constant scan with a loop join to a lookup or whatever was executed many times. It's impossible to know for sure which is the 'better query' just from looking at the SQL.As an aside, the addiction to measuring logical IO alone that some have is a bit one-dimensional. See Joe Chang's excellent blogs on CPU usage and table scanning at [url=http://sqlblog.com/blogs/joe_chang/default.aspx][/url]. Makes you think!Cheers,/PaulThu, 02 Apr 2009 05:53:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]GilaMonster (4/2/2009)[/b][hr][quote][b]Grant Fritchey (3/27/2009)[/b][hr]1. The first query, by a long shot. Although it has a higher number of physical reads. But if both these queries are accessing the same data then it was probably in cache when you ran the second query.[/quote]Why do you say that? It has higher logical IOs (100 000 vs 80 000) as well as the higher physical[/quote]Mainly based on the scan count. I'm still assuming, possibly incorrectly, that the second query was able to read from cache where as the first had to go to disk. I think if the second query had to go to disk as well, it'd be quite a bit longer.However, I wasn't aware that the scans were actually a little less of a decent measure than I had originally believed (by the way, do you just memorize Connect bugs or something) so maybe my original estimate is off.Generally, I don't count on the logical IOs as a single measure. I combine them with what I see in the execution plan and, possibly incorrectly, the scans, along with the actual execution times. What's not known here is how the queries were tested. Did the OP clear the cache and the buffers prior to each execution? If not, you're not getting a thorough test. At the very least we'd need to run the queries in reverse order and see how that affects the results.Thu, 02 Apr 2009 05:38:32 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxHi again Gail,Sure. Everything you say is true. The only thing I would say (in a small voice) is that my previous comments were 'off the top of my head' - you clearly spent more time on that last one! I do know really that a seek can return 0 or many rows - I was thinking of a key (+ unique-ifier) or RID lookup when I wrote that. The brain was in a different place from the fingers :blush:The points I was failing to make clearly are:1. What we understand and see in plans as scans and seeks won't necessarily tally exactly with stats IO ideas of scans and seeks.2. The range seek in your article is not all that different from a full scan really.Point 1If you look at the XML show plan for example, you'll see all sorts of index scan, seek, and range references, nested within the same operation. Some 'seeks' are EQ operations, others are GE and LE ranges, and so on. Just because that is the logical plan we can see, doesn't mean that the deep dark internals of the engine aren't free to do it slightly differently in practice, or to record different things as 'seeks' or 'scans' if they choose to. You mentioned semantics in your last post - I think there is a lot to that - and it is also true that 'scan' in particular seems to have different meanings in different places (e.g the tooltip). I also think that the terms in question might mean different things in different places. I'm happier with that answer than thinking that the reported scan count is somehow 'random' or just 'wrong'.Point 2So a full scan runs through the whole thing, and a range scan seeks to find a start and an end. The middle bit is quite similar though, eh? And sure, sometimes we'll get the thing traversing a linked list, and sometimes it'll scan from the IAM. That complexity doesn't change the fact that the operations are extremely similar aside from the initial seek: It's not as if a range seek starts from the very top of index and seeks down the b-tree to the leaf for every row. For a range-start, range-end operation I mean. Multiple prefix EQ seeks (e.g IN query), or key/RID lookups from a loop join, sure they do traverse the structure that way.Finally, yes prefetch is read-ahead and in a separate area. But in principle, those actions are physically a scan, even if the pages fetched that way are then seeked on a row basis, or range-scanned - it doesn't matter that it's in a separate area, pages were scanned to satisfy the query. I'm just sayin'.I'm not trying to be difficult at all, just explaining that I'm talking in a looser, wider sense. Hey I hope that makes some sort of sense to you.Thanks though for the articulate and accurate post, appreciated :)Thu, 02 Apr 2009 05:31:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]Paul White (4/2/2009)[/b][hr][quote]Not so sure. The article you wrote simply shows that a range seek/scan can cover all the rows in a table. Well, yeah ;)[/quote]I was challenging your statement that a seek returns 1 row. It does not.In the terms used by the query optimiser and query processor, a seek returns 0 or more rows and does a search down the b-tree to find the start or end of the range. A scan reads all the rows in the table and may or may not traverse the b-tree. Even if it does, it doesn't search, but just uses the b-tree to find the leaf pages[quote]The 'Index Seek' operator in that example has a tool-tip description of "*scan* a particular range of rows from a nonclustered index". So it's a seek logical operator doing a physical scan of a portion of the index? [/quote]English language semantics here. Perhaps 'Reads a particular range of rows' would be more correct. [quote]Even a full scan of an index is a seek operation (ordered or unordered) down the b-tree.[/quote]Not necessarily. There are other ways to get at the leaf pages than via the b-tree. Especially in an unordered scan of a cluster.[quote] To complicate things further, a single-row seek done as part of a key lookup operation (if the QO estimates that it will access many rows eventually) may implement ordered or unordered prefetch, which is a scan, isn't it?[/quote]We're crossing portions of the engine now.Seek/scan is a QO/QP operation. Prefetch is done and controlled by the storage engine. Prefetch is what affects the readahead reads in the IO stats[quote]What is the real different between a load of consecutive single-row seeks and an ordered scan?[/quote]A lot. Each of the single-row seeks would traverse the b-tree. The scan would do it at most once, and may not need to access the b-tree at all if it's doing an unordered scan, as that just uses the IAM pages.So, if you were to compare 500 single row seeks with a scan that reads 500 pages, the number of IOs would be dramatically different. If we say those 500 rows are on 100 data pages (at 5 rows per page) and the b-tree is 2 levels deep (just the root and the leaf) then 500 single row seeks would read a total of 1000 pages. The scan would read 100 or 101 pages.That, by the way, is why bookmark/key lookups are so expensive. They are single row seeks.Thu, 02 Apr 2009 03:49:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote]Unless I am missing something there?[/quote]Basically what it means is that the scan count can't be trusted as not all operators set it in the same way.[/quote]I'm inclined to agree - though I'd love to try a repro script for it![quote][b]GilaMonster (4/2/2009)[/b][hr]Not at all. Seeks can return any number of rows, up to the total number of rows in the table. It's a seek if SQL used the b-tree to find the row or the start or end of the range. It's a scan if the entire table/index is read with no search.See - [url]http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/[/url][/quote]Not so sure. The article you wrote simply shows that a range seek/scan can cover all the rows in a table. Well, yeah ;)The 'Index Seek' operator in that example has a tool-tip description of "*scan* a particular range of rows from a nonclustered index". So it's a seek logical operator doing a physical scan of a portion of the index? Even a full scan of an index is a seek operation (ordered or unordered) down the b-tree. To complicate things further, a single-row seek done as part of a key lookup operation (if the QO estimates that it will access many rows eventually) may implement ordered or unordered prefetch, which is a scan, isn't it?I guess my point is that it is a complex area, and probably just a question of semantics - are we talking logical or physical operations, for example? What is the real different between a load of consecutive single-row seeks and an ordered scan?I love SQL Server :-D-- edited cos I can't quote properly...Thu, 02 Apr 2009 02:59:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]Paul White (4/2/2009)[/b][hr]My understanding is that a 'seek' always selects one row (either by key or rid).A scan may be a scan of any part of an index, which involves more than a 1-row seek.[/quote]Not at all. Seeks can return any number of rows, up to the total number of rows in the table. It's a seek if SQL used the b-tree to find the row or the start or end of the range. It's a scan if the entire table/index is read with no search.See - [url]http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/[/url][quote]Unless I am missing something there?[/quote]Basically what it means is that the scan count can't be trusted as not all operators set it in the same way.Thu, 02 Apr 2009 02:39:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxHi!I've just looked at the connect article and MS seem to have closed it as 'by design'.My understanding is that a 'seek' always selects one row (either by key or rid).A scan may be a scan of any part of an index, which involves more than a 1-row seek.In the example given, a composite primary key on two columns is searched using a value for only the first column.There is nothing to say that this will always select one row (unless there is another unique constraint or index on the first column alone, I guess). Since the engine is searching a range in the index (all keys with the value given in the first position) it must be reported as a range scan rather than as a seek, even if it only returns on row.This makes a kind of weird M$-sense to me.Unless I am missing something there?Thanks,Paul [quote][b]GilaMonster (4/2/2009)[/b][hr][quote][b]suhasini.m (3/27/2009)[/b][hr]2. What does Scan Count exactly mean. And howz it related to IO.?[/quote]The scan count is actually not that useful. It should indicate how many times an object is read, however it doesn't do that consistently. Best bet is to completely ignore that and look at the logical IOssee - [url]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387322[/url][/quote]Thu, 02 Apr 2009 02:27:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]Grant Fritchey (3/27/2009)[/b][hr]1. The first query, by a long shot. Although it has a higher number of physical reads. But if both these queries are accessing the same data then it was probably in cache when you ran the second query.[/quote]Why do you say that? It has higher logical IOs (100 000 vs 80 000) as well as the higher physicalThu, 02 Apr 2009 02:26:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]suhasini.m (3/27/2009)[/b][hr]2. What does Scan Count exactly mean. And howz it related to IO.?[/quote]The scan count is actually not that useful. It should indicate how many times an object is read, however it doesn't do that consistently. Best bet is to completely ignore that and look at the logical IOssee - [url]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387322[/url]Thu, 02 Apr 2009 02:05:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxHi,In order to decide which is the 'better' query, we would need to know what defines 'best' for you:1. Logical IO2. CPU3. Concurrency4. Response time5. Overall server throughput4. Query plan coolness5. ...and so onI would imagine that the second query is prettier as an execution plan. The equality predicate on the partitioning column should mean that the query optimizer (QO) just searches the one partition instead of the whole table.It may then need to scan (either a full partition scan, or a range scan) to satisfy the join condition. It sounds (from the scan count) as if a loop join is being used. One would think that the QO might prefer a HASH or MERGE join here - try forcing it with a hint and check out the effects.The first query does 20%-odd more logical IO, so in that narrow sense it is less efficient.It's difficult to go into heaps of detail without seeing the actual execution plan - so if you can attach those that'd be great.BTW a range scan is a very efficient way of grabbing a load of rows all at once (the scan range can be small or large) and will usually outperform the equivalent number of key/rid lookups.Cheers, Wed, 01 Apr 2009 22:19:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]suhasini.m (3/27/2009)[/b][hr]Thanks for the detailed reply.I am running both the quries at the same time and also doing DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE before for both the queries.If this is a table or index scan howz the execution cost of 2nd query is less than the first one. Ultimately a table or index scan is costlier than an index seek on this table as it has some millions of rows. I have checked the query plan and there was no index or table scan on this table. I will try and post the plan.[/quote]You just have to remember that those are estimated costs, not actual costs. They're useful as a general measure, but not as a real determining factor. Check out the situation in this [url=http://scarydba.wordpress.com/2009/03/19/execution-plan-estimated-operator-cost/]blog post[/url] to see why.[quote]Is there a way to check the number of bytes of data that is being fetched by a sproc per execution if not a query?[/quote]Try checking out sys.dm_exec_query_stats. It's an aggregate, but it'll tell you what you want to know.Fri, 27 Mar 2009 09:40:16 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxThanks for the detailed reply.I am running both the quries at the same time and also doing DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE before for both the queries.If this is a table or index scan howz the execution cost of 2nd query is less than the first one. Ultimately a table or index scan is costlier than an index seek on this table as it has some millions of rows. I have checked the query plan and there was no index or table scan on this table. I will try and post the plan.Is there a way to check the number of bytes of data that is being fetched by a sproc per execution if not a query?Fri, 27 Mar 2009 09:29:36 GMTgoodlaughhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]suhasini.m (3/27/2009)[/b][hr]I have 2 queriesSELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1SELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1AND Table1.Column2 = XXXBoth these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.The IO statistics of these two queries are as follows.1st Query---------Table1 - Scan Count:0 Logical Reads:100000 Physical Reads:2002nd Query---------Table1 - Scan Count:7000 Logical Reads:80000 Physical Reads:100There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.1. Which is the better query in terms of IO?2. What does Scan Count exactly mean. And howz it related to IO.?3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?4. Is there a better way to compare the IO of queries?5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?[/quote]1. The first query, by a long shot. Although it has a higher number of physical reads. But if both these queries are accessing the same data then it was probably in cache when you ran the second query.2. Scans are the number of times that the engine had to scan an index or a table. It tells you how the data is being accessed and can help explain performance issues. Scans are not inherently bad, but high numbers of scans should give you pause.3. There must be scans going on. Do you have user defined functions in the query? These can mask all kinds of performance problems including scans. Post the execution plans if you can.4. Generally, that's the best way.5. Not specific to the execution of a particular query, no. You can get these measures through a server-side trace. Otherwise you're looking at performance counters that measure what's happening within the server, probably not helpful for performance tuning a particular query.Fri, 27 Mar 2009 08:38:30 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxI have 2 queriesSELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1SELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1AND Table1.Column2 = XXXBoth these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.The IO statistics of these two queries are as follows.1st Query---------Table1 - Scan Count:0 Logical Reads:100000 Physical Reads:2002nd Query---------Table1 - Scan Count:7000 Logical Reads:80000 Physical Reads:100There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.1. Which is the better query in terms of IO?2. What does Scan Count exactly mean. And howz it related to IO.?3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?4. Is there a better way to compare the IO of queries?5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?Fri, 27 Mar 2009 08:07:39 GMTgoodlaughhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspx[quote][b]suhasini.m (3/27/2009)[/b][hr]IO Statistics[/quote][url]http://technet.microsoft.com/en-gb/library/cc966500.aspx[/url]Fri, 27 Mar 2009 07:29:47 GMTmaechismo_8514http://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxYes, they are very handy, aren't they?Fri, 27 Mar 2009 06:32:30 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxwhat would you like to know about IO statistics?Fri, 27 Mar 2009 06:12:53 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic684946-360-1.aspxIO StatisticsFri, 27 Mar 2009 06:09:28 GMTgoodlaugh