SQL Server Performance Forum – Threads Archive
STORED PROCEDURE ANALYSISCAN ANY BODY HELP ME ,
I AM LOOKING FOR ANALYSING AN COMPLEX STORED PROCEDURE, I WANT TO KNOW HOW THE QUERIES BEHAVE IN THE SP .HOW MUCH TIME TIME AND HOW MANY READS AND WRITE TAKEN PLACE FOR EACH TEMP TABLE WHIHC I USED IN THE SP. WHAT THE DURATION OF TIME SPENT IN TEMP TABLE . I TRIED PROFILER , BUT NOT ABLE TO GET IT I NEED IT VERY UGRENTLY
What kind of events you’ve captured using PROFILER?
On the query analyzer you can paste the SP and check the execution plan about the process. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
thanks for u r reply
i used both these
SQLProfilerTSQL_Duration and SQLProfilerStandard
how i can check On the query analyzer you can paste the SP and check the execution plan about the process thnaks hhaappyy
hey i used session——-> existing connection
stored procedure —–> rpc complete
TSQL–> BatchComplete and datacolumns are
nested level and the the standard
SQLProfilerTSQL_Duration and SQLProfilerStandard from sql server
I suggest to include:<br />Store Procedures<br />SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted<br /><br />TSQL<br />SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided â€œAS ISâ€ with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
thanks Luis i did that also is there any way to find what I am looking for 1.HOW QUERIES BEHAVE IN THE SP?
2.HOW MUCH TIME TIME AND HOW MANY READS AND WRITE TAKEN PLACE FOR EACH TEMP TABLEs WHICH I USED IN THE SP?
3.WHAT THE DURATION OF TIME SPENT IN TEMP TABLEs?
in addition to it i would like to know is it good practice to call user’s defined function int he stored procedure? thanks
like mentioned above paste the Stored Procedure in the Query Analyzer Window and hit Control+L this will display the estimated execution plan in a graphical format. Also you can use this SET command. SET SHOWPLAN_ALL ON SQL Statement……… This will also give you detailed info on the SP. I would recommend staying away from UDF’s if you can, they are known to be resource intensive. Raulie
Step by step. 1) Is no easy because when you see a query, in profiler, if that query is the only one (suppose that) when finish you have to see RPC completed belong to that query. But may be there is a lot of queries belongs to other stores procedures and is hard to know to witch one belong to one sp. 2) Since there #aux tables in sp, those tables are written to tempdb. You can filter temdb database using database id. Also is good practice to improve performance, aislate that query and paste to Query Analyzer to see executio plan. Example: Some sp…. insert into #auxtable select ………… Copy and paste from select to end to see execution plan and find indexs if any. 3) Same 2) about filtering by temdb id. There is a good freeware tool: Spotlight from Quest. You can see a lot with that tool, also sp in cache. HTH
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
All postings are provided â€œAS ISâ€ with no warranties for accuracy.
Check out this article on UDF performance cost http://www.winnetmag.com/SQLServer/Article/ArticleID/25630/25630.html Saludos Raulie
thanks guys for your tips
If i need any furthur infomation i will call you here only