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 – Why storing dates as strings is a bad idea

Consider the following value: ’01-02-10′.  Is it the 1st February 2010?  Is it the 2nd of January 2010?  Is it the 10th of February 2001?

All three of those answers are potentially valid, and you can convert that string into all three:-

DECLARE @DateString CHAR(8) = '01-02-10';

SET DATEFORMAT DMY;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT YMD;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT MDY;

SELECT CONVERT(DATE, @DateString);

whichdateisright

71902217

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 – Odd behaviour with cached temp tables, a linked server and MSDTC (failing)

I recently encountered some strange behaviour occurring in a production system, whereby a procedure called from an application failed due to the error “unable to enlist in a distributed transaction”.  However, running the procedure from SSMS worked fine, and led to the procedure working again without problem from the application.

headscratch

Continue reading

SQL Server – Security: Schemas, Ownership Chaining and the dreaded TRUSTWORTHY flag – Part 1

soapbox_man44_t-147x265_zpsa987e01e

Warning: this post involves me standing on a soapbox: it’s going to be a bit of a rant/preach about why everyone should care about security!  Starting with….

SCHEMAS

When I talk to my developer colleagues, very few of them realise that database schemas are actually a security feature of SQL Server.  They also don’t appreciate that security in a database needs to be designed, it doesn’t just happen.  If it’s considered from an early point in the design of the database and application, then it will be much easier to implement and cause them less headaches in the long run when they ask me for some elevated permission or other and I say “no, because….” and start explaining about ownership chaining and a whole bunch of other security related stuff that makes them roll their eyes at me…. ahem, I digress.
Continue reading