Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
alainn
Starting Member
3 Posts |
Posted - 2008-05-23 : 04:19:06
|
HelloI'm working on an application which is mainly used to look up compressors and their data which are held in documents.This is a simplified representation of the tables of importance:Document--------Id (int, identity) (PK)Number (nvarchar(50))Compressor----------DocumentId (int) (PK, FK)Id (int, identity) (PK)Name (nvarchar(50))CompressorData--------------CompressorId (int) (PK, FK))Id (int, identity) (PK)Value (nvarchar(50))The tables are linked as follows:Document -> Compressor -> CompressorDataNon clustered indexes are created on Document.Number and Compresor.Name because these fields are used for querying.At certain points corrections will be released on compressors which will result in:- Creating new documents with new document numbers (note that a non clusterd index exists on Document.Number).- Copying affected compressors of existing documents into the new documents (note that a non clusterd index exists on Compressor.Name).- Copying the data of the affected compressors into the new documents.This can result in creating ten's of new documents and copying hundreds compressors and thousands compressor data records.My question:Will the users still be able to query for compressors while corrections are released (thinking about indexes which need to be modified) or will their be so many locks held that the database becomes unusable?Thanks in advance,Alain |
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-23 : 23:08:45
|
User may be blocked, it depends on how long your correction takes. Run it during off hours if possible. |
|
|
alainn
Starting Member
3 Posts |
Posted - 2008-05-26 : 10:15:46
|
Hello rmiaoThanks for your reaction.quote: Originally posted by rmiao User may be blocked, it depends on how long your correction takes. Run it during off hours if possible.
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-26 : 20:06:44
|
If your application is mission critical and you do not wana break the glass on the application side from DB side.The first thing is you may need to configure the job for optimization once in a week or may be after business hours. If your DB side is huge then better to be run that job once in a week.ManojMCP, MCTS |
|
|
alainn
Starting Member
3 Posts |
Posted - 2008-05-27 : 10:14:11
|
Hello ManojHow urgent the corrections are is something that will be discussed and it would be the best it can be done during the weekend.Thanks for your input.quote: Originally posted by mdubey If your application is mission critical and you do not wana break the glass on the application side from DB side.The first thing is you may need to configure the job for optimization once in a week or may be after business hours. If your DB side is huge then better to be run that job once in a week.ManojMCP, MCTS
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-28 : 13:44:13
|
That's what I mentioned, How critical your application it is? How big the DB is?Try to give those answers and decide, how you want to do it?ManojMCP, MCTS |
|
|
|
|
|
|
|