March 11, 2008 at 1:50 am
Thanks for posting that - can you also please post the "CREATE TABLE" statements for the tables involved? I appreciate that you cannot supply all of the sample data but could you also include some insert statements for a few rows worth of data?
March 11, 2008 at 2:08 am
Hi Ian,
This is the main facttable:
CREATE TABLE [dbo].[FactsDossiers] (
[Admincode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Broncode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Klantcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dossiercode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Boekstuktype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Productcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Kostenplaatscode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Medewerkercode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dienstverbandcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Functiecode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Financieringscode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Projectcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Rekeningcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Datum] [datetime] NOT NULL ,
[Kengetal] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Aantal] [float] NULL ,
[RapportageCategorie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Tredeomschrijving] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Ontdubbelingcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dubbeldossiercode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vervangendezorgcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VervangendeDossiercode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VervangendProductcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Documenttype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DocumentProductcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Documentnummer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DocumentMutatieomschrijving] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StapelDocumenttype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StapelDocumentProductcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StapelDocumentnummer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Indicatiebesluitnummer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Samenloop] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KlantID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactsDossiers] ADD
CONSTRAINT [DF_FactsDossiers_Broncode] DEFAULT (N'Nb') FOR [Broncode],
CONSTRAINT [DF_FactsDossiers_Dienstverbandcode] DEFAULT (N'nb') FOR [Dienstverbandcode],
CONSTRAINT [DF_FactsDossiers_Functiecode] DEFAULT (N'Nb') FOR [Functiecode],
CONSTRAINT [DF_FactsDossiers_Projectcode] DEFAULT (N'nb') FOR [Projectcode],
CONSTRAINT [DF_FactsDossiers_Rekeningcode] DEFAULT (N'nb') FOR [Rekeningcode],
CONSTRAINT [DF_FactsDossiers_RapportageCategorie] DEFAULT (N'nb') FOR [RapportageCategorie],
CONSTRAINT [DF_FactsDossiers_Tredeomschrijving] DEFAULT (N'nb') FOR [Tredeomschrijving],
CONSTRAINT [DF_FactsDossiers_Ontdubbelingcode] DEFAULT (N'nb') FOR [Ontdubbelingcode],
CONSTRAINT [DF_FactsDossiers_Vervangendezorgcode] DEFAULT (N'nb') FOR [Vervangendezorgcode],
CONSTRAINT [DF_FactsDossiers_MaatregelBoekstuktype] DEFAULT (N'nb') FOR [Documenttype],
CONSTRAINT [DF_FactsDossiers_MaatregelJRcode] DEFAULT (N'nb') FOR [DocumentProductcode],
CONSTRAINT [DF_FactsDossiers_MaatregelJRnummer] DEFAULT (N'nb') FOR [Documentnummer],
CONSTRAINT [DF_FactsDossiers_StapelDocumenttype] DEFAULT (N'nb') FOR [StapelDocumenttype],
CONSTRAINT [DF_FactsDossiers_StapelDocumentProductcode] DEFAULT (N'nb') FOR [StapelDocumentProductcode],
CONSTRAINT [DF_FactsDossiers_StapelDocumentnummer] DEFAULT (N'nb') FOR [StapelDocumentnummer],
CONSTRAINT [DF_FactsDossiers_Indicatiebesluitnummer] DEFAULT (N'nb') FOR [Indicatiebesluitnummer],
CONSTRAINT [DF_FactsDossiers_Samenloop] DEFAULT (N'nb') FOR [Samenloop]
GO
CREATE INDEX [IX_FactsDossiers] ON [dbo].[FactsDossiers]([Admincode], [Kengetal]) ON [PRIMARY]
GO
CREATE INDEX [IX_FactsDossiers_1] ON [dbo].[FactsDossiers]([Productcode]) ON [PRIMARY]
GO
CREATE INDEX [IX_FactsDossiers_2] ON [dbo].[FactsDossiers]([Kostenplaatscode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactsDossiers] ADD
CONSTRAINT [FK_FactsDossiers_DimAdmincode] FOREIGN KEY
(
[Admincode]
) REFERENCES [dbo].[DimAdmincode] (
[Admincode]
),
CONSTRAINT [FK_FactsDossiers_DimFunctie] FOREIGN KEY
(
[Admincode],
[Functiecode]
) REFERENCES [dbo].[DimFunctie] (
[Admincode],
[Functiecode]
),
CONSTRAINT [FK_FactsDossiers_DimMeasure] FOREIGN KEY
(
[Kengetal]
) REFERENCES [dbo].[DimMeasure] (
[Measurecode]
),
CONSTRAINT [FK_FactsDossiers_DimProduct] FOREIGN KEY
(
[Admincode],
[Boekstuktype],
[Productcode]
) REFERENCES [dbo].[DimProduct] (
[Admincode],
[Boekstuktype],
[Productcode]
),
CONSTRAINT [FK_FactsDossiers_DimRekening] FOREIGN KEY
(
[Admincode],
[Rekeningcode]
) REFERENCES [dbo].[DimRekening] (
[Admincode],
[Rekeningcode]
)
GO
March 11, 2008 at 2:08 am
Ok Thanks
March 11, 2008 at 2:11 am
OK Jeff, I suppose you suggest to turn off the autoshrink. Shall I do this in the overnight procedures? By the way the users are on a cube based on the datawarehouse in SQL. There are no user in the SQL warehouse. The are all on the cubes.
Menno
March 11, 2008 at 4:58 am
Doesn't matter who's on what... shrinking a database should not be done just as a matter of fact either automatically or by scheduled task.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 7:01 am
OK Jeff, So no shrinking! Is there any point in time that shrinking is good?
Menno
March 11, 2008 at 7:10 am
M.van Leewen (3/11/2008)
OK Jeff, So no shrinking! Is there any point in time that shrinking is good?Menno
Shrinking - sometimes. Auto-shrinking - essentially never.
Shrinking allows you to recover some size if you have a process that goes out of control, a bad query, etc... In other words - something makes your DB "blow up". Of course - besides the OS fragmentation, it also tends to create internal fragmentation, so you need to run reorgs and rebuilds a lot more than you usually would have to.
If your database is continuously growing, then give is lots of room to grow into. Once you get close to your size again, then add in another big chunk.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 7:20 am
No, not really... only time I do it is if someone made an accidental cross join (for example) that caused a db to become over inflated. But when that happens, if DBA isn't made aware almost immediately, then the DBA hasn't done his/her jobs by setting up the correct pager or email alerts. Other than that, if you have to shrink the database, then you haven't planned correctly. Database should not be allowed to grow on it's own. Sure, the settings say it can, but the DBA better be on top of space requirements and cause it to grow to anticipated size plus headroom for the next 3 to 6 months. "Hog" code that makes databases grow without a real need should be identified and rewritten. There's just not much of an excuse to need to shrink a database. And, depending on the size of your total number of databases for any given instance and the correct need for speed, you may want to do like we did... we set TempDB to boot up with 9Gig... 2nd best hardware related that you can do for a database after adding memory to the server is making sure TempDB is large enough to handle the load without the need for growth.
Of course, the best thing you can do for any database is to do code reviews and refuse to let anything that even comes close to RBAR or hidden RBAR into your database. Yeah, there's the occasional exception but the RBAR better be used to drive sets of processing if it's batch code. GUI code is a whole 'nuther story but you need to be double careful there... GUI code is inherently RBAR (one screen of info at a time) especially on the data entry side of the GUI... people do get carried away just because it's GUI code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 9:32 am
OK Jeff and Matt,
I have deactivated autoshrink and I will plan better :D. I learned a lot in a few post from you all. Thanks.
I ran the estimated execution plan and the indextuning wizard on the result and got some advise to add some indexes. It look like a good advise.
Any objection.:)
Menno
March 11, 2008 at 9:55 am
M.van Leewen (3/11/2008)
OK Jeff and Matt,I have deactivated autoshrink and I will plan better :D. I learned a lot in a few post from you all. Thanks.
I ran the estimated execution plan and the indextuning wizard on the result and got some advise to add some indexes. It look like a good advise.
Any objection.:)
Menno
Heh... no objection... if and when it does give some index advice, it's usually pretty good. You can beat it sometimes, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 9:58 am
Thanks Jeff. I go for it.
Menno
March 11, 2008 at 10:02 am
Good luck!~
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 10:05 am
Thanks Matt.
March 11, 2008 at 10:27 am
Late to the party and maybe I missed it, but tempdb has nothing to do with logging. Completely separate.
Good advice from Matt and Jeff and I'd agree with it.
Also, shrinking doesn't make sense if you'll just grow again in the next load cycle. you want to have space ready. You shrink when you have a one-time event that grows things abnormally, like someone ran the import 3 times and grew the database and you have to delete all this data.
Shrink is always (or should be) a manual process that is very, very rarely used.
March 11, 2008 at 10:52 am
Better Late then not at all.
Thanks for the confirmation.
I am executing tests now and I will report later.
I am still very interested in a suggestion for a SP that chops the process in better/smaller pieces.
Menno
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply