How likely is fragmentation on a table with 40000 products likely to affect performance

Based on the data that you have provided -

there are 67 total pages. Index fragmentation for such a small table would not affect the performance. I would not worry about index fragmentation for the table that you have mentioned.

You should update your table statistics so that sql server can generate better query plan.

I would start my troubleshooting by

• Running sp_Blitz: Overall Health Check

Read : Stop Worrying About SQL Server Fragmentation

Indices are bound to fragment in course of usage (time). What matters is if it is affecting the performance.

Usually we do not worry about of indices with page_count < 500. (We as in, my team)However,there is not hard and fast rule for a particular page_count.In my previous shop we had set the limit in our Ola script for 1000 page count. But yes with index having page_count = 67, the fragmentation will always be on the higher side.

You should not be worried about the fragmentation at current page_count.

Please have a go at below blogpost from Paul Randal :

Where do the Books Online index fragmentation thresholds come from?

Main difference between your Test server and Live server is Concurrency.

You haven't done Concurrency Test in your Test server.

Concurrency is one of the main reason for Live Site to be slow.

What code is written in Front end application like what you have written in connection string . Like connection pool,whether connection is properly close after each transaction.This is often the main reason behind slowness .

Then your other part of Front end code.

What query is written in your SP. Whether any deadlock issue because of Concurrent user.

Are there any situation where concurrent user trying to update same set of rows ? How you have handle Race condition ?

Why there are 12 index in single Table with only 40000 records ?

Please throw table structure along with all indexes.