SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Index management and database availability
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alainn
Starting Member

3 Posts

Posted - 05/23/2008 :  04:19:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/23/2008 :  23:08:45  Show Profile  Reply with Quote
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 - 05/26/2008 :  10:15:46  Show Profile  Reply with Quote
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

USA
133 Posts

Posted - 05/26/2008 :  20:06:44  Show Profile  Reply with Quote
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 - 05/27/2008 :  10:14:11  Show Profile  Reply with Quote
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

USA
133 Posts

Posted - 05/28/2008 :  13:44:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000