Roger's BI Blog

Today I ran into a very strange problem. I was doing an audit because a client lost 32 billion records in a table and he wanted to know "Who did it!". They did not use auditing because it had a too large impact on performance. The server and instance was not restarted so the DMV's were still available with data.  I presumed there were two options how the data could have been lost and so quickly: run a TRUNCATE TABLE. Very fast and and in less than a second your table is unrecoverable empty  Switch a partition out into a temporary table and just truncate the original table. Yes it happens and the effect is the same: nothing!  So I ran this query to collect the last queries: SELECT t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t WHERE t.TE...

Storage Spaces is a component available in Windows 2012. Microsoft has used this technique in their Microsoft Analytics Platform System or previously named SQL Server 2012 Parallel Data Warehouse.  But as I will show they got stuck in the 'old way of thinking' which will only work (slightly?) better if we live in a perfect world, were all data is requested at the same time. In a lot of cases this is not so.... The hardware behind Microsoft Analytics Platform System When looking closer at the hardware the Hewlett and Packard (HP) solution based on the DL360 Gen8, which has 2 PCIe slots, is equipped with one H221 SAS controller with two 4x6 Gb/s ports and one Melanox Infini Band 56 Gb/s controller. In the HP solution the JBOD is divided in two: every node manages 32 disks.  Dell addresses the solution with the PowerEdge R620 which has maximum 3 PCIe slots but 2 JBODs so in order to address all 102 disks in the JBOD's they need to do the same as HP namely one SAS...