Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue on the server Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 10:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 06, 2014 5:18 AM
Points: 290, Visits: 524
Hi Experts there is an issue on my production server where my cpu utilization is high.
Below are my configuration settings.

16 cpu with highperthreading enabled
Sp configure

Maxdop setting 12
Max server memory 32 GB

Windows and sqlserver 64 bit.

Cpu are always at more then 90 percent.

There are deadlocks errors on 1 table which might be the main cause.

Please let me know the requirements needed to get it into.





Post #1536165
Posted Wednesday, January 29, 2014 10:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 41,517, Visits: 34,433
Tune the code, tune the indexes

Chapter 3 - https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

and

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



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 #1536167
Posted Wednesday, January 29, 2014 11:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 06, 2014 5:18 AM
Points: 290, Visits: 524
Hi Gail

Thankz for prompt reply the queries which are accuring in the dead locks are an undate satements which is a single line update statements can we put rowlock or there is an clustered index with primary key on it can I drop and create it as a moncluster index on as it these table is being highly utilized with insert upadate and delete.secondly there is an implicit conversion for some queries which is making index to scan instead of seek.

Also the adhoc cache is 43 % used with 35 being ised by prepared.

Kindly suggest.

Post #1536168
Posted Wednesday, January 29, 2014 11:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 41,517, Visits: 34,433
samsql (1/29/2014)
Kindly suggest.


Tune the code, tune the indexes (or get a consultant in to do so). See the book and articles I referenced to guide you.



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 #1536173
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse