Liste von allen Datumswerten zwischen zwei Daten generieren

Manchmal benötigt man für Abfragen eine Liste von allen Datumswerten in einem definierten Bereich.
Hat man keine spezielle Kalendertabelle zur Verfügung, kann man sich eine solche Liste schnell und performant mit Hilfe einer systemtabelle generieren. 
Mein Ansatz für diese Problemstellung ist folgender:

DECLARE @StartDate date = '2016-12-31'
DECLARE @EndDate date = GETDATE()

SELECT DATEADD(DAY,number+1,@StartDate) [Date]
  FROM master..spt_values
 WHERE type = 'P'
   AND DATEADD(DAY,number+1,@StartDate) < @EndDate  

Das Statement generiert eine Datumsliste bis zum aktuellen Datum.
Falls wer eine einfachere oder performantere Lösung hat, einfach ein Kommentar hinterlassen. 

Zufallszahlen

Nachdem ich schon lange nichts mehr gepostet habe eine kleine Spielerei, die durchaus nützlich sein kann, für zwischendurch. Manchmal möchte man sich selbst eine Reihe von Testdaten rein zufällig generieren. Eine Zufallszahl selbst, kann einfach mit der Funktion RAND() generiert werden. Sofern nur Ganzzahlen im INT Wertebereich gewünscht sind, umgrenzt man die Funktion einfach mit der ROUND-Funktion.
Wer noch einen Schritt weiter gehen will und nur Werte in einem Bestimmten Bereich haben will, der kann dies mit unten angeführtem SQL einfach realisieren. 

---- Generiert Zufallszahl in einem Wertebereich
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 20   -- Untergrenze
SET @Upper = 80   -- Obergrenze
SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) -- Ganzzahl
SELECT (@Upper - @Lower -1) * RAND() + @Lower -- Float

CSV File Merger - schneller Weg

Nachdem ich vor kurzem mit meinem CSV File Merger Skript Laufzeitprobleme hatte (beide Files im GB Bereich), musste ich mir eine andere bzw schnellere Lösung überlegen.

Diese habe ich nun mit dem System.IO.StreamReader/Writer umgesetzt. Läuft für zwei Stück CSV Files mit je 500 MB nur wenige Sekunden lang. Somit um ein vielfaches schneller als meine alte Lösung und auch ressourcensparender. Die alte Version ist bei zu großen Files irgendwann wegen zuwenig Speicher abgestürzt. Hoffentlich läuft die neue Version besser.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$i = 0
$outs = New-Object System.IO.StreamWriter $OutputFile
            
foreach($CSV in $CSVFiles) {            
    $ins = New-Object System.IO.StreamReader $CSV

    while( !$ins.EndOfStream ) {
        $line = $ins.ReadLine();
        if( $i -ne 0 ) {
            $outs.WriteLine($line);
        }
        $i = $i+1;
    }
    $ins.Close();
            
}            

$outs.Close();     

CSV-File mergen

Ja, eigentlich heißt der Blog "sqlwithpleasure", aber manchmal muss man auch über den Tellerrand blicken. Deshalb - und weil ich in letzter Zeit mehr mit Control-M etc verbringe - heute wieder ein kleiner Post bzgl PowerShell.

Wir hatten ein recht simples Problem, dass wir tourlich verschiedene CSV-Files angeliefert bekommen, die jedoch dann auf eines zusammengeführt werden sollen. Natürlich wieder mal eine kleine Powershell-Lösung. Vielleicht benötigt ja jemand mal was ähnliches.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$Output = @();            
foreach($CSV in $CSVFiles) {            
    if(Test-Path $CSV) {                               
        $temp = Import-CSV -Path $CSV             
        $Output += $temp                   
    } else {            
        Write-Warning "$CSV : No such file found"            
    }            
            
}            
$Output | Export-Csv -Path $OutputFile -NoTypeInformation            
Write-Output "$OutputFile successfully created"      

Statistik über aufgetretene Wait Types

Wenn dieser Artikel gepostet wird, befind ich mich schon im Urlaub und somit auf meiner Reise in den Kaukasus.
Aber ich habe schon vorab ein interessantes Skript vorbereitet:
Dieses Skript summiert die aufgetretenen Waits nach Typ auf und zeigt an, wie oft sie auftreten, wie lange gewartet wurde, stellt sie in prozentualer Relation dar und berechnet auch die durchschnittliche Wartezeit.
Anhand dieser Liste bekommt man oft einen Überblick, wo es am MS SQL Server kränkelt. Einen Artikel über die diversen Wait-Types, welche gut und welche böse sind, werde ich nach meiner Rückkehr verfassen.

Syntax:

WITH [Waits] AS
    (SELECT [wait_type]
           ,[wait_time_ms] / 1000.0 AS [WaitS]
           ,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
           ,[signal_wait_time_ms] / 1000.0 AS [SignalS]
           ,[waiting_tasks_count] AS [WaitCount]
           ,100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage]
           , ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER'             
       ,N'BROKER_RECEIVE_WAITFOR'
       ,N'BROKER_TASK_STOP'               
       ,N'BROKER_TO_FLUSH'
       ,N'BROKER_TRANSMITTER'              
       ,N'CHECKPOINT_QUEUE'
       ,N'CHKPT'                           
       ,N'CLR_AUTO_EVENT'
       ,N'CLR_MANUAL_EVENT'                
       ,N'CLR_SEMAPHORE'
       ,N'DBMIRROR_DBM_EVENT'              
       ,N'DBMIRROR_EVENTS_QUEUE'
       ,N'DBMIRROR_WORKER_QUEUE'           
       ,N'DBMIRRORING_CMD'
       ,N'DIRTY_PAGE_POLL'                 
       ,N'DISPATCHER_QUEUE_SEMAPHORE'
       ,N'EXECSYNC'                        
       ,N'FSAGENT'
       ,N'FT_IFTS_SCHEDULER_IDLE_WAIT'     
       ,N'FT_IFTSHC_MUTEX'
       ,N'HADR_CLUSAPI_CALL'               
       ,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
       ,N'HADR_LOGCAPTURE_WAIT'            
       ,N'HADR_NOTIFICATION_DEQUEUE'
       ,N'HADR_TIMER_TASK'                 
       ,N'HADR_WORK_QUEUE'
       ,N'KSOURCE_WAKEUP'                  
       ,N'LAZYWRITER_SLEEP'
       ,N'LOGMGR_QUEUE'                    
       ,N'ONDEMAND_TASK_QUEUE'
       ,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
       ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
       ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
       ,N'REQUEST_FOR_DEADLOCK_SEARCH'     
       ,N'RESOURCE_QUEUE'
       ,N'SERVER_IDLE_CHECK'               
       ,N'SLEEP_BPOOL_FLUSH'
       ,N'SLEEP_DBSTARTUP'                 
       ,N'SLEEP_DCOMSTARTUP'
       ,N'SLEEP_MASTERDBREADY'             
       ,N'SLEEP_MASTERMDREADY'
       ,N'SLEEP_MASTERUPGRADED'            
       ,N'SLEEP_MSDBSTARTUP'
       ,N'SLEEP_SYSTEMTASK'                
       ,N'SLEEP_TASK'
       ,N'SLEEP_TEMPDBSTARTUP'             
       ,N'SNI_HTTP_ACCEPT'
       ,N'SP_SERVER_DIAGNOSTICS_SLEEP'     
       ,N'SQLTRACE_BUFFER_FLUSH'
       ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
       ,N'SQLTRACE_WAIT_ENTRIES'           
       ,N'WAIT_FOR_RESULTS'
       ,N'WAITFOR'                         
       ,N'WAITFOR_TASKSHUTDOWN'
       ,N'WAIT_XTP_HOST_WAIT'              
       ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
       ,N'WAIT_XTP_CKPT_CLOSE'             
       ,N'XE_DISPATCHER_JOIN'
       ,N'XE_DISPATCHER_WAIT'              
       ,N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type])          AS [WaitType],
    CAST (MAX ([W1].[WaitS])        AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS])    AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS])      AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount])          AS [WaitCount],
    CAST (MAX ([W1].[Percentage])                           AS DECIMAL (5,2))  AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount]))     AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount]))   AS DECIMAL (16,4)) AS [AvgSig_S]
 FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
   ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95
;

Langläufer suchen und warnen

Kurz vor meiner Urlaubspause (ja, auch ich darf mal) gibt es noch ein neues Skript.
Das unten angeführte Skript nehme ich gerne zur Laufzeitüberwachung von diversen Queries. Es passiert immer wieder, dass Queries, welche normalerweise nur einige Sekunden oder Minuten laufen, plötzlich von einem Tag auf den anderen schier unendlich laufen. 
Da natürlich viele Abfragen und Programme gleichzeitig Laufen, jedesmal zu einem unterschiedlichen Zeitpunkt, sollte man dies automatisiert überwachen.
In meinem Beispielskript habe ich zusätzlich eine Variable @Laufzeitgrenze eingebaut, welche wir derzeit via Control-M als Schwellwert mitgeben. Sollte die Abfrage ein Ergebnis zurückliefern wird automatisch ein Mail generiert, welches auf einen Langläufer hinweist. 
Danach erfolgt die übliche Analyse. Indizes, Statistiken, PlanHandle wegwerfen, Neustart.

Hat jemand Ideen zur Erweiterung oder Vereinfachung? Oder hat jemand einen komplett anderen Ansatz um dies zu bewerkstelligen? Freue mich über jede Rückmeldung

Syntax:

DECLARE @Laufzeitgrenze AS datetime
SET @Laufzeitgrenze = '00:05:00:000' -- 5 Minuten
-- SET @Laufzeitgrenze = '01:00:00:000' -- 1 Stunde

SELECT 'Schwellwert überschritten!!!' AS Warnung  
      ,[SID]   
      ,[STATUS]  
      ,[DOP]  
      ,[BLOCKED]  
      ,[DBNAME]  
      ,[PROGRAM_NAME]  
      ,CONVERT(CHAR(12),((CURRENT_TIMESTAMP)-start_time),14) AS [LAUFZEIT]
      ,[LOGINNAME]  
      ,[COMMAND]  
      ,SQLStatement  
      ,start_time  
      ,[PLAN_HANDLE]
FROM
  ( SELECT req.session_id AS [SID]  
          ,sqltext.TEXT AS [SQLStatement]  
          ,DB_NAME(req.database_id) AS [DBNAME]  
          ,req.status AS [STATUS]  
          ,mg.dop AS [DOP]
          ,p.blocked AS [BLOCKED]  
          ,req.command AS [COMMAND]  
          ,req.start_time AS start_time  
          ,p.loginame AS [LOGINNAME]  
          ,p.program_name AS [PROGRAM_NAME]  
          ,req.plan_handle AS [PLAN_HANDLE]
    FROM sys.dm_exec_requests req WITH (NOLOCK) 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext 
   CROSS APPLY sys.dm_exec_query_plan (plan_handle) eqp
   INNER JOIN sys.sysprocesses p WITH (NOLOCK) ON p.spid = req.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants mg WITH (NOLOCK) 
      ON req.session_id = mg.session_id
   WHERE req.session_id > 50
     AND req.session_id NOT IN (@@SPID)
     AND p.loginame <> ''
     AND (CURRENT_TIMESTAMP)-start_time > @Laufzeitgrenze) sub
ORDER BY start_time ASC

String in Files mittels PowerShell suchen

Ja, eigentlich heißt der Blog "sqlwithpleasure", aber manchmal muss man auch über den Tellerrand blicken. Deshalb heute wieder ein kleiner Post bzgl PowerShell.
Täglich werden tausende Logfiles produziert, diese (historisch) zu durchforsten ist manchmal notwendig, aber mitunter recht mühsam.
Natürlich gibt es im Explorer die Möglichkeit Dateiinhalte zu durchsuchen. Damit bin ich jedoch nicht immer ganz glücklich geworden, also hab ich mir selbst etwas gebastelt.

Ein kleines Powershellskript übernimmt diese Arbeit. Mitgegeben wird der Suchpfad, der Suchtext, ein Pattern um uU nur einen Bruchteil der Files in einem Ordner zu durchsuchen, ob die Suche rekursiv (auf alle Unterornder) erfolgen soll, oder nur im angegebenen Verzeichnis und auch noch, wie die Ausgabe erfolgen soll. HTML kann hier eine praktikable Lösung sein. Man kann einfach und schnell mittels HTML-Links durch die Files navigieren.

Gibt es Erweiterungsideen? Vorschläge zur Verbesserung? 
Freue mich wie immer auf jede einzelne Rückmeldung.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-07-30
   Purpose       : Files nach String durchsuchen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript sucht nach einem STRING in allen Dateien eines Verzeichnisses.
 
.EXAMPLE
.\SearchForStringInFilesRecursive.ps1 

#>

param(
 # PATH -> Gibt an in welchem Verzeichnis gesucht wird
 [Parameter(Mandatory=$True)]
    [string]$Path = "\\myserver\Logfiles\",
 # TEXT -> Gibt an nach welchem TEXT gesucht wird
    [Parameter(Mandatory=$True)]
    [string]$Text = "Timeout",
    # PATTERN -> Hier kann man die zu durchsuchenden Files mit Wildcards einschränken - zB CDW*.txt
    [Parameter(Mandatory=$True)]
    [string]$Pattern = "A*.txt",
 # RECURS -> gibt an ob die suche rekursiv auch alle Subordner einschließen soll, oder nicht -> Y oder N
    [Parameter(Mandatory=$False)]
    [string]$Recurs = "Y",
 # OUTPUT -> gibt an, wie der Output erfolgt. Console = nur Joblog, HTML = HTML File, TEXT = TXT File, ALL = Alle varianten
 #   Outputordner: \\myserver\Suchergebnis\
    [Parameter(Mandatory=$False)]
    [ValidateSet('ALL','CONSOLE','HTML', 'TEXT')]
    [string]$Output = "ALL"
)


$PathArray = @()
$Results = "\\myserver\Suchergebnis\Suchergebnis_" + "$Text"+ "_" +(Get-Date -format yyyy_MM_dd_HH_mm_ss) 
$ResultsHTML = $Results + ".html"
$ResultsText = $Results + ".txt"

Write-Host ("Suchstart: " + (Get-Date))


#Entscheidung ob rekursiv durchsucht wird oder nicht
if ( $Recurs = 'Y' )
{
    Get-ChildItem $Path -Filter $Pattern -Recurse | 
       Where-Object { $_.Attributes -ne "Directory"} | 
          ForEach-Object { 
             If (Get-Content $_.FullName | Select-String -Pattern $Text) {
                $PathArray += $_.FullName
             }
          } 
}
else
{
    Get-ChildItem $Path -Filter $Pattern | 
       Where-Object { $_.Attributes -ne "Directory"} | 
          ForEach-Object { 
             If (Get-Content $_.FullName | Select-String -Pattern $Text) {
                $PathArray += $_.FullName
             }
          }
}

Write-Host ("Suchende : " + (Get-Date))
Write-Host "------------------------------------------------------------------------------------------"
Write-Host ("Suchordner         :" + $Path) 
Write-Host ("Suchtext           :" + $Text)
Write-Host ("Durchsuchte Files  :" + $Pattern) 
Write-Host ("Rekursive Suche    :" + $Recurs)
Write-Host ("Outputordner       :" + $Output)
Write-Host ("Outputordner       :" + $Results)
Write-Host "------------------------------------------------------------------------------------------"

Switch($Output)
 {
 'CONSOLE'{ 
            Write-Host "String in folgenden Files gefunden:"
            $PathArray | ForEach-Object {$_}
   }
 'HTML'{ 
            $PathArray | % {'' + $_ + '
'} | Out-File $ResultsHTML
  }  
    'TEXT'{ 
            $PathArray | % {$_} | Out-File $ResultsText
  }  
    'ALL'{ 
            Write-Host "String in folgenden Files gefunden:"
            $PathArray | ForEach-Object {$_}
            $PathArray | % {'' + $_ + '
'} | Out-File $ResultsHTML
            $PathArray | % {$_} | Out-File $ResultsText
  } 
 }