Sql-server – Database Architecture Identifiers

Architecturebest practicesdatabase-designsql server 2014

I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, also my company is looking for a DBA dev so there is that.

Also this question comes as a follow up to Queries from different tables locking each other (LCK_M_X) .

This DB has a lot of Unique Identifiers linked with FKs, (Tid,Did,Cid,id) and a few more not so big, every time i make a transaction its inserted in "DidRegistry" table.

                  DidRegistry

DidRegistry

As you can see from "CreationMoment" column, This table gets lots of inserts.. i wanted to change Datetime to Datetime(2)7, what do you think?

Now this part i am not sure about, when i put load on the DB, sp_whoisactive never shows the locks in Didregistry, but in different tables and in different queries (see link on top), those queries are inside transactions, the transactions come from the Developers code, with SQLProfiler i didn't find a way to see what queries belong to what transactions.

I am at a loss if i am chasing the wrong rabbit hole or if this could be a Design problem for future bigger clients with more load profiles.

Best Answer

To capture client API transactions, add the TM: Begin Tran completed, TM: Commit Tran completed, and TM: Rollback Tran completed events in the trace along with the TransactionID column. Queries with the same TransactionID are part of the same transaction.

Note that Profiler is deprecated so I suggest you move to Extended Event tracing. The events are similarly named.

Use datetime2(7) to avoid loss of fractional second accuracy.

Related Question