Solved

SQL SERVER 2008 R2 Performance

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

785 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