Restoring a database onto a larger APS

For benchmarking purposes I was restoring a backup taken on a 4 node APS onto an 8 node APS (Microsoft Analytics Platform System). And the unexpected happened; I started to receive warnings that the system was running low on available disk space! (For those of you who never worked with APS; an 8 node full rack APS can store hundreds of terabytes!). 
It turned out that some colleagues had been way too generous in allocating database space. To me, this was an opportunity to explore the storage management and I learned something new about restoring APS databases which makes perfectly sense when you get it! Below a walk through and recommendations in case you have to restore an APS database yourself.

The APS database to Backup

The database to move onto a larger APS is 24 TB in size and is called ‘Blog’:


The database is distributed in 4 equally sized portions across the 4 compute nodes SQLCMP01-SQLCMP04:



Backup of an APS database

To backup the database we have to assign access permissions to a windows account that has write access to a file share on a target server via the ‘sp_pdw_add_network_credentials’ command. On the backup server you have to set up a file share called ‘backups’ and grant R/W permissions. And then, start the ‘backup database’ command.

Backup Database

The Full backup to the remote file share completed in 36minutes 14seconds and resulted in a backup set of 1.3TB on disk. The dataset from each compute node was saved in parallel into separate userdata_x_0.bak files. (Primary bottleneck is the write throughput of the file share we are writing to: ~640 MB/sec throughput). (See Fig.4) 

Fig. 3- 4 Node Backup Set


Restoring an APS database

To restore this backup onto another APS, we just have to do the same: assign access permissions to the backup server and start the ‘Restore database’ command.



Although it was a straight forward backup & restore exercise executed according the book, I noticed the following: a backup set that gets restored onto an appliance with a larger number of compute nodes will grow in allocated database size. Its in proportion to the number of Compute nodes: SQL Server APS initially restores the database initially onto 4 nodes to match the source configuration, and then internally redistributes the data onto all 8 nodes. On the extra nodes identical datasets are allocated. (See Fig.6).


The restore operation resulted in a database with twice the original size on the larger destination system:



How to check data volume in use

There are a couple of simple steps to check on the actual disk space allocated within the database and how to free up over-allocated space in the database.



To check on the actual allocated Data you can run the DBCC PDW_Showspaceused command.



This command outputs per compute node the allocated space in kilobytes. In our case approx. 1.6 TB of compressed Clustered Columnstore data resides inside the database; the allocated 24TB for the Database is therefor a bit overkill and we can safely free up some of the space.

4 Node

To free up space we can use the ALTER DATABASE command and define the desired new space allocations. Also, we can shrink down the transaction log to a minimal size (also to minimize the time spend on log files recreation). To change the database size to a max. of 5TB and 5GB log:




Auto redistribution of data during Restore

After the initial restore into the 4 compute nodes completed, a ‘Create table as select’ CTAS command is issued and all the distributed tables are automatically redistributed across all 8 compute nodes; the DBCC PDW_Showspaceused  output shows  the data got nicely spreads across all the nodes:

Result of the Restore onto 8 compute nodes



Even on an APS system that has the capacity to store hundreds of terabytes it may be worth to check on the overall space allocated to databases every now and then. Also when planning for restoring a SQL database onto a larger APS please check the overall database size upfront to calculate the space needed on the destination APS since it will grow –at ratio-. If needed, you can free up unused space from the database and its log files upfront by running the ‘ALTER Database’ and DBCC shrinklog command.

GD Star Rating
GD Star Rating

SQL2014EE ‘Insufficient System Memory’ message

While preparing a data load demo for my presentation at SQLSaturday 317 in Oslo I experienced an hickup with SQL2014 EE: although the server in use for the comparison has 200+GB of RAM assigned to a freshly installed SQL instance, the Bulk inserting command to load a ~75 GByte flat file into an newly created Clustered Columnstore Indexed (CCI) table failed with the following error message:

Msg 701, Level  17, State: 128, line xx
There is insufficient system memory in resource pool ‘default’ to run this query.

There are a lot of posts out there on this specific message but I didn’t find good insights, so hopefully my attempt helps you to find a resolution: try and execute the ‘ALTER RESOURCE GOVERNOR RESET STATISTICS’ command and check again; for me this was sufficient to clear the message an load the data successfully afterwards. If your interested in digging a bit deeper into the Resource Governor values and settings, continue reading!

The test commands:

Getting ready for SQLPass!

The Message in the SQL Server Log is slightly different than the one returned in SSMS:
Error: 701, Severity: 17, State: 128
There is insufficient system memory in resource pool ‘default’ to run this query. 

Thinking about this message for a minute, my first thought was, well this could make sense; the default settings of SQL Server will assign a maximum of 25% memory granted to a query. Would it be the case that we simply don’t have enough memory assigned (25% of 200 GB  == 50GB) to run this task? But this isn’t SQL-alike behavior: you would expect worst case, that SQL Server would start to swap data out to disk and report a lot of time spend on some sort of waitstat as the new ‘COLUMNSTORE_BUILD_THROTTLE’ instead?

So just to look for an easy fix, I enabled the Resource Governor and increased the Memory Grant Percentage to 50% via the GUI, followed by 60%, 80% and finally with the value set to 95% the bulk insert task completed and loaded the 75 GByte of data from flatfile. This ‘trial & error’ approach made me feel a bit uncomfortable; do I really need 2.5x the amount of memory? I never experienced any such problem with direct data loads of easily 20x this size directly into CCI tables in SQL Server APS. So time to dig deeper…


So,  to run some investigations I reverted the RG settings back to the default 25% and disabled it. Then noticed that the DMV’s related to the Resource Governor have been enhanced significant in SQL2014; new counters like ‘read_bytes_total’ and ‘write_bytes_total’ have been added to the sys.dm_resource_governor_resource_pools dmv. (for a full description click here).

Snapshotting the Resource Governor Resource Pool values

To take a quick snapshot of the current values,  just copy them into a temp table:

--Snapshot RG Resource Pool stats:
USE tempdb
IF OBJECT_ID('tempdb.dbo.__RG_resource_pools') IS NOT NULL BEGIN
    DROP TABLE tempdb.dbo.__RG_resource_pools

INTO tempdb.dbo.__RG_resource_pools    
FROM sys.dm_resource_governor_resource_pools 


and then the fun part begins;  watching the various values to increase over time is the interesting part , which values actually correlate best with the bulk insert task?  So I ended up primairly looking at the delta values of following fields;


List the Resource Pool delta values over time

    b.Date as Snapshot_Taken,
    DATEDIFF(ss,b.Date,GETDATE()) As Seconds_Delta, AS RG_Pool_name,
    a.statistics_start_time as Last_Statistics_Update_start_time,
    (a.read_bytes_total  - b.read_bytes_total)/1024/1024/1024 AS Read_GB_Delta,
    (a.write_bytes_total - b.write_bytes_total)/1024/1024/1024 AS Write_GB_Delta,
    --a.used_memgrant_kb - b.used_memgrant_kb AS Used_memgrant_kb_delta,
    a.cache_memory_kb - b.cache_memory_kb AS Cache_memory_kb_Delta,
    a.total_memgrant_count - b.total_memgrant_count AS Total_memgrant_count_Delta,
    (a.max_memory_kb/1024/1024) AS Max_Mem_GB,
    (a.used_memory_kb/1024/1024) AS Used_Mem_GB
FROM sys.dm_resource_governor_resource_pools a
JOIN tempdb.dbo.__RG_resource_pools    b ON =

Query Output

The DMV snapshot nicely shows the ‘Read_GB_delta’ column representing the size of the flat file being processed, and the ‘Write_GB_delta’ column shows the size of the created CCI cq. data written to disk!


What is really interesting here is that the input part, the 74 GB flatfile size, has been assigned to the Internal pool, where resource consumption is not restricted in any form, and the actual CCI data, 25 GB has been allocated to the default pool. 25 GB is a value which should have fit easily into the Default Group in the first place, right? So what happened?

But then, looking at the other columns I noticed that the ‘statistics_start_time’  value hadn’t changed when reverting the values to its original values.  According the article this value represents  ‘The time when statistics was reset for this pool.’, so I gave it a try and manually reset the RG statistics;


This indeed updated the statistics_start_time value, so I changed the value in my query above to : ‘Last_Statistics_Update_start_time’ to make it easier to interpret. 

After the reset, the data loading completed successfully, also with the default settings , so problem solved!


Just to validate that SQL Server2014EE will indeed throttle but still complete the CCI build process properly, as a test I lowered the Memory Grant % from 25% for the Default group down to as little as 5% (== ~max. 10GB) and started the bulk insert one more time;

ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=5) 

Surprisingly after the direct load into the CCI table completed succesfully,  not a sign of ‘COLUMNSTORE_BUILD_THROTTLE’ or any of the fancy new waitstats there; just the regular data loading ones!

Waitstats after bulk inserting into CCI with % mem. granted set to 5%.


To overcome the ‘There is insufficient system memory in resource pool ‘default’ to run this query. ’ error message please check the sys.dm_resource_governor_resource_pools dmv values. Before increasing the Max. Memory Grant setting first give it a try to update the the Resource Governor statistics with the ‘ALTER RESOURCE GOVERNOR RESET STATISTICS’ command.

(And as always.. please let me know your findings!

– Henk)

GD Star Rating
GD Star Rating
Better Tag Cloud