If you set up a replication – for example, a snapshot replication to refresh the ACC environment with PROD data – a correction of the identities is needed for the applications to run correctly.

The code below checks the identities and gives the syntax to correct the identity values:

SELECT
IDENT_SEED(TABLE_SCHEMA+’.’+TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_SCHEMA+’.’+TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_SCHEMA+’.’+TABLE_NAME) AS Current_Identity,
TABLE_SCHEMA+’.’+TABLE_NAME as table_name,
‘DBCC CHECKIDENT(”’+TABLE_SCHEMA+’.’+TABLE_NAME+”’, RESEED, ‘+CAST(IDENT_CURRENT(TABLE_SCHEMA+’.’+TABLE_NAME) +1 AS VARCHAR(10))+’)’ as Command
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+’.’+TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’
AND TABLE_NAME not like ‘Mspeer%’
AND TABLE_NAME not in (‘sysarticles’,’syspublications’)
ORDER BY TABLE_SCHEMA, TABLE_NAME

In order to view deadlock information in SQLserver (2012 and beyond), you need to have VIEW SERVER STATE permission. The deadlock reports are part of the default trace, called the System Health Session:

Open this Event_File, it may take a while for all events to load from the file. After that, from the Extended Events menu, choose Grouping, and group on NAME.

Click OK and the events are grouped by, well, name:

Open up the XML_deadlock_report and chose one of the deadlock reports. Click on the DEAEDLOCK tab to get the graph:

If you click on the DETAILS tab, you can doubleclick on the VALUE to get the XML with all the deadlock details.

In this post I will explain how to create executionplans from production on your developers laptop for SQLserver:

(1) download and install the SQLManagement Studio snap-in plan explorer from SentryOne

(2) Export databaseobjects including statistics and histograms from production as follows:

(3) Create the database on your laptop and make sure to switch off any statistics creations in the properties:

(4) Copy parallel processing configuration parameters from production:

sp_configure ‘cost threshold for parallelism’ , x
sp_configure ‘max degree of parallelism’, x

You are now ready to check the executionplans of your new SQL statements your new environment. If you look at the tablesizes ( with this query ) you will see that your new tables are have the statistics in place, but all tables are really empty:

In the next post we’ll have a look at the executionplans.