Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213

Introduction to Indexes: Part 2 – The clustered index Expand / Collapse
Author
Message
Posted Monday, July 9, 2012 10:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 3,033, Visits: 4,641
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1326967
Posted Monday, July 9, 2012 10:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 8, 2015 4:20 AM
Points: 1,932, Visits: 1,598
Sure. We will do it together…

Scans vs. Seeks
http://blogs.msdn.com/b/craigfr/archive/2006/06/26/647852.aspx


Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad
Post #1326975
Posted Monday, July 9, 2012 11:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 8, 2015 4:20 AM
Points: 1,932, Visits: 1,598
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.


Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad
Post #1326995
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse