Slow update on SQL Virtual Server 2000 on WIN2003 cluster

21 Jul 2006 5:18 PM

ikaliy

Hello,

I have noticed the difference in transaction handling between SQL
Server 2000 EE installed on local WinXP machine
and SQL Virtual server on WIN 2003 failover cluster.
And this difference put me in big trouble.

For example:
If you need to update 100000 record (update of one integer field),
it will run much faster on SQLServer WinXP than on SQL Virtual Server
WIN2003 cluster.

Suppose that you create simpe table on next way:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST]
GO

CREATE TABLE [dbo].[TEST] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Updated] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
    CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
    (
        [ID]
    ) ON [PRIMARY]
GO

Then you insert 100000 record into table with:

DECLARE @i int
SET @i = 1

WHILE @i <= 100000
BEGIN
INSERT INTO TEST (Updated) VALUES (1)
SET @i = @i + 1
END

Then you update every record with

DECLARE @i int
SET @i = 1

WHILE @i <= 100000
BEGIN
UPDATE TEST SET @Updated = 1 WHERE ID = @i
SET @i = @i + 1
END

Inserting and updating will work very fast on SQL Server on WinXP, but
very slow on SQL Virtual Server on Win2003 failover cluster.

To achieve also fast performance on SQL Virtual Server you must
enclose insert and update with BEGIN and COMMIT TRAN.

So, for updating you will have next SQL:
DECLARE @i int
SET @i = 1

BEGIN TRAN

WHILE @i <= 100000
BEGIN
UPDATE TEST SET @Updated = 1 WHERE ID = @i
SET @i = @i + 1
END

COMMIT TRAN

In my client application (exporting records from database table to
ASCII file) I need
to update one flag when upon exporting into file.
And I do the update with
UPDATE TEST SET @Updated = 1 WHERE ID = @i
It works very fast on
my local machine with WinXP, but very slow on cluster.

I am sure that is slow on cluster because of different transaction
handling.
It seems that Virtual server open and commit transaction for every
updated record.

I

1. Does anybody know how to achive the same behaviuor on SQL Virtual
Server like I have on
   SQL Server on WIinXP.

2. Is it the matter of SQL virtual server configuration ? or

3. Application for running on Virtual Server must be written on
totally different way.

Just for information, application is written in Visual C++ using ADO
for database handling.

21 Jul 2006 5:47 PM

Tracy McKibben

ikaliy wrote:
Show quote

> Hello,
>
> I have noticed the difference in transaction handling between SQL
> Server 2000 EE installed on local WinXP machine
> and SQL Virtual server on WIN 2003 failover cluster.
> And this difference put me in big trouble.
>
> For example:
> If you need to update 100000 record (update of one integer field),
> it will run much faster on SQLServer WinXP than on SQL Virtual Server
> WIN2003 cluster.
>
> Suppose that you create simpe table on next way:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[TEST]
> GO
>
> CREATE TABLE [dbo].[TEST] (
>     [ID] [int] IDENTITY (1, 1) NOT NULL ,
>     [Updated] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
>     CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
>     (
>         [ID]
>     ) ON [PRIMARY]
> GO
>
>
> Then you insert 100000 record into table with:
>
> DECLARE @i int
> SET @i = 1
>
> WHILE @i <= 100000
> BEGIN
> INSERT INTO TEST (Updated) VALUES (1)
> SET @i = @i + 1
> END
>
>
> Then you update every record with
>
> DECLARE @i int
> SET @i = 1
>
> WHILE @i <= 100000
> BEGIN
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> SET @i = @i + 1
> END
>
>
>
> Inserting and updating will work very fast on SQL Server on WinXP, but
> very slow on SQL Virtual Server on Win2003 failover cluster.
>
>
> To achieve also fast performance on SQL Virtual Server you must
> enclose insert and update with BEGIN and COMMIT TRAN.
>
> So, for updating you will have next SQL:
> DECLARE @i int
> SET @i = 1
>
> BEGIN TRAN
>
> WHILE @i <= 100000
> BEGIN
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> SET @i = @i + 1
> END
>
> COMMIT TRAN
>
> In my client application (exporting records from database table to
> ASCII file) I need
> to update one flag when upon exporting into file.
> And I do the update with
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> It works very fast on
> my local machine with WinXP, but very slow on cluster.
>
> I am sure that is slow on cluster because of different transaction
> handling.
> It seems that Virtual server open and commit transaction for every
> updated record.
>
> I
>
>
> 1. Does anybody know how to achive the same behaviuor on SQL Virtual
> Server like I have on
>    SQL Server on WIinXP.
>
> 2. Is it the matter of SQL virtual server configuration ? or
>
> 3. Application for running on Virtual Server must be written on
> totally different way.
>
>
>
> Just for information, application is written in Visual C++ using ADO
> for database handling.
>

Doesn't this sort of make sense? On the virtual server, you're not
writing directly to the hardware - you're writing to "virtual" hardware,
i.e. a translation layer of some kind that pretends to be hardware. On
the XP machine, you're not.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

21 Jul 2006 5:48 PM

Arnie Rowland

I'm confused, please help me out here.

Are you running Microsoft Virtual Server, and in that VS, running a set of
Clustered SQL Servers?
(OR)
When you refer to "SQL Virtual Server', do you mean the cluster Instance of
SQL Server?

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"ikaliy" <ika***@yahoo.com> wrote in message
news:1153502289.366284.58330@p79g2000cwp.googlegroups.com...
> Hello,
>
> I have noticed the difference in transaction handling between SQL
> Server 2000 EE installed on local WinXP machine
> and SQL Virtual server on WIN 2003 failover cluster.
> And this difference put me in big trouble.
>
> For example:
> If you need to update 100000 record (update of one integer field),
> it will run much faster on SQLServer WinXP than on SQL Virtual Server
> WIN2003 cluster.
>
> Suppose that you create simpe table on next way:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[TEST]
> GO
>
> CREATE TABLE [dbo].[TEST] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Updated] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
> CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
>
> Then you insert 100000 record into table with:
>
> DECLARE @i int
> SET @i = 1
>
> WHILE @i <= 100000
> BEGIN
> INSERT INTO TEST (Updated) VALUES (1)
> SET @i = @i + 1
> END
>
>
> Then you update every record with
>
> DECLARE @i int
> SET @i = 1
>
> WHILE @i <= 100000
> BEGIN
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> SET @i = @i + 1
> END
>
>
>
> Inserting and updating will work very fast on SQL Server on WinXP, but
> very slow on SQL Virtual Server on Win2003 failover cluster.
>
>
> To achieve also fast performance on SQL Virtual Server you must
> enclose insert and update with BEGIN and COMMIT TRAN.
>
> So, for updating you will have next SQL:
> DECLARE @i int
> SET @i = 1
>
> BEGIN TRAN
>
> WHILE @i <= 100000
> BEGIN
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> SET @i = @i + 1
> END
>
> COMMIT TRAN
>
> In my client application (exporting records from database table to
> ASCII file) I need
> to update one flag when upon exporting into file.
> And I do the update with
> UPDATE TEST SET @Updated = 1 WHERE ID = @i
> It works very fast on
> my local machine with WinXP, but very slow on cluster.
>
> I am sure that is slow on cluster because of different transaction
> handling.
> It seems that Virtual server open and commit transaction for every
> updated record.
>
> I
>
>
> 1. Does anybody know how to achive the same behaviuor on SQL Virtual
> Server like I have on
> SQL Server on WIinXP.
>
> 2. Is it the matter of SQL virtual server configuration ? or
>
> 3. Application for running on Virtual Server must be written on
> totally different way.
>
>
>
> Just for information, application is written in Visual C++ using ADO
> for database handling.
>

21 Jul 2006 6:02 PM

David Browne

Show quote

"ikaliy" <ika***@yahoo.com> wrote in message
news:1153502289.366284.58330@p79g2000cwp.googlegroups.com...
> Hello,
>
> I have noticed the difference in transaction handling between SQL
> Server 2000 EE installed on local WinXP machine
> and SQL Virtual server on WIN 2003 failover cluster.
> And this difference put me in big trouble.
>
> For example:
> If you need to update 100000 record (update of one integer field),
> it will run much faster on SQLServer WinXP than on SQL Virtual Server
> WIN2003 cluster.
>
> Suppose that you create simpe table on next way:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[TEST]
> GO
>
> CREATE TABLE [dbo].[TEST] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Updated] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
> CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
>
> Then you insert 100000 record into table with:
>
> DECLARE @i int
> SET @i = 1
>
begin transaction
> WHILE @i <= 100000
> BEGIN
> INSERT INTO TEST (Updated) VALUES (1)
> SET @i = @i + 1
> END
>
commit

This is often caused by the fact that your XP box uses disk write caching
for the transaction log and the server (rightly) doesn't. Wrap all the
inserts in one big transaction and see if the difference persists.

David

21 Jul 2006 6:48 PM

ikaliy

Thanks for help so far.

Yes Arnie, when I refer to "SQL Virtual Server', I mean on the cluster
Instance of
SQL Server.

> This is often caused by the fact that your XP box uses disk write caching
> for the transaction log and the server (rightly) doesn't. Wrap all the
> inserts in one big transaction and see if the difference persists.
>

I put that in one big transaction but it did not solved the problem.

21 Jul 2006 7:42 PM

ikaliy

>
> > This is often caused by the fact that your XP box uses disk write caching
> > for the transaction log and the server (rightly) doesn't. Wrap all the
> > inserts in one big transaction and see if the difference persists.
> >
>
> I put that in one big transaction but it did not solved the problem.

Yes, it solved the problem !!!
David, thanks for help