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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kent OlsenDBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.