Победа над фрагментацията, прозорци него реше
В този случай, разбира се, на твърдия диск е изключително активни през наблюдавания период, който забавя всички други операции. Тъй ADO.NET е малко агресивен алгоритъм добавяне на съединенията на басейна на връзка, дори в малък период от време на процеса на употреба съдържа множество връзки. Голям брой връзки към един и същ източник на данни обикновено се увеличава вероятността от конфликт брави за споделените данни, което от своя страна забавя реакцията на SQL Server. По пътя по време на пиковите часове, когато една кола, движейки се бавно, забавяне на движението на всички останали, леко увеличение на времето за реакция на сървъра на базата данни може да окаже ефект на доминото на забавянето в зависимост от неговата среда заявление.
Твърд диск като спирачка
Важно е да се помни, че всеки път, I / O процес включва забавяне по време на който на контролера е в очакване на диска се върти под главата за четене / запис. Ето защо, винаги четете осемте страници е необходимо повече от една страница със същия обем. В допълнение, I / O компоненти за ефективен достъп не могат да организират четене, тъй като тя не е в състояние да се предскаже какви въпроси да очакваме в бъдеще. оптимизация диск се случва, когато се комбинира съхранение на данните в големи парчета с възможно най-ниската диск достъп. доклад Filemon казва, че I / O компонент на една маса в прочетете на страница 2.5 GB в даден момент, това означава, че нещо възпрепятстван исканията на групата на двигателя в последователност. Така че трябва да разгледаме някои от масите и да разберете причината.
Полезен инструмент за идентифициране на степента на фрагментация в таблицата - на екип SQL Server DBCC SHOWCONTIG. За съжаление, стартирането си влошават техническите показатели, които просто трябва да се подобри, тъй като екипът създава голям брой операции I / O. С бутони за бърз достъп въздействие върху работата на сървъра е по-малко, отколкото ако изпълните командата без него. Но дори и изпълнение DBCC SHOWCONTIG FAST отбор забавя системата е достатъчно силна, ако той вече е претоварен. Най-добре е, когато се използва база данни Съвместимост Checker (DBCC) - разбира се, ако има време - извършване на пълно сканиране и инсталиране TABLERESULTS, за да сканирате една маса в изходния файл, и после запишете резултатите.
Аз писах на езика на T-SQL запомнени процедури uspBuildFraglist, текстът на която е показана на Обява 1. Трябва да се автоматизира командата DBCC SHOWCONTIG С TABLERESULTS. UspBuildFraglist примки през списъка с таблици в базата данни и за всяка таблица започва DBCC SHOWCONTIG с резултатите на маса, ALL_INDEXES, който показва информация за индекса на фрагментация на данните и каза на маса. Резултатите се съхраняват в първия временна таблица, след което се премества в постоянна маса в базата данни, който е проектиран да бъде държан от данните, получени в подкрепа на административните процеси, като този.
Обявата 1. съхранява процедурата uspBuildFraglist
Въпреки uspBuildFraglist съхранена процедура изпълнява една проста задача, тя ви позволява да проверите фрагментация, без компромис с качеството. Тази процедура води до по-точни резултати, когато се показва след всяко произведение, което ще се отрази на разпространението на файлове, като например работа на сгъстен база данни или почистване на данни.
Тълкуване на резултатите от DBCC
С цел да се тълкува изхода на командата данни DBCC, произведени от uspBuildFraglist, първо трябва да се определи дали проверяваният таблицата струпани индекс. Тъй като струпани индекс физически нарежда на данните на диска според индексирана поле, фрагментация обикновено се случва, когато данните се изтриват от масата, а след това е имало не е поставена никаква информация, която да запълни празното място. Обикновено причината за фрагментацията на този вид в присъствието на индекс клъстер монотонно с увеличаване на първичен ключ, като уникалната терена. За съжаление, стандартна основна ключ е да се използва струпани индекс, както и много дизайнери на базата данни трябва да използват уникална област, както на първичния ключ.
Друг често срещан метод за фрагментиране като скупчени, не-клъстерирани индекси така е разпределението на данни след известно време. В OLTP системи се добавят обикновено нови линии почти едновременно и в малки блокове. Ето защо, във всички таблици в една група от файлове ще бъдат поставени в един или два блока на допълнителни данни по едно и също време. Това означава, че повече блокове от данни могат да бъдат разпръснати из файла. Ако нови линии се добавят със същата скорост по всички маси, разпределението ще бъде достатъчно гладка. Ако една и съща маса се добавя повече линии в сравнение с другите, а след това допълнителните редове в таблицата могат да бъдат групирани заедно, а след това те ще бъдат само на непрекъснатостта на малките острови в морето, разделени с линии. SQL Server не се опитва да постави редовете на една маса заедно. Ето защо, с растежа на OLTP системи постоянно се фрагментирана.
Друга прогноза използване DBCCSHOWCONTIG използва за проверка на средната плътност на запълване на страницата, която показва как средната страница, пълна с данни. Въпреки че, може би, в таблицата не е фрагментация на страници, защото всички страници наистина са непрекъснати, а на страницата може да е празни места, тъй като на страниците на изтриване, модернизация и разделяне. Тъй като SQL Server винаги чете данните от най-малко на цяла страница, маса за сканиране или листа за сканиране на индекса винаги ще се нуждаят от по-голям брой страници, прочетени от броя байтове, използвани, разделен на 8192, ако страницата съдържа празни пространства. Четенето на тези допълнителни страници води до високо дискова активност и защото таблицата е голяма и има по-голямо влияние върху прочетете изпълнение, особено ако масата е сканиран.
Разпокъсаността на файлове на базата данни
Ако за следене на ефективността показва прекалено голям брой операции I / O, какво означава това? И ако Filemon показва най-малко 65,536 байта при извършване на I / O? Това означава, че на файла с базата данни е фрагментирана. Монитор на производителността показва отчет, физически показатели диск, независимо от факта, че Filemon описва I / O на ниво файлова система. За изтегляне на страницата с данните, операционната система Windows, за да намерите компенсира за установяване на физическото местоположение на байта на диск файла. NTFS се опитва да намали фрагментиране на диска, за определяне на местоположението на файла в най-голямото парче свободно място на диска, без всякакъв опит да се оптимизира този режим. Ако базата данни се разширява и свива редовно (т.е. използват DBCC отбор SHRINKDB), след като е възможно част от файловете на база данни, разпределени по целия диск, и това е малко вероятно, че тези части са подредени, а мениджърът на съхранение подрежда страниците на брой.
Може да се използва за приложение за дефрагментиране на диск, за да се чете файла от първия до последния байт на дисковия контролер може да се прилага едновременно. Но това е само частично решение, тъй като заявлението за дефрагментиране обикновено не се движи парчета от данни, така че те са непрекъснато. Дори и една малка разлика между поставянето на данни от диска води до очакването на момента, когато устройството трябва да се обърне четене / запис глава. По-голям брой прекъсвания винаги ще доведе до по-бавно действие в сравнение с възможното максимално.
Решението за новата база данни е да осигури оглед на растеж достатъчно пространство, за да се създаде база данни. Ако дискът е новоформатирания, NTFS ще осигури всичко в едно непрекъснато диск област. След това можете да използвате командата DBCC и възстановяване на индексите се сведе до минимум фрагментация.
По-ефективно решение на съществуващата база данни, както следва: извършване на пълно резервно копие на базата данни, изтриване на данни (с премахването на файлове), за да дефрагментирате диска, и след това да възстановите базата данни. Процесът на възстановяване ще доведе до Windows да се разпределят от дисковото пространство на разположение на най-големите парчета на свободно дисково пространство, така че страниците ще бъдат директно от диска, вероятно физически непрекъснато. В резултат на това I / O съставка следва да организира искания за страници по-ефективно, защото мениджърът може да получите достъп до големи данни фрагменти в един I / O операция.
Ключовите въпроси, които открих: голяма маса и фрагментиран файл с база данни, както и неефективните запълване плътност страници. Смятам, че всички три въпроса, които трябва да бъдат разгледани заедно, за да се реши проблема с производителност на база данни. Първият от тях е най-голямата, най-пълен, използвани за решаване на масата трябва да индекс външния ключ. Този конкретен проблем наистина не е бил причинен от раздробяването на, и се свързва с сканирането на големината на таблицата от 2.5 GB с външен ключ по време на надграждане на масата на първичен ключ поле. Това сканиране е причината за голям брой диск I / O, така че е невъзможно да се установи дали е имало и други проблеми с диска. Добавянето индекс драстично намалява докато повишаване и I / O операции диск време до приблизително 80% с операции за модернизация. За да се реши оставащите два проблема, аз написах запомнена процедура uspDefragTables, които считаме-долу по-подробно.
Вторият проблем, който намерих е, че таблицата е разстроен, т.е., неговите данни са "подредени" в реда, в който те са поставени. Това само по себе си не е страшно; страници маса бяха само малко по-фрагментирани. Тъй като данните, е било получено от около 75 до 80% от общия брой на данни въведени всички таблиците в базата данни, съхранявани в близост един до друг. Имайте предвид обаче, че данните от логиката дефрагментиране DBCC SHOWCONTIG неприложими към разстроен данни; Следователно тези данни с 0% фрагментация все още може да бъде отрицателен фактор във формирането на данни заявки на приложения.
Третият проблем, който прави организация на данните по-малко ефективен - когато DBCC SHOWCONTIG команда показва, че плътността на страница пълнене по-малко от 50%. Разбираемо е, че масата за съхранение се използва повече от две страници. Твърде много полупразни страници, разпръснати върху файла с базата данни е в действителност по-слабо представяне.
Съхранената процедура Обява 2 uspDefragTables реши проблема с плътността на пълнене страници и организация на данните. повтаря, Редът над всички редове от таблицата, която е резултат от DBCC SHOWCONTIG команден изход от съхранена процедура uspBuild-Fraglist, и това се търси в таблицата, която да надвишава нивото на фрагментация, каза в параметър @MaxFrag, както е показано на фрагмент Листването 2.
Обявата 2. съхраняват процедура uspDefragTables
процедура UspDefragTables изпълнява DBCC DBREINDEX за всеки индекс, който има логическа фрагментация, или фрагментация на данните, или ако средната плътност на запълване на страницата е по-малко от 100- @ MaxFrag. За скупчени индекси, тази команда реорганизира цялата таблица. За не-клъстерирани индекси го реорганизира индекса на нивото на листата.
Имайте предвид, че uspDefragTables процедурните определено игнорира данните неподредени (indexID = 0), тъй като командни такива данни DBCC DBREINDEX не работи. Вместо това, DBCC DBREINDEX може да се defragmented за създаване на не-клъстерирани индекси. За да се подобри организацията изискват различни методи, които често трябва да се прилага ръчно.
Оказа се, че основната причина, бях принуден да се събуди за факта, че работата не се извършва в подкрепа на няколко седмици. Бях зает със задачи, по-висок приоритет, а аз нямах време да разберете защо работата не се извършва в подкрепа. По това време аз вярвах, че ако не не е проблем, защото ми се струваше, до базата данни е да се фрагментира, ще отнеме месеци. Тази сутрин, разбрах, че това е много погрешно, поне в случая на някои променливите и претъпканите маси.
След като се добавя код, за да предотврати сканирането на маса, и построен с клъстери индекс за организиране на данни по-ефективно (които също са били defragmented и имаше не-клъстерирани индекси) две седмици гледах нивото на фрагментация. Това са минали само няколко дни, както и повечето големи таблици вече са започнали да показват признаци на фрагментиране отново, така че до края на седмицата работата значително се забави с тях. Стана ясно, че раздробяването на сянка ще стан, някога и само поддържане на постоянна бдителност, ние не може да получи в безнадеждна ситуация.
Moris Lyuis ([email protected]) - администратор на база данни за фирма, Digichart, доставчик на услуги заявки за здраве
Сподели снимки с приятели и колеги