Get a Sample of Queries Run Against a Database

get_query_samples.ps1

import-module sqlserver

$instance_list = @('<server-1>', '<server-2>')

$query = @"
SELECT dbs.[name] AS [database_name]
, stat.creation_time [statement_datetime_created]
, stat.execution_count AS [statement_run_count]
, txt.[text] AS [statement_text]
FROM sys.dm_exec_query_stats stat CROSS APPLY sys.dm_exec_sql_text(stat.plan_handle) txt
INNER JOIN sys.databases dbs
ON txt.dbid = dbs.database_id
WHERE dbs.name NOT IN ('master', 'msdb', 'tempdb', 'ssisdb', 'model')
"@

foreach ($instance_name in $instance_list) {
	$results = Invoke-Sqlcmd -ServerInstance $instance_name `
		-Database 'master' `
		-Query $query

	$results_json = $results `
		| select database_name, statement_datetime_created, statement_run_count, statement_text `
		| ConvertTo-Json
	
	$export_file_name = ".\{0}_{1}.txt" -f $instance_name.replace('\', '$'), $(Get-Date -Format 'yyyyMMdd_hhmmss')
	
	Set-Content -Value $results_json -Path $export_file_name
}