Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 
) ON [PRIMARY]
GO
 CREATE  INDEX [search] ON [dbo].[MyTable]([queue], [seqNum], [status]) ON [PRIMARY]
GO

 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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.