Skip to main content

Bug in Microsoft Management Data Warehouse (MDW).

For those who use MDW and complain about the bug in the report Disk Usage Collection Set - Database, I updated the query so that it shows now the correct data. The bug is very consistent: it's in there from 2008 R2 and still there.

Take a look at the report:

As you can see the report shows more than 8 GB but in the data below you can see that the database size is no more than 4,3 GB. 

 Someone just made a programming error at Microsoft and unknown on what the field values mean in MDW just mixed up some numbers. I have to admit that it is not easy :-)

 I rewrote the query after comparing the values with a real database and added the log value. Now it is to Microsoft to add it. 

Here's the code (I highlighted the changes):


exec sp_executesql @stmt=N'BEGIN TRY
DECLARE @snapshot_id int
SELECT TOP 1 @snapshot_id = snapshot_id FROM (

SELECT DISTINCT TOP 100 d.snapshot_id
FROM snapshots.disk_usage d, core.snapshots ss
WHERE ss.instance_name = @ServerName
AND ss.snapshot_id = d.snapshot_id
ORDER BY d.snapshot_id DESC

) AS q
ORDER BY snapshot_id ASC


SELECT
            CONVERT (datetime, SWITCHOFFSET (CAST (d.collection_time AS datetimeoffset), ''+00:00'')) AS collection_time,
            d.snapshot_id,
            ((convert(dec (15,2),d.dbsize) + convert(dec(15,2),d.logsize)) * 8192 / 1048576.0) AS ''database_size_mb'',
            (convert(dec(15,2),d.logsize) * 8192 / 1048576.0) AS ''log_size_mb'',
            ((d.usedpages - (d.usedpages - d.pages))  * 8192 / 1048576.0) AS ''data_size_mb'',
            ((d.reservedpages - d.usedpages) * 8192 / 1048576.0) AS ''reserved_mb'',
            (
                  case 
                        when d.dbsize >= d.reservedpages
                        then (convert (dec (15,2),d.dbsize) - convert (dec (15,2),d.reservedpages)) * 8192 / 1048576.0 
                  else 0 
                  end) AS ''unallocated_space_mb'',
            ((d.usedpages - d.pages) * 8192 / 1048576.0) AS ''index_mb''
FROM snapshots.disk_usage d, core.snapshots ss
WHERE database_name=@SelectedDatabaseName
  AND d.snapshot_id >= @snapshot_id
  AND ss.instance_name = @ServerName
  AND d.snapshot_id = ss.snapshot_id
ORDER BY collection_time

END TRY
BEGIN CATCH

SELECT
null AS collection_time,
ERROR_NUMBER() AS snapshot_id,
ERROR_SEVERITY() AS database_size_mb,
ERROR_STATE() AS reserved_mb,
ERROR_MESSAGE() AS unallocated_space_mb,
1 AS index_mb

END CATCH',@params=N'@ServerName NVarChar(max), @SelectedDatabaseName NVarChar(max)',@ServerName=N'MyServer',@SelectedDatabaseName=N'MDW'


I verified it with the real database data and it is 100% correct. 

This change wasn't too difficult. Maybe it can be added in a Cummulative Update for SQL Server versions 2008 R2 to 2012.


Labels:

Comments

Post a Comment

Popular posts from this blog

Privacy and the liberty to express yourself on LinkedIn

Unaware that LinkedIn has such a strong filtering policy that it does not allow me posting a completely innocent post on a Chinese extreme photography website I tried to post the following: "As an Mpx lover I was suprised to find out that the M from Million is now replaced by the B from Billion. This picture is 24 Bpx! Yes you read this well, 24 billion pixels.  Searching on the picture I stumbled on a fellow Nikon lover. If you want to search for him yourself you can find him here: http://www.bigpixel.cn/t/5834170785f26b37002af46a " In my eyes nothing is wrong with this post, but LinkedIn considers it as offending. I changed the lover words, but I could not post it.  Even taking a picture and post it will not let this pass:  Or my critical post on LinkedIn crazy posting policy: it will not pass and I cannot post it.  The technology LinkedIn shows here is an example what to expect in the near future.  Newspapers will have a unified re...
Post a Comment
Read more

How to run SQL Server 2016 with In-Databasse R on Windows 2016 CTP5

For those who like me tried to run SQL Server 2016 with In-Database R might have run into the same problem as me: In-Database R or the LaunchPad service gives a timeout and won't start. I did several clean installations with different configuration options - for instance I like to put my data on another disk than the system disk - but in the end I tried to do the next, next, next, finish install to see if it something in the setup options is hard coded in there (yes, it happens developers!). For some reason this problem is related to Windows 2016 and not on Windows 2012R2 and I hope the SQL Server team will soon resolve these issues because they are in one word a bit sloppy.  There are 2 issues (maybe even 3 so I give this one also):  The R setup does not create the ExtensibilityLog directory in the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" directory The R setup sets the number of users in the SQL Server Launchpad service to 0 it is pos...
Post a Comment
Read more

Windows Storage Spaces and SQL Server: a ride to super performance

This post is based on the tests I did to see if storage spaces in Windows 2012R2 can serve as a platform for our Fast Track environments. When Microsoft developed the Fast Track Data Warehouse architecture, which was at first very limited in hardware choice and for version SQL Server 2012 became a reference guide, Storage Spaces as a functionality in Windows did not exist.That has changed with the release of Windows 2012 and later on with version 2012R2 and soon 2016. Why is Storage Spaces as a storage technology so interesting for SQL Server?  Anyone who is a pro in SQL Server knows that parallelism - adding more disks - can greatly improve performance. Adding an hard disk for the tempdb and another one for the LOG files will do the job if the hard disks perform sufficiently (that might be another issue!). To my opinion (and also to others) SQL Server does not do a great job in using the available hardware. Before and after the installation it is mandatory to tune a SQL Se...
Post a Comment
Read more