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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Index management and database availability

Author  Topic 

alainn
Starting Member

3 Posts

Posted - 2008-05-23 : 04:19:06
Hello


I'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 -> CompressorData

Non 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.
Go to Top of Page

alainn
Starting Member

3 Posts

Posted - 2008-05-26 : 10:15:46
Hello rmiao

Thanks 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.

Go to Top of Page

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.

Manoj
MCP, MCTS
Go to Top of Page

alainn
Starting Member

3 Posts

Posted - 2008-05-27 : 10:14:11
Hello Manoj

How 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.

Manoj
MCP, MCTS

Go to Top of Page

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?

Manoj
MCP, MCTS
Go to Top of Page
   

- Advertisement -