SQL – Fragen und AntwortenVergrößern von Datenbanken, Verwenden von IFiltern und Herstellen von Remoteverbindungen

Herausgegeben von Matthew Graven

Tipp: Leeren Sie den Cache

Fragen Sie sich manchmal, warum eine gespeicherte Prozedur in der Testumgebung gut läuft, nicht aber nach der Bereitstellung auf dem Produktions-SQL-Server? Dies kann mit der Zwischenspeicherung zusammenhängen. Bevor Sie gespeicherte Prozeduren für Ihre Produktionsumgebung bereitstellen, testen Sie diese in der Testumgebung nach dem Löschen zwischengespeicherter Abfragepläne, um zu wissen, wie Ihre gespeicherte Prozedur in einer „kalten“ Cacheumgebung ausgeführt wird. Folgende nützliche Hinweise sollten Sie sich merken.

Löschen des Prozedurcache auf einem SQL-Server:

DBCC FREEPROCCACHE
Go

Abfrage zum Auflisten aller zwischengespeicherten Pläne:

Select * from sys.dm_exec_cached_plans
Go

F: Ich habe eine Datenbank, auf der tagsüber umfangreicher Datenverkehr stattfindet. Ich möchte nicht die automatische Vergrößerung verwenden, weil diese Timeouts verursachen könnte, falls SQL Server® sie während der Spitzenzeiten durchführt. Ich möchte einen regelmäßig durchgeführten Auftrag implementieren, mit dem die Datenbankdatei um einen bestimmten Prozentsatz des belegten Speicherplatzes erweitert wird. Wie kann ich das erreichen?

A: Eine Dateivergrößerung ist ein intensiver E/A-Vorgang auf dem Datenträger, und wenn SQL Server auf die Erweiterung einer Daten- oder Protokolldatei warten muss, können Sie sicher verstehen, dass dadurch Leistung und Antwortzeiten beeinträchtigt werden. Die Standardschrittweite bei der Vergrößerung beträgt 1 MB für Datendateien und 10 Prozent für Protokolldateien, was für ausgelastete Systeme möglicherweise nicht ausreicht. Außerdem kann die Verwendung der automatischen Vergrößerung zu Datenträgerfragmentierung führen, weil die Daten- oder Protokolldateien auf dem Datenträger nicht zusammenhängend gespeichert werden: Dies bedeutet möglicherweise längere Antwortzeiten als notwendig, da die Daten auf dem Datenträger physisch verstreut sind.

Der Schlüssel zu guter Leistung liegt in der proaktiven Zuweisung von ausreichendem Dateispeicherplatz für Daten- und Protokolldateien im Voraus. Dies erfordert oft eine Trendanalyse und Vergrößerungsprognose und führt zu besserer Leistung, weil die Dateien dann auf dem Datenträger zusammenhängend vorliegen, und es werden die durch automatische Vergrößerung in Spitzenzeiten verursachten E/A-Kosten vermieden. Die automatische Vergrößerung sollte in der Regel aktiviert bleiben, da sonst eine zu 100 % volle Daten- oder Protokolldatei den Zugriff auf die Datenbank gänzlich verhindern würde. Bedenken Sie aber, dass die automatische Vergrößerung als Sicherheitsmaßnahme betrachtet werden sollte und nicht als eine Funktion zur Verwaltung von Datenbanken.

Das Einrichten regelmäßiger Erweiterungen von Datenbankdateien ist zu vermeiden, da dies auch zu nicht zusammenhängenden Dateien auf dem Datenträger führen und somit wiederum die Leistung beeinträchtigen kann. Proaktive Überwachung kann erreicht werden, indem man regelmäßig ein Skript ausführt, das für jede Datenbank den Prozentsatz an freiem Speicherplatz bestimmt (ausgeführt durch einen Auftrag an den SQL-Agent), und dann Maßnahmen ergreift (wie z. B. das Senden einer E-Mail-Warnung über Datenbank-E-Mail). Der Code in der Abbildung bietet ein Beispielskript, aus dem hervorgeht, wie sich der Prozentsatz an freiem Speicherplatz für die aktuelle Datenbank erfassen lässt.

Nach Generierung einer Warnung können Sie mit dem ALTER DATABASE-Befehl ein Skript für einmalige Dateivergrößerung erstellen, und Sie können mithilfe eines Auftrags an den SQL-Agent diese Aktion so planen, dass sie außerhalb der Spitzenzeiten stattfindet. Versuchen Sie, die Datei so weit zu vergrößern, dass sie auf absehbare Zeit ausreichen wird, um künftige kleine inkrementelle Dateierweiterungen zu vermeiden. Es empfiehlt sich auch sicherzustellen, dass auf keiner Datenbank die automatische Verkleinerung aktiviert ist, denn dies könnte dazu führen, dass die Datenbank unnötigerweise immer wieder verkleinert und vergrößert wird.

– Justin Langford

Bestimmung des freien Speicherplatzes in einer Datenbank

-- Script to gather size, free space and 
-- calculate % free space for current 
-- USER database
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)

F: Mein Unternehmen speichert Dateien verschiedener Formate in unserer Datenbank und verwendet dabei varbinary- und image-Spalten. Ich habe gehört, dass es in SQL Server integrierte Funktionen gibt, mit denen ich diese verschiedenen Dateiformate durchsuchen kann. Wie kann ich SQL Server dafür konfigurieren?

A: Diese Funktionalität ist in den Volltextindizierungsdienst integriert. Der Dienst bietet die Flexibilität, IFilter-Schnittstellen zu verwenden, sodass sich Filter entwickeln und laden lassen, die nützliche Informationen aus proprietären Daten extrahieren können. Diese IFilter werden auch für andere Produkte wie z. B. Microsoft® Office SharePoint® Server verwendet, um Informationen zu gecrawlten Dateien zu sammeln.

Ein IFilter wird entweder vom Ersteller des Dateiformats oder von Drittanbietern bereitgestellt. SQL Server enthält bereits einige IFilter, die bei Installation des FulltextService (FTS) geladen werden. Diese enthalten Filter für HTML- und DOC-Dateien. Es können jedoch bei Bedarf weitere IFilter hinzugefügt werden. So sind zum Beispiel Filter für Adobe PDF auf der Adobe-Website zu finden, und ein neues Filterpaket für Erweiterungen von 2007 Office System wurde Ende 2007 veröffentlicht. Beachten Sie, dass Sie die erforderliche Version des IFilters kennen müssen. Beispielsweise funktioniert ein für 32-Bit-Systeme vorgesehener IFilter nicht mit 64-Bit-Installationen von SQL Server.

Nach dem Ausführen des Installationspakets auf dem Client wird der IFilter in der Regel im Ökosystem des Betriebssystems registriert. Mit den im Betriebssystem registrierten Bits müssen Sie einige Schritte durchführen, damit FTS die Filter laden kann. Geben Sie nach dem Starten Ihres Abfragetools die folgenden Befehle ein:

• sp_fulltext_service 'load_os_resources',1. (Mit dieser Anweisung kann FTS die registrierten Bits zur Verarbeitung laden, einschließlich Komponenten wie Worteinheiten- und Wortstammerkennung.)
• sp_fulltext_service 'verify_signature',0. (Hiermit wird SQL Server umgangen, um zu prüfen, ob die verwendeten Filter signiert sind, da viele Anbieter ihre Filter nicht gemäß dem Standard signieren.)
• Starten Sie die SQL Server-Instanz und die Instanz von FTS neu.
• Erstellen Sie Ihren Volltextindex für die Spalten, wobei die Binärspalte den zu crawlenden Inhalt für den IFilter enthält und die Erweiterungsspalte (d. h. die Spalte mit dem Erweiterungstyp wie z. B. DOCX) dem SQL Server dazu dient, den Filter auszuwählen, zu dem der Inhalt umgeleitet werden soll.

Weitere Informationen hierzu finden Sie unter go.microsoft.com/?linkid=7912971.

– Jens Süßmeyer

F: Ich schaffe es nicht, eine Remoteverbindung zu einem SQL-Server herzustellen. Muss ich die Firewall auf meinem Client- oder Servercomputer konfigurieren?

A: Remoteverbindungen zu SQL Server 2005 können aus vielen Gründen fehlschlagen, aber die Firewallkonfiguration ist eines der häufigsten Probleme. Der Blog zu SQL-Protokollen (blogs.msdn.com/sql_protocols) ist eine großartige Quelle für Informationen zur Herstellung einer SQL-Verbindung.

Die Standardinstallation von SQL Server 2005 lässt keine Remoteverbindungen zu. Wählen Sie auf dem Computer, der SQL ausführt, im Startmenü „Microsoft SQL Server 2005“ | „Konfigurationsprogramme“ | „SQL Server-Oberflächenkonfiguration“ aus. Gehen Sie von hier aus zu „Oberflächenkonfiguration für Dienste und Verbindungen“, wählen Sie „Remoteverbindungen“ und anschließend das Optionsfeld „TCP/IP und Named Pipes verwenden“ aus. Sie müssen SQL anschließend neu starten, damit die Änderungen wirksam werden.

Standardmäßig verwendet SQL Server Port 1433. Prüfen Sie mit dem folgenden Telnetbefehl, ob der Port offen ist. Ersetzen Sie dabei <ipaddress> durch die tatsächliche IP-Adresse des Computers, der SQL Server ausführt:

telnet <ipaddress> 1433

Wenn Sie die Antwort „Fehler beim Verbinden“ erhalten, öffnen Sie die Windows®-Firewall, gehen Sie zur Registerkarte „Ausnahmen“, wählen Sie „Anschluss hinzufügen“ aus, und fügen Sie TCP-Port 1433 hinzu. Telnet müsste jetzt funktionieren. (Beachten, dass Telnet unter Windows Vista® nicht standardmäßig installiert ist.)

– Rick Anderson

Unser Dank gilt den folgenden SQL Server-Experten für ihren Beitrag zu diesem Artikel:
Justin Langford arbeitet für Coeo Ltd., einen Systemintegrator und Microsoft Certified Partner mit Sitz in England. Jens Süßmeyer ist Datenbankberater bei Microsoft in Deutschland. Rick Anderson arbeitet in der Entwicklerschulung bei Microsoft. Saleem Hakani ist Senior Database Engineer und Leiter der SQL Server-Community bei Microsoft.

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.