Saving CSV file in UTF8, Compress and Archive them

We had a requirement, where the CSV files generated by SSIS were not in UTF8. We had to resave in UTF8 format. We also had to compress the group of files and archive the original set of files. We achieved that using Powershell.

 $CSVfilesFolder = "C:\CSVFiles\"  
 $CompressFolder = "C:\CSVFiles\Compress"  
 $ArchiveFolder = "C:\CSVFiles\Archive"  
 $filesForZip = Get-ChildItem -Path $CSVfilesFolder -Filter *.csv | Sort-Object LastWriteTime | Select-Object -First $CountofFilesToZip | select fullname | ForEach-Object { $_.FullName }  
 $newFileName = "$($CompressFolder)\GeneratedZipFile.zip"  
 #Encoding to UTF8   
 foreach($file in $filesForZip)  
 {  
   $UTF8Only = New-Object System.Text.UTF8Encoding($false)  
   $FileContent = Get-Content -Path $file  
   [System.IO.File]::WriteAllLines($file, $FileContent, $UTF8Only)     
 }  
 #Generate Zip file  
 $compress = @{  
  Path = $filesForZip  
  CompressionLevel = "Fastest"  
  DestinationPath = $newFileName  
 }  
 Compress-Archive @compress -Force  
 #Archive the files  
 foreach($file in $filesForZip)  
 {  
   Move-Item $file -Destination $ArchiveFolder -Force  
 }  

Maintain timestamp of CSV File

We were having a scenario, where we had to fix few CSV field issues. But, we also wanted to retain timestamp. We used below script to fix the CSV and maintain the timestamp.

 $BulkLoadFolder = "C:\Projects\Sntrax\Bulkload\FilesToFix"  
 $Folder = "C:\FilesToFix\"  
 $filesList = Get-ChildItem -Path $Folder -Filter *.csv | select fullname | ForEach-Object { $_.FullName }  
 foreach($file in $filesList)  
 {   
     $newfile = $file.Replace("FilestoFix","FilestoFix\fixed")  
     #Get lastwritetime for the filename   
     $LastWriteDateTime = Get-ItemPropertyValue -Path $file -Name LastWriteTime  
     #Import file and Export   
     Import-Csv $file | ForEach-Object {  
  if ($_.Field1.Trim() -eq '') {  
         $_.Field1 = '--'  
       }  
       if ($_.Field2.Trim() -eq '') {  
         $_.Field2 = '--'  
       }  
       $_  
  } | Export-Csv $newfile -NoTypeInformation -Force  
     Set-ItemProperty -Path $newfile -Name LastWriteTime -Value $LastWriteDateTime  
 }  

Powershell alert, once server is up and running

 We have got regular server patches every month. When the server is restarted, we have to keep checking whether server is up again. The below powershell script will be helpful, to let us know, once server is up and running.

1:  # Load assembly  
2:  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")  
3:  $ComputerName = "FQDN of Server"  
4:  $ServerOnline = $false  
5:  while($ServerOnline -eq $false)  
6:  {  
7:    if(Test-Connection -BufferSize 32 -Count 1 -ComputerName $ComputerName -Quiet)  
8:    {  
9:     [System.Windows.Forms.Messagebox]::Show("The server $computerName is up now");  
10:     $serverOnline = $true;  
11:     break;  
12:    }  
13:    else  
14:    {  
15:      [System.Windows.Forms.Messagebox]::Show("The server $computerName is offline now");  
16:      Start-Sleep -Seconds 5  
17:    }  
18:  }  

Copy web.config between two web servers

We are having load balancing of web portal, in two different web servers. The web.config has to be same between web portal. If one of them is changed, we want to copy the same to another server. Below script can be used to do the same:

I have put local path just for reference. UNC path can be used in place of local path. 

1:  $strSourceFile = "C:\\dev\\wv02\\test.txt"  
2:  $strDestFile = "C:\\dev\\wv01\\test.txt"  
3:  if ((Get-ChildItem $strSourceFile).LastWriteTimeUtc -gt (Get-ChildItem $strDestFile).LastWriteTimeUtc)  
4:  {  
5:    write-host "$($strSourceFile) is latest than $($strDestFile)"  
6:    copy-item -Path $strSourceFile -Destination $strDestFile  
7:  }  
8:  else  
9:  {  
10:     write-host "$($strDestFile) is latest than $($strSourceFile)"  
11:     copy-item -Path $strDestFile -Destination $strSourceFile  
12:  }  

Sending Email in Powershell

Below script can be used to send email from Powershell, for audit report for yesterday. 

1:  $yesterday = (get-date).AddDays(-1).Date.ToString("yyyy_MM_dd")  
2:  $yesterdayMailSubject = (get-date).AddDays(-1).Date.ToString("yyyy-MM-dd")  
3:  $From = "test@email.com"  
4:  $To ="test@email.com"  
5:  $Cc = "test@email.com"  
6:  $Attachment = "C:\temp\Audit_$yesterday.csv"  
7:  $Subject = " DB Server Audit for Day: $yesterdayMailSubject "  
8:  $Body = "<h2>Please see the audit report attached file</h2><br><br>"  
9:  $SMTPServer = "SMTPServerName"  
10:  $SMTPPort = "587" #SMTPPort  
11:  Send-MailMessage -From $From -to $To -Cc $Cc -Subject "test" -Usessl -Body "test" -SmtpServer $SMTPServer -Port $SMTPPort -Credential $From -Verbose -Debug #-Attachments $Attachment  

How to create covering Index to fix performance Issue

We had a performance issue, where we had to create  covering index to solve the problem, as we were querying many columns in the table in a single query. 

Problem Statement:

we are having a table with 10 columns. We have 9 columns in a query, for getting clustering key (identity column here). 

1:  SELECT IdentityValue FROM TableWithIdentity   
2:  WHERE col1 = @col1 , col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5, col6 = @col6, col7 = @col7, col8 = @col8, col9 = @col9  

It is a SQL Server 2012 database. We had to create a covering index with 10 columns. How do I decide the order of columns in the covering index for efficient index ?

Solution Adopted:

1. Get the statistics names associated with table columns. 

1:  SELECT s.stats_id StatsID,  
2:   s.name StatsName,  
3:   sc.stats_column_id StatsColID,  
4:   c.name ColumnName  
5:  FROM sys.stats s  
6:   INNER JOIN sys.stats_columns sc  
7:    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
8:   INNER JOIN sys.columns c  
9:    ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
10:  WHERE OBJECT_NAME(s.object_id) = 'TableWithIdentity'  
11:  ORDER BY s.stats_id, sc.column_id;  

2. Get the statistics for each column and note down the density of each column in the first result of the SHOW_STATISTICS. 

1:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName1');  
2:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName2');  
3:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column3Statistics');  
4:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column4Statistics');  
5:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column5Statistics');  
6:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumn6Statistics');  
7:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column7Statistics');  
8:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column8Statistics');  
9:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column9Statistics');  

3. Now, we know the density of each column. We have to choose the column with least density (meaning more unique column) and followed the next least density etc. 

0.00	column1
0.00	column2
0.01	column3
0.10	column4
0.33	column5
2.09	column6
3.09	column7
5.55	column8
6.46	column9

4. Now, we created covering index based on the uniqueness of the columns order

1:  CREATE INDEX IDX_TableWithIndentity_covering on dbo.TableWithIdentity(column1, column2,column3, column4, column5, column6, column7, column8, column9)   
2:  WITH  
3:  (  
4:  SORT_IN_TEMPDB = ON,  
5:  DROP_EXISTING = OFF  
6:  )  
7:  ON [PRIMARY]  

5. Now, the index is very helpful for fixing the performance Issue.

Getting detailed Performance issue metrics using sp_whoisactive

 

The below sp_whoisactive query can be executed to get detailed performance metrics for slowly running query, which can be later analyzed for troubleshooting performance issue

1:  EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @show_system_spids =1,  
2:  @show_own_spid =1, @show_sleeping_spids = 1