July 21, 2008 at 7:04 am
Hi,
I have a long running sql Statement.
It joins lots of data and writes the result into new table.
I studied the query plan and in my opinion there are indizes used where it's useful, but there are also full table scans and there is a hash join.
the cpu is on 100% when the statement is running. Is it normal that one statement grabs all power of the cpu? Are there obvious things I should check in the db configuration?
Thank you,
Tobias
July 21, 2008 at 8:07 am
It's not normal for a single procedure to take all the resources, no. But if the query is really poorly written or designed, it certainly could happen.
I'd make sure you have enough memory on the server. How much is that you ask? As much as you can possibly get would be my response. But it really depends on the needs of the system, the number of users, connections, amount of data, number of transactions, etc. Monitor the existing system. Except for this bad query, what is the standard average CPU load?
As to this query, if it's really doing table scans, and hash joins, that might explain it. Post the execution plan. There may be other issues with it as well. Lots of times people see an Index Scan or a Clustered Index Scan and assume that "hey, it's using an index, so everything is fine" But that can be as costly to the server as a table scan, more so depending on the process.
Also, you're reading from a bunch of tables to write into one table. Why? This is frequently a sign of rbar style processing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 21, 2008 at 11:49 pm
Hi,
that query was surely not optimized. I added one additional, combined index and suddenly the query uses a slightly different plan and is fast.
I just wonder, if (and i will) get in such situation again, is there a way to prevent sql server from consuming 100% of cpu (like setting a limit of 90%). I assume, there is not.
Tobias
July 22, 2008 at 12:16 am
Not in SQL 2005. In 2008, the resource governor can do that. Best way to stop a query from hogging the system is to rewrite it in an optimal way with good indexes.
The query processor's job is to run queries as fasst as possible, so it would rather take 1 CPU to 100% for 15 sec than take the CPU to 25% for a minute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 5:37 am
The best thing you can do is set up regular monitoring of your data. You can get third party tools like Idera's Diagnostic Manager or the new one from SQL Sentry, Performance Advisor, looks really interesting. On a few of our system we've got a home-grown system where Profiler collects data and a process moves it into a database for us to report off of. Either way, you can identify which queries are performing badly now and fix them, but more importantly, you can see which queries are degrading over time. This allows you to be proactive in your tuning rather than reactive. It makes a huge difference in your stress levels and should have an affect on your salary too if your boss is smart.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply