high CPU usage

Question

Post reply

high CPU usage

chidev

Mr or Mrs. 500

Points: 529
More actions
September 11, 2009 at 8:50 am

#209854

I'm running a process that does a lot of bulk inserts to process flat files. Bulk inserts have high CPU usage on SQL Profiler. At the same time, another process (stored proc) is selecting millions of records and inserting to a temp table to do calculations. This causes the server to hang. Also, replication is set up on this server (as a publisher). Any suggestions on how to improve the performance? The processes are runnning on a VM server (Intel Xeon CPU E7440 @2.40GHz, 7.87 GB of RAM, 256 GB HD on Windows Server 2003 x64 SP2). Will adding more CPUs or RAM help?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 1

give us more information about procs ? how many core you have assigned to this vm ? adding cores seems to be good idea.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

chidev (9/11/2009)
At the same time, another process (stored proc) is selecting millions of records and inserting to a temp table to do calculations.

Post the code and let's take a look. It would also be helpful if you posted the CREATE TABLE code for the tables. Be sure to include all constraints, indexes, triggers, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 3

The other question that stands out is: what type of replication is it exactly, and are any of the 'hot' tables replicated?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chidev Mr or Mrs. 500 Points: 529 More actions · Answer 4

I'm new at the job and the network admin claims that it is a quad core 8GHz. On the same box they have both x64 bit and x32 bit instances of SQL server installed. The replication they have set up is transactional.

I ran SQL profiler (server side) and

EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name takes 125 CPU and 1723 duration

sys.sp_replication_agent_checkup @heartbeat_interval = 10 takes 125 CPU and 1174 duration

EXEC dbo.sp_MShistory_cleanup @history_retention = 48 takes 359 CPU and 1528 duration

I have attached the stored procs that updates the data and the other one pulls the data for computation. I also included the structure of one of the tables used. A lot of the tables are indexed on the identity field. I have already added non-clustered indexes on some of the tables. Some tables contain as many as 50 mil rows.

The system was running ok up until 2 weeks ago when the performance slowed down.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

chidev (9/15/2009)
I'm new at the job and the network admin claims that it is a quad core 8GHz. On the same box they have both x64 bit and x32 bit instances of SQL server installed. The replication they have set up is transactional.
I ran SQL profiler (server side) and
EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name takes 125 CPU and 1723 duration
sys.sp_replication_agent_checkup @heartbeat_interval = 10 takes 125 CPU and 1174 duration
EXEC dbo.sp_MShistory_cleanup @history_retention = 48 takes 359 CPU and 1528 duration
I have attached the stored procs that updates the data and the other one pulls the data for computation. I also included the structure of one of the tables used. A lot of the tables are indexed on the identity field. I have already added non-clustered indexes on some of the tables. Some tables contain as many as 50 mil rows.
The system was running ok up until 2 weeks ago when the performance slowed down.

The times you have posted appear to be for replication, not the spocs you've posted. And, the replication times seem fairly normal and performant. At this point, I'm not sure what the question is. What are the run times for the spocs you've posted and what were they before when the performance slowed down?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chidev Mr or Mrs. 500 Points: 529 More actions · Answer 6

It normally takes 5-6 mins for the update stored proc I posted. It took around 45 mins to complete until I started archiving records from a 50 mil record table and shrinking it to 20 mil records. The performance was better. I have been cleaning up the database (deleting 'old' rows) every weekend just to free up some space. I can only save 1 week of full backup and 2 days of differential before it runs out of space.

Could it be IO that is causing the performance slow down -- I see a lot of PAGELATCHIO_sh. Could it be indexing? I am not sure where to begin to find out how to fix the performance issue.

Thanks for your help.

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 7

Dear;

I think heavily to follow the next steps to clarify well Hung problem then fix its causes well and then you can solve it easily:

1-No fixed memory planned for maximum in the properties of SQL server Engine ...under category of memory as by default you have windows 64 bit can get maximum memory up to 8TB if available.

2- Please monitor memory granted to your SQL engine through Granted Workspace Memory under alerts..

If increase over 3 GB by consistent way...then it is recommended heavily to increase memory to 16GB.

3-Make sure that Replication has high affect on performance while Bulk insert data is processing so you should optimize replication design by the highest performance way.

4- Monitor CPU usage by any way and if you have SQL Management studio 2008 , it will be so better to monitor CPU Parameter and also other parameters and make sure that it is <= 70% almost the time.

and if not ..then you should think to increase Processor speed.

5-From other side , if the applications connect to SQL server are .NET , please note that these applications raise CPU utilization Rapidly due to W3WP.EXE process attached to IIS service. opposite to Share point applications...So then you must increase CPU speed.

have nice day.

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

chidev (9/18/2009)
It normally takes 5-6 mins for the update stored proc I posted. It took around 45 mins to complete until I started archiving records from a 50 mil record table and shrinking it to 20 mil records. The performance was better. I have been cleaning up the database (deleting 'old' rows) every weekend just to free up some space. I can only save 1 week of full backup and 2 days of differential before it runs out of space.
Could it be IO that is causing the performance slow down -- I see a lot of PAGELATCHIO_sh. Could it be indexing? I am not sure where to begin to find out how to fix the performance issue.
Thanks for your help.

It sounds more like someone actually dropped an index somewhere. Either that, or they maybe added a trigger?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)