Solved

SQL SERVER 2008 R2 Performance

Posted on 2011-03-08
4
371 Views
Last Modified: 2012-05-11
Hi,

We have a wcf, wpf application running with SQL Server 2008 R2 Evaluation version and we did the stress test with more than 200 users and the CPU Usage of the DB Server went upto 70 to 90 % all the time.
We are planning to buy SQL Server 2008 R2 Enterprise Edition Processor License.

Please let me know the problem is because of the evaluation version or may be some other issues.

Regards,
Rajesh
0
Comment
Question by:rajeshmarappan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 35071915
Rajesh, evaluation edition does not have any restrictions. The problem is in the code. In 2 words - optimize the queries and database backend.
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35071921
Assuming, of course, that the hardware can handle the load.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 125 total points
ID: 35073121
The "evaluation edition" is a time-limited copy of Enterprise Edition - there's no functional difference at all between Enterprise (what you're going to) and Evaluation (what you currently have). There are actually no processor limitations of any kind of SQL Server, so that's not your issue.

I suspect there's a single misbehaving query that's causing most of your headache, and there are a couple of ways to hunt it down. SQL Server Management Studio has some built-in reporting functionality that will show you the top 20 most disk-intensive and cpu-intensive queries, both on average (per query) and in total (query resources times number of executions). Those two graphs make it pretty obvious where the offending queries are. You can find these reports in SSMS by right-clickcing on your server, going to "Reports", and then to "Standard Reports".

Here's a quick explanation of these top reports:
http://blogs.msdn.com/b/buckwoody/archive/2007/11/27/sql-server-management-studio-standard-reports-performance-top-queries-by.aspx

Another option is to add some indexing. I'd encourage you to read about proper indexing, but as a quick and dirty way to do it, you can run SQL Profiler while you do some load testing with your application, then load up "SQL Server Database Engine Tuning Advisor" and have it analyze your Profiler logs and recommend some indexes and statistics to help speed up your queries. You can also use the DTA to analyze a single query - for example, your long-running query I mentioned above - and make recommendations on indexes there.

Here's an example walk-through on AdventureWorks:
http://www.zimbio.com/SQL/articles/655/How+Tune+Database+Using+Database+Tuning+Advisor

Another walk-through:
http://www.mssqltips.com/tip.asp?tip=1872

A word of note, though - indexes are a trade-off. While they can make a huge difference in lookup speed, they slow down updates and deletes on the indexed tables (since the change needs to be made to the table and then to all indexes). It's not pronounced, but if you add too many indexes, you can have the opposite problem, so be careful.
0
 

Author Closing Comment

by:rajeshmarappan
ID: 35215553
Not Fully
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Enabled trace flag 4135 or 4199 - SQL SERVER 2 34
denied execute as 13 57
SQL profiler 3 51
Need a mirrored QA test site 2 93
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question