Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    99

    Unanswered: Right or wrong in index creation......

    Hi,

    like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts..... can you run process in sql server against tables when:

    - indexes are being created
    - the structure of a table is being modified
    - the database is being shrunk

    If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts

    Thanks in advance

    Luis Torres

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The ever-popular answer: It depends.

    During a schema change, all bets are off. Only the process modifying the table structure has any access to the table at all.

    During indexing, read operations are often allowed (depending on session settings), but write operations are usually blocked (although there are some creative ways to work around this too). In general, I recommend that you let the box have its way with the table while indexing, then you can have your turn later.

    While shrinking the log, there is no material impact on access to the tables. While shrinking the data device(s), performance goes down a rat-hole, but blocking doesn't often occur. I'd still suggest letting the machine have its way with the tables while this is going on, but that's because I'm paranoid and usually have lots of other things to entertain me... If push came to shove, I'm pretty sure that this isn't a problem.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I know you can not insert or delete (and 99% sure you can not update) a table that is currently being indexed. SQL Server in its present form does not keep a "redo area" like Oracle to keep track of changes.

    As for the others, shrinking the database is not really advised for a production environment. It tends to be costly, and since data pages have to be moved around, you are likely to see exclusive locking as a result. The major problem is, you will not be able to predict what tables will be locked.

    Edit: Sniped again. Pat, are you on course for 4,000?

  4. #4
    Join Date
    Aug 2004
    Posts
    99
    Thanks for your replies, gives me better understanding of what I'm doing and talking about

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley
    Edit: Sniped again. Pat, are you on course for 4,000?
    I might just make it. I've been so busy at the orifice that I don't always know which way is up (and they keep re-definiing just what "up" means constantly). I think I'll make it though!

    Quote Originally Posted by lgaxiola
    ...gives me better understanding of what I'm doing and talking about
    Always a good thing in my book. I like it when I've got a strong clue what I'm talking about!

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And speaking of books..you should probably go get one asap...did you get the client side tools installed?

    Have you looked at books online?

    EDIT: And, if the DBA is just reindexing everything, just because, that's not a good idea if they don't need to be done.

    They need to check the amount of fragmentation.

    I mean, why Reindex a code table?

    EDIT AGAIN: You might also want to look at DBCC INDEXDEFRAG
    Last edited by Brett Kaiser; 02-03-05 at 17:28.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2004
    Posts
    99
    Brett,

    I have definetly started reading books, specially BOL. I started training myself at the beginning of the year and now I'm allowed to "play" with the production database. I've been a dba for many years (close to 10) so I'm more than comfortable around them, just need to get used to the way SQLServer works.

    I would have been able to get the answer to the questions that I asked from BOL but something that time hasn't finished teaching me is patience and I when the actual "dba" started "teaching" me (he is new to databases) about things I knew or had a very accurate idea of how they work then I got pissed and wanted to get an answer as fast as I could from the best source I could find (which is of course the experience of other dbas). Something that time HAS taught me is to not open my mouth until I'm completely sure of what's coming out of it (because of my natural impatience I tended to do a lot of goofups when I was younger).

    Thanks again for al your coments and have an excelent end of day

    Luis Torres

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •