Before speaking at the Data TLV Summit, I sat by the Mediterranean Sea and discussed the top-voted questions you posted at https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 00:55 Manikandan GS: As postgres being one among the RDBMS lane, do you think postgres is having more features and flexibility compared to MS-SQL Server?
- 02:46 PaginationWoo!: Hi Brent. I use Ola’s script to update stats nightly. Other than time spent and % CPU used, is there a case where updating stats with fullscan would be a bad thing? I’m fortunate in that I have a long maintenance window. If I have time for fullscans, wouldn’t it be worth doing?
- 04:42 Adza: I had the thought of creating a date table with all dates ranging from 2010 to 2070 and have a smallint key for each date. Then in my tables I can store smallint values instead of actual dates. This could be a huge data saver (2 bytes vs 3 bytes). Smart or stupid? 05:53 Eyes Without a Face: Is there a portable version of the First Responder Kit? Our customers are mainly banks and they are not keen to install Stored Procedure. Not even the First Responder Kit
- 06:44 Dawson from Dawsons Creek: Do you have any clue why Microsoft suggests to use a windows collation for new development work but the default server collation for English(US) is still a SQL collation? For all other Windows locale the default server collation is a windows collation.
- 08:07 StillLearning: Hello Brent, as a Dev DBA I’ve recently became increasingly frustrated with computed columns and their limitations (filtered index…). Should I advise the dev teams to refrain from using them and do the computation in the app instead ? Thanks
- 09:26 Haurun: What are the pros/cons of installing Ola Hallengren maintenance script tables / sp’s in master database vs some kind of maintenance db?
- 10:42 Milind: Hi Brent, Thanks for your time. During execution of certain queries or SPs I am observing high logical reads. Where should I look to make sure it is appropriate or required improvement? Unfortunately, I haven’t completed all the training modules and asking this question.
- 12:06 TheCuriousOne: Hi Brent! If you have to work with local SSDs in a server for your SQL setup, is it better to have one big array with OS side logical separation through partitioning or to have physical separation by setting up smaller disk-sets with various RAID configurations?
2 Comments. Leave new
Another point about Adza’s question: think ahead to possible partitioning needs in the future. Sure, you might not see such a need now, but if it arises it is likely to involve a sat (transaction or creation) and so don’t code yourself out of that possibility in the future. All for one byte.
This “date, or a key from a date table” debate is a common one among those who use Star Schema DW designs. In fact, in my experience the majority of Star Schema designs have a date dimension table with a surrogate key used by the fact table. In those case, I prefer — and have been called all kinds of names over it! — to use the date itself as the key. In large part because of the possibility of partitioning needs later (especially in data warehouses where partitioning is often employed).
One thing I would add to TheCuriousOne’s question about physical drives is it comes down to making sure your drives and the storage controllers aren’t a bottleneck AND you can recover from hardware failures quickly. If you’re only using a handful of disks, then physically mark their installed order and go for it as Brent suggested.
If you’re working with more than a handful of disks, I would suggest setting up two physical partitions (vs logical).
The first as mirrored set for the OS and the second physical partition for all the db related stuff with a raid configuration that works with your performance and failure tolerance requirements.
The reason I like the OS on a separate physical partition is I’ve had a motherboard fail on a sql server and had to remove and replace all the disks. Knowing (and marking) which disks had the OS/MBR means I was confident I could at least boot up the server and not have to go back to a bare metal recovery. Even if I couldn’t figure out the proper order for installing the rest of the drives, I could format and restore the database from backup. The key to all of it is documenting your storage setup in such a way that someone else would be able to repair the server from the ground up without having to call you.