• Home
  • My Favorite Topics
    • Blogging
    • Business
    • Career
    • Cars
    • Consulting
    • Epic Life Quest
    • Iceland
    • Marketing
    • Presenting
    • Productivity
  • My Life Quest
    • Future Achievements
  • About Me
  • My Recent Photos

What Would a SQL Server Experience Index Look Like?

9 years ago
SQL Server
16 Comments

sql-server-experience-indexThe Windows Experience Index was a neat way to get a quick idea of how fast your hardware was relative to the rest of the world. It gauged the performance of your processor, memory, graphics card, and storage. You could see the weakest link in your rig, and then focus your upgrade money there.

It wasn’t perfect, but it was the non-geek consumer’s first chance to instantly compare desktops next to each other at stores and get a feel for whether they were hot or not.

Let’s build this for SQL Server.

We need to decide two things: what we’re going to measure, and how we’ll measure it. You can contribute ideas for either one, and then I’ll compile them into a spec document. Then we’ll set up a Github repository and a project so that anybody can contribute code. We’ll build it as a stored procedure to keep things as simple as possible.

Throw your ideas in the comments, and I’ll merge ’em into a file and publish it to the repository.

SQL Server
Previous Post
Is Your Boss Warming Up the Bus?
Next Post
The Problems with Remote DBA Companies Today

16 Comments. Leave new

  • Richie Rump
    January 14, 2014 9:32 am

    Fantastic! Pardon me if that sounded too British.

    As a dev the most important thing for me is how fast I can run a query. Maybe that’s some sort of “Query Performance” metric. How would that work? No clue, I haven’t had any coffee this morning. Maybe it’s a blend of Memory, Disk IO and other stuff. Of course the proper indexes are more important than that. Now I’m just rambling. Ignore this post. 😉

    Reply
  • Shannon Lowder
    January 14, 2014 11:20 am

    Of course pulling how much memory the box has, hos much is allocated to SQL, average IO lag read and write by disk then weight those drives that should be faster as a higher requirement… tempdb fastest possible, if another db is faster, ding their index for that.

    Also look at what services are running. Look to see if they are used , is this box running a full sql server stack. Is SSAS really used? If not, ding the score. Are they running extra services IIS, applications, if so, ding their score.

    It’s all the basic stuff you’ve been telling us to check for on our boxes, the real trick is coming up with a weight for each of these values. Managing those weights is going to be tougher since you’re doing it in public, Microsoft was very quiet about what resulted in a score. They would only give you rough ideas on what affected your score.

    I believe the openness is a strength here. And the discussion around those weights will be heated. Good luck!

    Reply
  • Kev Riley
    January 14, 2014 11:29 am

    Wow. Boiling down SQL performance to a single number. That excites and scares me in equal measures!

    Imagine being able to go into a client and say I’ve upped you from a 2.5 to a 3.5 – instant recognition of benefit of having a DBA/SQL person on hand. But on the flip side trying to explain why a customers ‘7’ server is running dog-slow because they don’t backup the T-logs/don’t use indexes/use varchar(max) and GUIDs everywhere.

    Reply
  • Justin Jones
    January 14, 2014 12:15 pm

    We would need this number to have components like the Windows Experience Index. Categories would start with Processing Power, Memory, TempDB IO, File IO.

    Reply
  • Ben Thul
    January 14, 2014 4:08 pm

    I think a lot could be done with running some queries against AdventureWorks and measuring certain things. Running the same query against a cold cache versus a warm cache could give you an indication of memory speed, for instance.

    Reply
  • Martin Kameniar
    January 15, 2014 2:11 am

    Hi brent, we would like to know numbers of storage I/O (logical, physical disk), Memory I/O, CPU utilization, Network impact of users and aplpications, VM memory and processor overhead/utilization.

    Also it should be good insert recommendation and compare recommendation numbers with real numbers of our servers. (like advanced option?)

    Reply
  • Nic Neufeld
    January 15, 2014 8:14 am

    What I want to know is, should we stop worrying about Experience Index fragmentation? 😉

    Reply
  • Greg Robson
    January 15, 2014 1:33 pm

    Interesting perhaps you could….

    …benchmark a query that’s really I/O intensive (with very little processing) to test hard drive performance.

    …repeatedly run a complex query (on a temp data set) to test the RAM?

    Capacity might be easier to work out (it’s a function of hard drive space)… although that depends how many drives you have?

    Of course, it’s all relative to your use-case, which makes it hard to create a one-size-fits-all solution? Interesting idea though!

    Reply
  • (SFTW) SQL Server Links 17/01/14 • John Sansom
    January 17, 2014 3:22 am

    […] What Would a SQL Server Experience Index Look Like? – Exploring the idea of giving your SQL Server instances a performance score, it’s Brent Ozar (Blog|Twitter). […]

    Reply
  • Andrew Notarian
    January 17, 2014 8:15 am

    I finally clicked on this after thinking all week this was a measure of a person’s SQL Server knowledge. I’ll give myself a 6.

    Maybe it needs a better name? Should there be anything from TPC? TPC stuff from Gartner that does not factor license cost used to really upset me in my grad school database class because it always made Oracle look better.

    I/O is basically most of this, and the bigger the databases, the more so. If your database is bigger than the RAM assigned to SQL Server it should bump up.

    When you’re done with this, please do it for Analysis Services. Kthxbai.

    Reply
  • Dale
    January 17, 2014 9:22 am

    Utilizing the existing sp_Blitz and sp_BlitzIndex to generate some form of average score across the databases would be a great way to begin as well. Give each test a weight and average it out over both the system and all the databases would give a good start.

    Reply
  • Chris
    January 17, 2014 10:06 am

    Assuming you’re looking to build a score on a new server with no data or load, I’d say these metrics with a 5 point scale.

    Disk latency: =20ms = 1
    Separate scores for log, data and TempDB drives
    Drive throughput: >1000Mb/s = 5, =384GB = 5, =32 =5, <=2 = 1

    Optionally also IOP efficiency. Is one SQL read equal to one disk read. Are the drives formatted and aligned correctly (Not so much an issue these days).

    Reply
  • David Anderson
    January 17, 2014 11:19 am

    While I like the concept of a single index number, I am not sure how much value it would give. If I have several servers all with an index of 5, which ones are performing well and which poorly?

    It could be useful as a relative measure for a single server. If the server was at 5 and, after making a few tweaks, it goes up to 6 then I can be pretty sure my changes had a positive effect.

    Reply
  • flyingcod @flyingcod
    January 31, 2014 7:47 am

    Security risk or Potential for risk metric? How many SQL Devs have access to your production servers? ;o)

    Reply
  • Tim
    June 13, 2014 12:56 pm

    Are you looking to put an index on the server hardware, or how SQL is set up, or both? I can have a great server, but if I run inefficient operations against a poorly designed database, how will that compare to more pedestrian hardware that is home to a well designed database app?

    Reply
    • Brent
      June 13, 2014 1:05 pm

      Tim the first two, but neither of those apply to the application code. It’s just like the Windows Experience Index, which measures your hardware but not your apps.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Hi. I’m Brent.

That's me, Brent.

I live in Las Vegas, Nevada. I'm on an epic life quest to have fun and make a difference.

I co-founded Brent Ozar Unlimited to help make your SQL Server go faster. I also maintain sp_Blitz® and the open source First Responder Kit repo.

My current car collection includes a Jaguar XKR-S, Porsche 944 Turbo, Porsche 356 Speedster replica, and a Ferrari 328 GTS.

profile for Brent Ozar on Stack Exchange, a network of free, community-driven Q&A sites

© 2021 Brent Ozar, all rights reserved. Privacy Policy

  • Home
  • My Favorite Topics
  • My Life Quest
  • About Me
  • My Recent Photos