Roy Ashbrook

let go or be dragged
posts - 197, comments - 29, trackbacks - 8

March 2008 Entries

how can i get some performance statistics on my stored procedures in sql2005?

select     case when dbid = 32767         then 'resource'         else db_name(dbid)     end [db_name]     , object_schema_name(objectid,dbid) [schema_name]     , object_name(objectid,dbid) [object_name]     , sum(usecounts) [executions]     , max(max_execution_time) [last_execution_time]     , sum(total_logical_reads) [total_logical_reads]     , sum(total_logical_reads) / sum(usecounts) * 1.0 [avg_logical_reads]     , sum(total_elapsed_time) / 1000 [total_elapsed_time_ms]     , ( sum(total_elapsed_time) / sum(usecounts) * 1.0 ) / 1000 [avg_elapsed_time_ms] from     sys.dm_exec_cached_plans cp     cross apply sys.dm_exec_sql_text(cp.plan_handle) qt     join (         select             plan_handle             , max(last_execution_time) [max_execution_time]             , sum(total_elapsed_time) [total_elapsed_time]             , sum(total_logical_reads) [total_logical_reads]         from             sys.dm_exec_query_stats         group by             plan_handle         ) r on r.plan_handle = cp.plan_handle where     objtype = 'Proc'    ...

posted @ Friday, March 21, 2008 8:57 PM | Feedback (0) | Filed Under [ DBA ]

SQL Server 2005 Emergency Diagnostic and Performance Queries

Great useful base diag scripts! Snipped from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry -- SQL Server 2005 Emergency Diagnostic and Performance Queries-- Glenn Berry 3-17-2008 -- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of time?-- If yes, run HIGH CPU queries below:-- Step 2 - Check Performance Monitor-- SQL Server Buffer Manager: Buffer Cache Hit Ratio and Page Life Expectancy-- SQL Server Memory Manager: Memory Grants Pending and Memory Grants Pending-- Physical Disk: Avg disk sec/Read and Avg disk sec/Write-- Step 3 - Check for locking, blocking and missing indexes-- Run the...

posted @ Friday, March 21, 2008 7:19 PM | Feedback (0) | Filed Under [ DBA ]

how can i find when a stored procedure was last executed in sql 2005?

select top 10     t.text     , s.last_execution_time     , * from     sys.dm_exec_query_stats s     cross apply sys.dm_exec_sql_text(s.sql_handle) t where     t.objectid is not null     and text like '%procname%' order by     s.last_execution_time desc   obviously replace procname with the procname you want to find. you might get multiple results, and you can probably clean it up some, but it works fine for what i generally need to get the info for. while i was researching this, i also came accross this pretty cool query:     -- Get Top 200 executed SP's ordered by calls/minute SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS...

posted @ Friday, March 21, 2008 6:22 PM | Feedback (0) | Filed Under [ DBA ]

how to create an extended stored proc in c#

quoted from http://www.dotnet247.com/247reference/msgs/7/38630.aspx Ok, to get you started, herewith a simple sample, the C# file contains a single class and one method.After compiling the file, you need to register the class using regasm.Regasm requires the assembly to have a strong name, therefore you ned to run sn -k to create a assembly key file (here calledMyKey.snk).Once the class registered, load the stored procedure in SQL queryanalyzer and run the Proc.More info on COM interop is found in the MSDN docs.C# file:using System;using System.Runtime.InteropServices;using System.Reflection;using System.Runtime.CompilerServices;[assembly: AssemblyTitle("CSServer")][assembly: AssemblyDescription("Test SQL .NET interop")][assembly: AssemblyVersion("1.0.0.1")][assembly: AssemblyDelaySign(false)][assembly: AssemblyKeyFile("MyKey.snk")]namespace SQLInterop {public interface ITest{string SayHello();}[ClassInterface(ClassInterfaceType.AutoDual)]public class CsharpHelper...

posted @ Wednesday, March 19, 2008 6:47 PM | Feedback (0) |

enable clr procs on sql2005

EXEC sp_configure     @configname = 'Show Advanced Options',     @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure     @configname = 'clr enabled',     @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure go

posted @ Wednesday, March 19, 2008 6:26 PM | Feedback (0) | Filed Under [ DBA ]

multiple objects in a using and compression sample

Found something nifty I didn't know about today. create multiple objects in a single using statement. I figured i would include some sample code i use for compression/decompression. I'm using the #ziplib libraries, but you could use any stream based process really. The bufferSize variable you can change to fit your needs.      //unzip data. assume we have a compressed file and are creating the output file using (FileStream     u = File.Create(PathToUncompressedFile),     c = File.OpenRead(PathToCompressedFile)) using (GZipInputStream gzip = new GZipInputStream(c))     while ((bufferSize = gzip.Read(buffer, 0, buffer.Length)) != 0)         u.Write(buffer, 0, bufferSize);   //zip data. assume we have a uncompressed file and are creating the...

posted @ Saturday, March 8, 2008 1:52 AM | Feedback (0) | Filed Under [ DEV ]

Powered by: