SQL Server speed and index

[jepatte]

I have to run an update on 2.5 mil records and it takes forever even when I have an index built. We have a server with 4GB memory and we allocate SQL Server to use 1-2 GB.

When I run an update like this, its bizarre, it actually slows down the other databases.

ie. I have another completely different database on this server with 50 tables. One table is a static table that has an index on it with 200,000 records. When I run this update on a seperate database, its almost like it removes this index in my static database table (they are not related at all). The index is visibly there in Enterprise Manager, but its slow. So I recreate this index and now it runs fast again. But this happens when I do a mass update in a seperate database.

Can indexes just go away on a static table? DO I have to recreate indexes like this once a day?

 

[ptheriault]

When I run an update like this, its bizarre, it actually slows down the other databases

That is normal. Your databases are sharing CPU, IO and DISK.

Can indexes just go away on a static table?

Not unless you drop the index.

You might want to update stats after your update.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jepatte]

What does "update stats" mean?

Also, if I have a table with 2.5 mil records and I'm trying to update a field in this table based on the value in another table, it shouldnt take 2-3 hours should it? If I have an index on the fields I'm joining? I'm actually joining on two fields, so I have two indexes on each table.

 

[ptheriault]

Is your update being blocked by another user?

When your update is running run sp_who2.

Find your SPID that is the update statement and check to see if it's being blocked. Also, check the status. Is it being suspended for some other reason?

statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving data and performing INSERT, SELECT, DELETE or UPDATE queries.
When you perform a large update your stats will be out of sync. which can impact performance. If you run update stats on the table you updated you are updating the stats to be used by the query optimizer which will help with performance.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

We have a server with 4GB memory and we allocate SQL Server to use 1-2 GB.

What version of SQL server are you running?
What is your max server memory setting?
Are you running anything else on this server?
How many CPU's?

These are all factors as to why your update is slow.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

Rememeber indexes can slow down updates not speed them up because they have to be adjusted as the data in the index changes. This is why it is sometimes faster to drop the index beforehand and then recreate it. Try it both ways to be sure. If you are doing this, it is best to schedule it to be done when the users are not using the the system (or at least the time when the least users are usually on if you are running 24/7).

Also SQL Server will perform better if you allow it to use all the server memory as it is designed to do. If you are running any other applications on this server, you need to get them onto a server of their own.

You may want to consider running in batches if you are updating many records. Sometimes this speeds up the process as well.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

You may find some advice in this thread helpful too.

thread183-1402843

-George

"the screen with the little boxes in the window." - Moron

 

[jepatte]

This is SQL 2000 and we have IIS running on the same box, but its definitely SQL server causing the slowness. We allocate 2 GB max for SQL. One other thought, my index should only be based on the fields in my where clause right?

ie. UPDATE t1 SET f1 = 'Y' WHERE f2 > 100

I dont need an index on f1 in this case do I, just f2?

 

[ptheriault]

>>I dont need an index on f1 in this case do I, just f2?

No, as SQLSister pointed out. Indexes on the table being updated can slow down the update. This is because the index must be updated too. She had some very good advice for you about speeding up your update. I would try them.

By the way, You should not install IIS on a SQL Server.

IS this SQL Server 2000 standard or Enterprise?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jepatte]

ok, I've cleared out all the indexes in my table except the primary indentity seed index.

I now want to update one field based on another.

UPDATE t1 SET f1 = 'Y' WHERE f2 LIKE 'E%'

I have an index created now for f2 (and the primary index above). 2.5 mil records.

* When I look at hte props of the database, Size is 7.5 GB and Space Available is 0. Is that a sign of something? Do I ned to make more space available? If so, how?

 

[SQLSister]

Look in your database properties and you will see if the database is set to grow and by how much. Indeed needing to grow can slow things down significantly.

"NOTHING is more important in a database than integrity." ESquared