For a DTS package; Copy SQL Server Objects Task

Source, pick a database on server1
Destination, pick tempdb on server1
Copy (with these selections):
Create destination object
Drop destination objects first
Include all dependant objects
NOT Checked, Include extended properties
Copy data (with Replace existing selected)
Use Collation
NOT Checked Copy all objects (Select Objects: 1 table selected only)
Use Default options

The one table selected has 52,000 rows.

Rightclick the Task and run...
10:54am starts, hourglass going on window
11:08am SQL Server 2000 window whites out (like screen is not updating)
11:20am the process completes successfully

Am I wrong to expect 52000 rows to copy in MUCH less than 26 minutes?
I have tried this twice, and have had the same results each time.

What could possibly cause this? (on the same PC I can browse the source
table and click through it page at a time and it builds very quickly)

How would I debug the process to see where all the extra time is being
taken-up?

--
Regards,
John

The copy obecjts task I believe writes everything out to text file first
then uses that. Is it normal to take that long? Only you can answer that.
Are you seeing contention for resources?

If you do not like this option then do a BACKUP/RESTORE.

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"John Keith" <JohnKeith (AT) discussions (DOT) microsoft.com> wrote

This is the first time I have tried to use the Copy SQL Server Objects task,
So I dont have any way to know if this would be normal. This was going to be
just 1 small step in a import-from-mainframe package that will update a
customers table. I am able to read the entire table using Excel-VBA and
write this out to disk in just a minute or two. So I would think that the
task is way out of line.

What do I do to check for resource contention? (I am still quite new to the
SQL Server world)

Perhaps the extra checking being done for integrity are slowing things down?

--
Regards,
John


"Allan Mitchell" wrote:

The tasks first will loop through the things it needs to script out, script
them, unload the data all to text file. It will then do the same in reverse
to get it back in again.

This is not what you are doing in Excel. In Excel you are only looking at
the table and the data. If that is all you actually want to do then this is
not the right task for you. You need to be looking at a data pump task.

Have a look at this

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)


also in SQL Server there is a tool called Profiler which will show you what
SQL Server is doing or rather what is being done against it


--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"John Keith" <JohnKeith (AT) discussions (DOT) microsoft.com> wrote

I booked-marked your site, there is a lot of good info there, thanks!

I have the Datapump working now. It's taking about 25seconds per 1000
records. That comes to about 20min to complete the copy. Since the package
I want to set up wil run at night... the time it takes is not quite as
critical. But I guess (coming from a mainframe background) I would think
that this could be done faster.

The Datapump ran through 50000 records then got a primary key constraint
error. (I think it was trying to append to the table that was created from
the object copy task) I guess since they take about the same time, that
maybe using the object copy is better since it has a built in option to
overwrite the data. Are there other pros/cons that I should consider?



--
Regards,
John


"Allan Mitchell" wrote:

25 seconds per 1000 rows is awful speed. What is causing the pushback? To
where are you pumping?


--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"John Keith" <JohnKeith (AT) discussions (DOT) microsoft.com> wrote

Checking into SQL Profiler now to see if I can find out what is going on.

--
Regards,
John


"Allan Mitchell" wrote:

I tried the Object copy this time to Trace...
I'm getting a loop with these results (in groups of 3 lines at a time...that
last line is quite long so it is shown with wrapping here)

SELECT N'Testing Connection...'

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

insert bulk [dbo].[Customers]("CustomerID" varchar(12) COLLATE
SQL_Latin1_General_CP1_CI_AS,"SiteID" varchar(8) COLLATE
SQL_Latin1_General_CP1_CI_AS,"BarCode" varchar(12) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Status" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"LastName" varchar(25) COLLATE
SQL_Latin1_General_CP1_CI_AS,"FirstName" varchar(30) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Mid" varchar(1) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Address1" varchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Address2" varchar(35) COLLATE
SQL_Latin1_General_CP1_CI_AS,"City" varchar(30) COLLATE
SQL_Latin1_General_CP1_CI_AS,"State" varchar(2) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Zip" varchar(11) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Phone" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Ssn" varchar(11) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Race" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"ApplicationID" varchar(12) COLLATE
SQL_Latin1_General_CP1_CI_AS,"AppendDate" datetime,"Posdis" varchar(12)
COLLATE SQL_Latin1_General_CP1_CI_AS,"Grade" varchar(2) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Home" varchar(10) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Buscod" varchar(16) COLLATE
SQL_Latin1_General_CP1_CI_AS,"VerificationStatus" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Brkml" int,"Lunml" int,"Cash"
money,"NextMealStatus" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Posuseb" datetime,"Posusel" datetime,"TrackID"
varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS,"RecordCode" varchar(15)
COLLATE SQL_Latin1_General_CP1_CI_AS,"Brkmlun" int,"Lunmlun" int,"Brkmlpr"
money,"Lunmlpr" money,"Windate" datetime,"AttendanceStatus" varchar(15)
COLLATE SQL_Latin1_General_CP1_CI_AS,"StatusType" varchar(25) COLLATE
SQL_Latin1_General_CP1_CI_AS,"DCSiblingID" varchar(12) COLLATE
SQL_Latin1_General_CP1_CI_AS,"StatusEffectiveDate"
datetime,"StatusExpirationDate" datetime,"Guardian" varchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS,"BirthDate" datetime,"Notes" varchar(255)
COLLATE SQL_Latin1_General_CP1_CI_AS,"PreviousSiteID" varchar(8) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Gender" varchar(7) COLLATE
SQL_Latin1_General_CP1_CI_AS,"AltSiteID" varchar(8) COLLATE
SQL_Latin1_General_CP1_CI_AS,"HomeMeals" varchar(20) COLLATE
SQL_Latin1_General_CP1_CI_AS,"AltMeals" varchar(20) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Sup" varchar(30) COLLATE
SQL_Latin1_General_CP1_CI_AS,"LCTS" datetime,"IsACCashOk"
bit,"InstantWinnerCode" varchar(1) COLLATE
SQL_Latin1_General_CP1_CI_AS,"UserString1" varchar(30) COLLATE
SQL_Latin1_General_CP1_CI_AS,"UserString2" varchar(30) COLLATE
SQL_Latin1_General_CP1_CI_AS,"UserCurrency1" money,"UserCurrency2"
money,"UserDt1" datetime,"UserDt2" datetime,"CCMonthlyAuthAmount"
money,"CCDefaultAmt" money,"CCPIN" varchar(10) COLLATE
SQL_Latin1_General_CP1_CI_AS,"CCNum" smallint,"CCExpirationDate" varchar(6)
COLLATE SQL_Latin1_General_CP1_CI_AS,"CCName" varchar(50) COLLATE
SQL_Latin1_General_CP1_CI_AS,"CCType" varchar(2) COLLATE
SQL_Latin1_General_CP1_CI_AS,"CCLastUsedTS" datetime,"CreditLimit"
money,"Lang" varchar(15) COLLATE
SQL_Latin1_General_CP1_CI_AS,"CustomerSIFGUID" varchar(40) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Email" varchar(128) COLLATE
SQL_Latin1_General_CP1_CI_AS,"DietaryRestriction" varchar(20) COLLATE
SQL_Latin1_General_CP1_CI_AS,"Posuseo" datetime,"CanRelease" bit,"FamilyCode"
varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS,"SessionMealsBrk"
int,"SessionMealsLun" int,"SessionMealsOth" int,"CaseNumber" varchar(15)
COLLATE SQL_Latin1_General_CP1_CI_AS)

The last of the 3 lines is where the duration ticks are by far the largest.
1st line is Duration 0
2nd line is Duration 25-35ish
3rd line is Duration 28000-32000ish

This is what is showing up while the DTS Object copy is running.

--
Regards,
John

To answer your "where" question...

The Data pump (and the object copy) are copying from and to databases on the
same server. I.E. in the instance of SQL server, I see ...
master; model; msdb; northwind; pubs; tempdb; winsnap;...
I am copying from winsnap.customers to tempdb.customers

--
Regards,
John


"Allan Mitchell" wrote:

That is where the insert is happening. Are you seeing anything like page
splitting, the database growing, disk queuing, memory pressure, what is the
recovery model of the DB

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"John Keith" <JohnKeith (AT) discussions (DOT) microsoft.com> wrote