Solved

best way to measure my SQL 2012 performance?

Posted on 2014-02-21
5
210 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

688 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