July 9, 2012 at 10:29 am
Dev (7/9/2012)
ScottPletcher (7/9/2012)
Dev (7/9/2012)‘select only a very small % of the total rows’ is applicable to Clustered Index as well for seek operation else it will scan the cluster.
False, of course. If, for example, you have a table with two years' worth of data, 2010 and 2011, clustered on date, and you specify >= '20110101', SQL will read only the 2011 data.
That is another main reason you should leverage the clus index as much as possible.
It’s just an optimization added to SCAN operation but it won’t be good anyways if the data is skewed and ‘>= 20110101’ returns 90% of rows.
Nope, sorry. You need to review the difference between "scan" and "seek".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 9, 2012 at 10:44 am
Sure. We will do it together…
Scans vs. Seeks
http://blogs.msdn.com/b/craigfr/archive/2006/06/26/647852.aspx
July 9, 2012 at 11:28 am
Sorry for dragging the discussion. I setup a small test below.
CREATE TABLE DUMMY_TABLE
(
ID_COL INT,
DESC_COL CHAR(8000),
)
GO
INSERT INTO DUMMY_TABLE
VALUES
(1, REPLICATE('X',8000)),
(2, REPLICATE('X',8000)),
(3, REPLICATE('X',8000)),
(4, REPLICATE('X',8000)),
(5, REPLICATE('X',8000)),
(6, REPLICATE('X',8000)),
(7, REPLICATE('X',8000)),
(8, REPLICATE('X',8000)),
(9, REPLICATE('X',8000))
GO
CREATE CLUSTERED INDEX IX_DUMMY_TABLE ON DBO.DUMMY_TABLE
(
ID_COL
)
GO
SET STATISTICS IO ON
-- Query 1
SELECT * FROM DUMMY_TABLE WHERE ID_COL > 1 --AND ID_COL < 9
------------------------
-- Query 2
SELECT * FROM DUMMY_TABLE
------------------------
-- Query 3
SELECT * FROM DUMMY_TABLE WHERE ID_COL = 1 --AND ID_COL < 9
GO
DROP TABLE DUMMY_TABLE
SET STATISTICS IO OFF
Scott, you were right. Execution plans (actual/estimate) show SEEK operation for 1st & 3rd queries. It’s quite surprising to me but I have to accept the fact, it’s SEEK operation.
BUT when I see the IO statistics, I see no difference between first two queries. So the SEEK predicate on in first query is fake IMO.
(9 row(s) affected)
(8 row(s) affected)
Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(9 row(s) affected)
Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Table 'DUMMY_TABLE'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Viewing 3 posts - 121 through 122 (of 122 total)
You must be logged in to reply to this topic. Login to reply