September 23, 2003 at 8:58 am
I read Kalen Delaney's SP3 Sysprocesses article in SQL Mag (Sept. 2003) InstantDoc ID 39664. I REALLY want to use the ::fn_get_sql (@Handle). However, I have multiple entries in sysprocesses per SPID, so I have multiple sql_handle's per SPID. Even if I place the sql_handle directly into the ::fn_get_sql(@Handle), I still get nothing. And these are statements that should be in cache. Anyone try out her thingy and get results? Or can explain why I can't get any results?
Thanks,
September 24, 2003 at 4:56 am
I tried it out, and it works.... sometimes!
On a lot of occasions, sysprocesses returned a zero sql_handle, and therefore fn_get_sql returned nothing.
fn_get_sql will only return the SQL if the query plan is cached. Zero cost plans don't get cached, but there is a new trace flag (2861) that is supposed to cache plans with zero cost.
I tried that, and it made no difference. I tried a few queries, simple and complex. The simple query always returned a sql_handle, but the complex query didn't.
I suspect this is something to do with lack of memory to cache the plan, because the same queries run on a machine with more memory returned a valid sql_handle.
September 25, 2003 at 11:50 am
Thank you! Now I'm wondering about the memory requirements for my Data Warehousing database. I've got 4 GB, and I still wasn't able to get the code for a long running query to return a valid sql_handle. Guess I'm going to have to corner Kalen at PASS for a more detailed anlaysis of this new option in sp3.
September 26, 2003 at 1:08 am
If you do get any more information from Kalen, please post it. I'm trying to make use of fn_get_sql in some production monitoring.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply