Help with performance (triggers)

Question

Help with performance (triggers)

sergio.arv

SSC Rookie

Points: 49
More actions
February 22, 2016 at 2:18 pm

#308237

Hi,
Actually i have a huge Production DB with different machines identified by an id (Machineid), so , now i need to monitor each machine (last write data timestamp), my first idea was to implement a trigger (AFTER INSERT) and create another table (Timestamp,Machineid), then when a data was inserted, the trigger update the Timestamp field on the table using the Machine.
Im not sure if this is the most optimal solution or not (db performance). Ill really appreciate your help with this!.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'd suggest you to use extended events. There are many places where you can find information about how to do what you want.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 2

sergio.arv (2/22/2016)
Hi,
Actually i have a huge Production DB with different machines identified by an id (Machineid), so , now i need to monitor each machine (last write data timestamp), my first idea was to implement a trigger (AFTER INSERT) and create another table (Timestamp,Machineid), then when a data was inserted, the trigger update the Timestamp field on the table using the Machine.
Im not sure if this is the most optimal solution or not (db performance). Ill really appreciate your help with this!.

You don't need a separate table or a trigger. Lookup sys.dm_db_index_usage_stats in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

It's a complex topic and there is no quick win.

Triggers will likely be a performance disaster, forget about it.

Trace could help, but again could turn into a performance nightmare and/or huge waste of disk space.

Extended Events will likely help, but support in 2008 is very limited. I wrote a blog post on the topic that you can find here: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

The idea behind is that you can use Extended Events to capture reads and writes to a table and record them to a target table. I have no idea whether this could end up working in 2008 though. Sure thing, it won't work as it is, you will need to rewrite parts of the code.

Good luck!

-- Gianluca Sartori