SQL Server 2000 ???

1
Insights and Answers for IT Professionals
2
SQL Server 2000 ????
????????? (?????????)
3
????

• SQL Server 2000 ??????
• ????????
• ????????????
• ???????
• ??????????????
• ????
• ??

4
????SQL Server?????
1
???
5
3
??
???
2
4
5
????

• ???
• ?????
• ?????
• ????

6
SQL Server 2000 ???????
Server Net-Libraries
Open Data Services
SQL Server Service
Distributed Query OLE DB Providers
OLE Automation Objects
Extended Stored Procedures
System-Level Data Structures
Procedure Cache
Buffer Cache
Log Caches
Connection Context
7
????????
??\???(Counter) ?? ????
Memory\ Available KBytes ????????????????? ???? 5,000 KB,??? 5,000 KB ?? SQL Server ?????????,???????? Task Manager ? Physical Memory/Available
SQL Server\ Buffer Manager\Free pages ?????? Pages ??????
SQL Server\ Buffer Manager\Buffer Cache Hit Ratio ? Buffer ??????????????? ????? 95
Memory\ Pages/sec ???????????????????? page faults(?????????????????)? ????????,???????????????
Memory\ Page Reads/sec ??????? page faults ??? ????????,???????????????
8
SQL Server 2000 ?????????
SQL Server Buffer Manager
C
E
A
7
3
D
1
8
H
D
F
F
H
B
G
A
E
6
4
2
C
G
B
5
Local Database
Memory Buffer Cache Pages
Windows 2000 I/O Buffer (64 KB) 8-KB increments








A
B
C
D
E
F
G
H
















9
????????????

• ???????? I/O ???
• ??????????(SCSI?RAID)??????
• ?? PCI Bus ??????????

?????? x ?????? ?????
(KB) (???? MB)
?? 150(???? x 64(??????) 9.6 MB(?????
?????? KB) ???? MB)
???????? ????? ????? (???? MB) (????
MB) ????????
?? 40(???????? 9.6(????? 4 (????? ????
MB) ???? MB) ????????)
PCI bus ???? ???????? ???????? (????
MB) (???? MB)
?? 266(PCI bus 40(???????? 6 (?????
???? ???? MB) ???? MB) ???)
10
???????
??\???(Counter) ?? ????
PhysicalDisk\ Disk Time ?????????? ?????????
PhysicalDisk\ Avg. Disk Queue Length ??????????????????? queue ? ???????? 3
PhysicalDisk\ Disk Reads/sec ???????? ?????????????
PhysicalDisk\ Disk Writes/sec ???????? ?????????????
11
??????????

• ???????? log ??????????
• ????????????????
• ?? FileGroup
• ?? RAID
• ?? Partition View

12
SQL 2000 ?????????????(Threads)
??????? ???????, ???????? ???????
SQL Server
SQL Server ???? Thread Pool ????????? ???????
Open Data Services
2
Processor 0
UMS (User Mode Scheduler) Scheduler
???
Processor 1
UMS Scheduler
3
Local DB
Processor n
UMS Scheduler
??????? ??????? ??????, ??????? ?Storage Engine
Relational Engine
?????? IOCompletion Port
Storage Engine
Rowsets
13
??????????????
??\???(Counter) ?? ????
Processor\ Processor Time ?????????????????????????? ????????,??????????????,??????
System\ Context Switches/sec ??????????? ?????? 8000,???????????????? fiber mode
System\ Processor Queue Length ????????????? ??????? 2?????? 2???????,??????
Processor\ Privileged Time ??????????????? privileged ???(kernel ??,? I/O) ??????????????,??????????
14
?????????

• ?? Performance Counter ?????
• ?? Profiler ??????????????????

15
????

• ???????
• ????????,??????????
• ???????
• ????????,?????????
• ??????????

16
???????? Heap ????????
sysindexes
SELECT lastname, firstname FROM member WHERE
lastname BETWEEN 'Masters' AND 'Rudd'
17
?????????
Martin
SELECT lastname, firstname FROM member WHERE
lastname 'Ota'
Martin
18
????????????????
sysindexes
SELECT lastname, firstname, phone FROM
member WHERE firstname 'Mike'
Nagata
19
??????????(?)

• ??????,??????????????????
• ????????,?? SQL Server 2000????????????,????? SQL
  ???????(PK),?????????????????????? NONCLUSTERED
  ???,??????????????
• ??????????????,???? WHERE ?????????(SELECTIVITY)
• ???????,?????????????????????,??????????????

20
??????????(?)

• ?? Fillfactor ???????? split ??
• ??????????????,??????????????
• ????????????????????,?????????????????????????????
  ???????????????
• ????????????? DBCC SHOWCONTIG ???????????
• ?? WHERE ???? AND ???,??????????,??????????,??????
  ????????????

21
??????????(?)

• ?? WHERE ???? OR ???,???????????????,?? SQL
  Server 2000 ?? Table Scan ??????
• ?? WHERE ????????,? ltgt?xx?NOT( Salary gt 30000)
  ?? (? ?? Like ????),????????????? Table Scan
• ?? Index Analysis ??????????????
• ?? Index Tuning Wizard ??????????,?????????
• ??????????? View ????
• ?? sysindexes ???,???????????

22
Sysindexes ??????
???? ???? ??
id int ????ID (? indid 0 ? 255). ??, ?????????? ID
first binary(6) ????? root ????
indid smallint ??? ID 1 ?????gt1 ??????255 ? text ? image ????????
root binary(6) ?? indid gt 1 ? lt 255, root ?? root ????? ? indid 0 ? indid 255, root ???????
dpages int ?? indid 0 ? indid 1, dpages ?????????. ? indid255, ???? 0? ?????????????????
reserved int ?? indid 0 ? indid 1, ?????????????????? indid 255, ??? text ? image ????????? ??????,???????????
used int ?? indid 0 ? indid 1,???????????????????? indid 255,??? text ? image ??????????? ??????,?????????????
rowcnt bigint ?? indid 0 ? indid 1??? Data-level ????? ?? indid255,???? 0
xmaxlen smallint ?????????
maxirow smallint ?????????????????
statblob image ?????????????
23

• 10 ???? ?Pentium iii 750 256MRAM
• select top 10 from testtable order by col1 desc
• ????? 1.6 sec
• ?? Nonclustered Index 0.14 sec

24

• Indexed View
• Clustered Index ? Nonclustered Index ???

25
???????

• ????????????
• Join ?????
• ????????????
• ?????????
• ?????????

26
????????????
Transact-SQL
?????????,???? ??? relational engine ????? ?????
parsed query tree
???
27
Join ?????

• Nested Loop Join
• ?????????????
• ??????????????
• ?????????,???????
• Merge Join
• ????????? Join ????????
• ???????????????,??????????????,????????
• Hash Join
• ???????????????
• ????????? Join ???? Hash ?
• ???????? Join ??? Hash ,???????????

28
????????????
29

• ?? Query

30
?????????
CPU time CPU ?? ???? Elapsed time ???
SHOWPLAN_ALL ?????? Rows ??????? ??????? Executes
?????????
Table ????????? Scan count ????????? Logical
reads ????????? ?? Physical reads ????????? Read-a
head ????????? ???????
31
??????????(?)

• ?????? SELECT (???????) ??? WHERE ??????
• ???????????? Join ,???????(denormalized)?????
  Table ??,??? Table ????????,?????????
• ??? Join ???????,?????? Join ????????????,?????,??
  ????
• SQL Server 2000 ??????????,??????????,????????????
  ?
• ???????????????,??????????????????????????????????
  ???

32
??????????(?)

• ???? Trigger ??????(aggregation)? Sum?Avg ??
• ???????????????,????? Page ???????,?? I/O ????
• ???????????????????SQL ???????,????????
  Insert-Select-Update-Delete ? ISUD ?,?????????????

33
?????????(?)

• ????????????????(Stored Procedure),? Command ??
  Parameters ?????
• ?? Connection,??? Connection ???? Close ?????
• Connection ???????????? ODBC ? OLE DB Pool ?
• ?????? Recordset(??????)
• ?? COM/MTS
• ???????????

34
?????????(?)

• ???? SET LOCK_TIMEOUT lt??gt ???????? Lock timeout
  ????????? Lock,?????? 1222 ?????
• ?????(BeginTran)?,????????????(Commit)????(Rollbac
  k)????????????
• ???????,???????????????????,??????????,???????????
  
• ???????????,??????????????????(??????????)????????

35
?? Lock ????????
?? ????
Sp_lock spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 51 1 775009842 0 TAB IS GRANT
Sp_who spid ecid status loginame hostname blk dbname cmd ------------------------------------------------------------------------------------------------------------------------------ 51 0 runnable sa BYRONNB2 0 Northwind SELECT
Sp_who2 SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID ----- ------------------------------ ----- -------- ----- --------- ---------------- -------- ------ --------------------------- ------------------ ----- 52 sleeping sa BYRONNB2 . Northwind WAITFOR 110 43 03/07 141451 Query Analyzer 52 53 sleeping sa BYRONNB2 52 Northwind SELECT 30 0 03/07 141422 Query Analyzer53
kill ?????Connection kill ?????Connection
?? Enterprise Manager/Management/Current Activity ?? Enterprise Manager/Management/Current Activity
?? TableSyslockinfo?sysprocesses ?? TableSyslockinfo?sysprocesses
36
??????(?)

• ???????????????????????,??????????????????????,???
  ??????
• ???????????????????,??? Lock ????
• ?????????? DBCC DROPCLEANBUFFERS?DBCC
  FREEPROCCACHE ???????????,?????? I/O ???

37
??????(?)

• ??????????????????,?????????????,???????????,?????
  ??
• ????????,?????????????
• ?????????????,?????????,??????????

38
???????

• ?????? PC ?,???? gt 500 MB,?? Relation,????????????
  ???
• ??
• HP ? PC ???
• PIII
• 4 ? SCSI HD
• 256 MRAM
• ?? Update ??????

39
???????

• ?? DAO ?? SQL Server,??????? Table ??,? 1 ? 1
  ??????

40
???????

• ???????????,? DNA ???????????????????????
• ????????????
• ????????????????????
• ?????????,?????
• DB ???? Lock,Index,?????

41
??Conceptual ???

• ?????
• ??????????????,??????????????????
• ???????????,??????????????????????(??????????,????
  )
• ????????????,???????????????????,?????,???????????
  ?????
• ???????????????,??????????????????????????????,???
  ??????????????,????????????,??????????

42
???????

• ???????????,??????
• ??????????,???????
• ???????????? prototype ?????,???????????
• ? SQL Server Profiler ?????????
• ????????????(dead lock),???????????,???????,??????
  Lock?

43
?????????????

• ??????? (???????????)
• ?????? (Cursor)
• ?????? (stored procedure)
• ??????,??????????????????????,?????????????
• ??????? (isolation level) ???????????????????
• ???????????
• ???????

44
?????

• SQL Server ????
• http//msdn.microsoft.com/sqlserver/Default.asp
• ????
• msnews.microsoft.com ??? microsoft.public.sqlserve
  r
• ????
• ???? SQL Server 7.0(Inside SQL Server 7.0) --
  Microsoft Press
• SQL Server 7.0 Unleashed -- SAMs
• SQL 2000 ????
• Microsoft TechNet
• http//www.uuu.com.tw

45
(No Transcript)