 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
Return to: General |
|
***************************************************** |
|
#Post#: 82-------------------------------------------------- |
|
What�s the optional_spid in sys.dm_exec_plan_attributes? |
|
By: srinivasma_exceldbp Date: July 21, 2014, 11:41 pm |
|
--------------------------------------------------------- |
|
Queries accessing non-dynamically bound temp tables, if issued |
|
from different connections would recompile to bind to the right |
|
temp table. This would cause a lot of recompiles that would have |
|
a performance impact for a workload that uses such mechanics. |
|
For example: |
|
-- connection 1 |
|
create table #t (a int) |
|
go |
|
insert into #t values (2) |
|
go |
|
select * from #t -- This would return 2. |
|
go |
|
-- connection 2 |
|
create table #t (a int) |
|
go |
|
insert into #t values (5) |
|
go |
|
select * from #t -- This would return 5 |
|
go |
|
In the above scenario, the select query in connection 1 will get |
|
cached. If now the select query in connection 2 is issued, it |
|
will cause a recompile. If, subsequently, the select query in |
|
connection 1 is reissued, it will cause another recompile. |
|
To prevent the recompiles, the spid ( optional_spid ) in which |
|
a query that accesses non-dynamically bound temp tables is |
|
issued. |
|
***************************************************** |
