Troubleshooting and Experience

LIKE vs PATINDEX

2012-07-25T11:53:00Z

We had a discussion on some of the performance warnings in VSTF like using LIKE(with %) operator. I was just going through multiple options on how to avoid LIKE clause from the query and
improve performance as well. Generally when we use LIKE operator, it goes for table/clustered index scan and this is the costliest operation SQL server performs so we need to be very carefull here.

First option was to use some other wild card characters instead of (%) but that does not work here as we search specific words/sentence. If a single character is unknow, then we can use range else % is the only one left to us. Further to avoid this, thought of using any other function, which would avoid using wild card characters and improve performance as well.

Tried using CHARINDEX & PATINDEX, which can substitute the like operators and tested the same, which seems to be better than LIKE.

Here is the example.

Using LIKE Operator:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET STATISTICS IO ON;

SELECT *

FROM tblContractSchedule

WHERE ScheduleName LIKE '%INVALID%'

AND ScheduleName LIKE '%DO NOT USE%'

STATS IO ON Result:

Table 'tblContractSchedule'. Scan count 5, logical reads 9287, physical reads 13, read-ahead reads 8423, lob logical reads 0,lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

Using PATINDEX:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET STATISTICS IO ON;

SELECT *

FROM tblContractSchedule

WHERE PATINDEX('%INVALID%',ScheduleName) > 0

AND PATINDEX('%DO NOT USE%',ScheduleName) > 0

Table 'tblContractSchedule'. Scan count 1, logical reads 8406, physical reads 9,read-ahead reads 8423, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

As per this test, it seems that we can straight away get rid of LIKE operator and reduce our warnings and improve the query performance.

How much space are you wasting?

2012-06-29T07:55:45Z

The Below script would provide us the space used by write only indexes.
The usage stats from the indexes is pretty easy to get from ‘sys.dm_db_index_usage_stats’
and if you join the DMV ‘sys.dm_db_partition_stats’
onto that, you can from the column ‘used_page_count’ calculate the number of
bytes that the particular indexes is wasting.

Here is the script, that I ended up with:

WITH WastedMB
AS
(
SELECT
t1.database_id,
t1.object_id,
t1.index_id,
t2.name,
t3.partition_number,
t3.used_page_count
FROM
sys.dm_db_index_usage_stats t1
INNER JOIN sys.indexes t2 ON (t1.object_id =
t2.object_id) AND (t1.index_id = t2.index_id)
INNER JOIN
sys.dm_db_partition_stats t3 ON (t1.object_id = t3.object_id) AND (t1.index_id =
t3.index_id)
WHERE
    database_id = DB_ID() and
    user_seeks = 0 and
user_scans = 0 and user_lookups = 0 and

OBJECTPROPERTY(t1.[object_id],'IsUserTable') = 1 and t2.index_id > 1
and
    t2.is_unique = 0 and t2.is_unique_constraint =
0
)
SELECT
database_id,
object_id,
index_id,
name,
COUNT(*)
AS numofpartitions,
SUM(used_page_count) AS
UsedPages,
(SUM(used_page_count) * 8) / 1024 AS spacewasteinmb
FROM
WastedMB
GROUP BY database_id, object_id, index_id,name
ORDER BY
1,2,3
COMPUTE SUM((SUM(used_page_count) * 8) / 1024)

As always be careful when you drop indexes – better check an extra time
before dropping

LAG’n’LEAD – New T-SQL Features in SQL Server Denali CTP3

2012-06-29T07:55:06Z

Are you tired of doing self joins, just to get the value of a column in the
previus/subsequent row, or maybe even

worse you are doing multiple self joins. This is where you will start
smiling.

Let's test this functionality, how it is simple and gives better
performance than self JOIN.

CREATE TABLE MonthlyProfit
(
    yearid int,
    Monthid
int,
    Profit bigint
)

--- Inserting multiple records through Row Constructor(New feature in
SQL 2008 R2)

INSERT INTO MonthlyProfit (yearid, Monthid,
Profit)
VALUES
(2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000),
(2010,5,900), (2010,6,45),
(2010,7,1766), (2010,8,9100), (2010,9,-100),
(2010,10,50), (2010,11,900000), (2010,12,6575)

What we would like to do now is to write a select statement that gives us
Yearid, MonthId, ProfitThisMonth and ProfitLastMonth, so that we would be able
to compare profit with last month. Before SQL Server Denali CTP3 my SQL

statement would look something like this:

SELECT
t1.yearid,
t1.Monthid,
t1.Profit as
ProfitThisMonth,
t2.Profit as ProfitLastMonth
FROM MonthlyProfit as
t1
LEFT JOIN MonthlyProfit as t2 on (t1.yearid = t2.yearid) AND (t1.Monthid =
t2.Monthid+1)

A pretty simple statement by looking at it, this is not possible without
the nasty self join.

SQL Server Denali CTP3 finally gives us LAG and
LEAD. Here is the description directly stolen from BOL:

Accesses data
from a previous row in the same result set without the use of a self-join in
Microsoft SQL Server

Code-Named “Denali”, Community Technology Preview 3 (CTP 3). LAG provides
access to a row at a given physical

offset that comes before the current row. Use this analytic function in a
SELECT statement to compare values in the

current row with values in a previous row.

The syntax for LAG and
LEAD is the same

LAG (scalar_expression [,offset] [,default]) OVER ( [
partition_by_clause ] order_by_clause)

Let’s have a look at the
statement now:

SELECT
yearid,
monthid,
Profit as
ProfitThisMonth,
LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as
ProfitLastMonth
FROM MonthlyProfit

No more self joins, and a much more simple statement. Read the full
documentation to the LAG and LEAD in BOL.

What about performance?

I set IO statistics on(SET STATISTICS IO
ON;) and run both queries again. Here is the results:
(12 row(s)
affected)
Table 'MonthlyProfit'. Scan count 2, logical reads 13, physical
reads 0, read-ahead reads 0,…

(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,…
Table 'MonthlyProfit'. Scan count 1,
logical reads 1, physical reads 0, read-ahead reads 0,
So the conclusion is,
we get a much simpler query that also does fewer reads, that’s what we call a
WIN-WIN situation.

THROW in Error Handling - Denali

2012-06-29T07:54:20Z

Error handling is now easier with the introduction of the THROW command in SQL
Server 2011.

Legacy method (SQL 2005 onwards)

In previous
versions, RAISERROR was used to show an error message. RAISERROR requires a
proper message number to be shown when raising any error. The message number
should exist in sys.messages. RAISERROR cannot be used to re-throw an exception
raised in a TRY..CATCH block.

2011 method

Unlike RAISERROR,
THROW does not require that an error number to exist in sys.messages (although
it has to be between 50000 and 2147483647). All exceptions being raised by THROW
have a severity of 16.

You can throw an error using Throw as
below:
THROW 50001, 'Error message', 1;
This will return an error
message:

Msg 50001, Level 16, State 1, Line 1 Error
message

THROW even allows for re-throwing an exception caught in a
TRY..CATCH block, which RAISERROR was not able to handle:
BEGIN
TRY
SELECT 'Using Throw'
SELECT 1 / 0
END TRY
BEGIN CATCH
--Throw
error
THROW
END CATCH
The above code snippet produces this
output:

(1 row(s) affected)

(0 row(s) affected)

Msg
8134, Level 16, State 1, Line 3

Divide by zero error
encountered.
THROW has now made the developer’s life much easier, and
developers can now code independent of the Tester’s input on the exception
message.

NOTE: The current version of 2011 Books Online indicates that
RAISERROR has been deprecated. Due to this, the use of RAISERROR should be
minimized.

Deprecated Database Engine Features in SQL Server 2012

2012-06-29T07:53:44Z

These features (http://technet.microsoft.com/en-us/library/ms143729(SQL.110).aspx)
are scheduled to be removed in a future release of SQL Server. Deprecated
features should not be used in new applications.

14 New Functions and 1 Changed Function in Denali

2012-06-29T07:53:02Z

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 14 new built-in
functions. These functions ease the path of migration for information workers by
emulating functionality that is found in the expression languages of many
desktop applications. However these functions will also be useful to experienced
users of SQL Server.

Those functions can be found in below Link:-http://msdn.microsoft.com/en-us/library/cc645577(v=SQL.110).aspx

Just for curiosity, lets me mention here two functions(IIF and CONCAT).

IIF : -
Returns one of two values, depending on whether the Boolean
expression evaluates to true or false.

DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b,
'TRUE', 'FALSE' ) AS Result;

CONCAT : -

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS
Result;

Here is the result set.
-------------------------
Happy
Birthday 11/25

CONCAT with NULL values

CREATE TABLE #temp (
    emp_name nvarchar(200) NOT NULL,

emp_middlename nvarchar(200) NULL,
    emp_lastname nvarchar(200) NOT
NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT
CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM
#temp;

Here is the result set.
------------------
NameLastname

Dude! What is Microsoft SQL Server ?

2012-06-29T07:51:43Z

Microsoft SQL Server is a relational
database server, developed by Microsoft:
It is a software product whose primary function is to store and retrieve data as
requested by other software applications, be it those on the same computer or
those running on another computer across a network (including the Internet).
There are at least a dozen different editions of Microsoft SQL Server aimed at
different audiences and for different workloads (ranging from small applications
that store and retrieve data on the same computer, to millions of users and
computers that access huge amounts of data from the Internet at the same
time).

True to its
namesake, Microsoft SQL Server's primary query languages are T-SQL and ANSI
SQL.

History

Genesis

Version	Year	Release Name	Codename
1	1989	SQL Server 1.0	-
(OS/2)	1989	(16bit)	-
1.1	1991	SQL Server 1.1	-
(OS/2)	1991	(16bit)	-
4.21	1993	SQL Server 4.21	SQLNT
(WinNT)	1993	SQL Server 4.21	SQLNT
6	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0	Plato
-	1999	OLAP Tools	Plato
8	2000	SQL Server 2000	Shiloh
8	2003	SQL Server 2000	Liberty
8	2003	64-bit Edition	Liberty
9	2005	SQL Server 2005	Yukon
10	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure	Matrix (aka CloudDB)
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11		SQL Server 2012	Denali

Prior to
version 7.0 the code
base for MS SQL Server was sold by Sybase SQL Server to Microsoft, and was
Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase. Microsoft, Sybase and Ashton-Tate originally teamed up to create and
market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same
as Sybase SQL Server 3.0 on Unix, VMS, etc. Microsoft SQL Server 4.2 was shipped
around 1992 (available bundled with IBM OS/2 version 1.3). Later Microsoft SQL Server
4.21 for Windows NT was released at the same time as Windows NT 3.1. Microsoft
SQL Server v6.0 was the first version designed for NT, and did not include any
direction from Sybase.

About the time
Windows NT was released, Sybase and Microsoft
parted ways and each pursued their own design and marketing schemes. Microsoft
negotiated exclusive rights to all versions of SQL Server written for Microsoft
operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion
with Microsoft SQL Server. Until 1994, Microsoft's SQL Server carried three
Sybase copyright notices as an indication of its origin.

Since parting
ways, several revisions have been done independently. SQL Server 7.0 was a
rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which
was the first edition to be launched in a variant for the IA-64 architecture.

In the ten
years since release of Microsoft's previous SQL Server product (SQL Server
2000), advancements have been made in performance, the client IDE tools, and
several complementary systems that are packaged with SQL Server 2005. These
include: an ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data
mining server (Analysis Services), and several messaging
technologies, specifically Service Broker and Notification
Services.

SQL Server
2005

SQL Server
2005 (codename Yukon), released in October 2005, is the successor to SQL Server
2000. It included native support for managing XML
data, in addition to relational data. For this purpose, it defined an
xml data
type that could be used either as a data type in database columns or
as literals in queries. XML columns can be
associated with XSD schemas; XML data being stored is verified
against the schema. XML is converted to an internal binary data type before
being stored in the database. Specialized indexing methods were made available
for XML data. XML data is queried using XQuery; Common Language Runtime (CLR) integration was a
main feature with this edition, enabling one to write SQL code as Managed Code
by the CLR. SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries
in T-SQL. In addition, it also defines a new extension to XQuery, called XML
DML, that allows query-based modifications to XML data. SQL Server 2005 also
allows a database server to be exposed over web
services using Tabular Data Stream (TDS) packets encapsulated
within SOAP (protocol) requests. When the data is
accessed over web services, results are returned as XML.

For relational
data, T-SQL has been augmented with error handling
features (try/catch) and support for recursive queries with CTEs (Common Table
Expressions). SQL Server 2005 has also been enhanced with new indexing
algorithms, syntax and better error recovery systems. Data pages are checksummed for better error resiliency, and
optimistic concurrency support has been added for better performance.
Permissions and access control have been made more granular and the query
processor handles concurrent execution of queries in a more efficient way.
Partitions on tables and indexes are supported natively, so scaling out a
database onto a cluster is easier. SQL CLR was introduced with
SQL Server 2005 to let it integrate with the .NET Framework. SQL Server 2005
introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of
database connections for multiple purposes. SQL Server 2005 introduced DMVs
(Dynamic Management Views), which are specialized views and functions that
return server state information that can be used to monitor the health of a
server instance, diagnose problems, and tune performance.

SQL Server
2005 introduced Database Mirroring, but it was not fully supported until the
first Service Pack release (SP1). In the initial release (RTM) of SQL Server
2005, database mirroring was available, but unsupported. In order to implement
database mirroring in the RTM version, you had to apply trace flag 1400 at
startup. Database mirroring is a high availability option that provides
redundancy and failover capabilities at the database level. Failover can be
performed manually or can be configured for automatic failover. Automatic
failover requires a witness partner and an operating mode of synchronous (also
known as high-safety or full safety).

SQL Server
2008

The next
version of SQL Server, SQL Server 2008, was released (RTM) on August 6, 2008and aims to make data
management self-tuning, self organizing, and self
maintaining with the development of SQL Server Always On technologies, to
provide near-zero downtime. SQL Server 2008 also includes support for structured and semi-structured data, including
digital media formats for pictures, audio, video and other multimedia data. In
current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are
generic bitstreams. Intrinsic awareness of multimedia data will allow
specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server
Applications, Microsoft Corp., SQL Server 2008 can be a data
storage backend for different varieties of data: XML, email, time/calendar,
file, document, spatial, etc as well as perform search, query, analysis,
sharing, and synchronization across all data types.

Other new data
types include specialized date and time types and a Spatial data type for
location-dependent data. Better support for unstructured and semi-structured
data is provided using the new FILESTREAM data type, which can be used to
reference any file stored on the file system. Structured data and metadata about
the file is stored in SQL Server database, whereas the unstructured component is
stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as
a BLOB. Backing up and restoring the database backs up or restores the
referenced files as well SQL Server 2008 also natively supports hierarchical
data, and includes T-SQL constructs to directly deal with them,
without using recursive queries.

The Full-text search functionality has been
integrated with the database engine. According to a Microsoft technical article,
this simplifies management and improves performance. Spatial data will be stored
in two types. A "Flat Earth" (GEOMETRY or planar) data type represents
geospatial data which has been projected from its native, spherical, coordinate
system into a plane. A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal
model in which the Earth is defined as a single continuous entity which does not
suffer from the singularities such as the international dateline, poles, or map
projection zone "edges". Approximately 70 methods are available to represent
spatial operations for the Open Geospatial Consortium Simple Features for
SQL, Version 1.1.

SQL Server
includes better compression features, which also helps in improving scalability.
It enhanced the indexing algorithms and introduced the notion of filtered
indexes. It also includes Resource Governor that allows reserving
resources for certain users or workflows. It also includes capabilities for transparent encryption of data (TDE) as well as
compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools,
replication, and data definition will be built around the Entity Data Model. SQL Server Reporting Services will gain charting
capabilities from the integration of the data visualization products from Dundas Data Visualization, Inc., which was
acquired by Microsoft. On the management side, SQL Server 2008 includes the
Declarative Management Framework which allows configuring policies and
constraints, on the entire database or certain tables, declaratively. The
version of SQL Server Management Studio included with SQL
Server 2008 supports IntelliSense for SQL queries against a SQL Server
2008 Database Engine. SQL Server 2008 also makes the databases available via Windows PowerShell providers and management
functionality available as Cmdlets, so that the server and all the running
instances can be managed from Windows PowerShell.

SQL Server
2008 R2

SQL Server
2008 R2 (formerly codenamed SQL Server "Kilimanjaro") was announced at
TechEd 2009, and was released to manufacturing on April 21, 2010 SQL Server 2008
R2 adds certain features to SQL Server 2008 including a master data management system branded as Master Data Services, a central management of
master data entities and hierarchies. Also Multi Server Management, a
centralized console to manage multiple SQL Server 2008 instances and services
including relational databases, Reporting Services, Analysis Services &
Integration Services.

SQL Server
2008 R2 includes a number of new services, including PowerPivot for Excel and
SharePoint, Master Data Services, StreamInsight, Report Builder 3.0, Reporting Services Add-in
for SharePoint, a Data-tier function in Visual Studio that enables packaging of
tiered databases as part of an application, and a SQL Server Utility named UC
(Utility Control Point), part of AMSM (Application and Multi-Server Management)
that is used to manage multiple SQL Servers.

The first SQL
Server 2008 R2 service pack (Service Pack 1) was released on July 11,
2011.

SQL Server
2012

At the 2011 PASS summit on October 11, Microsoft announced
that the next major version of SQL Server, codenamed Denali, would be SQL Server
2012. The release date is set to the first half of 2012

Editions

Microsoft
makes SQL Server available in multiple editions, with different feature sets and
targeting different users. These editions are:

Mainstream
editions

Datacenter

SQL Server
2008 R2 Datacenter is the full-featured edition of SQL Server and is designed
for datacenters that need the high levels of application support and
scalability. It supports 256 logical processors and virtually unlimited memory.
Comes with StreamInsight Premium edition.

Enterprise

SQL Server
Enterprise Edition includes both the core database engine and add-on services,
with a range of tools for creating and managing a SQL Server cluster. It can
manage databases as large as 524 petabytes and address 2 terabytes of memory and supports 8 physical
processors

Standard

SQL Server
Standard edition includes the core database engine, along with the stand-alone
services. It differs from Enterprise edition in that it supports fewer active
instances (number of nodes in a cluster) and does not include some
high-availability functions such as hot-add memory (allowing memory to be added
while the server is still running), and parallel indexes.

Web

SQL Server Web
Edition is a low-TCO option for Web hosting.

Workgroup

SQL Server
Workgroup Edition includes the core database functionality but does not include
the additional services.

Express

SQL Server
Express Edition is a scaled down, free edition of SQL Server, which includes the
core database engine. While there are no limitations on the number of databases
or users supported, it is limited to using one processor, 1 GB memory and 4 GB
database files (10 GB database files from SQL Server Express 2008 R2). The
entire database is stored in a single .mdf file, and thus making it suitable for XCOPY deployment. It is intended as a replacement
for MSDE. Two additional editions provide a superset
of features not in the original Express Edition. The first is SQL Server
Express with Tools, which includes SQL Server Management Studio Basic. SQL Server
Express with Advanced Services adds full-text search capability and
reporting services.

Specialized
editions

Azure

Microsoft SQL
Azure Database is the cloud-based version of Microsoft SQL Server,
presented as software as a service on Azure Services Platform.

Compact (SQL CE)

The compact
edition is an embedded database engine. Unlike the other editions of SQL Server,
the SQL CE engine is based on SQL Mobile (initially designed for use with
hand-held devices) and does not share the same binaries. Due to its small size
(1 MB DLL footprint), it has a markedly reduced feature set compared to the
other editions. For example, it supports a subset of the standard data types,
does not support stored procedures or Views or multiple-statement batches (among
other limitations). It is limited to 4 GB maximum database size and cannot be
run as a Windows service, Compact Edition must be hosted by the application
using it. The 3.5 version includes considerable work that supports ADO.NET
Synchronization Services.

Developer

SQL Server
Developer Edition includes the same features as SQL Server Datacenter Edition,
but is limited by the license to be only used as a development and test system,
and not as production server. This edition is available to download by students
free of charge as a part of Microsoft's DreamSpark program.

Embedded (SSEE)

SQL Server
2005 Embedded Edition is a specially configured named instance of the SQL Server
Express database engine which can be accessed only by certain Windows
Services.

Evaluation

SQL Server
Evaluation Edition, also known as the Trial Edition, has all the features
of the Enterprise Edition, but is limited to 180 days, after which the tools
will continue to run, but the server services will stop

Fast
Track

SQL Server
Fast Track is specifically for enterprise-scale data warehousing storage and
business intelligence processing, and runs on reference-architecture hardware
that is optimized for Fast Track.

Parallel Data Warehouse (PDW)

A massively parallel processing (MPP) SQL Server
appliance optimized for large-scale data warehousing such as hundreds of terabytes.

To know more about Architecture and others components, follow the below link-
http://en.wikipedia.org/wiki/Microsoft_SQL_Server

SQL Server 2012 – New Features – Contained Databases

2012-06-29T07:50:45Z

A very common problem that has plagued both the DBAs and developers for a
long time is migrating databases to different SQL Server instances for
application development and testing purposes OR for various failover conditions.
Databases are not really independent units. They have many server dependent
objects like logins, SQL Server Agent jobs, linked servers, different collations
etc. A lot of these dependencies cause problems when working with temporary
objects and therefore it is always challenging to migrate databases for e.g.,
for testing and quality assurance.

SQL Server 2012 introduces a new feature called Contained Databases, which
intends to reduce and eventually eliminate SQL Server instance dependencies that
databases currently have. The intention of this feature is to make database
migrations easier with less work involved in reproducing and validating the
instance level dependencies.

Following is a list of some of the issues that are solved in the SQL Server
2012 RC0 release

We can now create a user with a password at the database level – a

“contained database user“. The user authentication is done at

the database level and the applications just need to change their connection

strings. This solves a very common problem of “Orphaned Users“.

In case of a failover, users would be able to connect to the secondary server,

without creating logins on the instance hosting the secondary.
Collation conflict between the SQL Server instance and the

database is one common problem that every DBA and developer has experienced. In

SQL Server 2012, tempdb will automatically create the temporary objects by using

the collation of the “Contained Database”, instead of the collation of the

server. This works very well if you are working with one database or multiple

databases having the same collation. The issue with queries joining
temporary tables from multiple databases with different collations is still

not resolved.
We can get a list of queries and objects that are not contained or
cannot be separated from the instance by using DMVs (sys.dm_db_uncontained_entities (Transact-SQL) and events (database_uncontained_usage_event). In other words, SQL Server

2012 provides the information about when the migration crosses the containment

boundary.
Using the contained databases, a cloud environment can be
simulated with clear boundaries between the database and the SQL Server

instance.

Given all the advantages of contained databases, the only containment
option SQL Server 2012 gives is PARTIAL - which means that it
allows the features that cross the application boundary – containment is only
observed not enforced. In the future releases, we can hope for further extension
of this feature dealing with other dependencies. Even though SQL Server 2012
does not provide fully contained databases, it is a great first step towards
database autonomy. I have had many incidents in my past jobs
where I had to migrate databases (as part of application development) to new
servers and it was not something I really enjoyed doing. I am really glad that
this feature is finally available. Though not complete but it is a step in the
right direction.

Reference Links:

http://sql-articles.com/articles/general/contained-database-sql-server-2012/

Parallel Data Warehouse Overview

2010-09-14T12:15:37Z

Introduction

SQL Server 2008 R2 Parallel Data Warehouse (PDW) is a highly scalable appliance for enterprise data warehousing. The PDW works by using Massively Parallel Processing Software and Appliance hardware to control several physical servers each running their own instances of SQL Server 2008. This control process allows the PDW to scale out query processing and objects across multiple nodes of CPU, storage etc.. This provides a significant boost of performance to even the widest queries for the widest tables. These instances all still appear as one instance to the end user even though a single PDW table may be distributed across several servers.

The MPP functionality comes from an acquisition of Datallegro by Microsoft in summer 2008. The engineering teams at Microsoft have been working on rewriting the software to work with SQL Server and have now accomplished their goal of releasing V1 of the PDW offering. As V1’s go it is very strong and already competing directly with the Teradata’s and Neteeza’s of the world. Currently the PDW offering is scaling up into the hundreds of terabytes without breaking a sweat.

The benefit to the MPP architecture and Microsoft’s implementation is that from a cost per TB or query perspective, Microsoft comes out way ahead. This is because the PDW is built on and expanded by commodity hardware. Not to say these are netbooks chained together, but we are talking midgrade to enterprise quality HP and Dell servers, not $800K Teradata racks.

This is a new direction for Microsoft since they have, until now, operated in the Symmetric Multi-processing (SMP) world, where the only option is larger more powerful single servers. In the SMP world, all the processing takes place on a single node or physical instance of the database. Limits are created by the CPU, memory and storage capacities. In PDW large tables are partitioned across multiple physical nodes, where each node has its own CPU, memory, and storage. This is called a “shared nothing“ architecture.

PDW Architecture

In PDW those multiple nodes controlled by the software and appliance are referred to as “Control Nodes”. Remember there are no proprietary hardware components in a PDW system all of it is commodity hardware, networking and storage. As the environment grows over time more control nodes can be added or upgraded individually with faster CPU, memory or network components. As new hardware comes out those servers can be repurposed for other roles in the enterprise that don’t require the same level of performance such as file servers. I have included some diagrams from Microsoft’s official documentation to help highlight the architecture. The first one is below.

For those of you who aren’t familiar with what the term appliance means I’m definitely not talking about your refrigerator. I am hungry though, not to self, no more in depth writing without packing a lunch. In computing an appliance is a special purpose application whose software and hardware work together in a single unit for a specific purpose. In PDW the software and server running it work together to accomplish scaling the queries and data across multiple nodes and tracking metadata, query response etc... The PDW appliance has a set of nodes that look like the diagram below from Microsoft.

The architecture of a typical PDW system would look something like the next diagram.

Some important things to note about the design of this architecture:

- The components are balanced with each other to avoid bottlenecks and all components are mirrored for enterprise quality redundancy.

- The control node sends the queries to the proper compute nodes, then organizes and returns the results.

- Queries are much fast then on an SMP system since the data is spread evenly over the different nodes and their storage.

- Scaling the architecture requires simply adding capacity to the appliance, eliminating the need for a replace or forklift style upgrade.

Server Roles in the PDW

There are several different server roles in the PDW architecture so let’s explore those.

Control Node

Clients connect to the control node since the PDW MPP engine runs here. These nodes contain no permanent user data, they only collect and display the results from queries. The control node is the central point for all hardware monitoring and contains the metadata about the PDW configuration, databases and objects. Its instance of SQL Server is there primarily for metadata and to make use of TempDB for some query types.

Compute Node

The compute nodes store the user data and perform most of the query execution. They are not typically accessible to the outside world since the Control node is doing all the connecting, collecting and correlating.

Landing Zone

We are not going into much detail on loading data in this article but there are some very specific things we will cover later in the series including the new adapters for SSIS and DWLoader utilities. The landing node is where the data can be “landed” prior to final loading into the compute nodes. This server CAN be access by windows users and can also run 3^rd party hardware and software to help with data loading. Think of this as your staging or final load sandbox on the PDW.

Backup Node

This node will store your appliance backup files, and can also be access via windows and loaded with 3^rd party hardware or software.

Management Node

If the control node is the slave driver, then the management node is the safety net. This node runs the domain controller for the PDW. You didn’t think Microsoft was going to trust this bad boy to your rusty old domain controller? No way, a shiny new one comes right in the rack. IT also is used for patch deployment and holds images of the different nodes in case a reimage is required.

Hub and Spoke Architecture

Enterprise Data Warehouses typically fit into one or several of the following areas:

1. Centralized data warehouse with the “single version of the truth” – These can be expensive and difficult to maintain depending on your companies staff and capabilities.

2. Decentralized data marts or departmental analysis with no central repository or EDW. – These can be more responsive to the business but are not generally the best tools for company analysis since they contain different versions of the truth and are very difficult to keep aligned across the organization. In addition they are often designed and built using whatever toolset the business unit has at hand so the technologies involved are often at odds as well.

3. Hub and spoke architectures with a central EDW and department data marts, fed from the EDW. Hub and spoke architectures are typically the best solutions for the largest enterprises. Those enterprises that would be looking at a PDW solution. It offers the combination of a strong centralized EDW which provides company wide data and single sources of truth for important information, while allowing for synchronization to smaller data marts for more customized departmental analysis. See the diagram below for an example of a Hub and spoke architecture.

The parallel copy capabilities from PDW provide a lot of improved flexibility here to move large amounts of data to the data marts. This parallel copying functionality is in theV1 release of PDW.

There are two types of tables in a PDW implementation. Replicated tables are tables where the table is stored in its entirety on each compute node. Distributed tables are split evenly over each compute node using a hashing algorithm. As you can imagine typically your replicated tables would be your dimension tables and those giant fact tables would be distributed across the compute nodes.

Connecting to PDW

PDW ships with a client tool to connect to the system called Nexus. Nexus looks and acts similarly to SSMS or SQL Server Management Studio but it is not Management Studio, it only runs PDW implementations. See an example of connecting to a PDW with Nexus below.

Creating Objects in PDW

There are several new object types in PDW but overall creating them is still essentially the same, with a twist. For instance:

Create Database

Creates a new database and the files used to store the database.

Syntax

CREATE DATABASE database_name

[ WITH ( <database_option> [ ,…n ] ) ][;]

<database_option> ::= AUTOGROW = ON | OFF

| REPLICATED_SIZE = replicated_size [ GB ]

| DISTRIBUTED_SIZE = distributed_size [ GB ]

| LOG_SIZE = log_size

Example:

CREATE DATABASE bidnPDW

WITH

   (REPLICATED_SIZE = 200 GB,
    DISTRIBUTED_SIZE = 800 GB,
    LOG_SIZE = 300 GB);

You’ll notice a trend beginning here. We are specifying options for replicated table areas and distributed table areas. These apply for the database as a whole and will be stored individually or spread evenly based on the type of table we create.

Creating Tables

A. Creating a replicated table

The following example creates a replicated table with three columns and with no partitions. The id column includes a NOT NULL constraint. Notice there is no “distributed” flag engaged here.

CREATE TABLE bidnTable1
(   id integer NOT NULL,
    lastName varchar(20),
    zipCode varchar(6)
);

B. Creating a distributed table

The following example creates the same table as the previous example; however, in this example the table is distributed (on the id column) instead of replicated.

CREATE TABLE bidnTable2
(   id integer NOT NULL,
    lastName varchar(20),
    zipCode varchar(6)
)

WITH ( DISTRIBUTION = HASH (id) );

C. Creating a partitioned table

The following example creates the same table as in example A with the addition of RANGE LEFT partitioning on the zipCode column.

CREATE TABLE bidnTablePart

(   id integer NOT NULL,
    lastName varchar(20),
    zipCode integer
)

WITH    ( PARTITION

( id RANGE LEFT FOR VALUES (10, 20, 30, 40, 50, 60, 70, 80, 90)));

In this example, data would be sorted into the following partitions:

Partition	1	2	3	4	5	6	7	8	9	10
Value	col <= 10	10 < col <= 20	20 < col <= 30	30 < col <= 40	40 < col <= 50	50 < col <= 60	60 < col <= 70	70 < col <= 80	80 < col <= 90	90 < col

If we created the table with RANGE RIGHT instead of RANGE LEFT (default), the data would be sorted into the following partitions (this is the same as in your normal SQL Server partitioning:

Partition	1	2	3	4	5	6	7	8	9	10
Value	col < 10	10 <= col < 20	20 <= col < 30	30 <= col < 40	40 <= col < 50	50 <= col < 60	60 <= col < 70	70 <= col < 80	80 <= col < 90	90 <= col

E. Creating a table with date partitioning

The following example creates a new table, bidnTableDatePT, with partitioning on a date column and distributing the data over the compute nodes on the OrderKey. This will provide the scalability and increased seek speed gained from partitioning. Notice we have to specify the distribution key since it’s a distributed table and then our partitioning information second.

CREATE TABLE bidnTableDatePT (

    l_orderkey      bigint,
    l_partkey       bigint,
    l_suppkey       bigint,
    l_shipdate      date,
    l_commitdate    date,
    l_receiptdate   date,
    l_shipinstruct char(25),
  l_shipmode      char(10),
    l_comment       varchar(44))

WITH

  ( DISTRIBUTION = HASH (l_orderkey),
    PARTITION ( l_shipdate
    RANGE RIGHT FOR VALUES

(

'1992-01-01','1992-02-01','1992-03-01','1992-04-01','1992-05-01','1992-06-01','1992-07-01','1992-08-01','1992-09-01','1992-10-01','1992-11-01','1992-12-01',

'1993-01-01','1993-02-01','1993-03-01','1993-04-01','1993-05-01','1993-06-01','1993-07-01','1993-08-01','1993-09-01','1993-10-01','1993-11-01','1993-12-01',

'1994-01-01','1994-02-01','1994-03-01','1994-04-01','1994-05-01','1994-06-01','1994-07-01','1994-08-01','1994-09-01','1994-10-01','1994-11-01','1994-12-01',

'1995-01-01','1995-02-01','1995-03-01','1995-04-01','1995-05-01','1995-06-01','1995-07-01','1995-08-01','1995-09-01','1995-10-01','1995-11-01','1995-12-01',

'1996-01-01','1996-02-01','1996-03-01','1996-04-01','1996-05-01','1996-06-01','1996-07-01','1996-08-01','1996-09-01','1996-10-01','1996-11-01','1996-12-01',

'1997-01-01','1997-02-01','1997-03-01','1997-04-01','1997-05-01','1997-06-01','1997-07-01','1997-08-01','1997-09-01','1997-10-01','1997-11-01','1997-12-01',

'1998-01-01','1998-02-01','1998-03-01','1998-04-01','1998-05-01','1998-06-01','1998-07-01','1998-08-01','1998-09-01','1998-10-01','1998-11-01','1998-12-01'

)));

We can also do some old favorites in PDW such as Create Table as Select and regular joins between distributed tables and replicated tables. The two examples below are simple versions of these queries.

Create Tables as Select

CREATE TABLE FactResellerSalesNew
WITH (

DISTRIBUTION = HASH(ProductKey),
PARTITION ( OrderDateKey
RANGE RIGHT FOR VALUES
(20010101, 20020101, 20030101,20040101, 20050101))
,CLUSTERED INDEX(OrderDateKey))

AS
SELECT * FROM dbo.FactResellerSales

Query Tables with Join

SELECT b.ProductKey, b.OrderQuantity

FROM dbo.DimPromotion a /* Replicated Table*/

JOIN dbo.FactInternetSales b /* Distributed Table*/ ON a.PromotionKey = b.PromotionKey

WHERE a.PromotionKey = 13;

In this article I covered the basics of the PDW architecture, how it fits together and what the major components are. We also discussed why this is an exciting new offering and how it compares with other solutions you or your company may be considering. These units are already up at Microsoft training centers around the country and available for testing with your data if you’re interested. Please let me know or contact your Microsoft TSP in your area to get you setup for a proof of concept.

In the next article we’ll talk about working with additional objects in the PDW environment, how to create and manage them as well as some of the new flags and options for those objects. It’s important to understand that while not all companies will jump on this PDW appliance it provides an industry leading, cost effective and easily scalable solution and options over the competition. Next week I will shoot you some resources on the new PDW system from Microsoft, but for now I will leave you with some terms to remember.

Terms to Remember

1. MPP – Massively Parallel Processing – Scaling processing across multiple nodes or servers

2. SMP – Symmetric Multi Processing – Processing occurs within ones instance of SQL Server and its CPU, memory and storage.

3. EDW – Enterprise Data Warehouse

4. Hub and Spoke – Central EDW with departmental or business function data marts for specialized analysis.

5. Distributed Tables – Tables that are spread across the compute nodes in a PDW environment. These are typically fact tables and are evenly spread using a hashing algorithm.

6. Replicated Tables – These tables are all located in their entirely on the compute nodes and are typically dimension or lookup tables that do not require the distribution capabilities of PDW to manage them.

Monitoring tempdb Transactions and Space usage

2010-04-14T06:28:00Z

As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any adhoc query by the user can eat all the space available to the tempdb database resulting decrease in the performance of other applications running under the same instance. So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb

Use the below query to check the current tempdb size:

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Tempdb session File usage

sys.dm_db_session_space_usage : Returns the number of pages allocated and deallocated by each session for the database.

sys.dm_exec_sessions: Gives details about the sessions.

SELECT

sys.dm_exec_sessions.session_id AS [SESSION ID],

DB_NAME(database_id) AS [DATABASE Name],

HOST_NAME AS [System Name],

program_name AS [Program Name],

login_name AS [USER Name],

status,

cpu_time AS [CPU TIME (in milisec)],

total_scheduled_time AS [Total Scheduled TIME (in milisec)],

total_elapsed_time AS [Elapsed TIME (in milisec)],

(memory_usage * 8) AS [Memory USAGE (in KB)],

(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)],

(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],

(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],

(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],

CASE is_user_process

WHEN 1 THEN 'user session'

WHEN 0 THEN 'system session'

END AS [SESSION Type], row_count AS [ROW COUNT]

FROM sys.dm_db_session_space_usage

INNER join

sys.dm_exec_sessions

ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.

SELECT

transaction_id AS [Transacton ID],

[name] AS [TRANSACTION Name],

transaction_begin_time AS [TRANSACTION BEGIN TIME],

DATEDIFF(mi, transaction_begin_time, GETDATE()) AS [Elapsed TIME (in MIN)],

CASE transaction_type

WHEN 1 THEN 'Read/write'

WHEN 2 THEN 'Read-only'

WHEN 3 THEN 'System'

WHEN 4 THEN 'Distributed'

END AS [TRANSACTION Type],

CASE transaction_state

WHEN 0 THEN 'The transaction has not been completely initialized yet.'

WHEN 1 THEN 'The transaction has been initialized but has not started.'

WHEN 2 THEN 'The transaction is active.'

WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'

WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'

WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'

WHEN 6 THEN 'The transaction has been committed.'

WHEN 7 THEN 'The transaction is being rolled back.'

WHEN 8 THEN 'The transaction has been rolled back.'

END AS [TRANSACTION Description]

FROM sys.dm_tran_active_transactions

Long running Queries

sys.dm_exec_requests : Returns information regarding the requests made to the database server.

SELECT

HOST_NAME AS [System Name],

program_name AS [Application Name],

DB_NAME(database_id) AS [DATABASE Name],

USER_NAME(USER_ID) AS [USER Name],

connection_id AS [CONNECTION ID],

sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],

blocking_session_id AS [Blocking SESSION ID],

start_time AS [Request START TIME],

sys.dm_exec_requests.status AS [Status],

command AS [Command Type],

(SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],

wait_type AS [Waiting Type],

wait_time AS [Waiting Duration],

wait_resource AS [Waiting FOR Resource],

sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],

percent_complete AS [PERCENT Completed],

estimated_completion_time AS [Estimated COMPLETION TIME (in mili sec)],

sys.dm_exec_requests.cpu_time AS [CPU TIME used (in mili sec)],

(memory_usage * 8) AS [Memory USAGE (in KB)],

sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)]

FROM sys.dm_exec_requests

INNER join

sys.dm_exec_sessions

ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

WHERE DB_NAME(database_id) = 'tempdb'

Ranking Functions in SQL Server

2010-03-24T09:41:00Z

SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query.

ROW_NUMBER()

The ROW_NUMBER function simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set. You can create these groupings (partition the records) using the PARTITION BY clause. The syntax for ROW_NUMBER function is:

ROW_NUMBER() OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])

OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated. If you don't specify it, you will get an error similar to "Incorrect syntax near 'ROW_NUMBER', expected 'OVER'."
PARTITION BY clause is not mandatory and if you don't specify it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied. When you specify a column/set of columns with PARTITION BY clause then it will divide the result-set into record groups/partitions and then finally ranking functions are applied to each record group/partition separately and the rank will restart from 1 for each record group/partition separately.
ORDER BY clause is mandatory and if you don't specify it you will get an error similar to "The ranking function "ROW_NUMBER" must have an ORDER BY clause.". With this clause you specify a column or a set of columns which will be used to order the records within a result-set or within record groups/partitions of a result-set. Please note you can specify only those columns which are being made available by the FROM clause of the query. Also, you can not specify an integer to represent the position of a column, if you do so you will get "Windowed functions do not support integer indices as ORDER BY clause expressions.

Example: - (You can use AdventureWorks database to look at the resultset of below queries),

--This script assign sequential number to each row
--of resultset which is ordered on BirthDate column

SELECT ROW_NUMBER() OVER (
   ORDER BY BirthDate) AS RowNumber,
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM [HumanResources].[Employee]

--This script assign sequential number to each row
--of resultset as it is stored in the database
SELECT ROW_NUMBER() OVER (
   ORDER BY (SELECT 1)) AS RowNumber,
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM [HumanResources].[Employee]

RANK()

The RANK function instead of assigning a sequential number to each row as in the case of the ROW_NUMBER function, it assigns rank to each record starting with 1. If it encounters two or more records to have the same ORDER BY <columns> values, it is said to be a tie and all these records get the same rank.

RANK() OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])

--This script assign rank to each row of result-set which is
--ordered by Title column. If two or more records happen to have
--same value for Title Column they will get the same rank
SELECT RANK() OVER (
ORDER BY Title) AS [RecordRank],
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM HumanResources.Employee

Storage Reporting Tool

2010-03-03T07:30:00Z

If you have multiple servers and want to capture the space availability to plan for future, then this tool can be used collect those information.

This tool will only run in Windows OS and a SQL server installed in one of the machine to store the data.

Job Fails on Linked Server - Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)

2010-03-03T07:06:00Z

Problem: Having a SQL Agent Job, which executes a Stored prcedure to connect to a remote SQL server via linked server and extract/manupulate the data.

The SP runs successfully in SSMS but get failed, if run through an agent Job.

NOTE: A Windows a/c is used here to connect to remote server and the a/c is having dbo permission in both source and remote server databases. Both the servers are on same domain.

Resolution:

Step -1:
a. Make sure that the a/c used in linked server/Job having necessary permissions on both source and remote server databases.
b. Remove the run as "Login a/c" for the Job step
c. Remove the Login a/c, if it defined under "Local Server Login to Remote Server Login Mappings" to impersonate.
d. In Linked Server properties, Choose "Be made using the Login's current security context",if the a/c having permissions on remote server and is used to run the SQL Agent Service else choose "Be made using this security context" and define the Remote Login and password.

Issues with Updatable Subscriptions for Transactional Replication

2010-03-02T12:36:00Z

Careful with Replication type "Updatable Subscriptions for Transactional Replication" when your application does not use column names in INSERT and SELECT statements and has database triggers

NOTE:This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Problem : Configuration of replication type "Updatable Subscriptions for Transactional Replication" made application stop working because of column names not defined in some of INSERT/SELECT statements and "NOT FOR REPLICATION" option not defined in Triggers.

Resolution : Replication needs to be removed ASAP and dropping of extra columns and constraints created by Replication.

Please follow below steps to remove replication and drop constraints/columns,

1. Remove Subscription and Publication (Please follow the below link)

http://support.microsoft.com/default.aspx/kb/324401

2. Removing Columns and constraints from table/articles created by Replication

1. Run the below query on publisher database, copy the output and execute the output on publisher database to remove constraints prefixed with "msrepl_tran_version".
SELECT 'ALTER TABLE '+ s.name+ '.'+ OBJECT_NAME(o.parent_object_id) + ' DROP CONSTRAINT ' + o.name

    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.name LIKE '%MSrepl_tran_version_default%'

2. Run the below query on publisher database, copy the output and execute the output on publisher database to remove columns named "msrepl_tran_version".

SELECT 'ALTER TABLE '+TABLE_SCHEMA+'.'+TABLE_NAME + ' DROP COLUMN msrepl_tran_version'

FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='msrepl_tran_version'

Above steps can be followed for subscriber database, if needed.

Suggestion : before configuring this replication type, please go through the below link to understand more about this and considering for your environment
http://msdn.microsoft.com/en-us/library/ms151718.aspx