March 2008 Entries
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_handlewhere objtype = 'Proc' ...
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...
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) twhere 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/minuteSELECT 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...
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...
EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1 RECONFIGURE WITH OVERRIDEGOEXEC sp_configure @configname = 'clr enabled', @configvalue = 1 RECONFIGURE WITH OVERRIDEGOEXEC sp_configure go
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 output
file
using (FileStream
u...