About sp_prepare ?sp_execute and sp_unprepare of SQL Server The records o

  • 892717952


    Points: 442

    The records of the picture is from a SQL trace record(it was saved by the trace file, generated in SQL Server 2008). and the records are sorted by its starttime.

    as I'm not familiar with sp_prepare ?sp_execute and sp_unprepare , could you please tell me what are the uses of them ? and in which situation these 3 system procedures will be executed ?

    and will they be executed together ? if yes, how about the sequence to be executed? (first to exeute sp_prepare, then to execute sp_execute, final to execute sp_unprepare )?


    You must be logged in to view attached files.
  • Erland Sommarskog


    Points: 23827

    You will typically not find calls to these sps in directly in application code, but they are generated under the covers by the client API with certain settings.

    I am not too familiar with them myself, but I think the idea is that if a client wants to run the same query many times, but with different parameters, it can submit the query text once with sp_prepare, run it multiple times with sp_execute and then drop the query with sp_unprepare. But it seems that many times there is only one execution with sp_execute, and in that case you could question the idea.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • 892717952


    Points: 442

    Thank you Erland Sommarskog for your kind help! and waiting some experts for detailed explanations.

    there are many confusions for me aside from ones mentioned above. when thoese stored procedures called, I don't know the value passed for each parameter.



Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply