Power Pivot Error While Upgrading a Workbook

I recently tried upgrading a workbook to Excel 2013 from an older version (I believe 2010).

Upgrade message

It succeeded (or at least it looked like it succeeded), but I needed to save, close and reopen the workbook according to Excel. 

Upgrade Data Model

Close and reopen workbook

Saving of the document failed with the below error message and then the Power Pivot Add-in was corrupt and the whole tab was even gone.

Error message

Disabling/enabling the Add-in didn't work, I got an error message saying the add-in cannot be loaded and Office Repair needs to run.

Office (Pro Plus) Repair ran successfully, it only needed a reboot after which the problem was solved. I still have no idea what caused Excel to blow up like this.

The second try of upgrading the workbook did work correctly and now I could save and reopen it and go on with my business! :-)

T-SQL Tuesday #66: Monitoring

This month's T-SQL Tuesday is being hosted by Cathrine Wilhelmsen (b|t), and the topic is as follows: Monitoring.

I thought I'd finally give it a go by joining the tradition of T-SQL Tuesday. The last months or so I kept saying to myself: this topic is not particularly suited for me, or I don't have the time right now.. As most people say about blogging: not starting is usually not blogging at all, so here it goes!

Since I'm a SQL/BI developer I'm not overly into monitoring servers or SQL-instances, but after thinking about it I do have some other things to monitor during my day-to-day tasks.

SSIS
I tend to execute quite a few packages and jobs during the day. I monitor them via the Standard Report option of the SSISDB Catalog in SSMS as that's the easiest way. Following naming conventions for my packages, tasks and components I can quickly address issues and see where things go bad.

SQL
I also regularly take a look at the servers that are running my solutions to keep everything going smoothly. Until now it's mainly been reactive, e.g. when users start complaining about slow reports or dashboards.
I use the below query for finding the top slow queries that have run lately.

SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (
   (
    CASE qs.statement_end_offset
     WHEN - 1
      THEN DATALENGTH(qt.TEXT)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset
    ) / 2
   ) + 1) AS SQLStatement
 ,qs.execution_count
 ,qs.total_logical_reads
 ,qs.last_logical_reads
 ,qs.total_logical_writes
 ,qs.last_logical_writes
 ,qs.total_worker_time
 ,qs.last_worker_time
 ,qs.total_elapsed_time / 1000000 total_elapsed_time_in_Sec
 ,qs.last_elapsed_time / 1000000 last_elapsed_time_in_Sec
 ,qs.last_execution_time
 ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Besides getting slow queries that have already run, it sometimes happens that a DWH-job is still running in the morning when it should've been completed looong ago :). Running sp_who2 or sp_WhoIsActive by Adam Machanic (b|t) usually gets me to find the reason why it is still running and what the problem is.

Hopefully I can continue to add more posts to the T-SQL Tuesday tradition. Have a nice day and keep sharing!