使用 SQLBulkCopy - SQL Server 2016 中的表明显大于 SQL Server 2014 [英] Using SQLBulkCopy - Significantly larger tables in SQL Server 2016 than in SQL Server 2014

问题描述

我有一个应用程序，它使用 SqlBulkCopy 将数据移动到一组表中.最近发生的事情是，使用 SQL2016 的用户报告他们的硬盘驱动器充满了非常大的数据库(不应该那么大)的问题.SQL2014不会出现这个问题.经过检查，似乎运行 TableDataSizes.sql(附加脚本)显示 UnusedSpaceKB 中有大量空间.

I have an application that uses SqlBulkCopy to move data into a set of tables. It has transpired recently that users that are using SQL2016 are reporting problems with their harddrives being filled with very large databases (that should not be that large). This problem does not occur in SQL2014. Upon inspection it appears that running TableDataSizes.sql (script attached) showed large amounts of space in UnusedSpaceKB.

我想知道 a) SQLServer 2016 中是否存在一些错误，或者我们对 SQLBulkCopy 的使用是否与新功能冲突".我注意到 SQLServer 2016 中的页面分配发生了一些变化.一般而言 - 这是什么原因造成的?

I would like to know if a) There is some bug in SQLServer 2016 or if our use of SQLBulkCopy has "clashed" with a new feature. I note that there has been some changes to Page Allocation in SQLServer 2016. In general - What is causing this?

重现步骤注 – 以下描述了我看到的删除非必要信息的情况.我实际上并没有在数据库表中存储数千个时间戳(其他列已被删除).

Steps to Reproduce Note – The below describes a situation I am seeing with non-essential information removed. I am not actually storing thousands of timestamps in a database table (the other columns have been removed).

1. 在 SQL 中创建一个数据库(我的名为 TestDB)

2. 在该数据库中创建一个表(使用如下脚本)

1. Create a database in SQL (mine was called TestDB)

2. Create a table in that DB (using script as below)

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

在该表上创建索引(使用如下脚本)

Create an index on that table (using Script as below)

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

开始使用下面提供的代码将记录运行到表中.(这是 Windows 窗体背后的代码，该窗体上只有一个名为 btnGo 的按钮和一个名为 nupRecordsToInsert 的 numericUpDown.

Start to run records into the table using the code provided below. (This is the code behind for a windows form that simply has a button called btnGo on it and a numericUpDown called 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

结束课程

在某个时候，大约有 500 条记录，数据库表中的项目数意味着需要将新记录写入新页面.在这一点上，这很有趣，如实际结果中所述.

At some point around 500 records the number of items in the database table will mean that new records will need to be written onto a new page. At this point interesting this happen as outlined in Actual Results.

实际结果SQL2016 中的数据库非常大(这发生在第一个页面已填充并启动第二个页面之后).

Actual Results The databases in SQL2016 are extremely large (this occurs after the first page has been filled and a second one is started).

这可以更详细地看到

1. 运行以下 SQL 以了解表大小.您在数据库中运行的记录越多，您在 UnusedSpaceKB 列中看到的非常大的数字就越多.

1. Running the below SQL to get an idea of the tablesizes. The more records you run into the database the more you see extremely large numbers in the UnusedSpaceKB column.

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 中显示大量数字的输出

Output showing large number in UnusedSpaceKB's

1. 运行以下查询显示已分配了许多页面，但仅使用了每个一组 8"中的第一个.这使得每 8 页中的最后 7 页未使用，从而造成大量空间浪费.

1. Running the below query shows that many pages have been allocated but that only the first one out of every 'set of 8' is used. This leaves the last 7 of every 8 pages unused and thus creates a lot of wasted space.

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

下面显示了页面分配不连续运行的部分结果.

The below shows part of the results where the page allocations do not run continously.

SQL2014中的数据库没有出现这个问题1. 在运行适当的查询时(如上)，我们在 UnusedSpaceKB 列中没有看到大的值.

The databases in SQL2014 do not show this problem 1. When running the appropriate query (as above) we do not see large values in the UnusedSpaceKB column.

1. 运行另一个查询(即查询 - dm_db_database_page_allocations)显示已分配了许多页面，但每个页面都按顺序使用.没有间隙 - 没有 7 个未使用的页面块.

预期结果我希望 SQL2016 的行为类似于 SQL2014，并且不会创建非常大的表.特别是我希望页面被连续分配，并且分配中没有 7 个页面间隙.

Expected Results I would expect SQL2016 to behave like SQL2014 and not create extremely large tables. In particular I would expect the pages to be allocated contigously and not have 7 page gaps in the allocation.

如果有人对我看到这种差异的原因有任何想法，那将非常有帮助.

If anyone has any thought on why I see this difference it would be tremendously helpful.

推荐答案

您需要 使用跟踪标志 692:

如果出于任何原因，您无法更改批量大小，或者如果您没有看到使用默认最小日志记录行为改进的数据加载性能，您可以使用跟踪标志 692 (...) 在 SQL Server 2016 中禁用快速插入行为.我们预计在正常情况下客户不会需要此跟踪标志.

If for any reason, you cannot change the batchsize or if you are not seeing an improved data load performance with default minimal logging behavior, you can disable fast inserts behavior in SQL Server 2016 using trace flag 692 (...). We do not anticipate under normal circumstances this trace flag will be required for customers.

这篇关于使用 SQLBulkCopy - SQL Server 2016 中的表明显大于 SQL Server 2014的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！