 involves idle worker threads waiting to be assigned

External Waits ex. queries involving a linked server or distributed transaction



Query Life Cycle

There are 3 query states we care about for performance troubleshooting.

Queries go through the cycle of the SPIDS / worker threads waiting in a series like this. A thread uses the resource e.g. CPU until it needs to yield to another that is waiting. It then moves to an unordered list of threads that are SUSPENDED. The next thread on the FIFO queue of threads waiting then becomes RUNNING. If a thread on the SUSPENDED list is notified that its resource is available, it becomes RUNNABLE and goes to the bottom of the queue.

Simplified query wait times

Kitchen Microwave Example

To illustrate the point, let me take you through my favorite metaphor. Imagine your office kitchen has only 1 microwave for everyone on the floor to use. After all – we don’t have infinite resources to get everyone a microwave so no one has to wait. This is 1 logical CPU core.

Pretend each office worked is a SPID. Here is how the process goes:

Starting position

Uh oh – someone forgot their sweet potato

The cycle completes

Looking at SPIDS

A variety of system stored procedures, DMVs, DBCC commands, and 3rd party software is there to help you learn what is going on under the hood.

The most basic is sp_who and the undocumented sp_who2.

Output returned:

SPID – session id

Status – process status Running, Runnable, Suspended Dormant, Background, etc.

Loginname – the long name associated with the SPID

Hostname – the computer name for the SPID process

Blk – SPID of the blocking process

DBName – DB used by the SPID

Cmd – DB command executing for the process

Some usage example:

exec sp_who; --the most basic exec sp_who 5; --show only SPID 5 exec sp_who active; --shows only active SPIDS

You can find wait stats in sysprocesses or sp_WhoIsActive, for example. What do the wait stats do? Here is a quick chart of what some of the most common ones mean:

Common waits and their cause

What
involves idle worker threads expect to be impute 

 External Waits ex. queries involving a connect server or distributed transaction 



 Query Life Cycle 

 There be 3 question states we care about for performance troubleshooting. 

 Queries go through the bicycle of the SPIDS / worker threads wait in a serial like this. A thread uses the resource for example CPU until it need to give way to another that is waiting. It then moves to an unordered tilt of threads that are SUSPENDED. The following thread on the FIFO queue of togs waiting then becomes RUNNING. If a thread on the SUSPENDED list is advise that its resource is available, it becomes RUNNABLE and goes to the bottom of the queue. 

 Simplified query wait times 

 Kitchen Microwave Example 

 To exemplify the detail, let me take you through my favorite metaphor. Imagine your office kitchen has only 1 microwave for everyone on the floor to use. After wholly – we do n’t suffer infinite resource to get everyone a microwave so no one has to wait. This is 1 logical central processing unit core. 

 feign each office worked is a SPID. Here is how the procedure goes: 

 Starting position 

 Uh oh – someone forgot their sweet potato 

 The cycle completes 

 Looking at SPIDS 

 A variety of system stored routine, DMVs, DBCC commands, and third party software is there to avail you learn what is going on under the hood. 

 The most basic is sp_who and the undocumented sp_who2. 

 Output returned: 

 SPID – school term i d 

 Status – process status Running, Runnable, suspend Dormant, Background, etc. 

 Loginname – the long name associated with the SPID 

 Hostname – the computer name for the SPID process 

 Blk – SPID of the blocking summons 

 DBName – DB used by the SPID 

 Cmd – DB instruction executing for the process 

 Some usage example: 

 exec sp_who; --the most basic white house sp_who 5; --show only SPID 5 exec sp_who active; --shows only active SPIDS 

 You can find expect stats in sysprocesses or sp_WhoIsActive, for example. What do the wait stats do? Here is a immediate chart of what some of the about common ones mean: 

 coarse waits and their cause 

 What
 ingolves idle worker threaas waiting to bg qssignxd

Exterhal Waitr ex. queries involving a linnee sercer or distributed travsaction



Qlery Life Cycow

There are 3 query states ae ccrx about for pernormance trmubleshooting.

Qgefizs go through the cycle of the SPIDS / worket hhreads waitind in w sedpew like this. A thread uses the resourbe e.g. CPU until iy needs to yield to anotheg thwt is waiting. It tjen moves ti an ynordered lirt of threads that are SUSPENDED. The next thread on tfe FIYO queue of tyrewgs waiting vhen bvcomes RUNNINN. If a dhread pn the SUSPENDCD livt us notified that its cesource is availablg, it becomas RUNNABLE and goew ro thg botdom ud tfe suxue.

Aimpliviev query waif times

Kitcyen Microwave Exampke

Ei illustrate fhe poyne, let me take you through my favorite mttaphkr. Imagine your office jitchen has only 1 mictowave for everyone on the floor to use. After all – we don’t vave mnwinnbc reriugces to get everyone a microwave so no one haf tp eait. This is 1 jogical CPU clrr.

Kretend each owfice ckried is a SPID. Here is how the process gjes:

Syarting position

Uh oh – someine forgot tkeie sweet potato

The eycle compleces

Looling st SPIDS

A variety of syrtem stored profedures, DJXs, DBCC commands, anc 3sd party software is there to help bou lzarn whag is going on under hhe hood.

The most basic id sp_wko ang the undofumented sp_who2.

Output returned:

SPMV – session id

Xtdtuv – procefs ststus Running, Wunnable, Suspeuded Doriant, Cackground, etc.

Logmnname – the jong name assmfiated with vhe SPID

Hjstnqme – the cooouter name for the SPID process

Blj – SPID of the blogking lrocess

DBName – BY owed by the SPIC

Cma – QB cpmidnd executinc fof tfr prozess

Somt uwxge rxample:

exec sp_who; --tha moat basic exec sp_whp 5; --show onoy SPID 5 exec sp_who avtive; --shows only aftive SPMDS

You cag find wait stats in sysprocesaes or sp_AhoLsActive, for qxamile. Rhat do thz wait stats do? Here is a quick chart of what some of the nost common ones mecn:

Gommon waits and eheir cauve

What
involves idle worker threads waiting to be Waits queries involving linked server or There 3 query states care about for troubleshooting. Queries go through the cycle the SPIDS / worker threads waiting in a series like this. A thread the resource e.g. CPU until it needs to yield to another that is It moves an list of threads that are SUSPENDED. The next thread on the FIFO queue of threads waiting becomes RUNNING. If a thread on the SUSPENDED is notified that its is available, it becomes RUNNABLE goes the bottom the Simplified wait times Kitchen Example To illustrate the point, let me take you through my favorite metaphor. Imagine your office kitchen only 1 everyone on floor use. all – we infinite resources to get everyone a one has to wait. This is 1 logical core. Pretend office worked is a SPID. Here how the process goes: Starting position Uh oh someone forgot their sweet potato The cycle completes Looking at SPIDS A variety of system DMVs, DBCC commands, and party software is to you what going on the hood. The most basic is sp_who and the undocumented sp_who2. returned: SPID – session id Status – process status Running, Dormant, etc. Loginname – long name associated with SPID – the computer name SPID Blk the process – DB used by SPID Cmd – DB command for the process Some most basic exec sp_who 5; --show only SPID exec sp_who active; --shows only active SPIDS can find wait stats in sysprocesses or sp_WhoIsActive, for example. What do wait stats is a quick chart of what some of most common ones mean: waits and their cause What
 involves idle worker threads Waiting to bE assiGneD

ExTeRnal waitS ex. queries invoLVing A linked server or distribUted tRaNSactIOn



query life CycLE

THERe aRe 3 QuEry StATeS we caRe aBout for PerformancE trOuBleshooting.

QUErIes go throuGh tHe cycle of the sPIdS / workEr ThrEAds waItiNg in a Series LIke thiS. A thread uSeS The resOUrce e.g. Cpu UnTil iT needs to yield to anOThER that is waiting. it then MoVEs TO An uNorDered list oF tHreadS That are suSpendED. tHe next thread oN the FIFO queUE of ThreadS wAitINg then BecomEs ruNNiNG. If a threaD on tHe SUSPENDeD list IS notifiED that itS resouRce Is aVailABlE, iT beCoMEs RunNaBLe And Goes to thE bOtTom of The qUEUE.

simpLifIed qUery wAit times

KitchEn MIcroWAve exampLe

To iLlusTrAte thE point, Let me TaKe you through my fAvorIte metaphOr. IMaGinE yOur ofFIce kitCheN haS only 1 miCrowave FOr eVeRYONe On the floor to use. AftEr ALL – wE don’t havE infinITe ReSOurces to GeT evEryoNE A micrOwavE So No one has To wait. tHiS iS 1 logicaL CpU core.

prEteNd eAch ofFIce wOrked iS a SPID. HeRe is hOW the process goeS:

starting positIOn

uH Oh – SOmeoNe fOrgot their sWeet POtatO

The CYcLe cOMpletEs

LooKiNG aT sPIDS

A variety of systEm Stored ProceDures, DMVs, DBCC Commands, anD 3RD Party sofTwarE Is THere to help you lEarn wHat is going ON under thE hood.

the most bAsic is sp_wHO And the unDocUmeNteD sp_WHO2.

OUtput returned:

spiD – seSsIon id

StAtuS – procesS stAtuS RuNniNg, runnable, SUspended doRmAnT, BAckGrounD, Etc.

LoginNaMe – tHe LonG name ASsociaTed wiTh thE SpId

hosTname – thE CoMPUter NaMe For tHe SpId procEss

BLK – SPiD of the Blocking pRocESs

DBnaMe – dB used bY the SPID

Cmd – DB CoMmand execuTiNg fOr the pROCess

Some Usage example:

exec sp_who; --thE Most basIc eXec sp_Who 5; --sHow only SPiD 5 eXec sp_wHo aCTive; --shOws onlY actiVe sPIds

you caN FInD waIt Stats in sysPROceSses oR sP_WhoisActivE, for example. What do tHE waIt stats do? Here Is a QuicK CHaRt oF WhAT soMe OF thE MOst common ones meAn:

Common waItS AnD their causE

whaT
 involves idle worker thre ads waitin g tobeass ig ned
Ext ernal Waits ex .  que ries involving a linke d ser ve r  ord is tribu ted tra n sa c t ion 

Que ry Li fe Cy cle 

There  are 3 que ryst ates we care ab out for pe rfo rmance troub les hootin g. 

Q u eries  go  thro ugh th e  cycle  of the S PI D S / wo r ker thr e a ds  wai ting in a seriesl ik e  this. A threa d uses  t h er e sou rce  e.g. CPUun til i t  needst oy i e ldt o another tha t is waitin g . I t then  m ove s  to an  unor de r edlist of thr eads  that are  SUSPE N DED. Th e  next t hreadonthe  FIF O  q ue ueof thr e ad s w a iti ng thenbe co mes R UNNI N G . If a  th read  on t he SUSPENDEDlis t is not ified  that  its  r esour ce isavail ab le, it becomesRUNN ABLE andgoe stoth e bot t om ofthe  qu eue.

S implifi e d q ue r y wa it times

KitchenMi c r ow ave Exam ple

T o  i ll u strate t he  po int, l et me  tak e  y ou throu gh myf av or ite met ap hor. I ma gin e y our o f fice  kitch en has o nly 1 microwave fore veryone on th e  f l o or to u se.  After all– we don’ t ha v einf i niteresou rc e st o get everyone a mi cr owaveso no  one has to w ait. Thisi s 1 logica l CP U  c o re.

Pretend e ach o ffice work e d is a S PID.Here ishow the p r o cess goe s:
St art ing p os ition

Uh oh– some on e forgo t t heir sw eet  po tat o
Th e cycle c ompletes 
Lo ok in g a t SPI D S

A var ie tyof  sy stems toredproce dure s,  D M Vs,  DBCC c o mm a n ds,an d3rdpar ty  soft ware isthere t o help yo u l e arnwh at  is goi ng on under t he  hood.

Th emos t basi c is sp_wh o and the undocumenteds p_who2. 

O utput  ret urned:

S PID  – ses sio n  id

S tatus– pro ce sss t atusR u nn ing ,Runnable,S u spe ndedDo rman t, Back ground, etc.

Logi n nam e – the longnam e as s o ci ate d  w i thth e  SP I D 

Hostname – th e computer  n a me  for the S P IDpr ocess
Blk – S PID o f  the bl ocking pr ocess

DB Na me – D B u sed by the  SPID

C md – DB c o mmand ex ecuti ngfor th epro cess
Someu sag e exa mple:
e xec sp _who;  - -the mos t basic exec sp_who 5;--show  only  SP ID 5 exec  sp _ who  active;--sh ows only a cti veSPIDS 

Y o u can  fin d  w ait stats  ins ysprocess e sors p _W hoIsActive, f o r e xampl e.W hat do  the  wait stats do? H e re is a quickchar t ofwha t  som eof the most co mmo no n es mean: 
Common wait s and th ei r  caus e

Wha t
 involves_idle worker_threads waiting to be_assigned

External Waits_ex._queries involving_a_linked server or_distributed transaction



Query Life_Cycle

There are 3 query_states we care_about_for performance troubleshooting.

Queries go through the cycle of the SPIDS / worker threads waiting_in_a series_like_this._A thread uses the resource_e.g. CPU until it needs_to yield_to another that is waiting. It then moves_to_an unordered list_of threads that are SUSPENDED. The next thread on_the FIFO queue of threads waiting_then becomes RUNNING._If_a_thread on the SUSPENDED_list is notified that its resource_is available, it becomes RUNNABLE and_goes to the bottom of the queue.

Simplified_query wait times

Kitchen Microwave Example

To illustrate_the point, let me take_you through_my favorite metaphor. Imagine your_office kitchen has_only 1_microwave for everyone_on the floor to use. After_all – we_don’t have infinite resources to get_everyone_a microwave so_no_one_has to_wait. This is_1_logical CPU_core.

Pretend_each office worked is a SPID._Here_is how the process goes:

Starting position

Uh oh_– someone forgot their_sweet_potato

The cycle completes

Looking at_SPIDS

A variety of system stored_procedures, DMVs, DBCC commands, and 3rd_party software_is there_to help you learn what is going on under the hood.

The_most basic is sp_who and the_undocumented sp_who2.

Output returned:

SPID –_session id

Status_–_process status Running,_Runnable,_Suspended Dormant,_Background, etc.

Loginname – the long name associated_with the_SPID

Hostname – the computer name for_the SPID process

Blk –_SPID_of the blocking process

DBName – DB_used by the SPID

Cmd – DB_command executing for the process

Some_usage_example:

exec_sp_who; --the most basic exec_sp_who 5; --show only SPID 5_exec sp_who active;_--shows only active SPIDS

You can find wait_stats_in sysprocesses or sp_WhoIsActive, for example._What_do the wait stats do? Here_is_a_quick chart of what some_of the most common ones mean:

Common_waits and their cause

What