sql server 面试题（3） - 未名空间MITBBS历史存档

z*y2008-10-27 07:10

1 楼

Round 3!
这是一道SQL Server failover clustering 安装问题：again, open question:
1. Now we are planning to install 64 bit sql server on a two node A/P
clustering environment with SANS. The OS is windows server 2003 on IA-64 .
The db is OLTP db and has the initial size of 650GB. The storage engineer
will create the LUN, what are the requirements you might give to him (RAID
type/Alignment,etc)?
2.The server has totally 16 CPU cores, in order to optimize the performance,
how many tempdb data files you woul

B*g2008-10-27 07:10

2 楼

zan.
问一下，找team lead，谁考他／她？

RAID
performance,
I

【在 z***y 的大作中提到】

: Round 3!
: 这是一道SQL Server failover clustering 安装问题：again, open question:
: 1. Now we are planning to install 64 bit sql server on a two node A/P
: clustering environment with SANS. The OS is windows server 2003 on IA-64 .
: The db is OLTP db and has the initial size of 650GB. The storage engineer
: will create the LUN, what are the requirements you might give to him (RAID
: type/Alignment,etc)?
: 2.The server has totally 16 CPU cores, in order to optimize the performance,
: how many tempdb data files you woul

B*g2008-10-27 07:10

3 楼

没看到最后一句，太同意了。NND，现在俺整天干极无聊的工作，想学DBA，公司还不同
意。自己看了书也没处练手。要是有找junior的oracle DBA的给介绍一下。

RAID
performance,
I

【在 z***y 的大作中提到】

: Round 3!
: 这是一道SQL Server failover clustering 安装问题：again, open question:
: 1. Now we are planning to install 64 bit sql server on a two node A/P
: clustering environment with SANS. The OS is windows server 2003 on IA-64 .
: The db is OLTP db and has the initial size of 650GB. The storage engineer
: will create the LUN, what are the requirements you might give to him (RAID
: type/Alignment,etc)?
: 2.The server has totally 16 CPU cores, in order to optimize the performance,
: how many tempdb data files you woul

z*y2008-10-27 07:10

4 楼

of course me yah

【在 B*****g 的大作中提到】

: zan.
: 问一下，找team lead，谁考他／她？
:
: RAID
: performance,
: I

z*y2008-10-27 07:10

5 楼

Try learn SQL Server bah...
Recently I saw many companies switched to sql server. And the pay is as much
as
good with oracle dba.

【在 B*****g 的大作中提到】

: 没看到最后一句，太同意了。NND，现在俺整天干极无聊的工作，想学DBA，公司还不同
: 意。自己看了书也没处练手。要是有找junior的oracle DBA的给介绍一下。
:
: RAID
: performance,
: I

B*g2008-10-27 07:10

6 楼

you are PM?

【在 z***y 的大作中提到】

: of course me yah

B*g2008-10-27 07:10

7 楼

nnd, I convert from sql server to oracle in 2004

much

【在 z***y 的大作中提到】

: Try learn SQL Server bah...
: Recently I saw many companies switched to sql server. And the pay is as much
: as
: good with oracle dba.

z*y2008-10-27 07:10

8 楼

谈谈看法：
１.　ＲＡＩＤ
应该尽量避免ＲＡＩＤ５。这是因为它每一次写都要有三次ｄｉｓｋ　ｉ／ｏ　，不适
合ＯＬＴＰ，应该尽可能地选择ＲＡＩＤ１，ＲＡＩＤ１０.　退一步说，　即使用Ｒ
ＡＩＤ５，ｔｅｍｐdb 绝对不能放在上面。
2. Alignment
为什么要align，因为MBR占磁道的头62块，数据从63块起，但是磁盘每次读都是以64
块为单位，这样一来，比如我要读1块，那一次i/o就可以，但是如果我要读2块，就
要disk i/o两次，所以我们要求设定数据从64块起,或者64的倍数起。
经常用到的工具是diskpar
diskpar alignment=1024
3. NTFS 格式化
对于sql server, 默认的格式化block 是 4k, sql server engine 每次读8个页，共
64k, 所以NTFS应该格式化为64k block
4. disk layout
至少， tempdb 要单独在一个LUN 上。
log 要和data 在不同的LUN上。
第二题：
一般说来，数据文件的个数，是所有CPU core 数量的.25 ~1.0. 而te

【在 z***y 的大作中提到】

: Round 3!
: 这是一道SQL Server failover clustering 安装问题：again, open question:
: 1. Now we are planning to install 64 bit sql server on a two node A/P
: clustering environment with SANS. The OS is windows server 2003 on IA-64 .
: The db is OLTP db and has the initial size of 650GB. The storage engineer
: will create the LUN, what are the requirements you might give to him (RAID
: type/Alignment,etc)?
: 2.The server has totally 16 CPU cores, in order to optimize the performance,
: how many tempdb data files you woul

w*e2008-10-27 07:10

9 楼

RAID5 的读的速度是很快的, 所以我们是用在ARCHIVE database or table上的.
因为archive 的东西主要是读.
对其他的, 还只是耳闻.....听说过62块什么的.....看来差得远呀...555....

64

【在 z***y 的大作中提到】

: 谈谈看法：
: １.　ＲＡＩＤ
: 应该尽量避免ＲＡＩＤ５。这是因为它每一次写都要有三次ｄｉｓｋ　ｉ／ｏ　，不适
: 合ＯＬＴＰ，应该尽可能地选择ＲＡＩＤ１，ＲＡＩＤ１０.　退一步说，　即使用Ｒ
: ＡＩＤ５，ｔｅｍｐdb 绝对不能放在上面。
: 2. Alignment
: 为什么要align，因为MBR占磁道的头62块，数据从63块起，但是磁盘每次读都是以64
: 块为单位，这样一来，比如我要读1块，那一次i/o就可以，但是如果我要读2块，就
: 要disk i/o两次，所以我们要求设定数据从64块起,或者64的倍数起。
: 经常用到的工具是diskpar

c*d2008-10-27 07:10

10 楼

我的一些看法,
第1题.
tempdb可以放在RAID 0上
log放在RAID 0+1
data最好放在RAID 0+1上

RAID
performance,
I
2005

【在 z***y 的大作中提到】

: Round 3!
: 这是一道SQL Server failover clustering 安装问题：again, open question:
: 1. Now we are planning to install 64 bit sql server on a two node A/P
: clustering environment with SANS. The OS is windows server 2003 on IA-64 .
: The db is OLTP db and has the initial size of 650GB. The storage engineer
: will create the LUN, what are the requirements you might give to him (RAID
: type/Alignment,etc)?
: 2.The server has totally 16 CPU cores, in order to optimize the performance,
: how many tempdb data files you woul