I create backups of a remote, production database and copy it after compression to a local,
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is an AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is noticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 records. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is acceptable.
After synchronizing the database logins with the those in the Master table on the development
machine, I execute the same SP on the development machine. The Reads are about 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do need help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIA
Run sp_updatestats after a restore and then try it.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:5os5k15iub07uqubpdqb8acgg7vcaq0grj@.4ax.com...
>I create backups of a remote, production database and copy it after
>compression to a local,
> development machine.
> The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is
> an AMD X2 3800+ dual
> processor with 2G memory. Subjectively, I feel the development machine is
> noticeably faster in most
> ways.
> On the remote machine, I execute a SP which inserts approximately 6000
> records. From the profiler, I
> see each insertion takes about 50 Reads and about 0 Duration, which is
> acceptable.
> After synchronizing the database logins with the those in the Master table
> on the development
> machine, I execute the same SP on the development machine. The Reads are
> about 7500 and the Duration
> around 3200!
> Can anyone suggest reasons why this differential might occur? I really do
> need help. I'm not a DBA
> but a developer. But it doesn't take a DBA to know that this won't fly.
> TIA
|||Thanks for the reply, Andrew. I'm sorry to say it had no effect.
On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Run sp_updatestats after a restore and then try it.
|||Are you sure there were no indexes added to the remote machine between the
time the backup was created and now? If the stats are the same (try running
in on the remote machine and see if it changes) then they should be the same
plan. Can you post the code for the sp?
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com... [vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
|||May be optimizer is not using the right indexes, you can try forcing the index.
Mohammed.
"Andrew J. Kelly" wrote:
> Are you sure there were no indexes added to the remote machine between the
> time the backup was created and now? If the stats are the same (try running
> in on the remote machine and see if it changes) then they should be the same
> plan. Can you post the code for the sp?
> --
> Andrew J. Kelly SQL MVP
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
>
>
|||You did run sp_updatestats in the correct database context not the default
for [master]?
i.e.
USE [MyDB]
GO
exec sp_updatestats
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com... [vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
|||Andrew,
Sorry for the delay - DSL down for over 24 hours.
No changes made to anything execpt restoring DB, syncying user ids from Master, and running the
sp_updatestats. I also went to the remote, production machine and copied the BAK file to DVD, rather
than use the compressed BAK. The results were the same.
The SP and associated table definitions follow. I abridged some of the tables for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.PersonID = 1659603,
@.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100 12:00AM', @.OEL =
N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
select @.P1
CREATE PROCEDURE [dbo].usp_Mailings_Ins
@.MailCampaignDetailID int,
@.AddressID int,
@.PersonID int,
@.BatchID int,
@.TrayNo int,
@.SerialNo int,
@.NextMailDate DATETIME,
@.OEL varchar(50),
@.MailID int OUTPUT
AS
INSERT INTO [dbo].[Mailings] (
[MailCampaignDetailID],
[AddressID],
[PersonID],
[BatchID],
[TrayNo],
[SerialNo],
NextMailDate,
OEL
) VALUES (
@.MailCampaignDetailID,
@.AddressID,
@.PersonID,
@.BatchID,
@.TrayNo,
@.SerialNo,
@.NextMailDate,
@.OEL
)
SET @.MailID = SCOPE_IDENTITY()
GO
CREATE TABLE [dbo].[MailingBatch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[CompID] [int] NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[BatchType] [int] NOT NULL ,
[PostageType] [int] NULL ,
[Cost] [decimal](18, 0) NOT NULL ,
[parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MailCampaignDetail] (
[MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mailings] (
[MailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[AddressID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[BatchID] [int] NULL ,
[TrayNo] [int] NULL ,
[SerialNo] [int] NULL ,
[NextMailDate] [datetime] NULL ,
[MailDate] [smalldatetime] NOT NULL ,
[OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[BatchID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
(
[MailCampaignDetailID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
(
[MailID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_MCDID_AddressID] ON [dbo].[Mailings]([MailCampaignDetailID],
[AddressID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON [PRIMARY]
GO
CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID], [MailCampaignDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] ADD
CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressValid] (
[AddressID]
),
CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
(
[MailCampaignDetailID]
) REFERENCES [dbo].[MailCampaignDetail] (
[MailCampaignDetailID]
),
CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[Person] (
[PersonID]
)
GO
On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Are you sure there were no indexes added to the remote machine between the
>time the backup was created and now? If the stats are the same (try running
>in on the remote machine and see if it changes) then they should be the same
>plan. Can you post the code for the sp?
|||I was sure I ran sp_updatestats against the appropriate DB.
On Wed, 05 Oct 2005 06:13:44 GMT, "Nik Marshall-Blank" <Nik@.here.com> wrote:
>You did run sp_updatestats in the correct database context not the default
>for [master]?
>i.e.
>USE [MyDB]
>GO
>exec sp_updatestats
|||Mohammed,
I'm afraid I don't know how to "force" an index. Can you give me an example?
On Tue, 4 Oct 2005 18:08:03 -0700, "Mohammed" <Mohammed@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>May be optimizer is not using the right indexes, you can try forcing the index.
>Mohammed.
>"Andrew J. Kelly" wrote:
|||Well one thing I see is that you should put SET NOCOUNT ON at the beginning
of your sp but that should be the same on both.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:fs4bk1de83u04keblbk0qnnr0hit4fvtae@.4ax.com... [vbcol=seagreen]
> Andrew,
> Sorry for the delay - DSL down for over 24 hours.
> No changes made to anything execpt restoring DB, syncying user ids from
> Master, and running the
> sp_updatestats. I also went to the remote, production machine and copied
> the BAK file to DVD, rather
> than use the compressed BAK. The results were the same.
> The SP and associated table definitions follow. I abridged some of the
> tables for simplicity.
> exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896,
> @.PersonID = 1659603,
> @.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100
> 12:00AM', @.OEL =
> N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
> select @.P1
> CREATE PROCEDURE [dbo].usp_Mailings_Ins
> @.MailCampaignDetailID int,
> @.AddressID int,
> @.PersonID int,
> @.BatchID int,
> @.TrayNo int,
> @.SerialNo int,
> @.NextMailDate DATETIME,
> @.OEL varchar(50),
> @.MailID int OUTPUT
> AS
> INSERT INTO [dbo].[Mailings] (
> [MailCampaignDetailID],
> [AddressID],
> [PersonID],
> [BatchID],
> [TrayNo],
> [SerialNo],
> NextMailDate,
> OEL
> ) VALUES (
> @.MailCampaignDetailID,
> @.AddressID,
> @.PersonID,
> @.BatchID,
> @.TrayNo,
> @.SerialNo,
> @.NextMailDate,
> @.OEL
> )
> SET @.MailID = SCOPE_IDENTITY()
> GO
> CREATE TABLE [dbo].[MailingBatch] (
> [BatchID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompID] [int] NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [BatchType] [int] NOT NULL ,
> [PostageType] [int] NULL ,
> [Cost] [decimal](18, 0) NOT NULL ,
> [parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dateAdded] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Person] (
> [PersonID] [int] IDENTITY (1, 1) NOT NULL ,
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Mailings] (
> [MailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [AddressID] [int] NOT NULL ,
> [PersonID] [int] NOT NULL ,
> [BatchID] [int] NULL ,
> [TrayNo] [int] NULL ,
> [SerialNo] [int] NULL ,
> [NextMailDate] [datetime] NULL ,
> [MailDate] [smalldatetime] NOT NULL ,
> [OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
> CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
> (
> [BatchID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
> (
> [MailCampaignDetailID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
> CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
> (
> [MailID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID],
> [SerialNo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_MCDID_AddressID] ON
> [dbo].[Mailings]([MailCampaignDetailID],
> [AddressID]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID],
> [MailCampaignDetailID]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] ADD
> CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressValid] (
> [AddressID]
> ),
> CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
> (
> [MailCampaignDetailID]
> ) REFERENCES [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID]
> ),
> CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
> (
> [PersonID]
> ) REFERENCES [dbo].[Person] (
> [PersonID]
> )
> GO
>
> On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
