Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

best way to measure my SQL 2012 performance?

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
arthurh88
Asked:
arthurh88
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
arnoldCommented:
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
 
ZberteocCommented:
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
 
Aaron TomoskyTechnology ConsultantCommented:
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
 
David ToddSenior DBACommented:
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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

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