Update performance

[MeganP]

HI all,
When we migrated SQL server database from one server to another (New Server is robust), Insert performance has been improved a lot, but Update performance has diminished a lot. I couldn't find any change in objects like Indexes, Keys. What could be the issue?

PS: DTS packages are exactly same
THanks!

 

[tlbroadbent]

Have you reviewed the execution plan of the Update queries to make sure they are using the indexes? Have the tables been reindexed since the move?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[MeganP]

How can I review the execution plan of DTS Package? I would really appreciate if You would throw some light on this.

 

[tlbroadbent]

Is the DTS package executing SQL update queries or stored procedures? You should be able to analyze queries and SPs in Query Analyzer.

You can also run a trace while the package runs and capture the duration, cpu time, reads, and writes of each statement. This can help you identify the longest running queries.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[MeganP]

DTS package executes SQL update query, but the source is TEXT file. In DTS it shows like this

UPDATE Supplier
SET SUPPLIER_LINE_1 =?,
SUPPLIER_LINE_2 =?, Lineage_Full =?, Lineage_Short =?
WHERE Supplier_number = ?

How can I use this query in Query Analyzer? What changes do I need to make to use it in Query Analyzer?

Thanks a lot!

 

[tlbroadbent]

You'll need to provide values in place of each "?"

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[MeganP]

I ran SQL profiler on both the servers and found out that INDEX has not been used on new server. HOw can I force SQL Server to use index for update process?

 

[tlbroadbent]

You should reindex the table on the new server and perhaps update statistics (SQL Books Online). Then analyze query performance. Is Supplier_number indexed and is it clustered? A clustered index should give best performance.

Another trick is to add a where criterion to the query that reads ColumnName = ColumnName where ColumnName is the name of the first column in the index definition. In your case, I assume the table isindexed on Supplier_number.

UPDATE Supplier 
SET SUPPLIER_LINE_1 =?, 
SUPPLIER_LINE_2 =?, Lineage_Full =?, Lineage_Short =? 
WHERE Supplier_number = ?
  [COLOR=green][b]And Supplier_number = Supplier_number[/b][/color]

If reindexing did not help then you can use an index hint though I don't recommend it. Index hints are described in SQL Books Online.

If none of these things help, you may need to alter existing indexes or add new indexes on the new server.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm