2021年1月16日星期六

To Get the SQL Query_Hash in powershell

I am trying to execute the below powershell script to collect the SQL query_hash to use them in the next SQL queries but the output contains some random numbers instead of a query_hash like "query_hash : {39, 0, 2, 205...}" please help me.

$params = @{      'Database' = 'Database name'      'ServerInstance' =  'servername'      'Username' = 'username'      'Password' = 'pass'      'OutputSqlErrors' = $true  }    $sqlcmd= "  SELECT TOP 2      GETDATE() runtime, *  FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS" + '"Statement_Text" '+"      FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text          FROM sys.dm_exec_requests AS req                  CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats      GROUP BY query_hash) AS t  ORDER BY Total_Request_Cpu_Time_Ms DESC;"    $sql= Invoke-Sqlcmd -query $sqlcmd @params -MaxCharLength 999999       
https://stackoverflow.com/questions/65756949/to-get-the-sql-query-hash-in-powershell January 17, 2021 at 11:06AM

没有评论:

发表评论