none
To much space Unused ( aproximtly 50%)

    Question

  • Hello,

    After SQL upgrade from 2012 to 2016 the Operations Manager database has relay high values of Unused Space. ApproximatelyHalf of the Database is defined as Unused.

    Do you know if this is normal?

    Also all new tables that are created are created the same way, a lot of  space is reserved in the Unused.

    Do you have any hints?

    Table Name # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB)
    dbo.PerformanceData_19 1,368,511 1,975,520 176,304 236,960 1,562,256
    dbo.PerformanceData_22 1,298,646 1,888,776 168,168 221,080 1,499,528
    dbo.PerformanceData_21 1,289,952 1,831,840 164,472 219,720 1,447,648
    dbo.PerformanceData_23 1,309,492 1,792,368 166,560 225,048 1,400,760
    dbo.PerformanceData_20 1,295,088 1,380,960 168,176 225,576 987,208
    dbo.PerformanceData_24 923,083 1,302,616 116,680 147,624 1,038,312
    dbo.StateChangeEvent 480,830 323,944 251,128 65,096 7,720
    dbo.ManagementPack 281 285,176 284,400 56 720
    dbo.Event_23 21,811 127,736 46,928 1,376 79,432
    dbo.Resource 1,585 108,032 105,496 128 2,408
    dbo.LocalizedText 121,565 85,632 54,624 29,544 1,464
    dbo.MonitoringJobStatus 16,597 83,880 82,168 1,280 432
    dbo.Event_24 12,633 74,592 28,464 744 45,384

    Total Spaced Reserverd: 16,60. Space Used: 12,27GB

    Unused: 48,07%.  Index: 8,93%, Data 16,9%   Unalocated: 26,09 ( it was less but i increased the Database)

    Thank you,

    Sunday, May 14, 2017 7:33 AM

Answers

  • So... something is doing so to them to get the Unused value big when adding data, because the Unused space looks to be increasing as the DB increases.

    This looks to be a symptom of bulk inserting data in small batches. I was able to reproduce this with the Powershell script below. With a small batch size like 10 against SQL Server 2016, I observed about 8MB unused out of 9MB reserved. The same script against SQL 2012 showed about 1MB reserved and 1KB unused. So the issue seems to apply to newly inserted data under SQL 2016 rather than data upgraded from SQL 2012.

    I suspect this is a behavior change in SQL Server 2016 to optimize bulk insert performance but can increase unused space as a side effect, especially for small batch sizes.  I'll see of I can find more information about this and post back here.

    If the batch size is configurable with SCOM you might try increasing it. If not, you can reorg the tables periodically, which should be done during routine maintenance anyway.

    # bulk insert test data in batches
    
    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
    $batchSize = 10; # small batch size will increase unused pages
    $dt = New-Object System.Data.DataTable;
    $sqlScript = @("
    --(re)create test table
    IF OBJECT_ID(N'dbo.Example', 'U') IS NOT NULL
        DROP TABLE dbo.Example;
    
    CREATE TABLE dbo.Example(
    	  ExampleID int NOT NULL
            CONSTRAINT PK_Example PRIMARY KEY
    	, ExampleData char(100) NOT NULL
    );
    
    --return table schema only
    SELECT
          ExampleID
        , ExampleData
    FROM dbo.Example
    WHERE 0 = 1;
    ");
    
    try
    {
    
        # create test table and get schema
        $da = New-Object System.Data.SqlClient.SqlDataAdapter($sqlScript, $connectionString);
        $null = $da.Fill($dt);
        $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock);
        $bcp.DestinationTableName = "dbo.Example";
    
        # load table using SqlBulkCopy in batches
        for($recordNumber = 1; $recordNumber -le 10000; ++$recordNumber)
        {
            
            $newRow = $dt.NewRow();
            $null = $dt.Rows.Add($newRow);
            $newRow["ExampleID"] = $recordNumber;
            $newRow["ExampleData"] = $recordNumber.ToString();
    
            if(($recordNumber % $global:batchSize) -eq 0) 
            {
                $bcp.WriteToServer($dt);
                $dt.Rows.Clear();
                Write-Host "$recordNumber records inserted";
            }
    
        }
    
        if($dt.Rows.Count -gt 0)
        {
            $bcp.WriteToServer($dt);
            Write-Host "$recordNumber records inserted";
        }
    
        $bcp.Close();
    
    }
    catch
    {
        throw;
    }
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, May 15, 2017 12:33 PM
    Moderator

All replies

  • Did you perform restore and backup db? Run the below

    • DBCC UPDATEUSAGE
    • UPDATE STATISTICS


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 14, 2017 8:19 AM
    Moderator
  • I wouldn`t run non SCOM maitenance on the DB

    https://blogs.technet.microsoft.com/kevinholman/2015/07/28/what-sql-maintenance-should-i-perform-on-my-scom-2012-databases/

    select * 
    from DomainTable dt 
    inner join DomainTableIndexOptimizationHistory dti 
    on dt.domaintablerowID = dti.domaintableindexrowID 
    ORDER BY optimizationdurationseconds DESC

    DomainTableRowId TableObjectId TableName CreatedDateTime DomainTableIndexOptimizationHistoryRowId DomainTableIndexRowId OptimizationStartDateTime OptimizationDurationSeconds BeforeAvgFragmentationInPercent AfterAvgFragmentationInPercent OptimizationMethod
    5224 1553609519 [dbo].[MT_Microsoft$Windows$InternetInformationServices$10$0$ComputerGroup_Log] 3/28/17 12:30 AM 166444 5224 5/14/17 12:31 AM 0 25 25 online reorganize
    4974 1865942615 [dbo].[MT_LW$Microsoft$Windows$CertificateServices$CARole$6$4_Log] 3/21/17 1:30 AM 166443 4974 5/14/17 12:31 AM 0 833,333,333,333,333 50 online rebuild
    4973 1817942444 [dbo].[MT_LW$Microsoft$Windows$CertificateServices$CARole$6$4] 3/21/17 1:30 AM 166442 4973 5/14/17 12:31 AM 0 923,076,923,076,923 4 online rebuild
    4971 1535513445 [dbo].[MT_LW$Microsoft$Windows$Biztalk$2013R2] 3/21/17 1:30 AM 166441 4971 5/14/17 12:31 AM 0 833,333,333,333,333 50 online rebuild
    4970 1439513103 [dbo].[MT_LW$Microsoft$Windows$Biztalk$2013_Log] 3/21/17 1:30 AM 166440 4970 5/14/17 12:31 AM 0 76,271,186,440,678 256,410,256,410,256 online rebuild
    4969 1391512932 [dbo].[MT_LW$Microsoft$Windows$Biztalk$2013] 3/21/17 1:30 AM 166439 4969 5/14/17 12:31 AM 0 20 20 online reorganize
    4965 904559202 [dbo].[MT_LW$Microsoft$Windows$Biztalk$2009] 3/21/17 1:30 AM 166438 4965 5/14/17 12:31 AM 0 40 333,333,333,333,333 online rebuild
    4964 808558860 [dbo].[MT_LW$Microsoft$Windows$Biztalk$2006_Log] 3/21/17 1:30 AM 166437 4964 5/14/17 12:31 AM 1 472,222,222,222,222 321,428,571,428,571 online rebuild
    4962 664558347 [dbo].[MT_LW$Microsoft$Windows$AppController$2012R2_Log] 3/21/17 1:30 AM 166436 4962 5/14/17 12:31 AM 0 50 0 online rebuild
    4961 616558176 [dbo].[MT_LW$Microsoft$Windows$AppController$2012R2] 3/21/17 1:30 AM 166435 4961 5/14/17 12:31 AM 0 50 50 online rebuild
    4953 943511336 [dbo].[MT_LW$Microsoft$BaseOS$2012R2] 3/21/17 1:30 AM 166434 4953 5/14/17 12:31 AM 0 50 0 online rebuild
    4952 895511165 [dbo].[MT_LW$Microsoft$BaseOS$2012_Log] 3/21/17 1:30 AM 166433 4952 5/14/17 12:31 AM 0 50 50 online rebuild
    4950 815510880 [dbo].[MT_LW$Microsoft$BaseOS$2008R2_Log] 3/21/17 1:30 AM 166432 4950 5/14/17 12:31 AM 0 96,551,724,137,931 111,111,111,111,111 online rebuild
    4949 783510766 [dbo].[MT_LW$Microsoft$BaseOS$2008R2] 3/21/17 1:30 AM 166431 4949 5/14/17 12:31 AM 0 886,075,949,367,089 344,827,586,206,897 online rebuild
    4947 639510253 [dbo].[MT_LW$Microsoft$ADRMS$6$4$Server] 3/21/17 1:30 AM 166430 4947 5/14/17 12:31 AM 0 857,142,857,142,857 50 online rebuild
    4945 495509740 [dbo].[MT_LW$Microsoft$ActiveDirectoryFederationServices$10$0$FederationServerSeed] 3/21/17 1:30 AM 166428 4945 5/14/17 12:31 AM 0 285,714,285,714,286 285,714,285,714,286 online reorganize
    4946 543509911 [dbo].[MT_LW$Microsoft$ActiveDirectoryFederationServices$10$0$FederationServerSeed_Log] 3/21/17 1:30 AM 166429 4946 5/14/17 12:31 AM 0 942,307,692,307,692 256,410,256,410,256 online rebuild
    4944 447509569 [dbo].[MT_LW$Base$Microsoft$Windows$Server$BaseOS$Category_Log] 3/21/17 1:30 AM 166427 4944 5/14/17 12:31 AM 0 916,666,666,666,667 20 online rebuild
    4943 415509455 [dbo].[MT_LW$Base$Microsoft$Windows$Server$BaseOS$Category] 3/21/17 1:30 AM 166426 4943 5/14/17 12:30 AM 1 865,671,641,791,045 196,078,431,372,549 online rebuild
    4942 400056511 [apm].[SOURCE] 3/21/17 1:30 AM 166425 4942 5/14/17 12:30 AM 0 166,666,666,666,667 166,666,666,666,667 online reorganize
    4940 384056454 [apm].[PMRULETRACE] 3/21/17 1:30 AM 166424 4940 5/14/17 12:30 AM 0 20 20 online reorganize
    4939 304056169 [apm].[PMRULESTATUS] 3/21/17 1:30 AM 166423 4939 5/14/17 12:30 AM 0 20 20 online reorganize
    4923 1203535371 [apm].[CHAINENTRYINFO] 3/21/17 1:30 AM 166422 4923 5/14/17 12:30 AM 0 75 0 online rebuild
    4921 1043534801 [apm].[APPLICATIONSOURCEMACHINE] 3/21/17 1:30 AM 166420 4921 5/14/17 12:30 AM 0 166,666,666,666,667 166,666,666,666,667 online reorganize
    4922 1587536739 [apm].[ASPECT] 3/21/17 1:30 AM 166421 4922 5/14/17 12:30 AM 0 541,666,666,666,667 526,315,789,473,684 online rebuild
    4800 1352456688 [dbo].[MT_MSExchangeADAccessProcess_Log] 3/15/17 1:31 AM 166413 4800 5/14/17 12:30 AM 0 967,741,935,483,871 4 online rebuild
    4799 1304456517 [dbo].[MT_MSExchangeADAccessProcess] 3/15/17 1:30 AM 166412 4799 5/14/17 12:30 AM 0 959,459,459,459,459 321,428,571,428,571 online rebuild
    4797 1160456004 [dbo].[MT_MSExchangeADAccessDC] 3/15/17 1:30 AM 166411 4797 5/14/17 12:30 AM 0 50 0 online rebuild
    4796 1064455662 [dbo].[MT_Custom$Exchange$Server$2013$Group_Log] 3/15/17 1:30 AM 166410 4796 5/14/17 12:30 AM 0 727,272,727,272,727 142,857,142,857,143 online rebuild
    4794 767406599 [dbo].[MT_Microsoft$Exchange$15$Custom$WarningHealthSets$Group_Log] 2/28/17 1:30 AM 166409 4794 5/14/17 12:30 AM 0 50 0 online rebuild
    4793 719406428 [dbo].[MT_Microsoft$Exchange$15$Custom$WarningHealthSets$Group] 2/28/17 1:30 AM 166408 4793 5/14/17 12:30 AM 1 236,842,105,263,158 263,157,894,736,842 online reorganize
    4779 1858886485 [dbo].[MT_Microsoft$Exchange$15$Custom$ExtendedObjects$Group] 2/28/17 1:30 AM 166407 4779 5/14/17 12:30 AM 0 75 50 online rebuild
    4778 1762886143 [dbo].[MT_Microsoft$Exchange$15$Custom$CriticalHealthSets$Group_Log] 2/28/17 1:30 AM 166406 4778 5/14/17 12:30 AM 0 764,705,882,352,941 833,333,333,333,333 online rebuild
    4772 77308131 [dbo].[MT_Microsoft$Windows$Server$10$0$Processor_Log] 2/7/17 1:30 AM 166405 4772 5/14/17 12:30 AM 0 857,142,857,142,857 142,857,142,857,143 online rebuild
    4770 2080791266 [dbo].[MT_Microsoft$Windows$Server$10$0$PhysicalDisk_Log] 2/7/17 1:30 AM 166404 4770 5/14/17 12:30 AM 0 952,380,952,380,952 142,857,142,857,143 online rebuild
    4769 2032791095 [dbo].[MT_Microsoft$Windows$Server$10$0$PhysicalDisk] 2/7/17 1:30 AM 166403 4769 5/14/17 12:30 AM 0 795,698,924,731,183 117,647,058,823,529 online rebuild
    4758 221308644 [dbo].[MT_Microsoft$Windows$Server$10$0$LogicalProcessor_Log] 2/7/17 1:30 AM 166402 4758 5/14/17 12:30 AM 0 50 0 online rebuild
    4757 173308473 [dbo].[MT_Microsoft$Windows$Server$10$0$LogicalProcessor] 2/7/17 1:30 AM 166401 4757 5/14/17 12:30 AM 0 40 333,333,333,333,333 online rebuild
    4505 709590312 [dbo].[MT_HewlettPackard$ProLiant$Monitored$VMware$HPESXServer] 8/30/16 12:30 AM 166400 4505 5/14/17 12:30 AM 0 285,714,285,714,286 285,714,285,714,286 online reorganize
    4432 951635129 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$SystemHealthValidator_Log] 8/10/16 12:30 AM 166399 4432 5/14/17 12:30 AM 0 166,666,666,666,667 166,666,666,666,667 online reorganize
    4385 1949210690 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$ReplicationToSecondarySite] 8/10/16 12:30 AM 166398 4385 5/14/17 12:30 AM 0 170,731,707,317,073 24,390,243,902,439 online reorganize
    2069 1899921890 [dbo].[MT_System$NetworkManagement$GNACable] 4/10/14 12:33 AM 166397 2069 5/14/17 12:30 AM 0 50 20 online rebuild
    2056 633873425 [dbo].[MT_System$NetworkManagement$FoundryEnvMon_Fault_PowerSupply_Log] 4/10/14 12:33 AM 166396 2056 5/14/17 12:30 AM 0 20 20 online reorganize
    2038 1427588224 [dbo].[MT_System$NetworkManagement$Fortigate_Performance_Memory_Log] 4/10/14 12:33 AM 166395 2038 5/14/17 12:30 AM 0 20 20 online reorganize
    2037 1395588110 [dbo].[MT_System$NetworkManagement$Fortigate_Performance_Memory] 4/10/14 12:33 AM 166394 2037 5/14/17 12:30 AM 0 166,666,666,666,667 166,666,666,666,667 online reorganize
    2034 1315587825 [dbo].[MT_System$NetworkManagement$FortiAnalyzer_Performance_Memory_Log] 4/10/14 12:33 AM 166393 2034 5/14/17 12:30 AM 0 20 20 online reorganize
    1942 1759449442 [dbo].[MT_System$NetworkManagement$ExpandRsc_Performance_Processor_Log] 4/10/14 12:33 AM 166392 1942 5/14/17 12:30 AM 0 25 25 online reorganize
    1939 1029734871 [dbo].[MT_System$NetworkManagement$EntityState_Fault_VoltageSensor] 4/10/14 12:33 AM 166391 1939 5/14/17 12:30 AM 0 20 20 online reorganize
    1381 1430400265 [dbo].[MT_System$NetworkManagement$Acmepacket_Performance_Processor] 4/10/14 12:32 AM 166390 1381 5/14/17 12:30 AM 0 25 25 online reorganize
    1380 1802541555 [dbo].[MT_System$NetworkManagement$Acmepacket_Performance_Memory_Log] 4/10/14 12:32 AM 166389 1380 5/14/17 12:30 AM 0 333,333,333,333,333 333,333,333,333,333 online rebuild
    1254 1287219886 [dbo].[MT_Microsoft$Windows$Server$2012$R2$OperatingSystem] 4/10/14 12:31 AM 166388 1254 5/14/17 12:30 AM 0 25 25 online reorganize
    1215 106691678 [dbo].[MT_Microsoft$Windows$Server$2008$OperatingSystem_Log] 4/10/14 12:31 AM 166387 1215 5/14/17 12:30 AM 0 333,333,333,333,333 333,333,333,333,333 online rebuild
    1200 186691963 [dbo].[MT_Microsoft$Windows$Server$2008$Core$OperatingSystem] 4/10/14 12:31 AM 166386 1200 5/14/17 12:30 AM 0 20 20 online reorganize
    1199 1278171849 [dbo].[MT_Microsoft$Windows$Server$2008$Core$ComputerGroup_Log] 4/10/14 12:31 AM 166385 1199 5/14/17 12:30 AM 0 25 25 online reorganize
    997 1582836901 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$SecondarySite_Log] 4/10/14 12:31 AM 166384 997 5/14/17 12:30 AM 0 20 20 online reorganize
    996 1534836730 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$SecondarySite] 4/10/14 12:31 AM 166383 996 5/14/17 12:30 AM 0 333,333,333,333,333 333,333,333,333,333 online rebuild
    985 1195359523 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$PxeServicePoint_Log] 4/10/14 12:31 AM 166382 985 5/14/17 12:30 AM 0 333,333,333,333,333 333,333,333,333,333 online rebuild
    984 1147359352 [dbo].[MT_Microsoft$SystemCenter2012$ConfigurationManager$PxeServicePoint] 4/10/14 12:31 AM 166381 984 5/14/17 12:30 AM 0 333,333,333,333,333 333,333,333,333,333 online rebuild


    For DBCC UPDATEUSAGE , there is no instruction so on the webiste so i am not 100% shure if it can be run or not safely.

    Sunday, May 14, 2017 9:36 AM
  • If you are not inclined to run non-SCOM maintenance on the database, you should not post in a general SQL Server forum. Here you will get general answers about SQL Server, but don't expect that people who answer wlll know about SCOM. I certainly do not. If you want SCOM-specific answers, you should ask in a forum devoted to SCOM.

    I don't know why Uri adviced you run UPDATE STATISTICS, because that has absolutely nothing to do with the numbers you see. DBCC UPDATEUSAGE may have, but that was more in older days (SQL 2000 or so). I have not found any need to run DBCC UPDATEUSAGE for quite some time.

    My reflection with the numbers you post, is that an index rebuild on these tables would make sense to compact the tables. As for why they were inflated about the upgraded, this is something that requires knowledge about SCOM to answer.

    By the way, I notice in the blog post that you refef to that the author uses old DBCC commands to investigate fragmentation and index rebuild. There are more modern commands for these tasks that are to prefer.

    Sunday, May 14, 2017 9:53 AM
  • Hi Erland, i asked in both places :). In general the SCOM Admins do not know SQL so extensively.

    The SQL upgrade was made by the SQL Admins ( but still trying to figure out with them also what happens).
    In the worst case, i would do also let`s say "not so remanded tasks"  but want to have the best option in order to fix it not to try different things without having an solution.

    Because i do not see errors, i would suspect something went strange at the upgrade, ( that i can fix with SQL commands, Maybe shrink with the Option "Reorganize files before releasesing unused space" ) or this is a normal way of SQL of working for SQL 2016 in case you have tables that are getting a lot of information in a short time frame.

    Kind regards,

    Adrian

    Sunday, May 14, 2017 10:40 AM
  • I have not heard of anything in general that an upgrade to SQL 2016 would have this effect, so I assume that this is something application-specific.

    I assume that you have verified that the version of SCOM you are running supports SQL 2016?

    Sunday, May 14, 2017 11:35 AM
  • Also all new tables that are created are created the same way, a lot of  space is reserved in the Unused.

    This part of you question could be due to the MIXED_PAGE_ALLOCATION database setting, which was introduced in SQL Server 2016 (https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-changes-in-default-behavior-for-autogrow-and-allocations-for-tempdb-and-user-databases/). When set to OFF (0), a full 64K extent is allocated initially rather than an 8K page from a mixed extent. You can check the setting for your databases with this query:

    SELECT name, is_mixed_page_allocation_on
    FROM sys.databases;

    Unused space for small tables will increase when MIXED_PAGE_ALLOCATION is OFF but that should not significantly affect space for larger tables like the ones in question. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 14, 2017 12:37 PM
    Moderator
  • >>>>I don't know why Uri adviced you run UPDATE STATISTICS,

    Yep, I meant the only first one 

     >>>>>because that has absolutely nothing to do with the numbers you see

    That is correct , but it always worth running this command especially just after upgrading :-) 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 14, 2017 1:27 PM
    Moderator
  • Because i do not see errors, i would suspect something went strange at the upgrade, ( that i can fix with SQL commands, Maybe shrink with the Option "Reorganize files before releasesing unused space" ) or this is a normal way of SQL of working for SQL 2016 in case you have tables that are getting a lot of information in a short time frame.

    I suggest you try reorganizing or rebuilding indexes rather than shrink. My suspicion is that shrink will only move the allocated space without affecting the unused amount, plus shrink can introduce significant fragmentation.

    I can't speak to the specifics of doing this via SCOM but below is T-SQL example. You might try this on one of your problem tables and repeat for the others if it provides the desired result. 

    ALTER INDEX ALL
    ON dbo.PerformanceData_19
    REORGANIZE;
    After thinking about this some more, perhaps the unused space problem existed before the upgrade but was not reported properly. Following a reorganize or rebuild, each table should have no more than 64K unused per index and index level. This is typically no more than a few MB per table.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 14, 2017 2:44 PM
    Moderator
  • Hi Dan, 

    I run it on _20 ( because SCOM has for each day 1 table and they are changing from 00 to 60, one is clean and another one is populated with data) and it had effect.

    For Odb.PerfromanceData_20

    Reserved:  1 380 960KB       Data: 168 176KB    Indexes: 225 576KB         Unused:987 208KB

    After:

    Reserverd: 240 096KB;        Data:   94 944KB     Indexes:   94 944KB         Unused:    2 176KB

    So... something is doing so to them to get the Unused value big when adding data, because the Unused space looks to be increasing as the DB increases.


    PS: for your information there is 1 more environment to be upgraded, that has the Tables PerformanceData_ around 5GB. Currently space is 47.96% out of a total 26.37GB.  ( currently the biggest value of Unused space is 3320KB)

    Other information: on the Server there are 2 more instances one for SCOM DataWarehouse and one for SCCM that do not have this strange thing.
    Monday, May 15, 2017 4:03 AM
  • I have not found any need to run DBCC UPDATEUSAGE for quite some time.

    After running some heavy DML e.g. in ETL process the pages stats can get out of sync and that command fixes the count of pages; but mostly there are only small differences, like in this Output

    DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'IDX_MyTable', partition 1):
            USED pages (LOB Data): changed from (36) to (18) pages.
            RSVD pages (LOB Data): changed from (38) to (19) pages.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 15, 2017 6:04 AM
    Moderator
  • SQL Server never reduces the physical size of the files, unless you manually shrink them.

    Your post would indicate a couple of possibilities.  Either you never setup your retention period in SCOM correctly, and then did, so it removed a bunch of data or there used to be a process in SCOM which grew the database if free space was less than X% and that was set too big.

    In any case, you should allow SCOM to manage the database. All database maintenance functionality is built into SCOM functions.  You should never need to access the database directly.

    Monday, May 15, 2017 12:04 PM
    Moderator
  • So... something is doing so to them to get the Unused value big when adding data, because the Unused space looks to be increasing as the DB increases.

    This looks to be a symptom of bulk inserting data in small batches. I was able to reproduce this with the Powershell script below. With a small batch size like 10 against SQL Server 2016, I observed about 8MB unused out of 9MB reserved. The same script against SQL 2012 showed about 1MB reserved and 1KB unused. So the issue seems to apply to newly inserted data under SQL 2016 rather than data upgraded from SQL 2012.

    I suspect this is a behavior change in SQL Server 2016 to optimize bulk insert performance but can increase unused space as a side effect, especially for small batch sizes.  I'll see of I can find more information about this and post back here.

    If the batch size is configurable with SCOM you might try increasing it. If not, you can reorg the tables periodically, which should be done during routine maintenance anyway.

    # bulk insert test data in batches
    
    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
    $batchSize = 10; # small batch size will increase unused pages
    $dt = New-Object System.Data.DataTable;
    $sqlScript = @("
    --(re)create test table
    IF OBJECT_ID(N'dbo.Example', 'U') IS NOT NULL
        DROP TABLE dbo.Example;
    
    CREATE TABLE dbo.Example(
    	  ExampleID int NOT NULL
            CONSTRAINT PK_Example PRIMARY KEY
    	, ExampleData char(100) NOT NULL
    );
    
    --return table schema only
    SELECT
          ExampleID
        , ExampleData
    FROM dbo.Example
    WHERE 0 = 1;
    ");
    
    try
    {
    
        # create test table and get schema
        $da = New-Object System.Data.SqlClient.SqlDataAdapter($sqlScript, $connectionString);
        $null = $da.Fill($dt);
        $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock);
        $bcp.DestinationTableName = "dbo.Example";
    
        # load table using SqlBulkCopy in batches
        for($recordNumber = 1; $recordNumber -le 10000; ++$recordNumber)
        {
            
            $newRow = $dt.NewRow();
            $null = $dt.Rows.Add($newRow);
            $newRow["ExampleID"] = $recordNumber;
            $newRow["ExampleData"] = $recordNumber.ToString();
    
            if(($recordNumber % $global:batchSize) -eq 0) 
            {
                $bcp.WriteToServer($dt);
                $dt.Rows.Clear();
                Write-Host "$recordNumber records inserted";
            }
    
        }
    
        if($dt.Rows.Count -gt 0)
        {
            $bcp.WriteToServer($dt);
            Write-Host "$recordNumber records inserted";
        }
    
        $bcp.Close();
    
    }
    catch
    {
        throw;
    }
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, May 15, 2017 12:33 PM
    Moderator
  • Hi,

    I did not found a Key for the Operations Manager DB, but only for the DataWarehouse that does not have this symptom.

    I only have a performance counter that shows the Avg. Batch Size

    So... if this is the case of QSL 2016 i can`t do to much things.

    Kind regards,

    Adrian

    Monday, May 15, 2017 4:21 PM
  • Others have pointed this out as well, and I believe it is either a bug or just a design difference in SQL 2016 vs previous versions of SQL:

    https://www.sqlservercentral.com/Forums/1864337/Different-unused-table-space-on-SQL-Server-2012-vs-2016


    Kevin Holman http://blogs.technet.com/b/kevinholman

    Monday, May 29, 2017 8:55 PM
  • Hi Kevin,

    What do you think, should we schedule a Task to be run on the Performance Data Tables? the other way it to add a more of Disk space for the OeprationsManager DB.

    ALTER INDEX ALL
    ON dbo.PerformanceData_xx
    REORGANIZE;

    Thank you,

    Adrian

    Tuesday, May 30, 2017 3:24 AM