October 11, 2012 at 8:07 am
Hi all,
I have one job which takes some time to execute, about 2 hours.
During the job, CPU usage goes high and that slows website because regular queries on page are executing slower.
It is not important for this job to run as fast as possible. It can run in background and take more time.
More important is to have web pages load fast all time, including while this job is running.
Do you know which options I have to decrease job's CPU usage and save server resources for common queries which come from front end pages?
For now, I have these ideas:
- Adding WAITFOR DELAY on some places and on that way break job in smaller chunks
- Using MAXDOP to limit number of processor used by job's queries
- Maybe break data in smaller pieces and update them one by one
Anyone had problem like this before? 🙂
Thanks!
October 11, 2012 at 8:39 am
If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2012 at 8:47 am
Sean Lange (10/11/2012)
If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.
Hi Sean,
thanks for response.
Time is not important in this case. Even if it runs only for a 10 minutes, I don't want websites go down for 10 minutes.
The first priority is to have website working fast.
Regards
October 11, 2012 at 8:56 am
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.Hi Sean,
thanks for response.
Time is not important in this case. Even if it runs only for a 10 minutes, I don't want websites go down for 10 minutes.
The first priority is to have website working fast.
Regards
I understand what you are saying but if the process can be made to be less resource intensive it may help the site. There is little anybody can do to help without some details about the job.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2012 at 9:15 am
Sean Lange (10/11/2012)
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.Hi Sean,
thanks for response.
Time is not important in this case. Even if it runs only for a 10 minutes, I don't want websites go down for 10 minutes.
The first priority is to have website working fast.
Regards
I understand what you are saying but if the process can be made to be less resource intensive it may help the site. There is little anybody can do to help without some details about the job.
I see. So, there is no some way to make job run as lower priority?
The job is (very simplified) doing this:
- Load about 200 records to cursor.
- Using loop to examine each record.
- For each record, calls 4 procedures.
These 4 procedures are very similar. In short, they are:
- Creating one table variable
- Calculating what should be inserted to table variable and insert records. This is pretty complex SELECT
- Copying data from table variable to real table on SQL Server
This INSERT could contain a lot of rows, for some cases about 500 000 records per INSERT
October 11, 2012 at 9:42 am
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.Hi Sean,
thanks for response.
Time is not important in this case. Even if it runs only for a 10 minutes, I don't want websites go down for 10 minutes.
The first priority is to have website working fast.
Regards
I understand what you are saying but if the process can be made to be less resource intensive it may help the site. There is little anybody can do to help without some details about the job.
I see. So, there is no some way to make job run as lower priority?
The job is (very simplified) doing this:
- Load about 200 records to cursor.
- Using loop to examine each record.
- For each record, calls 4 procedures.
These 4 procedures are very similar. In short, they are:
- Creating one table variable
- Calculating what should be inserted to table variable and insert records. This is pretty complex SELECT
- Copying data from table variable to real table on SQL Server
This INSERT could contain a lot of rows, for some cases about 500 000 records per INSERT
There may be some way to make it run at a lower priority but I don't know how to do that. Well there is a way to use OS priority commands but it is a deprecated feature.
The first step is a solid clue to me that the entire process can be made a ton faster. Cursors are horrible for performance. It then sounds like you use a loop to examine each column?
In your case you are processing 200 rows and it takes 2 hours. That screams of a serious need of performance tuning. I bet you can make this entire job run in seconds instead of hours.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2012 at 9:54 am
Sean Lange (10/11/2012)
In your case you are processing 200 rows and it takes 2 hours. That screams of a serious need of performance tuning. I bet you can make this entire job run in seconds instead of hours.
Maybe I didn't explain well.
That 200 rows are just to load client IDs from one table. That should not take any resources.
Then, in the loop, for every found ID, 4 stored procedures are called.
These procedures do real work.
Each of them opens large tables (about 75 millions rows), and then copy selected rows to other tables. This SELECT takes some time, and can't be used directly on front end pages.
So, that SELECT and after INSERT is what takes time and takes CPU, not CURSOR.
Also, I noticed that INSERT of these 500 000 rows takes more time than SELECTing of rows, so maybe that is the clue.
CURSOR operates with only 200 rows. That probably could be faster, but on very small record set, it is all the same.
October 11, 2012 at 10:04 am
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
In your case you are processing 200 rows and it takes 2 hours. That screams of a serious need of performance tuning. I bet you can make this entire job run in seconds instead of hours.
Maybe I didn't explain well.
That 200 rows are just to load client IDs from one table. That should not take any resources.
Then, in the loop, for every found ID, 4 stored procedures are called.
These procedures do real work.
Each of them opens large tables (about 75 millions rows), and then copy selected rows to other tables. This SELECT takes some time, and can't be used directly on front end pages.
So, that SELECT and after INSERT is what takes time and takes CPU, not CURSOR.
Also, I noticed that INSERT of these 500 000 rows takes more time than SELECTing of rows, so maybe that is the clue.
CURSOR operates with only 200 rows. That probably could be faster, but on very small record set, it is all the same.
OK. I still think you could make that run a lot faster. 75M rows is quite a bit when doing it over and over. If you can make your process set based it would probably go a long way to making it faster.
I seem to have steered you off topic. Are you familiar with resource governor? http://msdn.microsoft.com/en-us/library/bb895232.aspx It may be just the thing you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2012 at 10:16 am
Sean Lange (10/11/2012)
I seem to have steered you off topic.
No, I really appreciate because you spent your time to think about my problem. Thank you.
Sean Lange (10/11/2012)
Are you familiar with resource governor? http://msdn.microsoft.com/en-us/library/bb895232.aspx It may be just the thing you are looking for.
I am familiar with resource governor, but unfortunately it doesn't work on SQL Server 2005. I think it is introduced with 2008.
Hmm, in that direction..., maybe using of MAXDOP (1) on SELECT and INSERT would help. I will try that next. Server has 4 processors and would be good if I can save 3 of them for front end pages. 🙂
October 11, 2012 at 10:33 am
Boris Pazin (10/11/2012)
Sean Lange (10/11/2012)
I seem to have steered you off topic.
No, I really appreciate because you spent your time to think about my problem. Thank you.
Sean Lange (10/11/2012)
Are you familiar with resource governor? http://msdn.microsoft.com/en-us/library/bb895232.aspx It may be just the thing you are looking for.I am familiar with resource governor, but unfortunately it doesn't work on SQL Server 2005. I think it is introduced with 2008.
Hmm, in that direction..., maybe using of MAXDOP (1) on SELECT and INSERT would help. I will try that next. Server has 4 processors and would be good if I can save 3 of them for front end pages. 🙂
You're welcome. The MAXDOP might help. It sounds like the slow points are the selects from the big tables so I don't know how gain would be found using WAITFOR, although adding those when you can should allow your front end a "window" to get the data it needs. This one is going to take quite a bit of tweaking and testing to make it functional.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply