SQL Nexus, Adding Additional Collection Events

http://sqlgator.com/2012/05/29/sql-nexus-adding-additional-collection-events/

The author, Christian Bolton, noted that the default Trace events that are collected when you run StartSQLDiagTrace2008R2.cmd from the performance scripts that we mentioned earlier in this series provide a good baseline for your monitoring activity.  However, he noted added the following events in order to enable additional functionality necessary to compare execution plans and drill down to the statement level:

• EventType “Performance”, Event name “Show Plan Statistics”
• EventType ”Stored Procedures”,  Event name “SP:StmtCompleted”
• EventType ”Stored Procedures”,  Event name “SP:StmtStarting”
• EventType ”TSQL”,  Event name “SQL:StmtCompleted”
• EventType ”TSQL”,  Event name ”SQL:StmtStarting”

In order to enable these simply edit the XML file, SQLDiagPerfStats_Trace2008.xml, changing the above items from false to true.  After you collect these new events by running the CMD file, then import the collection into SQL Nexus and check out your new results.  Enjoy!

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/