Two procesor problem

[Frenkie]

Hi all,

I have two processor system with instaled SQL server. Sometimes this processors works on 100% somethimes on 1% on the same task. Why? Have anybody good advise how to powering this system?

Also I have DB Tabe with 1.6 milion records. Is normal that Update takes about 20min?

thanks,
Frenkie

 

[tlbroadbent]

Let me answer the 2nd question first. It is difficult to know how long it should take to update 1.6 million records without knowing the table schema, the update method, the load on the server, etc. However, 20 minutes seems like a long time to update 1.6 million records.

Processor usage can vary greatly, even while a single application is running. SQL will use both processors if you have configured it to do so. Load will be balanced between the processors. Usage spikes are to be expected. The more important measure is the average processor usage unless the spikes last a long time.

Other factors probably have more impact on performance than the processors. Speed, type and amount of disk space and the amount of RAM are key hardware factors in performance.

The most important aspect of performance tuning is making sure the applications are optimized. Applications are optimized by proper table design, creation of proper indexes on the tables and good query design.

Read the following topics in SQL Books Online.

Optimizing Database Performance Overview
Monitoring Server Performance and Activity

Check the following web links for more performance tips.

http://www.swynk.com/friends/mcgehee/developers_tuning_tutorial.asp

http://www.sql-server-performance.com/articles.asp

Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[foxdev]

I'm guessing that the primary bottleneck for your Updates is not the CPUs but the disk I/O system, though as Terry said this is difficult to know without more details of your Update statement and schema.

Frenkie, you didn't say if you were updating one record, a group of records, or all records.

Terry mentioned that CPU usage will be balanced. Quite true, but CPU 0 will likely spend a lot of its time on OS-related tasks, with CPU 1 being almost exclusively devoted to SQL Server. That is why you might sometimes see CPU 0 working fairly hard, but CPU 1 idling - there may be some non-SQL Server tasks being performed. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[Frenkie]

Thaks for tips tlbroadbent, foxdev.

Here is more info about this problem. I updating one field in whole table with NULL value. I have also index for that field, but sometime this operation do it after 10min, 5 min, sometime for 20 min. I understand that this operation is depended on many factors, but seems to me is problem with setup procesors and app tunning. I think that because when I start this process and nobody is on the network and procesors performance is 0%, performance go up to 2%. Also memory si 512MB, with swap file is 1.5GB. I think the resources are not at the end. Everything looks OK, but this take too long. Whole database is aprox. 800MB big.

anyway
thanks for tips.
Frenkie

 

[foxdev]

I updating one field in whole table with NULL value

Do you mean that you are setting the column's value to NULL, or you are replacing the column's value where the current value is NULL?

If the latter, be aware that a WHERE clause that includes something like [TT]where mycolumn is NULL[/tt] will use a table scan, not an index (that beam of light thanks to other posters in this forum with the link to

http://www.sql-server-performance.com/quiz_start.asp

) Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[Frenkie]

I setting this column's value to null

 

[tlbroadbent]

Frenkie,

Having an index on the field you are updating will actually degrade performance because the index must be updated also. If you are updating all records in the table, remove all indexes, run the update and then restore the indexes except the index on the column you make null as it will do absolutely no good.

If you have further problems post the query along with your comments. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin