Alter Table > Add Identity Column Performance Question

Alter Table > Add Identity Column Performance Question

Post by Jame » Sat, 18 Jan 2003 00:08:27



I'm updating a table, by adding an identity column to it,
and I am getting different performance from seperate
environments. Running it against a SQL 7 server and it
runs great, but running it against a SQL 2000 server,
clustered, with a SAN and it is terrible. Any comments on
what may be the critical difference here?

Thanks is advance.

 
 
 

Alter Table > Add Identity Column Performance Question

Post by Alvin Zhao[MSF » Sat, 18 Jan 2003 09:57:05


Hi James,

To narrow down the problem, please try to provide the information below:
1. How did you execute the SQL statement? In an application or in Query
Analyzer? How long did the SQL statement return?
2. Is the server that SQL Server is running on a dedicated for SQL Server?
Are there other connections to the SQL Server when you were executing the
SQL statement.
3. You mentioned that when you executed the ALTER TABLE statement, the
performance is slow. Is it the only SQL statement that experience
performance issue? Is it reproducible? Please try to drop the column and
recreate the column to see if you still experience low performance. Please
execute other SQL statement, for example, select from another table, to see
if the issue is related to the specific table.
4. Try to stop and restart SQL Server service and see if the problem still
exists.

Sincerely,

Alvin Zhao
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

Alter Table > Add Identity Column Performance Question

Post by Tibor Karasz » Sat, 18 Jan 2003 11:56:02


In addition to Alvin's post:

Perhaps the disk layout differs (think transaction logging). The SQL7 machine perhaps has log in
RAID1 or even a separate disk. And the SQL2K machine might not.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:> I'm updating a table, by adding an identity column to it,
> and I am getting different performance from seperate
> environments. Running it against a SQL 7 server and it
> runs great, but running it against a SQL 2000 server,
> clustered, with a SAN and it is terrible. Any comments on
> what may be the critical difference here?

> Thanks is advance.

 
 
 

1. Alter Table (add Identity to Column)

I am trying to make a column in my table an identity
column after it's already been defined.  I have
manipulated my alter table statement may ways and can not
get it to work.  Below is the original table definition
prior to adding identity, and at the bottom is my
statement to add the identity property to the column.

Please help if you know how to make this work.

--Table Definition prior to adding identity!
SET ANSI_NULLS OFF
GO
CREATE TABLE [dbo].[Article]
        (
                [ArticleId] [int] NOT NULL,
                [ArticleTypeId] [int] NOT NULL,
                [Author] [varchar] (50) NULL,
                [ComposedDateTime] [smalldatetime] NULL,
                [PostedDateTime] [smalldatetime] NOT NULL,
                [Description] [varchar] (125) NULL,
                [Content] [text] NOT NULL,
                [Keywords] [varchar] (1000) NULL
        )
GO
SET ANSI_NULLS ON
GO

--Alter Table Statement to add identity to id column.
ALTER TABLE     [dbo].[Article]
ALTER COLUMN    [ArticleId] [int] NOT NULL  IDENTITY
(10001,1)
GO

2. Conection ADO emulating by code a Gallery DSN

3. ALTER TABLE ... ALTER COLUMN IDENTITY

4. ASP - Storedprocedure w/temp table - stored procedure attached

5. 'alter table' add column question

6. How to tell Windows to print Landescape?

7. how to alter an identity column to a non-identity column

8. CL - Paradox on Unix/Linux ?

9. Invalid column after alter table adds column

10. add a column at a specific position in the table or alter a columns position

11. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

12. ALTER TABLE ADD COLUMN column SERIAL -- unexpected

13. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results