SQL Server Performance

Index creation takes longer time

Discussion in 'SQL Server 2005 General DBA Questions' started by Raghu, Oct 8, 2009.

  1. Raghu New Member

    Issue: Index creation is taking longer time than the usual timing, there were no changes on the SQL server and the server. All of the sudden on most of my sql servers index creation is taking long time.
    My SQL servers are SQL 2005 SP3, we use custom script to run the index creation, which is create index command and it is usaly completes in reasonable time, all of sudden on most of the server, the create index script is running for more than 1hr for 1 index, earlier it use to take 3 mins to create. This is happening in most of the servers.
    Do we have any know issue on SQL server 2005 with index creation (create index script) command ??
  2. Luis Martin Moderator

    1) Non clusters indexes.?
    2) Are you creating indexes in windows time?

  3. Raghu New Member

    Yes, its all non clustered indexes..
    creating indexes in windows time mean? i didn't understand this ? wha you mean creating index in windows time ??
  4. Luis Martin Moderator

    With no users using databases.
    Sometimes when you try to create one index and at the same time are users using database, you can have a lock.
  5. Raghu New Member

    No, there is no users connected to the database during the index creation window. User are locked out of the database.
  6. Luis Martin Moderator

    No Integrity, Optimization, jobs running I presumes.
    Growing database is set to automatically?
    Have you plenty disk space?
  7. Raghu New Member

    no activity is happening on the databases expect the create index command.
    database is set to auto grow and it has enough space on the drive
    Index is creating successfully, only constaint is taking longer time.. like 1hr job will run for 3-4hrs
    i'm not able to predict anything, since the index is not failing and no errors in the window event viewer & in errorlog....
  8. davidfarr Member

    Are you quite sure that there are no locks or waits on the process during index creation ? Take a look the SQL Server Activity Monitor to check for locks and latches on each process. Look at the Wait Time, Wait Type, Blocked By and Blocking columns for anything unusual.
    You mention a "create index" script. Are these new indexes on new tables that do not have indexes yet ? ...or are you re-indexing existing tables ? Do you drop the previous index first before using "Create index" again ? Why not use DBCC DBREINDEX on tables to re-create existing indexes ? Are you sure that the tables have not recently grown in size ? Larger tables with more records will obviously take longer to index than they did before.
    Are these all dedicated database servers ? Check the Windows performance monitor to compare SQL Server resource usage and total server resource usage, to see if any other application could be consuming resources, especially disk resources. Some anti-virus programs, for example, will perform file checking at all times of the day and can definitely affect SQL server performance.
  9. moh_hassan20 New Member

    If you mean index rebuild for existing indexes , it is a bug in sql 2005 SP3
    download and install Cumulative update package 1 for SQL Server 2005 Service Pack 3
    bug#: 50003944
    Knowledge Base article#: 960570 (http://support.microsoft.com/kb/960570/ )
    FIX: A maintenance plan that contains a Rebuild Index Task runs slow in SQL Server 2005

Share This Page