Sql Server Replication : Query Tracer Tokens using T-SQL (MStracer_tokens & MStracer_history)

Interesting one today:

Last time we walked through the T-SQL steps to insert a tracer token to measure latency in replication topology. Today, we’ll look at an alternate method to query tracer token details; a.k.a. MStracer_tokens &  MStracer_history meta tables.

Along with the T-SQL procedures (sys.sp_helptracertokenhistory), Sql Server also provides a way to query the tracer tokens using metadata tables i.e. MStracer_tokens &  MStracer_history.  They keep track of details for each token. Querying them will provide us necessary information.

--
-- Query tracer token tables
--
SELECT	  publication_id
	, agent_id
	, t.publisher_commit
	, t.distributor_commit
	, h.subscriber_commit

FROM MStracer_tokens t
JOIN MStracer_history h
	ON t.tracer_id = h.parent_tracer_id

ORDER BY t.publisher_commit DESC
GO

Query Tracer Token Details

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Advanced Query Tuning Concepts with Understanding of Hash Joins

Interesting one today:

This article is part 3 in a series on Advanced Query Tuning Concepts, that are good to be familiar with. Full list is here.

Hash Join

When both the data sets are large with unsorted & unindexed data sets, Hash Join is the best operator. This is the most complicated data set to process and Hash Join could process them efficiently.

Hash Joins follow a complicated logic in identifying matching records. We’ll get into the details in a future post.

There are different types of Hash Joins

1. In-memory hash join
2. Grace hash join
3. Recursive hash join

Note:

Hash Joins end up being used a lot in intermediary steps. During large table joins, when virtual tables are generated during intermediary steps, the subsequent joins on those intermediary tables are performed using Hash Joins — as these intermediary tables are not indexed or sorted. 

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Advanced Query Tuning Concepts with Understanding of Merge Joins

Interesting one today:

This article is part 2 in a series on Advanced Query Tuning Concepts, that are good to be familiar with. Full list is here.

Merge Join

When both the inputs are fairly large size with indexed and sorted data sets, Merge Join is very efficient in returning matching records. See the image below:

In this example, we have 2 similarly sized tables (this is important), that are Indexed (and hence sorted — this is also important). When they are joined on ID columns (ON B.ID = S.ID) that are indexed, Sql Server uses Merge Join

Simple Explanation:

From the sorted input lists, Sql Server takes one record from each table and compares them; If they match, it is returned. If not, the lower value row is discarded and next row from the same table is obtained for next comparison. It keeps iterating until the end of that table.

• Both the tables need to be similarly sized in comparison to each other.
• The join columns in both the data sets need to be indexed & sorted
  • This is important for efficient processing of the records.
  • If they are not already sorted, Merge Join adds a sorting step resulting in longer processing times — making it inefficient.

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Advanced Query Tuning Concepts with Understanding of Nested Joins

Interesting one today:

This is Part 1 of a series on Advanced Query Tuning Concepts, that are good to be familiar with. Today we’ll cover Nested Loop Joins. Full list is here.

Nested Loop Join

Nested Loop Join is utilized when, in a join, one input is a small data set (fewer than 10 records) and the other is a large data set that is indexed on the columns used in join.

Simple Explanation:

For each record in the SmallTable, it searches entire LargeTable for matching records. It keeps iterating for all the records in the SmallTable. On the first glance it seems like an inefficient method, but it is the most efficient method.

• Since we are making the SmallTable as the outer input table, it limits the number of times we need to loop through.
• Since the LargeTable is indexed, a quick Index Seek returns the matching value for the given value from SmallTable (i.e. ON B.ID = S.ID).
  • Hence the reason for Index Scan for SmallTable, with Index Seek for Large table.
  • The other way round would be very inefficient (Scaning BigTable and Seeking on SmallTable)

In this combination of Small vs. Large data sets, Nested Loop Join is the most efficient operator.

 

 

Hope this helps,
_Sqltimes

Read Full Post »