Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including:
- Warming up SQL Server’s buffer pool by selecting all the data from all the tables
- Creating indexes with triggers
- Adding nonclustered indexes to clustered columnstore indexes
- Compressing backups the hard way
- Putting databases on a RAM drive
In this 20-minute video, I’ll explain what drove me to these crazy solutions, and it’ll be up to you to decide whether they’re awesome or awful.
For questions & answers about these, tune in to our weekly webcast.
9 Comments. Leave new
Due to hard drive space, to store last night’s backups… we do not have any third party “SQL Server” backup software. We backup a database and then use winzip to compress, and then we move the zip file over to our file server… and move to backing up the next database. Not the best solution… IMHO, but it is cost effective.
David – interesting! The trick there is that the databases will be pretty far out of sync at restore time, since the backups are done so far apart. As long as the business is okay with that, though…
Are you using sql 2005? I could understand winzip then. Had to do it myself that way. Slow, but it worked. Also striped it, which sped things up a good bit, too. Can you back up to a file share instead of locally? Combining those might speed things up if that’s an issue.
Remind me of why TAR was invented and why I use RAR and UNRAR.
RAR (and even more so RAR64) is faster than win-zip and IMHO easier to script.
Very interesting video!
I love your shop. Although I enjoy and use all the free stuff you provide, I worry some whether you are getting enough revenue to pay yourselves properly. I certainly hope so!
I appreciated the “create indexes through triggers” hack, along with its gotchas. It’s a clever solution to a real problem – especially when you understand “out of support” and that the vendor isn’t responsive. Of course it’s not the best solution and has a LOT of possible pitfalls, but sometimes it’s the best option for what you’ve been given.
as someone who consistently uses the phrase “bad idea jeans” despite the blank look it invariably elicits, i must say i loved the title of this post.
re: ramdrives–i was once in a situation where tempdb was the bottleneck and literally every slot/drive bay on the server was full (using all local storage, of course). the server had 128GB of RAM and was running SQL standard (on top of Windows enterprise). the quickest (and most economical) fix turned out to be replacing all the ram with double capacity DIMMs (which had since come out) and using all the extra memory as a RAM Drive to host TempDB. The results were nothing short of magical.
I tested the Columnstore Indexed View trick. What I found most limiting (though logical) was that they do not support key lookups to the “main data store” because the CCI does not support lookups. So to be useful a B-tree index must be covering.
Doing this the CCI space savings are quickly lost.
This is probably one of the restrictions that the next SQL Server version will lift. Point (or small range) lookups on DB fact tables are important. it is ridiculous to assume that each query must scan 10TB of CCI data.
Great session!
Classic SNL:
http://www.hulu.com/watch/10310
Good