April 20, 2012 at 6:13 am
Hi
I have a web application in .net which is used for movie ticket booking. There are around 12 tables which gets data with each booking. So either data should go in all of them or none of them.
Now .net code opens a transaction and inserts data in those 12 tables. Most of these tables are quite big but proper indexes are in place.
What happens is that transaction timeouts after 30 seconds because there are many inserts on big tables in one transaction.
One solution to this is to increase the timout duration to say 60 seconds.(but that won't be a perfect solution)
I am looking for alternative suggestions to tackle this kind of scenarios.
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 20, 2012 at 6:16 am
This is not a solution but is table possibly over-indexed? Too many indexes will impact INSERT, UPDATE and DELETE performance as the indexes have to modified.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 20, 2012 at 6:18 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Have you checked for blocking.
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
April 20, 2012 at 6:24 am
I have already checked that. There are not too many indexes on any of these tables.
I was thinking if there can be some other way of handling this kind os scenario rather than putting everything in one transaction.
Robin Sasson (4/20/2012)
This is not a solution but is table possibly over-indexed? Too many indexes will impact INSERT, UPDATE and DELETE performance as the indexes have to modified.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 20, 2012 at 6:26 am
Hi Gail
I understand that but putting definitions and indexes for 12 tables wil make it so big that probably people won't want to see all that.So I tried to put it in a generic way.
Thanks for pointing out for blocking.
GilaMonster (4/20/2012)
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Have you checked for blocking.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 20, 2012 at 6:28 am
If you want my help, I need to see the details. The generic description is not enough. At least post the insert statements and list the indexes (as sp_help shows them)
Inserts into 12 tables should not in any way take 30 seconds unless there's severe blocking.
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
April 20, 2012 at 6:32 am
I have to agree with Gail here. If you checked for blocking / long running queries / index fragmentation then more details would be useful.
Have you checked the available resources and the stability of the server itself?
Thanks
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 20, 2012 at 6:35 am
S_Kumar_S (4/20/2012)
I was thinking if there can be some other way of handling this kind os scenario rather than putting everything in one transaction.
A transaction has to include everything that has to be either commited or rolled-back as one piece - business specifications drive what a transaction should include or not.
Have you traced the transaction? see what is it doing? which wait events are holding it for so long?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2012 at 6:53 am
I am planning to trace it. Since I don't have direct access to this server, I'll need to send the trace to someone to run it.
One question: What can block an insert?
PaulB-TheOneAndOnly (4/20/2012)
S_Kumar_S (4/20/2012)
I was thinking if there can be some other way of handling this kind os scenario rather than putting everything in one transaction.A transaction has to include everything that has to be either commited or rolled-back as one piece - business specifications drive what a transaction should include or not.
Have you traced the transaction? see what is it doing? which wait events are holding it for so long?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 20, 2012 at 7:07 am
S_Kumar_S (4/20/2012)What can block an insert?
Locks acquired by INS/UPD/DEL statements stay put for the lenght of the transaction therefore a system that has transactions taking as much as 30 seconds is most probably shooting itself on its foot.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2012 at 7:08 am
S_Kumar_S (4/20/2012)
I am planning to trace it. Since I don't have direct access to this server, I'll need to send the trace to someone to run it.One question: What can block an insert?
Anything. Inserts require exclusive locks, so any other operation that's grabbed a page lock in any mode (not uncommon) can block the insert.
Also, check for triggers.
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
April 20, 2012 at 7:36 am
Gail, triggers are not there.
We plan to try two options first:
1.defrag indexes more frequently after checking how much they are fregmented.Currently they are rebuild weekly.
2. Increase CommandTimeout to 40s from default 30s.
Hope it timeouts get reduced atleast to start with....
GilaMonster (4/20/2012)
S_Kumar_S (4/20/2012)
I am planning to trace it. Since I don't have direct access to this server, I'll need to send the trace to someone to run it.One question: What can block an insert?
Anything. Inserts require exclusive locks, so any other operation that's grabbed a page lock in any mode (not uncommon) can block the insert.
Also, check for triggers.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 20, 2012 at 7:52 am
S_Kumar_S (4/20/2012)
1.defrag indexes more frequently after checking how much they are fregmented.Currently they are rebuild weekly.
2. Increase CommandTimeout to 40s from default 30s.
1- May just add to the missery while index rebuild/reorg are running.
2- Will most certainly add to the missery by just extending locking times.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2012 at 8:02 am
S_Kumar_S (4/20/2012)
1.defrag indexes more frequently after checking how much they are fregmented.Currently they are rebuild weekly.
Fragmentation won't slow down inserts, so this will likely do nothing
2. Increase CommandTimeout to 40s from default 30s.
If I had to wait 40 seconds for a website to process a purchase, I'd find another place to buy from.
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
April 20, 2012 at 8:10 am
Paul
You scaring me 🙂
Index defrag will be done during non peak hours.It's already done once in week. We'll see if changing that to say,3 days, helps.Of course we'll first check how much the deframentation is.
Why increasing timeout will cause more problem? this big transaction is being blocked by some other process, hence timing out. If we make it to wait for another 10 secs, then there is probability that blocking created by other process goes and my transaction completes. At worst, it may timeout again, but the chances of it getting it complete will increase,am i right?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply