Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

Question

We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated columns) from a very large (some billion rows) ColumnStore table. The query usually needs about 1 minute to return about 200-300 rows.

A few days ago it started (randomly) to throw a very unspecific Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException error message after opening and / or manually refreshing the data in the Excel sheet (after about 30 seconds).

While Excel is refreshing the data, I can see the query when running sp_WhoIsActive / sp_BlitzWho.

Executing the query manual in SSMS works usually fine, there was no real workload at the server and after some tries (in SSMS) Excel started to work again.

Answer 1

Using the profiler I found, that Excel sends an EXEC sys.sp_prepare to return the meta data and compiles the execution plan.

When I executed the prepare statement in SSMS it hangs too, the same when I tried to display the estimated execution plan for the SELECT on the view itself. I let it run for a while and after about 7 minutes it was done compiling the query and showing me the estimated plan (sp_WhoIsActive showed me only about 30 sek CMEMTHREAD plus some minor wait stats for my query).

After this "initial" compiling both - SSMS and Excel - were able to load the data / show execution plans / preparing the statment without any delay.

Reason:

When I executed

SELECT STATS_DATE(s.object_id, s.stats_id) AS last_updated, s.*
  FROM sys.stats AS s
 WHERE s.object_id = OBJECT_ID('dwh.big_table')
 ORDER BY last_updated DESC

I found, that there are 4 auto generated statistics that were updated today, the last one exactly at the moment when the execution plan was finally generated. All updated statistics were on columns that were used in the query / view.

Obviously we had a problem with manually updating the statics (e.g. using Ola Hallengreens Maintenance Solution) after loading the latest data to the DWH and the Auto Update Statistics feature kicked in, when the query was first executed today. And while the statistics were updating, it could not compile an execution plan (e.g. since it does not know how many rows we have for the last x days and how much memory it needs to reserve).

Excel itself seems to have an internal timeout (found no way to configure it) of about 30 seconds regarding the sp_prepare statement and shows the very vague error message (Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException) when it does not get back the metadata for the query in this time.

Solution:

I moved the statistics update to a new (later) time frame, to ensure that the data was loaded first (before updating the statistics), so that there is no need for the Auto Update Statistics feature to kick in and block the table for several minutes...