CODEWITHSUNDEEP
c#sql-serverfluent-nhibernateperformance
leora
leora

Reputation: 196539

Why would SQL be very slow when doing updates?

Suddenly doing updates into a few tables have gotten 10 times slower than they used to be. What are some good recommendations to determine root cause and optimization? Could it be that indexing certain columns are causing updates to be slow? Any other recommendations?

I guess more important than guesses would be help on the process of identifying the root cause or metrics around performance.

Is there anything in Fluent NHibernate that you can use to help identify the root cause of performance issues?

Upvotes: 0

Views: 910

Answers (6)

Yves M.
Yves M.

Reputation: 3318

Causes...

  • Too many indexes

  • Growing number of records

  • Bad index covarage of the where-clause of the update query

  • Locking issues due to multiple updates (table-lock)

  • Hardware issues (disk controller, network, etc.)

Upvotes: 1

TalentTuner
TalentTuner

Reputation: 17556

1- First check if the table does not have locks

2- Recently implemented triggers

3- Database refresh may need index rebuilt.

4- Some predefind jobs is in process.

5- ANY UNKNOWN REASONS

Use some query analzer tools to see what is actually happening.

Upvotes: 1

David Crowell
David Crowell

Reputation: 3813

Also check if you're inserting data into a clustered index in an out-of-order way.

A clustered index stored the data in order of the field, which means if it's a uniqueidentifer, you'll be re-arranging the data on each insert.

Clustered indexes work better with sequential data.

Upvotes: 0

Paddy
Paddy

Reputation: 33867

And as ever, running up SQL profiler on your SQL server will give you a good idea of exactly at what point your bottleneck is.

Upvotes: 1

Robb
Robb

Reputation: 3851

Its worth checking your growth settings on the data files, if you're doing a lot of inserts which forces regular file grows it can manifest as slowdown.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

First try updating your statistics.

Then, look into your indexing, and make sure you have only what you need. Additional indexes can most definitely slow down inserts.

Then, try rebuilding the indexes.

Without knowing the schema, query, or amount of data, it is hard to say more than that.

Upvotes: 3

Related Questions