SQL 2005 - UPSERT: In nature but not by name; but at last!

SQL Server has long been criticised for not having an UPSERT command, well now in SQL 2005 we have some good news, we have two new TSQL set operators that give us the complex insert/update logic that constitute an UPSERT.  These are INTERSECT and EXCEPT e.g. — UPDATE Destination FROM (Source INTERSECT Destination) — INSERT…

1

SQL Server Performance Top Tip: SQL Server User Mode Scheduler (UMS) – understanding it can explain some odd performance

When performing batch jobs where you wish to run processes in parallel it is important to understand how the scheduler works.   You can get a description of what the scheduler does by looking under the title ‘SQL Server Task Scheduling’ in Books on Line.  However what you need to know is not at all…

4

Microsoft SQL Server Performance Top Tip: Multi Processor (SMP) Box Doesn’t Use All It’s Processors

Seen this?  You’ve got a nice new sparkling multi processor box, packed with ‘go-faster’ chips but the performance isn’t much better than your old one or two processor box.  You take a look at the processor usage, either through task manager or Performance (System) Monitor, and notice only one processor seems to be hard at…

6

CREATE proc track_waitstats & CREATE proc get_waitstats - useful code for diagnosing SQL 2000 performance

For those attending my TechNet SQL 2000 performance tuning session at TVP Reading on Tuesday 25th Jan, here is the code for the track_waitstats and get_waitstats stored procedures I refer to during the presentation.   CREATE proc track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype nvarchar(10)=’minutes’) as — T. Davidson — This stored procedure is provided “AS IS” with…

0