May 5, 2012 at 1:11 pm
Comments posted to this topic are about the item Stored Procedures Performance
May 7, 2012 at 10:38 am
This is a good script.
One suggested addition would be a 'schemaname' column. When I'm unit testing in Development, I often times have a different version of the stored procedure contained under different schema(s), so I can compare their runtime side by side.
select
...
os.schemaname,
...
join (select object_id id, schema_name(schema_id)schemaname
from sys.objects) os on os.id = object_id
...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 7, 2012 at 10:52 am
adnan.korkmaz (5/5/2012)
Comments posted to this topic are about the item <A HREF="/scripts/Stored+Procedures/90129/">Stored Procedures Performance</A>
Good script..
This will only work for SQL server 2008 and plus.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
May 8, 2012 at 3:54 am
Good One ....
May 22, 2012 at 9:17 pm
i run it on SQLServer2008 R2, why received the msg:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_exec_procedure_stats'.
May 23, 2012 at 12:42 am
Nice script.
If parallel plan is used in the procedure for any query(ies) then the cpu time doesnt reflect actual cpu time but of the thread which started the proc execution. Thus it sometimes is misleading...
http://www.sqlservercentral.com/Forums/Topic1304371-391-1.aspx
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 23, 2012 at 12:55 am
Hi @caoxp930,
Two possibilities that I can think of:
1) You might have ran the query on a SQL 2005 server, via SQL 2008 interface. The sys.dm_exec_procedure_stats doesn't exist on SQL 2005.
2) You don't have VIEW SERVER STATE permission on the server you are running the query.
November 22, 2012 at 9:55 am
Eric M Russell (5/7/2012)
This is a good script.One suggested addition would be a 'schemaname' column. When I'm unit testing in Development, I often times have a different version of the stored procedure contained under different schema(s), so I can compare their runtime side by side.
select...
os.schemaname,
...
join (select object_id id, schema_name(schema_id)schemaname
from sys.objects) os on os.id = object_id
...
Good suggestion but no need to join unnecessarily - use OBJECT_SCHEMA_NAME([object_id])
😉
gsc_dba
October 1, 2014 at 6:21 am
Very nice. 🙂
October 1, 2014 at 10:11 am
Use extreme prejudice when and where you run DBCC FREEPROCCACHE! SQL Books Online sums this up quite nicely:
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
May 21, 2015 at 1:22 pm
This is a pretty cool script. Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply