how can I find out why publish is constantly rebuilding a table that hasn't changed?

Question

Hi all, I have a large project I've been running in SSDT since CTP3. Now RTM is out I want to get it so that each publish is only affecting the actual objects that have changed in this version. This is tricky as sometimes sql changes values (which I've raised as a different issue http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/e48b0b20-37e5-4644-931f-022c0408ecc8) However I've been taking the structures from the Sql server where this has happened to eliminate this issue.

However I now have a situation where publish wants to rebuild a table that hasn't changed and compare doesn't. Is there any way I can get some clues on why it is doing this?

I'm including all objects in the compare (on the second tab)

Unfortunately Publish profiles are not importable into schema advanced options (please could we have a future feature to unify this?) so I've had to do a manual comparison. In doing so I've found a number of options that are available in publish which don't seem to be available in compare (which I've listed below). It would be great if someone could tell me what the equivilent options are for those items that are NA (does it assume On or Off for that process?) - although the exception ones I don't think would cause a table rebuild..

So I've got all the settings (that do exist on both) to be identical and I still get the rebuild table during publish but not during compare. I get this every time even after publishing after a successful publish.

This is a large table and it isn't good for it to be continually rebuilding on deploy apart from anything else.

When generating the script from compare I also notice that it isn't including my pre-post deploy scripts. Could this have any affect?

Thanks for any help!

Comparison of settings available between Deploy Profile and Schema Compare:

	Deploy  Profile	Schema Compare
Ignore ANSI Nulls	On	NA
Ignore cryptographic provider file path	NA	On
Ignore extended properties	Off	NA
Ignore Permissions	Off	NA
Ignore Quoted Identifiers	On	NA
Ignore Role Membership	Off	NA
Ignore with nocheck on check constraints	Off	NA
Ignore with nocheck on foreign keys	Off	NA
Include Transactional Scripts	On	Off
Script database properties	NA	On
Script Refresh module	On	NA

Answer 1

Hi Brett,

I'll work with you to get to the bottom of the issue you're struggling with.  Could you include or maybe email the script(s) for the table that's causing the problem?

Unifying the profile models and options for Schema Compare and Publish is on our radar, but thanks for drawing attention to it as that helps us prioritize our work.

Cheers,

Bill Gibson 

Answer 2

Hi Brett,

Further to my reply above, can you confirm if you have a refactor log in the project concerned?  If so could you temporarily remove it from the scope of the deployment (renaming the file will do this) and see if that has an effect and report back to us?

Also, optionally including pre- and post-deployment scripts in a Schema Compare-driven update is another item on our radar.

Cheers,

Bill

Answer 3

Hi Bill,  Thanks for the offer of help on this, it is really appreciated.

As per your suggestion (which I should have thought of!) I tried setting refactorlog to build:none - but it didn't change the constantly rebuild behaviour that I'm experiencing in this case.

I have spent some time this morning building a test project that reproduces the issue. If you can let me have your email address I can package up and send over. There are a few tables involved due to FKs but I have reduced the project of 1000's of objects down to around 10.

Thanks for letting me know that the underlying parts will be prioritised to help users solve this kind of issue themselves. I think these are key progressions from the current tool to round out the awesome functionality that you guys have provided.

Thanks again

Answer 4

Hi Brett,

Thanks for boiling this down to a small repro.  You can contact me at microsoft.com - 'billgib' is my alias...

Cheers, Bill

Answer 5

Hi Bill, I sent on monday but haven't had a reply so far. Could you confirm that you've received it ok? Thanks

Answer 6

Guys, I've tried again using gmail but billgib alias fails when emailing it 

Does anyone else want this repro that I've created?

Answer 7

Hi Brett,

Sorry, I didn't see that you were having problems.  Can you try bill.gibson at microsoft.com ?  If that doesn't work I'll investigate another avenue.

Cheers, Bill

Answer 8

Hi Brett,

Can you also send me just an email with no attachment - that way I can get your email address and we can work out an exchange mechanism for the data offline if need be.

Thanks, Bill

Answer 9

We have repro'd the problem and will reply back to this thread when we understand the cause.

Answer 10

Hi Brett,

The problem is caused by the use of computed columns in the middle of the problem table.  When published, the scripts for these columns are rewritten by the database engine and stored in the rewritten form in the database.  When Publish compares the table definition in the project to the definition in the database there is always an apparent difference, which, because the affected columns are in the middle of the table causes the table to be rebuilt. 

Two work arounds exist:

1. Use Schema Compare to apply the table definition from the database to the project.  This will overwrite the code for the computed columns in the form that the engine uses.

2. Move the computed columns to the end of the table (retaining your code style/format)

I have tried both approaches and both stopped the table from being rewritten.  We are looking into providing a long term fix for this in a future release.

Let me know if these workarounds don't solve the problem for you.

Cheers,
Bill

    

Answer 11

Hi Bill, the problem I found (that I did mention originally) is that schema compare doesn't detect any changes. Your workaround no.1 is the one I usually use however it doesn't work in this case due to this. Did you change settings? Did you build and deploy the example to a sql 2005 then try to compare from db -> project? If you are seeing what I am compare will see no changes.

The problem with the workaround 2 is that it is still detecting the table as changed and although now only altering the table instead of rebuilding it it is still rebuilding indexes that relate to the persisted computed column (that it is detecting as changed).

I have even tried to make a change to my local object to get schema compare to detect a change and then refresh from the database - it still detects a change each time and rebuilds. Are you sure it isn't anything to do with the persisted computed column?

Also, it seems when you move the computed columns to the end - it doesn't actually change the order of the fields in the database as part of the deploy - so then compare does show differences but no amount of deploys fix the field order. I suspect this is an loosely related additional ssdt deploy problem.

A related feature request that I thought of - what would be really nice is an option on sqlpackage.exe to produce an xml report that can be loaded into the gui for schema compare - this would allow me to use this report to view the things that were going to change and crucially why before I commit to the publish and also this would allow highlighting the reason for the changes that are being seen by the deploy tool.

I currently have a powershell script that I use to produce a report wait for a keypress then script and/or publish in one operation - I could add the above to this and it would make for a really elegant process.

Answer 12

Hi Brett,

Well I just did this a couple more times to check my findings (all with the released version of SSDT).  I took the project you sent and built it and published it to SQL 2005.  I published it a second time and saw the table rebuild problem.  I then ran Schema Compare with the published database as source and the project as target and the comparison highlighted the script differences for the column computations in the one problem table.  I then applied the update indicated by schema compare to the project which changed the table script, and then refreshed the comparison which then showed no differences.  Inspecting the table in the project showed the code had been updated - all your comments had been lost, for example.  Then I ran publish again and verified using DTOPs by inspecting the preview and script that it did not plan or script a rebuild of the previously affected table).

I used the default options for schema compare.  If you can't repro this using the RTW release and the default options, and using the project you sent me then we will need to dig into this further.

I will investigate the other issues you reported with option#2 -  I did not check what happened to the indexes when I tried that.  However, as you have tried this solution before it seems we shuld focus on getting option #1 to work for you.

Regarding your request for richer reporting from Publish and SqlPackage - these are options we're also exploring for future releases - I'm particularly interested to see us minimize the apparent differences between the various ways we handle the compare/update cycle in different parts of the product.  Your suggestion to load the output from SqlPackage.exe into Schema Compare is also really interesting - not one I had got to yet - so a nice suggestion, thanks!

Cheers,
Bill

Answer 13

Thanks Bill, I suspect the issue could be related to the fact that I'm not using the default deploy options - but also I'm using a larger project which may be introducing other factors not in the repro. See below for my publish profile (this is from my real full project, rather than the cut-down repro) - sorry I'd normally like to prove the repro myself but I wont get any time in the next few days, so perhaps this might quickly shine the light on the cause.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>db</TargetDatabaseName>
    <DeployScriptFileName>db.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=myserver;Integrated Security=True;Pooling=False</TargetConnectionString>
    <DeployDatabaseProperties>True</DeployDatabaseProperties>
    <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
    <GenerateDropsIfNotInProject>True</GenerateDropsIfNotInProject>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <GenerateDeployStateChecks>True</GenerateDeployStateChecks>
    <IgnoreKeywordCasing>True</IgnoreKeywordCasing>
    <IgnoreWhitespace>True</IgnoreWhitespace>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <PublishDependentProjects>False</PublishDependentProjects>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <ScriptDeployStateChecks>True</ScriptDeployStateChecks>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
  </PropertyGroup>
  <ItemGroup>
  </ItemGroup>
</Project>

If you get a chance to apply these settings to the repro and can see the same issue then great, otherwise I will try to take a look in a few days to repro the problem with the repro project with the same steps you detailed and we'll get to the bottom of it.

Answer 14

For the benefit of others following this thread...

Working offline, we have found what appears to be a difference between the behavior of SqlPackage.exe and Publish in SSDT, where the script produced by SqlPackage.exe includes the rebuild of a table that has persisted computed columns, even after updating the script of that table with Schema Compare as described above.  We have logged a bug and are investigating the root cause.

   

Answer 15

To Brett and others following this issue,

Investigating offline, we have found the root cause of the issues at play here. First off, as Bill mentioned above, updating the project with the engine-normalized table definitions from the database that result from the first publish operation will eliminate the table rebuilds from future deployments when publishing via the IDE.

Secondly, as to the difference in behavior between the Project Publish and SqlPackage.exe deployment scenarios, it turns out the the constant rebuild of the table when deploying using SqlPackage is due to a known bug in the model comparison engine in the dacpac<->db case. This issue does not exist in the project<->db case, hence why the behavior was not appearing in repeated Project Publish operations. This bug has since been fixed and I verified that this issue no longer repros in an environment that contains the fix. This fix will show up in a future release of DACFX/SSDT.

Thanks,

Adam

---

Adam Mahood - Program Manager - SQL Server Data Tools

Answer 16

I am seeing this issue and assuming it has the same cause (computed persistent columns mid-table). My SqlPackage scenario, however, is DACPAC <-> DACPAC. When using Schema Compare, it sees a difference, but doesn't highlight anything in the definitions as changed. I have installed CU1 of DACFX, but to no avail. Also, I can not get Schema Compare to detect any difference between DB and Project.

Answer 17

Is this fix publicly available yet? Can I get it if I open a PSS case?

I'm having this exact problem. From the work arounds above I tried option 1 (use schema compare to reimport/overwrite the table). This just put my table and keys into one file rather than the seperate ones I had (I converted from VS2010).

Option 2 (move the column to be the last column in the table). This stops the table rebuild but I have a nonclustered index on this column as well. It now drops and recreates the index. As this is such a big table in my case I can't ignore it.

Thanks

Simon

Answer 18

Simon,

This fix is not yet publicly available. However, it is fixed in the payload for our upcoming DACFx release, so look for the fix in the near future. I will update this thread whenever this release is available.

Thanks,

Adam

---

Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

Answer 19

I have a similar issue with computed persistent columns as well. But it only occurred when the function was inline in the table definition. If I moved the logic into a separate function, it no longer would constantly rebuild.