SQL2008或SQL2012 如何配置, 使SQL的执行效率最高?

硬件配置:
1.双CPU,共16核32线程,每个逻辑线程的主频是2.1GHz
2.内存:48G

软件配置:
1.Win7 64位
2.SQL2012

我是使用内存表插入数据的,每次插入两千条左右数据;
表有八个字段,没有任何约束,有两千个这样的表.

现在的测试结果是32核没有4核3GHz的机器快,这是什么原因?
为什么资源监视器的资源利用率不高?

=========
我想最大范围提升执行速度(主要是插入操作),把大部分硬件资源耗在插入操作也没关系
请各位大侠建议.谢谢.

21 个解决方案

#1

CPU的频率哪个高？插入除了看CPU的话还需要看IO，你看一下执行慢的语句等待的资源是不是IO？另外用不到并行计划的话，那么多核也是没用的。

#2

提升数据插入速度，主要瓶颈在io上面。
1、采用固定内存分配，如果服务器上只有SQLSERVER在运行，使用6G给系统，42G给SQLSERVER。
2、做磁盘阵列RAID10，第一个阵列放置系统，temp数据库，第二个阵列放置数据文件，第三个阵列放置日志文件。

#3

资源监控

引用 1 楼 SmithLiu328 的回复:

CPU的频率哪个高？插入除了看CPU的话还需要看IO，你看一下执行慢的语句等待的资源是不是IO？另外用不到并行计划的话，那么多核也是没用的。

单个核来说4核的机器3GHz，32核机器是2.1GHz.
程序是并行的，但是我感觉sql server执行insert却是串行的。请问sql server如何并行执行？

#4

资源监控器等待的只有logging

#5

引用 3 楼 liuxmzc 的回复:

资源监控

Quote: 引用 1 楼 SmithLiu328 的回复:

CPU的频率哪个高？插入除了看CPU的话还需要看IO，你看一下执行慢的语句等待的资源是不是IO？另外用不到并行计划的话，那么多核也是没用的。

单个核来说4核的机器3GHz，32核机器是2.1GHz.
程序是并行的，但是我感觉sql server执行insert却是串行的。请问sql server如何并行执行？

你说的程序并行是指什么？ 

#6

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

#7

也就意味着同时有多次插入请求。

引用 5 楼 SmithLiu328 的回复:

Quote: 引用 3 楼 liuxmzc 的回复:

资源监控

Quote: 引用 1 楼 SmithLiu328 的回复:

CPU的频率哪个高？插入除了看CPU的话还需要看IO，你看一下执行慢的语句等待的资源是不是IO？另外用不到并行计划的话，那么多核也是没用的。

单个核来说4核的机器3GHz，32核机器是2.1GHz.
程序是并行的，但是我感觉sql server执行insert却是串行的。请问sql server如何并行执行？

你说的程序并行是指什么？ 

#8

那应该是可以的，你同时运行多次插入看看ＳＱＬ　Ｓｅｒｖｅｒ是如何运行语句的。

#9

引用 8 楼 SmithLiu328 的回复:

那应该是可以的，你同时运行多次插入看看ＳＱＬ　Ｓｅｒｖｅｒ是如何运行语句的。

跟踪sql server执行记录发现确实有多表交叉插入，但是资源监控器的整体资源利用率很低，速度也提不上

#10

引用 2 楼 jack11430 的回复:

提升数据插入速度，主要瓶颈在io上面。
1、采用固定内存分配，如果服务器上只有SQLSERVER在运行，使用6G给系统，42G给SQLSERVER。
2、做磁盘阵列RAID10，第一个阵列放置系统，temp数据库，第二个阵列放置数据文件，第三个阵列放置日志文件。

我的logging等待很大1000多毫秒，  IO和其他基本没有等待，有也是很快被处理掉.

#11

LZ的问题很清晰了：
大量的日志写入时瓶颈。
1、按我之前说的做。
2、把日志大小设置为磁盘空间的80%，允许自动增长，每天按GB来增长（主要看你的日志增长速度）。
3、定时备份日志。
解释：
1、可以充分的利用磁盘，达到多个磁盘协同读写，同时日志和数据分离，可以分离数据的随机写入和日志的顺序写入，这样可以尽可能的提升磁盘读取速度。
2、设置日志的最大大小，并让日志按设置大小增长，可以让日志基本达到不自动增长的目的，因为日志文件自动增长会导致磁盘碎片和消耗磁盘的性能。

#12

我分配了40G内存给server，我使用的是SSD，并且把数据库的日志文件设置大了。
刚试验了下，依然是logging的等待， 通过SQL server Profilter 观测insert的速度大约是 100条/s，这个速度基低，而查询的速度达到1000条/s。

#13

引用 11 楼 jack11430 的回复:

LZ的问题很清晰了：
大量的日志写入时瓶颈。
1、按我之前说的做。
2、把日志大小设置为磁盘空间的80%，允许自动增长，每天按GB来增长（主要看你的日志增长速度）。
3、定时备份日志。
解释：
1、可以充分的利用磁盘，达到多个磁盘协同读写，同时日志和数据分离，可以分离数据的随机写入和日志的顺序写入，这样可以尽可能的提升磁盘读取速度。
2、设置日志的最大大小，并让日志按设置大小增长，可以让日志基本达到不自动增长的目的，因为日志文件自动增长会导致磁盘碎片和消耗磁盘的性能。

请问有何建议？

#14

干了一晚上了,现在实现了往2400多个表中分别插入3000条记录,用时7分钟. 
这样算下来排除资源切换,insert速度达到17000条/s,对这个速度还是很满意的
数据库IO高峰到达80MB/s
内存基本6分钟的时候到达90%以上
buffer IO 和logging 阻塞的很少.
我现在发现logging阻塞的原因是操作过于频繁,只要歇息几秒自然将下来而且就是一千多没有增大的迹象,说明这个不是主要影响性能的地方,影响性能的地方是SQL 语句.

最后谢谢两位大侠的指点我这位新手.

#15

该回复于2013-06-22 11:36:09被管理员删除

#16

重点就是LOG配置上，或者优化插入批次

#17

引用 6 楼 liuxmzc 的回复:

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

能否把你的C#代码贴出来看看。

#18

引用 17 楼 SQL_Beginner 的回复:

Quote: 引用 6 楼 liuxmzc 的回复:

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

能否把你的C#代码贴出来看看。

其实我觉得两千多次并行循环应该当作一个BATCH，然后再做Commit,这样写LOG的次数会变少，性能应该会有改进。 

#19

引用 18 楼 SmithLiu328 的回复:

Quote: 引用 17 楼 SQL_Beginner 的回复:

Quote: 引用 6 楼 liuxmzc 的回复:

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

能否把你的C#代码贴出来看看。

其实我觉得两千多次并行循环应该当作一个BATCH，然后再做Commit,这样写LOG的次数会变少，性能应该会有改进。 

嗯，所想略同，但是我不确实它到底是不是已经这样做了，所以想看看他的代码。

#20

引用 18 楼 SmithLiu328 的回复:

Quote: 引用 17 楼 SQL_Beginner 的回复:

Quote: 引用 6 楼 liuxmzc 的回复:

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

能否把你的C#代码贴出来看看。

其实我觉得两千多次并行循环应该当作一个BATCH，然后再做Commit,这样写LOG的次数会变少，性能应该会有改进。 

这里一共有两千条记录，还要做batch，一个batch 1000条吗？我感觉做batch有可能会提高性能，但效果应该不大。

CPU数目虽多，但是单个CPU的能力不一定强；而跑得慢的操作，需要消耗一定的CPU资源，同时SQLServer又是用单线程完成的。

在OLTP类型的应用里，语句相对比较简单，操作的数据量比较少，SQL
Server会选择用单个线程完成。也就是说，每个操作，SQL
Server都是用单个CPU做的。CPU数目多，可以使得SQL
Server能够在同一个时间，处理更多的并发请求。但是对于单个操作的时间长短，则是由单个CPU的能力决定的。
现在服务器上的CPU，往往一个就包含4核、32核，而且常常设置成Hyper-Threading。结果是在Windows里看上去，CPU的数目很多。但是这些CPU都是逻辑CPU。它们单个的处理能力怎么样？最好能测试一下。如果真是并行执行往2000个表里放数据的话。还应该是32盒的跑的快，你也许该看看你的代码是不是并行执行的。因为你上面说你的资源利用率不高。

#21

引用 18 楼 SmithLiu328 的回复:

Quote: 引用 17 楼 SQL_Beginner 的回复:

Quote: 引用 6 楼 liuxmzc 的回复:

c#并行开发的app，如循环用了Parallel.For在这里面通过内存表批量插入数据，每一次循环insert一表（2000条左右记录），有2000多次并行循环

能否把你的C#代码贴出来看看。

其实我觉得两千多次并行循环应该当作一个BATCH，然后再做Commit,这样写LOG的次数会变少，性能应该会有改进。 

我确实采用类似Batch的方法，最后再commit。谢谢， 结贴了。