SSIS Perf Tuning - Tables Of 15M+ Rows

Feb 28, 2008

The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.

I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.

I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?

I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

The design of the data flow at the moment is:


..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4

any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?


*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

View 7 Replies


ADVERTISEMENT

Perf Tuning Question - Gurus Only

Feb 19, 2004

Howdy

I have a server that has the following average readings :

No. CPUs = 1
% CPU = 2
SystemCPU Queue Length = 2 to 4
SQL Server:Buffer ManagerBuffer Cache = 99.85%
RAM in the box = 1 GB
MemoryPages/sec = 1 to 5
SQL memory in use ( using Task Manager ) = 250 MB
Max worker threads = 255
Average number of connection = 60

So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.

As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?

Any thoughts much appreciated.

Cheers,

SG.

View 13 Replies View Related

Loading Data Warehouse (Perf. Tuning)

Feb 25, 2008



Hi all,

I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:

1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.

OR

2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.

The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?

View 7 Replies View Related

How Will I Know The Rows Being Saved By SSIS Package Into Tables

Jul 17, 2007

Hi Guys,



Yet another question again on the issues with SSIS. I have a package now which is working fine.

The package consists of a control flow and i have 2 DF tasks which are unionall first and then saved into a sql server destination.

It's fine up to this point but i've just been notified that i would need to generate 2 files based on different values after i combined the data from 2 sql server DF tasks.

My question is how can i know the rows which are being saved on this sql server destination.

I have a primary key which is an autoincrement column.

Thank you

Gemma

View 45 Replies View Related

Performance Issues And Tuning (Rows Per Batch, MICS, Etc.)

Mar 6, 2008

I built an SSIS package to pull a large amount data (over 4 million records) from our legacy system into a SQL server for analysis. As it stands the package takes somethign like 30+ hours to run. I would really like to trim down the time it is taking. The process runs monthly.

We're connecting to a cache database via an ODBC connection and dumping to an OLEDB connection to the SQL server. Is there something I can change in the rows per batch, maximum commit size to improve performance. It looks like the ODBC connection returns chunks of about 3000 rows at a time.

Is anyone familiar with cache that might know where I can get the best performing ODBC driver from?

What about the table that I'm dumping the data to? I assume no (or very few) indexes is the way to go for speed of dropping the data into the table.

I don't feel that 30 hours is an acceptable amount of time for this process to run. Am I off base here?

Any tips would be appreciated.

View 5 Replies View Related

Query Tuning And Stored Procedure Tuning

Nov 22, 2000

Hi

Is there any good books for Query Tuning and Stored procedure Tuning

Thanks

View 1 Replies View Related

Ssis Package Design To Load Only Rows Which Are Changed From Exisiting Rows.

Aug 17, 2007

Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.

example of Data would be like

exisiting data

StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL


New row to insert would be

12 DDS 15 M DFG ST 4/5/07

the data should reflect

StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07

12 DDS 15 M DFG ST 4/5/07 NULL

14 hgS 17 M ABC ST 3/4/07 NULL

Please provide your input as much as you can even though it might not be a 100% solution.






View 4 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

Perf Mon

Mar 15, 1999

On an installation of sql 6.5, when I go to performance monitor, there are no sql counters. What gives? How can this be fixed,and do I have to reinstall,(wipeout data) to do it?

View 2 Replies View Related

SQL Perf Mon

Nov 9, 1998

The User Defined Counters Object has disappeared from me Performance Monitor.
Any ideas why or how to get it back? Thanks, Mike

View 1 Replies View Related

Perf Monitor

Feb 24, 2004

Is there anyway that you can automate performance monitor from the cmd line. I.e feed it some parameters like server and counters and receive a log file at the end.

Cheers

View 5 Replies View Related

SQL Network Perf Issues

Mar 19, 2003

I have a SQL2000 db running a vendor custom app on a Win2000 sp3 server. The front-end is run on Citrix to the client. Can't change the app or the somewhat poor infrastructure of client. users starting to complain about slowness ( we can shadow them and this seems the case). On the SQL box all the standard perf counters look good (buffer cache at 100%, queue length always less than 1, memory and disk look good. The server currently has 2 nics doing load balancing. The network counter bytes/sec averages over 100,000. The network counter output queue length shows an average of 4,294,967,251 (That seems high but in reading on another sql perf site this counter doesn't always work). Any other ideas where I might look. Do these numbers look high?
Thanks

View 1 Replies View Related

Perf Report Query

Sep 22, 2005

CREATE TABLE [Perf] ([TransId] INTEGER,[FileNo] VARCHAR(80),[TimeInSeconds] INTEGER,[FileSizeMB] INTEGER,[FileName] VARCHAR(255),[StartDate] datetime)Ok!! Here's the Problem. I am working on a perf stats report. FileType is First 9 chars of the field FileName. I need to compare a similar filename from this month to last month or before ordered by file size. Maybe this is a very simple query but currrently my mind refuses to work. Seeking F1.

View 3 Replies View Related

MSDE And Missing Perf Counters

May 15, 2002

The SQL performance couters do not get installed with MSDE. Does anyone know how to install them??

View 2 Replies View Related

Sql 2000 Perf Monitoring Books

Mar 23, 2004

Any recommendations from DBA's on good books for performance monitoring for sql 2000

View 2 Replies View Related

High Value For The PagesSec Perf Counter

Feb 26, 2008

Hello All,

I have a 64-bit SQL Server running on 64-bit OS having 12GB of RAM. The Server only hosts our Application database (its a 30 GB database). There are no major process running on it. Despite of this, the Avg Pagessec shows a count of 1500 and
the Avg Page Faultsec shows 22000. Is there any specific reason as to why this is happening? The %idle time of the disk is 87%. Also there is no major load on the server. Do let me know if you need any other input.

Thanks in Advance,
Mitesh

View 2 Replies View Related

Perf On Read Uncommitted Isolation Level

Jul 5, 2005

Are there really any benefit on using Read Uncommitted Isolation Level or having a NOLOCK hints for retrieve queries when the default Isolation level just Read Committed (not using COM+).  I'm confused why the Community Server uses this technique perhaps for perf issues but I couldn't see any reason why...

View 1 Replies View Related

2005 Perf Much Worse Than 2000... Suggestions Please..

Nov 20, 2006

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas??

Here is the procedure...

declare @srch_field varchar(8000)

declare @row int, @productid varchar(25)

DECLARE @title varchar(150), @actors_keyname varchar(1200), @directors_name varchar(400)

Declare @genres varchar(700), @theme varchar(1500), @type varchar(1500), @studio_desc varchar(100)

DECLARE @media_format varchar(50), @artist_name varchar(100), @dev_name varchar(100)

DECLARE @flags varchar(256), @starring varchar(256), @esrb varchar(100), @esrb_desc varchar(500)

DECLARE @ptrval varbinary(16), @text varchar(max)

declare @productlist table(product_id varchar(25), IDNUM int identity)

insert into @productlist (product_id)

select product_id

from music_load..globalsearch

select @row = @@rowcount

while @row > 0

begin

select @productid = product_id

from @productlist

where idnum = @row

SELECT @title = rtrim(title) ,

@actors_keyname = actors_keyname ,

@directors_name = directors_name,

@genres = genres ,

@theme = theme ,

@type = type ,

@studio_desc = studio_desc,

@media_format = media_format ,

@artist_name = artist_name,

@dev_name = dev_name,

@flags = flags ,

@starring =starring ,

@esrb = esrb ,

@esrb_desc = esrb_desc

FROM globalsearch

where product_id = @productid

Set @srch_field = isnull(@title,'')

if @actors_keyname is not null and @actors_keyname <> 'unknown'

Set @srch_field = @srch_field + ' ~ ' + rtrim(@actors_keyname)

if @directors_name is not null and @directors_name <> 'unknown'

Set @srch_field = @srch_field + ' ~ ' + rtrim(@directors_name)

if @genres is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@genres, 0,''))))

if @theme is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@theme, 0,''))))

if @type is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@type, 0,''))))

if @studio_desc is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@studio_desc)

if @media_format is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@media_format)

if @artist_name is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@artist_name)

if @dev_name is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@dev_name)

if @flags is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@flags)

if @starring is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@starring)

if @esrb is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb)

if @esrb_desc is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb_desc)

update globalsearch

set srch_field = @srch_field

where product_id = @productid

SELECT @ptrval = TEXTPTR(srch_field),

@text = credits

FROM globalsearch

where product_id = @productid

UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text

SELECT @ptrval = TEXTPTR(srch_field),

@text = track

FROM globalsearch

where product_id = @productid

UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text

set @row = @row - 1

end



View 5 Replies View Related

Webinar On High-perf Extracts From Oracle, DB2, Teradata, Etc.

Jul 12, 2006

This webcast from our partner, ETI, may be of interest to readers on the forum - we see questions quite often about high-performance interaction with other databases.

Donald

View 4 Replies View Related

2005 Developer - Perf Monitor Counter Check Failed Message

Nov 11, 2006

Trying to install 2005 Dev edition in xp pro, sp2.

Performance Monitor Counter Check Failed Error.
Had to stop the install twice at the point it verified what components were to be included as
the docs were not going to be installed. Resolved the issue.

Now it won't pass the system config test because the registry is not the way it wants it.
I look up the messages and the only solution is to hack the registry and risk my system.

This is nuts.

Isn't there some way to restart cleanly without hacking registry keys ?

Help.

View 3 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all



i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................



any ideas

View 6 Replies View Related

Deleting Rows From Many Tables

May 19, 2006

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

View 8 Replies View Related

Count Rows In All Tables In A Db

Mar 1, 2001

Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.

View 2 Replies View Related

Compare Each Rows In Two Tables?

Nov 16, 2011

I need to compare two tables in each row. the tables are as follows:-

Table a:

Code:
IDFirst_Name Last_name Birthdate
1Shradha Deshkmukh 1981-12-25 00:00:00
2Shradha Verma 1981-05-11 00:00:00
3Divya Dutta 1982-07-21 00:00:00
4Matthew Palmer 1983-12-28 00:00:00

table d:-

Code:
idfnlndob
1ShradhaTiwari1981-12-25 00:00:00
2DivyaDutta1983-07-21 00:00:00
3SulabhManesar1975-09-11 00:00:00
4MatthewPalmer1983-12-28 00:00:00
5SamuelMaxwell1984-05-22 00:00:00

I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.

similarly if First name matches and dob matches then @log is 'LN not match'.

And in case all three match it should show 'match'as log value.The query I use is a s follows:-

Code:
USE testing
GO
DECLARE @NR int
DECLARE @log varchar(200)
SELECT @NR = COUNT(*) FROM a
WHILE @NR>0

[code]...

the result I am getting is :-

Code:
fnlndob (No column name)
ShradhaTiwari1981-12-25 00:00:00match
ShradhaTiwari1981-12-25 00:00:00match
DivyaDutta1983-07-21 00:00:00match
MatthewPalmer1983-12-28 00:00:00match

I have tried using CASE but that doesnt work either.

View 3 Replies View Related

Return Tables With 0 Rows

Nov 20, 2006

Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server

Thanks

View 9 Replies View Related

Append Rows In 2 Tables

Oct 8, 2007

how to append rows of 2 tables. Each table consist only 1 row.

sample:
table1
f1 f2 f3

table2
g1 g2 g3


my expected result is:

f1 f2 f3 g1 g2 g3

Note: tables have no relation to each other...

thanks



RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 2 Replies View Related

Rows In Database Tables

Jan 14, 2008

This is a question that I have not had an opportunity to test. Was wanting to know if anyone in the SQl world knows the answer. In SQL 2K and 2005 your rolls are limited to 8060 bytes without using varchar(MAX). My question is do you have to specify varchar(max) before your roll can exceed 8060 or does SQL 2005 exceed without specifing varchar(Max). Also does it expand it across multiple pages automatically. Please assist if you can.


Thanks

View 2 Replies View Related

Total No Of Rows In All Tables

Feb 26, 2008

Hi,

Does anyone know how to calcuate total no of rows of all tables from database in single query?

Thanks in advance

--kneel

View 11 Replies View Related

Duplicate Rows But No Key On The Tables

Jul 23, 2005

Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther

View 1 Replies View Related

How To Count Rows From Two Different Tables

Feb 29, 2008

If I have the following tables:

ID Car
1 Mazda3
2 Miata

Color Car ID
Black 1
Silver 1
Black 2

Style Car ID
i 1
s 1
touring 1




What is the simplest query that will return a result set like:

Car # of Colors # of Styles
Mazda3 2 3
Miata 1 0

View 3 Replies View Related

How To Delete Rows In Tables...

Jan 25, 2007

I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database.

Im using SQL 2000

I have tried with the following:
Delete from fsalesinvoiceline
Join dsalesinvoiceheader on
Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and
Fsalesinvoiceline.company= dsalesinvoiceheader.company
Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™
Go
Delete from dsalesinvoiceheader
Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™
 
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN'
 
What am I doing wrong?
/Søren D. Jensen

View 25 Replies View Related

Insert Rows Into Four Tables At One Time

Nov 14, 2007

Hello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row  for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you, 

View 1 Replies View Related

Dealing With MS SQL Tables That Contain Duplicate Rows

Jan 30, 2008

Hello,
I have a question, i loaded 2 files into SQL and the files have some cells that have the same model number.
how can I merge the cells together that have the same model number and (if possible take the avarage of their cell called price) 
(and combine their other cell called stock)
and make it into one cell.
Any help would be very very apriciated. Thank you. 
i tryed this but it does not work
SELECT Model_number FROM Products
Join Where Model_number='3CM3C1670800B'
I have also Tryed this, IT SHOULD work but I have an error someWhere:
delete from Productsfrom part_number a join
(select part_number, max(part_number) from part_number group by part_number having count(*) > 1) b
on a.part_number = b.part_number and part_number < b.part_number

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved