SQL Server – Compare Write Speed Before and After Adding Index

clustered-indexindexnonclustered-indexperformancesql server

I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on a development server. I just want to know the methodology of testing the writing speed.

Best Answer

I want to know the methodology of testing the writing speed.

I am assuming you want to know if queries are running faster. It can be read, write, or a combination of both. There are many approaches you can take. It depends on the usage pattern of the table. I am sure I won't be able to list everything, but the following points will give you enough tools to measure the success of your newly created non-clustered index. Despite the method you choose, always be consistent with your test. Test with the same workload and on the same hardware.

You want to confirm if the new index is being used? How can you tell if an index is, being used? by Paul Randal explain how to do that.
If you measure the effectiveness of your non clustered index by using one query (or one store procedure call), you can use some of the methods listed below.

i) Comparing CPU, time, read, write. Measuring SQL Query Performance by Matthew McGiffen explains how to do that.

ii) Adam Machanic originally wrote SQLQueryStress, which is a fantastic tool to test the performance of a single query. How do I best measure the query performance? has more details on that plus talking about statistics parser.

iii) Compare Showplan in SSMS can give some good insight into the pre and post- performance. Compare execution plans documentation by Microsoft explains how to use it.

If you need to measure a workload (a combination of multiple queries or stored procedures), you will need to do more work.

i)As explained in the comment section by @David Browne, you can use the Experiment Assistant.

ii) You can use ostress tool before and after the change. SQL Server stress testing using OStress by Nikhilesh Patel explains how to use the tool

Related Solutions

Sql-server – Large Fact table and partitioning key dilemma

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one). The reason I state this is because I don't know what your code is querying. Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column? For example, are your queries actually looking for the row ID often? If you're not sure, you might be surprised by getting some performance benefits. The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you. Check out this article by Kim Tripp: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits. However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list. Page Splits are managed by proper FILLFACTOR not increasing INSERTS. Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting. In short, the argument runs shallow "

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes. I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.

SQL Server – Query Optimizer Recommends Adding Index Instead of Using Existing Index

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion:

WHERE [serialNumber] = 137802

According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of lower precedence is converted to the datatype of the higher precedence. Unfortunately, int is higher in the list than varchar. This blows up any hope of using the index as the column (serialNumber) values are converted to integers. The column being the 1st position of the index, leads the optimizer to not use that index and search for an alternative (and thus the suggestion.)

Solution is not to have any implicit or explicit conversions of columns in WHERE condition. Simply use:

WHERE [serialNumber] = '137802'

Best Answer

Related Solutions

Sql-server – Large Fact table and partitioning key dilemma

SQL Server – Query Optimizer Recommends Adding Index Instead of Using Existing Index

Related Question