script to quickly find out which spid is using most CPU and/or IO and what that SPID is doing

Technorati Tags: SQL Server,CPU,DBA

Hey Guys,

This is a bit off-topic, and specific to SQL Server, but this script has been very useful to me when the server is running slow.  Run the first part up to the 2^nd dashed line all together and then use the identified SPID(s) in the queries below the line to see what it is doing:

------identify spid with highest cpu and io usage-----
SELECT spid, sum(cpu)as cpu
into #temp1
FROM master.dbo.sysprocesses
group by spid
WAITFOR DELAY '0:0:0.3';
SELECT spid, sum(cpu)as cpu
into #temp2
FROM master.dbo.sysprocesses
group by spid

select t3.spid, t4.cpu - t3.cpu diff
from #temp1 t3 inner join #temp2 t4 on t3.spid = t4.spid
order by diff desc

SELECT spid, sum(physical_io)as physical_io
into #temp3
FROM master.dbo.sysprocesses
group by spid
WAITFOR DELAY '0:0:0.3';
SELECT spid, sum(physical_io)as physical_io
into #temp4
FROM master.dbo.sysprocesses
group by spid

select t3.spid, t4.physical_io - t3.physical_io diff
from #temp3 t3 inner join #temp4 t4 on t3.spid = t4.spid
order by diff desc

drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
-------------------------------------------------------------------------------------
--NOW, to see what the process is ACTUALLY DOING:
--same as Activity Monitor (use from ANY db)
select * from master..sysprocesses where spid=73

--same as 'details' from Activity Monitor
DBCC inputbuffer(73) --from any db

--interesting - similar to above, but with variables, if used, instead of actual values (e.g. @strDate)
--*PLUS* this shows you the CURRENT procedure running, not just the wrapper procedure like above
--SQL2000:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = 73
SELECT * FROM ::fn_get_sql(@Handle) --seems to cut off text at some point
--SQL2005 (doesn't always return same as sql2000 format)
SELECT session_id, text
FROM sys.dm_exec_requests AS r
     CROSS APPLY
     sys.dm_exec_sql_text(sql_handle) AS s
WHERE session_id = 73

Hello world! (and why you should use the Decimal data type and not Money in SQL Server)

Hi!  This blog is called “The Order of SQL” as both a reference to the community of SQL users I hope to cultivate here and to an upcoming e-book I am working on that explains in simple terms exactly how a SQL query is processed and in what order (no, it is NOT all processed simultaneously).

I will try to stick to ANSI SQL as close as possible, but since my focus professionally is on SQL Server , I may occasionally add in a tidbit or two specific to that platform.  Hence, today, we discuss an important “gotcha” in SQL Server around the currency datatype.

Check this example out:

declare @m money
declare @d decimal(9,2)

set @m = 19.34
set @d = 19.34

select (@m/1000)*1000 as money, (@d/1000)*1000 as decimal

RESULTS:
money     decimal
19.30       19.3400000

It should be obvious, that if we start with ANY number (19.34 in this case), and then simply divide by ANY number (1000 in this case), and then multiply by the SAME number we divided with (1000), we should always end up with the number we started with.

So, what happened with the “money” column?  Where did the .04 go?

 

The answer is related to how the money (aka currency) data type is stored: it has a “scale” of only 4.  This means that it can only store 4 decimals after the decimal point.

 

So, here is what happened:

19.34/1000 = .01934  BUT we only have 4 decimal places, so, this is truncated to .0193.

THEN, .0193 * 1000 = 19.30!

 

Some of you may now being saying, “Wait!  The decimal variable has even LESS scale (2).  How did it maintain all the decimals?!”

 

This is apparently due to the fact that although the variable @d can only store 2 decimals, the intermediate results of a computation involving that variable can indeed be stored beyond that limitation.  The problem with the currency data types is that SQL Server apparently chooses to store those intermediate results at the same scale as the money variable itself, hence the truncation of the 4 from the end of .01934.

 

The lesson?  Just use Decimal (9,2) for currency — it only  takes 5 bytes of storage.  I have yet to find a reason to extend that scale beyond 2, although I’ve seen some people do it (e.g., Decimal (10,4)).  If you know of an advantage or reason to do that – please let me know in the comments below!

 

Thanks and keep checking back for my ebook on the “order of SQL” – it will be good — I promise! — and most likely will be free at first, as I hope to get YOUR help in making it better before I sell it – see ya soon!

Scott