Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an old system running MSSQL7. It takes about 10.5 seconds to create the table below and another 30 seconds to add the index. Is there anything I can do to decrease these times?

CREATE TABLE [dbo].[MyTable] (
    [queue] [int] NOT NULL ,
    [seqNum] [numeric](12, 0) NOT NULL ,
    [cTime] [char] (14) NOT NULL ,
    [msg] [char] (255) NULL ,
    [status] [int] NOT NULL ,
    [socket] [int] NULL 
 CREATE  INDEX [search] ON [dbo].[MyTable]([queue], [seqNum], [status]) ON [PRIMARY]

 CREATE  INDEX [new] ON [dbo].[MyTable]([queue], [status]) ON [PRIMARY]
share|improve this question
are you getting auto-grow events? – Nick Kavadias Aug 18 '09 at 0:53
I don't show any, it's not very active. – scottm Aug 18 '09 at 15:48
up vote 1 down vote accepted

Are you having any locking issues on the system objects going on when this is running?

What's the load on the disks look like?

Any reason that you are running a version of SQL Server that is way out of support? There have been a LOT of performance improvements between SQL 7 and SQL 2008.

share|improve this answer
Oh trust me, if I could upgrade I would. – scottm Aug 17 '09 at 21:01
I'll accept that as a valid answer. I've been there. How about the questions I asked? – mrdenny Aug 18 '09 at 0:08
I couldn't get back to this yesterday, but when I try an profile creating the table again, I get reasonable times. Yesterday, read and writes were fairly high for the query itself, but disk load on the server as a whole is pretty minimal. There does seem to be lock on master.dbo.spt_values table pretty frequently – scottm Aug 18 '09 at 15:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.