SQL Server Performance Forum – Threads Archive

CPU Usage is 90%

Hi Folks, We are inserting approx 10 million records in a table.This table has 4 coloumns.
The speed of insertion is 50 records per second.Its more than 24 hours and only 2 million records inserted ,Further the CPU utilization is going 90%.
It is a dual core 8 cpu box.4 GB RAM,awe enabled.
I have increased the max deg of parallelism from 2 to 4 ,so the CPU usage is now approx 84%. Is this a normal case or there is something wrong.
What are the other things I can cross check and configure,so that the insertion become fast. One more thing I observed that the avg disk queue length is pretty high. Any ideas?

---

You can use the maxdop function to decrease the CPU utilization.

---

It depends how other processes/queries are querying against the database causing the spike. http://support.microsoft.com/kb/917905
http://www.microsoft.com/technet/pr…cle/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk
http://support.microsoft.com/kb/224587 http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx – help with indexed views.
The above links should get you more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Hi Satya, THanks for the above links. Its a staging server and no other application is currently querring th database.

---

Then you can take full help of the tools that are specified on the above links, try to configure the design for optimum performance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Did you check the Average current disk queue length couter?
In four column, is any one text by any chance? Is your database has enough allocated space? so that it won’t expand during the insert operation.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Are you doing the insert in one big transaction? its better to do it in smaller batches..and also have a job running to truncate the log every minute.
***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

No None of the columns is of text datatype,
I am using print @str in the code,does this print effect the performance in any way.The averege disk queue length is high

---

In most cases we have broken data ,to insert in batches and also make sure if you can spread them across multiple disks in case you estimate the table would be huge. Cheers
sat

---

Have you tried or tested by removing them?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

I stop the query and started again ..now its inserting at the rate of 150 records per second.. Going to another server today ,similar situation arised with delete
Now only a 3 record is been deleted per second..damn slow..
here is the script I am using ,can sm one look what is wrong … ———————————————
CREATE PROCEDURE [dbo].[CalScore_Queue]
AS
DECLARE @uniqueid uniqueIdentifier,
@Iduniqueidentifier,
@AIDint,
@MessageIDuniqueidentifier,
@CreationDatedatetime,
@Score1bigint,
@Score2bigint,
@Score3bigint ,
@userName varchar(400),
@UserIduniqueidentifier
set nocount on update test.dbo.[TBLScoreQueue_deleted]
set IsUpdated=1
where AID<>3 DECLARE cur_ScoreQueue CURSOR for
select Id,AID,MessageID,CreationDate,
Score1,Score2,Score3
from test.dbo.[TBLScoreQueue_deleted] where IsUpdated=1 and AID<>3 Open cur_ScoreQueue
Fetch next from cur_ScoreQueue into @Id,@AID,@MessageID,@CreationDate,@Score1,@Score2,@Score3
while @@FETCH_STATUS = 0
BEGIN
SELECT @UserId=UserId FROM Test.dbo.[Temp_25042007] WHERE [email protected]
SELECT @userName=userName FROM Authentication.dbo.[user] WHERE [email protected] EXEC [CalScore] @uniqueid,@UserId,@userName,@Id,@Score1,@Score3,@Score2 delete from test.dbo.[TBLScoreQueue_deleted]
where IsUpdated=1 and [email protected] and [email protected] and [email protected]
and AID<>3 Fetch next from cur_ScoreQueue into @Id,@AID,@MessageID,@CreationDate,@Score1,@Score2,@Score3
END
Close cur_ScoreQueue
Deallocate cur_ScoreQueue

---

Forgot to mention, TBLScoreQueue_deleted table has 5 million records

---

And yes this script just execute fine on another server with same configuration.

---

]]>