The 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.
16 comments. Leave new
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. 😉
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!
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.
We would need this number to have components like the Windows Experience Index. Categories would start with Processing Power, Memory, TempDB IO, File IO.
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.
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?)
What I want to know is, should we stop worrying about Experience Index fragmentation? 😉
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!
[…] 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). […]
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.
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.
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).
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.
Security risk or Potential for risk metric? How many SQL Devs have access to your production servers? ;o)
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?
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.