Hi?u su?t truy v?n SQL c th? gi?m khi tr?ng h?p b? my c s? d? li?u SQL Server c phn m?nh ch? s? cao

D?ch tiu ? D?ch tiu ?
ID c?a bi: 2755960 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

Tm t?t

Hi?u su?t truy v?n SQL c th? gi?m n?u cc danh m?c ?c phn m?nh r?t nhi?u trong tr?ng h?p c?a Microsoft SQL Server b? my c s? d? li?u. B?n c th? s? d?ng ch?c nng h? th?ng sys.dm_db_index_physical_stats ? pht hi?n cc phn m?nh ch? s? cho cc tr?ng h?p sau:
  • M?t ch? s? c? th?
  • T?t c? cc ch? s? trn m?t b?ng
  • Cc ch? s? t?t c? vo m?t ci nh?n ?c l?p m?c ch? d?n
  • T?t c? cc ch? s? trong b? my c s? d? li?u
  • Cc ch? s? t?t c? trong t?t c? cc b? my c s? d? li?u
? xc ?nh cc phn m?nh l hn 30 ph?n trm, ch?y k?ch b?n truy v?n SQL sau y:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,i.name AS IndexName, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID WHERE indexstats.avg_fragmentation_in_percent > 30 AND i.index_id = indexstats.index_id
? xc ?nh cch t?t nh?t ? kh?c ph?c cc phn m?nh, tham kh?o cc h?ng d?n sau:
  • S? d?ng cc thay ?i INDEX xy d?ng l?i v?i (tr?c tuy?n = ON) * l?nh cho cc ?i t?ng m phn m?nh ph?n trm trung b?nh l hn 30 ph?n trm.
  • S? d?ng l?nh Thay ?i ch? s? t? ch?c l?i cho cc ?i t?ng m phn m?nh ph?n trm trung b?nh l gi?a 5-30 ph?n trm.

Thu g?n b?ng nyBung r?ng b?ng ny
gi tr? avg_fragmentation_in_percentKh?c ph?c tuyn b?
> 5% v< ==""> T? CH?C L?I m?c ch? d?n THAY ?I
> 30 %THAY ?I INDEX XY D?NG L?I V?I (TR?C TUY?N = ON) *
Ghi ch:
  • Nh?ng gi tr? ny cung c?p m?t h?ng d?n th ? xc ?nh i?m m t?i , b?n nn chuy?n ?i gi?a cc Thay ?i ch? s? t? ch?c l?i v Thay ?i INDEX xy d?ng l?i. Tuy nhin, cc gi tr? th?c s? c th? thay ?i cc tr?ng h?p. B?n ph?i th? nghi?m ? xc ?nh ng?ng t?t nh?t cho mi tr?ng c?a b?n.
  • M?c r?t th?p c?a phn m?nh (t hn nm ph?n trm) khng nn ?c gi?i quy?t b?ng m?t trong cc l?nh. i?u ny l v? l?i ch t? lo?i b? m?t s? ti?n nh? nh v?y phn m?nh n?ng hn th?ng r?t nhi?u b?i chi ph s?p x?p l?i ho?c xy d?ng l?i cc ch? s?.

Thng tin thm

? bi?t thm chi ti?t v? lm th? no ? t? ch?c l?i v xy d?ng l?i cc ch? s?, i ?n web site c?a Microsoft sau y:
Lm th? no ? t? ch?c l?i v xy d?ng l?i cc ch? s?

B?ng sau y cung c?p cc thng tin thm v? s?n ph?m ho?c cc cng c? t? ?ng ki?m tra t?nh tr?ng ny c?a b?n th? hi?n c?a SQL Server v cc phin b?n c?a s?n ph?m my ch? SQL ch?ng l?i cc quy t?c ?c nh gi.
Thu g?n b?ng nyBung r?ng b?ng ny
Ph?n m?m quy t?cTiu ? quy t?cM t? quy t?cPhin b?n s?n ph?m ch?ng l?i cc quy t?c ?c nh gi
H? th?ng Trung tm c? v?nFragmentations cao SQL Server c th? h? c?p hi?u su?t truy v?nH? th?ng Trung tm c? v?n ki?m tra phn m?nh h?p l? b?ng cch s? d?ng cc avg_fragmentation_in_percent gi tr? sys.dm_db_index_physical_stats cho m?i ch? s? v d? c?a SQL my ch? b? my c s? d? li?u. N?u phn m?nh trung b?nh cho ch? s? ho?c ?ng l hn 5%, c?nh bo ny ?c t?o ra. Th?c hi?n theo cc h?ng d?n t? bi vi?t ny ? gi?i quy?t cc v?n ? c lin quan ?n ch? s? cao fragmentations.SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Thu?c tnh

ID c?a bi: 2755960 - L?n xem xt sau cng: 27 Thang Chin 2012 - Xem xt l?i: 1.0
p d?ng
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Web
T? kha:
kbsurveynew kbhowto kbexpertiseadvanced kbmt KB2755960 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny: 2755960

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com