February 24, 2015 at 5:53 am
Experts
We have 6 tables , 5 of them references 1 parent table.
there is one insert process through a stored procedure that an application is calling to insert data in batch. this call only happens from lets say from application App1.(this activity happens 10% of the time, ONLY INSERT process)
However a different application App2 , does DML queries on those above tables , which is a high priority.we want activities from App2 not to be affected by inserts from App1.(this activity happens 90% of the time)
as of now there is a performance problem.
can you tell me what is the best way to do this with less performance issue?
February 24, 2015 at 6:48 am
Is the performance problem blocking-related? If so, have you considered using one of the optimistic concurrency isolation levels? That's read-committed-snapshot isolation or snapshot isolation levels?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2015 at 8:39 am
I'd lean towards what Gail mentioned. If you need inserts to happen without affecting reads, then isolation is probably the best way. However this changes where resources are needed on your system, and you might need more hardware to handle this.
It's not simple, and if you decide this is a possibility, I'd get some consulting help to assist in planning and examining your migration plan/architecture.
February 24, 2015 at 8:41 am
daniarchitect (2/24/2015)
ExpertsWe have 6 tables , 5 of them references 1 parent table.
there is one insert process through a stored procedure that an application is calling to insert data in batch. this call only happens from lets say from application App1.(this activity happens 10% of the time, ONLY INSERT process)
However a different application App2 , does DML queries on those above tables , which is a high priority.we want activities from App2 not to be affected by inserts from App1.(this activity happens 90% of the time)
as of now there is a performance problem.
can you tell me what is the best way to do this with less performance issue?
1) Look at the query plan for the inserts and see if any table scans are happening. If so, consider creating non-clustered indexes to assist.
2) It is general best advice to index foreign keys
3) use sp_whoisactive (free tool from sqlblog.com, v11.11 is latest I think) to watch for blocking during inserts. Address blocking causes (scans being most likely cause). You can also see the query plans with this script.
4) you mentioned "insert in batch". How many rows are you inserting in a batch? If too many this can cause elevated locking instead of row locking. Consider using smaller batches to avoid page or table locks?
5) do you have any unfortunate stuff going on such as UDFs, mismatched data types on the joins, etc?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2015 at 12:41 pm
Experts thank you all for responding quick and valuable inputs, it an eye opener.
we found a trigger which calls a procedure that uses a transaction , that caused the blocking.
Isolation level , we would like to keep the current database configuration as is.
as for table scanning , there is none showed which is bad.
Does indexing help inserting ? we dont have a column that donot have index on it thats used in the select statement of the where clause, and not that much of joins.
February 24, 2015 at 12:52 pm
daniarchitect (2/24/2015)
Experts thank you all for responding quick and valuable inputs, it an eye opener.we found a trigger which calls a procedure that uses a transaction , that caused the blocking.
Isolation level , we would like to keep the current database configuration as is.
as for table scanning , there is none showed which is bad.
Does indexing help inserting ? we dont have a column that donot have index on it thats used in the select statement of the where clause, and not that much of joins.
If you don't have scanning in the insert activity (including the work done by the sproc, and not just table scans but also possibly non-clustered index scans) then indexing won't help you.
You need to fix the sproc to address the blocking issues. There are umpteen ways to proceed, depending on what the sproc is doing. Can't advise further without more details on that activity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2015 at 1:24 pm
Indexing certainly adds load to inserts/updates/deletes. The index, as well as the data, must be touched.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply