Thanh Ngay Nguyen - DBA's Blog

Tuesday, June 14, 2011

Using T-SQL to Verify Tables Row Counts in Transactional Replication - Continue

Last year, I wrote an article (Using T-SQL to Verify Tables Row Counts in Transactional Replication) about Transactional Replication and published it on on SQLServerCentral (http://www.sqlservercentral.com/articles/Replication/70545/)

At my new company, we're using a separated Distributor server, so I modified my script. Uhmm... not sure if I try to submit it again to SQL Server Magazine, they would take it or not. Anyhow, here's the script:

/*==================================================================
Originally created by: Thanh Ngay Nguyen (DBA in Minneapolis, MN)
Created date: 7/22/2010
Modified date: 6/11/2011
--------------------------------------------------------------------
This script verifies the Subscriber(s) have the same number of rows of
replicated data as the Publisher in transactional replication

This script must be run at the Distributor (where it resides on separate
server from the Publisher)
--------------------------------------------------------------------
Feel free to modify this script to fit your needs. All I ask is to
keep the original author name intact. Thanks.
--------------------------------------------------------------------
Modified by: You
Modified date: xx/yy/zzzz
--------------------------------------------------------------------
System tables that I use throughout the script:

SELECT * FROM distribution.dbo.MSarticles
SELECT * from distribution.dbo.MSpublications
SELECT * from distribution.dbo.MSsubscriptions
select * from master.sys.servers

USE [myDB]
SELECT * from dbo.sysindexes
SELECT * from sys.objects

--Instead of using deprecated dbo.sysindexes, sys.partitions can be used to obtain the rowcount

SELECT ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName,
p.rows AS RowCnt
FROM sys.partitions AS p
INNER JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND p.index_id IN ( 0, 1 )
====================================================================*/

/********************************************************************
BEGINING OF SCRIPT
*********************************************************************/

USE [Distribution]
GO

--------------------------------------------------------------------
--STEP 1: Gather information about current transactional replication
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempTransReplication') IS NOT NULL
DROP TABLE #tempTransReplication

CREATE TABLE #tempTransReplication
(
publisher_id INT,
publisher_srv VARCHAR(255),
publisher_db VARCHAR(255),
publication VARCHAR(255),
subscriber_id INT,
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
object_type VARCHAR(255),
source_owner VARCHAR(255),
source_object VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
rowcount_publisher INT,
rowcount_subscriber INT,
rowcount_diff INT
)

INSERT INTO #tempTransReplication
SELECT s.publisher_id,
ss2.data_source,
a.publisher_db,
p.publication,
s.subscriber_id,
ss.data_source,
s.subscriber_db,
NULL,
a.source_owner,
a.source_object,
ISNULL(a.destination_owner, a.source_owner), -- if NULL, schema name remains same at subscriber side
a.destination_object,
NULL,
NULL,
NULL
FROM distribution.dbo.MSarticles AS a
INNER JOIN distribution.dbo.MSsubscriptions AS s
ON a.publication_id = s.publication_id
AND a.article_id = s.article_id
INNER JOIN [master].sys.servers AS ss
ON s.subscriber_id = ss.server_id
INNER JOIN distribution.dbo.MSpublications AS p
ON s.publication_id = p.publication_id
LEFT OUTER JOIN [master].sys.servers AS ss2
ON p.publisher_id = ss2.server_id
WHERE s.subscriber_db <> 'virtual'

--SELECT * FROM #tempTransReplication

--------------------------------------------------------------------
-- STEP 2: Gather rowcount at Publisher side
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempPublishedArticles') IS NOT NULL
DROP TABLE #tempPublishedArticles

CREATE TABLE #tempPublishedArticles
(
publisher_srv VARCHAR(255),
publisher_db VARCHAR(255),
source_owner VARCHAR(255),
source_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_publisher INT
)

DECLARE @pub_srv VARCHAR(255),
@pub_db VARCHAR(255),
@strSQL_P VARCHAR(4000)

DECLARE db_cursor_p CURSOR
FOR SELECT DISTINCT
publisher_srv,
publisher_db
FROM #tempTransReplication

OPEN db_cursor_p
FETCH NEXT FROM db_cursor_p INTO @pub_srv, @pub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_P = 'SELECT ' + '''' + @pub_srv + ''''
+ ' AS publisher_srv, ' + '''' + @pub_db + ''''
+ ' AS publisher_db, s.name AS source_owner, o.name AS source_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_publisher
FROM ' + @pub_srv + '.' + @pub_db + '.sys.objects AS o
INNER JOIN ' + @pub_srv + '.' + @pub_db + '.sys.schemas AS s
on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @pub_srv + '.' + @pub_db + '.dbo.sysindexes AS i
on o.object_id = i.id
WHERE ' + '''' + @pub_srv + '.' + @pub_db + ''''
+ ' + ' + '''' + '.' + '''' + ' + s.name'
+ ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT publisher_srv + ' + '''' + '.' + ''''
+ ' + publisher_db + ' + '''' + '.' + ''''
+ ' + source_owner + ' + '''' + '.' + ''''
+ ' + source_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempPublishedArticles
EXEC ( @strSQL_P
)

FETCH NEXT FROM db_cursor_p INTO @pub_srv, @pub_db
END
CLOSE db_cursor_p
DEALLOCATE db_cursor_p

--SELECT * FROM #tempPublishedArticles

--------------------------------------------------------------------
-- STEP 3: Gather rowcount at Subscriber(s) side
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL
DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles
(
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_subscriber INT
)

DECLARE @sub_srv VARCHAR(255),
@sub_db VARCHAR(255),
@strSQL_S VARCHAR(4000)

DECLARE db_cursor_s CURSOR
FOR SELECT DISTINCT
subscriber_srv,
subscriber_db
FROM #tempTransReplication

OPEN db_cursor_s
FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + ''''
+ ' AS subscriber_srv, ' + '''' + @sub_db + ''''
+ ' AS subscriber_db, '
+ 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber
FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o
INNER JOIN ' + @sub_srv + '.' + @sub_db
+ '.sys.schemas AS s on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db
+ '.dbo.sysindexes AS i on o.object_id = i.id
WHERE ' + '''' + @sub_srv + '.' + @sub_db + ''''
+ ' + ' + '''' + '.' + ''''
+ ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT subscriber_srv + ' + '''' + '.' + ''''
+ ' + subscriber_db + ' + '''' + '.' + ''''
+ ' + destination_owner + ' + '''' + '.' + ''''
+ ' + destination_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempSubscribedArticles
EXEC ( @strSQL_S
)

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db
END
CLOSE db_cursor_s
DEALLOCATE db_cursor_s

--SELECT * FROM #tempSubscribedArticles

--------------------------------------------------------------------
-- STEP 4: Update table #tempTransReplication with rowcount
--------------------------------------------------------------------

UPDATE t
SET rowcount_publisher = p.rowcount_publisher,
object_type = p.object_type
FROM #tempTransReplication AS t
INNER JOIN #tempPublishedArticles AS p ON t.publisher_db = p.publisher_db
AND t.source_owner = p.source_owner
AND t.source_object = p.source_object

UPDATE t
SET rowcount_subscriber = s.rowcount_subscriber
FROM #tempTransReplication AS t
INNER JOIN #tempSubscribedArticles AS s ON t.subscriber_srv = s.subscriber_srv
AND t.subscriber_db = s.subscriber_db
AND t.destination_owner = s.destination_owner
AND t.destination_object = s.destination_object

UPDATE #tempTransReplication
SET rowcount_diff = ABS(rowcount_publisher - rowcount_subscriber)

--SELECT * FROM #tempTransReplication

--------------------------------------------------------------------
-- STEP 5: Display final results
--------------------------------------------------------------------

-- rowcount result by replicated database
SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those databases which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by publication
SELECT publisher_srv,
publisher_db,
publication,
subscriber_srv,
subscriber_db,
sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv,
publisher_db,
publication,
subscriber_srv,
subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those publications which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by table
SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
( source_owner + '.' + source_object ) AS source_objectname,
( destination_owner + '.' + destination_object ) AS destination_objectname,
rowcount_diff AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
AND rowcount_diff > 0 -- only show those tables which fall behind
ORDER BY rowcount_diff DESC

--------------------------------------------------------------------
-- information about current transactional replication setup
--------------------------------------------------------------------

SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
( source_owner + '.' + source_object ) AS source_objectname,
( destination_owner + '.' + destination_object ) AS destination_objectname,
rowcount_publisher
FROM #tempTransReplication
ORDER BY publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
source_objectname,
rowcount_publisher

/********************************************************************
END OF SCRIPT
*********************************************************************/

Wednesday, January 19, 2011

Snapshot Isolation

I’m currently making use of the SQL Server Change Tracking (new feature in 2008) to incrementally load our data warehouse. Per Microsoft, it's recommended to turn on Allow_Snapshot_Isolation at server level to make sure the consistency of data when using Change Tracking. I googled about this snapshot isolation stuff and ran into some very interesting discussions/blogs/articles. See below:

Hugo Kornelis, SQL Server MVP:
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/21/Snapshot-and-integrity-part-1.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot_and_integrity_part_2.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/08/25/174.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot_and_integrity_part_4.aspx

Paul Alcon:
http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level

microsoft.public.sqlserver.server:
http://www.sqlnewsgroups.net/sqlserver/t11164-performance-sql2k5-with-snapshot-isolation-level-turned.aspx

Sunil Agarwal:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/overhead-of-row-versioning.aspx

Wednesday, December 29, 2010

Database Growth Tracker Tool

Today, while I was googling for some t-sql scripts to collecting database stats from central mgmt server, I ran into CLR tool written by Tara Kizer (http://weblogs.sqlteam.com/tarad/archive/2010/07/09/Database-Growth-Tracker-Tool-ndash-New-Version.aspx). What a nice job she did without the hassle of setting up linked-server.

Well, while I was inside her blog, I pass through some very interesting things that she mentioned, e.g. missing indexes DMV, Max Degree of Parallelism, etc… You know what? I discover everyday that more and more things that I need to learn…

Tuesday, December 21, 2010

SQL Server Magazine

Well today I got email from SQL Server Magazine Editor saying that they wanted to publish my article in print on monthly SQL Server Magazine. For some reason, my article that submitted back in June, got buried in a backlog and just got surfaced recently. I was super duper excited about it. I told the editor that I didn’t get response from the company for couple months, so I submitted to SQLServerCentral and got posted/published there in August. The editor said since my article already got published elsewhere, so they can’t publish it in their magazine…. Dang…Bumper…Oh well… next time then…

Monday, December 20, 2010

Blocking - who blocks whom?

As a DBA, I got power to query meta system data to monitor any blocking going on. Although I monitor the blocking on all three enviroments (prod/dev/stg), but I tend to ignore the dev/stg. These 2 enviroments (dev/stg) where developers run their test codes and often block each other. Sometimes, I shoot them an email, but most of the time, i got buried with other prod stuff and no chance to bother, while developers they patiently wait and wait and have no clue why their queries takes so long to complete. Most of our developers are very knowlegeable and they can query dbo.sysprocesses from master db, but unfortunately they don't have permissions. Well... we do give them db_owner on all user-defined databases on dev/stg enviroments thou. Anyways,.. so to overcome this problem, i create a report (RDL) and publish on our main reporting services server, so they can go there and check themseves if any blocking occurs on specific server.

My objective is to run a store proc from centralized server. From there, I'm using linked server to query other server for blocking info.

USE [master]
CREATE PROCEDURE [dbo].[_dbaGetBlockingInfoByServer]
@linkedsrvName VARCHAR(255)
AS
/*
************************************************************************************************
-- //$Date: 12/15/2010 hh:mm $
-- //$Modtime: 12/15/2010 hh:mm $
-- //$Workfile: xxx.yyyy.zzz $
************************************************************************************************
-- // schema = xxx
-- // objname = yyyyyy
-- //
-- // Description:
-- //
-- // Modification history: Get blocking info details by server name
-- // Date  Author    PDW/SR  Description
-- // ---------- ---------------  -------  --------------------------------------------
-- // 12/15/2010 Thanh Nguyen  xxx   Originally created
************************************************************************************************
*/

-----------TESTING BLOCK------------
--DECLARE @linkedsrvName VARCHAR(255);
--SET @linkedsrvName = 'SQLSRV01';
----------------------------------
SET NOCOUNT ON ;
DECLARE @debug BIT;
SET @debug = 0;
DECLARE @strSQL VARCHAR(8000);
IF OBJECT_ID('tempdb..#temp_blocking') IS NOT NULL
    DROP TABLE #temp_blocking ;
CREATE TABLE #temp_blocking
    (
      SPID SMALLINT,
      Blocked_By SMALLINT,
      DBName varchar(255),
      WaitTime BIGINT,
      Wait_Info VARCHAR(255),
      Open_Tran SMALLINT,
      STATUS VARCHAR(255),
      HostName VARCHAR(255),
      AppName VARCHAR(255),
      Command VARCHAR(255),
      Login_Name VARCHAR(255),
      UserName VARCHAR(255),
      UserLocation VARCHAR(255),
      CPU INT,
      Physical_IO INT,
      Memory_Usage INT,
      Last_Batch DATETIME,
      Sql_Handle BINARY(20),
      SQL VARCHAR(8000)
    ) ;
SET @strSQL = 'SELECT *
    FROM    ( SELECT    p.spid AS SPID,
                        p.blocked AS Blocked_By,
                        d.name AS DBName,
                        p.waittime,
                        ( CONVERT(VARCHAR, p.waittime / 1000) + ' + ''''
    + 'sec - ' + '''' + '
                          + COALESCE(p.lastwaittype, ' + '''' + '''' + ') + '
    + '''' + ' ' + '''' + '
                          + COALESCE(p.waitresource, ' + '''' + ''''
    + ') ) AS Wait_Info,
                        p.open_tran AS Open_Tran,
                        p.Status AS Status,
                        p.hostname AS HostName,
                        p.program_name AS AppName,
                        p.cmd AS Command,
                        p.loginame AS Login_Name,
                        NULL as UserName,
                        NULL as UserLocation,
                        p.cpu AS CPU,
                        p.physical_io AS Physical_IO,
                        p.memusage AS Memory_Usage,
                        p.last_batch AS Last_Batch,
                        p.sql_handle as Sql_Handle,
                        NULL as SQL
              FROM      ' + @linkedsrvName + '.master.dbo.sysprocesses p WITH (NOLOCK)
                        LEFT OUTER JOIN ' + @linkedsrvName
    + '.master.dbo.sysdatabases d WITH (NOLOCK) ON d.dbid = p.dbid
                        LEFT OUTER JOIN ( SELECT DISTINCT
                                                    blocked
                                          FROM      ' + @linkedsrvName
    + '.master.dbo.sysprocesses WITH (NOLOCK)
                                        ) AS b ON b.blocked = p.spid
            ) AS A
    ORDER BY A.Blocked_By' ;
IF @debug = 1
PRINT @strSQL;
INSERT #temp_blocking
        (
          SPID,
          Blocked_By,
          DBName,
          WaitTime,
          Wait_Info,
          Open_Tran,
          STATUS,
          HostName,
          AppName,
          Command,
          Login_Name,
          UserName,
          UserLocation,
          CPU,
          Physical_IO,
          Memory_Usage,
          Last_Batch,
          Sql_Handle,
          SQL
        )
        EXEC ( @strSQL
            ) ;
IF @debug = 1
SELECT * FROM #temp_blocking
IF OBJECT_ID('tempdb..#temp_blocking_final') IS NOT NULL
    DROP TABLE #temp_blocking_final ;

SELECT t.*
INTO #temp_blocking_final
FROM    ( SELECT    *
          FROM      #temp_blocking
          WHERE     Blocked_By <> 0
          UNION
          SELECT    *
          FROM      #temp_blocking
          WHERE     SPID IN ( SELECT    Blocked_By
                              FROM      #temp_blocking )
        ) AS t
ORDER BY t.Blocked_By,
        t.SPID ;
DECLARE @mystrSQL VARCHAR(500),
    @mysqlhandle BINARY(20)
DECLARE db_cursor CURSOR
    FOR SELECT Sql_Handle
        FROM    #temp_blocking_final
        ORDER BY SPID
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @mysqlhandle
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @debug = 1
   PRINT sys.fn_sqlvarbasetostr(@mysqlhandle);
        IF object_id('tempdb..#okay') IS NOT NULL
            DROP TABLE #okay ;
        CREATE TABLE #okay ( txtSQL VARCHAR(8000) ) ;
        SET @mystrSQL = 'SELECT * FROM master.sys.dm_exec_sql_text('
            + sys.fn_sqlvarbasetostr(@mysqlhandle) + ')'
        SET @mystrSQL = N'SELECT text FROM OPENQUERY(' + @linkedsrvName + ', ''' + @mystrSQL
            + ''')'
        IF @debug = 1
   PRINT @mystrSQL;
        INSERT #okay ( txtSQL )
                EXEC ( @mystrSQL
                    ) ;
        UPDATE t
        SET     t.SQL = ( SELECT TOP 1
                                        txtSQL
                               FROM     #okay
                             )
        FROM    #temp_blocking_final AS t
        WHERE   t.Sql_Handle = @mysqlhandle
        FETCH NEXT FROM db_cursor INTO @mysqlhandle
    END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT *, (LEFT(REPLACE(SQL, (CHAR(13) + CHAR(10)), ' '), 50) + ' ... (more)') AS short_SQL
FROM    #temp_blocking_final;

Thursday, September 30, 2010

Traverse Tree - Shortest path & Dijkstra's algorithm

I finally found a solution on how to traverse dependency tree using shortest path or Dijkstra's algorithm. The key to this solution is using spanning tree concept, i.e. not to care about either traverse upward or downward. I'll post my implementation here soon.