Solved

best way to measure my SQL 2012 performance?

Posted on 2014-02-21
5
209 Views
Last Modified: 2014-03-10
I would like to test my new dedicated Win 2012 server to see how well it handles SQL2012 before I decide whether or not I want to keep it or change to a new server.  SQL 2012 web edition is already installed on it (and has SQL Mgt Studio).  What is a good way to benchmark the performance of SQL 2012 on my server?  I want to see how well it runs.  Thanks for any help!
0
Comment
Question by:arthurh88
[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
5 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39879335
The best test would be testing a real world scenario, wouldn't it? Attaching your own database and using it with your own application.

You might do some load test:
http://www.brentozar.com/archive/2012/06/load-test-sqlserver/

To know, if it'll fit your needs, you'll first need to know your needs or estimate them. Be prepared to be able to extend your hardware and optimise your software.

Bye, Olaf
0
 
LVL 78

Expert Comment

by:arnold
ID: 39879359
To Olaf's point.

SQL performance can be affected by several things.

system memory versus cumulative size of the user database/s. This will tell you whether when all databases are active at the same time (PEAK time) memory swap may occur which will degrade the performance.
storage setup/configuration i.e. RAID 1 OS, raid 10 DATA, RAID 1 transaction log etc. as well as the type of storage components used. SATA/SAS (HD/SSD) or FC/iscsi SAN LUNS.
I believe there is an osql from MS to test the storage components.
Finally when the above is taken into consideration, the SQL tunning advisor will help with SQL specific performance enhancements i.e. adding an index, a statistics to assist the execution plan of queries.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39879390
Before you go into rocket science try to compare to something you already know. For instance if you have a heavy query or procedure on a database on an existing server, other than this one of course, restore that database on the new server and run the same query and compare the results. And don't try it only once but multiple times from different query windows.

Another thing you can do is do a backup and then a restore for the largest database you have and than do the same thing on the new server.

If these things don't perform better on the new server you don't need to go further and see what the cause might be. In the most cases if the CPU and memory are similar the reason is the I/O performance of the storage associated to that server.

As a note the W2012 is the best OS that you can pair with the 2012 SQL server. The Standard works just fine. We use it on production environments on VM boxes and actually SQL performs better than our original stand alone box but that is mainly because of the much better storage units. RAID 6 for data files and RAID 10 for transaction logs at 15K rpm.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39879587
If you have a service or from end of some kind hitting the database, set that up and then use something to stress it
https://jmeter.apache.org/
There are others but this is free and works great.

Like has been said, you have to test it for YOUR situation.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39879717
Hi,

I thought that one of the trade-offs for the lower price of web-edition windows was that you couldn't install SQL (or other middleware) on it.

Maybe that was a couple of versions ago, but I'd look up the supported Windows versions/editions before starting the install of SQL.

HTH
  David
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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

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