Query Stats Ranking

use master



Select
SQL as SqlStatement
,DBNAME
,OBJECTNAME

,100. * SUM(total_execution_time_ms) / SUM(SUM(total_execution_time_ms)) OVER() AS PCTOFTOTAL_TOTAL_EXECUTION_TIME_MS
,ROW_NUMBER() OVER(ORDER BY SUM(total_execution_time_ms) DESC) AS TOTAL_EXECUTION_TIME_MS_RANK

,100. * SUM(total_cpu_time_ms) / SUM(SUM(total_cpu_time_ms)) OVER() AS PCTOFTOTAL_TOTAL_CPU_TIME_MS_MS
,ROW_NUMBER() OVER(ORDER BY SUM(total_cpu_time_ms) DESC) AS TOTAL_CPU_TIME_MS_RANK

,100. * SUM(total_logical_reads) / SUM(SUM(total_logical_reads)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_READS
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_reads) DESC) AS TOTAL_LOGICAL_READS_RANK

,100. * SUM(total_logical_reads) / SUM(SUM(total_logical_reads)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_READS
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_reads) DESC) AS TOTAL_LOGICAL_READS_RANK

,100. * SUM(total_logical_writes) / SUM(SUM(total_logical_writes)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_WRITES
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_writes) DESC) AS TOTAL_LOGICAL_WRITES_RANK

,cast(100.00 * SUM(execution_count) / SUM(SUM(execution_count)) OVER() as numeric(32,2) ) AS PctofTotalExecution_count
,ROW_NUMBER() OVER(ORDER BY SUM(execution_count) DESC) AS Execution_Count_Rank

,total_execution_time_ms
,total_cpu_time_ms
,total_logical_reads
,total_logical_writes
,execution_count
,query_hash
,query_plan_hash
,cast(query_plan as XML) as queryplan
from (

SELECT TOP 200
        SUBSTRING(text, statement_start_offset/2 + 1,
             (CASE WHEN statement_end_offset = -1
                             THEN LEN(CONVERT(nvarchar(max), text)) * 2
                             ELSE statement_end_offset
                END - statement_start_offset)/2) as SQL              
        ,coalesce( db_name(query_text.DBID) , DB_Name(F_DB.DatabaseID) ) as DBNAME
        , Object_name(query_text.objectid,query_text.DBID) as OBJECTNAME
   ,s.total_worker_time / 1000 AS total_cpu_time_ms
      ,s.total_elapsed_time / 1000 AS total_execution_time_ms
,s.total_logical_reads
,s.total_logical_writes

,s.execution_count as execution_count
,s.query_hash
,s.query_plan_hash
,c.query_plan
,((s.total_elapsed_time)/ s.execution_count)/ 1000 AS avg_execution_time_ms
,  ((s.total_worker_time * 1.00)/ s.execution_count)/ 1000 AS avg_cpu_time_ms
,  ((s.total_logical_reads * 1.00)/ s.execution_count) AS avg_logical_reads
,  ((s.total_logical_writes * 1.00)/ s.execution_count) AS avg_logical_writes
FROM sys.dm_exec_query_stats s
  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text
      cross apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) c
       CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(s.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
--ORDER BY total_worker_time DESC
ORDER BY total_elapsed_time DESC
--ORDER BY total_logical_reads DESC


 ) as a
Group BY SQL,DBNAME,OBJECTNAME, total_cpu_time_ms, total_execution_time_ms, total_logical_reads, total_logical_writes, execution_count,query_hash,query_plan_hash
,query_plan

Order by
--a.total_cpu_time_ms desc
a.total_execution_time_ms desc
--a.total_logical_reads desc

Retreiving Sql Server 2012 Product Key from the registry

https://xzwang.wordpress.com/2013/06/22/retreiving-sql-server-2012-product-key-from-the-registry/

function Get-SQLserverKey {

## function to retrieve the license key of a SQL 2012 Server.

## by Jakob Bindslet (jakob@bindslet.dk)

## 2012 Modification by Xian Wang (daanno2@gmail.com)

param ($targets = ".")

$hklm = 2147483650

$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup"

$regValue1 = "DigitalProductId"

$regValue2 = "PatchLevel"

$regValue3 = "Edition"

Foreach ($target in $targets) {

$productKey = $null

$win32os = $null

$wmi = [WMIClass]"\\$target\root\default:stdRegProv"

$data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)

[string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue

[string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue

$binArray = ($data.uValue)[0..16]

$charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"

## decrypt base24 encoded binary data

For ($i = 24; $i -ge 0; $i--) {

$k = 0

For ($j = 14; $j -ge 0; $j--) {

$k = $k * 256 -bxor $binArray[$j]

$binArray[$j] = [math]::truncate($k / 24)

$k = $k % 24

}

$productKey = $charsArray[$k] + $productKey

If (($i % 5 -eq 0) -and ($i -ne 0)) {

$productKey = "-" + $productKey

}

}

$win32os = Get-WmiObject Win32_OperatingSystem -computer $target

$obj = New-Object Object

$obj | Add-Member Noteproperty Computer -value $target

$obj | Add-Member Noteproperty OSCaption -value $win32os.Caption

$obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture

$obj | Add-Member Noteproperty SQLver -value $SQLver

$obj | Add-Member Noteproperty SQLedition -value $SQLedition

$obj | Add-Member Noteproperty ProductKey -value $productkey

$obj

}

}