Αδικαιολόγητο μέγεθος βάσης δεδομένων

[julax]

[]

 

 

[julax]

Λοιπον κάθε φύλλο έχει και το select του και αυτά είναι τα xls www.isweb.gr/provlimatiki_DB.xls   http://www.isweb.gr/kanoniki_DB.xls

 

 

[antonch]

ωραία

πάμε παρακάτω

θέλω τα αποτελεσματα των παρακατω

sp_helpdb

και

SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size -1 OR max_size 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,
SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN convert(nvarchar(30),((growth * 8)/1024))
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id

[julax]

Και απο τις δύο βάσεις ή μόνο απο την προβληματική?

 

 

[antonch]

μια φορα το καθένα

απλά βάλε στην αρχή

USE master

αλλά δώσε μου και αυτο στην κάθε βάση όμως αυτό

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount order by rowcnt desc
drop table #rowcount

[julax]

Τα αποτελέσματα είναι βρίσονται εδώ www.isweb.gr/fasi2.xls στο πρώτο φύλλο είναι τα αποτελέσματα της εντολής sp_helpdb και το δεύτερο φύλλο έιναι τα αποτελέσματα του δεύτερου σκέλους

κατα την εκτέλεση της εντολής

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'

select * from #rowcount order by rowcnt desc

drop table #rowcount

 

στην κανονική βάση (με use master) βγάζει το εξής αποτέλεσμα

 

[dbo].[tblPriceListBeta]    0

 

ενώ στην προβληματική δεν βγάζει τίποτα.

 

Όταν αλλάξω το use και το βάλω ανα κάθε βάση μου βγάζει τα αποτελέσματα του φύλλου 3

 

Κάτι σημανικό: Οι δύο βάσεις βρίσκονται σε διαφορετικά μηχανήματα.

 

 

 

[julax]

Τα αποτελέσματα  βρίσονται εδώ www.isweb.gr/fasi2.xls στο πρώτο φύλλο είναι τα αποτελέσματα της εντολής sp_helpdb και το δεύτερο φύλλο έιναι τα αποτελέσματα του δεύτερου σκέλους

κατα την εκτέλεση της εντολής

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'

select * from #rowcount order by rowcnt desc

drop table #rowcount

 

στην κανονική βάση (με use master) βγάζει το εξής αποτέλεσμα

 

[dbo].[tblPriceListBeta]    0

 

ενώ στην προβληματική δεν βγάζει τίποτα.

 

Όταν αλλάξω το use και το βάλω ανα κάθε βάση μου βγάζει τα αποτελέσματα του φύλλου 3

 

Κάτι σημανικό: Οι δύο βάσεις βρίσκονται σε διαφορετικά μηχανήματα.

 

 

 

[antonch]

εξαιρετικά

τρεξε και στις 2 αυτό αφού πρωτα στη θεση ΧΧΧ βάλεις το όνομα της βάσης

SELECT * FROM sys.dm_db_index_physical_stats(db_id('ΧΧΧ'),null,null,null,null)
GO

και λύσε μου μια απορία ακόμα

έχεις 2 servers και οι δύο έχουν 2008 SQL Server?

[julax]

Τώρα θα εκτελέσω την εντολή, και στους δύο server έχω SQL Server 2008 με Windows Server 2008 Standar

 

[julax]

Στο πρώτο φύλλο είναι τα αποτελέσματα της προβληματικής βάσης, ενώ στο δεύτερο είναι τα αποτελέσματα της κανονικής ( www.isweb.gr/fasi3.xls )

 

[julax]

μη το λάβει υπόψη, θα ανεβάσω το σωτό xls

 

 

[julax]

Τώρα είναι εντάξη

 

 

[antonch]

ωραία αρχίζει να φαίνεται φως

για τρεξε στην κανονική αυτό

select * from sys.objects where object_id=

1413580074

 

και στην άλλη το ίδιο με τιμη

1381579960

[julax]

Είναι λογικό να μη φέρνουν αποτελέσματα ?

 

 

[antonch]

οχι δεν είναι λογικό θα πρέπει να σου φέρει για τρεξε το παραπανω query και στις 2 βασεις με τιμη στο where

1381579960

[julax]

Κατα την εκτέλεση εντολής select * from sys.objects where object_id=1381579960 το αποτέλεσμα είναι:

 

name            object_id   principal_id   schema_id    parent_object_id  type    type_desc        create_date               modify_date

InMessages    1381579960    NULL          1                 0                      U      USER_TABLE    2009-06-26 13:36:25.140    2010-09-28 21:04:25.280   

 

is_ms_shipped    is_published    is_schema_published

0                                0                0

 

 

 

 

 

Ενώ κατα την εκτέλεση εντολής select * from sys.objects where object_id=1413580074 το αποτέλεσμα είναι :

 

name              object_id       principal_id    schema_id    parent_object_id    type    type_desc              create_date                       modify_date    

OutMessages  1413580074        NULL           1                             0              U       USER_TABLE     2009-06-26 13:36:25.153    2009-11-24 16:17:38.820

 

is_ms_shipped    is_published    is_schema_published

 

    0                         0                    0

[antonch]

Για δείξε μου την δομή του πίνακα InMessages (για να το κάνεις αυτό κάνε δεξι κλικ στον πινακα script table as > create to)

[julax]

USE [CMS12131]

GO

 

/****** Object:  Table [dbo].[inMessages]    Script Date: 01/14/2011 01:13:07 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[inMessages](

    [MsgID] [uniqueidentifier] NOT NULL,

    [FromBranchID] [uniqueidentifier] NOT NULL,

    [ToBranchID] [uniqueidentifier] NOT NULL,

    [status] [int] NOT NULL,

    [Received] [datetime] NOT NULL,

    [Processed] [datetime] NULL,

    [Payload] [nvarchar](max) NOT NULL,

    [Error] [nvarchar](1024) NULL,

    [insStamp] [char](30) NULL,

 CONSTRAINT [PK_INMESSAGES] PRIMARY KEY CLUSTERED

(

    [MsgID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

ALTER TABLE [dbo].[inMessages]  WITH CHECK ADD  CONSTRAINT [FK_INMESSAG_REFERENCE_OFFICES] FOREIGN KEY([ToBranchID])

REFERENCES [dbo].[branches] ([branchID])

GO

 

ALTER TABLE [dbo].[inMessages] CHECK CONSTRAINT [FK_INMESSAG_REFERENCE_OFFICES]

GO

 

ALTER TABLE [dbo].[inMessages]  WITH CHECK ADD  CONSTRAINT [FK_INMESSAG_REFERENCE_OFFICES2] FOREIGN KEY([FromBranchID])

REFERENCES [dbo].[branches] ([branchID])

GO

 

ALTER TABLE [dbo].[inMessages] CHECK CONSTRAINT [FK_INMESSAG_REFERENCE_OFFICES2]

GO

[antonch]

δεν μου λες στο συγκεκριμένο πίνακα έβαλες εγγραφές (προφανώς αρκετές) και μετά έσβησες (και πάλι προφανώς αρκετές)?

[julax]

ναι έτσι ακριβώς. Έκανα βλακεία ???? [A]

 

[antonch]

έχεις πέσει στην περίπτωση

1o. Στον πίνακα σου έχεις πεδίο με nvarchar(max)

2ο. Είναι πολύ κακή πρακτική να έχεις auto shrink την βάση σου και γενικά να κάνεις shrink την βάση σου όταν έχει blob fields όπως text, image, varchar(max), nvarchar(max), xml, varbinary(max).

 Τα LOB fields αποθηκεύουν τις τιμές τους είτε in-row είτε off-row. Τα text, ntext, image που είναι τα παλαιά data types εξ ορισμού αποθηκεύονται off-row. Τα varchar(max),nvarchar(max), xml, varbinary(max) αποθηκεύονται in-row εφόσον η τιμής τους χωράει στη σελίδα που είναι το record αλλιώς off-row. Στην off-row αποθήκευση υπάρχει ένας περίπλοκος pointer o οποίος αποθηκεύεται είτε στο data row είτε στο index row και ονομαζεται blob root και περιέχει ένα Pointer στην root page περιέχει τα δεδομένα αυτά (σημειωση αυτά τα data types μπορούν να αποθηκεύσουν πληροφορία μέχρι 2GB άρα έχω ένα δεντρο απο σελίδες των 8Κ που όλες μαζί συνθέτουν την πληροφορία) και ένα timestamp.

Τώρα εσύ έχεις ένα Clustered Index στο πεδίο MsgID και όπως ξέρουμε το leaf level στον clustered index είναι στην ουσία τα data pages (περισσότερα το Σάββατο στο SQL Saturday Night). 

Από τα στοιχεία που μου έστειλες (συγνώμη που σε παίδεψα, αλλά γενικά όταν κατάλαβα περίπου τι παίζει ήθελα να έχω όλες τι πληροφορίες που χρειαζόμουν διαθέσιμες)  και σε ευχαριστώ για αυτό στην προβληματική βάση (που δεν είναι μπορείς να το παθεις και στην κανονική) στα σταστικά με τους Indexes στο πίνακα InMessages υπάρχουν για τον clustered index δύο γραμμές

 

database_id	object_id	index_id	partition_number	index_type_desc	alloc_unit_type_desc	index_depth	index_level	avg_fragmentation_in_percent	fragment_count	avg_fragment_size_in_pages	page_count
7	1381579960	1	1	CLUSTERED INDEX	IN_ROW_DATA	4	0	99,19692565	20295	1,000098546	20297
7	1381579960	1	1	CLUSTERED INDEX	LOB_DATA	1	0	0	NULL	NULL	4999474

όπως βλέπεις στην κίτρινη γράμμη αυτά αποθηκεύονται ξεχωριστα και δυο γραμμές αυτές κάνουν το index. Κοίτα στο τέλος το πόσες σελίδες είναι.

όπως επίσης δες το πόσο fragmented είναι ο index αν και cluster 99,19692565!!!!!

Γιατί τώρα έγινε αυτό

Εσύ έβαλες τα όμορφα δεδομένα σου και αποφάσισες να σβήσεις κάποια από αυτά. Εαν δεν είχες auto shrink την βάση σου δεν θα έτρεχε κάστανο τώρα όμως τρέχεις και αυτό γιατί η διαδικασία του shrink είναι single file δλδ ένα αρχείο την φορά και όχι μόνο αυτό αλλά χρησιμοποιεί και GAM bitmaps για να βρει τη μεγαλύτερη δεσμευμένη σελίδα στο αρχείο. Αυτό έχει σαν αποτέλεσμα να πηγαίνει συνέχεια μπρος και πίσω και το αποτέλεσμα της είναι να κάνει fragmented τον index από εκεί που ήταν μια χαρα defragmented.

Δυστυχώς για σένα δεν υπάρχει μια εύκολη λύση για λύσεις το πρόβλημα σου ή θα πορευτείς με αυτό ή θα βγάλεις το auto shrink. Τώρα για έρθει ο index στα καλά του θα πρέπει να τον κάνεις reorganize. (γάτος ο Απόστολος που είπε για ανατιναγμένους indexes εκει πήγε το μυαλό όλων μας αλλά πρέπει να το τεκμιριώσουμε σωστά

Αυτά το ολίγα από μένα

 

 

[julax]

Μαλιστα..... αν βγάλω auto shrink και κάνω reorganize τα indexes θα μικρύνει η βάση ? θα το δοκιμάσω......

Ευχαριστώ πολύ πάντως. Ήταν παραπάνω απο πολύτιμη η βοήθειά σου (και τα άρθρα σου)

 

[antonch]

Με το που θα κάνεις reorganize τον index, αυτός σίγουρα θα γίνει μικρότερος, όμως με το που θα πας να κάνεις ξανά shrink την βάση θα γίνει όπως και τώρα, άρα όπως σου είπα και παραπάνω θα πρέπει να ζήσεις με αυτό. Εαν τώρα ντε και καλα θέλεις να κάνεις shrink τότε θα μπεις στο κόπο να κάνεις τα εξής:

• Να φτιάξεις ένα νέο  filegroup
• Nα μεταφέρεις όσους πίνακες (και τους indexes αυτών) αντιμετωπίζουν το ίδιο θέμα με τον ΙnMessages (μάλλον και τον OutMessages) στο νέο filegroup χρησιμοποιόντας την CREATE INDEX ... WITH (DROP_EXISTING) ON και αφού γίνει αυτό τότε
• Να κάνεις shrink το PRIMARY FILEGROUP.

Φιλικά

[apostolos]

Έχω την εντύπωση, αλλά πρέπει να το ψάξω καλύτερα, ότι στον 2008 δεν έχουμε τα προβλήματα που υπήρχαν με το reindexing στα blob πεδία.

Στον 2000 η λύση ήταν να μεταφέρεις τα δεδομένα πχ export, truncate το table και ξανά import data. Επίσης ήταν σχεδόν απαραίτητο να κάνεις clustered αυτά τα indexes. Η βάση σου πρέπει να μεταφέρθηκε από 2000 σε 2005 και τώρα σε 2008 χωρίς να διορθωθεί το schema της. Πλέον έχουμε την επιλογή από τον 2005 του ALTER INDEX LOB_COMPACTION = ON. Εκτός από αυτό http://msdn.microsoft.com/en-us/library/ms189738.aspx δυστυχώς δεν έχω βρει κανένα καλύτερο documantation.

Ίσως πάλι και μόνο με ένα dbcc cleantable να λύσεις το πρόβλημα σου http://msdn.microsoft.com/en-us/library/ms174418.aspx

[antonch]

Δεν έχει να κάνει με το reindexing αγαπητέ Απόστολε ...

Έχει να κάνει με το shrink database και μάλιστα ισχύει ακόμα και αν δεν έχει blob fields. Για να το δεις κάνε το εξής:

1.     Φτιάξε μια νέα βάση - CREATE DATABASE test

2.     Σε αυτή φτιάξε ένα πίνακα που απλά να μπαίνει ένα record / page - CREATE TABLE T1 ( f1 INT IDENTITY, f2 CHAR(8000) )

3.     Βάλε και ένα clustered index στο f1 - CREATE CLUSTERED INDEX CI_T1_F1 ON T1 (f1)

4.     Γέμισε τον με δεδομένα ώστε να γίνει 20ΜΒ - INSERT INTO T1(f2) VALUES('APOSTOLOS APOSTOLOS APOSTOLOS') GO 2560

5.     Φτιάξε ακριβώς ένα ίδιο πίνακα Τ2 χωρίς όμως να φτιάξεις σε αυτόν το clustered index και γέμισε τον όπως και τον προηγούμενο

6.     Εκτέλεσε την sp_helpdb test και θα δεις ότι έχεις μια βάση που θα είναι μαζί με το log περίπου 43ΜΒ.

7.     Εκτέλεσε την sp_spaceused πάνω σε αυτή την βάση και δες το πόσοστο του unallocated space, σημείωσε το.

8.     Σβήσε τον πίνακα Τ2 - DROP TABLE T2

9.     Εκτέλεσε ξανά την sp_spaceused πάνω σε αυτή την βάση και δες το πόσοστο του unallocated space, θα δεις ότι έχεις πλέον ελεύθερα τα 20ΜΒ που ήταν δεσμευμένα από τον Τ2.

10.  Εκτέλεσε την εντολή SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('T1'),1,NULL,NULL) η οποία θα σου δείξει το ποσοστό που ο clustered index είναι fragmented και θα πρέπει να δεις ότι είναι κάτω από 1%

11.  Τώρα κάνε shink database DBCC SHRINKDATABASE(test)

12.  Και ξαναεκτέλεσε την SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('T1'),1,NULL,NULL), θα δεις ότι υπάρχει μια διαφορά αλλά είναι μικρή και βέβαια είναι πάλι κάτω από 1% σωστά;

Επιβεβαίωσε μας ότι έτσι όπως τα λέω σε παρακαλώ για να συνεχίσω παρακάτω

Σε ευχαριστώ