Home
%3CLINGO-SUB%20id%3D%22lingo-sub-776142%22%20slang%3D%22en-US%22%3ERe%3A%20Tasks%2C%20Workers%2C%20Threads%2C%20Scheduler%2C%20Sessions%2C%20Connections%2C%20Requests%20%3B%20what%20does%20it%20all%20mean%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776142%22%20slang%3D%22en-US%22%3EFew%20questions%20-%201.%20DMV%20data%20for%20Connection%20%26amp%3B%20Session%20appear%20same%20since%20those%20are%201%3A1%20mapped%2C%20unless%20uniquely%20used%20in%20Troubleshooting%2FOptimisation%20Process.%20Can%20you%20share%20any%20instance%20wherein%20we%20could%20use%20these%202%20in%20unison%20or%20separately%3F%202.%20In%20which%20part%20of%20Troubleshooting%2FOptimisation%20Process%20are%20these%20DMVs%20used.%20Any%20lead%20is%20appreciated%20and%20would%20set%20a%20starting%20point%20for%20further%20understanding.%20Thank%20You!%20--In%20'thoughts'...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1227909%22%20slang%3D%22en-US%22%3ERe%3A%20Tasks%2C%20Workers%2C%20Threads%2C%20Scheduler%2C%20Sessions%2C%20Connections%2C%20Requests%20%3B%20what%20does%20it%20all%20mean%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1227909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382830%22%20target%3D%22_blank%22%3E%40LonelyRogue%3C%2FA%3E%26nbsp%3B-%20thank%20you%20for%20your%20question.%20It%20prompted%20me%20to%20add%20more%20clarifications%20to%20the%20Session%20and%20Connection%20descriptions%20in%20order%20to%20differentiate%20them.%20Please%20see%20if%20those%20help%20and%20provide%20feedback.%20Thank%20you%20for%20helping%20make%20this%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269984%22%20slang%3D%22en-US%22%3ERe%3A%20Tasks%2C%20Workers%2C%20Threads%2C%20Scheduler%2C%20Sessions%2C%20Connections%2C%20Requests%20%3B%20what%20does%20it%20all%20mean%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269984%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F231546%22%20target%3D%22_blank%22%3E%40Joseph%20Pilov%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20wonderful%20write-up%20and%20it%20has%20brought%20me%20much%20closer%20to%20understanding%20the%20fundamentals%20of%20Sessions%20vs%20Connections.%20While%20reading%20your%20original%20post%2C%20I%20was%20cross%20referencing%20the%20Microsoft%20Docs%20and%20I%20was%20wondering%20if%20you%20could%20help%20me%20better%20understand%20one%20of%20your%20points.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegarding%20a%20session%20being%20potentially%20broken%20up%20into%20multiple%20rows%20due%20to%20what%20you%20called%20a%20%22parallel%20query%22%2C%20would%20you%20happen%20to%20have%20some%20example%20which%20I%20can%20run%20that%20will%20result%20in%20multiple%20sessions%20in%20the%20sessions%20DMV%20for%20demonstration%20purposes%3F%20Is%20your%20term%20%22parallel%20query%22%20the%20same%20as%20%22parallel%20processing%22%20on%20a%20single%20query%20statement%2C%20or%20something%20different%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20just%20wanted%20to%20mention%20that%20in%20my%20research%20and%20cross-referencing%20I%20found%20that%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-exec-sessions-transact-sql%3Fview%3Dsql-server-ver15%23relationship-cardinalities%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esys.dm_exec_sessions%3C%2FA%3E%20has%20a%20potential%20to%20have%20a%20one-to-zero%20relationship%20to%20connections%2C%20meaning%20a%20session%20does%20not%20necessarily%20have%20to%20be%20associated%20with%20a%20connection.%20This%20wasn't%20mentioned%20in%20your%20post%20but%20I%20thought%20it%20would%20make%20a%20great%20addition%20here.%20The%20only%20example%20I%20could%20locate%20of%20this%20are%20what%20are%20referred%20to%20as%20%22%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Farchive%2Fblogs%2Fpsssql%2Fhow-it-works-system-sessions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESystem%20Sessions%3C%2FA%3E%22.%20I%20don't%20understand%20what%20these%20are%20but%20based%20on%20the%20name%2C%20I%20assume%20these%20are%20sessions%20required%20for%20the%20system%20running%20the%20SQL%20Server%20instance%20to%20operate%20that%20are%20separate%20from%20any%20user%20connection.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20query%20to%20view%20those%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3ESELECT%20*%0AFROM%20sys.dm_exec_sessions%20%0AWHERE%20is_user_process%20%3D%200%3B%3C%2FPRE%3E%3CP%3EThanks%20for%20your%20great%20post!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1273060%22%20slang%3D%22en-US%22%3ERe%3A%20Tasks%2C%20Workers%2C%20Threads%2C%20Scheduler%2C%20Sessions%2C%20Connections%2C%20Requests%20%3B%20what%20does%20it%20all%20mean%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1273060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603733%22%20target%3D%22_blank%22%3E%40Database_Nova%3C%2FA%3E%26nbsp%3B%2C%20thanks%20for%20the%20feedback.%20It%20helped%20me%20clarify%20the%20points%20you%20were%20asking%20about%20-%20I%20appreciate%20it.%20See%20the%20updated%20write-up.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20parallel%20queries%20is%20the%20same%20as%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fquery-processing-architecture-guide%3Fview%3Dsql-server-ver15%23parallel-query-processing%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eparallel%20processing%3C%2FA%3E%26nbsp%3Bdone%20by%20SQL%20Server.%20You%20submit%20a%20query%20and%20if%20it%20is%20%22too%20expensive%22%20from%20optimizer%20point%20of%20view%20AND%20if%20it%20qualifies%20for%20parallel%20processing%2C%20SQL%20Server%20will%20break%20up%20the%20work%20among%20multiple%20threads.%20As%20a%20simple%20example%2C%20let's%20say%20you%20submit%20a%20query%20against%20a%20huge%20table%20and%20want%20to%20get%20all%20rows%20and%20perform%20aggregation%26nbsp%3B%20select%20sum%20(sale_amount)%20from%20InternetSalesTbl.%20SQL%20Server%20could%20take%20that%20clustered%20index%20scan%20and%20split%20it%20among%208%20threads%2C%20with%20each%20of%20the%20threads%20scanning%20a%20part%20of%20the%20table%20%2C%20in%20parallel%20with%20the%20other%20threads.%20That%20way%20you%20get%20the%20work%20done%20ideally%208%20times%20faster%20(not%20always%20the%20reality%20because%20there%20is%20overhead%20to%20parallelism%20and%20because%20distribution%20of%20the%20data%20among%20all%20threads%20may%20not%20be%20an%20even%201%2F8%20of%20the%20table%20-%20it%20is%20driven%20by%20statistics).%20The%20same%20MicrosoftDoc%20I%20quoted%20above%20provides%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fquery-processing-architecture-guide%3Fview%3Dsql-server-ver15%23parallel-query-example%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ean%20example%3C%2FA%3E%20for%20you%20to%20look%20at.%3CBR%20%2F%3E%3CBR%20%2F%3EYes%2C%20good%20point-%20system%20sessions%26nbsp%3B%20(those%20created%20by%20SQL%20Server%20for%20internal%20operations%20like%20checkpoint%2C%20lazy%20writer%2C%20and%20many%20others)%20are%20not%20invoked%20by%20an%20external%20client%20application.%20In%20other%20words%20no%20external%20connection%20was%20open%20to%20SQL%20Server%20to%20get%20those%20started.%20Therefore%20your%20statement%20about%201%3A0%20mapping%20is%20correct%20-%20a%20session%20exists%2C%20but%20no%20corresponding%20connection%20to%20it.%20Thanks%20for%20reminding%20me%20of%20this%20scenario.%20I%20have%20now%20captured%20it%20in%20the%20write%20up%20above.%20Thanks%20for%20helping%20make%20this%20write%20up%20better%20for%20the%20benefits%20of%20many.%3C%2FP%3E%0A%3CP%3EYour%20query%20example%20is%20accurate%2C%20but%20the%20older%20sysprocesses%20system%20view%20provides%20a%20much%20better%20insight%20as%20to%20what%20those%20system%20sessions%20are%20doing.%20Here%20is%20a%20query%20to%20help%20visualize%20that%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Eselect%20spid%2C%20kpid%2C%20lastwaittype%2C%20login_time%20%3CBR%20%2F%3Efrom%20sysprocesses%0Awhere%20spid%20%26lt%3B%2050%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-333990%22%20slang%3D%22en-US%22%3ETasks%2C%20Workers%2C%20Threads%2C%20Scheduler%2C%20Sessions%2C%20Connections%2C%20Requests%20%3B%20what%20does%20it%20all%20mean%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Dec%2013%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3EWith%20this%20meditation%20I%20attempt%20to%20explain%20what%20some%20of%20the%20more%20common%20concepts%20that%20get%20used%20with%20SQL%20Server%20thread%20management%20and%20scheduling%20are.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3EParable%3A%20%3C%2FSTRONG%3E%20There%20was%20an%20all-powerful%2C%20but%20humble%20and%20benign%20Master%2C%20whom%20the%20workers%20revered%20and%20humbly%20served.%20The%20master%20accepted%20requests%20from%20other%20kingdoms%20and%20graciously%20agreed%20to%20grant%20all%20of%20them.%20To%20do%20so%20the%20Master%20assigned%20tasks%20to%20his%20workers%20(servants)%20who%20completed%20them%20cooperating%20with%20each%20%E2%80%93%20allowing%20each%20other%20graciously%20to%20approach%20the%20Master%20one%20at%20a%20time.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1597830075%22%20id%3D%22toc-hId-1597830075%22%3E%3CSTRONG%3E%20Components%3A%20%3C%2FSTRONG%3E%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScheduler%20%3C%2FSTRONG%3E%20(SOS%20Scheduler)%E2%80%93%20the%20object%20that%20manages%20thread%20scheduling%20in%20SQL%20Server%20and%20allows%20threads%20to%20be%20exposed%20to%20the%20CPU%20(described%20in%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms177526.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20sys.dm_os_schedulers%20%3C%2FA%3E%20).%20This%20is%20the%20all-powerful%20but%20benign%20and%20graceful%20master%20whom%20everyone%20abides.%26nbsp%3B%20He%20does%20not%20control%20things%20but%20lets%20the%20workers%20work%20with%20each%20other%20and%20relies%20on%20their%20cooperation%20(co-operative%20scheduling%20mode).%20Each%20scheduler%20%2Fmaster%20(one%20per%20logical%20CPU)%20accepts%20new%20tasks%20and%20hands%20them%20off%20to%20workers.%20SOS%20Scheduler%20allows%20one%20worker%20at%20a%20time%20to%20be%20exposed%20to%20the%20CPU.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3ETask%20%3C%2FSTRONG%3E%20%E2%80%93a%20task%20represents%20the%20work%20that%20needs%20to%20be%20performed%20(%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms174963.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20sys.dm_os_tasks%20%3C%2FA%3E%20).%20A%20task%26nbsp%3Bcontains%20one%20of%20the%20following%20events%3A%20%3CEM%3E%20query%20%3C%2FEM%3E(RPC%20event%20or%20Language%20event)%2C%20%3CEM%3E%20a%20prelogin%20%3C%2FEM%3E(prelogin%20event)%2C%26nbsp%3B%20a%20%3CEM%3E%20login%20%3C%2FEM%3E(connect%20event)%2C%20a%20%3CEM%3E%20logout%26nbsp%3B%20%3C%2FEM%3E(disconnect%20event)%2C%20a%20%3CEM%3E%20query%20cancellation%20%3C%2FEM%3E(an%20Attention%20event)%2C%20a%20%3CEM%3E%20bulk%20load%20%3C%2FEM%3E(bulk%20load%20event)%2C%20a%20%3CEM%3E%20distributed%20transaction%20%3C%2FEM%3E(transaction%20manager%20event).%20A%20task%20is%20what%20the%20Master%20is%20about%20%E2%80%93%20it%20is%20what%20defines%20its%20existence.%20Note%20these%20are%20tracked%20at%20the%20SOS%20scheduler%20layer%20(thus%20dm_OS_tasks)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3E%20Worker%20%3C%2FSTRONG%3E%20(worker%20thread)%20%E2%80%93%20This%20is%20the%20logical%20SQL%20Server%20representation%20of%20a%20thread%20(think%20of%20it%20as%20a%20wrapper%20on%20top%20of%20the%20OS%20thread).%20It%20is%20a%20structure%20within%20the%20Scheduler%20which%20maintains%20SQL%20Server-specific%20information%20about%20what%20a%20worker%20thread%20does.%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms178626.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esys.dm_os_workers%20%3C%2FA%3E%20.%20Workers%20are%20the%20humble%20servants%20who%20carry%20out%20the%20task%20assigned%20to%20them%20by%20the%20Master%20(scheduler).%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3EThread%20%3C%2FSTRONG%3E%20%E2%80%93%20this%20is%20the%20OS%20thread%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms187818.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20sys.dm_os_threads%20%3C%2FA%3E%20that%20is%20created%20via%20calls%20like%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms682453(VS.85).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20CreateThread()%20%3C%2FA%3E%20%2F%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fkdzttdcb(v%3DVS.80).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20_beginthreadex()%20%3C%2FA%3E%20.%20A%20Worker%20is%20mapped%201-to-1%20to%20a%20Thread.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3ERequest%20%3C%2FSTRONG%3E%20is%20the%20logical%20representation%20of%20a%20%3CEM%3E%20query%20request%20%3C%2FEM%3E%20made%20from%20the%20client%20application%20to%20SQL%20Server%20(%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms177648(v%3Dsql.105).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20sys.dm_exec_requests%20%3C%2FA%3E%20).%20This%20query%20request%20has%20been%20assigned%20to%20a%20task%20that%20the%20scheduler%20hands%20off%20to%20a%20worker%20to%20process.%20This%20represents%20query%20requests%20as%20well%20as%20system%20thread%20operations%20(like%20checkpoint%2C%20log%20writer%2C%20etc)%3B%26nbsp%3Byou%20will%20not%20find%20login%2C%20logouts%2C%20attentions%20and%20the%20like%26nbsp%3Bhere.%26nbsp%3BAlso%2C%20note%20that%20this%20is%20a%20representation%20at%20the%20SQL%20execution%20engine%20level%20(thus%20dm_EXEC_requests)%20not%20at%20the%20SOS%20Scheduler%20layer.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSTRONG%3ESessions%20%3C%2FSTRONG%3E%20%E2%80%93%20when%20the%20client%20application%20connects%20to%20SQL%20Server%20the%20two%20sides%20establish%20a%20%22session%22%20on%20which%20to%20exchange%20information.%20Strictly%20speaking%20a%20session%20is%20not%20the%20same%20as%20the%20underlying%20physical%20connection%2C%20it%20is%20a%20SQL%20Server%20logical%20representation%20of%20a%20connection.%20But%20for%20practical%20purposes%2C%20you%20can%20think%20of%20this%20as%20being%20a%20connection%20(session%20%3D~%20connection)%2C%20see%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms176013(v%3Dsql.105).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20sys.dm_exec_sessions%20%3C%2FA%3E%20.%20This%20is%20the%20old%20SPID%20(session%20process%20id)%20that%20existed%20in%20SQL%20Server%202000%20and%20earlier.%20In%20the%20case%20of%20system%20sessions%20(internal%20sessions%20spawned%20by%20SQL%20Server%20like%20LazyWriter%2C%20Checkpoint%2C%20Log%20Writer%2C%20Service%20Broker%2C%20etc)%2C%20no%20external%20physical%20connection%20is%20mapped%20to%20the%20session.%20Only%20a%20session_id%20exists.%20Typically%20reserved%20for%20session%20IDs%20%26lt%3B%2050.%3CBR%20%2F%3EYou%20may%20sometimes%20notice%20a%20single%20session%20repeating%20multiple%20times%20in%20a%20DMV%20output.%20This%20happens%20because%20of%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fquery-processing-architecture-guide%3Fview%3Dsql-server-ver15%23parallel-query-processing%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eparallel%20queries%3C%2FA%3E.%20A%20parallel%20query%20uses%20the%20same%20session%20to%20communicate%20with%20the%20client%2C%20but%20on%20the%20SQL%20Server%20side%20multiple%20worker%20threads%20are%20assigned%20to%20service%20this%20query%20request.%20So%20if%20you%20see%20multiple%20rows%20with%20the%20same%20session%20ID%2C%20know%20that%20the%20query%20request%20is%20being%20serviced%20by%20multiple%20threads.%26nbsp%3B%20A%20session_id%20(SPID)%20is%20used%20to%20identify%20the%20work%20performed%20inside%20SQL%20Server.%20For%20example%2C%20you%20may%20want%20to%20find%20out%20which%20session_id%20is%20executing%20a%20query%2C%20or%20which%20session_id%20has%20its%20query%20cancelled.%26nbsp%3B%20Some%20of%20the%20properties%20of%20a%20sesssion%20include%20login%20time%2C%20last%20request%20(query)%20time%2C%20CPU%20consumed%20%2C%20memory%20used%2C%20and%26nbsp%3B%20total%20elapsed%20time%20used%20by%20a%20query%20or%20set%20of%20queries%20on%20this%20session%2C%20user%20name%2C%20SET%20options%20configured%20for%20this%20session.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%20Connections%20%E2%80%93%20this%20is%20the%20actual%20physical%20connection%20established%20at%20the%20lower%20protocol%20level%20with%20all%20of%20its%20characteristics%20sys.dm_exec_connections%20.%20There%20is%20a%201%3A1%20mapping%20between%20a%20Session%20and%20a%20Connection.%20A%20connection%20has%20some%20of%20the%20following%20properties%20-%20protocol%20(Shared%20Memory%2C%20TCP%2C%20Named%20Pipes)%2C%20authentication%20type%20(NTLM%2C%20Kerberos)%2C%20Encryption%20(on%20or%20off)%2C%20Network%20packet%20size%2C%20client%20IP%20address%20and%20port.%20These%20are%20all%20physical%20properties%20of%20the%20connection.%20A%20connection_id%20in%20sys.dm_exec_connections%20is%20a%20GUID%20and%20is%20used%20to%20uniquely%20identify%20that%20physical%20connection.%20You%20typically%20won't%20use%20a%20connection_id%20to%20identify%20which%20session%20is%20executing%20a%20query%3B%20a%20session_id%20is%20used%20instead.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--954326886%22%20id%3D%22toc-hId--954326886%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-788483449%22%20id%3D%22toc-hId-788483449%22%3E%3CSTRONG%3E%20Interconnection%20between%20the%20Components%3A%20%3C%2FSTRONG%3E%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20client%20application%20creates%20a%20physical%20connection%20to%20SQL%20Server.%20Then%20the%20application%20sends%20a%20pre-login%20request%20and%20a%20task%20is%20created%20and%20assigned%20to%20a%20worker%20to%20fulfill.%20Once%20the%20server%20and%20client%20finish%20the%20pre-login%20process%2C%20a%20login%20request%20is%20sent%20and%20another%20task%20is%20formed%20and%20handed%20off%20to%20a%20worker%20thread.%20Once%20the%20login%20is%20completed%2C%20SQL%20Server%20creates%20a%20session%20that%20represents%20this%20logical%20connection%20where%20it%20will%20exchange%20information%20with%20the%20client.%20When%20the%20client%20application%20sends%20a%20query%20request%20(or%20DTC%20or%20bulk%20load)%2C%20the%20server%20again%20creates%20a%20task%20and%20assigns%20it%20to%20a%20worker%20thread%20for%20completion.%20If%20the%20query%20is%20cancelled%20in%20the%20middle%20of%20execution%2C%20for%20some%20reason%2C%20the%20server%20will%20receive%20an%20Attention%20request%20upon%20which%20the%20IOCP%20listener%20will%20mark%20a%20bit%20that%20the%20query%20is%20cancelled%20and%20the%20worker%20that%20was%20running%20the%20query%20would%20stop%20executing%20when%20it%20sees%20the%20bit.%20If%20the%20query%20is%20allowed%20to%20complete%2C%20on%20the%20other%20hand%2C%20and%20the%20client%20application%20is%20done%2C%20it%20can%20send%20a%20disconnect%20or%20logout%20request%20which%20again%20is%20packaged%20as%20a%20task%20and%20serviced%20by%20a%20worker.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENamaste!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJoseph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-333990%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Dec%2013%2C%202012%20With%20this%20meditation%20I%20attempt%20to%20explain%20what%20some%20of%20the%20more%20common%20concepts%20that%20get%20used%20with%20SQL%20Server%20thread%20management%20and%20scheduling%20are.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-333990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EArchitecture%20%26amp%3B%20guidance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

First published on MSDN on Dec 13, 2012
With this meditation I attempt to explain what some of the more common concepts that get used with SQL Server thread management and scheduling are.

Parable: There was an all-powerful, but humble and benign Master, whom the workers revered and humbly served. The master accepted requests from other kingdoms and graciously agreed to grant all of them. To do so the Master assigned tasks to his workers (servants) who completed them cooperating with each – allowing each other graciously to approach the Master one at a time.


Components:

 

Scheduler (SOS Scheduler)– the object that manages thread scheduling in SQL Server and allows threads to be exposed to the CPU (described in sys.dm_os_schedulers ). This is the all-powerful but benign and graceful master whom everyone abides.  He does not control things but lets the workers work with each other and relies on their cooperation (co-operative scheduling mode). Each scheduler /master (one per logical CPU) accepts new tasks and hands them off to workers. SOS Scheduler allows one worker at a time to be exposed to the CPU.

Task –a task represents the work that needs to be performed ( sys.dm_os_tasks ). A task contains one of the following events: query (RPC event or Language event), a prelogin (prelogin event),  a login (connect event), a logout  (disconnect event), a query cancellation (an Attention event), a bulk load (bulk load event), a distributed transaction (transaction manager event). A task is what the Master is about – it is what defines its existence. Note these are tracked at the SOS scheduler layer (thus dm_OS_tasks)

Worker (worker thread) – This is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thread). It is a structure within the Scheduler which maintains SQL Server-specific information about what a worker thread does. sys.dm_os_workers . Workers are the humble servants who carry out the task assigned to them by the Master (scheduler).

Thread – this is the OS thread sys.dm_os_threads that is created via calls like CreateThread() / _beginthreadex() . A Worker is mapped 1-to-1 to a Thread.

Request is the logical representation of a query request made from the client application to SQL Server ( sys.dm_exec_requests ). This query request has been assigned to a task that the scheduler hands off to a worker to process. This represents query requests as well as system thread operations (like checkpoint, log writer, etc); you will not find login, logouts, attentions and the like here. Also, note that this is a representation at the SQL execution engine level (thus dm_EXEC_requests) not at the SOS Scheduler layer.

Sessions – when the client application connects to SQL Server the two sides establish a "session" on which to exchange information. Strictly speaking a session is not the same as the underlying physical connection, it is a SQL Server logical representation of a connection. But for practical purposes, you can think of this as being a connection (session =~ connection), see sys.dm_exec_sessions . This is the old SPID (session process id) that existed in SQL Server 2000 and earlier. In the case of system sessions (internal sessions spawned by SQL Server like LazyWriter, Checkpoint, Log Writer, Service Broker, etc), no external physical connection is mapped to the session. Only a session_id exists. Typically reserved for session IDs < 50.
You may sometimes notice a single session repeating multiple times in a DMV output. This happens because of parallel queries. A parallel query uses the same session to communicate with the client, but on the SQL Server side multiple worker threads are assigned to service this query request. So if you see multiple rows with the same session ID, know that the query request is being serviced by multiple threads.  A session_id (SPID) is used to identify the work performed inside SQL Server. For example, you may want to find out which session_id is executing a query, or which session_id has its query cancelled.  Some of the properties of a sesssion include login time, last request (query) time, CPU consumed , memory used, and  total elapsed time used by a query or set of queries on this session, user name, SET options configured for this session. 

Connections – this is the actual physical connection established at the lower protocol level with all of its characteristics sys.dm_exec_connections . There is a 1:1 mapping between a Session and a Connection. A connection has some of the following properties - protocol (Shared Memory, TCP, Named Pipes), authentication type (NTLM, Kerberos), Encryption (on or off), Network packet size, client IP address and port. These are all physical properties of the connection. A connection_id in sys.dm_exec_connections is a GUID and is used to uniquely identify that physical connection. You typically won't use a connection_id to identify which session is executing a query; a session_id is used instead. 

 

 

 

Interconnection between the Components:

 

A client application creates a physical connection to SQL Server. Then the application sends a pre-login request and a task is created and assigned to a worker to fulfill. Once the server and client finish the pre-login process, a login request is sent and another task is formed and handed off to a worker thread. Once the login is completed, SQL Server creates a session that represents this logical connection where it will exchange information with the client. When the client application sends a query request (or DTC or bulk load), the server again creates a task and assigns it to a worker thread for completion. If the query is cancelled in the middle of execution, for some reason, the server will receive an Attention request upon which the IOCP listener will mark a bit that the query is cancelled and the worker that was running the query would stop executing when it sees the bit. If the query is allowed to complete, on the other hand, and the client application is done, it can send a disconnect or logout request which again is packaged as a task and serviced by a worker.

 

Namaste!

 

Joseph

4 Comments
Occasional Visitor
Few questions - 1. DMV data for Connection & Session appear same since those are 1:1 mapped, unless uniquely used in Troubleshooting/Optimisation Process. Can you share any instance wherein we could use these 2 in unison or separately? 2. In which part of Troubleshooting/Optimisation Process are these DMVs used. Any lead is appreciated and would set a starting point for further understanding. Thank You! --In 'thoughts'...
Microsoft

@LonelyRogue - thank you for your question. It prompted me to add more clarifications to the Session and Connection descriptions in order to differentiate them. Please see if those help and provide feedback. Thank you for helping make this better.

Regular Visitor

Hello @Joseph Pilov ,

 

This is a wonderful write-up and it has brought me much closer to understanding the fundamentals of Sessions vs Connections. While reading your original post, I was cross referencing the Microsoft Docs and I was wondering if you could help me better understand one of your points.

 

Regarding a session being potentially broken up into multiple rows due to what you called a "parallel query", would you happen to have some example which I can run that will result in multiple sessions in the sessions DMV for demonstration purposes? Is your term "parallel query" the same as "parallel processing" on a single query statement, or something different?

 

Also, I just wanted to mention that in my research and cross-referencing I found that sys.dm_exec_sessions has a potential to have a one-to-zero relationship to connections, meaning a session does not necessarily have to be associated with a connection. This wasn't mentioned in your post but I thought it would make a great addition here. The only example I could locate of this are what are referred to as "System Sessions". I don't understand what these are but based on the name, I assume these are sessions required for the system running the SQL Server instance to operate that are separate from any user connection.

 

Here's a query to view those: 

SELECT *
FROM sys.dm_exec_sessions 
WHERE is_user_process = 0;

Thanks for your great post!

 

 

 

 

Microsoft

@Database_Nova , thanks for the feedback. It helped me clarify the points you were asking about - I appreciate it. See the updated write-up.

 

Yes, parallel queries is the same as parallel processing done by SQL Server. You submit a query and if it is "too expensive" from optimizer point of view AND if it qualifies for parallel processing, SQL Server will break up the work among multiple threads. As a simple example, let's say you submit a query against a huge table and want to get all rows and perform aggregation  select sum (sale_amount) from InternetSalesTbl. SQL Server could take that clustered index scan and split it among 8 threads, with each of the threads scanning a part of the table , in parallel with the other threads. That way you get the work done ideally 8 times faster (not always the reality because there is overhead to parallelism and because distribution of the data among all threads may not be an even 1/8 of the table - it is driven by statistics). The same MicrosoftDoc I quoted above provides an example for you to look at.

Yes, good point- system sessions  (those created by SQL Server for internal operations like checkpoint, lazy writer, and many others) are not invoked by an external client application. In other words no external connection was open to SQL Server to get those started. Therefore your statement about 1:0 mapping is correct - a session exists, but no corresponding connection to it. Thanks for reminding me of this scenario. I have now captured it in the write up above. Thanks for helping make this write up better for the benefits of many.

Your query example is accurate, but the older sysprocesses system view provides a much better insight as to what those system sessions are doing. Here is a query to help visualize that

 

select spid, kpid, lastwaittype, login_time 
from sysprocesses where spid < 50