[quoted text, click to view]

I've run into 2 problems w/ very large DB.
A) Backups. I have a 50G DB. I backup to file then delete
yesterday's backup file (I have another process which transfers the
backup file to tape). So I have a 50G backup file and I need another
50G freespace, to run my backup process.

B) Reindexing. On any given day maybe 5% (max) of new data is added
to my DB. But DBCC reindex has to work on all the indexes. So DBCC
reindex takes 3 hours a night. Yuck.

Maybe there is a better way. But I try to keep each database as small

I need some information to understand what is the impact of Database size on
the performemce.


Few questions :

1. Is there any impact?
2. Does one very large table impact the performence of the the whole DB,
meaning the overall performence and access the other tables.
3. What are the ways to reduce the impact of the DB size on the DB
performence


I will apreciate help in regards .


Thanks Avner

[quoted text, click to view]

In queries that make use of the data, yes.

[quoted text, click to view]

Essentially no.

[quoted text, click to view]

DB size is so low on the radar of performance issues, that I think it is
basically worth ignoring. If your database is so big that it fills the drive
it is on, that could slow the whole system down as the page file has to get
chunked up. But for all reasonable scenereos, you are much better off
focusing on reducing the number of calls that are required to get work done,
and improving the efficiency of your functions and procedures.

Cheers!

Avner (avnera@cvdo.com) writes:
[quoted text, click to view]

Maybe. Maybe not. Depends whether you query the large table, and not the
least how you query them, and what indexes you have. As a matter of fact,
for the same amount of information, a database A twice as big as database
B, could give better performance for retrieving a certain subset of
that information, because in A data has been stored redudantly, maybe
preaggregated.

For instance, say that you have a transactions table for bank accounts,
and you need to be able to retrieve the balance on a certain date. Saving
the standings for each day is going to take up a lot of space, but
it will be much faster to retrieve the historic balance, than adding
up all transactions.

[quoted text, click to view]

If the table just sits there, no. However, it will have an impact on
backup operations.

[quoted text, click to view]

Good indexing is of course essential.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[posted and mailed]

louis nguyen (louisducnguyen@hotmail.com) writes:
[quoted text, click to view]

Here is a script that I got from SQL Server MVP Andrew Kelly, which examines
which tables that are in actually in need of fragmentation. This could
reduce the reindex time a bit.



-- Gather Fragmentation Info

SET NOCOUNT ON
USE PRESENTS

DECLARE @TableName VARCHAR(100)

-- Create a table to hold the results of DBCC SHOWCONTIG
IF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULL
DROP TABLE #Contig

CREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT, [IndexName]
VARCHAR(200),
[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,
[MinimumRecordSize] INT,
[MaximumRecordSize] INT , [AverageRecordSize] INT,
[ForwardedRecords] INT ,
[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes] NUMERIC(6,2)
,
[AveragePageDensity] NUMERIC(6,2), [ScanDensity] NUMERIC(6,2) ,
[BestCount] INT ,
[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,
[ExtentFragmentation] NUMERIC(6,2) )


DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'

OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT FROM curTables INTO @TableName
END

CLOSE curTables
DEALLOCATE curTables


-- Get a list of all the tables where the Logical Fragmentation of at least
-- one index is over 30% and is not a statistic, a heap or Text / Image.
DECLARE curFrag CURSOR STATIC LOCAL
FOR
SELECT [ObjectName]
FROM #Contig
WHERE LogicalFragmentation >= 30 AND IndexID BETWEEN 1 AND 254
AND INDEXPROPERTY (ObjectId, IndexName, 'IsStatistics') = 0
GROUP BY [ObjectName]


OPEN curFrag
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)

WHILE @@FETCH_STATUS = 0
BEGIN

DBCC DBREINDEX(@TableName)

FETCH NEXT FROM curFrag INTO @TableName
END

CLOSE curFrag
DEALLOCATE curFrag



select * from #Contig



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Hi Erland,

I was able to get the script running. It was a lot of work in sql 7.0
as the SHOWCONTIG doesn't support table results and it outputs as a
print message. I was able to get around it using OSQL and
xp_cmdshell. Let me ask you another question. What is your opinion
of INDEXDEFRAG on sql 2000? BOL says it doesn't require an exclusive
lock as DBREINDEX. But is there any drawback in substituting