将整数列添加到大表SQL Server 2012花费的时间比character更多...我... [英] Adding integer column to large table SQL Server 2012 taking more time than characterHow do I...

问题描述

将整数列添加到大型表SQL Server 2012所花费的时间比字符类型多。在此期间数据库挂起。为什么会有区别。我的数据库大小超过200 GB，表大小超过10GB，该表中的记录数超过2242607.



添加整数列（null）大约10分钟

添加Char / varchar列（null）大约2分钟

Adding integer column to large table SQL Server 2012 taking more time than character type. During that time database getting hang. Why is the difference. My database size is more than 200 GB and the table size is more than 10GB, number of record in that table is more than 2242607.

Adding Integer Column(null) approximate 10 min
Adding Char/varchar column(null) approximate 2 min

推荐答案

每当我们对我之前的表格进行架构更改时使用，我们先运行脚本来删除所有索引。您可能需要检查是否对表有任何约束。此链接可能对 sqlservercentral 有用[ ^ ]（刚刚意识到你必须在网站上注册 - 我已经有了 - 我发现它在过去很有用）



有用的文章来自msdn博客 [ ^ ]。



关于基于列类型的时序差异......这可能只是一个特征您在（int first，varchar 2nd）中执行它们的顺序，在这种情况下，缓存和分页，磁盘空闲空间，网络活动等可能会产生影响。使用监视工具找出实际发生的情况

Whenever we did schema changes against our tables in my previous employ, we ran scripts to drop all indexes first. You might need to check to see if you have any constraints on the table also. This link might be useful for that sqlservercentral[^] (just realised you have to register on the site - I already have - I've found it useful in the past)

Useful article from msdn blogs[^] on performance during schema changes.

As to the difference in timings based on column types ... this could just be a feature of the order you did them in (int first, varchar 2nd) in which case caching and paging, disk free space, network activity etc etc could be having an effect. Use monitoring tools to find out what is actually happening

首先，您的数据库的大小如此之大。缩小数据库文件和日志文件以减小大小。尽量不要将所有细节存储在日志文件中。维护日志文件不是一个好主意，从日志文件中检索数据是一项繁琐的工作，最佳做法是每天进行备份。

First of all your database is so larger in size . shrink your database file and log file to reduce the size . try not store all details in log files. To maintain log file is not a great idea , retrieve data from log files is tedious job and the best practice is to take daily backup.

好的，我在这个问题上已经很晚了。



差异可能是因为？df文件中数据的物理布局。

但这只是一个猜测;我只想了解最终可以解释这种行为的原因。


想象一下整数类型优先存储在文件的开头，与varchars相反存储在最后。

在这种情况下，添加整数列意味着插入整数列和现有整数列的结尾（即移动所有剩余数据）;而添加varchar列只意味着在文件末尾添加一个新列。



但是，正如我所说，这只是猜测。我不知道MSSQL文件存储的内部，我甚至不确定事情是如此简单/天真地描述。



干杯。

Ok, I'm so late on this one.

The difference could be due to the physical layout of data in ?df files.
It's just a guess, though; I'm only trying to understand what could eventually explain such a behaviour.

Imagine integral types are stored preferentially at the beginning of the file, opposed to varchars which would be stored at the end.
In this case, adding an integer column means inserting an integer column and the end of existing integer colums (i.e., moving all remaining data); whereas adding a varchar column only means adding a new column at the end of the file.

But, as I told, it's just a guess. I don't know the internals of MSSQL files storage, and I'm not even sure things could be described so simply/naively.

Cheers.

这篇关于将整数列添加到大表SQL Server 2012花费的时间比character更多...我...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！