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.
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 BEGIN -- Your query here SET @i = @i+1 END