Introduction
When talking which queries are used a lof of them are checcking statistics, execution as a job and saving data in table. After test is completed all this data is then converted in:- html
- excel
- svn.
HTML is very usefull, easy to open, but do not give you a possibility to sort data. .svn pattern gives you a posibility to open file in kibana and sort, compare data, etc. Excel great is after testing is completed. It is to sort data compare with different version of application, creating graphs,etc. In this section will be described export to excel.
SQL
In our case will be used statistics of most expensive stored procedures. There a over 100 queries to analyze statistics, latches, blocking processes, etc. SQL:
SELECT TOP 1000 d.object_id,db_Name(d.database_id)
,OBJECT_NAME(object_id, database_id)
,GETUTCDATE()
,d.total_elapsed_time/d.execution_count
,d.execution_count
FROM sys.dm_exec_procedure_stats AS d
WHERE db_Name(d.database_id)='yourdatabase'
This query search for all executed stored procedures in the test. Most important is information is average elapsed time, which identify, which tables are not optimized for searching, inserting, deleting
Additional information about returned data from query will not be explained.
Connect to SQL
Powershell is script language which has a lot common with .NET framework. On internet there is a lot help available how to connect to Mssql and execute query. For connection to MSSQL server is used connection string. Used will be used SQLClient class in the System.Data namespace. First will be instantiate a new SqlConnection and SqlCommand object.:$connectionString = "Server=$databaseServer;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "SELECT TOP 1000 d.object_id
,db_Name(d.database_id)
, OBJECT_NAME(object_id, database_id)
,GETUTCDATE()
,d.total_elapsed_time/d.execution_count
,d.execution_count
FROM sys.dm_exec_procedure_stats AS d
Where db_Name(d.database_id)='Database'
ORDER BY d.last_elapsed_time DESC ; "
In some cases SQL execution takes more than expected, default timeout is 30 seconds to extend timeout use next command:
$command.CommandTimeout = 300
To create an in-memory table that can easily be manipulated is used SqlDataAdapte. The SqlDataAdapter will fill a variable $DataSet with the data from given sql statement from SQL.
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
dssdsd
Now we have extracted data from sql saved in the array table, it's time to prepare excel file. If folder does not exists then should be created:
$fileXls ="$global:nasDepot\SQLReports\SPExpensiveQueries.xls"
if (-Not (Test-Path "$global:nasDepot\SQLReports") )
{
New-Item "$ReportFileFolder\SqlReports" -ItemType directory > $null
}
To create an excel file is used object OfficeOpenXml.ExcelPackage with excel filename. If file does not exists then header in table is created starting with row in excel 1. New worksheet ExpensiveSP is created with method $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP").
Updating cell in the sheet is used method Cells with parameters: "$column$row". Header can be populated with different approach.
$intRow = 1
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage $fileXls
if (-Not (Test-Path $fileXls) )
{
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP")
$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1
$Worksheet.Cells["A$intRow"].Value="INSTANCE NAME:"
$Worksheet.Cells["B$intRow"].Value= $connectionString
$intRow ++
$Worksheet.Cells["A$intRow"].Value="Server Name:"
$Worksheet.Cells["B$intRow"].Value= $dataSource
$intRow ++
$intRow ++
$Worksheet.Cells["A$intRow"].Value = "ObjectId"
$Worksheet.Cells["B$intRow"].Value = "DatabaseId"
$Worksheet.Cells["C$intRow"].Value = "Object Name"
$Worksheet.Cells["D$intRow"].Value = "DateTime"
$Worksheet.Cells["E$intRow"].Value = "Averega response time (microsecond)"
$Worksheet.Cells["F$intRow"].Value = "Execution count"
$Worksheet.Cells["G$intRow"].Value = "TestName"
$Worksheet.Cells["H$intRow"].Value = "Version"
$Worksheet.Cells["I$intRow"].Value = "Comment"
$intRow ++
}
else
{
$Worksheet = $ExcelPackage.Workbook.Worksheets.Item("ExpensiveSP")
$dimAddress = $Worksheet.Dimension.Address
}
When header was completed now data from variable $DataSet saved in excel. First should be identified end row to continue adding data from $DataSet that values in excel will not be updated.
$Rows=$worksheet.Dimension.End.Row
$Columns=$worksheet.Dimension.End.Column
for($i=0;$i -le $DataSet.Tables[0].Rows.Count-1;$i++)
{
$a = $($DataSet.Tables[0].Rows[$i][0])
$intRow=$Rows+$i+1
#write-host "value is : $i $a $($DataSet.Tables[0].Rows[$i][1])$($DataSet.Tables[0].Rows[$i][2])"
$Worksheet.Cells["A$intRow"].Value = $($DataSet.Tables[0].Rows[$i][0])
$Worksheet.Cells["B$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][1])"
$Worksheet.Cells["C$intRow"].Value = $($DataSet.Tables[0].Rows[$i][2])
$Worksheet.Cells["D$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][3])"
$Worksheet.Cells["E$intRow"].Value =$($DataSet.Tables[0].Rows[$i][4])
$Worksheet.Cells["F$intRow"].Value = $($DataSet.Tables[0].Rows[$i][5])
$Worksheet.Cells["G$intRow"].Value = "$global:TestName $global:TestCaseName $global:TestScenario"
$Worksheet.Cells["H$intRow"].Value = $global:Version
$Worksheet.Cells["I$intRow"].Value = "$global:additional_comment"
}
After excel worksheet is populated with data from sql statement now table should be formatted and saved.
$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()
$ExcelPackage.Save()
