January 29, 2013 at 9:12 am
Hello everyone,
I got the Problem, that my SQL Server has always 97-99% CPU usage. We have snapshot isolation mode enabled, because we had a lot of deadlocks before. Old relicts are also the with nolocks and explicit rowlocks used by the developers. All I can see, is that the most CPU-consuming queries are queries which are joining at least 5 tables and have a lot of search predicates. When I take a look at the execution plan i see that 66% are wasted on a key lookup of a primary key. As far es I understand, I have to add the field of the primary key as include to an exisiting index. Is this the right way? I'm sure the experts here can help me!
PS: Another phenomenon I saw now is that these high-cpu-queries run forever and at a certain point all spids get killed an reactivated. The developers already reported me about timeouts in their queries. What could this be?
PPS: Just tell me which further details would be helpful...
Greetz
Query Shepherd
January 30, 2013 at 1:45 am
Which OS (be specific please)?
What kind of HW (# of cpu's, ram, etc)?
How big are the Databases?
Have you run sp_updatestats?
Are you performing regular index maintenance?
Did this just start? or crept up over a period of time?
January 30, 2013 at 2:05 am
Which OS (be specific please)?
Windows Server 2008 Enterprise SP 2 (64 Bit)
What kind of HW (# of cpu's, ram, etc)?
Intel XEON Quadcore @ 3.00GHz
40 GB RAM
HP MSA connected via FibreChannel
How big are the Databases?^
The Database has a size of 390 GB at the moment.
CDC in a different filegroup of 6GB size.
IDXs also in a different filegroup of 90 GB size.
Have you run sp_updatestats?
Are you performing regular index maintenance?
Yes, I run this every night.
Did this just start? or crept up over a period of time?
The problems exist longer and crept up over a period of time...
Greetz
Query Shepherd
January 30, 2013 at 2:26 am
No magic bullets.
And no, adding the pk column to NC indexes is not going to fix lookups, and is probably a waste of time.
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
January 30, 2013 at 2:27 am
Poor Performing SQL Part 1 - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Poor Performing SQL Part 2 - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
Also take a look at the Accidental DBA guide on the high CPU chapter (Link in my signature for free eBook download)
Edit
Gail quick off the draw as ever.
January 31, 2013 at 1:52 am
Thank you both for your quick nswer. To be honest, I've seen this great article before and I've been playing around with things like this several weeks before I chose to open a thread in this great forum. Yes, maybe I'm still searching the magic bullet...but I'm running out of ressources.
So as I understand you, my problems are still bad queries and bad indixes?
I know the queries that consume a lot of cpu, but I don't know what else i could do to make em run faster? I can see the problems of the qureis in the execution plan, but have no idea how to transfer them in indexes.
How about partitioning the tables that hurt? Is this an option?
Greetz KSC
Greetz
Query Shepherd
January 31, 2013 at 1:54 am
Partitioning is for data management not for performance.
If you have the execution plans, upload them as attachments in a SQLPLAN file format and I am sure someone will get around to looking at them.
Also take a look in the books section, for Grant's book on Execution plans, should hopefully give you some insite into understanding what the plan is saying.
January 31, 2013 at 2:07 am
There isn't a magic bullet. You need to sit down and tune the queries. If you have no idea where to start, consider hiring someone who does.
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
January 31, 2013 at 2:12 am
Ok. So i can state, that if all my queries are running well, my server is also running well?
Why does a query or better stored procedure claim so much cpu? Could you explain this to me or least tell me where I could read about it?
Greetz KSC
Greetz
Query Shepherd
January 31, 2013 at 2:13 am
High CPU chapter in the Accidental DBA guide, would be a good place to start.
January 31, 2013 at 2:19 am
KSC (1/31/2013)
Why does a query or better stored procedure claim so much cpu? Could you explain this to me or least tell me where I could read about it?
Typically because it's written in an inefficient way or can't use indexes.
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
January 31, 2013 at 2:27 am
Ok...I've already run over the High CPU-Chapter, but haven't studied it in detail. Because a lot of it i had already hurt and there was again no practical help or examples. I'm gonna make it up right now.
Typically because it's written in an inefficient way or can't use indexes.
Sounds logical!
Here's my execution plan...could you please have a look at it.
Thanks in advance!
Greetz
Query Shepherd
January 31, 2013 at 2:31 am
well as 87% is spent on a key lookup, I would add the output column from the key lookup as an included column of the IX_art_ID_client_ID_price..... index on the price table.
January 31, 2013 at 2:36 am
KSC (1/31/2013)
Here's my execution plan...could you please have a look at it.
I can look, but I can't tell you much useful from an estimated plan alone. Actual execution plan please, table and index definitions.
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
January 31, 2013 at 2:37 am
I'm trying it at the moment. Index change will take up some time. I'll report the results.
How did you come to this result?
What's the difference beetween an index key column and an included column?
Greetz
Query Shepherd
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply