April 20, 2010 at 3:44 am
A theoritical query:
I am using SQL 2005. I have one table where only data insertion happens. It was working fine for first 2 hours but after a sudden drop in performance is shown.
What may be the reason.
This is not a practical scenario. I have faced this in an interview.
April 20, 2010 at 3:50 am
Some things that come to my mind:
1) Badly coded triggers
2) Too many indexes
3) ... any possible reason. The question is too generic to give a good answer.
-- Gianluca Sartori
April 20, 2010 at 3:54 am
Gianluca Sartori (4/20/2010)
Some things that come to my mind:1) Badly coded triggers
2) Too many indexes
3) ... any possible reason. The question is too generic to give a good answer.
Hi, thnx 4 ur reply. Yes, I do agree that this is a very generic question. Even I have answered about the index but the interviewer was telling that if it s prob of index, then the problem would occour from the scratch. And yes, triggers may cause this.
Any other reasons?
April 20, 2010 at 4:33 am
Gianluca Sartori (4/20/2010)
(2) Too many indexes
HOw indexes put sudden effect on indexes. i am confused with it
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 20, 2010 at 4:38 am
Bhuvnesh (4/20/2010)
Gianluca Sartori (4/20/2010)
(2) Too many indexesHOw indexes put sudden effect on indexes. i am confused with it
I beleave while you are inserting some rows to the table, the corresponding indexes also populated. So, MAY BE, after a sudden time, the index size is much bigger, hence effecting the whole insertion process.
April 20, 2010 at 4:43 am
this is such a generic that you could have done 1 hrs easy timepass with them.;-)
a whole book can be written on this.
April 20, 2010 at 4:45 am
Bhuvnesh (4/20/2010)
Gianluca Sartori (4/20/2010)
(2) Too many indexesHOw indexes put sudden effect on indexes. i am confused with it
i am not sure about your comment but , out dated statistics could be reason.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 20, 2010 at 4:46 am
Ananda-292708 (4/20/2010)
this is such a generic that you could have done 1 hrs easy timepass with them.;-)a whole book can be written on this.
can you post some probable reaons of slowness ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 20, 2010 at 4:52 am
many reasons..
1. any job inserting bulk data into that database.
2.some other offline operation on related tables.
3.backup database job start by that time.
4.maintenance plans
5.tempdb full
6.cpu,memory bottleneck
7.too many indexes
8.excessive normalization
April 20, 2010 at 4:55 am
apart from Rateesh
it could be
some other processes are running in SQL consuming most of the mem and cpu cylcles
it could be other than sql application making the box crumble.
It could be issue with SAN , probably luns share too much of load
nway are too gearing up for the same 😉
April 20, 2010 at 6:20 am
arup_kc (4/20/2010)
Gianluca Sartori (4/20/2010)
Some things that come to my mind:1) Badly coded triggers
2) Too many indexes
3) ... any possible reason. The question is too generic to give a good answer.
Hi, thnx 4 ur reply. Yes, I do agree that this is a very generic question. Even I have answered about the index but the interviewer was telling that if it s prob of index, then the problem would occour from the scratch. And yes, triggers may cause this.
Any other reasons?
Well, actually the interviewer is wrong. Index behavior changes over time. A very small index with a small number of rows can have a couple of page splits and you won't see a change in behavior, but as page splits continue to occur your index will get fragmented. This will not only slow down reads, but inserts as well since to insert to the index, the appropriate place for storage must be found.
I don't think this is what the guy was driving at, but the index is still a possible cause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply