July 19, 2010 at 10:45 am
Running 2005 32 bit, Standard Edition on a VM with single proc 2.33 GHz with 4 GB installed memory.
Generally when there a bunch of SSIS jobs running the CPU will be high and stick at 100% for short periods (5 seconds maybe 10 seconds).
What we are seeing is 100% CPU for 10 -15 minutes. I can shut down all jobs and still have the high CPU so this does not appear to be related to SSIS.
When running perfmon on all processes, SQL Server and the System process are the 2 highest CPU.
I've been monitoring the threads and trying to tie them back to SQL PID's to locate the offending process. Also the doing this with the system process using PViewer and pstat to locate the system module. Also I've noticed that system and SQL both drop off at the same time. There appears to be a correlation.
looking for some ideas since I'm still having trouble locating the issue.
Thanks
July 19, 2010 at 11:08 am
Hi Tom,
Check whether SQL Server is experiencing
1. Excessive compilations and Recompilations
2. Parallel Queries (Usually Parallel queries are characyterized by 100% CPU time.
3. Poor Cursor usage with small Fetch Buffer Size.
Thank You,
Best Regards,
SQL Buddy
July 19, 2010 at 11:32 am
Check this link for the actual queries
http://technet.microsoft.com/en-us/library/cc966540.aspx
Thank You,
SQLBuddy
July 19, 2010 at 12:27 pm
High CPU utilization is typically due to missing indexes. When the CPU is pegged at 100%, run PerfMon to track the heavy hitters. Analyze those with high CPU and high Reads. You will likely see a table/clustered index scan and will need to determine if an index can be added to support the query. If an index is already in place to support the query, but the plan shows that another index is being used, you will need to investigate whether or not it's a bad plan. You try recompiling it to see if you can get a good plan. If you can't, you may want to consider adding an index hint. Only do that if testing shows that the index hint performs better.
Also check out the missing indexes report: http://sqlserverpedia.com/wiki/Find_Missing_Indexes
Only review those over perhaps 1000000 impact and ensure that you aren't adding duplicate indexes or near duplicates.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 21, 2010 at 7:55 pm
Thanks for the replies. We just had another occurrence and this time we were able to shut down the sql server and agent. Also no ssis jobs were running.
The system process was consuming between 90-100% of the cpu for an additional 5 minutes. I have ruled out SSIS or SQL. Unfortunately I've not been able to determine what under the system process is causing the problem.
I'd take any ideas on that although I know it's out of the scope of the forum.
thanks
July 21, 2010 at 8:31 pm
Are the CPUs Hyper Threaded?
First off I'd almost never run SQL Server 2005 on a single processor core, which is what I assume you are doing. Your per-processor licence (which is what I assume you have) is per physical socket, if this is a multi-core processor (say Quad core) you are using 1/4 of what you have paid for. If you also have hyper threading enabled you are running on less than 1/8. Note that a Hyper Thread CPU is NOT equal to a full CPU, in fact probably not better than about 60% of a full CPU.
Since SQL is optimised to multi-thread and can do lots of switching of processes in and out of the CPU, if you have hyper threading enabled, this is also doing a lot of switching processes in and out of the CPU, and you are effectivly doing switching on top of switching, which is not very efficient.
I'd recommend you add a second CPU at least, more if you are using hyper threading. Consider setting Max Degree Of Parallelism to 1 to remove the risk of a bad parallel query plan grabbing all the CPUs, or consider testing this to see what the effect is.
By the way I'd probably consider the CPU at 100% for 5-10 second periods to be a potential problem already.
Cheers.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 4, 2010 at 1:38 pm
thanks for the info Leo,
we are running a single Intel Zeon 2.33 GHz on a 32 bit vm with 4 GB RAM
Server 2003 R2 Standard SP2
SQL 2005 SP3 32 bit
Primary purpose of this server is to run SSIS but there are a couple databases that don't get a lot of use.
I've been running the SQLH2 collector for a couple weeks now and I've attached a couple graphs.
The CPU graphs are not the best since there are too many data points but you can get an idea of the averages. The committed bytes is interesting. I can post other graphs if needed.
We also have an SR with Microsoft, but have not gotten anywhere with them yet.
August 5, 2010 at 8:19 am
Have you tried ProcessHacker? http://sourceforge.net/projects/processhacker/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2010 at 10:09 am
Installed process Hacker. Not sure what to focus on. this morning the server was CPU bound for about 5-10 minutes. this appears to coincide with a sharp drop in page file usage. see attached.
Generally the server will be running around 100% then once the page file drop occurs the CPU is pegged tight at 100% for at least 5 minutes. Then everything comes back to normal.
August 6, 2010 at 9:02 pm
Try running SQL server performance dashboard and find out which query are taking CPU. Also you have mentioned one CPU but how many cores.
August 12, 2010 at 5:25 pm
The problem here has nothing to do with SQL Server this is an SSIS issue of memory leak. The high CPU is still a mystery but we think it has something to do with page file cleanup.
The issue was discovered by moving an SSIS job to another server, this was a job I was suspicious of and turned out there is some type of memory leak. I've posted this to the SSIS 2005 forum.
Thanks for all the input
http://www.sqlservercentral.com/Forums/Topic968614-148-1.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply