How to benchmark my t-sql directly using dmv dm_exec_sessions

Tested on Sql Azure 11.0.9206.32
/*Get actual reads, writes and other nice to know stuff for benchmarking.Notice go statements which are crucial since stats seem to update by batch.*/ 
go
if object_id('tempdb..#stats') is not null drop table #stats select getdate() as startTime,cpu_time,memory_usage,reads,writes,logical_reads,session_id into #stats from sys.dm_exec_sessions a where session_id=@@spid
go
--do your magic stuff that need tuning
go

select   datediff(ms,s2.startTime,getdate()) as ElapsedMs,   s1.cpu_time-s2.cpu_time as cpu_time,   s1.memory_usage-s2.memory_usage as memory_usage,   s1.reads-s2.reads as reads,   s1.writes-s2.writes as writes,   s1.logical_reads-s2.logical_reads as logical_reads from sys.dm_exec_sessions s1   join #stats s2   on s1.session_id=s2.session_Id   where s1.session_id=@@spid

Comments

Post a Comment

Popular posts from this blog

Constructing nested array structs in BigQuery

Image
with  animal  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Horse'   AS  animal_name,  1   as  animal_count ) ,        ( 1 ,  'Cow' ,  25 ) ,        ( 2 ,  'Old World Swallowtail Butterfly' ,  1000   ) ,        ( 4 ,  'Fish'  , 5500 ) ,        ( 5 ,  'Beetle'  , 700000 )      ])   ) , zoo  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Skansen'   as  zoo_name,  100   as  city_id ) ,        ( 2 ,  'Fjärilshuset' ,  100   ) ,   ...
Read more

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Cause for Parameter is incorrect 0x80070057 error in ssis

Image
Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.
Read more