October 13, 2014 at 6:54 pm
Hi all ,
I wonder how to tune this INSERT Query( it causes high CPU load) :
INSERT INTO RTS
(
ReaderId,
FileId,
MS,
Mc,
GS,
LS,
TS,
TD
)
VALUES
(
@RId,
@FileId,
@ms,
@mc,
@Gs,
@ls,
@TS,
)
This query is executed many times a day
This table has 1.233.666 records
Has 3 indexes :
1 clustered index ( primary key ) fill factor = 90 %
1 nonclustered index based on ReaderId.--> fill factor = 80 %
1 nonclustered index based on FileId with included column (Id) -- > fill factor = 0%
Pls kindly advise ...
Thanks a lot ]
Cheers
October 14, 2014 at 12:06 am
Quick question, can you post the full DDL of the table and the indices?
😎
October 14, 2014 at 12:37 am
Eirikur Eiriksson (10/14/2014)
Quick question, can you post the full DDL of the table and the indices?😎
in addition to the above question, is there any Trigger on this table ?
October 14, 2014 at 11:31 am
How have you identified that this insert is the cause of the high CPU condition?
Based on this limited information, unless there is a trigger or triggers, a singleton insert doesn't seem like something that would cause high CPU.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2014 at 1:06 pm
...Unless you're calling the insert individually (i.e. once for every readerID and fileId being inserted). Is there any way to group up/batch up the inserts? Doing 1M x 1 row is NOT the same as doing 1 x 1M rows. SQL Server is built to deal with sets of info, so sending in a bunch at a time could dramatically improve inserts. Of course - not knowing the data types, you could be running into high levels of page splits (granted I know what your fill factor is, but that's only the starting factor).
Again - assuming it's even possible to do so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply