בדיקת ביצועים של Clustered ColumnStore Index - ilDBA Portal

בדיקת ביצועים של Clustered ColumnStore Index

06/11/2014 | פורסם על ידי

במאמרים הקודמים דיברנו על איך משתמשים ב-Clustered ColumnStore Index, במאמר זה נשחק קצת עם הביצועים שלו לשם ההתרשמות.

כדי לבצע את זה, ניצור 3 טבלאות על פי הבא:

SalesOrders_Clustered- עם cluster index על השדה SalesOrderNumber.

SalesOrders_NCCI- עם NCI על השדה SalesOrderNumber.

SalesOrders_CCI – עם CCI, שכמו שראינו מכיל את כל העמודות. נכניס לכל אחת מהטבלאות 12 מליון רשומות.

use [AdventureWorks2012]
--If the SalesOrders_Clustered table is already present, you can delete it, and then create new one with the name Orders
IF OBJECT_ID('SalesOrders_Clustered', 'U') IS NOT NULL BEGIN
DROP TABLE SalesOrders_Clustered
END
GO
--creating table for demo
CREATE TABLE SalesOrders_Clustered(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO

--inserting 12000000 rows into table
INSERT INTO SalesOrders_Clustered(OrderDate, SubTotal, SalesOrderNumber)
SELECT TOP 12000000
DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04'
AS DATETIME)),
ABS(a.object_id % 10),
CAST(ABS(a.object_id % 13) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
 
--creating clustered index on column SalesOrderNumber
CREATE CLUSTERED INDEX idx_SalesOrderNumber ON SalesOrders_Clustered(SalesOrderNumber)
GO
  
use [AdventureWorks2012]
--If the orders table is already present, you can delete it, and then create new one with the name Orders
IF OBJECT_ID('SalesOrders_NCCI', 'U') IS NOT NULL BEGIN
DROP TABLE SalesOrders_NCCI
END
GO
--creating table for demo
CREATE TABLE SalesOrders_NCCI(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO
--inserting 12000000 rows into table
INSERT INTO SalesOrders_NCCI(OrderDate, SubTotal, SalesOrderNumber)
SELECT TOP 12000000
DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04'
AS DATETIME)),
ABS(a.object_id % 10),
CAST(ABS(a.object_id % 13) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
--creating columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX
idx_NCCI_SalesOrderNumber
ON SalesOrders_NCCI(SubTotal,SalesOrderNumber)
GO
 
 
use [AdventureWorks2012]
--If the orders table is already present, you can delete it, and then create new one with the name Orders
IF OBJECT_ID('SalesOrders_CCI', 'U') IS NOT NULL BEGIN
DROP TABLE SalesOrders_CCI
END
GO
--creating table for demo
CREATE TABLE dbo.SalesOrders_CCI(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO
 
 
--Creating CLUSTERED COLUMNSTORE INDEX
CREATE CLUSTERED COLUMNSTORE INDEX
idx_CCI_SalesOrderNumber
ON dbo.SalesOrders_CCI
GO
 
--inserting 12000000 rows into table
INSERT INTO SalesOrders_CCI(OrderDate, SubTotal, SalesOrderNumber)
SELECT TOP 12000000
DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04'
AS DATETIME)),
ABS(a.object_id % 10),
CAST(ABS(a.object_id % 13) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

נריץ את קטע הקוד הבא שמבצע את אותה שאילתה עבור 3 הטבלאות הנ"ל כ- batch אחד. יש לבחור באופציה Include Actual Execution Plan ב- SQL server Management studio תחת התפריט Query.

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
 
--executing same query after creating clustered Index
--Examine the execution plan
SELECT
SalesOrderNumber
,sum(SubTotal) as 'Total Sales Amount'
,avg(SubTotal) as 'Average Sales'
,MIN(SubTotal) as 'Min Sales Amount'
FROM
SalesOrders_Clustered
WHERE
SalesOrderNumber>3
Group By
SalesOrderNumber
Order By
SalesOrderNumber
 
 
--Executing the query on the table with a NONCLUSTERED COLUMNSTORE INDEX.
 
 
SELECT
SalesOrderNumber
,sum(SubTotal) as 'Total Sales Amount'
,avg(SubTotal) as 'Average Sales'
,MIN(SubTotal) as 'Min Sales Amount'
FROM
SalesOrders_NCCI
WHERE
SalesOrderNumber>3
Group By
SalesOrderNumber
Order By
SalesOrderNumber
GO
 
--Executing the query on the table with a CLUSTERED COLUMNSTORE INDEX.
SELECT
SalesOrderNumber
,sum(SubTotal) as 'Total Sales Amount'
,avg(SubTotal) as 'Average Sales'
,MIN(SubTotal) as 'Min Sales Amount'
FROM
SalesOrders_CCI
WHERE
SalesOrderNumber>3
Group By
SalesOrderNumber
Order By
SalesOrderNumber
GO
 
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

תוצאות

התוצאה עבור כל טבלה אפשר לראות להלן. תחת ה- tab בשם execution plan נוכל לראות את המחיר היחסי באחוזי של כל שאילתה.

cci-tuning-1תחת message Tab נוכל לראות כמה logical read עבור כל שאילתה. נסכם את התוצאות:

'SalesOrders_Clustered:
Table 'SalesOrders_Clustered'. Scan count 5, logical reads 26884
Query cost relative to the batch: 86

SalesOrders_NCCI':
Table 'SalesOrders_NCCI'. Scan count 4, logical reads 484, physical reads 1
Query cost relative to the batch: 7

SalesOrders_CCI:
Table 'SalesOrders_CCI'. Scan count 4, logical reads 454, physical reads 0
Query cost relative to the batch: 7

ברור שהשימוש ב- CCI הוא הטוב ביותר במקרה שלנו לעומת NCI. שימוש ב – CCI ו- NCCI הרבה יותר מ- Clustered Index מבחינת I/O.

The following two tabs change content below.

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

תגובה אחת ל- “בדיקת ביצועים של Clustered ColumnStore Index”

דויד יצחק DBA | 24/07/2015 בשעה 01:54
commenter

חברים יקרים
יריות הפתיחה של SQL 2016
ראו את האחרונה שלי
David Itzhak: Practical SQL 2014 Data Mining and introduction to R
https://www.youtube.com/watch?v=eJO8G9if3EY
תהנו !

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

עבודה עם Clustered ColumnStore Index

במאמר זה נדבר על העבודה המעשית עם ה-Clustered ColumnStore Index (CCI). נסביר איך יוצרים אותו על חלק מהעמודות, נדבר על Catalog Views ואיך מתחזקים את ה-index. יצירה של CCI על חלק מהעמודות כל העמודות חיבות להיות מוכלות ב CCI [...]

Updateable Clustered ColumnStore Index – אשליה או אמת?

ה-Clustered ColumnStore Index (CCI) הם סוג של אשליה של SQL 2014 מכוון שהאינדקסים הם לא באמת ברי עדכון on the fly. אומנם אנחנו יכולים לעדכן את הנתונים בזמן אמת וזה בהחלט feature חדש עם הרבה אפשרויות, אבל יש להבין את המימוש [...]

Clustered ColumnStore Index מגבלות והערות נוספות

ה-Clustered ColumnStore Index הוא פיצ'ר נחמד של גרסה SQL Server 2014, אבל הוא מגיע עם כמה מגבלות ודברים ששווה לבדוק לפני שרצים לממש אותו. הנה כמה מהדברים האלה… תמיכה ב- isolation levels ל- SQL Server 6 isolation levels: Read Uncommitted, Read [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: