Tempdb Latch Contention

SELECT
   session_id,
   wait_type,
   wait_duration_ms,
   blocking_session_id,
   resource_description,
   ResourceType = CASE
   WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
   WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
   WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
       ELSE 'Is Not PFS, GAM, or SGAM page'
   END
FROM (  SELECT 
           session_id,
           wait_type,
           wait_duration_ms,
           blocking_session_id,
           resource_description,
           CAST(RIGHT(resource_description, LEN(resource_description)
           - CHARINDEX(':', resource_description, 3)) AS INT) AS PageID
       FROM sys.dm_os_waiting_tasks
       WHERE wait_type LIKE 'PAGE%LATCH_%'
         AND resource_description LIKE '2:%') AS tab;

SQL Replication Links

SQL Replication demystified:

http://sqlblog.com/blogs/rob_farley/archive/2012/12/26/sql-replication-demystified.aspx

Achieving Excellence in Designing and Maintaining SQL Server Transactional Replication Environments:

http://technet.microsoft.com/en-us/library/jj215849.aspx

REPLTalk covers Using and Tuning SQL Replication

http://blogs.msdn.com/b/repltalk/

Stairway to SQL Server Replication

http://www.sqlservercentral.com/stairway/72401/

Links

Master..SP_INDEXINFO



Who is active

 Snapper (Who is active for Oracle)

script to copy all permissions from a database:

http://dangerousdba.blogspot.com/2012/08/copying-permissions.html




SET NOCOUNT ON
DECLARE    @OldUser sysname, @NewUser sysname
DECLARE PC CURSOR FOR SELECT name FROM sys.database_principals WHERE Principal_ID>4 AND is_fixed_role=0
OPEN PC
FETCH NEXT FROM PC INTO @OldUser
WHILE @@FETCH_STATUS=0
BEGIN
SET    @NewUser = @OldUser
SELECT '-- '+@OldUser AS '-- Current User'
SELECT    'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT    'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE    USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC

SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE    usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE    usr.name = @OldUser
AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
FETCH NEXT FROM PC INTO @OldUser
END
CLOSE PC
DEALLOCATE PC

Performance Monitor Wizard

Performance Monitor Wizard

Performance Analysis of Logs (PAL) Tool

http://pal.codeplex.com/

Process Trace File

 --- I believe Raymond Peacock is the author



-- variables
declare @trace nvarchar(1000), @filter nvarchar(1000)
select @trace = $(traceFile)
--'E:\Traces\SERVERNAME__sp_trace.trc'
--select @filter = 'p_chat'


-- read raw data
select *
into #trace
from ::fn_trace_gettable(@trace, DEFAULT)
where TextData is not null
and CONVERT(varchar(100), textdata) <> 'exec sp_reset_connection'


-- extract according to filter
select CASE WHEN TextData LIKE '%exec %' THEN CONVERT(varchar(1000), TextData) ELSE NULL END AS ProcName, HostName, cpu, reads, duration, starttime, CONVERT(varchar(1000), TextData) AS TextData
into #procs
from #trace
where textdata like case when @filter is not null then '%' + @filter + '%' else textdata end


-- Deletes
DELETE #procs WHERE textData LIKE '%msdb.dbo.%'
DELETE #procs WHERE TextData = ' if @@trancount > 0 commit tran'
DELETE #procs WHERE TextData = 'begin tran'


-- reformat
update #procs
SET procname = SUBSTRING(procname, CHARINDEX('exec dbo.', ProcName), 1000)
WHERE CHARINDEX('exec dbo.', ProcName) > 0

update #procs
set procname = REPLACE(ProcName, 'exec dbo.', '')

update #procs
set procname = LEFT(procname, charindex('@', ProcName)-1)
where CHARINDEX('@', procname) > 0


-- summary results by proc
select top 10 procname, COUNT(*) as calls, AVG(cpu) AS AvgCPU, AVG(Reads) AS AvgReads, AVG(duration) as duration, MIN(starttime) AS [Start], MAX(starttime) AS [End]
from #procs
group by procname
order by (COUNT(*) * AVG(Reads)) DESC


-- results for top 5 sprocs
select *
from #procs
where procname in (
    select top 1 procname
    from #procs
    where ProcName is not null
    group by procname
    order by (COUNT(*) * AVG(Reads)) DESC
)
order by StartTime


-- cleanup
DROP TABLE #trace
DROP TABLE #procs