SQL Server - Useful Stuff

To fix orphansed users.
Occurs when you force a db backup onto another instance on another server
------------------------------------------------------------------------------------------------
exec sp_change_users_login 'Auto_Fix', ''

To move data around
You can use SQL Server 2008 to move data into older databases


See where CPU is being used when CPU utilisation is high
---------------------------------------------------------------------
Select
signal_wait_time_ms=sum(signal_wait_time_ms)
, '%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
, resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
, '%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats


If % signal waits (i.e. time spent waiting for CPU to be free) is greater than 25%, then more CPUs / faster CPUs will help

if % resource waits is high then use the following to see what:
---------------------------------------------------------------------------
SELECT * FROM sys.dm_os_wait_stats

Before doing a check call the following to reset wait counters:
---------------------------------------------------------------------------
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)