SQL Server – When is a new database appropriate?

I frequently get requests from our developers to create new databases.  This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement.  However, something that usually isn’t considered at the point of a request is: why do we need a new database?

Continue reading

SQL Server – Delayed Durability and Log Flushes

This one stems from a an interesting discussion around Delayed Durability and In Memory OLTP on LinkedIn, looking at how Delayed Durability might be the winner for performance that In Memory OLTP is billed to be.  I think they have different purposes, but simply to improve throughput on specific workload types involving very high volume single record transactions, Delayed Durability could be a viable performance winner for you, with one caveat: you absolutely must be comfortable with some data loss.

Continue reading

SQL Server – Understanding the TOP operator and implications for batch processing

I was debating using a picture of Kylie Minogue in this blog post, from her video for the song “Spinning Around”.  Because it is relevant, and not just because it’s a picture of Kylie….

kylie-289808
Kylie likes loops

Anyway.  Today, we had a problem where “all of a sudden” CPU was maxing out on a server, and the disk I/O was going through the roof.  Uh-oh, I thought, today is going to be rubbish.  Rolling up my sleeves, I opened up SQL Sentry Performance Advisor to have a look what was going on.

Continue reading

SQL Server – Top 5 things I learnt at SQL Skills’ IEPTO1 course

Last week, I was fortunate enough to be on SQL Skills’ Immersion Event on Performance Tuning and Optimization Part 1 with Paul S Randal and Kimberly L Tripp.  I’d highly recommend the course, which was excellent albeit a little tiring with five full in depth days diving into SQL Server’s internals.  

Continue reading

SQL Server – Another reason why using SELECT * is bad for performance

I think it’s fair to say that most database professionals will assert that SELECT * is bad practice.  For lots of reasons: code maintainability, it’s lazy, prevents being able to properly use covering indexes.  However, a recent Linked In post by Mike West made me look into whether there was any performance concerns when using SELECT * versus selecting a column list when there aren’t any supporting covering indexes.

Continue reading