Management Statistics

  • Published on
    13-Jul-2015

  • View
    141

  • Download
    0

Embed Size (px)

Transcript

PowerPoint Presentation

MANAGEMENT STATISTICS

NHM 8

1164002 V c Anh1164048 L Anh Khoa1164158 H Nguyn Thy UynMANAGEMENT STATISTICS

I. Statistics trong SQL server 1. nh nghaII. Statistics 1. To Statistics (Statistics c to khi no) 2. To, xa, cp nht, lit k StatisticsIII. Index 1. Clustered Index 2. Non-Clustered IndexIV. Cch Statistics c s dng

I. Statistics trong SQL Server1. nh ngha- Statistics (thng k): c lp trn mt ct ca mt bng- Qu trnh sp xp (tng hoc gim), gom nhm cc c cng gi tr (bao gm m chng)- Statistics gip SQL la chn phng n thc hin truy vn ti u nhtHeaderDensity VectorHistogram

Tn StatisticsTGian UpdateS dng trong bngS dng c thng kS gi trI. Statistics trong SQL Server1. nh ngha- Statistics (thng k): c lp trn mt ct ca mt bng- Qu trnh sp xp (tng hoc gim), gom nhm cc c cng gi tr (bao gm m chng)- Statistics gip SQL la chn phng n thc hin truy vn ti u nht

Tn s lp li ca mi gi trCt lp thng kI. Statistics trong SQL Server1. nh ngha- Statistics (thng k): c lp trn mt ct ca mt bng- Qu trnh sp xp (tng hoc gim), gom nhm cc c cng gi tr (bao gm m chng)- Statistics gip SQL la chn phng n thc hin truy vn ti u nht

Gi tr khaS dng nm trong khong gi trS dng cng gi trS gi trI. Statistics trong SQL Server1. nh ngha- Statistics (thng k): c lp trn mt ct ca mt bng- Qu trnh sp xp (tng hoc gim), gom nhm cc c cng gi tr (bao gm m chng)- Statistics gip SQL la chn phng n thc hin truy vn ti u nhtII. Statistics1. To Statistic (Statistics c to ra khi no ???)* Mt Statistic c to ng thi khi Index c khi to* Cu truy vn t pht sinh Statistic* To th cng bng Transct-SQL- Ty chn Auto Create Statistics ca Database l true- Khi Statistic khng thng tin p ng qu trnh truy vnCREATE STATISTICS STATS_SINHVIEN_MALOPQLON SINHVIEN(MaLopQL)CREATE STATISTICS [Tn Statistic]ON [Tn bng](Tn ct)DemoDemoDemoII. Statistics* To Statistics* Cp nht StatisticsCREATE STATISTICS STA_SV_TENSVON SINHVIEN(TenSV)* Xem thng tin StatisticsDBCC SHOW_STATISTICS (SINHVIEN,STA_SV_TENSV)* Xa StatisticsDROP STATISTICS SINHVIEN.STA_SV_TENSVUPDATE STATISTICS SINHVIEN- Khi no Statistics c cp nht (lm r phn sau)1. To, xa, cp nht Statistics* Lit k cc StatisticsEXEC sp_helpstats SINHVIEN, 'ALL'- Index l mt cu trc d liu c lu tr trn cng tng ng vi table- Index cha cc key c xy dng t mt hay nhiu ct trong bng c lu thnh cu trc dng B-Tree cho php vic tm kim mt hay tp hp cc kha nhanh chngIII. Index1. Clustered Index* Clustered Index- Clustered Index sp xp v lu tr d liu tng vt l (ngay trn bng)- Clustered Index c to mc nh cho ct kha chnh trong bngCLUSTERED INDEX PK_SINHVIENON SINHVIEN(MaSV)* Non-Clustered IndexDemo

CREATE CLUSTERED INDEX [IndexName]ON [TableName]([ColumnName])9IV. Cch Statistics c s dngDatabaseName: CreditTable: member(member_no,lastname,firstname,middleinitial,city,phone_no)* Truy vnSELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'Kim%'+ Ln 1:SELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'Ki%'SELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'K%'+ Ln 2:+ Ln 3:? Statistics c s dng nh th no ?IV. Cch Statistics c s dngDatabaseName: CreditTable: member(member_no,lastname,firstname,middleinitial,city,phone_no)* To IndexCREATE INDEX Member_LNameON member(Lastname, FirstName, MiddleInitial)* Truy vnSELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'Kim%'+ Cu truy vn pht sinh StatisticsStatisticsName: _WA_Sys_00000003_0CBAE877+ Thc thi truy vnNonclustered Index Scan trn Index Member_LNameKhng c StatisticsC Statistic trn FirstName+ Thc thi truy vnClustered Index Scan trn Index member_ident*member_ident: Clustered Index mc nh ca Primary Key Theo thng k c 1.96078 dng tr v(Estimated Number of Rows)Logical reads: 144Logical reads: 46DemoIV. Cch Statistics c s dngDatabaseName: CreditTable: member(member_no,lastname,firstname,middleinitial,city,phone_no)* Truy vnSELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'Ki%'SELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS m WITH(INDEX(member_ident))WHERE m.FirstName LIKE 'Ki%'+ Thc thi truy vnClustered Index Scan trn Index member_ident+ Thc thi truy vnNonclustered Index Scan trn Index Member_LNameLogical reads: 82Logical reads: 144Estimated Number of Rows : 19.6078 dngEstimated Number of Rows : 19.6078 dngChi ph thc hin truy vnThpCaoDemo13IV. Cch Statistics c s dngDatabaseName: CreditTable: member(member_no,lastname,firstname,middleinitial,city,phone_no)* Truy vnSELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'K%'SELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS m WITH(INDEX(Member_LName))WHERE m.FirstName LIKE 'K%'+ Thc thi truy vnNonclustered Index Scan trn Index Member_LName+ Thc thi truy vnClustered Index Scan trn Index member_identLogical reads: 144Logical reads: 935Estimated Number of Rows : 431,373 dngEstimated Number of Rows : 431,373 dngChi ph thc hin truy vnThpCaoDemoIV. Cch Statistics c s dngDatabaseName: CreditTable: member(member_no,lastname,firstname,middleinitial,city,phone_no)? Khi no Statistics c cp nht ?* Update d liuUPDATE memberSET firstname = 'Kimberly'WHERE member_no >= 1 AND member_no SQL chn phng n truy vn khng ti uDemoUPDATE memberSET firstname = 'Kimberly'WHERE member_no > 1000 AND member_no 20% tng s dng* Truy vnSELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWITH (INDEX (member_ident))WHERE m.FirstName LIKE 'Kim%'SELECT m.LastName, m.FirstName, m.MiddleInitial, m.Phone_no, m.CityFROM dbo.Member AS mWHERE m.FirstName LIKE 'Kim%'+ Thc thi truy vnClustered Index Scan trn Index member_ident+ Thc thi truy vnClustered Index Scan trn Index member_identLogical reads: 144Logical reads: 144Estimated Number of Rows : 2509,8 dngActual Number of Rows: 2501Statistics c cp nhtDemoSUMMARY- Statistics gip SQL la chn phng n thc hin truy vn ti u- Ty chn Auto Create Statistics cho db l true- Khng nht thit to Index cho thuc tnh khng thng xuyn truy vn - Cn c k hoch truy vn r rng qun l Statistics- Update Statistics bng Transact-SQL m bo truy vn lun ti uTHAM KHOtechnet.microsoft.commsdn.microsoft.comsqlskills.comstackoverflow.comsqlviet.combigdata.com.vnVideo Demo: http://social.technet.microsoft.com/Search/en-US/sqlserver?query=statistics&ac=4#refinementChanges=300&pageNumber=1&showMore=trueSample Database and Script: http://www.sqlskills.com/sql-server-resources/sql-server-demos/18MANAGEMENT STATISTICSNHM 8

1164002 V c Anh1164048 L Anh Khoa1164158 H Nguyn Thy UynTHANKS FORYOUR ATTENTION