Low Performance - High CPU Problem and others...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Low Performance - High CPU Problem and...

42 posts, Page 1 of 5

1 2 3 4 5 »»»

Low Performance - High CPU Problem and others...

Rate Topic

Display Mode

Topic Options

Author

Message

SQL Pizza

Posted Tuesday, January 29, 2013 9:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

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
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1413125

SpringTownDBA

Posted Wednesday, January 30, 2013 1:45 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:19 AM
Points: 316, Visits: 1,484

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?

Post #1413512

SQL Pizza

Posted Wednesday, January 30, 2013 2:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

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
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1413517

GilaMonster

Posted Wednesday, January 30, 2013 2:26 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413523

anthony.green

Posted Wednesday, January 30, 2013 2:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413524

SQL Pizza

Posted Thursday, January 31, 2013 1:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

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
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1413901

anthony.green

Posted Thursday, January 31, 2013 1:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1413903

GilaMonster

Posted Thursday, January 31, 2013 2:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413910

SQL Pizza

Posted Thursday, January 31, 2013 2:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

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
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1413915

anthony.green

Posted Thursday, January 31, 2013 2:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

High CPU chapter in the Accidental DBA guide, would be a good place to start.

Post #1413917

« Prev Topic | Next Topic »

42 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions