many inserts results in a massive reserved space for table

Hi,
I've got a real problem inserting approx 1,000,000 rows of data into
some SQL server 2000 tables. The data is being inserted 1 row at a
time using a T-sql cursor. When I view the table size using
sp_spaceused I get the following type of results:
reserved = 27021280 KB
data = 3376216 KB
Unused = 23642952
why is the reserved space sooooo much more than the actual amount of
data in the table?
The table cannot be fragmented as this is the first data which has
been inserted into it.
I cannot use a different insert method (like DTS) as logic needs to be
applied to the data before it is inserted.
Is there any way round this?
Shrinking the tables after the event is also not an option, as I would
run out of disk space way before all of the tables are populated.Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
Also, what indexes do you have on the table. This along with the data distribution of the data you
are inserting will determine the level of fragmentation you get when you perform your inserts. Did
you check fragmentation level using DBCC SHOWCONTIG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> Hi,
> I've got a real problem inserting approx 1,000,000 rows of data into
> some SQL server 2000 tables. The data is being inserted 1 row at a
> time using a T-sql cursor. When I view the table size using
> sp_spaceused I get the following type of results:
> reserved = 27021280 KB
> data = 3376216 KB
> Unused = 23642952
> why is the reserved space sooooo much more than the actual amount of
> data in the table?
> The table cannot be fragmented as this is the first data which has
> been inserted into it.
> I cannot use a different insert method (like DTS) as logic needs to be
> applied to the data before it is inserted.
> Is there any way round this?
> Shrinking the tables after the event is also not an option, as I would
> run out of disk space way before all of the tables are populated.|||Thanks for your advice, I am new to SQL server so had not heard of
these procedures before...
DBCC UPDATEUSAGE - has no effect.
When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
fragmented (at least I think that's what it's saying) - results:
- Pages Scanned........................: 429774
- Extents Scanned.......................: 429755
- Extent Switches.......................: 429754
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
- Extent Scan Fragmentation ...............: 98.13%
- Avg. Bytes Free per Page................: 7712.6
- Avg. Page Density (full)................: 4.71%
There are no indexes on the table at all (I am trying this routine on
a test database before running it elsewhere and I assumed that leaving
off the indexes would increase the rate of the inserts) - are you
suggesting that if I had the indexes on the table it would reduce the
fragmentation as the data is inserted?
you say "This along with the data distribution of the data you are
inserting", but how can I control where the data is physically written
to? (and therefore control the fragmentation)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
> Also, what indexes do you have on the table. This along with the data distribution of the data you
> are inserting will determine the level of fragmentation you get when you perform your inserts. Did
> you check fragmentation level using DBCC SHOWCONTIG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> > Hi,
> >
> > I've got a real problem inserting approx 1,000,000 rows of data into
> > some SQL server 2000 tables. The data is being inserted 1 row at a
> > time using a T-sql cursor. When I view the table size using
> > sp_spaceused I get the following type of results:
> >
> > reserved = 27021280 KB
> > data = 3376216 KB
> > Unused = 23642952
> >
> > why is the reserved space sooooo much more than the actual amount of
> > data in the table?
> > The table cannot be fragmented as this is the first data which has
> > been inserted into it.
> >
> > I cannot use a different insert method (like DTS) as logic needs to be
> > applied to the data before it is inserted.
> >
> > Is there any way round this?
> >
> > Shrinking the tables after the event is also not an option, as I would
> > run out of disk space way before all of the tables are populated.|||The pages seems indeed very empty, on average. To say anything more conclusive, we would need the
table layout and what indexes you have on the table. You say no indexes, but that means they you
didn't define a primary key (or unique constraint). This is not recommended! Sp_helpindex will list
the indexes you have on the table.
In general every table should have a clustered index. Which column(s) you define in the clustered
index is based on both the data distribution and your queries. It is likely that you will have less
"emptiness" in the pages with a proper clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410050059.7bbdb78f@.posting.google.com...
> Thanks for your advice, I am new to SQL server so had not heard of
> these procedures before...
> DBCC UPDATEUSAGE - has no effect.
> When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
> fragmented (at least I think that's what it's saying) - results:
> - Pages Scanned........................: 429774
> - Extents Scanned.......................: 429755
> - Extent Switches.......................: 429754
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
> - Extent Scan Fragmentation ...............: 98.13%
> - Avg. Bytes Free per Page................: 7712.6
> - Avg. Page Density (full)................: 4.71%
> There are no indexes on the table at all (I am trying this routine on
> a test database before running it elsewhere and I assumed that leaving
> off the indexes would increase the rate of the inserts) - are you
> suggesting that if I had the indexes on the table it would reduce the
> fragmentation as the data is inserted?
> you say "This along with the data distribution of the data you are
> inserting", but how can I control where the data is physically written
> to? (and therefore control the fragmentation)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
>> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
>> Also, what indexes do you have on the table. This along with the data distribution of the data
>> you
>> are inserting will determine the level of fragmentation you get when you perform your inserts.
>> Did
>> you check fragmentation level using DBCC SHOWCONTIG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
>> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
>> > Hi,
>> >
>> > I've got a real problem inserting approx 1,000,000 rows of data into
>> > some SQL server 2000 tables. The data is being inserted 1 row at a
>> > time using a T-sql cursor. When I view the table size using
>> > sp_spaceused I get the following type of results:
>> >
>> > reserved = 27021280 KB
>> > data = 3376216 KB
>> > Unused = 23642952
>> >
>> > why is the reserved space sooooo much more than the actual amount of
>> > data in the table?
>> > The table cannot be fragmented as this is the first data which has
>> > been inserted into it.
>> >
>> > I cannot use a different insert method (like DTS) as logic needs to be
>> > applied to the data before it is inserted.
>> >
>> > Is there any way round this?
>> >
>> > Shrinking the tables after the event is also not an option, as I would
>> > run out of disk space way before all of the tables are populated.|||Well, I don't understand at all!!
It was true that the tables did not have indexes or primary keys on -
this was simply because I was running a test and I thought it would
run more quickly - again my not understanding SQL Server properly (not
because I think it's a good idea in terms of design!).
After your latest message - here's what I did (sorry if this is
longwinded).
I loaded the indexes / PKs onto the relevent tables - although I
notice there are no clustered indexes on any of the 5 tables which I
am inserting data into - please bear in mind though that I have no
influence over the design of the database...
I re-ran my routine against THE SAME SET OF DATA - this time the data
loaded in a 100th of the time, and took up approx 350Mb per table!!
(prior to this the tables had 27Gb of reserved space, with maybe 3.5Gb
of data) - HOW CAN THIS BE SO DIFFERENT? (total size of database was
61Gb and now is 3Gb !!)
So, then I tried to run my same routine loading data into a different
database on a different server which already had the indexes on (same
data model). This server only had 12Gb of free space (the total data
in my first db took up 1.9Gb). Afetr a very short space of time the
database ran out of disk space after only a fraction of the data had
been inserted. Looking at SP_SPACEUSED, the reserved space was way out
of sync with the data figure as before.
I ran DBCC UPDATEUSAGE, truncated the 5 tables and re-ran my routine,
now the data is inserting happily and is taking up 353Mb per table
again!
I have no more servers to play with!!
Is the data "behaving properly" the 2nd time around simply because it
is the 2nd time the same routine has been run?
Or is the fact that each time I have run DBCC UPDATEUSAGE relevent?
Surely there must be a way of achieving the proper data figures during
the first time this routine is run (I clearly can't keep running the
same routines and running out of disk space when I come to run this
against production databases).
For reference - here is an example of one of the tables I am inserting
data into (results from sp_help) -
BookingPayment dbo user table 2004-08-24 11:48:44.210
BooRefNo int no 4 10 0 no (n/a) (n/a) NULL
PayID int no 4 10 0 no (n/a) (n/a) NULL
BpyDate datetime no 8 no (n/a) (n/a) NULL
BpyAmount money no 8 19 4 no (n/a) (n/a) NULL
BpyTzoName varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyDateUTC datetime no 8 yes (n/a) (n/a) NULL
BpyPayAmount money no 8 19 4 yes (n/a) (n/a) NULL
BpyExrRate float no 8 53 NULL yes (n/a) (n/a) NULL
WrkID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
UseID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedWkgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedUgrID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedProID varchar no 5
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedPrgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyInvStatus tinyint no 1 3 0 yes (n/a) (n/a) NULL
PaymentKey nonclustered, unique located on PRIMARY PayID, BooRefNo
PK___3__21 nonclustered, unique, primary key located on
PRIMARY BooRefNo, PayID, BpyDate
PRIMARY KEY (non-clustered) PK___3__21 (n/a) (n/a) (n/a) (n/a) BooRefNo,
PayID, BpyDate
there is a difference in 27Gb (!!!) in total space used between the
1st and 2nd times I insert data into this table...

Managing Connections for optimal performance question, switch from Oracle to SQL

I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.

To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.

At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.

Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.No! Definitely not the way to do it. Just use ADO.NET's connection pooling to manage it. Out of the box it's pretty efficient, but you can tweak it if necessary. There's no way you'll write anything that will be as efficient as what's already there.

You're right that a data reader hogs a connection.

And the decision is the same whether you use data adapters or readers.

Don|||How do I use ADO.Net's connection pooling? Is there an article or something that you know about that can teach me to manage this problem better.

Thanks again for your help.|||That connection pooling stuff is pretty cool. My systems class was based on Java so apparantly that type of behind the scenes work didn't take place to efficiently manage the connections. I didn't realize that when I was instanciating a connection it was already managing a pool of connections for me. slick stuff.|||So you figured out how to use it? Cool. Yeah, it definitely is an area where all of Microsoft's hard work is paying off.

Don