Generate T-SQL Script for a List of Views

USE:

CLEAR 

$sSRCServer = "TUIRA"
$sSRCDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $true
$oSO.IncludeIfNotExists = $true

$a = Get-Content "C:tempviewlist.txt"

foreach ($o in $a) {
	Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlView -Name $o| `
                     Get-SqlScripter -scriptingOptions $oSO
	WRITE("GO `n`n`n")
}

WMI Date Conversion

USE: To convert the GMT data returned from error logs to a readable date time value.

CLEAR

$sHostName = "TUIRA"
$aLogName = "Application"
$FromDate = [datetime]::today
$WMIQuery = "LogFile='$aLogName' and Type='Error' and TimeGenerated>='$FromDate'"
 
Get-WmiObject Win32_NTLogEvent -ComputerName $sHostName `
                               -Filter "$WMIQuery" `
							   -ErrorAction SilentlyContinue | `
							    SELECT SourceName, `
 @{Name = "TimeGenerated"; EXPRESSION = {$_.ConvertToDateTime($_.TimeGenerated)}}, `
							    Message

Top 25 Most Expensive Stored Procedures

USE: These queries will display the top 25 most expensive stored procedures that are still in the cache.

SET NOCOUNT ON

--[ REVIEW MOST EXPENSIVE TOP 25 CACHED STORED PROCEDURES ]
--[ LOGICAL READS RELATE TO MEMORY PRESSURE               ]
SELECT TOP(25) A.name [SP Name]
     , B.total_logical_reads [TotalLogicalReads]
     , B.total_logical_reads / B.execution_count [AvgerageLogicalReads]
     , B.execution_count
     , ISNULL(B.execution_count / DATEDIFF(SECOND, B.cached_time, GETDATE()), 0) [Calls/Second]
     , B.total_elapsed_time
     , B.total_elapsed_time / B.execution_count [AverageElapsedTime]
     , B.cached_time
  FROM sys.procedures A
 INNER JOIN sys.dm_exec_procedure_stats B ON A.[object_id] = B.[object_id]
 WHERE B.database_id = DB_ID()
 ORDER BY B.total_logical_reads DESC;
 
 
 
--[ REVIEW MOST EXPENSIVE TOP 25 CACHED STORED PROCEDURES ]
--[ PHYSICAL READS RELATE TO IO PRESSURE                  ]
SELECT TOP(25) A.name [SP Name]
     , B.total_physical_reads [TotalphysicalReads]
     , B.total_physical_reads / B.execution_count [AvgeragePhysicalReads]
     , B.execution_count
     , ISNULL(B.execution_count / DATEDIFF(SECOND, B.cached_time, GETDATE()), 0) [Calls/Second]
     , B.total_elapsed_time
     , B.total_elapsed_time / B.execution_count [AvgerageElapsedTime]
     , B.cached_time
  FROM sys.procedures A
 INNER JOIN sys.dm_exec_procedure_stats B ON A.[object_id] = B.[object_id]
 WHERE B.database_id = DB_ID()
 ORDER BY B.total_physical_reads DESC;