Использование SQLBulkCopy — значительно большие таблицы в SQL Server 2016, чем в SQL Server 2014.

У меня есть приложение, которое использует SqlBulkCopy для перемещения данных в набор таблиц. Недавно выяснилось, что пользователи, использующие SQL2016, сообщают о проблемах с заполнением их жестких дисков очень большими базами данных (которые не должны быть такими большими). Эта проблема не возникает в SQL2014. После проверки выяснилось, что запуск TableDataSizes.sql (скрипт прилагается) показал большое количество места в UnusedSpaceKB.

Я хотел бы знать, есть ли а) какая-то ошибка в SQLServer 2016 или наше использование SQLBulkCopy «столкнулось» с новой функцией. Я отмечаю, что в SQLServer 2016 произошли некоторые изменения в распределении страниц. В целом — с чем это связано?

Действия по воспроизведению Примечание. Ниже описана ситуация, с которой я столкнулся, когда была удалена второстепенная информация. На самом деле я не храню тысячи временных меток в таблице базы данных (другие столбцы были удалены).

1. Создадим базу данных на SQL (моя называлась TestDB)

2. Создайте таблицу в этой БД (используя скрипт, как показано ниже)

   USE [TestDB]
   GO
   
   /****** Object:  Table [dbo].[2017_11_03_DM_AggregatedPressure_Data]    Script Date: 07/11/2017 10:30:36 ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[TestTable](
       [TimeStamp] [datetime] NOT NULL
   ) ON [PRIMARY]
   
   GO
   

3. Создайте индекс в этой таблице (используя скрипт, как показано ниже)

   USE [TestDB]
   GO
   
   /****** Object:  Index [2017_11_03_DM_AggregatedPressure_Data_Index]    Script Date: 07/11/2017 10:32:44 ******/
   CREATE CLUSTERED INDEX [TestTable_Index] ON [dbo].[TestTable]
   (
      [TimeStamp] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
   GO
   

4. Начните вводить записи в таблицу, используя приведенный ниже код. (Это код для формы Windows, в которой просто есть кнопка с именем btnGo и числовой элемент UpDown с именем nupRecordsToInsert.

   Public Class Form1
   
   Private conStr As String = "Integrated Security=true;Persist Security Info=true;Server=.;Database=TestDB;Pooling=True"
   Dim tableName As String = "TestTable"
   
   Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
   
       Dim table as DataTable = GetData(nupRecordsToInsert.Value)
   
       Using conn As SqlConnection = New SqlConnection(conStr)
           conn.Open()
           Using sbc As SqlBulkCopy = New SqlBulkCopy(conStr, SqlBulkCopyOptions.UseInternalTransaction Or SqlBulkCopyOptions.KeepIdentity)
   
               sbc.DestinationTableName = "[" & tableName & "]"
               sbc.BatchSize = 1000
               sbc.WriteToServer(table)
   
           End Using
       End Using
   
       MessageBox.Show($"Records Inserted = {nupRecordsToInsert.Value} into Database - TestDB. Table - {tableName}")
   End Sub
   
   Private Function GetData(numOfRecordsNeeded As Integer) As DataTable
       Dim table As DataTable = New DataTable()
       table.Columns.Add("TimeStamp", GetType(DateTime))   
   
       Dim dtDateTimeToInsert as DateTime = DateTime.Now
   
       For index As Integer = 1 To numOfRecordsNeeded
           dtDateTimeToInsert = dtDateTimeToInsert.AddSeconds(2)
           table.Rows.Add(dtDateTimeToInsert) 
       Next
   
       Return table
   End Function
   

   Конец класса

5. В какой-то момент около 500 записей количество элементов в таблице базы данных будет означать, что новые записи необходимо будет записать на новую страницу. На данный момент интересно, как это происходит, как указано в фактических результатах.

Фактические результаты Базы данных в SQL2016 чрезвычайно велики (это происходит после заполнения первой страницы и запуска второй).

Это можно увидеть более подробно, когда

1. Запустите приведенный ниже SQL, чтобы получить представление о размерах таблиц. Чем больше записей вы запускаете в базу данных, тем чаще вы видите чрезвычайно большие числа в столбце UnusedSpaceKB.

   use [TestDB]
   
   SELECT 
      t.NAME AS TableName,
      s.Name AS SchemaName,
      p.rows AS RowCounts,
      SUM(a.total_pages) * 8 AS TotalSpaceKB, 
      SUM(a.used_pages) * 8 AS UsedSpaceKB, 
      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
   FROM 
      sys.tables t
   INNER JOIN      
      sys.indexes i ON t.OBJECT_ID = i.object_id
   INNER JOIN 
      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
   INNER JOIN 
      sys.allocation_units a ON p.partition_id = a.container_id
   LEFT OUTER JOIN 
      sys.schemas s ON t.schema_id = s.schema_id
   WHERE 
     t.NAME = 'TestTable'
     AND t.is_ms_shipped = 0
     AND i.OBJECT_ID > 255 
   GROUP BY 
     t.Name, s.Name, p.Rows
   ORDER BY 
     RowCounts desc
   

Вывод, показывающий большое число в UnusedSpaceKB

2. Выполнение приведенного ниже запроса показывает, что было выделено много страниц, но используется только первая из каждого «набора 8». Это оставляет последние 7 из каждых 8 страниц неиспользованными и, таким образом, создает много неиспользуемого пространства.

    select * from sys.dm_db_database_page_allocations
    (DB_id() , object_id('[dbo].[TestTable]') , NULL , NULL , 'DETAILED')
   

Ниже показана часть результатов, где выделение страниц не выполняется непрерывно.

Базы данных в SQL2014 не показывают эту проблему 1. При выполнении соответствующего запроса (как указано выше) мы не видим больших значений в столбце UnusedSpaceKB.

2. Выполнение другого запроса (который запрашивает — dm_db_database_page_allocations) показывает, что было выделено много страниц, но каждая страница используется последовательно. Нет пробелов - нет блоков по 7 неиспользованных страниц.

Ожидаемые результаты Я ожидаю, что SQL2016 будет вести себя как SQL2014 и не будет создавать очень большие таблицы. В частности, я ожидаю, что страницы будут распределены непрерывно и не будут иметь 7 пробелов в распределении страниц.

Если у кого-то есть мысли о том, почему я вижу эту разницу, это было бы чрезвычайно полезно.