Solved

best way to measure my SQL 2012 performance?

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

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 26

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 38

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now