Find Inefficient T-SQL Stored Procedures

The following is a stored procedure you can use when you want to quickly identify procedures in your SQL Server database which are taking up excessive CPU time.

The script returns 3 recordsets:

  • The number of active connections by database name, with login name and hostname
  • All stored procedures with execution count and total/average CPU times
  • All stored procedures with average and total elapsed time
CREATE PROCEDURE [dbo].[procProcessTimes]ASSELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName , HostnameFROM    sys.sysprocesses WHERE     dbid > 0 GROUP BY     dbid, loginame, HostnameSELECT DB_NAME(st.dbid) DBName      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure      ,max(cp.usecounts) Execution_count      ,sum(qs.total_worker_time) total_cpu_time      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time  FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)  order by sum(qs.total_worker_time) descSELECT DB_NAME(st.dbid) DBName      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure      ,max(cp.usecounts) execution_count      ,sum(qs.total_elapsed_time) total_elapsed_time      ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)  order by sum(qs.total_elapsed_time) desc

English to Scots Translations in your .NET Project

You can now use the Scotranslate dictionary in your own project using the new developer API.  You simply pass in the English phrase for translation and the system will return your phrase back in broad Scots!

You can use this to translate your entire website, or provide your own English to Scots translation service to your users.

What’s more, it couldn’t be easier to use:

Step 1

Add a web reference to the following web service from within Visual Studio 2005 or 2008 and call it Scotranslate.

http://services.scotranslate.com/Translator.asmx

Step 2

Create an instance of the translator object

C#

Scotranslate.Translator MyTranslator = new Scotranslate.Translator();

VB
 

Dim MyTranslator As New Scotranslate.Translator()

Step 3

Pass your string to the Translator object you just created and receive the translated text

C#

string translatedString = "";
translatedString = MyTranslator.Translate(MyString);

VB

Dim translatedString As String = ""
translatedString = MyTranslator.Translate(MyString)

Scotranslate’s vision is to be the largest, most relevant English to Scottish translator. For more information about Scotranslate, click here.

http://my.captua.com/xspf_player_slim.swf?playlist_url=http://my.captua.com/OlderMixdown.mp3.xml&player_title=Captua%20Audio%20Player