• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

SQL SERVER 2008 R2 Performance

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
rajeshmarappan
Asked:
rajeshmarappan
  • 2
1 Solution
 
dwkorCommented:
Rajesh, evaluation edition does not have any restrictions. The problem is in the code. In 2 words - optimize the queries and database backend.
0
 
dwkorCommented:
Assuming, of course, that the hardware can handle the load.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
rajeshmarappanAuthor Commented:
Not Fully
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now