Why does sa_conn_properties() return a value of TempFilePages for the current connection that is wildly different from CONNECTION_PROPERTY ( 'TempFilePages' ) which should return the same value?
It may not be fair for the following query to call it "Incorrect TempFilePages" because (I think) it is a snapshot of the TempFilePages value when it has been momentarily inflated by the process of calculating the sa_conn_properties() result set itself.
HOWEVER, if that is the explanation, perhaps sa_conn_properties() could be modified to avoid using that inflated value.
SELECT @@VERSION, CONNECTION_PROPERTY ( 'TempFilePages' ) AS "Correct TempFilePages", Value AS "Incorrect TempFilePages" FROM sa_conn_properties() WHERE PropName = 'TempFilePages' AND Number = @@SPID; @@VERSION,Correct TempFilePages,Incorrect TempFilePages '220.127.116.1110','14',794
asked 22 Feb '13, 15:38