sql bit data type: backups

Showing posts with label backups. Show all posts

Saturday, February 25, 2012

BLOB Field in a table causing huge wastage?

Our backups are extremely slow as the docs table in the site database is
extremely large.
I've tried to shrink the file and shrink the database using DBCC
ShrinkDatabase and then DBCC ShrinkFile with no luck.
On examining the database i found this...
Table_Name data_KB unused_KB unused_pct
-- -- -- --
Docs 16490400 KB 24676360 KB 60%
DocVersions 1957800 KB 3915816 KB 67%
DBCC ShowContig (Docs) shows the following
DBCC SHOWCONTIG scanning 'Docs' table...
Table: 'Docs' (1993058136); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned........................: 13643
- Extents Scanned.......................: 1761
- Extent Switches.......................: 1760
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.28%
- Avg. Bytes Free per Page................: 210.6
- Avg. Page Density (full)................: 97.40%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
so as far as i can see the table isnt fragmented but is extremely wasteful.
My thinking is this...
Files in the docs table range widely in size from a few hundred KB to 50MB.
Am i right in thinking that the "content" column in Docs is set to a size
that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB are
wasted on other rows?
I really need someones help in reducing the size of the database, i've tried
reindexing the database too, again with no difference other than the extent
scan fragmentation then goes shooting up to 99.97%Hi Mauro
> Files in the docs table range widely in size from a few hundred KB to
> 50MB.
There are no files in a table. Do you mean LOB data (text, image or ntext)?
How is the table defined?
How is the LOB data inserted?
Is the LOB data ever modified? If so, how?
What version are you using?
> Am i right in thinking that the "content" column in Docs is set to a size
> that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> are
> wasted on other rows?
This is NOT correct. How would SQL Server know the largest size you are
planning to insert? The largest size a LOB column can hold is 2 GB and it
obviously isn't reserving 2 GB per row.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
> Our backups are extremely slow as the docs table in the site database is
> extremely large.
> I've tried to shrink the file and shrink the database using DBCC
> ShrinkDatabase and then DBCC ShrinkFile with no luck.
> On examining the database i found this...
> Table_Name data_KB unused_KB unused_pct
> -- -- -- --
> Docs 16490400 KB 24676360 KB 60%
> DocVersions 1957800 KB 3915816 KB 67%
> DBCC ShowContig (Docs) shows the following
> DBCC SHOWCONTIG scanning 'Docs' table...
> Table: 'Docs' (1993058136); index ID: 1, database ID: 15
> TABLE level scan performed.
> - Pages Scanned........................: 13643
> - Extents Scanned.......................: 1761
> - Extent Switches.......................: 1760
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.28%
> - Avg. Bytes Free per Page................: 210.6
> - Avg. Page Density (full)................: 97.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> so as far as i can see the table isnt fragmented but is extremely
> wasteful.
> My thinking is this...
> Files in the docs table range widely in size from a few hundred KB to
> 50MB.
> Am i right in thinking that the "content" column in Docs is set to a size
> that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> are
> wasted on other rows?
> I really need someones help in reducing the size of the database, i've
> tried
> reindexing the database too, again with no difference other than the
> extent
> scan fragmentation then goes shooting up to 99.97%
>|||I noticed full text indexing was on in the table, so i disabled it for the
table and across the entire sharepoint site. this again had no effect. For
curiosity's sake, i am going to DTS the entire table row by row to another
table and see if it is 26GB as it reports or 40GB as is shown by the file
size.
"Kalen Delaney" wrote:
> Hi Mauro
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> There are no files in a table. Do you mean LOB data (text, image or ntext)?
> How is the table defined?
its defined by Sharepoint Server 2003 (in its unmodified state) there are
three blob fields
> How is the LOB data inserted?
as far as i know its inserted using a normal insert statement, and also
inserted by sharepoint through the sharepoint user interface.
> Is the LOB data ever modified? If so, how?
not as such, it can be overwritten or deleted by a client app. no one can
actually edit the LOB that is inserted directly to the database, however,
sharepoint does allow microsoft office integration which allows us to edit MS
Office documents (and any other documents which have internet explorer
integration).
> What version are you using?
its SQL Server 2000 with SP4.
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> This is NOT correct. How would SQL Server know the largest size you are
> planning to insert? The largest size a LOB column can hold is 2 GB and it
> obviously isn't reserving 2 GB per row.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>|||thanks for the help, on closer inspection (looking at the file size's
themselves) i've found sql server was reporting the wrong size. Ive since
forced the db to update the stats and its now reporting the correct size.
"Kalen Delaney" wrote:
> Hi Mauro
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> There are no files in a table. Do you mean LOB data (text, image or ntext)?
> How is the table defined?
> How is the LOB data inserted?
> Is the LOB data ever modified? If so, how?
> What version are you using?
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> This is NOT correct. How would SQL Server know the largest size you are
> planning to insert? The largest size a LOB column can hold is 2 GB and it
> obviously isn't reserving 2 GB per row.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
> news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
> > Our backups are extremely slow as the docs table in the site database is
> > extremely large.
> >
> > I've tried to shrink the file and shrink the database using DBCC
> > ShrinkDatabase and then DBCC ShrinkFile with no luck.
> >
> > On examining the database i found this...
> > Table_Name data_KB unused_KB unused_pct
> > -- -- -- --
> > Docs 16490400 KB 24676360 KB 60%
> > DocVersions 1957800 KB 3915816 KB 67%
> >
> > DBCC ShowContig (Docs) shows the following
> > DBCC SHOWCONTIG scanning 'Docs' table...
> > Table: 'Docs' (1993058136); index ID: 1, database ID: 15
> > TABLE level scan performed.
> > - Pages Scanned........................: 13643
> > - Extents Scanned.......................: 1761
> > - Extent Switches.......................: 1760
> > - Avg. Pages per Extent..................: 7.7
> > - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
> > - Logical Scan Fragmentation ..............: 0.01%
> > - Extent Scan Fragmentation ...............: 0.28%
> > - Avg. Bytes Free per Page................: 210.6
> > - Avg. Page Density (full)................: 97.40%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > so as far as i can see the table isnt fragmented but is extremely
> > wasteful.
> >
> > My thinking is this...
> >
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> >
> > I really need someones help in reducing the size of the database, i've
> > tried
> > reindexing the database too, again with no difference other than the
> > extent
> > scan fragmentation then goes shooting up to 99.97%
> >
>
>|||Full text indexes are stored completely separately and are not considered
part of the space used by the table.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:BD339FF2-F59C-434D-B270-83746525FD87@.microsoft.com...
>I noticed full text indexing was on in the table, so i disabled it for the
> table and across the entire sharepoint site. this again had no effect.
> For
> curiosity's sake, i am going to DTS the entire table row by row to another
> table and see if it is 26GB as it reports or 40GB as is shown by the file
> size.
> "Kalen Delaney" wrote:
>> Hi Mauro
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> There are no files in a table. Do you mean LOB data (text, image or
>> ntext)?
>> How is the table defined?
> its defined by Sharepoint Server 2003 (in its unmodified state) there are
> three blob fields
>> How is the LOB data inserted?
> as far as i know its inserted using a normal insert statement, and also
> inserted by sharepoint through the sharepoint user interface.
>> Is the LOB data ever modified? If so, how?
> not as such, it can be overwritten or deleted by a client app. no one can
> actually edit the LOB that is inserted directly to the database, however,
> sharepoint does allow microsoft office integration which allows us to edit
> MS
> Office documents (and any other documents which have internet explorer
> integration).
>> What version are you using?
> its SQL Server 2000 with SP4.
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> This is NOT correct. How would SQL Server know the largest size you are
>> planning to insert? The largest size a LOB column can hold is 2 GB and it
>> obviously isn't reserving 2 GB per row.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>|||I'm glad you figured this out.
I usually suggest that running with updateusage should be your first step
if you are concerned about the values returned by sp_spaceused.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:7945DA78-EAA0-4248-8C66-9C33B4BF9BB3@.microsoft.com...
> thanks for the help, on closer inspection (looking at the file size's
> themselves) i've found sql server was reporting the wrong size. Ive since
> forced the db to update the stats and its now reporting the correct size.
> "Kalen Delaney" wrote:
>> Hi Mauro
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> There are no files in a table. Do you mean LOB data (text, image or
>> ntext)?
>> How is the table defined?
>> How is the LOB data inserted?
>> Is the LOB data ever modified? If so, how?
>> What version are you using?
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> This is NOT correct. How would SQL Server know the largest size you are
>> planning to insert? The largest size a LOB column can hold is 2 GB and it
>> obviously isn't reserving 2 GB per row.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
>> news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
>> > Our backups are extremely slow as the docs table in the site database
>> > is
>> > extremely large.
>> >
>> > I've tried to shrink the file and shrink the database using DBCC
>> > ShrinkDatabase and then DBCC ShrinkFile with no luck.
>> >
>> > On examining the database i found this...
>> > Table_Name data_KB unused_KB
>> > unused_pct
>> > -- -- -- --
>> > Docs 16490400 KB 24676360 KB 60%
>> > DocVersions 1957800 KB 3915816 KB 67%
>> >
>> > DBCC ShowContig (Docs) shows the following
>> > DBCC SHOWCONTIG scanning 'Docs' table...
>> > Table: 'Docs' (1993058136); index ID: 1, database ID: 15
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 13643
>> > - Extents Scanned.......................: 1761
>> > - Extent Switches.......................: 1760
>> > - Avg. Pages per Extent..................: 7.7
>> > - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
>> > - Logical Scan Fragmentation ..............: 0.01%
>> > - Extent Scan Fragmentation ...............: 0.28%
>> > - Avg. Bytes Free per Page................: 210.6
>> > - Avg. Page Density (full)................: 97.40%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > so as far as i can see the table isnt fragmented but is extremely
>> > wasteful.
>> >
>> > My thinking is this...
>> >
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> >
>> > I really need someones help in reducing the size of the database, i've
>> > tried
>> > reindexing the database too, again with no difference other than the
>> > extent
>> > scan fragmentation then goes shooting up to 99.97%
>> >
>>

BLOB Field in a table causing huge wastage?

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.
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.

Saturday, February 25, 2012

BLOB Field in a table causing huge wastage?

BLOB Field in a table causing huge wastage?

Sunday, February 12, 2012

Bizarre performance hit

Bizarre performance hit

Bizarre performance hit

sql bit data type

Blog Archive

About Me