SQL Server Performance

dba questions

Discussion in 'The Lighter Side of Being a DBA' started by alankrita, Jul 25, 2005.

  1. alankrita New Member

    I am preparing for an interview and would like answers to the following questions.

    1)How can you prevent a database administrator from dropping an object on User database.

    2)What command you will run after large data Insert/Update on a table and why?

    3)Is DBCC REINDEX the command you will run after altering a Clustered Index on a table having few non clustered indexes.

    Thank you,
  2. FrankKalis Moderator

    ad 1) You can't do that. A user with system administrator credentials can virtually do whatever he wants on the server. It is the responsibility of the DBA to take care of what he's doing. <br /><br />ad 2) Probably reindex the table and update the statistics to ensure SQL Server's optimizer can create efficient execution plans.<br /><br />ad 3) What is DBCC REINDEX? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] scnr...<br />Not sure if I understand you here, but when you alter a clustered index, i.e. add or remove a column to (from) the index, the nonclustered indexes have to be rebuild anyway. There should be no need to issue a <b>DBCC DBREINDEX</b>. A not commonly known issue when rebuilding a clustered index on SQL Server 2000 is described here.<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;304519>http://support.microsoft.com/default.aspx?scid=kb;en-us;304519</a><br /><br />Good luck with the interview!<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  3. satya Moderator

    1) As you cannot prevent rather you can audit the transactions using third party tools or your own monitoring script.

    2) If the data is stored sequentially like using identity column then there is no need to run any optimizer statements such as update stats or DBCC DBREINDEX, on the basis of performance you must deploy a schedule of DBCC DBREINDEX in this case.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. mmarovic Active Member

    2) You should rebuild indexes that are fragmented. As Satya and Frank said there is no need to rebuild index on identity column. For indexes that are not rebuilding you should update statistics (if they are not autoupdated).
  5. Adriaan New Member

    If this is "The Lighter Side of Being a DBA" then I shudder to think what the heavier side would be [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]

Share This Page