ColumnIndex and memory requirements

Question

I have recently downloaded SQL Server Denali CTP3. I am particularily interested in the ColumnIndex feature. I loaded a sample of my own data into one of the sample databases and attempted to add a column index using SSMS

Unless I reduced the number of records to an almost trivial amount and reduced the number of columns in the index to, in my case, an unusable level, I get the following error:

"Could not get the memory grant of xxxKb because it exceeds the maximum configuration limit in workload group 'default' and resource pool 'default'

Is there a minimum hardware requirement for this? What should I be doing?

Thanks in anticipation

Regards

John King

Answer 1

I ran into this issue on myself on a less able virtual machine. I was finally able to get it to work, after fiddling around with Resource Governor and probably some more settings. Since I tried so many different things, I don't really know what was the fix. But I have the feeling that unless you have really configured resource pools on your machine, that you should not have to play with Resource Governor.

Can you give some facts about your machine? How much memory do you have? It this a dedicated SQL Server machine or a developer machines with lots of other programs? Have you configured "Max server memory" with sp_configure?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

I'm having a very similar situation as John King.

I'm using database AdventureWorksDWDenali, which can be found here http://msftdbprodsamples.codeplex.com/releases/view/55330.  Windows 7 Pro is installed on Virtual PC.  The host server has 16 GB memory and the processor is i7-920.  The Virtual PC is configured with 4 GB memory.

The following scripts were ran in Denali CTP3 of SSMS

I ran the following script to create a test table.

SELECT * 
INTO FactResellerSalesPtnd
FROM FactResellerSales

 I then ran the following scripts multiple times to increase the number of records in the table.

INSERT INTO FactResellerSalesPtnd 
SELECT * FROM FactResellerSales WITH(NOLOCK)

At this time there aproxamently 319,000 records

I run the following script to create the Columnstore index

CREATE NONCLUSTERED COLUMNSTORE INDEX [NonClusteredColumnStoreIndex-20110930-143300] ON [dbo].[FactResellerSalesPtnd]
( 
 ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
)WITH (DROP_EXISTING = OFF)

And the following error was returned: 
"Could not get the memory grant of 290016 KB because it exceeds the maximum configuration limit in workload group 'default' (2) and resource pool 'default' (2).  Contact the server administrator to increase the memory usage limit."

Now if I run the following script to delete 2000 records. And then re-run the previous script that created the Columnstore Index, everything works fine.

DELETE TOP(2000 )
FROM  FactResellerSalesPtnd 

I have not touched Resource Governer
 

 

Answer 3

Interesting that deleting 2000 rows was enough to create the index. Although that may be pure occasion.

I composed a script from your instruction, and on my first attempt it was sufficient to copy the source table three times to get the error message. My VM for Denali has only 2GB of memory. I then closed SQL Server Manangement Studio and everything else. After this I was able to build the columnstore index with three copies of the table. It still fails if I copy it four times.

Personally, I feel that something is not right here. The desired grant is way below the amount of memory, and quite a bit more than the size of the table. And I should certainly not have to fiddle with Resource Governor, which is a tool that far from every developer is acquainted with.

I've brought this up with some MS people. If nothing else, this should be better documented.

In practice, I think this error is much more likely to happen when you play around on a development workstation, than a real production server. But it's not a good start.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

I was pointed to this link which contains some useful information about columnstore indexes, including a formula to compute the memory required to create the index.

http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

What can I do if I do not have enough memory to build the columnstore index?

First, check your resource governor settings. The default setting for resource governor limits a query in the default pool to 25% of available memory even if the server is otherwise inactive. Consider changing the resource governor settings to allow the create index query to access more memory. A second option is to vertically partition a wide table into two or more tables so that each table has fewer columns. If a query touches both tables, the table will have to be joined, which will affect query performance. If you use this option, you will want to allocate columns to the different tables carefully so that queries will usually touch only one of the tables. This option would also affect any existing queries and loading scripts. A third option is to omit some columns from the columnstore index. Good candidates are columns that are infrequently touched by queries that require scanning large amounts of data. A fourth option is to provision your server with additional memory.

The above is from the link you provided. To be honest, the options aren't many!

Option 1 can be discarded since I'm not using (and it's not enabled) the resource governor. Vertical partionition will involve changes to the code and too much of a hassle to create an index, while the third index is 'drop columns'!!!! Very nice workarounds.

Can someone please tell me what I'm misunderstanding?

Answer 6

Nah, there is a third option: buy more memory. :-)

I think what they are saying is that even if we have not used Resource Governor before, we may have to use it to get this working. I know that I was able to get by this error by fiddling with Resource Governor in the past. Unfortunately, I don't remember all commands, and I'm short on time, so I don't have the time find out the real commands.

I appreciate that if you are a plain developer that you don't want to spend time on learning Resource Governor. But I think we should also keep in mind that this feature is not really designed with developer workstations in mind.

---

Erland Sommarskog, SQL Server MVP, [e-mail]