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

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql server 面试题（4）

sql server 面试题（4）# Database - 数据库

z*y2008-10-27 07:10

1 楼

round　４：
调优题：
1. Developers reported that one db is very slow and ask you to tune up, what
should you do?
2. Now, you ruled out the hardware issue, the code issue, you think it might
be the fragmentation issue, how to verify that--you need show me the dbcc
command with correct parameters, remember it is 24X7 database.
3. Depends interviewee can answer the question 2, now explain what's the
meaning of the result.

j*n2008-10-27 07:10

2 楼

1. 2 tools first: perfmon, profiler
2 + 3. I'd highly recommend ppl who wants to answer this question first take
a look DBCC SHOWCONTIG in BooksOnline. specially it's example.

z*y2008-10-27 07:10

3 楼

老兄，这些答案都是没有一定的对错，只有好与不好。这样的回答太简单。
而且你不能在面试地时候说I highly recommend ppl who wants anwser this
questinon. 虽然
你的思路是正确。作为和大家讨论，说的越具体越好！
像coolbid同学学习！

take

【在 j*****n 的大作中提到】

: 1. 2 tools first: perfmon, profiler
: 2 + 3. I'd highly recommend ppl who wants to answer this question first take
: a look DBCC SHOWCONTIG in BooksOnline. specially it's example.

j*n2008-10-27 07:10

4 楼

哦，我只是把你每个问题的回答开个头而已，像酷毕得那样都回答全了其他想学习的朋
友就没得玩了，呵呵。

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

: 老兄，这些答案都是没有一定的对错，只有好与不好。这样的回答太简单。
: 而且你不能在面试地时候说I highly recommend ppl who wants anwser this
: questinon. 虽然
: 你的思路是正确。作为和大家讨论，说的越具体越好！
: 像coolbid同学学习！
:
: take

n*62008-10-27 07:10

5 楼

1. A couple of scenarios that can slow down the database.
a. First look at sp_who2 active to see how many connections are and who is
doing what. Then use profiler for more details. If suspected connections can
not identified, use trace later if cannot find answer in the following
items.
b. Look at data file and log file, Shrink if necessary.
c. Look at usage of CPU, memory to see if any bottleneck. 80% of CPU
usage can be recognized as a bottleneck.(if my memory does not go wrong). (use perfmon)

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

: round　４：
: 调优题：
: 1. Developers reported that one db is very slow and ask you to tune up, what
: should you do?
: 2. Now, you ruled out the hardware issue, the code issue, you think it might
: be the fragmentation issue, how to verify that--you need show me the dbcc
: command with correct parameters, remember it is 24X7 database.
: 3. Depends interviewee can answer the question 2, now explain what's the
: meaning of the result.

z*y2008-10-27 07:10

6 楼

多谢回复！
第一个问题没有什么标准答案，主要看interviewee的思路。
a.c. 是不错的方法。
b. 有待商榷，为什么要shrink 呢？在production environment, shrink不是一个很好
的主意。
第二，三个问题，
dbcc showcontig with fast
～～～～～
这是重点。
在结果中，主要看他的log read switch 和 extend switch 和 fragment
log read switch 发生在读页过程中， extend switch 发生在读extend （八张页）
过程中。
fragment 越高，碎片越多。
一般来说，碎片高于30%，利用reorganize 是没有用的，只有rebuild。

can
(use perfmon)

【在 n********6 的大作中提到】

: 1. A couple of scenarios that can slow down the database.
: a. First look at sp_who2 active to see how many connections are and who is
: doing what. Then use profiler for more details. If suspected connections can
: not identified, use trace later if cannot find answer in the following
: items.
: b. Look at data file and log file, Shrink if necessary.
: c. Look at usage of CPU, memory to see if any bottleneck. 80% of CPU
: usage can be recognized as a bottleneck.(if my memory does not go wrong). (use perfmon)

c*d2008-10-27 07:10

7 楼

我真是比窦娥还冤.....

【在 j*****n 的大作中提到】

: 哦，我只是把你每个问题的回答开个头而已，像酷毕得那样都回答全了其他想学习的朋
: 友就没得玩了，呵呵。

z*y2008-10-27 07:10

8 楼

哪里！
不管题目是什么，回答的方式和内容反映了一个dba 的特点和技术掌握度。我还是欣
赏 detail oriented 的dba。
而且，当interviewee对一个问题很了解，他也愿意从各个方面阐述。当答案就不是很
清楚，这个时候就要多问。不过我个人，如果连续问两次都没有合适的答案，就会转
移话题。

【在 c*****d 的大作中提到】

: 我真是比窦娥还冤.....

S*k2008-10-27 07:10

9 楼

I guess “Log read switch”, you mentioned, is “Pages scanned”.
In most cases I would like to simply check Scan Density value. In theory the
value should be closed to 100%. If the value is 80%-70%, it means
fragmentation exists and reindex may be necessary.

w*e2008-10-27 07:10

10 楼

应该是logical scan 和extent scan fragmentation吧....

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

: 多谢回复！
: 第一个问题没有什么标准答案，主要看interviewee的思路。
: a.c. 是不错的方法。
: b. 有待商榷，为什么要shrink 呢？在production environment, shrink不是一个很好
: 的主意。
: 第二，三个问题，
: dbcc showcontig with fast
: ～～～～～
: 这是重点。
: 在结果中，主要看他的log read switch 和 extend switch 和 fragment

S*k2008-10-27 07:10

11 楼

If there is a clear clue which index may be in fragmentation, dbcc
showcontig is a pretty good choice.
Otherwise, I more like to get the avg_fragmentation_in_percent and avg_page_
space_used_in_percent values from sys.dm_db_index_physical_stats for easy
analysis purpose.

z*y2008-10-27 07:10

12 楼

这是很好！
这两个DMV更准确！

page_

【在 S***k 的大作中提到】

: If there is a clear clue which index may be in fragmentation, dbcc
: showcontig is a pretty good choice.
: Otherwise, I more like to get the avg_fragmentation_in_percent and avg_page_
: space_used_in_percent values from sys.dm_db_index_physical_stats for easy
: analysis purpose.

n*62008-10-27 07:10

13 楼

请教一下，在management studio -> task -> shrink也可以看到一个比例。这个比例
（%）是不是表示page平均剩余空间多少？
windows下操作系统一次最大读1 extent，包含8 page，每个page要预留20-25%的空间
来提高I/O。多次插入，删除后，有很多空间没法很好的利用，导致%降低，导致I/O降低，需要shrink吗？
这个page是不是你们所讨论的？deframentation 是指什么？我说用shrink zenny为什
么说没有道理？如果不用shrink，用什么办法？
另外问一下，在24*7production environment，如果在凌晨作maintainance plan包含
shrink,为什么shrink不好？

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

: 这是很好！
: 这两个DMV更准确！
:
: page_

S*k2008-10-27 07:10

14 楼

Shrink database or shrink files is to remove unused pages and release disk
space. It is usually used after delete operations, such as truncate a table
or drop a table.In most cases, the fragmentation related to performance is
index fragmentation which is the internal or external fragmentation of the
pages supporting indexes. Because removing unused pages could not help
reduce the degree of index pages fragmentation, shrink could not help too
much to improving performance.
Repeatedly shrinking a

n*62008-10-27 07:10

15 楼

Thank you very much.

table
free
be

【在 S***k 的大作中提到】

: Shrink database or shrink files is to remove unused pages and release disk
: space. It is usually used after delete operations, such as truncate a table
: or drop a table.In most cases, the fragmentation related to performance is
: index fragmentation which is the internal or external fragmentation of the
: pages supporting indexes. Because removing unused pages could not help
: reduce the degree of index pages fragmentation, shrink could not help too
: much to improving performance.
: Repeatedly shrinking a

z*y2008-10-27 07:10

16 楼

ze ze ze.
velly G00d!

table
free
be

【在 S***k 的大作中提到】