Joel Greijer

create   table   dbo . PlanGuideTest (   Id   int   identity ( 1 , 1 )   primary   key ,    Data   varchar ( 100 )   not   null ) insert   dbo . PlanGuideTest   ( data )   values   ( 'a' ) , ( 'b' ) , ( 'c' ) , ( 'd' ) /* run this a couple of times and verify in execution plan that  QueryPlan DegreeOfParallelism="" is greater than 1 (this will not be the case if your server is not set up for paralellism)*/ exec   sp_executesql   N'select * from PlanGuideTest where Id between @p0 and @p1' , N'@p0 int,@p1 int' , 2 , 4 /* check which plan is used, remember that your original query might be altered before stored in cache, so check usecounts column. */ select   st . te...

Will probably not work on heap (non clustered) tables select   object_name ( s . object_id )   as   TableName ,           i . name                     as   IndexName ,           last_user_update   --,s.*   from     sys . dm_db_index_usage_stats   s           join   sys . indexes   i             on   i . index_id   =   s . index_id                and   i . object_id   =   s . object_id   where    object_name ( s . object_id )   =   'myTable'  

/*   Awaits job to finish, then returns job_status   0 = Failed   1 = Succeeded   2 = Retry   3 = Canceled   null = Job never executed / no history   If job was executed and already finished successfully before this proc starts, it will return 1 (success) */   create   procedure   tools . AwaitSqlServerAgentJob      @jobName   sysname   as      declare   @jobid   uniqueidentifier   =   ( select   job_id   from   msdb . dbo . sysjobs   where   name   =   @jobname   )    declare   @sessionId   int   =   ( select   max ( session_id )   from   msdb . dbo . sysjobactivity ...

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' ) ,    ( ...

/* Example of how to merge partial set of target table  In this example I merge from #mysource to #mytarget, but only affect type='car' */   if   object_id ( 'tempdb..#mytarget' )   is   not   null     drop   table   #mytarget if   object_id ( 'tempdb..#mysource' )   is   not   null     drop   table   #mysource create   table   #mytarget   (   id   int   not   null ,     type   varchar ( 100 )   not   null ,      name   varchar ( 100 )   not   null   ) create   table   #mysource   (   id   int   not   null ,     type   varchar ( 100 )   not   null ,      name   varchar ( 100 )   not   null ...

--Works good with numbers, careful with text due to xml-conversion select stuff( ( select ','+AnimalId from dbo.Animal as a for xml path('') ), 1,1,'') AS AggregatedAnimalIdsAsString

/* Short question: is "where not exists" atomic? Short answer: No it will fail, if not in transaction or using locking hint. Even merge will fail. Follow steps to reproduce below. (Written on sql2008r2) */   /* Preparation script */   /* drop table dbo.Monkey drop table stage.Monkey */   create   table   dbo . Monkey   (  MonkeyId   int ,     MonkeyDate   datetime ,     primary   key   clustered   ( Monkeyid ) ) create   table   stage . Monkey   (  MonkeyId   int   identity ( 1 , 1 ) ,     MonkeyDate   datetime ) go set   nocount   on insert   stage . Monkey   select   getdate ( ) go  10 insert   stage . Monkey   select   getdate ( )   from   stage ...