SQL Server Performance Forum – Threads Archive

Real Time Data Triggering

Hi all We have a feeder tha feeds stockmarket messages to our SQL server. Every message is inserted with an insert statement to a table called "feed". The "feed" table has triggers on it that parse each message and fill in other tables of the database. Inserts are done at a rate of about 3-10 per second. If inserts start getting faster, our feeder starts filling up it’s queue cause the sql server cannot parse the messages fast enough, although sql server cpu and disk usage are both under 30% ! If we deactivate a few triggers, our feeder queue drops and messages are parsed faster. As soon as we activate these triggers again, the story starts all over again. My question is this: How can it be that CPU is under 30% in all cases, disk usage is low, but sql cannot execute the triggers fast enough? Any ideas? Thanx, Alex

In profiler you would capture SQL Statements and optimize them, you can see how long it takes for a statement to execute, the io it performs, etc. Using PERFMON you can capture the physical disk activity, memory utilization, and cpu utilization ALL specific to SQL Server. The events that will show the triggers are under the stored procedure section (SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtStarting, SP: StmtCompleted). Also get TSQL StmtStarting and StmtCompleted and make sure to get the duration column. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

That’s exactly my problem… The duration of each insert as shown in profiler is about 1020 – 1250 ms, (which is outrageously high) while perfmon shows 20-25% total CPU utilization and very low IO use !!! Why isnt SQL server utilizing the extra CPU power to process the inserts (and subsequent triggers) faster ??? Thanx again Alex

I also just found out, that if I set profiler to show only locks/unlock/deadlocks etc, I’m getting about 6000-7000 locks per second !!!! (no deadlocks) Is this normal ? Alex

Make sure you have a good choice of indexes. If your statements does not resolve fast enough than you can experience lock problems.
If your indexes are ok, you might try to use some lock hints to reduce lock problems. Where you can effort to read uncommitted data (if there is such a case), use WITH (NOLOCK). Obviously it refers to SELECT statements only.
On UPDATE/DELETE/INSERT, try the ROWLOCK/PAGLOCK and/or UPDLOCK. In the home page of this site you can find some useful tips like
http://www.sql-server-performance.com/reducing_locks.asp
http://www.sql-server-performance.com/reducing_locks_hints.asp

Another option is to get rid of the trigger to speed up the insert process and create a seperate application or database job that runs every few seconds to make those inserts for you.

unfortunately "every few seconds" is not an option… does anyone know why cpu stays so low, while statements take
more than a sec to complete? shouldnt cpu hit the roof if
those triggers are so rough? that’s what really bothers me… I/O is low too. So where’s the bottleneck? SQL internals maybe?

Try capturing the insert in the profiler and you can check to see which part of trigger is taking long time to finish. Sounds like there is a lock which is preventing the insert from happening faster.

thanx rage, i’ll do hat and come back with the results

The locks are the bottleneck. If for example, a certain process holds an exclusive lock on a table, than other processes that wish to modify or read (committed) data from it will have to wait until this lock is released. CPU has nothing to do yet everything’s slow. In this case the things you would want to check is why is process obtainning a table lock. Maybe it can be solved with a better choice of indexes. Maybe your transaction isolation level is higher than what you need. Maybe you are using a lock hint that is causing the problem.
You said you are inserting to "feed" table and that the triggers are inserting rows to other table. Try to first identity the objects (tables) that are causing the locks you were talking about.
If you run sp_lock with sp_who2 you can see which process is holding what lock on what resource and it’s duration. together with a trace that monitors the activity it should give you a better idea of the source of the problem.
If you post some code, maybe we could help more.
Again, I would recommand you read a bit about b/locks. The links above, BOL and Inside SQL Server are good sources.

Follow the refernce by Bambola and this linkhttp://www.sql-server-performance.com/trigger_tuning.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanx a million. I am in the process of trying to identify the locks,
I’ll get back to you as soon as I find out whats causing the locks. thanx again!

]]>