Strange Issue- SQL Server 1

[happyIndian100]

Let me explain this strange issue first:

In SQL Server 2000 we use DTS to insert data into a TableA. it worked fine for couple of months but recently the DTS was failing. when checked found out that it is taking forever to insert the data into TableA.

for Temporary solution i do this:
Drop the tableA and recreate the tableA. The DTS works fine.

but i want a solution which can be permanent to this problem?

Any help is greatly appretiated.


Thanks

 

[bborissov]

And the error is?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[happyIndian100]

I dont get any error, The DTS runs forever, never finishes the process.

 

[jbenson001]

drop and create the table as you described inside of your DTS package

 

[happyIndian100]

Thanks Jbenson, that will fix my problem.

but any one knows the reason why this happens?

 

[ptheriault]

Have you been just appending rows to your table or Truncating then inserting? How large was the table getting? Do you have any indexes or a Primary Key?

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Also,
I would run a trace to see if it really is hung or just taking a very long time.

- Paul
- Database performance looks fine, it must be the Network!

 

[happyIndian100]

It happens once in every six months.

The tableA is truncated first, then insert the records(recCount:875000) into tableA. After that when i delete some records with the delete statement below:

Delete From TableA
Where IsNumeric(Price1) = 0
Or ISNumeric(Price2) = 0
Or ISNumeric(Price3) = 0
Or ISNumeric(Price4) = 0
Or ISNumeric(Price5) = 0
Or ISNumeric(Price6) = 0
Or ISNumeric(Location1) = 0
Or ISNumeric(Location2) = 0
Or ISNumeric(Location3) = 0
Or ISNumeric(Location4) = 0
Or ISNumeric(Location5) = 0
Or ISNumeric(Location6) = 0

the table is hung and the DTS package never ends.


The DTS package is scheduled for every 30 minutes and The DTS package takes 4 minutes to execute the process successfully.

Hope this info helps.

 

[SQLBill]

What else is happening at that time? Could you be doing reindexing or something else at that time and it be taking up lots of your resources/blocking the DTS?

-SQLBill

Posting advice: FAQ481-4875

 

[ptheriault]

I agree with Bill, Run sp_who2 from query analyzer. Look for blocking SPIDs.
Also, if you have a PK on that table run DBCC SHOWCONTIG. All the inserting and deleting data will seriously frament that table wich will kill performance. It would get worse slowly over time.
What does the SQL trace show?

- Paul
- Database performance looks fine, it must be the Network!

 

[happyIndian100]

Thanks for Info.

It helps me to investigate the problem further when it happens again. I will keep you posted what i find.


Thanks

 

[happyIndian100]

When i run the DBCC SHOWCONFIG (tableA) i get the below results and paul you are right the extent scan Fragmentation has a value which will increase slowley. What action should i take now? there is not PK on the tableA. pls advice.

DBCC SHOWCONTIG scanning 'tableA' table...
Table: 'tableA' (1446400322); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 16831
- Extents Scanned..............................: 2108
- Extent Switches..............................: 2107
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.81% [2104:2108]
- Extent Scan Fragmentation ...................: 0.85%
- Avg. Bytes Free per Page.....................: 400.3
- Avg. Page Density (full).....................: 95.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[ptheriault]

Currently your table is not fragmented. As time goes by you will se the Extent Scan Fragmentation percent increase. As that number increases performance will decrease. However, because this is a heap table, (no PK) your options are limited. You can not Reindex a heap table. To correct fragmentation on heaps you have 3 options.

1 create a PK. That will reorganize the data. However, If you are adding 875000 rows every 30 min it will have a negative impact on the performance of your DTS job.

2. Create the PK to reorg the data then drop it.

3 select the data into a temp table, truncate it, and insert the data back in the table with an order by clause.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

happy,
Is this table truncated every 30 min? If so, don't worry about fragmentation.

- Paul
- Database performance looks fine, it must be the Network!

 

[happyIndian100]

Yes paul this table is Truncated every 30 mins. This table is like a Temporary physical table(tableA) where we pump the data into it ,clean the data and then transfer the data into another original production table.

But the problem is with the tableA.

according to your sugesstion if this problem happens again i shall run the trace and post the details here.

-
thanks
happy

 

[ptheriault]

Yes run a trace and run sp_who2 to see if your DTS jobs is being blocked. The trace will show if it is actually moving data or if it truly stuck.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Running Profiler while running the DTS package will also help you identify exactly what part of the queries are running that get stuck.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"