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 开发者_StackOverflowmore 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?

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.

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.

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.

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.)

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.

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.