Index Rebuild on all tables in a DB gives an unexpected performance gain.

Hi EE,

This question is an inverse of what is usually asked in EE; I am looking for an explanation for what happened not a solution.

The setup:

A web application that used IIS as it's web server was having performance issues (12-27 second load times) when loading a large amount of form data.  The issue only occurred with users on our Citrix environment anyone who accessed the web application from their local desktop didn't have the issue. So while waiting for our Citrix guys to look into the issue, I thought I do a mid-week index rebuild on all tables in the server using the following script:

DECLARE @fillfactor INT
SET @fillfactor = 100
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

Open in new window

Observing the fragmentation levels of the tables in the respective database was minor ranging between 7%-13% percent. Suddenly performance issues disappeared for the Citrix users.

How did this happen or did something else to fix this issue that isn't SQL related.

Any assistance is welcome.

Thank you.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Is it possible that the queries that came through Citrix were different than the others?  A small difference in a query can mean a huge difference in overhead.
DBAduck - Ben MillerPrincipal ConsultantCommented:
As much as I would love to say that it would be easy to say what fixed this, there is a ton of context that we are missing to give you a definitive answer.  Rebuilding the indexes all the way would help, but because the ones on Citrix were slow and the ones that were not were fast, there is difference that would not necessarily be fixed from an index rebuild.  The rebuild would help queries that may have needed more contiguous indexes, but if the same queries were being run on citrix and locally then the index rebuild would not fully explain the performance gain.
ZackGeneral IT Goto GuyAuthor Commented:
Hi DBAduck,

Cheers for the response yes a lot of context is missing, I am trying to look at our infrastructure to see if there is anything else I can add to this case to provide some clarification.

Thank you.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

A rebuild also does a full update statistics. In my experience this update statistics might explain a performance gain.

If you ever encounter this issue again, try an update statistics first and see what it gives.
ZackGeneral IT Goto GuyAuthor Commented:
Hi Kent,

Thank you for the suggestion, and I will enable debug mode on our UAT instance to see if the SQL parameters parsed in Citrix differ from those from a local desktop.

Thank you.
ZackGeneral IT Goto GuyAuthor Commented:
Hi Kent,

 A close investigation does reveal the queries originating from Citrix sessions are slightly different and take longer to complete than those coming from desktop sessions according to the SQL Query Analyser. The question is why, which I will begin to troubleshoot with the vendor.

Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.