Solved

best way to measure my SQL 2012 performance?

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Quer 4 22
Update one table with results from another table in SQL 6 27
VB.NET Application Installation with sqlserver 8 32
active directory 6 11
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
A procedure for exporting installed hotfix details of remote computers using powershell
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

829 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