[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL SERVER 2008 R2 Performance

Posted on 2011-03-08
4
Medium Priority
?
382 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 375 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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