SQL Server Performance Forum – Threads Archive

STORED PROCEDURE ANALYSIS

CAN 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
ALWAYS HAPPYNEES
HHAAPPYY

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hi satya
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
event class
textdata
applicationname
ntusername
loginname
cpu
reads
write
duration
startname
endname
nested level and the the standard
SQLProfilerTSQL_Duration and SQLProfilerStandard from sql server
thanks
HHAAPPYY

I suggest to include: Store Procedures SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted TSQL SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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?
thanks HHAAPPYY

in addition to it i would like to know is it good practice to call user’s defined function int he stored procedure? thanks
HAPPY

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
HP

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
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
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
Hewlett-Packard

thanks guys for your tips
If i need any furthur infomation i will call you here only
thanks
HHAAPPYY

]]>