Joel Greijer

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  ...

--drop table dbo.Earth create   table   dbo . Earth    (      Id   hierarchyid   not   null ,      Path   as   Id . ToString ( ) ,      Data   varchar ( 500 ) ,      primary   key   clustered   ( id ) ) insert   dbo . Earth   ( id , data ) values    ( '/1/1/1/1/' , 'Monkey' ) ,    ( '/1/1/1/2/' , 'Horsey' ) ,    ( '/1/1/1/3/' , 'Piglet' ) ,    ( '/1/1/1/' , 'Mammal' ) ,    ( '/1/1/' , 'Animal' ) ,    ( '/1/1/2/' , 'Insect' ) ,    ( '/1/1/2/14/' , 'Ant' ) ,    ( '/1/2/' , 'Plant' ) ,    ( '/1/2/1/' , 'Tree' ) ,    ( '/1/2/1/1/' , 'Birch' ) ,    ( '/1/2/1/2/' , 'Oak' ) ,    ( '/1/' , 'Living stuff' ) ,    ( '/2/' , 'Moving stuff' ) ,    ( '/2/1/' , 'Cars' ) ,    ( ...