DUPLICATE INDEXES

============== exact duplicates ==============================
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

==================================================================
-----------------------------
Overlapping indxes
------------------------------
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + '%'
or c2.cols like c1.cols + '%') ;

=====================================================

SQL Server Profiler Stored Procedures

SQL Server supports the following system stored procedures that are used by SQL Server Profiler to monitor performance and activity.

sp_trace_create
sp_trace_setfilter
sp_trace_generateevent
sp_trace_setstatus
sp_trace_setevent

SQL SQRVER with SP's

http://sqlserverpedia.com/wiki/SQL_Server_Release_Date_Calendar#Checking_the_SQL_Server_Version

DBA WORLD: Microsoft CodeNames for Products

DBA WORLD: Microsoft CodeNames for Products

Microsoft CodeNames for Products

http://en.wikipedia.org/wiki/List_of_Microsoft_codenames#SQL_Server_family

Instant File Initialization Speeds SQL Server

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.


Read more: http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server

Bidirectional, Transactional Replication VS Peer-to-Peer Transactional Replicatio

With Bi-Directional replication if you want to make a schema change you must drop your publications, and then drop your subscriptions, make the schema change and then recreate the publications and subscriptions on both sides. Then you start up the agents and you are in business again. The best way to deploy subscriptions in bi-di transactional topologies is by restoring the subscriber via a backup.

Now if your users are banging away on your first publisher while you do this it will be hard to achive consistency between both nodes in your bi-directional replication toplogy, as these updates may or may not be in the backup. You can run validations after the fact but it hard to cobble together a consistent topology while you are live. It can be done, but its hard. Plus table locks are held when the publication is created. So you can run into a lot of locking.

With peer-to-peer in SQL 2005 you again have to quiesce the system, make your change, ensure it is everywhere and then let your users back on. Its the same problem.

WIth merge replication and bi-di transactional the model is a hub and spoke methodology. You have a central publisher and multiple subscribers all talking to this central publisher which is the clearing house for changes. Peer to peer is a mesh topology which means a<->b, b<->c, c<->a. b can drop off and a can replicate with c until b comes back on.

You can't do this using bi-directional transactional replication and merge.

More on
http://technet.microsoft.com/en-us/library/ms151196.aspx

======================================================

How to Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms152536.aspx