SQLskills

Want to learn SQL Server from the best? Check out SQL Skills: https://www.sqlskills.com/sql-server-training/immersion-events-schedul… They are the best and most comprehensive. Not associated with them, but a long time, satisfied customer.

For Microsoft SQL server, I recommend reading all things what Brent Ozar [1] has to say There is also another blog from Dr. DMV on SQL Server performance [2] [1] https://www.brentozar.com/ [2] http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

> Coming from pretty heavy background in MSSQL internals, this article is really great. Do you know of any good resources for the undocumented function fn_dblog? I'm looking to understand the structure of RowLog Contents and Log Record in different Operations/Contexts to reconstruct DDL/DML. http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-… is a good example but is really just an introduction.

> Do a query, get back a handle to a file that you can treat just like you opened it yourself Things are a bit more complex. For one, the trivial problem of client vs. server host. The DB cannot return a handle (a FD) from the server, because it has no meaning on the host running the app. The second problem is that any file manipulation must conform to the DB semantics for transactions, locking, rollback and recovery. What you describe does exists, is the FileStream feature that dates back to 2007 if I remember correctly. I'm describing the SQL Server feature since this is what I'm familiar with. The app queries the DB for a token, using GET_FILESTREAM_TRANSACTION_CONTEXT[0] and then uses this token to get a Win32 handle for the 'file' using OpenSqlFilestream[1]. The result handle is valid for usual file handle operations (read, write, seek etc). There were great expectations on this feature, but in real life it flopped. For one it caused all sort of operational headache from the increased DB files size (increased backups size etc) or from problems like having to investigate 'filestream thumbstone status'[2]. But more importantly, adoption required application rewrite (to use the OpenSqlFilestream), which of course never materialized. File Tables is a newer stab at this problem and this one does allow to expose the DB files as a network share and apps can create and manipulate files on this share and everything is backed by the DB behind the scenes. But turns out a lot…

> SQL Server is one of the few commercial DB's that does real nested transactions Not sure where this myth keeps coming from, but no, it does not: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-…

I can give one example when SQL Server was purchased by Microsoft from Sybase. Overnight literally everyone claimed to be an expert. I did some research including asking the Microsoft employees I knew. It turned out there was one lady who did all the training at Sybase and who taught the Microsoft employees about their new database. That lady Kalen Delaney spoke highly of a husband and wife consulting team. The husband was ex Microsoft and designed some of the DBCC code. https://www.sqlskills.com/about/meet-the-team/

There is a nice post: SQLskills procs to analyze data skew and create filtered statistics [1] that links to a session where statistics skew is explained and some scripted solutions provided to kind of cover more rows with filtered stats. [1]: http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-analyze-data-s…

SQLServer uses it from version 2005 til 2012 to create databases snapshots in order to run DBCC CHECKDB (consistency check). So for actually a critical feature of MSSQL. I suppose this was the reason why ReFS was not supported for SQL data disks. It seems they are not used anymore since sql 2014. See for example http://www.sqlskills.com/blogs/paul/issues-around-dbcc-checkdb-and-the…

You're optimizing for write, but the performance gain is negligible in comparison to the performance loss due to index fragmentation and slow joins. Also, on "storage is cheap": http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/

200ms seems like a long time to me. From what I know, if your database takes longer than 100ms to return a query, theres something wrong[1] and CDNs have pretty much solved that "I need to get this static file around the world quickly." With just an song id, with S3/Cloudfront and [db of choice] I don't think it would be hard to deliver a song in less than 200ms (to the continental US atleast). [1] http://www.sqlskills.com/blogs/paul/are-io-latencies-killing-your-perf… - Random article, I'm not sure where I picked it up from, but I've come to believe >100ms db latencies should be rare and when it happens, you messed up somewhere.

There is a good reason why a GUID/UUID or the like should never be used as a database primary key when records are regularly added. The short answer is that the records are usually stored in the primary key order (clustered index) and every new record being inserted at a random location in the middle of this order rather than just added at the end causes problems. See http://stackoverflow.com/questions/583001/improving-performance-of-clu… http://sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-t…

I recall reading something about this in the PostgreSQL mailing list, message written in 2016 but may still be relevant https://www.postgresql.org/message-id/20151222124018.bee10b60b3d9b58d7… “There's no substance to these claims. Chasing the links around we finally find this article: http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-th… which makes the reasonable argument that random primary keys can cause performance robbing fragmentation on clustered indexes. But Postgres doesn't _have_ clustered indexes, so that article doesn't apply at all. The other authors appear to have missed this important point. One could make the argument that the index itself becomming fragmented could cause some performance degredation, but I've yet to see any convincing evidence that index fragmentation produces any measurable performance issues (my own experiments have been inconclusive).”