• Artykuł

Microsoft SQL Server 2008

SQL Pytania i Odpowiedzi: Wzrost rozmiaru baz danych, zastosowanie IFilters oraz połączenia zdalne Udostępnij na: Facebook

Edycja Matthew Graven

Opublikowano: 13 sierpnia 2008 | Zaktualizowano: 13 sierpnia 2008

Pyt. Wzrost rozmiaru baz danych  Pyt. Wzrost rozmiaru baz danych
Pyt. Zastosowanie IFilters  Pyt. Zastosowanie IFilters
Pyt. Połączenia zdalne  Pyt. Połączenia zdalne

Pyt. Wzrost rozmiaru baz danych
  Mam bazę danych która jest mocno obciążona w ciągu dnia i nie chcę włączać funkcji autogrow, ponieważ mogłoby to potencjalnie powodować przekraczanie limitów czasu, jeśli SQL Server® zdecyduje się na przeprowadzenie tej operacji w czasie godzin szczytu. Chciałbym zaimplementować okresowe zadanie, które rozszerzałoby plik bazy danych o określony procent wykorzystywanego miejsca. W jaki sposób mogę to zrobić?
Odp.

Zwiększanie rozmiaru pliku to kosztowna dyskowa operacja we/wy i jeśli SQL Server musi oczekiwać na rozszerzenie pliku danych lub dziennika, z pewnością może to w zauważalny sposób wpłynąć na wydajność i czasy odpowiedzi. Domyślny przyrost rozmiaru to 1MB dla plików danych oraz 10 procent dla plików dziennika, co może być niewystarczającym przyrostem w przypadku obciążonych systemów. Ponadto poleganie na funkcji autogrow może prowadzić do fragmentacji dysku, ponieważ pliki danych oraz dziennika nie są ciągłe na dysku. A to oznacza, że czasy odpowiedzi mogą być dłuższe niż to konieczne, gdyż dane będą porozrzucane fizycznie po całym dysku.

Kluczem do dobrej wydajności jest proaktywne alokowanie wystarczającej ilości miejsca w plikach danych oraz plikach dziennika. Często wymaga to dokonania pewnych analiz trendów oraz prognoz wzrostu, jednak owocuje lepszą wydajnością, ponieważ pliki będą ciągłe na dysku i nie będzie konieczne przeprowadzanie przez funkcję autogrow kosztownych operacji we/wy w godzinach szczytu. Zasadniczo funkcja autogrow powinna pozostać włączona, ponieważ zapełnienie pliku danych lub dziennika całkowicie uniemożliwi dostęp do bazy danych. Jednak należy pamiętać, że funkcja autogrow powinna być traktowana jako dodatkowe zabezpieczenie, a nie funkcja zarządzania bazami danych.

Należy unikać planowania regularnych operacji zwiększania rozmiaru pliku bazy danych, ponieważ to również może prowadzić do fragmentacji plików na dysku i tym samym obniżać wydajność. Proaktywne monitorowanie można osiągnąć, regularnie wykonując skrypt w celu sprawdzenia procentowej ilości wolnego miejsca w każdej bazie danych (przy użyciu zadania SQL Agent), a następnie podejmując akcję (taką jak wysłanie alertu e-mail za pomocą funkcji Database Mail). Kod na rysunku prezentuje przykładowy skrypt ilustrujący, w jaki sposób można zebrać informacje o procentowej ilości wolnego miejsca w aktualnej bazie danych.

Po wygenerowaniu alertu można stworzyć skrypt do jednorazowego zwiększania rozmiaru pliku przy użyciu polecenia ALTER DATABASE i wykorzystać zadanie SQL Agent do zaplanowania tej akcji tak, aby została ona przeprowadzona poza godzinami szczytu. Aby uniknąć kolejnych, niewielkich przyrostowych rozszerzeń pliku, należy spróbować zwiększyć plik do rozmiaru, który będzie wystarczający w przewidywalnej przyszłości. Dobrym pomysłem jest również wyłączenie funkcji autoshrink we wszystkich bazach danych, ponieważ może ona powodować występowanie niepotrzebnych cyklów zmniejszania i zwiększania bazy danych.

Wskazówka: Czyszczenie pamięci podręcznej

Czasami zdarza się, że procedura składowana działa prawidłowo w środowisku testowym, ale słabo, gdy zostanie wdrożona na produkcyjnym serwerze SQL Server. Może to wynikać z problemów związanych z pamięcią podręczną. Przed wdrożeniem procedur składowanych w środowisku produkcyjnym należy przetestować procedury w środowisku testowym po oczyszczeniu przechowanych w pamięci podręcznej planów kwerend, aby sprawdzić, jak dana procedura składowana zachowywać się będzie w środowisku z "czystą" pamięcią podręczną. Oto kilka przydatnych sztuczek, które warto znać.

Aby oczyścić pamięć podręczną procedury na serwerze SQL Server:

 
DBCC FREEPROCCACHE

Go
  Kwerenda wyświetlająca listę wszystkich planów przechowywanych w pamięci podręcznej: 
Select * from sys.dm_exec_cached_plans

Go
 

—Justin Langford

 Do początku strony Do początku strony

Pyt. Zastosowanie IFilters
  Moja firma przechowuje w bazie danych różne formaty plików przy użyciu kolumn varbinary oraz image. Słyszałem, że SQL Server posiada zintegrowaną funkcjonalność, która pozwala przeszukiwać różne formaty plików. W jaki sposób mam skonfigurować SQL Server, aby osiągnąć ten cel?
Odp.

Wymagana funkcjonalność jest wbudowana w usługę indeksowania pełnotekstowego. Usługa ta zapewnia elastyczność w zakresie stosowania interfejsów IFilter, umożliwiając rozwijanie i ładowanie filtrów, które potrafią ekstrahować przydatne informacje z danych o zastrzeżonych formatach. Technologia IFilters jest również wykorzystywana w innych produktach, takich jak Microsoft® Office SharePoint® Server, w celu zbierania informacji o przeszukiwanych plikach.

IFilter jest dostarczany przez twórcę formatu pliku lub zewnętrznego dostawcę. SQL Server zawiera pewne interfejsy IFilters, które są ładowane w ramach instalacji usługi FulltextService (FTS). Do interfejsów tych zaliczają się filtry dla plików HTML oraz DOC. Jednak gdy istnieje taka potrzeba, można dodać więcej interfejsów IFilters. Na przykład filtry dla plików Adobe PDF znaleźć można w witrynie firmy Adobe. Natomiast pod koniec 2007 roku opublikowany został nowy pakiet filtrów dla rozszerzeń systemu Office 2007. Jednak trzeba mieć świadomość, która wersja interfejsu IFilter jest potrzebna. Na przykład, IFilter zaprojektowany z myślą o 32-bitowych systemach nie będzie działał w 64-bitowych instalacjach SQL Server.

Po uruchomieniu pakietu instalacyjnego na maszynie klienckiej IFilter będzie zazwyczaj rejestrowany w ekosystemie systemu operacyjnego. Gdy komponenty są już zarejestrowane w systemie operacyjnym należy wykonać kilka czynności, aby umożliwić usłudze FTS ładowanie filtrów. Po uruchomieniu narzędzia wykonywania kwerendy należy wywołać następujące polecenia:

  • sp_fulltext_service 'load_os_resources',1. (instrukcja ta umożliwi usłudze FTS załadowanie na potrzeby przetwarzania zarejestrowanych komponentów, na przykład programów szukających rdzeni lub narzędzi dzielących wyrazy).
  • sp_fulltext_service 'verify_signature',0. (ominie SQL Server, aby sprawdzić, czy wykorzystywane filtry są podpisane, ponieważ wielu dostawców nie podpisuje swoich filtrów zgodnie ze standardem).
  • Zrestartować instancję SQL Server oraz instancję usługi FTS.
  • Stworzyć indeks pełnotekstowy na kolumnach, określając kolumnę binarną posiadającą zawartość do przeszukiwania przez IFilter oraz kolumnę rozszerzenia (czyli kolumnę z typem rozszerzenia np. DOCX), aby SQL Server mógł zadecydować, do którego filtra ma przekierować zawartość.

Więcej informacji znaleźć można pod adresem go.microsoft.com/?linkid=7912971.

Określanie ilości wolnego miejsca w bazie danych 

-- Skrypt do określania rozmiaru, ilości wolnego miejsca 

-- oraz wyliczania % wolnego miejsca w aktualnej 

-- bazie danych

DECLARE @size DEC(15,2)

DECLARE @free DEC(15,2)

DECLARE @result DEC(15,2)



SELECT @size = SUM(size)*1.0/128

FROM sys.database_files



SELECT @free = 

(SUM(unallocated_extent_page_count)*1.0/128)

FROM sys.dm_db_file_space_usage



PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)

PRINT 'Free Space ' + 

CONVERT(VARCHAR(15), @free)



SELECT @result = (@free/@size)*100



PRINT '% Free Space ' + 

CONVERT(VARCHAR(15), @result)
 

—Jens Suessmeyer

 Do początku strony Do początku strony

Pyt. Połączenia zdalne
  Nie mogę połączyć się ze zdalnym serwerem SQL Server. Czy muszę skonfigurować zaporę sieciową na maszynie klienckiej czy na serwerze?
Odp.

Zdalne połączenia z serwerem SQL Server 2005 mogą kończyć się niepowodzeniem z wielu powodów, ale konfiguracja zapory sieciowej jest jednym z najczęstszych problemów. Blog SQL Protocols (blogs.msdn.com/sql_protocols) stanowi świetne źródło informacji dotyczących nawiązywania połączeń SQL.

Domyślna instalacja SQL Server 2005 nie umożliwia zdalnych połączeń. Na maszynie, na której uruchomiony jest serwer SQL, w menu Start należy wybrać opcję Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration. Z tego miejsca należy przejść do Surface Area Configuration for Services and Connections, wybrać opcję Remote Connections oraz zaznaczyć pole wyboru "Using both TCP/IP and named pipes". Następnie trzeba zrestartować serwer SQL, aby zmiany zostały wprowadzone.

Domyślnie SQL Server wykorzystuje port 1433. Aby upewnić się, że port jest otwarty, można użyć następującego polecenia telnet, zastępując właściwym adresem IP maszyny, na której uruchomiony jest SQL Server:

 
telnet <adresip> 1433
 

W przypadku otrzymania odpowiedzi o niepomyślnym połączeniu należy otworzyć Zaporę systemu Windows (Windows® Firewall), przejść do karty Wyjątki (Exceptions), wybrać opcję Dodaj port (Add Port) i dodać port TCP 1433. Tym razem wykonanie polecenia telnet powinno zakończyć się sukcesem (należy podkreślić, że program Telnet nie jest domyślnie zainstalowany w systemie Windows Vista®).

—Rick Anderson

 Do początku strony Do początku strony

Microsoft SQL Server 2008