Post reply

SP3 Sysprocesses Problem

  • September 23, 2003 at 8:58 am

    #64944

    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

    #475560

    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

    #475856

    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

    #475928

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy