5

We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all Unicode and I have calculated a savings of 5G in space converting only 2 such fields averaging 206 characters each to non-Unicode, and almost 10G in space if we convert a few more of them from nchar and nvarchar to char and varchar types. These fields will never have a requirement to hold Unicode characters that cannot be in the SQL_Latin1_General_CP1_CI_AS collation as they come in as plain ASCII originally and will always do so per the protocol standard.

I’m the software architect and chief C# developer though only a DBA hack or I would not have designed our database to have Unicode fields for high volume tables that did not need Unicode for those fields when the database was created 3 years ago. I want to correct this mistake now before we finalize converting to an AlwaysOn environment to help with various performance and backup issues.

After downsizing these two or more fields, we would like to shrink the database one time to take advantage of the space savings for full backups, and for seeding an AlwaysOn environment.

Questions are –

  1. What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types? Esp. when there are multiple fields in the same table to be converted. I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server (4 virtual core, 8G memory) before running out of disk space even though there was 8G left on the disk, and the db has unlimited size set (Could not allocate space for object 'dbo.abc'.'PK_xyz' in database 'xxx' because the 'PRIMARY' filegroup is full). I did delete an old database before it finished after getting a disk warning so maybe it did not count that new space. Regardless it was too slow. And this was on just the two largest of these columns (12.6M rows) and only ran 2 to 3% CPU busy so seemed not very efficient, and indicated unacceptable downtime if we were to convert even these two fields much less any additional fields. Average field size for these two fields was only 206 characters or 412 bytes each. Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table. I have a FK and indexes to contend with on the table.

  2. If I figure out how to do #1 efficiently within an acceptable maint window, what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics? I understand the logic of not doing regular shrinks and that sometime it can actually increase the size.

  3. Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?

Any suggestions and best practices most welcome.

Thanks, Dave

5

Regarding:

I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server ... before running out of disk space ... it was too slow.

and

Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table.

Generally speaking, making a copy of the table with the ideal schema is my preferred approach. But, if you only now have maybe enough space to convert the two columns, are you sure you have enough space to make a copy of the entire table?

Also, the new table would just need to have a different name. It wouldn't need to be in a different schema.

Since you are on Enterprise Edition, have you considered or even looked at enabling Data Compression? Not only would it have the effect you are looking for on the NCHAR / NVARCHAR fields, but it would also save space on other fields with other types as well.

There are two types of Compression: Row and Page. You should read up on them and run the stored procedure that estimates what your savings would be.

Enabling compression can be done as an ONLINE operation, but might require a bit of disk space. If you do not have the available space for this, then you might could consider a hybrid approach where you build a copy of the table as TableNEW, and have the clustered index already created, and created with Compression enabled. Then you should be able to slowly fill TableNEW and the data will compress as it goes in. Of course, you will want to use INSERT INTO...SELECT to do it in batches. And the full benefit of the compression might not be realized until after you drop the original table and do a full rebuild on the Clutered Index of TableNEW.

And keep in mind that there are scenarios where you might not save that much space, or saving the space isn't worth the increase in CPU activity. But, that all depends on a lot of factors so it is really something that should be tested on your system.

You could always take the approach of:

  1. Enable compression, either directly to the current table as ONLINE (if there is enough space to support it), or into a separate table that has Compression enabled.
  2. IF you find that CPU actually has increased beyond the benefit of the space savings, then you have the option of building the table again with regular VARCHAR fields and no Compression. Because compression was already enabled, you should definitely have room for this now.

But again, like anything we do, it should be tested. I have heard for years how horrible "XML parsing" is on CPU, and how bad Compression is supposed to be, but in practice, those concerns are quite often overstated. The only way to know is to test on your system. (ps, just in case it is not clear being a text-only medium, these final statements are not attacking what @Kin said in his answer about needing to be cautious of CPU activity increase. He is correct, at least to a degree. I am just reminding everyone to be sure to put everything into perspective of current hardware and software and system setup).

  • 1
    Thx srutzky. We can add more space. This was just a test system and I almost ran out for this test, yet it too long even up to that point. I'm going to try a table copy approach next esp. since i have a few more columns on that table I would like to convert. And will experiment with compression as well to see what sort of time/space trade off there is. – Dave Oct 13 '15 at 2:26
2
  1. What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types?

You can use schema switch or add a nullable varchar(with proper length) column and batch (e.g. 1K or 10K) update the rows and once update is done, drop old column and rename the new column. At the end, rebuild the indexes.

Another method would be to create a separate database with the correct data type you want, bcp out data and bulk insert data into the new database and then rebuilt indexes and rename it to the older name (dropping the old database).

BCP in native format and bulk insert is extremely fast, since you will be doing it on the same server. This process assumes you have enough disk space. I have done a 400GB database bcp out and bulk insert in less than 20 mins. As usual, test your entire process thoroughly and you have to do this during your maintenance window.

  1. what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics?

If you are sure, your database is never going to grow to that size again, then shrinking it will give you that space. Make sure you have a sensible autogrowth setting (and keep the autogrowth in MBs not in Percent) and presize your data and log files (so they dont have to autogrow on frequent basis). Enable instant file initialization as well, so that data files can leverage its awesomeness.

  1. Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?

Not that I know of. I would say its not possible unless you are implying - doing a schema compare with your current database and to-be-database (the one that has proper data type) and generating scripts to sync. For that Redgate's schema compare is an excellent tool. I have used it and its a life saver !

I agree with above answer for using data compression - row or page, but it comes with an expense of CPU.

  • Kin, I've done the schema switch approach when updating other tables with new daily data and it works well, but they are large price lookup tables used during live processing and remain static until the new data arrives, so I update copies using bulk load and switch them. This table however is constantly live with a 20-30,000 Tx per day though slows late in the evening. I pretty much have to take my process down to do the conversion so the main concern is doing it as fast as possible. – Dave Oct 13 '15 at 2:39
  • The database will continue to grow but it would likely be 6 months until it reached its former size, if I can pare it down now, and by then I hope to have a daily data migration strategy in place to keep the live database more manageable. The instant init is interesting and even though consider myself an OS guy have never heard of that so will check it out. Funny how the trade off is usually time vs space. Thx for the suggestions and links. – Dave Oct 13 '15 at 2:43
  • I'll try various approaches for #1 but #2 is my main concern and after I to shrink, I'm not familiar with how to best deal with the indexes and statistics afterwards. Honestly they have only recently even been on the radar because the growth finally caught up with us and pushed the statistics update out too long (500 + 20%) and hit us when reporting off a restore of the live database took exceptionally long as compared to the month before until we updated statistics on one particular index. Updating all the indexes and statistics seems overwhelming and esp. I'm wondering how to approach that. – Dave Oct 13 '15 at 2:51
  • Dave you should look at ola's maintenance scripts. They will only defrag your indexes and update stats based on industry best practices. Let me know if you are still unclear, and i will update my answer tomorrow (its 12 midnight already). – Kin Shah Oct 13 '15 at 3:53
  • Is there a way to mark both as answers? Thx for the reference to Ola's sproc. – Dave Oct 13 '15 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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