Archive for the ‘SQL Server’ Category

How to Use Parameters Like a Pro and Boost Performance by Guy Glantser

10. Mai 2017

Thema

Parameters are a fundamental part of T-SQL programming, whether they are used in stored procedures, in dynamic statements or in ad-hoc queries. Although widely used, most people aren’t aware of the crucial influence they have on query performance. In fact, wrong use of parameters is one of the common reasons for poor application performance.

Sprecher

Guy Glantser

Links

http://www.madeiradata.com/tag/parameterization-series

Notizen

Abfrage von PlanCacheDaten:

SELECT
    BatchText            = BatchTexts.text ,
    QueryPlan            = BatchPlans.query_plan ,
    ExecutionCount        = CachedPlans.usecounts ,
    ObjectType            = CachedPlans.objtype ,
    Size_KB                = CachedPlans.size_in_bytes / 1024
FROM
    sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
    sys.dm_exec_query_plan (plan_handle) AS BatchPlans
CROSS APPLY
    sys.dm_exec_sql_text (plan_handle) AS BatchTexts;
Advertisements

SQL Server: Interessanter Post über die Aktualisierung großer Datenmengen

6. September 2013

Panther erläutert unter Tipps & Tricks: Große Datenmengen in einer Produktionsumgebung aktualisieren, wie man ein Update auf eine größere Tabelle performant durchführt.

SQL Server 2008 R2 Reporting Services in ASP.Net 4.0 Webseite einbinden

13. Juni 2012

Die letzten Stunden habe ich damit verbracht, auf einem SQL Server 2008 R2 in den Reporting Services einen Bericht zu erstellen,
der auf eine Datenbank zugreift,
der eine Shared DataSource dazu verwendet,
der ein Shared DataSet verwendet,
der per Parameter über das DataSet gefilter wird,
der alle fünf Minuten als SnapShot historisiert wird,
und von dem die letzten 10 SnapShots gehalten werden.

Diesen Report wollte ich in einer ASP.Net Seite auf zwei Wegen bekommen:
1.) Anzeige des Reports im ReportViewer Control
2.) Report als PDF-Datei zum Download

1.) Anzeige des Reports im ReportViewer Control

Diese Variante war relativ einfach – Control auf die Seite ziehen und die Properties für den Report und den ReportServer angeben:

<ServerReport
 ReportPath="/ReportProject1/Report2"
 ReportServerUrl="http://meinServer/reportserver" />

Der ReportViewer zeigt so konfiguriert immer die Daten des aktuellsten SnapShot an.

2.) Report als PDF-Datei zum Download

Zunächst braucht man den NameSpace Microsoft.Reporting.WebForms bzw. in der web.config den folgenden Verweis (Version 10 = SSRS 2008 R2 ):

<add assembly=“Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A“/>

Im Code sieht das dann etwa so aus:

Dim bytes As Byte()
'// Instanz des ReportViewer-Steuerelements
Using rv = New ReportViewer
'// Festlegen des Verarbeitungsmodus des ReportViewer-Steuerelements.
 rv.ProcessingMode = ProcessingMode.Remote
'// Berichtsserver festlegen
 rv.ServerReport.ReportServerUrl = New Uri("http://meinServer/reportserver")
'// Festlegen des Pfades zum Bericht
 rv.ServerReport.ReportPath = "/ReportProject1/Report1"
'// Parameter aus der am Bericht hinterlegten Parameterliste auswählen
 Dim pars = rv.ServerReport.GetParameters
 Dim par0 = pars(0)
 Dim p = New ReportParameter(name:=par0.Name, value:=par0.ValidValues(0).Value) 'rv.ServerReport.ReportParameter()
 rv.ServerReport.SetParameters(p)
'// Verarbeitet den Bericht und rendert ihn im angegebenen Format und speichert diesen in ein byteArray
 bytes = rv.ServerReport.Render(format:="PDF", deviceInfo:=Nothing, _
 mimeType:=mimeType, encoding:=encoding, fileNameExtension:=extension, _
 streams:=streamIDs, warnings:=warnings)
End Using
'// Schreibt das Resultat in ein PDF
 Using fs = IO.File.Create(IO.Path.Combine(Server.MapPath("~/."), "result.pdf"), bytes.Length)
 fs.Write(bytes, 0, bytes.Length)
 fs.Close()
 End Using

Damit man auf Daten aus einem bestimmten SnapShot zugreifen kann, habe ich den SSRS WebService / SOAP Client verwendet:

Zunächst also eine ServiceReference setzen auf :

http://meinServer:80/ReportServer/ReportService2010.asmx?wsdl

Dann die paar folgenden Zeilen nach der Parameterübergabe und vor dem Rendern des Berichts im obigen Code eingefügt:

    Dim rs2010 As New ReportingService2010.ReportingService2010SoapClient
    Dim tuh2010 As New ReportingService2010.TrustedUserHeader
    Dim itemHist2010() As ReportingService2010.ItemHistorySnapshot = Nothing
    Dim sih2010 As ReportingService2010.ServerInfoHeader = rs2010.ListItemHistory( _
        TrustedUserHeader:=tuh2010 _
        , ItemPath:=rv.ServerReport.ReportPath _
        , ItemHistory:=itemHist2010 _
        )
    '// einfach mal den 5. Snapshot herausgepickt
    Dim histId = (From o In itemHist2010 Order By o.CreationDate Select o.HistoryID)(5)

    rv.ServerReport.HistoryId = histId

Dann gab’s ein paar Fehlerchen bzgl. irgendwelcher Anmeldewurschteleien. Diese konnte ich durch folgende Maßnahmen vermeinden:

In der web.config:

<!--<security mode="None">-->
<security mode="TransportCredentialOnly">
  <!--<transport clientCredentialType="None" proxyCredentialType="None" realm="" />-->
  <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />

Und im Code wie folgt entsprechende Benutzerdaten angeben:

    Dim cred As New System.Net.NetworkCredential(userName:="ich", password:="wirdnichtverraten", domain:="meinreich")
    rs2010.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation
    rs2010.ClientCredentials.Windows.ClientCredential = cred

Damit funktionierte dann alles wie gewünscht.

Ganz brauchbar dabei fand ich die folgenden Links:

SSRS Part 5 Demo2 – Configuring a Report Snapshot

Report Server Web Service

Developer’s Guide: Tutorials (Reporting Services)

Creating the Web Service Proxy

Viewing Report History Snapshot …

SQL Server 2005 ausgesperrt

29. Februar 2012

Die Tage hatte ein Kollege ein „nettes“ Problem mit dem SQL Server 2005 Express Edition auf Win2k8 x64, an dem wir einigermaßen zu knabbern hatten:

Die Datenbank hatte die max. Größe von 4 GB erreicht und wollte nun nicht mehr so recht. Außerdem war die SQL Server Authentifizierung nicht aktiv und das sa-Konto (einziges Konto in der sysadmin-Rolle!!!) war deaktiviert.

Zum Glück konnten wir mit einem Domänen-/Windows-Benutzer einiges an Daten löschen, so dass wenigstens mal die DB wieder arbeitete.

Dann wollten wir den SQL Server im Einzelbenutzer-Modus / Single-User-Mode starten, bekamen aber den Fehler, dass für die TempDB zu wenig Platz sei.

Also erstmal Datenbank verkleinern und den Speicherplatz in den Files freigeben – geht ja relativ einfach über das SSMS.

Bei der Gelegenheit stellten wir auch wieder über das SSMS über die Server Properties in der Rubrik Security den Authentifizierungsmodus wieder um, so dass auch SQL Server Authentifizierung wieder geht.

Dann also auf dem Server direkt als lokaler Administrator angemeldet den SQL Server im Single-User-Mode gestartet:
Mit Kommandozeile ins Verzeichnis
c:\program files (x86)\Mircrosoft SQL Server\MSSQL.1\MSSQL\Binn
und dann:

sqlservr-m -e -s instanzname

Wenn alles klappt, startet der SQL Server im Single-User-Mode

Dann neue Kommandozeile im selben Verzeichnis:
osql -E -S .\instanzname

Dann zunächst das sa-Konto wie folgt wieder aktivieren:

ALTER LOGIN sa ENABLE
GO

Dann sicherheitshalber das Password des sa neu setzen:

ALTER LOGIN sa WITH PASSWORD='super-passwort'
GO
quit

Dann SQL Server durch Schließen der ersten Kommandozeile beenden und dann funktionierte alles wieder.

Verwalten von SQL Server mit der richtlinienbasierten Verwaltung

1. Dezember 2011

Man kennt das ja: DB angelegt, ein paar Kollegen halten sich nicht an die Namenskonvention für die DB-Objekte.
Darum habe ich heute ein paar Minuten Zeit investiert und mich mal mit den Richtlinien/Policies des SQL Server beschäftigt.

Dabei sind mir v.a. folgende Artikel untergekommen:

Mit der schönen Abfrage …

SELECT facet.name as ‚Facet Name‘,
OnDemand = CASE facet.execution_mode & 4
WHEN 4 THEN ‚On Demand; On Schedule‘
END,
OnChangeLogOnly = CASE facet.execution_mode & 2
WHEN 2 THEN ‚On Change, Log Only‘
END,
OnChangePrevent = CASE facet.execution_mode & 1
WHEN 1 THEN ‚On Change, Prevent‘
END
FROM msdb.dbo.syspolicy_management_facets facet

… lässt sich schön Abfragen, welche Facets (ich würde das mal als ZielObjekte bezeichnen) bestimmte Evaluation Modi unterstützen.

In meinem Fall wollte ich z.B. erzwingen, dass Tabellennamen in der Datenbank DB1 immer mit tDb1 beginnen. Leider scheint genau dieses über die Policies nicht unterstützt zu sein, da dafür der Evalutation Mode On Change:  prevent verwendet können werden müßte.

Es kann vermutlich nur über DDL Trigger realisiert werden.

Datumstypen im SQL Server

8. November 2011

Der Post mit dem bescheidenen Artikel Der ultimative Guide für die Datetime Datentypen ist zwar schon ein paar Tage alt, aber trotzdem sehr interessant und einigermaßen ausführlich.

SQL Server – Anmeldung wenn nichts mehr geht

26. Oktober 2011

Falls der SQL Server nicht mehr reagiert (z.B. wenn alle Worker Threads belegt bzw. blockiert sind), dann kann man sich mit der Dedicated Admin Connection (DAC) behelfen.
Mehr dazu und auch was man unternehmen kann, um die Maschine zu retten im Post Troubleshooting THREADPOOL Waits von Klaus.
Weitere Details zur DAC in der MSDN Library:

 

Wichtig ist, dass man sich im SSMS nicht mit dem Explorer verbindet, sondern ein neues Query öffnet und damit die Verbindung zur DB herstellt (Wichtig: dem Instanznamen admin: voranstellen und einen Sysadmin verwenden).

Das Öffnen einer DAC per SQLCMD direct auf dem DB-Server sieht wie folgt aus:

sqlcmd -Sadmin:localhost -U sa -P password -d master

Der SQL Browser lief bei mir übrigens dabei nicht.

SQL Server Plan Cache

21. Oktober 2011

Dass SQL Server (in meinem Fall 2008 R2) sich Ausführungspläne im (endlichen) Speicher merkt, ist wohl den meisten DB-Administratoren und -Entwicklern bekannt.
Ebenso leuchtet ein, dass Adhoc Abfragen diesen Cache vollmüllen und dadurch die Leistung des Systems nicht gerade verbessert wird.
Wenn dann noch die ungünstige Situation eintritt, dass man häufig direkt auf dem ProduktivSystem entwickeln muss, dann ist spätestens das ein Grund, sich mal mit dem Thema zu beschäftigen.

Das Blog von Pinal Dave war mit dem Post SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script Ausgangspunkt meiner Recherche.

Zunächst einmal kann man sich mit folgender Abfrage einmal alle gespeicherten Pläne im Cache anzeigen lassen, die nur einmal benutzt wurden:

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;
GO

Einzelne Pläne können mit dem Verwaltungsbefehl DBCC FREEPROCCACHE aus dem Cache entfernt werden, indem man das plan_handle aus der obigen Abfrage dem Befehl in Klammern hinten anstellt, wie im folgenden Beispiel:

DBCC FREEPROCCACHE(0x06000100804AD30040219A86000000000000000000000000)
WITH NO_INFOMSGS;
GO

Wenn ansonsten kein plan_handle angegeben wird, werden alle Pläne aller DBs auf dem Server gelöscht.
Das ist auf dem Produktivserver nicht unbedingt best practice.

Da das Finden und Löschen eines Plans die Entwicklerproduktivität negativ beeinflusst, ist es hilfreich, dass man die Erstellung der Pläne schon relativ einfach im Vorfeld verhindern kann.
Dazu kann man dem jeweiligen SQL Befehl wie im folgenden Beispiel den Query Hint OPTION (RECOMPILE) hinzufügen:

SELECT * FROM Customers
OPTION (RECOMPILE);
GO

Ein Hinweis von Klaus Aschenbrenner und der Blogpost Clearing your ad-hoc SQL plans while keeping your SP plans intact von Maciej Pilecki führten mich zu folgendem Befehl, der wohl nur die Adhoc Pläne, nicht aber die von Prozeduren löscht:

DBCC FREESYSTEMCACHE('SQL Plans');
GO

Allerdings hat das Teil den Nachteil, dass es auch parameterisierte Abfragen (z.B. von LinqToSQL und Entity Framework) wegputzt.

Übrigens gibt es auch beim Zugriff mit .Net-Anwendungen über das Entity Framework das Query Plan Caching eine Property System.Data.EntityClient.EntityCommand.EnablePlanCaching.
Die habe ich allerdings noch nicht genauer untersucht.

Evtl. könnte man mit Hilfe der obigen Abfrage in Kombination mit DBCC FREEPROCCACHE automatisch in bestimmten Zeitzyklen einfach alle Pläne löschen, die nur einmal verwendet wurden.

SQL Server 2008 Extended Events

11. Oktober 2011

Zur Fehlersuche oder zur Untersuchung aller möglicher Probleme kannt ich bisher nur die DMVs und den Profiler.
Aber der SQL Server 2008 bietet ein noch weit besseres Toolset für solche Anlässe, nämlich die Extended Events.

Damit läßt sich scheinbar so ziemlich alles verfolgen, was auf der Box abgeht und mit dem SQL Server zu tun hat.
Vereinfacht gesagt, legt man fest, welche Ereignisse beobachtet werden sollen und wo die gesammtelten Informationen gesammelt werden sollen.

Als einführende Beispiele haben mir die folgenden Artikel sehr geholfen:

Ausführlicher kann man sich mit dem Whitepaper Using SQL Server 2008 Extended Events einarbeiten.

Auf CodePlex gibt’s auch eine grafische Oberfläche für die Extended Events, das SQL Server 2008 Extended Events SSMS Addin. Damit kommt man anfangs wahrscheinlich einfacher zu einem Ergebnis, da es einige Events, Targets, usw. gibt.

Mit folgendem Skript kann man z.B. auftretenden Fehler mitprotokollieren:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AjErrorsReported')
DROP EVENT SESSION AjErrorsReported ON SERVER
GO

--SELECT * FROM sysmessages WHERE msglangid=1031 ORDER BY 1,2

-- Create Event
CREATE EVENT SESSION AjErrorsReported
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.error_reported
(
-- Add action - event property
ACTION (
sqlserver.session_id
,sqlserver.sql_text
,sqlserver.tsql_stack
,sqlserver.database_id
,sqlserver.username
,sqlserver.client_hostname
)
WHERE sqlserver.error_reported.severity>=11
)
-- Add target for capturing the data - XML File
ADD TARGET package0.asynchronous_file_target(
SET filename='E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported.xet'
, metadatafile='E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported.xem')
-- Add target for capturing the data - Ring Bugger
--,ADD TARGET package0.ring_buffer(SET max_memory = 4096)
WITH (max_dispatch_latency = 3 seconds)
GO

-- Enable Event
ALTER EVENT SESSION AjErrorsReported ON SERVER
STATE=START
GO

-- Read the data from Ring Buffer
--SELECT CAST(dt.target_data AS XML) AS xmlLockData
--FROM sys.dm_xe_session_targets dt
--JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
--JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
--WHERE dt.target_name = 'ring_buffer'
--AND ds.Name = 'AjErrorsReported'
--GO

-- Read the data from XML File
SELECT
event_data_XML.value('(event/data[1])[1]','INT') AS error
,event_data_XML.value('(event/data[2])[1]','INT') AS severity
,event_data_XML.value('(event/data[3])[1]','INT') AS [state]
,event_data_XML.value('(event/data[4])[1]','BIT') AS user_defined
,event_data_XML.value('(event/data[5])[1]','VARCHAR(1024)') AS [message]
,event_data_XML.value('(event/action[1])[1]','INT') AS session_id
,event_data_XML.value('(event/action[4])[1]','INT') AS database_id
,event_data_XML.value('(event/action[5])[1]','VARCHAR(50)') AS username
,event_data_XML.value('(event/action[6])[1]','VARCHAR(10)') AS client_hostname
,event_data_XML.value('(event/action[2])[1]','VARCHAR(1024)') AS sql_text
,event_data_XML.value('(event/action[3])[1]','VARCHAR(512)') AS tsql_stack
--SELECT CAST(event_data AS XML) event_data, *
FROM
(
SELECT CAST(event_data AS XML) event_data_XML, *
FROM sys.fn_xe_file_target_read_file
('E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported*.xet',
'E:\SqlServerData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\AjErrorsReported*.xem',
NULL, NULL)) T
GO

-- Stop the event
ALTER EVENT SESSION AjErrorsReported ON SERVER
STATE=STOP
GO

-- Clean up. Drop the event
DROP EVENT SESSION AjErrorsReported
ON SERVER
GO

Wenn’s mal eng wird … How compressed is your SQL Server DB backup?

31. August 2011

Während des DB Backup kann das Backup File größer sein, als am Ende der Sicherung.
Dieses Verhalten ist über das Traceflag 3042 beeinflussbar.

Siehe dazu diesen Post bzw.  KB-Artikel.