Refresh processes

Usually there is some kind of process that the DBA is involved in to move data from prod and put it into test environments.  If there is data in tables that drives the environment, you may want to 'persist' that data.  In order to do that effectively, I think it is important and helpful to have a way to verify that table A and table B are identical.

Below is how I accomplish that currently:

ALTER PROCEDURE usp_CompareTables @table1 VARCHAR(100), @table2 VARCHAR(100)
AS
BEGIN
  -- return 1 if identical 0 if not
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @count INT;
  DECLARE @parmDefinition NVARCHAR(200) = '@countOUT INT OUTPUT';
  set @sql = 'select @countOUT = count(*) from (
  (select * from ' + @table1 + '
  except
  select * from ' + @table2 + ' )
  union all
  (select * from ' + @table2 + '
  except
  select * from ' + @table1 +')) a'
 BEGIN TRY 
  -- try to see if the tables are identical
  exec sp_executesql @sql, @parmDefinition, @countOUT=@count OUTPUT
 END TRY
 BEGIN CATCH
  -- if we errored out then the datatypes don't match or the tables are not 
  -- alike.. Or maybe the DBA is comparing a table to a giraffe
  RETURN 0 
 END CATCH

 IF @count = 0
  -- if we have nothing in the list we are identical
  return 1
 ELSE
  -- if not we got a problem
  return 0
END 
GO

Past that you will need something to truncate and load.  If there are foreign keys, things get messy -- but I accomplish that through the following code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Constraints]') AND type in (N'U'))
BEGIN
 DROP TABLE Constraints
END
  create table Constraints
  (
 ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 PKTABLE_QUALIFIER VARCHAR(50),
 PKTABLE_OWNER   VARCHAR(50),
 PKTABLE_NAME   VARCHAR(50),
 PKCOLUMN_NAME    VARCHAR(50),
 FKTABLE_QUALIFIER VARCHAR(50),
 FKTABLE_OWNER     VARCHAR(50),
 FKTABLE_NAME      VARCHAR(50),
 FKCOLUMN_NAME     VARCHAR(50),
 KEY_SEQ           INT,
 UPDATE_RULE       INT,
 DELETE_RULE       INT,
 FK_NAME           VARCHAR(256),
 PK_NAME           VARCHAR(256),
 DEFERRABILITY     INT
   )
GO
declare @database varchar(50)
declare @table varchar(50)
declare @sql   varchar(max)

-- Really could just use a while loop.. but cursors work too.
declare fk_cursor cursor for 
select  SourceDatabase, SourceTable from PersistedItems

OPEN fk_cursor
FETCH NEXT FROM fk_cursor into @database, @table



WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; INSERT INTO [Database].[dbo].[Constraints] exec sp_fkeys ''' + @table + ''';'
 exec (@sql)
 FETCH NEXT FROM fk_cursor into @database, @table
END

close fk_cursor
deallocate fk_cursor
GO

-- Drop Constraints
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(50)
declare @sql   varchar(max)

declare drop_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME from Constraints

OPEN drop_cursor

FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname

WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; ALTER TABLE ' + QUOTENAME(@fktablename) + ' DROP CONSTRAINT ' + QUOTENAME(@fkname) + ';' from Constraints; 
 exec (@sql)
 FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname
END

close drop_cursor
deallocate drop_cursor
GO
use Database
GO

IF object_id('usp_RestoreTable') IS NULL
    EXEC ('create procedure dbo.usp_RestoreTable as select 1');
GO

ALTER PROCEDURE usp_RestoreTable
@SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT
AS
BEGIN

 DECLARE @SQL nvarchar(MAX) = ''
 DECLARE @Columns nvarchar(MAX) = ''
 CREATE TABLE #Columns
 (
  ColID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ColName varchar(100)
 )


 SELECT @SQL = 'INSERT INTO #Columns SELECT COLUMN_NAME FROM [' + @Database + '].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = ''' + @SourceTableName + ''' ORDER BY ORDINAL_POSITION'

 EXEC (@SQL)
 SELECT @SQL = ''

 SELECT @Columns += '[' + ColName + '], ' FROM #Columns
 SELECT @Columns = LEFT(@Columns, len(@Columns) - 1)

 DROP TABLE #Columns

 SELECT @SQL = 'TRUNCATE TABLE ' + @Database + '..' + @SourceTableName
 EXEC (@SQL)
 SELECT @SQL = 'USE ' + @Database + '; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' ON; ';
 SELECT @SQL = @SQL + 'INSERT INTO [dbo].[' + @SourceTableName + '] (' + @Columns + ') '
       + 'Select ' + @Columns + ' FROM [Database].[dbo].[' + @PersistedTableName + ']; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' OFF; ';

 exec (@SQL)

END
GO

--Move the data and verify
USE Database
GO
DECLARE @SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT,
@SourceTableFullName NVARCHAR(100),
@Matches BIT

DECLARE tandl_cursor CURSOR FOR
select SourceDatabase, SourceTable, PersistedTable, isIdentity from PersistedItems
OPEN tandl_cursor

FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity

WHILE (@@FETCH_STATUS = 0)
BEGIN
 exec usp_RestoreTable @SourceTableName,@PersistedTableName,@Database,@isIdentity
 SELECT @SourceTableFullName = @Database + '..' + @SourceTableName
     exec @Matches = usp_CompareTables @table1 = @SourceTableFullName, @table2 = @PersistedTableName
 update PersistedItems 
 SET IsPostPersisted = @Matches 
 where SourceDatabase = @Database and SourceTable = @SourceTableName and PersistedTable = @PersistedTableName 
 FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity
END

close tandl_cursor
deallocate tandl_cursor
GO

-- Create the FKeys
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(256)
declare @fkcolumnname varchar(50)
declare @pktablename varchar(50)
declare @pkcolumnname varchar (256)
declare @sql   varchar(max)

declare create_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME, FKCOLUMN_NAME, PKTABLE_NAME, PKCOLUMN_NAME from Constraints

OPEN create_cursor

FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname


WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'USE ' + @database + '; '
    + 'ALTER TABLE ' + QUOTENAME(@fktablename) 
       + ' ADD CONSTRAINT ' + QUOTENAME(@fkname) 
    + ' FOREIGN KEY (' + QUOTENAME(@fkcolumnname) 
    + ') REFERENCES ' 
    + QUOTENAME(@pktablename) +
     '(' + QUOTENAME(@pkcolumnname) 
  + ');' FROM Constraints;
 
 exec (@sql)
 FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname
END

close create_cursor
deallocate create_cursor
GO

This way I can simply add items to a driver table, and have the code handle the rest.

These are my notes on troubleshooting with wait stats:

It's not my favorite approach to correcting issues, as this is more reactive than proactive.  But sometimes its necessary to be able to dig through the wait stats and discern why it is the query is taking forever.

Things to keep in mind:

• When SQL Server needs to update data, it will need to pull the clustered index and ALL nonclustered indexes into data. Even triggers and others items will be brought in.

• NOT ALL WAITS ARE BAD – They need to be seen as excessive or impactful

Query Statuses

• Running

• Suspended

• Waiting for something

• "Yielding" so others can run

• Runnable

• Ready to run, but waiting to do so

• Needs someone else to yield

• Sleeping

• No work to do

• Waiting for instruction from the app

When waiting SQL Server will collect what you are waiting on
PAGEIOIOLATCH_xx – Waiting to ftech pages from storage into memory
SOS_SCHEDULER_YIELD – Task has 'voluntarily' yielded to another task so it can complete.  Basically the query is waiting for SQL Server to say 'Go ahead'
PAGELATCH_xx – Query might be waiting for the public toilet to have enough bandwidth for it. Protecting data in memory from inconsistency.
ASYNC_NETWORK_IO – Processing of the query is done.. This usually means that SQL Server is trying to send the results of the query to the client and the network limitations or client app limitations are restricting it.
LCK_xx – If updating data these waits will be created in order to maintain data integrity – isolating transactions
LATCH_xx – Protecting memory from other transactions.
WRITELOG – Flushing the transaction to storage as it commits
ASYNC_IO_COMPLETION – All committed transactions from the dirty pages in memory are asynchronously committed to the database
CX_PACKET – When a query goes parallel, some of the threads might take longer than others. This means its waiting for everyone to finish

Examples of problems:

Example	Issues Caused	Type of waits you might see
Implicit Conversions	Big Index Scans (more IO and CPU)	PAGEIOLATCH_xx, SOS_SCEDULER_YIELD
Missing Critical Indexes	Big Index Scans (more IO and CPU)	PAGEIOLATCH_xx, SOS_SCEDULER_YIELD
Cursors (the devil)	Lots of tiny writes – ants mobbing the tlog	WRITELOG
Duplicate Indexes	Clog up your memory and cause extra IO	PAGEIOLATCH_xx, ASYNC_IO_COMPLETION
"After" triggers (Satan) causing cascading deletes and updates on foreign keys	Make transactions take longer, Secretly uses escalated isolation level to cascade deletes to child tables	LCK_xx (which can lead to a large backlog of queries which often lead to CPU and Storage Waits
App Running giant reports	More data sent to app server than it can handle	ASYNC_NETWORK_IO

Refer to waits and stats by Paul Randal for an excellent method of collecting these (Create a table with a Date column (default getdate()) and Insert to exec on a schedule (every five minutes or hour whatever you prefer.)

Or you can also use the handy dandy sp_whoisactive and a table with a loop around it:
Brent Ozar: Who is Active to a Table
 I actually do this.

Moving a primary key

Moving a primary key

I recently had some fun un-partitioning some tables.  Deleting the schemes, functions, and the files/filegroups means getting the data out of there.

Now if you have a clustered primary key, moving the data can be a real pain.  You typically will have to drop all the constraints using that primary key, drop the primary constraint, and then add it back on the new filegroup.

While playing with the syntax I stumbled upon this, and it actually worked:

CREATE UNIQUE CLUSTERED INDEX PK_NAME ON TableName(PK_Column)
WITH (DROP_EXISTING = ON) ON [YourFileGroupHere]

example:

create unique clustered index PK_Members_ID on Members(ID)
WITH (DROP_EXISTING = ON, FILLFACTOR = 100)
ON [PRIMARY]

The wonderful thing about this is you don't have to drop the foreign keys, drop the primary key, recreate everything.  One command, in and out.

Make sure you have the database to yourself! It can be a timely operation to complete.