Daily job runs slow after upgrade to 2005.

We have a process that uses XMLSpy to import an xml file of products/pricing
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/pricing
> daily. We receive this file through an ftp site. This process usually ran
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>> That's not much to go on Tim. I don't know what this XMLSpy does but even
>> 15 minutes is way to long a time to import just a few thousand rows. I
>> would think 15 seconds would be too long. Can you give some more details
>> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
>> --
>> Andrew J. Kelly SQL MVP
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so
>> different?
>> The database used is set to compatibility level 90, simple recovery
>> mode, ansi defaults. We've tried turning off create and udpate of
>> statistics as this is temporary data used for updating other tables.
>> Nothing seems to make a difference. I just bet there's something simple
>> I'm missing here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>>
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com