Contents

1 碁煙 襷覯
2 碁煙 襴
3 Index Design 譟一


1 碁煙 襷覯 #

MSSQL Server 碁煙 襷覯 朱 Performance Tuning Tool 螻牛. 企 Profiler朱 企 讌 襭襯 伎 覿 れ Index 譟一語 譴. 伎 蟆暑 2~3 谿 譴企手 襷 一企. 讌襷 襷轟伎 . 碁煙 襷覯螳 譟一誤伎朱 蟆郁骸 蟲磯蠍磯れ 覿 蠍 覓語 覿覿 伎殊伎 . 螳 譬 蟆 蟆 譬.

碁煙 襷覯螳 企 襦讌 蟆郁骸襯 企讌 覈襯伎襷 襷 蟆渚 牛 碁煙 襷覯螳 蟆郁骸襯 襷 伎 企 螻 . Index Tuning Wizard 譯朱 蟆壱碁煙るゼ 燕り 襷 碁レ 蠍一語碁. 譟郁唄 伎 伎 蟆壱碁煙るゼ 襷るり . 蠏碁覩襦 碁煙 襷れ伎 . (る 螻給 Trace襭 磯殊 蟆郁骸螳 れ蠍磯 .)

襯 れ, 覲 蟆曙 ChartNo(谿碁)朱 貉殊 覿覿 Query 譟郁唄 蟆 . 蟆曙一 磯 襴讌襷 企 螳 ChartNo 1~3蟇 . 蠏碁磯 Index Tuning Wizard襯 牛 襷れ伎 Index れ螻 觜訣.

  • ChartNo + ColA + ColB
  • ChartNo + ColB + ColC + ColD
  • ChartNo + ColZ

覿覿 蟆郁骸螳 企. Where 覈 譟郁唄 覈 蟆壱碁煙る 襷れ伎 蟆企. るゼ 覲伎 讌襷 蟆渚 Index Tuning Wizard 譟郁唄 覈 貉手骸 襯 伎 Index襯 燕 蟆朱 .

蟆曙 WHERE ChartNo = 1234 襷 3蟇, 襷 覈 蟇伎 覿蟲螻 ChartNo襦 蟆壱碁煙り 襷 襷れ伎 蟆覲 . 企 蟆 Index Tuning Wizard 訖襷 螳覦 企 蟆渚レ 覲伎企 蟆曙郁 . 蠏碁 螳 蟆曙 Tuning 蟆渚 危 Index 襴覿 . 碁煙るゼ 讌譯手碓 れ 燕 蟆企. 螳 蟆曙磯 覈 企. ChartNo 貉殊 碁煙る 蟲燕 蟆 螳 豕企. 襯 覲伎.

2 碁煙 襴 #

企 れ螻 螳 碁煙り 焔 .

index_tuning_wizard01.jpg

襷豺 碁煙 襷覯襯 蟆螻 觜訣 碁煙るゼ 螳譟. 覿覈 蠍一 焔 碁煙 覈 螳 れ襦 讌 . 襷 る 蠏瑚 覓語. れ 碁煙 譴 讌 碁煙れ企.

IDX_YEAR_MONTH_DAY
IDX_YEAR_MONTH_DAY_HOUR

讌 讌 企 伎 蠍 瑚朱襦 牛襦 蟆. 危エ覲伎 蟆 企. 谿願 襷 讌 る 碁煙るゼ 豢螳襦 襴 譯殊伎 . 貎朱Μ 企 覲伎 . 貎朱Μ 伎 譟一企蓋 蟆郁骸 企 貎朱Μ SP 錫 蟾讌襷 襦 . 蠏碁覩襦 螳 危 . 蠏碁覩襦 れ 碁煙る 蟇磯伎 .

IDX_YEAR_MONTH_DAY_HOUR_SERVER

YEAR, MONTH, DAY, LEVEL ′語れ レ 覩語讌襷 LEVEL レ 覩語讌 朱襦 IDX_YEAR_MONTH_DAY_LEVEL_TYPE 碁煙れ LEVEL 觜碁 . 企 朱 覲企 YEAR, MONTH, DAY襦 蟲焔 Non-Clustered Index 覈 蟇磯伎 . るジ 貎朱Μ 覓企Μ螳 蟆企. 貎朱Μ 襯 覲伎.

SELECT 
	YEAR
, 	MONTH
, 	DAY
, 	HOUR
, 	MINUTE
, 	TYPE
,	SUM([COUNT]) AS COUNT   
FROM T_CURRENT_CHARACTER_INFO
WHERE  YEAR BETWEEN DATEPART(yy, '2004-05-25 00:00:00') 
AND DATEPART(yy, '2004-05-26 00:00:00') 
AND  MONTH BETWEEN DATEPART(mm, '2004-05-25 00:00:00') 
AND DATEPART(mm, '2004-05-26 00:00:00') 
AND  DAY  BETWEEN DATEPART(dd, '2004-05-25 00:00:00') 
AND DATEPART(dd, '2004-05-26 00:00:00') 
AND  TYPE = 30
AND LEVEL BETWEEN 100 AND 400  
GROUP BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE  
ORDER BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE   
GO

蟆曙磯 蟇一 豕企. 讌襯 , , 朱 讓手 蟆 覩瑚 讌 . れ襦 貎朱Μ襯 ろ覃 YEAR 蟇碁Π 碁煙 襷 . Clustered Index襯 朱 讌 . 襷 Non-Clustered Index襯 る 觜 蟆企. ろ 蟆郁骸 貎朱Μ Non-Clustered Index襯 .
企. れ  1, 朱Μ 所鍵  8447, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.

Clustered Index襯 蟆 れ螻 螳 蟆郁骸襯 覲伎.
企. れ  1, 朱Μ 所鍵  1255, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.

襷 企 れる 朱襷殊 I/O螳 覦蟾?
企. れ  1, 朱Μ 所鍵  2026, 覓朱Μ 所鍵  4, 覩碁Μ 所鍵  223.

蟆郁骸襦 覩碁企骸 YEAR 0.5朱 蟆 . 蟆郁骸 67蟇伎企. 67蟇伎 所鍵 伎 襷 伎襯 曙. 企 語 蠏 豌 覯讌 語企. YYYY, MM, DD襯 覲牛煙朱 覲 蟆語, 殊煙朱 覲 蟆語 豢覿 螻り 伎 . 伎 螳 蟆曙磯 YYYYMMDD螳 螳企. 一危 覓語螳 . 一危 蠏豺(蠏豺 谿瑚) 覲企 TinyInt Int覲企 一螳 . 蠏碁蠍 覓語 WHERE Month = DATEPART() TinyInt = Int 螳 觜蟲螳 覩襦 企朱 譬豸 貉殊 Month Covert(Int, Month) 螳 企 覲 殊企. 蠏碁覩襦 碁煙るゼ 讌 覈詩. 覯讌 覓語 碁煙れ 觜 煙企. MSSQL Server 一危一 ′語ろ 伎襯 曙朱り 蠍 覓語 螳 碁煙り る 朱 蠍郁 碁煙るゼ . 蠏碁覩襦 螳 蟆曙磯 Bookmark Lookup 襷 伎襯 ′語ろ 蟆企. れ 碁煙るゼ 襴螻, 貎朱Μ企.

--襷り覲襯 讌朱 覦る 螳
DECLARE @SDate CHAR(8)
DECLARE @EDate CHAR(8)

SET @SDate = CONVERT(CHAR(8), CAST('2004-05-25 00:00:00' AS DATETIME), 112)
SET @EDate = CONVERT(CHAR(8), CAST('2004-05-26 00:00:00' AS DATETIME), 112)

SELECT 
	YEAR
, 	MONTH
, 	DAY
, 	HOUR
, 	MINUTE
, 	TYPE
,	SUM([COUNT]) AS COUNT   
FROM T_CURRENT_CHARACTER_INFO
WHERE  YEAR BETWEEN LEFT(@SDate, 4) AND LEFT(@EDate, 4)
AND  MONTH BETWEEN SUBSTRING(@SDate, 5, 2) AND SUBSTRING(@EDate, 5, 2)
AND  DAY  BETWEEN RIGHT(@SDate, 2) AND RIGHT(@EDate, 2)
AND  TYPE = 30
AND LEVEL BETWEEN 100 AND 400  
GROUP BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE  
ORDER BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE   
GO

れ 襴 碁煙る.
index_tuning_wizard02.jpg

れ 蟆郁骸企.
企. れ  1, 朱Μ 所鍵  144, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.

貎朱Μ 蟆郁骸 I/O 壱 譴企 蟆 . 蟆 訖襷 , 螳煙, 焔ル 碁煙れ 螳 譴企れ朱襦 褐 蟆企. 蠍一 豕襯 貅覲伎. 豕 蟆 碁煙れ 襯 譟一 蟆企. HOUR, MINUTE 螳 螻, LEVEL 譟郁唄 一願, SERVER譟郁唄 朱襦 YEAR, MONTH, DAY, LEVEL, SERVER, CLASS, TYPE, HOUR, MINUTE 襦 譟一 覲伎.

ALTER TABLE T_CURRENT_CHARACTER_INFO
DROP CONSTRAINT PK_T_CURRENT_CHARACTER_INFO
GO 

ALTER TABLE T_CURRENT_CHARACTER_INFO
ADD 
  CONSTRAINT PK_T_CURRENT_CHARACTER_INFO 
  PRIMARY KEY(YEAR, MONTH, DAY, LEVEL, SERVER, CLASS, TYPE, HOUR, MINUTE)
GO 

Query 蟆郁骸 れ螻 螳.

企. れ  1, 朱Μ 所鍵  96, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.

伎 豕 . れ襦 企蟆 碁煙るゼ 襴 蟆 蟒 焔 螻手 . 譟壱一磯 螳讌螻 る朱 碁煙る 曙, 螳煙, 一一 企朱 讌覲伎 貊螳 ろ. 蠏碁覩襦 豌伎朱 覲伎 襷 觜 譴企.

3 Index Design 譟一 #

伎蟾讌 襷 碁煙 り襯 覲伎讌襷 襷 れ 碁煙 襷覯 螳 伎朱 碁煙るゼ り. 蟆曙一 SQL覓語 襷 碁煙るゼ 襷れ 蟆曙磯 覲伎. 轟 蠍郁螳 企. 蠍郁覲企 碁煙 り襯 覈詩 蟆 蠍磯蓋 覿譟燕蠍 覓語企.