table lock whe creating an index

Hi everybody! I have a crash-like problem when creating an index in a VIEW (!!!) mode.

I have a very basic view - like

Create View dbo.VeryBasicView
As
Select Detail,QuantityAvailable From dbo.LargeTable With (NOLOCK)
Where (QuantityAvailable > 0)

I use it for finding a record using an ASP+SQL connection
(SQL statement is more than simple: "Select * From VeryBasicView Where Detail = 'aValue')
When I run ASP script from my IE on my intranet, my SQL server becomes incredibly slow, SQL server memory decreases dramatically, and, most of the time, I see a Time-Out Error message.

Now, when I try creating any index when in the VeryBasicView design mode
to resolve this Slow Speed Ploblem,
my table (LargeTable) becomes LOCKED for editing (!!!!!!),
thus, sales cannot modify it - noone can use our Inventory except for the reason of viewing it.

Did anyone have a similar problem?
Thank you,
chessclock

Thank you, this might be the case!..
I suspected that the index wasn't just created properly, too, -
because I did it overnight (off-hours), and the table lock was a morning surprise.
How can I check if the index is properly created?

Originally posted by satya

When you create the index the table will be locked until the operation is finished. So you need to make sure to create during less traffic operational hours.

Thank you. I am impressed by the quick reply! [^]

Sorry to bother you - I hope the next will be my last questions:

- is indexing VIEWS as effective in speeding up queries as indexing tables?
- can adding up physical memory (from 1Gb up to 2Gb) improve perfomance much?
- does it make sense building up stored procedures for simple queries like the ones in my example

I really can't find why querying simple views kills my SQL server in a matter of seconds.

Originally posted by satya

SP_HELPINDEX [tablename] of SP_HELP [tablename] will give you the information.

Thank you all!

I'll try indexing those views first (I'm working with two 'own' views).
I don't want to index existing tables as I don't know the way the existing (LOB) application queries the tables,
especially after indexing a view recently locked a table for editing [8].
It strikes strange to me, but the exisisting application does not have any view indexed, still being fast enough when using it.
I won't be using any stored procedures until having my simple query up and running.
I'll add some RAM and see how much the speed increases.

Frank, you're right.
To my regret, I was unable to install our LOB ("business") application on a ghost server
(kind of backup domain controller), - it just refused to install properly with all the same settings I have on the main server, so I have decided to work with views on a main server. I just could not expect creating an index for a view might lock a table!...

The problem is, to test the LOB application (server site) properly, I must have it installed on the ghost server. The LOB application has been developed using Visual Basic (which is not bad if it was 100% finished and tested), and behaves sometimes different (when SQL server does not show any problems and all the basic statements work well).

Anyways, as this is a safe approach, I really have to test all the stuff on a dev machine first.
I did mess up [V] but I fixed it fast [8D]

I thank you all once more for your suggestions. It is very useful.


>Are you saying that you develop on a prod machine?
>Or why do you say "sales cannot modify it"?
>If so, this sounds like a heart surgery with no anaesthesia to me.
>You're begging for problems.
>Copy the database to a dev machine
>and do your stuff there and then apply only the changes back to the prod machine.