{"id":1500,"date":"2014-04-22T08:50:26","date_gmt":"2014-04-22T06:50:26","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1500"},"modified":"2014-04-22T12:00:11","modified_gmt":"2014-04-22T10:00:11","slug":"sql-server-2014-ujdonsagok-2-in-memory-oltp-hash-indexek","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2014\/04\/22\/sql-server-2014-ujdonsagok-2-in-memory-oltp-hash-indexek\/","title":{"rendered":"SQL Server 2014 \u00fajdons\u00e1gok – 2. In-Memory OLTP – hash indexek"},"content":{"rendered":"

A mem\u00f3riat\u00e1bl\u00e1khoz k\u00f6telez\u0151 minimum 1 indexet l\u00e9trehozni, \u00e9s ez az index egy hash index kell legyen. Mivel k\u00f6telez\u0151 rajtuk egy primary key is, ez\u00e9rt az els\u0151 indexen nem kell sokat gondolkodni, az a pk m\u00f6g\u00f6tti index lesz.
\nA hash index hasonl\u00f3 mint a .NET Dictionary vagy b\u00e1rmely m\u00e1s rendszer hash t\u00e1bl\u00e1ja. Az index \u00e9rt\u00e9kekb\u0151l hasht k\u00e9peznek, az azonos hash-\u0171 sorokat \u00f6sszel\u00e1ncolj\u00e1k pointerek ment\u00e9n. Az adatok NEM lapokon vannak elhelyezve, mivel a lapok a diszk adatok miatt voltak kialak\u00edtva a norm\u00e1l t\u00e1bl\u00e1k eset\u00e9n. Mivel nincsenek lapok, nem veszekednek a sz\u00e1lak a lapok latch-el\u00e9s\u00e9n se (mint a .NET lock kulcssz\u00f3), \u00edgy sokkal nagyobb p\u00e1rhuzamoss\u00e1got \u00e9rnek el, mint a kor\u00e1bbi strukt\u00fara eset\u00e9n. Diszk t\u00e1bl\u00e1kn\u00e1l ha egy lapon sokan matatnak egyszerre, be kell v\u00e1rj\u00e1k egym\u00e1st. Itt minden sor szinten van t\u00e1rolva, azaz sokkal finomabb felbont\u00e1s\u00fa r\u00e9gi\u00f3kat kell v\u00e9deni, r\u00e1ad\u00e1sul a m\u00f3dos\u00edt\u00e1s sose helyben t\u00f6rt\u00e9nik, de err\u0151l majd a konkurenci\u00e1r\u00f3l sz\u00f3l\u00f3 r\u00e9szben \u00edrok.<\/p>\n

Nincsenek GAM, SGAM, PFS, IAM \u00e9s m\u00e1s egy\u00e9b szok\u00e1sos adat\u00f6sszetart\u00f3 lapok, csakis az indexek tartj\u00e1k egyben egy t\u00e1bla sorait. Ez\u00e9rt kell minimum 1 index a t\u00e1bl\u00e1ra.
\nTov\u00e1bbi indexeket is l\u00e9tre lehet hozni, \u00f6sszesen max. 8-at. Az\u00e9rt ennyit, mert az \u00fctk\u00f6z\u0151 hash-ek miatt \u00f6ssze kell l\u00e1ncolni a sorokat, \u00edgy minden sorba be van \u00e9p\u00edtve fixen hash index sz\u00e1m\u00fa pointer a l\u00e1ncol\u00e1shoz, de be kellett hat\u00e1rolni a sz\u00e1mukat, ne fogjanak el t\u00fal sok helyet. \u00cdgy n\u00e9z ki egy sor, a v\u00e9g\u00e9n vannak a l\u00e1ncolt list\u00e1khoz a pointerek :<\/p>\n

\"InMemoryTableRow\"<\/a><\/p>\n

Az els\u0151 mez\u0151kr\u0151l majd a p\u00e1rhuzamoss\u00e1g kezel\u00e9s\u00e9r\u0151l (concurrency) sz\u00f3l\u00f3 r\u00e9szben \u00edrok.<\/p>\n

A hash indexn\u00e9l fontos a BUCKET_COUNT, ez szab\u00e1lyozza mennyire sz\u00f3rjon a hash f\u00fcggv\u00e9ny. Ha t\u00fal kicsire vessz\u00fck, hossz\u00fa l\u00e1ncolt list\u00e1k alakulnak ki egy bucketen bel\u00fcl. Ha t\u00fal nagy, feleslegesen eszi a mem\u00f3ri\u00e1t, \u00e9s lass\u00edtja a scaneket, mivel minden bucketet v\u00e9gig kell n\u00e9znie scan sor\u00e1n. Mivel 64 biten dolgozunk, egy pointer 8 byte, \u00edgy hash index m\u00e9rete BUCKET_COUNT * 8 byte, csak mindig a legk\u00f6zelebbi, 2 hatv\u00e1nyra kerek\u00edtik bel\u00fclr\u0151l. Azaz 1000-et megadva 1024 v\u00f6d\u00f6r keletkeze, ami 8192 byte (ez persze a gyakorlatban nagyon pici lenne, nem lenne hat\u00e9kony index). \u00c9rdemes akkor\u00e1ra venni, mint ah\u00e1ny k\u00fcl\u00f6nb\u00f6z\u0151 \u00e9rt\u00e9ke van a kulcsnak * 1-2. \u00c9rhet\u0151, hisz \u00edgy nem nagyon lesz sokelem\u0171 a l\u00e1ncolt lista egy bucketen bel\u00fcl, kev\u00e9s lesz az \u00fctk\u00f6z\u00e9s.
\nPrimary keyn\u00e9l k\u00f6nny\u0171 a helyzet, mivel egyedi \u00e9rt\u00e9keket tartalmaz. Ha a t\u00e1bla p\u00e9ld\u00e1ul 3 milli\u00f3 sort tartalmaz, \u00e9s v\u00e1rhat\u00f3an a k\u00f6zelj\u00f6v\u0151ben se n\u0151 mondjuk 5 milli\u00f3 f\u00f6l\u00e9, akkor egy hasonl\u00f3 nagys\u00e1grend\u0171 BUCKET_COUNT j\u00f3 lehet. K\u00e9s\u0151bb, ha sokkal t\u00f6bb sor lesz, majd \u00fajra l\u00e9trehozzuk a t\u00e1bl\u00e1t m\u00e1s BUCKET_COUNT-tal. Ebb\u0151l l\u00e1tszik, hogy a norm\u00e1l t\u00e1bl\u00e1kkal szemben itt nem annyira \u00f6nhangol\u00f3 minden, mint megszoktuk.
\nNorm\u00e1l oszlopokn\u00e1l egy select count(distinct oszlop) lek\u00e9rdez\u00e9ssel k\u00f6nny\u0171 megn\u00e9zni, mennyi egyedi \u00e9rt\u00e9k van a t\u00e1bl\u00e1ban. Ha mondjuk 100 k\u00fcl\u00f6nb\u00f6z\u0151 \u00e9rt\u00e9k van egy 5000 soros t\u00e1bl\u00e1ban, akkor egy nagy BUCKET_COUNT eset\u00e9n is csak 100 bucketben lesz \u00e9rt\u00e9k, azaz a bucketekben \u00e1tlagban 50 sor lesz, amiben m\u00e1r csak line\u00e1risan lehet keresni. Magyarul, erre nem j\u00f3 a hash index, ide sima fa alap\u00fa indexre lesz sz\u00fcks\u00e9g, mivel azt is l\u00e9tre lehet hozni a mem\u00f3ria t\u00e1bl\u00e1kon. <\/p>\n

N\u00e9zz\u00fck meg a gyakorlatban!<\/p>\n

Hozzunk l\u00e9tre egy mem\u00f3ria t\u00e1bl\u00e1t az AdventureWorks Person t\u00e1bl\u00e1ja adataival:<\/p>\n

\r\nCREATE TABLE [Person].[Person_InMem]\r\n(\r\n\t[BusinessEntityID] [int] NOT NULL,\r\n\t[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),\r\n\t[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),\r\n\t[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),\r\n\t[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),\r\n\r\nCONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH \r\n(\r\n\t[BusinessEntityID]\r\n)WITH ( BUCKET_COUNT = 1000)\r\n)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )\r\nGO\r\n\r\nINSERT INTO [Person].[Person_InMem]\r\n           ([BusinessEntityID]\r\n           ,[PersonType]\r\n           ,[NameStyle]\r\n           ,[Title]\r\n           ,[FirstName]\r\n           ,[MiddleName]\r\n           ,[LastName]\r\n           ,[Suffix]\r\n           ,[EmailPromotion]\r\n           ,[rowguid]\r\n           ,[ModifiedDate])\r\nSELECT [BusinessEntityID]\r\n      ,[PersonType]\r\n      ,[NameStyle]\r\n      ,[Title]\r\n      ,[FirstName]\r\n      ,[MiddleName]\r\n      ,[LastName]\r\n      ,[Suffix]\r\n      ,[EmailPromotion]\r\n      ,[rowguid]\r\n      ,[ModifiedDate]\r\n  FROM [Person].[Person]\r\n<\/pre>\n
Az eredeti t\u00e1bl\u00e1b\u00f3l kihagytam az xml oszlopokat, azok nem t\u00e1mogatottak mem\u00f3ria t\u00e1bl\u00e1kban. Hash bucket sz\u00e1moss\u00e1gnak 1000-et adtam meg. A t\u00e1bl\u00e1ban 19972 sor van. Mivel primary key-r\u0151l van sz\u00f3, ugyanennyi k\u00fcl\u00f6nb\u00f6z\u0151 \u00e9rt\u00e9k van benne. De csak 1024 bucket\u00fcnk van, az\u00e9rt \u00e1tlagosan kb. 20 sor ker\u00fcl egy bucketbe. N\u00e9zz\u00fck meg, igaz-e az elm\u00e9let, az \u00faj dm_db_xtp_hash_index_stats n\u00e9zettel:<\/p>\n
\r\nSELECT \r\n   object_name(hs.object_id) AS 'object name', \r\n   i.name as 'index name', \r\n   hs.total_bucket_count,\r\n   hs.empty_bucket_count,\r\n   floor((cast(empty_bucket_count as float)\/total_bucket_count) * 100) AS 'empty_bucket_percent',\r\n   hs.avg_chain_length, \r\n   hs.max_chain_length\r\nFROM sys.dm_db_xtp_hash_index_stats AS hs \r\n   JOIN sys.indexes AS i \r\n   ON hs.object_id=i.object_id AND hs.index_id=i.index_id\r\nwhere object_name(hs.object_id) = 'Person_InMem'\r\n<\/pre>\n
\r\nobject name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length\r\n-------------- --------------------------------- -------------------- -------------------- ---------------------- -------------------- --------------------\r\nPerson_InMem   PK_Person_InMem_BusinessEntityID  1024                 0                    0                      19                   38\r\n<\/pre>\n
L\u00e1that\u00f3, hogy az \u00e1tlagos l\u00e1nc hossza egy bucketben 19, de van legal\u00e1bb egy 38 hossz\u00fa is, mivel a hash f\u00fcggv\u00e9ny nem teljesen egyenletes.
\nJ\u00f3 ez nek\u00fcnk? Nagyon nem. Ide\u00e1lis esetben 1-2 t\u00e9tel van csak egy bucketben, de 10 f\u00f6l\u00f6tt m\u00e1r er\u0151sen \u00e9rezhet\u0151 lesz a teljes\u00edtm\u00e9nyvesztes\u00e9g. L\u00e1that\u00f3, hogy az \u00fcres bucketek (\u00e1tt\u00e9rek a v\u00f6d\u00f6r sz\u00f3ra, de el\u00e9g h\u00fcly\u00e9n hangzik) sz\u00e1ma 0. Ez is probl\u00e9ma, “nincs hely” az \u00faj sorok r\u00e9sz\u00e9re, azaz m\u00e9g nagyobb torl\u00f3d\u00e1s lesz k\u00e9s\u0151bb. Az \u00fatmutat\u00f3 szerint az \u00fcres v\u00f6dr\u00f6k sz\u00e1ma legal\u00e1bb 33% kell legyen, hogy legyen hely a t\u00e1bl\u00e1nak n\u00f6vekedni.<\/p>\n
Pr\u00f3b\u00e1ljuk meg magasabbra venni a v\u00f6dr\u00f6k sz\u00e1m\u00e1t, mondjuk 10000-re. Mivel mem\u00f3ria t\u00e1bl\u00e1kn\u00e1l nincs alter table, \u00fajra l\u00e9tre kell hozni, m\u00e1s param\u00e9terekkel, majd \u00fajrat\u00f6lteni. Ez ut\u00e1n a hash statisztik\u00e1k:<\/p>\n
\r\nobject name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length\r\n-------------- --------------------------------- -------------------- -------------------  ---------------------- -------------------- --------------------\r\nPerson_InMem   PK_Person_InMem_BusinessEntityID  16384                4933                 30                      1                   8\r\n<\/pre>\n
Na, ez m\u00e1r sokkal jobban n\u00e9z ki. 16384 v\u00f6d\u00f6r van (10000 ut\u00e1n ez volt a k\u00f6vetkez\u0151 2 hatv\u00e1ny), 30%-a a v\u00f6dr\u00f6knek \u00fcres, \u00e1tlagosan 1 elem van minden v\u00f6d\u00f6rben (nyilv\u00e1n kicsit t\u00f6bb, mivel 19000 sor van 16000 v\u00f6d\u00f6rre).<\/p>\n
De nem mindig lehet csak a v\u00f6dr\u00f6k sz\u00e1m\u00e1val ilyen sz\u00e9p helyzetet el\u0151\u00e1ll\u00edtani, ha t\u00fal sok ism\u00e9tl\u0151d\u00e9s van az adatokban. Hozzunk l\u00e9tre egy m\u00e1sodik indexet is, ez\u00fattal a FirstName oszlopra:<\/p>\n
\r\nSELECT count(distinct FirstName) FROM [AdventureWorks2012].[Person].[Person]; --1018\r\n\r\nCREATE TABLE [Person].[Person_InMem]\r\n(\r\n\t[BusinessEntityID] [int] NOT NULL,\r\n\t[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),\r\n\t[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),\r\n\t[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),\r\n\t[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),\r\n\r\nCONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH \r\n(\r\n\t[BusinessEntityID]\r\n)WITH ( BUCKET_COUNT = 10000)\r\n,\r\nINDEX [ix_FirstName] NONCLUSTERED HASH\r\n(\r\n\t[FirstName]\r\n) WITH ( BUCKET_COUNT = 1000)\r\n)\r\nWITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )\r\n<\/pre>\n
De nem j\u00f6n l\u00e9tre az index, mert:
\nIndexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.<\/p>\n
Ez van, a hasht gyorsan kell tudni el\u0151\u00e1ll\u00edtani, ebben a verzi\u00f3ban legal\u00e1bbis nem mentek bele, hogy a nyelvek nemzeti saj\u00e1toss\u00e1gait kihaszn\u00e1lva is k\u00e9pezzenek hasht. Ezekre lehet majd rendes fa alap\u00fa indexet l\u00e9trehozni, de most koncentr\u00e1ljunk m\u00e9g a hash indexekre.<\/p>\n
L\u00e1ssuk a d\u00e1tum oszlopon:<\/p>\n
\r\nSELECT count(distinct [ModifiedDate]) FROM [AdventureWorks2012].[Person].[Person] --1290\r\n\r\nCREATE TABLE [Person].[Person_InMem]\r\n(\r\n\t[BusinessEntityID] [int] NOT NULL,\r\n\t[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),\r\n\t[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,\r\n\t[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n\t[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),\r\n\t[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),\r\n\t[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),\r\n\r\nCONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH \r\n(\r\n\t[BusinessEntityID]\r\n)WITH ( BUCKET_COUNT = 10000)\r\n,\r\nINDEX [ix_ModifiedDate] NONCLUSTERED HASH\r\n(\r\n\t[ModifiedDate]\r\n) WITH ( BUCKET_COUNT = 1000)\r\n)\r\nWITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )\r\n<\/pre>\n
D\u00e1tum oszlopra m\u00e1r megy a hash index.<\/p>\n
A hash statisztik\u00e1k:<\/p>\n
\r\nobject name    index name       total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length\r\n-------------- --------------- -------------------- -------------------  ---------------------- -------------------- --------------------\r\nPerson_InMem   ix_ModifiedDate                 1024                 262                      25                   26                  143\r\n<\/pre>\n
Csapnival\u00f3ak az eredm\u00e9nyek. B\u00e1r van 25% szabadon, azaz a v\u00f6dr\u00f6k sz\u00e1m\u00e1t nem l\u0151tt\u00fck al\u00e1, m\u00e9gis \u00e1tlagosan 26 sor van egy v\u00f6d\u00f6rben. Mi\u00e9rt? Mert minden d\u00e1tum kb. 20-szor szerepel a kulcsban, nem el\u00e9g egyedi az oszlop. Az ilyenekre nem igaz\u00e1n hat\u00e9kony a hash index.
\nA k\u00f6vetkez\u0151 r\u00e9szben megn\u00e9zz\u00fck, hogyan t\u00e1rolja \u00e9s hogyan haszn\u00e1lja a hash indexeket az SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"
A mem\u00f3riat\u00e1bl\u00e1khoz k\u00f6telez\u0151 minimum 1 indexet l\u00e9trehozni, \u00e9s ez az index egy hash index kell legyen. Mivel k\u00f6telez\u0151 rajtuk egy primary key…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,87],"tags":[],"class_list":["post-1500","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2014"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1500"}],"version-history":[{"count":16,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1500\/revisions"}],"predecessor-version":[{"id":1564,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1500\/revisions\/1564"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}