כיוונן של In Memory table ו- Hash Index ב- SQL 2014 - ilDBA Portal

כיוונן של In Memory table ו- Hash Index ב- SQL 2014

28/05/2014 | פורסם על ידי

במאמר זה אני אדגים שימוש באחד הפיצ'רים של SQL Server 2014: יצירה של HASH index עבור טבלה מסוג In Memory עם ערכים שונים של BUCKET_COUNT. לצורך כך אני אשתמש ב-DMV החדש: sys.dm_db_xtp_hash_index_stats בשביל לבדוק את הסטטיסטיקות המתאימות, אחרי שאכלסתי את הטבלה עם רשומות.

הערות:

  • אינדקס מסוג HASH הוא אידיאלי עבור שאילתות עם equi-joins (האופרטור =)
  • האינדקסים הם מצביעים לשורות בזיכרון הבנויים כרשימה מקושרת בניגוד לאינדקסים מסוג B Tree שהם שיכפול של המידע,
  • לטבלה מסוג In Memory חייב להיות אינדקס מסוג primary key אחד, אחרת נקבל את הודעת השגיאה הבאה:

 

Msg 41327, Level 16, State 7, Line 1
The memory optimized table 'hash_tbl' must have at least one index or a primary

בדוגמא הראשונה ניצור את הטבלה הבאה:

USE [AdventureWorks2012]
GO
 
IF OBJECT_ID('dbo.Hash_tbl1', 'U') IS NOT NULL DROP TABLE dbo.Hash_tbl1;
GO
 
CREATE TABLE Hash_tbl1
  ([FirstName] nvarchar(20) COLLATE LATIN1_GENERAL_BIN2 NOT NULL,
  [ID] INT NOT NULL,
    INDEX IDX_ID NONCLUSTERED HASH ([ID]) WITH (BUCKET_COUNT=8))
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
 
 SET NOCOUNT ON;
INSERT INTO Hash_tbl1 VALUES
    ('ISIK',1),
    ('DAVID',2),
    ('ISRAEL',3),
    ('HANA',4),
    ('YUAV',5),
    ('ANAT',6),
    ('SHAKED',7),
    ('YUVAL',9),
    ('MEIR',9),
    ('AVI',10);

נתחקר את ה- DMV החדש sys.dm_db_xtp_hash_index_stats בשביל לבדוק את הסטטיסטיקות של BUCKET_COUNT עבור HASH index ע"י השאילתה הבאה:

SELECT OBJECT_NAME(S.object_id) as TableName
  , I.name AS IndexName
   , S.total_bucket_count
   , S.empty_bucket_count
   , S.avg_chain_length
   , S.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats S
JOIN sys.indexes I
on S.index_id = I.index_id
  AND
  S.object_id = I.object_id;

פלט:

img 1

בפלט ניתן לראות שלאינדקס IDX_ID:

  • השדה  total_bucket_count שווה ל 8,
  • לא כל ה- buckets הם בשימוש מכיוון שהעמודה empty_bucket_count שווה ל 7,
  • ניתן לראות שהיו 10 hash collisions כי העמודה max_chain_length שווה ל 10, כלומר יש לנו 10 שורות שמופו לאותו bucket בגלל hash collisions. Hash collisions משמעותו יותר משורה אחת שמופתה ע"י פונקצית ה- hash של ה- hash index לאותו bucket, במקרה שלנו זה רע מאוד !
  • יש 10 שורות,מסקנה: יש לנו 1 buckets עם 10 שורות  ו 7 buckets כל אחד עם 0 שורות,סה"כ 10 שורות,

כעת ניצור טבלה חדשה בשם Hash_tbl2, עם HASH index ו- BUCKET_COUNT השווה ל 2048. כאשר הטבלה נוצרה אכלסתי אותה עם 10000 שורות ע"י שימוש בלולאה שמילאה את הטבלה ב 10000 ערכים ייחודיים שונים עבר העמודה ID,

DROP TABLE dbo.Hash_tbl1;
 
IF OBJECT_ID('dbo.Hash_tbl2', 'U') IS NOT NULL DROP TABLE dbo.Hash_tbl2;
GO
 
CREATE TABLE Hash_tbl2
  ([ID] int NOT NULL,
  [FirstName] nvarchar(20) COLLATE LATIN1_GENERAL_BIN2 NOT NULL,
    INDEX IDX_ID NONCLUSTERED HASH ([ID]) WITH (BUCKET_COUNT=2048))
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
 
SET NOCOUNT ON;
DECLARE @I INT = 0;
WHILE @I < 10000
BEGIN
    INSERT INTO Hash_tbl2 VALUES(@I, 'Person Num = ' + cast(@I as char(6)));
  SET @I += 1;
END

נריץ את השאילתה הבאה כדי לבדוק את הסטטיסטיקה עבור ה- BUCKET_COUNT של HASH index ששמו IX_Id.

SELECT OBJECT_NAME(S.object_id) as TableName
  , I.name AS IndexName
   , S.total_bucket_count
   , S.empty_bucket_count
   , S.avg_chain_length
   , S.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats S
JOIN sys.indexes I
on S.index_id = I.index_id
  AND
  S.object_id = I.object_id
WHERE OBJECT_NAME(S.object_id) = 'Hash_tbl2';

פלט להלן:

2בפלט ניתן לראות שלאינדקס IDX_ID:

  • השדה  total_bucket_count שווה ל 2048,
  •  ניתן לראות שהיו hash collisions כי העמודה avg_chain_length שווה ל 4, השדה empty_bucket_count שווה ל 0,לפחות bucket אחד מכיל 6 שורות כי השדה max_chain_length שווה ל 6,

נזרוק את הטבלה וניצור טבלה שלישית עם 10000 שורות אבל עם BUCKET_COUNT = 10000:

DROP TABLE Hash_tbl2 ;
 
IF OBJECT_ID('dbo.Hash_tbl3', 'U') IS NOT NULL DROP TABLE dbo.Hash_tbl3;
GO
CREATE TABLE Hash_tbl3
  ([ID] int NOT NULL,
   [FirstName] nvarchar(20) COLLATE LATIN1_GENERAL_BIN2 NOT NULL,
    INDEX IDX_ID NONCLUSTERED HASH ([ID]) WITH (BUCKET_COUNT=10000))
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
 
SET NOCOUNT ON;
DECLARE @I INT = 0;
WHILE @I < 10000
BEGIN
    INSERT INTO Hash_tbl3 VALUES(@I, 'Person Num = ' + cast(@I as char(6)));
  SET @I += 1;
END

שוב נבדוק את הסטטיסטיקות לטבלה שיצרנו:

SELECT OBJECT_NAME(S.object_id) as TableName
  , I.name AS IndexName
   , S.total_bucket_count
   , S.empty_bucket_count
   , S.avg_chain_length
   , S.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats S
JOIN sys.indexes I
on S.index_id = I.index_id
  AND
  S.object_id = I.object_id
WHERE OBJECT_NAME(S.object_id) = 'Hash_tbl3';

הפלט:

3בפלט ניתן לראות שלאינדקס IDX_ID:

  • השדה  total_bucket_count שווה ל 16384, למה? SQL Server לוקח את ה- Bucket Count (BUCKET_COUNT = 10000 במקרה שלנו) ומעגל אותו לחזקה הבאה של 2 שגדולה שווה ל Bucket Count שהוגדר ביצירת ה- Hash Index, במקרה שלנו: 2 בחזקת 14 (16384).
  • השדה max_chain_length שווה ל-1. כלומר כל ערך של ID מופה ע"י פונקצית ה- hash ל- Bucket משלו.
  •  empty_bucket_count שווה ל 6384.הסיבה: total_bucket_count שווה ל 16384 פחות 10000 bucket שתפוסים.

לסיכום.במאמר זה ראינו שימוש ב – DMV sys.dm_db_xtp_hash_index_stats החדש של SQL 2014 שמאפשר כיוונן מהיר של Hash Index. הכללים למצוא את הערך האופטימאלי הראשוני של BUCKET_COUNTהם:

  • חלק את הערך של BUCKET_COUNT ב- distinct keys של האינדקס. אם הערך קטן מ 1 אזי BUCKET_COUNT נמוך מדי. ערך החלוקה צריך להיות בן 1.5 ל 2.
  • השתמש בשדה average ו-max chain length בשביל לדעת כמה ערכים מופו לאותו bucket.
  • אם הערכים של השדות average ו-max chain length יחסית גבוהים והשדה total_bucket_count נמוך, צור את הטבלה מחדש עם ערך גבוה של total_bucket_count. שינוי של In memory Table מצריך יצירה מחדש של הטבלה. (הפקודה alter לא נתמכת עבור In Memory Table ).
  • יש להשתמש בשאילתה הבאה בכדי למצוא את הערך האופטי מלי הראשוני של BUCKET_COUNT
SELECT
 POWER(
  2,
  CEILING( LOG( COUNT( 0)) / LOG( 2)))
  AS 'OPTIMAL BUCKET_COUNT'
FROM
 (SELECT DISTINCT <Index Columns>
  FROM <Target Table>) T

במקרה הנוכחי עבור הטבלה Hash_tbl3, בה יש 10000 ערכים שונים עבור עמודת המפתח, נשתמש בשאילתה הבא. התוצאה היא 16384:

SELECT
 POWER(
  2,
  CEILING( LOG( COUNT( 0)) / LOG( 2)))
  AS 'OPTIMAL BUCKET_COUNT'
FROM
 (SELECT DISTINCT ID
   FROM Hash_tbl3 ) Hash_Tbl;

להתראות במאמר הבא…
ד. יצחק

The following two tabs change content below.

דוד יצחק

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

6 תגובות ל- “כיוונן של In Memory table ו- Hash Index ב- SQL 2014”

commenter

תודה רבה
מאמר מצויין

commenter

מאמר מצויין, אלא שזה טוב לאחר איכלוס של הנתונים וכשמספר הנתונים הוא ידוע.
מה עושים בתכנון מראש של הטבלה. להגדיר מראש מספר גבוה? הרי יש לזה מחיר.

דויד יצחק | 10/07/2015 בשעה 21:06
commenter

תודה על ההערות !
אתה מופנה להרצאה האחרונה שלי

Performance Improvements in SQL 2014 – lessons from the field
http://globalhebrew.sqlpass.org/en-us/home.aspx?EventID=2343
ההרצאה כולל דמו , מצגת ושפע קטעי קוד

דויד יצחק | 10/07/2015 בשעה 21:10
commenter

תודה על ההערהֱ!
אתה מפונה להרצאה האחרונה שלי המקיפה בעברית ב- sqlpass שכוללת שפע קטעי קוד להורדה ו- demoes
http://globalhebrew.sqlpass.org/en-us/home.aspx?EventID=2343
Performance Improvements in SQL 2014 – lessons from the field

דויד יצחק DBA | 10/07/2015 בשעה 21:14
commenter

אתה מופנה להרצאה החדשה שלי ב 22.7.15 בעברית שתכלול את אחד הנושאים החמים ו- SQL 2016 :
https://globalhebrew.sqlpass.org/
Practical SQL 2014 Data Mining and introduction to R
Microsoft have acquired Revolution Analytics in order to help customers to find their "Big Data" value. Revolution Analytics is the leading commercial provider of software and services for R, the world’s most widely used programming language for statistical computing and predictive analytics. R will be integrated to SQL 2016 BI. I will give introduction to R and the advantages it gives to Microsoft Data Mining.
• An introduction to data mining
• SQL Server Data Tools (SSDT) Installations
• Demo : Creating a data mining solution with Microsoft association rules
• DEMO : Decision Trees example- In short
• Prediction with MDX queries , No need to know the syntax .
• Identifying Data Patterns – Predictive Models in SSAS

דויד יצחק dba | 10/09/2015 בשעה 19:48
commenter

שלום לכולם

חברים יקרים שלום
הרצאה שניה שלי ב Global Hebrew Virtual PASS Chapter's YouTube videos
הפעם על אחד הנושאים היותר מרתקים שכוללים דוגמאות מעשיות ל Data Mining ושימוש ב R בגובה העיניים ובצורה מעשית .
R היא שפה סטטיסטית הפופולרית ביותר בעולם והיא שימושית ביותר בכל תחומי ההנדסה , Data Science וה Big Data .לשימושכם !
David Itzhak: Practical SQL 2014 Data Mining and introduction to R
https://www.youtube.com/watch?v=eJO8G9if3EY
ההרצאה הקודמת כולל שפע דוגמאות מינימום תאוריה :
David Izhak: Performance Improvements in SQL 2014 – lessons from the field

השאר תגובה:

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

*



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

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

במאמרים הקודמים דיברנו על איך משתמשים ב-Clustered ColumnStore Index, במאמר זה נשחק קצת עם הביצועים שלו לשם ההתרשמות. כדי לבצע את זה, ניצור 3 טבלאות על פי הבא: SalesOrders_Clustered- עם cluster index על השדה SalesOrderNumber. [...]

עבודה עם 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 בלוגרים אהבו את זה: