🚀 go-pugleaf

"Avner" <avnera@cvdo.com> wrote in message
news:tSjac.16658$xb.714@newssvr27.news.prodigy.com...
> I need some information to understand what is the impact of Database size
on
> the performemce.
>
<CUT>
> 1. Is there any impact?

In queries that make use of the data, yes.

> 2. Does one very large table impact the performence of the the whole DB,
> meaning the overall performence and access the other tables.

Essentially no.

> 3. What are the ways to reduce the impact of the DB size  on the DB
> performence

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:
> I need some information to understand what is the impact of Database
> size on the performemce. 
> 
> Few questions :
> 
> 1. Is there any impact?

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.

> 2. Does one very large table impact the performence of the the whole DB,
> meaning the overall performence and access the other tables.

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

> 3. What are the ways to reduce the impact of the DB size  on the DB
> performence

Good indexing is of course essential.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

> 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'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
as possible.

[posted and mailed]

louis nguyen (louisducnguyen@hotmail.com) writes:
> 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
> as possible.

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
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Wow.  Thanks a bunch, Erland!