SQL Server

When you migrate to SQL Server 2014, rather than turning on the new cardinality estimator right away, I recommend sticking with the old cardinality estimator initially by leaving your database in SQL Server 2012 compatibility mode. Let the database settle down for a few weeks, let folks air out their grievances, and give yourself some…

The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110: Transact-SQL SELECT ClosedDate, Id FROM dbo.Posts WHERE LastActivityDate > '2013-09-05 11:57:38.690' OPTION (QUERYTRACEON 2312); GO 12345 SELECT ClosedDate, IdFROM dbo.PostsWHERE LastActivityDate > '2013-09-05…

Quiz time! Want to test your knowledge on indexes and query tuning in SQL Server? Curious how your query tuning style compares to others? Looking to have a little fun? Interested in a coupon code? Your Monday just got better. Check out our new free online quizzes right now: Test your SQL Server indexing prowess…

When you work with the same database server day in and day out, you can get a bit blinded by your assumptions. You might just assume that the way SQL Server does it is how every database does it. But check out these features from other database platforms: Cache query results – SQL Server only caches…

If your application can sometimes connect to SQL Server – and sometimes not – it can be really tough to troubleshoot. If SQL Server doesn’t even hear the call, it can’t log any errors. Here’s the questions I ask to get to the root cause: When it happens, does it happen to all applications? For…

How did I ever get this to work???? Some people hate repetition. They want to avoid manual tasks at all costs. I used to be part of the cult of automation. I thought that everything should be made efficient. Why should I do something twice, if I can figure out how to do it once and…

There are a few little gems built into SQL Server Management Studio that are easy to forget, but they sure can come in handy. I sometimes generate a little formatted report of recent growths for a database, and how long they took. It’s really easy, and it’s built right in. Here’s how: Right click on a…

After the SQL 2014 SP1 mess, I thought, “How are people with a real job supposed to keep up with updates?” Go try to find the most recent SQL Server service packs and cumulative updates on Microsoft.com. It’s miserable – they’re scattered all over the place. Eventually, a lot of us started relying on SQLServerBuilds.blogspot.com,…

Developers – how much operations work are you doing? DBAs – how much development do you do? The Separation of Duties For most of us, we stick to our assigned job role. Developers write code and then throw it over the wall for the ops team to put in place. If there’s a problem, there…