<\/font>).<\/font><\/span>\n<\/p>\n\nFor the first step, I capped the max server memory for my test Denali SQL Server instance to a very low value (300 MBs did the trick in this case):<\/font><\/span>\n<\/p>\n\nEXEC sp_configure 'show advanced options', 1<\/font><\/span>\n<\/p>\n\nRECONFIGURE<\/font><\/span>\n<\/p>\n\n<\/font><\/span>\n<\/p>\n\nEXEC sp_configure 'max server memory (MB)', 300<\/font><\/span>\n<\/p>\n\nRECONFIGURE<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/span><\/span>\n<\/p>\n\nNext I attempted to create the following columnstore index:<\/font><\/span>\n<\/p>\n\nUSE [AdventureWorksDWDenali]<\/font><\/span>\n<\/p>\n\nGO<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/span>\n<\/p>\n\nCREATE NONCLUSTERED COLUMNSTORE <\/font><\/span>\n<\/p>\n\nINDEX [NCSI_FactInternetSales] <\/font><\/span>\n<\/p>\n\nON [dbo].[FactInternetSales]<\/font><\/span>\n<\/p>\n\n(<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[ProductKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[OrderDateKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[DueDateKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[ShipDateKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[CustomerKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[PromotionKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[CurrencyKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[SalesTerritoryKey],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[SalesOrderNumber],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[SalesOrderLineNumber],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[RevisionNumber],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[OrderQuantity],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[UnitPrice],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[ExtendedAmount],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[UnitPriceDiscountPct],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[DiscountAmount],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[ProductStandardCost],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[TotalProductCost],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[SalesAmount],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[TaxAmt],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[Freight],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[CarrierTrackingNumber],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[CustomerPONumber],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[OrderDate],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[DueDate],<\/font><\/span>\n<\/p>\n\n <\/font><\/span>[ShipDate])<\/font><\/span>\n<\/p>\n\n<\/font><\/span>\n<\/p>\n\nGO<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/span>\n<\/p>\n\nThis failed with the following error:<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/span>\n<\/p>\n\nThe statement has been terminated.<\/font><\/span>\n<\/p>\n\nMsg 8657, Level 17, State 5, Line 2<\/font><\/span>\n<\/p>\n\nCould not get the memory grant of 143560 KB<\/strong> because it exceeds the maximum configuration limit in workload group 'default' (2) and resource pool 'default' (2). <\/span>Contact the server administrator to increase the memory usage limit.<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/span>\n<\/p>\n\nSo the memory grant request was just over 140 MB. <\/span>Looking at the requirements of the columnstore index request and taking into account the equation in BOL, we have 8 MBs x 25 columns x 4 available processors = 800 MB. <\/span>Quite a bit higher than the 140 MB it needed at runtime. <\/span>But this was assuming 4 processors in the equation. <\/span>If we factor in just 1 proc being used – we have 200 MB which is closer to what was being requested and factors in the varying data types and sizes for columns defined in FactInternetSales.<\/font><\/span>\n<\/p>\n\n<\/font><\/span>\n<\/p>\n\n<\/font><\/span>\n<\/p>\n\nWhat if I capped the DOP but keep the memory capped at the low value? <\/span>I gave it a try:<\/font><\/span>\n<\/p>\n\n \n<\/p>\n
\n