Solved

SQL SERVER 2008 R2 Performance

Posted on 2011-03-08
4
374 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

689 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