Home Dashboard Directory Help
Search

Error 701 for columnstore creation/rebuild due to too optimistic memory request by Klaus Ondrich


Status: 

Active


4
0
Sign in
to vote
Type: Bug
ID: 804271
Opened: 10/1/2013 10:42:57 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

We have several wide tables (up to 200 cols) with nonclustered columnstore indexes covering all columns. Though our server has 192 GB RAM (max mem 175GB for SQL Server instance) and memory grant in default pool of resource governor is set to 50% we often get error 701 during columnstore index creation/rebuilds.

Comparing SQL Server 2012 SP1 (11.0.3381) and SQL Server 2014 CTP1 (11.0.9120) sys.dm_exec_query_memory_grants shows completely different memory requests for the same table containing the same data during rebuild (200 columns in table/index, thereof 28 string columns), given MAXDOP of 4):
SQL Server 2012 SP1:
requested_memory_kb: 4,495,160
granted_memory_kb: 5,319,480

SQL Server 2014 CTP1:
requested_memory_kb: 8,865,528
granted_memory_kb: 17,115,448

It seems that memory requests on SQL Server 2014 CTP1 are much more conservative compared to the tight calculation of SQL Server 2012 SP1. SQL Server 2014 CTP1 provides much more memory for the rebuild process.
Consequently, SQL Server 2014 CTP1 does not cancel rebuild due to error 701.

Since we greatly suffer from this issue, we would really appreciate any fix for this (e.g. more accurate memory grant estimation).
Details
Sign in to post a comment.
Posted by Ludo from Belgacom on 11/8/2013 at 2:14 AM
We are having the same issue , server has min mry 4096 & Max mry 8192
When I run create columnstore index with 14 columns, 170 Milj records with a maxdop value higher than 16 (64 cores on server) than the create index failes with error 701.
query manager grants show following result.

Maxdop    session_id    request_id    scheduler_id    dop    request_time    grant_time    requested_memory_kb    granted_memory_kb    required_memory_kb    used_memory_kb    max_used_memory_kb    query_cost    timeout_sec    resource_semaphore_id    queue_id    wait_order    is_next_candidate    wait_time_ms    plan_handle    sql_handle    group_id    pool_id    is_small    ideal_memory_kb
0    67    0    22    31    2013-11-08 11:06:36.470    2013-11-08 11:06:36.470    4469216    4469216    4469216    3378832    3378832    1921,540509    48038    0    NULL    NULL    NULL    NULL    0x06000600D415C02050CBA1FC0200000001000000000000000000000000000000000000000000000000000000    0x02000000D415C02032EDDD414035B7E821E4569EE1EBC7640000000000000000000000000000000000000000    2    2    0    4469216
                                                                                                
16    67    0    22    16    2013-11-08 11:07:35.000    2013-11-08 11:07:35.000    2382728    2382728    2382728    2194912    2211040    2307,621166    57690    0    NULL    NULL    NULL    NULL    0x06000600FE0C321550CBA1FC0200000001000000000000000000000000000000000000000000000000000000    0x02000000FE0C3215E5A4B6568B9DE2510BCD3E67B7C7DC2F0000000000000000000000000000000000000000    2    2    0    2382728

Seeting Max Memory to 12 GB doesn't help
Posted by Klaus Ondrich on 10/23/2013 at 5:57 AM
Now with SQL Server 2014 CTP 2 the situation has changed again:
requested_memory_kb: 4,505,336
granted_memory_kb: 4,505,336

Could anybody explain those ups and downs from version to version?
Sign in to post a workaround.