performance degradation after several inserts

We have a VB6 app that uses an MSDE SP3 database. We use a process with
transactions to add data to the database. As several similar packets of data
are added we've noticed a degradation in the speed of loading the
information. We've also noticed that if at the end of the insert of several
packets of data we get a 30 second upload time per packet, then shut down
MSDE, start it up again and then insert in an almost identical packet of
data, it will run twice to 3 times faster.
We tried turning autoshrink and auclose off but that didn't seem to help.
Thanks,
Terry
hi Terry,
tgrignon wrote:
> We have a VB6 app that uses an MSDE SP3 database. We use a process
> with transactions to add data to the database. As several similar
> packets of data are added we've noticed a degradation in the speed of
> loading the information. We've also noticed that if at the end of
> the insert of several packets of data we get a 30 second upload time
> per packet, then shut down MSDE, start it up again and then insert in
> an almost identical packet of data, it will run twice to 3 times
> faster.
> We tried turning autoshrink and auclose off but that didn't seem to
> help.
it can depend on the transaction... if you load a big transaction, you will
see a degradation as the transaction size increses... you can perhaps
separate the load into separated transactions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the reply Andrea!
The loads are separated into different transactions. If I use a monitor I
can see that memory use continues to grow with each load so it seems like
there is a memory leak somewhere.
We are planning to try:
DBCC FREEPROCCACHE
&
DBCC DROPCLEANBUFFERS
in between loads with our next build. Do you have any other suggestions?
multo grazie,
Terry
"Andrea Montanari" wrote:

> hi Terry,
> it can depend on the transaction... if you load a big transaction, you will
> see a degradation as the transaction size increses... you can perhaps
> separate the load into separated transactions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
|||hi Terry,
tgrignon wrote:
> Thanks for the reply Andrea!
> The loads are separated into different transactions. If I use a
> monitor I can see that memory use continues to grow with each load so
> it seems like there is a memory leak somewhere.
not a memory leak (or at least...) ... just the standard SQL Server
behavior... plese see http://tinyurl.com/bvpg9 for some details on SQL
Server memory management...

> We are planning to try:
> DBCC FREEPROCCACHE
> &
> DBCC DROPCLEANBUFFERS
> in between loads with our next build. Do you have any other
> suggestions?
do not know I I like that
those to DBCC commands will actually clear the procedure cache... that
usually includes penalty executing statements due to recompilations of
stored procedures, plans, more I/O activity due to refetching of database
pages...
and more, a nasty security requirements for them... they require high
privileges...
http://msdn.microsoft.com/library/de..._dbcc_7q5l.asp
http://msdn.microsoft.com/library/de..._dbcc_512h.asp
that's to say sysadmins and serveradmins membership...
if I could eventually not speed up operations via separating the job into
multiple transactions, I'd think I'd stay with the time degradation...

> multo grazie,
prego, you are welcome
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I see what you mean about the permissions... but we tried
DBCC FREEPROCCACHE
in between loads and it works perfectly... the separate transaction loads
are back down to a manageable load time. I wonder if a stored procedure is
being left "on" somewhere in the code...
Any other ideas?
Thanks,
Terry
"Andrea Montanari" wrote:

> hi Terry,
> tgrignon wrote:
> not a memory leak (or at least...) ... just the standard SQL Server
> behavior... plese see http://tinyurl.com/bvpg9 for some details on SQL
> Server memory management...
>
> do not know I I like that
> those to DBCC commands will actually clear the procedure cache... that
> usually includes penalty executing statements due to recompilations of
> stored procedures, plans, more I/O activity due to refetching of database
> pages...
> and more, a nasty security requirements for them... they require high
> privileges...
> http://msdn.microsoft.com/library/de..._dbcc_7q5l.asp
> http://msdn.microsoft.com/library/de..._dbcc_512h.asp
> that's to say sysadmins and serveradmins membership...
> if I could eventually not speed up operations via separating the job into
> multiple transactions, I'd think I'd stay with the time degradation...
> prego, you are welcome
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
tgrignon wrote:
> I see what you mean about the permissions... but we tried
> DBCC FREEPROCCACHE
> in between loads and it works perfectly... the separate transaction
> loads are back down to a manageable load time. I wonder if a stored
> procedure is being left "on" somewhere in the code...
no idea... you have to control

> Any other ideas?
nope..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Performance decline in parallel SPs execution

Hi,

We have a process that builds our data warehouse.

The processes execute SPs in serial order.

Each SP builds separate table.

Each table is build destructive (truncate and Insert Into).

I've tried to change the configuration by running 4 SP in parallel by SSIS to shorten the update time.

I've noticed in two declines in performance:

1. Each SP execution time is higher in the parallel execution in around 50% then in the serial execution. The CPU utilization is the same.

2. On each parallel execution we have a decline in performance of around 5 -10% compare to the previous parallel execution.

Do you have any directions to inquire?

Btw – we have Itanium 64bit x8 with 32GB memory

Thanks,

Assaf

It sounds like the stored procedures may be blocking each other when running in parallel.

You can verify if there is blocking by querying sys.dm_exec_requests and looking at the blocking_session_id column.

If you do see blocking you will want to look at the locks that stored procedure is taking, and determine if there is any way to reduce the locks held.

|||

Hi,

Sorry that I forgot mention it.

There is no locks between tables.

each SP right to only single table and Other SP do not read from other written tables.

Also validated it on server level.

Any other ideas?

Thanks,

Assaf

|||

If the stored procedures are inserting into tables in the same database then they could be contending for both physical IO on the data device and also be writing to the same log devices. The storage devices are quite often the bottlenecks in systems like this, and regardless of how fast the processors can run the disk can only write at a certain rate.

As for the 10% degredation I'm not so sure. Is the 10% cumulative run on run or are the subsequent run all just 10% faster than the first? What is the recovery model of your database? If it is not Simple then it may be having to extend the logs if they are not being cleared between runs. On a data warehouse which is completely rebuilt and does not then have data written to it you can probably just use Simple recovery and do a full backup after the load if necessary.

Performance counter : PhysicalDisk: % Disk Time

We have a DataWarehouse process that, at times, causes this Perfmon counter to exceed > 1800 (for several minutes at a time. Not good).

Question: How can that counter exceed 100 * (number of spindles in array)? (I suspect that numbers in that range are meaningless except to point out a disk bottleneck.)

TIA,

barkingdog

P.S. We use sql 2005, x64 edition, with SP1.

http://www.demandtech.com/FAQsdisk.htm#DiskFAQ1

Frequently Asked Questions about Disk
1. The Logical/Physical Disk % Disk Time counters look wrong. What gives?

http://support.microsoft.com/kb/269481

not about:: We use sql 2005, x64 edition, with SP1.

Performance / Design Question

Hi all,
Ok - I'm building a database around the content of an XML file I've been
supplied with - the process is straight forward enough...
The supplier has provided in the XML file far more data/information than I
actually need at this point in time, however, there's nothing to say that
"some" of this extra information might not be needed in the future...
I have therefore been adding columns like a mad man to incorporate the data
from the beginning, so if requested later on the impact on the system, and
the turn-around will be quicker.
I then wondered "wonder if this will have any side effects?"
Lets say, to make things simple I have a table with 5 columns that I do
need, I have a file with 40 items that could potentially be columns in this
table, I decide to add these now. I run my import process and populate my
table. Lets say the database size would have been 10Mb with the 5 columns,
but now, because of the types of data, and the sizes, its not 350Mb - would
this have any impact on SQL Server's performance when queries are run
against it? Obviously the sizes above are just "figures plucked from the
air" and are not actual sizes of my database, I was just trying to show a
big markup.
I would "guess" that if I wasn't using the other columns in the queries then
it shouldn't make any difference...
Any information would be appreciated - thanks in advance,
Regards
RobRob Meade wrote:
> Hi all,
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the dat
a
> from the beginning, so if requested later on the impact on the system, and
> the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in thi
s
> table, I decide to add these now. I run my import process and populate my
> table. Lets say the database size would have been 10Mb with the 5 columns
,
> but now, because of the types of data, and the sizes, its not 350Mb - woul
d
> this have any impact on SQL Server's performance when queries are run
> against it? Obviously the sizes above are just "figures plucked from the
> air" and are not actual sizes of my database, I was just trying to show a
> big markup.
> I would "guess" that if I wasn't using the other columns in the queries th
en
> it shouldn't make any difference...
> Any information would be appreciated - thanks in advance,
> Regards
> Rob
>
Proper indexing and normalization will affect your table performance
more than the number of columns will. I hope you're not just building a
table that mimics the structure of the XML file. For instance, consider
an XML file that contains a list of customers, a billing address for
each customer, and for some customers, an optional shipping address.
Resist the temptation to build a single table that contains two sets of
address fields - one for billing, one for shipping. Instead, build a
"Customers" table that contains identifying information for the
CUSTOMER, such as customer ID, name, account number, etc.. Build an
"Addresses" table that contains information for each ADDRESS, such as an
address ID, street address, city, state, country, etc.. Build a third
table, "CustomerAddresses", that contains maybe four columns; customer
address ID, customer ID, address ID, and address type. This table
creates a 1-to-many link between a customer and its address(es). Even
the address type could be split off into an AddressTypes table,
containing an AddressType ID and a text label describing the AddressType
(billing, shipping, etc).|||"Tracy McKibben" wrote...

> Proper indexing and normalization will affect your table performance more
> than the number of columns will.
okey dokey

> I hope you're not just building a table that mimics the structure of the
> XML file.
Nope...one element "sku" has a number of attributes, these are all in the
same table, however, each sku can also have a number of "objectives",
"topics", "members" and so on...these are currently in separate tables.
I have used the SKUID to link between these tables and the sku table.
It was really more of a question about whether or not the addition (possibly
excessive - as some of it might not be used) data would have an adverse
effect.
Thanks for your reply Tracy.
Regards
Rob|||Rob Meade (ten.bewdoowsgnikNO-SPAM@.edaem.bbor) writes:
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the
> data from the beginning, so if requested later on the impact on the
> system, and the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in
> this table, I decide to add these now. I run my import process and
> populate my table. Lets say the database size would have been 10Mb with
> the 5 columns, but now, because of the types of data, and the sizes, its
> not 350Mb - would this have any impact on SQL Server's performance when
> queries are run against it? Obviously the sizes above are just "figures
> plucked from the air" and are not actual sizes of my database, I was
> just trying to show a big markup.
The answer is, as it often does when it comes to performance: it depends.
However, here the "it depends" has a fairly straight-forward qualification.
If you have good indexing, the cost of the extra column is moderate.
The database will grow faster, and you must pay more attention to running
backup of the database and the transaction. It will also be more important
to keep fragmentation in check. But the overall cost for performance
does not have to be more than, say, 10%.
On the other hand, if you are lax about indexing, the cost for the
extra columns can be significant (assuming that they are in the same
tables as the rest). Since the rows are wider, there are fewer rows
per page, and more pages have to be read to scan tables.
Personally, I would consider the strategy of only adding the columns
you need, but add a table to save the XML documents in, so if need
occurs later, you can recover more fields from the XML document. This
is particularly attractive, if you are on SQL 2005 where you can store
the data in column of the xml data type. The performance cost for this
extra table is nil, but of course the increased database size takes
some administrative toll as discussed above.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Rob Meade wrote:
> Nope...one element "sku" has a number of attributes, these are all in the
> same table, however, each sku can also have a number of "objectives",
> "topics", "members" and so on...these are currently in separate tables.
> I have used the SKUID to link between these tables and the sku table.
> It was really more of a question about whether or not the addition (possib
ly
> excessive - as some of it might not be used) data would have an adverse
> effect.
Sounds like you're in good shape then. Make sure you have good indexes,
including covering indexes if appropriate, and you should be fine
performance-wise.

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!
Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com
|||Hi
It depends on application, implimentation and hardware, but http://www.tpc.org
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:

> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com|||Hi
It depends on application, implimentation and hardware, but http://www.tpc.org
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:
> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!

Performance - transactions/second

Does anyone know how many transactions can SQL Server process per second?
Any suggestions on SQL Server performance testing tools?
Thanks!Teresa wrote:
> Does anyone know how many transactions can SQL Server process per
> second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!
A lot on a 8-way server and much less on a single CPU with 512MB RAM. Of
course, this assumes all code is running efficiently and the drive
subsystems are designed properly, etc.
If you have specific requirements, I would encourage you to speak with
your hardware vendor and have them recommend a solution that fits your
needs. Compaq used to have a SQL Server Sizer that could give you
hardware specs based on performance criteria, but I don't know if HP
still has the tool on the web site.
You can also visit TPC.org and see the performance results there, but I
would encourage you to speak with someone who can custom design a
solution for you.
David Gugick
Imceda Software
www.imceda.com|||Hi
It depends on application, implimentation and hardware, but [url]http://www.tpc.org[/ur
l]
786,646 transactions per minute.
Regards
Mike
"Teresa" wrote:

> Does anyone know how many transactions can SQL Server process per second?
> Any suggestions on SQL Server performance testing tools?
> Thanks!

Performance - Slow load times for SSIS Packages

Is there any information around what the SSIS packages are doing in the first 5-10 seconds of execution, and ways to speed this process up?Validation then Pre-Execute are the first things to happen as I recall. You can see all the events being raised prior to the Execute event itself in the BIDS Progress or Output windows. You can also capture lots if information including this through logging. DTEXEC can also dump all this information to the console, so easily captured when not running interactively, just not through BIDS, thereby cutting out the debugging overhead.|||It would be nice to know how to get rid of the delay when loading packages, so that tasks would fire instantaneously. Every package, no matter how simple, seems to have a 5-10 second delay.|||

Make sure the SSIS service is running - it caches information about packages that have run, making subsequent package invocations go faster.

Cheers/Kristian