0

I have the following query:

SELECT [t].[Key], SUM([t].[ValueA]), SUM([t].[ValueB])
FROM [MyTable] AS [t]
GROUP BY [t].[Key]

This table had an nvarchar column with value lengths of 2000 characters (I think this shoot up the I/O cost). As I didn't need this column in the query, I created a new auxiliary table, and moved this heavy column to make the query faster and update statistics runing:

Update STATISTICS HCJConsumos WITH FULLSCAN

But the query still takes 1 minute to run and the I/O Cost is still very high 383 enter image description here

This improved the execution time but the I/O cost was still high. Then, I tried Exporting Data-tier Applicattion and importing it, when I estimated the Execution Plan again the I/O cost dropped to 7. enter image description here

And this improved the performance much more, it takes 6 seconds.

Is it possible to make the database realize that this cost is overestimated without exporting and importing the database, as this makes the query run much faster. Due it is the same data, this must be affecting the execution plan and performance of the query.

Improve this question
4
  • 2
    You probably want to add how exactly you "created a new auxiliary table, and moved this heavy column", as this might be relevant to the answer.
    – mustaccio
    Commented Sep 8, 2021 at 23:12
  • 1
    Does the original table have a clustered index? Was it rebuilt after the column was removed?
    – Michael Green
    Commented Sep 9, 2021 at 2:32
  • @mustaccio This is a Code First web project so, I executed manually a script with a CREATE TABLE Aux( PkId int, FkId int, HeavyColumn nvarchar(max)); INSERT INTO Aux(...) SELECT ... FROM MyTable Then I added a migration to remove the heavy column from MyTable and create the OfficialAux table, to keep this OfficialAux table in the known by the model. And later executed another Insert statement manually to move the data from Aux to OfficialAux (I didn't do this in the migration since I got a Timeout) and remove Aux table.
    – cballes
    Commented Sep 9, 2021 at 7:02
  • @MichaelGreen About the indexes, the original table (MyTable) had a Clustered Index, and OfficialAux too. After moving data I didn't rebuilt the clustered index (PkId) from MyTable.
    – cballes
    Commented Sep 9, 2021 at 7:03

1 Answer 1

Reset to default
0

Statistics only affect the query plan that is chosen. They have no impact on run-time speed. The estimated cost in a plan is calculated from the statistics. Neither one affect how a particular query plan performs. In your case, you have identical plans, so the difference in performance must be due to something else.

After exporting/importing the data, you got the same plan, but now with drastically lower estimated costs.

Lack of proper index maintenance is the likely culprit. For example, if the clustered index was rebuilt with a FILLFACTOR of 10%, each page would be 90% empty and the size of the table on disk would be 10x greater, hence 10x more IO to read the table. This effect could also be created by the insert/update/delete pattern on the table.

Exporting/importing a data tier application is done in clustered index order. This has the same effect as a clustered index rebuild.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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