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.

  • What is your programming background? Jan 4 '20 at 10:42
  • 2
    I would run a representative workload of both reads and write operations with and without the index to measure times since there isn't much actionable information with writes alone. Be aware that the physical writing to data files is asynchronous but physical reads are synchronous (unless prefetched). Unless your dev box is the same as production, interpreting results is a swag.
    – Dan Guzman
    Jan 4 '20 at 14:29
  • You can use the "Microsoft Database Experimentation Assistant" for A/B testing based on a captured workload. It's intended for upgrade verification, but usable for scenarios like this too: microsoft.com/en-us/download/details.aspx?id=54090 Jan 4 '20 at 16:53

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


If you want a simple test, I would identity the top 5 most frequently executed insert queries on the table on your live system. Then on Dev, switch on the client statistics in ssms (https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/) and note the total execution times taken when you run the queries before and after the non-clustered index is added. I would run the queries at different times of the day and multiple times to get an average.

Maybe you could use something like this to run the query multiple times:

 DECLARE @repeats INT = 1000
        DECLARE @i INT = 1

        WHILE @i <= @repeats
            -- Your query here

            SET @i = @i+1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.