April, 2008 | Michael J. Swart

April 30, 2008

What are you waiting for?

Filed under: SQL Scripts,Technical Articles — Tags: "sql server", lazywriter_sleep, wait statistics — Michael J. Swart @ 5:04 am

Update July 5, 2011: Many people are coming to this blog post through search engines trying to figure out what LAZYWRITER_SLEEP means. I looked at this article and determined that it’s not clear. I call the wait type ignorable, but I don’t say why. So here’s why: LazyWriter_Sleep: Happens when a lazy writer task is suspended and waits to do more work. Since lazy writer tasks are background tasks, you don’t have to consider this state when you are looking for bottlenecks. Hope this helps… By the way, a much better article than this one is here: Wait Statistics by Paul Randal. What follows now is the original article:

The sys.dm_os_wait_stats helps to see where server processes are spending most of their time waiting. If the top cause is LAZYWRITER_SLEEP or SQLTRACE_BUFFER_FLUSH, then you’re all right. If not, then you’ve got some digging to do. Maybe start with sys.dm_exec_requests.

What are you waiting for now?

The times that are shown are cumulative since the start of the SQL Server service. I use this script to figure out what SQL Server processes are waiting for now:

select * into #BufferWaitStats from sys.dm_os_wait_stats

-- wait ten seconds or so
select ws.wait_type,
      ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count,
      ws.wait_time_ms - bws.wait_time_ms as wait_time_ms,
      ws.max_wait_time_ms,
      ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
      on ws.wait_type = bws.wait_type
order by wait_time_ms desc

--clean up
drop table #BufferWaitStats

-- Comments (3)

April 29, 2008

Who’s blocking who?

Filed under: Technical Articles — Michael J. Swart @ 10:50 am

A query that uses a CTE to determine who’s blocking who. I find it useful but a bit slow if things have gotten really really crazy.

SELECT spid, blocked INTO #tempSysProcesses FROM master..sysprocesses;

with BlockingChains (session_chains, leaf) AS

(  
 --base case  
 select distinct cast(spid as varchar(max)), spid  
 from #tempSysProcesses with (nolock)  
 where spid >= 50
    and (blocked = 0 or blocked = spid)

    UNION ALL

    --recursive step
  select bc.session_chains + ', ' + 
        cast(sp.spid as varchar(max)),
        sp.spid
    from #tempSysProcesses sp with (nolock)
    join BlockingChains bc
        on sp.blocked = bc.leaf
    where sp.blocked <> sp.spid
)
select leaf as session_id, session_chains as blocking_chain
from BlockingChains
where session_chains like '%,%'
order by leaf

DROP TABLE #tempSysProcesses

-- Comments (0)

SQL Poetry

Filed under: Tongue In Cheek — Tags: sql poem, sql poetry — Michael J. Swart @ 6:52 am

Probably one of the geekiest things I’ve ever done.

Take a list of SQL Server keywords. Stick the words in a database
table along with it’s meter and rhyme. Get SQL Server to cross join
this table to itself concatenate meter and output candidate lines of
poetry. Select and arrange according to rhyme:

ALTER PROCEDURE, CONTINUE, IGNORE,
money and smallmoney, AFTER, BEFORE,
INDEXKEY_PROPERTY, CAST and CONVERT,
GROUP BY, IDENTITY (Function), DEFERRED,
BACKUP, nchar and nvarchar, COMPUTE,
EXECUTE, UPDATE STATISTICS, INCLUDE,
datetime and smalldatetime, ISNULL, ROUTINE,
ROLLBACK TRANSACTION, IS_MEMBER, BETWEEN

Things I’ve noticed.
1) This must be what Vogon poetry is like.
2) Statistics is a word that should never show up in any poem
anywhere.

-- Comments (2)

Sudoku vs. SQL

Filed under: Tongue In Cheek — Michael J. Swart @ 6:51 am

A while ago, I heard about someone solving Sudoku with SQL. That sounded like a challenge to me. I came up with something that works pretty nicely. I can’t say for sure that it is guaranteed to solve every puzzle, but I haven’t seen it stumped yet. I’ve since updated the file so that when it gets stuck, it plays “what if”. It uses savepoints and rollbacks which work quite well.

-- Comments (2)

Things you'll find here.

Filed under: Miscelleaneous SQL — Michael J. Swart @ 6:47 am

I hope to post thoughts on SQL Server. Fixes, revelations, troubleshooting experiences that I don’t want to relive and maybe some more light hearted things.

-- Comments (0)