posted 5/11/2012 9:41:45 AM by Amit Bansal - Views: [3434]
USE AdventureWorksDWDenali; GO -- observe the size of the table sp_spaceused N'dbo.FactProductInventory' --what's the size?? -- let us create a non clustered index CREATE NONCLUSTERED INDEX [NCI_FactProductInventory_DateKey] ON [dbo].[FactProductInventory] ([DateKey],[ProductKey]) INCLUDE ([UnitCost],[UnitsOut]); GO -- observe the size of the Non-clustered index select (page_count * 8)/1024 as IndexSizeInMB from sys.dm_db_index_physical_stats (db_id(),object_id(N'dbo.FactProductInventory'), NULL, NULL, 'SAMPLED') where index_id > 0 --whats the size?? --create a non clustered columnstore index CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory] ON dbo.FactProductInventory ( ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance ); -- determine the size of the columnstore index SELECT SUM(on_disk_size_MB) AS TotalSizeInMB FROM ( (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB FROM sys.indexes AS i JOIN sys.partitions AS p ON i.object_id = p.object_id JOIN sys.column_store_segments AS css ON css.hobt_id = p.hobt_id WHERE i.object_id = object_id('FactProductInventory') AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') UNION ALL (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB FROM sys.indexes AS i JOIN sys.partitions AS p ON i.object_id = p.object_id JOIN sys.column_store_dictionaries AS csd ON csd.hobt_id = p.hobt_id WHERE i.object_id = object_id('FactResellerSalesPtnd') AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') ) AS SegmentsPlusDictionary -- whats the size??
Amit Bansal (Member since: 3/12/2011 4:59:54 PM) Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions. FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346 LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Leave a comment