The TPC-E benchmark is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring hardware vendors will not feel as much pressure to equip their test systems with disproportionately large, expensive disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark, which means that the results tend to correlate pretty well to CPU performance, as long as the I/O subsystem can drive the workload effectively. My assumption here is that any hardware vendor that takes the time and expense required to submit a TPC-E Benchmark result is going to make sure they have enough I/O capacity to properly drive the workload for the benchmark.

When I am assessing the relative OLTP performance of different processors, I like to take the raw TPC-E tpsE score for a system using that particular processor and divide it by the number of physical cores in the system to get an idea of the relative “per physical core performance”.  When you do this, you should also be aware of how many physical sockets are in the system, since you will not see 1:1 scaling as you add additional sockets, even with NUMA (which is a huge improvement over the old SMP architecture). This means that a four socket system would not have double the performance of a two socket system that was using the exact same processors and components. This is further complicated by the fact that Intel has a different release cycle for their processors that are primarily intended for two socket systems, where newer models become available in the two socket space about 12-18 months before the roughly equivalent four socket processor. I say roughly equivalent, since the two socket models usually have higher base and turbo clock speeds than the four socket models from the same processor generation. A good example is the two socket Intel Xeon X5690 (Westmere-EP) compared to the four socket Intel Xeon E7-4870 (Westmere-EX).  The Intel Xeon X5690 has a significantly higher base clock speed (3.46GHz vs. 2.4GHz) and a higher turbo clock speed (3.73GHz vs. 2.8GHz) compared to the Intel Xeon E7-4870. There are more than clock speed differences to consider here, since the X5690 only has six physical cores, and a 12MB L3 cache, while the E7-4870 has ten physical cores and a 30MB L3 cache.

Keeping all of this in mind, I found some recent TPC-E results for several of the latest processors from both AMD and Intel, and divided the actual raw score by the number of physical cores in the system. I decided to divide by physical cores, since SQL Server 2012 Enterprise Edition is licensed by physical processor core instead of the old familiar per socket licensing that was used in previous versions of SQL Server. This means that people should be focused on getting the most performance and scalability possible from each physical processor core from a hardware selection and licensing perspective. As you can see by looking at the Score/Core column of Table 1, the new Intel Xeon E5-2690 (Sandy Bridge-EP) is the current champion, followed pretty closely by the Intel Xeon X5690 (Westmere-EP). We see a pretty big drop going to the Intel Xeon E7 processors, in a four and eight socket configuration. Finally, the latest AMD processors are bringing up the rear, with quite a gap between them and the Intel offerings. This does not look very good if you are thinking about using AMD processors for an OLTP workload, especially since you will have to pay for more physical cores (up to 16) in an Opteron 6200 series processor.  One factor that helps mitigate these scores and license costs somewhat for AMD is the new SQL Server 2012 Core Factor Table that Microsoft released on April 1, 2012. This table reduces the licensing cost for most new AMD processors that have six or more cores by allowing you to multiply the actual physical core count by a factor of 0.75 for licensing purposes.

Table 1: TPC-E Performance by Physical Core

Every time you modify data in a table(with a insert, update, or delete), the affected indexes must also be updated. With an OLTP workload (which means that your data is volatile) you typically want to have fewer indexes than with a DSS/DW type of workload (where the data is relatively static). One guideline from Tom Davidson when he was on the SQLCAT team in 2006 was to try to have less than four indexes on tables that were frequently updated. Lindsey Allen (from SQLCAT) also discussed the cost of having too many relational indexes here.  I generally start to get concerned when I see more than about five or six relational indexes on a volatile table. Having too many relational indexes has a negative effect on insert, update, and delete performance. Ultimately, it is a judgment call, based on your workload, your hardware and I/O subsystem, and on your experience.

On the other hand, if you have too few effective indexes or you are “missing” important indexes that SQL Server could effectively use to satisfy its query workload, you will also see bad overall query performance. You will see table or index scans where a seek could have satisfied a query that only returned a few rows of data. This can cause increased memory, I/O, and CPU pressure, depending on the situation. It can also cause extra locking and blocking while these extra scans are occurring.

What you want to have is a happy balance between too many and two few relational indexes. You want to eliminate indexes that are not being used, and add indexes that SQL Server thinks it needs (using your own good judgment and knowledge of your workload). After all, my two miniature dachshunds always think they need more doggie treats, but I sometimes know better!

Here are a couple of queries that will help you reach this sweet spot for your relational indexes. I like to call these two the “dynamic duo”, since they are so useful for judging whether you need to adjust your overall index strategy at the database level.

-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], 
i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], 
user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, 
[Total Reads] ASC OPTION (RECOMPILE);

-- Look for indexes with high numbers of writes 
-- and zero or a very low numbers of reads
-- Consider your complete workload
-- Investigate further before dropping an index


-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) 
AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, 
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Look at last user seek time, number of user seeks 
-- to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!

After I look at that, I tend to try to focus on a single table in more detail before I make any changes. You should not just start blindly dropping or adding indexes based on these two queries alone. You should evaluate your existing indexes on that table and consider your complete workload, and apply some good human judgment before you make index changes.

The queries below will help focus your attention on a single table. In this case, I am looking at the ActivityEvent table.

-- Index Read/Write stats for a single table
SELECT OBJECT_NAME(s.[object_id]) AS [TableName], 
i.name AS [IndexName], i.index_id,
SUM(user_seeks) AS [User Seeks], SUM(user_scans) AS [User Scans], 
SUM(user_lookups)AS [User Lookups],
SUM(user_seeks + user_scans + user_lookups)AS [Total Reads], 
SUM(user_updates) AS [Total Writes]     
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND OBJECT_NAME(s.[object_id]) = N'ActivityEvent'
GROUP BY OBJECT_NAME(s.[object_id]), i.name, i.index_id
ORDER BY [Total Writes] DESC, [Total Reads] DESC OPTION (RECOMPILE);


-- Missing indexes for a single table
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) 
AS index_advantage, migs.last_user_seek, 
mid.statement AS 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, 
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
-- Specify one database, schema,table
WHERE [statement] = N'[NewsGator_SocialServices].[dbo].[ActivityEvent]' 
ORDER BY index_advantage DESC OPTION (RECOMPILE);


-- Look at existing indexes 
--(does not show included columns or filtered indexes)
EXEC sp_helpindex N'dbo.ActivityEvent';

-- See how big the table is
EXEC sp_spaceused N'dbo.ActivityEvent';

Remember, sp_helpindex does not show information about included columns or filtered indexes. One work-around is to script out the index creation DDL statements for each existing index to see what is really there. You can also use Kimberly Tripp’s (blog|twitter) excellent sp_helpindex2 procedure to pickup that information.