In the production server we are noticing blocks & dead-locks. I have used trace flag 1204 to capture info for deadlock which hapens very rarely.
I am seeing blocks every now & then. Is there any tools/system tables to query to get more info abt blocking & blocked spids. I used info from syslockinfo & sysprocess. dbcc inputbuffer gives info like 'sp_cursorunprepare;1' or 'sp_cursorfetch;1' and puts exclusive locks on bunch of tables. However no activity is seen for that spid.
Any help will be appreciated. I am using SQL Server 2000 with SP2
RachaelTry with sp_who2 and adding (NOLOCK) in your selects.
Csar.
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Monday, March 19, 2012
Blocking while getting Snapshot
We have a production database that is replicated(transactional).
Whenever we have to take a snapshot, it creates blocking on the
production tables and causes timeouts within the production
application. This is creating big problems and I am wondering if there
is away to avoid the blocking which causes the timeouts.
Please have a look at the option to allow concurrent snapshot generation
(snapshot tab - 'do not lock tables...').
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That works very well for us. Thanks for the information.
|||I have the same problem and tried to use the 'Do Not Lock Table Option'.
However, the snapshot loading failed. Please help if you could post your
test result in this threat. Thanks a lot.
|||Please can you post up your complete error message.
Rgds,
Paul Ibison
Whenever we have to take a snapshot, it creates blocking on the
production tables and causes timeouts within the production
application. This is creating big problems and I am wondering if there
is away to avoid the blocking which causes the timeouts.
Please have a look at the option to allow concurrent snapshot generation
(snapshot tab - 'do not lock tables...').
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That works very well for us. Thanks for the information.
|||I have the same problem and tried to use the 'Do Not Lock Table Option'.
However, the snapshot loading failed. Please help if you could post your
test result in this threat. Thanks a lot.
|||Please can you post up your complete error message.
Rgds,
Paul Ibison
Sunday, February 19, 2012
Blank Pages in report
I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.
Does anyone have any ideas?
Thanks
If you take things out does it still put 3 extra pages in?|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.
Thanks
|||This is the most common problem for blank pages in a report.
In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.
Blank Pages in a report <-- text put there for people searching on this problem.
Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.
Daryl
Blank Pages in report
I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.
Does anyone have any ideas?
Thanks
If you take things out does it still put 3 extra pages in?|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||
Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.
Thanks
|||This is the most common problem for blank pages in a report.
In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.
Blank Pages in a report <-- text put there for people searching on this problem.
Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.
Daryl
Sunday, February 12, 2012
Bizarre performance hit
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:
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:
Bizarre performance hit
I create backups of a remote, production database and copy it after compress
ion to a local,
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is a
n AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is no
ticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 recor
ds. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is accep
table.
After synchronizing the database logins with the those in the Master table o
n the development
machine, I execute the same SP on the development machine. The Reads are abo
ut 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do ne
ed help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIARun 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@.shadhaw
k.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 ind
ex.
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 runni
ng
> in on the remote machine and see if it changes) then they should be the sa
me
> 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 Mast
er, 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 table
s for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.Per
sonID = 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 N
OT 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]([Bat
chID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([Pe
rsonID]) 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]([A
ddressID]) 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@.shadhaw
k.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 runnin
g
>in on the remote machine and see if it changes) then they should be the sam
e
>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.microsof
t.com> wrote:
[vbcol=seagreen]
>May be optimizer is not using the right indexes, you can try forcing the in
dex.
>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]([B
atchID],
> [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:
>
ion to a local,
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is a
n AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is no
ticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 recor
ds. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is accep
table.
After synchronizing the database logins with the those in the Master table o
n the development
machine, I execute the same SP on the development machine. The Reads are abo
ut 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do ne
ed help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIARun 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@.shadhaw
k.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 ind
ex.
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 runni
ng
> in on the remote machine and see if it changes) then they should be the sa
me
> 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 Mast
er, 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 table
s for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.Per
sonID = 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 N
OT 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]([Bat
chID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([Pe
rsonID]) 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]([A
ddressID]) 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@.shadhaw
k.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 runnin
g
>in on the remote machine and see if it changes) then they should be the sam
e
>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.microsof
t.com> wrote:
[vbcol=seagreen]
>May be optimizer is not using the right indexes, you can try forcing the in
dex.
>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]([B
atchID],
> [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:
>
Bizarre performance hit
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.
TIARun 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...
> 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.|||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...
> > 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.
>
>|||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...
> 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.|||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:
>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...
>> > 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.
>>|||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...
> 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 began comparing the execution plans on each of the two machines. Of course, they were different.
One big difference was a Hash Match/Inner Join which has an estimated row count of 2,250,000 on the
development machine.
The execution plan says that it is doing an Index Scan on Addressvalid.Address21 and also on
UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
|--Hash Match(Inner Join, HASH:([UseCode].[UseCodeID],
[UseCode].[CountyFipsID])=([AddressValid].[useCodeID], [AddressValid].[countyCodeFips]),
RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
[AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
|--Index Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
|--Index Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
Why is doing all this when it's supposed to be inserting rows in Mailings?
Why is the development machine doing this and the production not?
How can I get to two machines in sync?
Thanks for you help.
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL,
[dataSourceID] [int] NOT NULL,
...
[useCodeID] [char] (6) NOT NULL,
...
[houseNo] [varchar] (10) NULL,
[preDir] [char] (2) NULL,
[streetName] [varchar] (28) NULL,
[streetSuffix] [char] (4) NULL,
[postDir] [char] (2) NULL,
[city] [varchar] (28) NULL,
[state] [char] (2) NULL,
[zip5] [char] (5) NULL,
[zip4] [char] (4) NULL,
[sud] [char] (4) NULL,
[unitNum] [varchar] (8) NULL,
...
[countyCodeFips] [char] (5) NULL,
...
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
(
[streetName],
[houseNo],
[streetSuffix],
[preDir],
[postDir],
[zip5],
[zip4],
[sud],
[unitNum]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
GO
CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID], [useCodeID], [zip5],
[countyCodeFips]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] ADD
CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressSource] (
[addressID]
),
CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
(
[dataSourceID]
) REFERENCES [dbo].[datasource] (
[DataSourceID]
),
CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
(
[countyCodeFips],
[useCodeID]
) REFERENCES [dbo].[UseCode] (
[CountyFipsID],
[UseCodeID]
)
GO
CREATE TABLE [dbo].[UseCode] (
[UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
[CountyFipsID] [char] (5) NOT NULL,
[UseCodeID] [char] (6) NOT NULL,
[descr] [varchar] (50) NOT NULL,
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
(
[UseCodePK]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
(
[CountyFipsID],
[UseCodeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] ADD
CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
(
[CountyFipsID]
) REFERENCES [dbo].[CountyFips] (
[CountyFipsID]
)
GO
On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> 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.|||Hello,
I have tested the issue on my side but I am unable to reproduce the issue.
To narrow down the issue, I suggest that you perform the following steps:
1. Restore the database on another known working machine using the same
backup file. The machine has the similar hardware configuration as the
remote machine. Check if you can reproduce the issue on another machine.
2. Create a new test database. Create some tables and a SP in the test
database to check if you can reproduce the issue on another database. Let
me know the results.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sounds like you have referential integrity on some of the columns. In order
to enforce the RI it has to search the other tables. If they don't have
proper indexes it can be a real mess. If the schemas really are identical
(use a tool such as www.red-gate.com to verify) and the number of rows are
the same it would usually boil down to statistics being different. You say
you updated the ones on the dev server, what about the production?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:58bbk11d1g82ein2issf0c878q6kjj6r6o@.4ax.com...
>I began comparing the execution plans on each of the two machines. Of
>course, they were different.
> One big difference was a Hash Match/Inner Join which has an estimated row
> count of 2,250,000 on the
> development machine.
> The execution plan says that it is doing an Index Scan on
> Addressvalid.Address21 and also on
> UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
> |--Hash Match(Inner Join,
> HASH:([UseCode].[UseCodeID],
> [UseCode].[CountyFipsID])=([AddressValid].[useCodeID],
> [AddressValid].[countyCodeFips]),
> RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
> [AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
> Why is doing all this when it's supposed to be inserting rows in Mailings?
> Why is the development machine doing this and the production not?
> How can I get to two machines in sync?
> Thanks for you help.
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL,
> [dataSourceID] [int] NOT NULL,
> ...
> [useCodeID] [char] (6) NOT NULL,
> ...
> [houseNo] [varchar] (10) NULL,
> [preDir] [char] (2) NULL,
> [streetName] [varchar] (28) NULL,
> [streetSuffix] [char] (4) NULL,
> [postDir] [char] (2) NULL,
> [city] [varchar] (28) NULL,
> [state] [char] (2) NULL,
> [zip5] [char] (5) NULL,
> [zip4] [char] (4) NULL,
> [sud] [char] (4) NULL,
> [unitNum] [varchar] (8) NULL,
> ...
> [countyCodeFips] [char] (5) NULL,
> ...
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
> (
> [streetName],
> [houseNo],
> [streetSuffix],
> [preDir],
> [postDir],
> [zip5],
> [zip4],
> [sud],
> [unitNum]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
> GO
> CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID],
> [useCodeID], [zip5],
> [countyCodeFips]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] ADD
> CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressSource] (
> [addressID]
> ),
> CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
> (
> [dataSourceID]
> ) REFERENCES [dbo].[datasource] (
> [DataSourceID]
> ),
> CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
> (
> [countyCodeFips],
> [useCodeID]
> ) REFERENCES [dbo].[UseCode] (
> [CountyFipsID],
> [UseCodeID]
> )
> GO
> CREATE TABLE [dbo].[UseCode] (
> [UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
> [CountyFipsID] [char] (5) NOT NULL,
> [UseCodeID] [char] (6) NOT NULL,
> [descr] [varchar] (50) NOT NULL,
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
> (
> [UseCodePK]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
> (
> [CountyFipsID],
> [UseCodeID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] ADD
> CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
> (
> [CountyFipsID]
> ) REFERENCES [dbo].[CountyFips] (
> [CountyFipsID]
> )
> GO
>
> On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> 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.
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.
TIARun 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...
> 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.|||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...
> > 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.
>
>|||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...
> 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.|||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:
>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...
>> > 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.
>>|||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...
> 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 began comparing the execution plans on each of the two machines. Of course, they were different.
One big difference was a Hash Match/Inner Join which has an estimated row count of 2,250,000 on the
development machine.
The execution plan says that it is doing an Index Scan on Addressvalid.Address21 and also on
UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
|--Hash Match(Inner Join, HASH:([UseCode].[UseCodeID],
[UseCode].[CountyFipsID])=([AddressValid].[useCodeID], [AddressValid].[countyCodeFips]),
RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
[AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
|--Index Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
|--Index Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
Why is doing all this when it's supposed to be inserting rows in Mailings?
Why is the development machine doing this and the production not?
How can I get to two machines in sync?
Thanks for you help.
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL,
[dataSourceID] [int] NOT NULL,
...
[useCodeID] [char] (6) NOT NULL,
...
[houseNo] [varchar] (10) NULL,
[preDir] [char] (2) NULL,
[streetName] [varchar] (28) NULL,
[streetSuffix] [char] (4) NULL,
[postDir] [char] (2) NULL,
[city] [varchar] (28) NULL,
[state] [char] (2) NULL,
[zip5] [char] (5) NULL,
[zip4] [char] (4) NULL,
[sud] [char] (4) NULL,
[unitNum] [varchar] (8) NULL,
...
[countyCodeFips] [char] (5) NULL,
...
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
(
[streetName],
[houseNo],
[streetSuffix],
[preDir],
[postDir],
[zip5],
[zip4],
[sud],
[unitNum]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
GO
CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID], [useCodeID], [zip5],
[countyCodeFips]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] ADD
CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressSource] (
[addressID]
),
CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
(
[dataSourceID]
) REFERENCES [dbo].[datasource] (
[DataSourceID]
),
CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
(
[countyCodeFips],
[useCodeID]
) REFERENCES [dbo].[UseCode] (
[CountyFipsID],
[UseCodeID]
)
GO
CREATE TABLE [dbo].[UseCode] (
[UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
[CountyFipsID] [char] (5) NOT NULL,
[UseCodeID] [char] (6) NOT NULL,
[descr] [varchar] (50) NOT NULL,
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
(
[UseCodePK]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
(
[CountyFipsID],
[UseCodeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] ADD
CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
(
[CountyFipsID]
) REFERENCES [dbo].[CountyFips] (
[CountyFipsID]
)
GO
On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> 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.|||Hello,
I have tested the issue on my side but I am unable to reproduce the issue.
To narrow down the issue, I suggest that you perform the following steps:
1. Restore the database on another known working machine using the same
backup file. The machine has the similar hardware configuration as the
remote machine. Check if you can reproduce the issue on another machine.
2. Create a new test database. Create some tables and a SP in the test
database to check if you can reproduce the issue on another database. Let
me know the results.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sounds like you have referential integrity on some of the columns. In order
to enforce the RI it has to search the other tables. If they don't have
proper indexes it can be a real mess. If the schemas really are identical
(use a tool such as www.red-gate.com to verify) and the number of rows are
the same it would usually boil down to statistics being different. You say
you updated the ones on the dev server, what about the production?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:58bbk11d1g82ein2issf0c878q6kjj6r6o@.4ax.com...
>I began comparing the execution plans on each of the two machines. Of
>course, they were different.
> One big difference was a Hash Match/Inner Join which has an estimated row
> count of 2,250,000 on the
> development machine.
> The execution plan says that it is doing an Index Scan on
> Addressvalid.Address21 and also on
> UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
> |--Hash Match(Inner Join,
> HASH:([UseCode].[UseCodeID],
> [UseCode].[CountyFipsID])=([AddressValid].[useCodeID],
> [AddressValid].[countyCodeFips]),
> RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
> [AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
> Why is doing all this when it's supposed to be inserting rows in Mailings?
> Why is the development machine doing this and the production not?
> How can I get to two machines in sync?
> Thanks for you help.
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL,
> [dataSourceID] [int] NOT NULL,
> ...
> [useCodeID] [char] (6) NOT NULL,
> ...
> [houseNo] [varchar] (10) NULL,
> [preDir] [char] (2) NULL,
> [streetName] [varchar] (28) NULL,
> [streetSuffix] [char] (4) NULL,
> [postDir] [char] (2) NULL,
> [city] [varchar] (28) NULL,
> [state] [char] (2) NULL,
> [zip5] [char] (5) NULL,
> [zip4] [char] (4) NULL,
> [sud] [char] (4) NULL,
> [unitNum] [varchar] (8) NULL,
> ...
> [countyCodeFips] [char] (5) NULL,
> ...
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
> (
> [streetName],
> [houseNo],
> [streetSuffix],
> [preDir],
> [postDir],
> [zip5],
> [zip4],
> [sud],
> [unitNum]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
> GO
> CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID],
> [useCodeID], [zip5],
> [countyCodeFips]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] ADD
> CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressSource] (
> [addressID]
> ),
> CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
> (
> [dataSourceID]
> ) REFERENCES [dbo].[datasource] (
> [DataSourceID]
> ),
> CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
> (
> [countyCodeFips],
> [useCodeID]
> ) REFERENCES [dbo].[UseCode] (
> [CountyFipsID],
> [UseCodeID]
> )
> GO
> CREATE TABLE [dbo].[UseCode] (
> [UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
> [CountyFipsID] [char] (5) NOT NULL,
> [UseCodeID] [char] (6) NOT NULL,
> [descr] [varchar] (50) NOT NULL,
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
> (
> [UseCodePK]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
> (
> [CountyFipsID],
> [UseCodeID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] ADD
> CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
> (
> [CountyFipsID]
> ) REFERENCES [dbo].[CountyFips] (
> [CountyFipsID]
> )
> GO
>
> On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> 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.
Subscribe to:
Posts (Atom)