 silence. It was lonely, so I made these songs. 'Glad to Be Here' is where I find myself right now. 'Party Dawn' is tied to Maryland, to a dear friend and our adolescence," says Von Schleicher. Bringing the songs back to New York, she finalized them with collaborator Adam Brisbin (Sam Evian, Jolie Holland, Buck Meek) and mixed them with Julian Fader (Ava Luna, Frankie Cosmos, Nadine, Palehound). UK import. Limited to 20 US copies.Connecting busy Lambton Quay and the tranquil Botanic Garden, Wellington's cable car has been a popular mode of transport for over 100 years. Built to connect downtown with the expanding suburbs, the cable car is also popular with tourists seeking maximum views. Photograph the dramatic city and harbor backdrop, tour the small museum detailing the history of the cable car and then make your way back down via tree-lined walkways or another ride on the funicular. There's also a free shuttle bus to the Zealandia wildlife sanctuary.Do you have quite large MDF files on your database? By large, I mean hundreds of gigabytes (or larger). Have you ever noticed that your SQL Server disk stall metrics for these data files are much higher than the storage latency metrics exhibited on the underlying operating system layer? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Why don’t we do this enough for our user databases as well? It’s easy for a brand-new database from day zero, but what about existing databases that have grown out of control with a single data file attached? Let me show you how to adjust this for existing databases without an outage!
I’ll be taking the SQL Server database generated by the HammerDB benchmark, appropriately named tpcc_test. It’s just a backed up and restored copy of the (also appropriately named) database tpcc_single to represent a single MDF file present. If you’ve never used HammerDB to stress-test a relational database, I have a how-to guide on my site here.
I did a restore onto the F: drive of this server. The MDF size is rather substantial at 420GB. The log size is inconsequential for the purposes of this task. your internal fill size on the MDF, divide by the number of datafiles you want total, and round up a smidge to the nearest megabyte. If the database is quite active with a lot of data being added, you might want to add a bit more just to have it balance by the time we’re done with this task.
(Thanks Glenn Berry for the diagnostic queries!)
Now is the time to determine if these should be placed on the same underlying disk or if they should be distributed to more disks. I’m doing that on this server so that I, and this test lab is on all-flash storage. Let’s place two of these files on each disk with the fourth holding the last NDF with the MDF.
See the little trick that I did here? Set the maximum file size of each of the new NDF files to be that percentage of the current main file space consumed.
Note: This could also be a good time to think about additional file groups with more data files. You could elect to relocate items such as hot and highly active tables, nonclustered indexes, auditing tables, etc. However, if you’re tied to the database design, such as with a third-party application vended database, you might be stuck with the architecture of a single primary file group. That’s the aspect I’m taking with this test, as the HammerDB database is technically from a third-party piece of software. The file structure on disk changes but very little changes outside of this adjustment. In a perfect world, the primary file group is reserved for metadata and all user data goes into one or more new file groups, but that’s a different topic for discussion, as I know almost all of us work with databases we’ve inherited.
Now here’s the fun part. We’re going to use the little-known command to empty the MDF file. In theory, it’ll drain the file completely, but since we capped the file growth of the NDF files that we attached to limit their growth rate, this command will run until we have filled the NDFs, then fail. It will take a while to perform this operation, but it keeps the database online while it runs. You might have a bit of a performance hit from the CPU and I/O impact of this running process, so be wise in when you chose to run this operation.
You can watch the process work with the internal used space query listed above. You’ll notice the primary MDF is draining while the other NDF files are slowly filling up.
Again, it does take a good amount of time for this process to finish. Give it time – it will eventually complete. When it finally “completes”, the EMPTYFILE command will fail because of the maximum file size limit on the NDF files.
FYI – Index optimization routines can cause a circular block with this process that I have encountered twice while running this process, so keep an eye out if you see this occur.
At this point, your internal data consumed space rates are almost identical.
Verify that your autogrow rates are now the same between the data files. Remove the file growth limit on the NDF files.
Perform a one-time shrink of the primary MDF file to shrink it down to where it is the same size as the others.
It will be most likely be veryrowth for all files at the same rate if you have not already done so. This could mean that the database users might need to be punted from the database to enable the change, so do this during a maintenance window.
Sample your SQL Server disk stall rates and see what different you experience once this goes live.
Also, check if you get an improvement in SQL Server database backup times. If you can write as fast as you read and transmit, you might just get a bump in speed! Note that this is a backup to NUL test, so I’m testing just read performance and not actually taking a real backup. My normal network-based backup target cannot write as fast as it can read from flash disks, so I wouldn’t see any real improvement in backup times in my test lab.
I hope this helps you improve the operational efficiency of your SQL Server database data files, and you get better performance as a result. Let me know how this works for you!
Trackbacks/PingbacksWe didn’t always go to the movies on Thanksgiving. It was a holiday to spend with your family, and the bonding festivities were to last the whole day through. For better or worse. Now we escape our family, or at least their objectionable conversation topics, by sitting in a theater, where no one can talk politics or put down each other’s life choices, and staring at pretty much anything available on the big screen for a couple of hours.
But Thanksgiving shouldn’t be a holiday for escapism. Leave that fluff for Christmas. The whole point of the observance of Thanksgiving is to express gratitude for our blessings – or, for the less-religious, simply anything we hold dear in our lives. It’s about looking at our real present situation and acknowledging what we do have, not what we wish for. And it’s a day to recognize that we could all be less fortunate.
Fifty-five years ago today, CBS aired one of the most famously impactful documentaries of all time, Harvest of Shame. The nearly hour-long film was made by Edward R. Murrow and Fred W. Friendly, best known now as the guys portrayed by David Straithairn and George Clooney in Good Night, and Good Luck, and it exposes the plight and poverty of migrant workers of the time.
It was an eye-opening report for Americans, who were shocked to see conditions that seemed to be recorded during the Great Depression, actually filmed in the present age of general prosperity in the United States. Millions tuned in because there weren’t many other options back then. News and other serious nonfiction programs could reach and influence a lot more people.
Harvest of Shame was broadcast the day after Thanksgiving, a time we now call Black Friday and associate with shopping. That year in particular it was more a day to consider an important issue relevant to the spirit of the holiday. As producer David Lowe later admitted, the idea was to show the more fortunate Americans where much of their Thanksgiving dinner came from. It worked.
Well, it didn’t change the world, but it was something. Poverty and poor conditions for migrant workers never went away, of course, though some improvements were made by Congress, thanks to the documentary’s help in lighting a fire under some existing legislation. Not everyone appreciated the editorializing “news” of Harvest of Shame, and other critics have denied it had any worthwhile impact at all. That it could raise awareness and provoke a conversation in so many homes across the country, however, is still remarkable compared to what’s possible now.
No network is going to capture such a large audience with a single documentary these days, but it’d be great if Americans could take it upon themselves to honor the legacy of Harvest of Shame and devote their post-thanks-giving with some serious recognition and contemplation about those outside their sphere of personal appreciation. We can still have a tradition of going to the movies, but make it a nonfiction