locking

Question

locking

sunandas

Old Hand

Points: 357
More actions
December 5, 2008 at 4:20 am

#203789

Hi,
We have a production database.There we have made a job of reindexing which runs in any 4 hours.But when this job runs at that time I cant view current activity and when I refresh it the following error comes.
error 1222:Lock request time out period exceeded.
When the job finishes I can see the current activity. But in between if I see the locking and kill it through query analyzer then also it takes a lot of time to rollback the process.
For last 4 months the reindex job was working and it was working fine.But now only the problem is coming.So I cant understand why this problem arises.
If anybody can help me in this regard then I will be very grateful.
Thanks & regards.
Sunanda

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

sunandas (12/5/2008)
Hi,
We have a production database.There we have made a job of reindexing which runs in any 4 hours.But when this job runs at that time I cant view current activity and when I refresh it the following error comes.
error 1222:Lock request time out period exceeded.
When the job finishes I can see the current activity. But in between if I see the locking and kill it through query analyzer then also it takes a lot of time to rollback the process.
For last 4 months the reindex job was working and it was working fine.But now only the problem is coming.So I cant understand why this problem arises.
If anybody can help me in this regard then I will be very grateful.
Thanks & regards.
Sunanda

your database size is increasing and thus Reindexing is taking time

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ps. One Orange Chip Points: 29423 More actions · Answer 2

If you're monitoring through enterprise manager, it does get into hung state when there is intensive IO activity.. you can see these activities and locks by querying syslocks and sysprocesses system tables.

Since this activity is logged, killing any running process will take time to roll back.

Pradeep Singh

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hey... why are you reindexing every 4 hrs?

"Keep Trying"

sunandas Old Hand Points: 357 More actions · Answer 4

I need to do reindexing as the tables are very big and if I wont do reindexing then locking in objects and for which the agent's system are hanged frequently.

If any other solution is there then please tell me.

Sunanda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sunandas (12/10/2008)
I need to do reindexing as the tables are very big and if I wont do reindexing then locking in objects and for which the agent's system are hanged frequently.

Do you need to reindex? Is the fragmentation increasing so fast that it's necessary to rebuild all indexes every couple hours?

Can you post the table and index definitions of the tables that fragment the fastest?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Atul DBA Hall of Fame Points: 3610 More actions · Answer 6

Reindexing every four hour is not a good idea in anyway. It will create the object locking problem with the increase in the table size. This may be due to poor database design.

You need to check the definition of Index of the table and the amount of Transaction on the table, if it is very high, try to set the Fill Factor at different level and measure the impact.

Once needs to look for the option to minimise the reindexing to maximum possible extend.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

GilaMonster (12/10/2008)
sunandas (12/10/2008)
I need to do reindexing as the tables are very big and if I wont do reindexing then locking in objects and for which the agent's system are hanged frequently.
Do you need to reindex? Is the fragmentation increasing so fast that it's necessary to rebuild all indexes every couple hours?
Can you post the table and index definitions of the tables that fragment the fastest?

and if possible the approx amount of data that gets inserted/deleted within the 4 hrs.

"Keep Trying"

sunandas Old Hand Points: 357 More actions · Answer 8

I am attaching the table structure and these tables contain the following records.

shareholder--------1693568

company_master---864470

share_capital-------903381

tbldirector----------1257093

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

Without wanting to know corporate details or other 'secret' information, it sounds like you are trying to improve performance without having a clear idea what's truly wrong, other than either 'it takes too long' or 'it does not work at all'. Depending on which of those two problems, your response and results will vary greatly.

Do you have any means to measure current activity? How many inserts per day/ workshift / hour / 15 minute intervals? How many different tables are affected?

(What happens if you delay the reindexing to only daily, or less frequently? --> this question is more micromanaging without having any idea of what truly needs to be fixed)

Do you have response time requirements that are not being met?

Do you have permission to redo any stored procedures? Is it possible that a stored procedure is being inefficient rather than the database itself? There may be more elegant, simpler, and much more effective solutions than reindexing.

If the horse you are riding is dead, get off.