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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 2,027, Visits: 3,023
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1326967
Posted Monday, July 9, 2012 10:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 PM
Points: 2,013, Visits: 1,589
Sure. We will do it together…

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


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1326975
Posted Monday, July 9, 2012 11:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 PM
Points: 2,013, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1326995
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse