SQL Server Performance

table lock whe creating an index

Discussion in 'Performance Tuning for DBAs' started by chessclock, Aug 2, 2005.

  1. chessclock New Member

    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
  2. satya Moderator

    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.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. chessclock New Member

    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.

  4. satya Moderator

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

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. chessclock New Member

    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.
  6. Luis Martin Moderator

    1) Yes, no big difference.
    2) Yes. More if you have done all performance task (good indexes, maintenance jobs, etc.)
    3) In terms of maintence, I suppose is good idea.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. chessclock New Member

    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.
  8. FrankKalis Moderator

    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.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. satya Moderator

    I agree with Frank as you need to test the implication on dev. machine and then apply to production server as you can avoid any shambles during realtime implementation.

    If the total performance is degraded then you must deploy steps like regular reindexing, update statistics on highly update tables and check the hardware resources are being used properly.
    Adding more physical RAM will have a good effect on performance but if the SQL edition is a STANDARD edition then you're restricted to 2GB and cannot go beyond that value.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Luis Martin Moderator

    With standard no indexes view is possible.
    I´m missing something?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  11. FrankKalis Moderator

    Luis,
    that is not correct. You actually can have indexed views in Standard edition. However, there is a drawback in the way that in Enterprise Edition the optimizer considers an indexed view on his own when creating an execution plan just like any other index, while on Standard Edition you have to specify the NOEXPAND hint, IIRC. The optimizer in Standard Edition otherwise simply ignores the indexed view.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. Luis Martin Moderator

    I could said, yes I forget that...., but to be honest......[:I]<br />Frank you are my hero[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  13. chessclock New Member

    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.

Share This Page