SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query -- it's the memory also known as Query Workspace Memory. Start reading more here.
How hard is it for a systems administrator who's used to running SQL Server on Windows Clusters to tackle Availability Groups? Our example system administrator knows a bit of TSQL and their way around Management Studio, but is pretty new to performance tuning. Well, it might be harder than you think. First, let's look at…
An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let's test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it's "unsafe" to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn't seem like it should have to be there. The weather might be cloudy.
It's tempting to think that table partitioning will improve query performance. After all, it's an Enterprise Edition feature-- it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…
5. Turning on auto-shrink It might make disk space alerts go away for a minute, but it can cause lots of problems. 4. Enabling xp_cmdshell and giving the SQL Server account administrative rights in Windows Young DBAs often do this to try to get a quick fix in place to manage backup files. Later on,…
... is to use a SQL Server where a robot does it for you! Let's take a look at how you can do this in AmazonRDS's hosted SQL Server these days. Normally, restoring transaction logs is super tedious. You've got to restore all the files in the right order, for every single database. Even if…
I've worked with source control quite a bit over the years -- everything from ye olde Visual Source Safe to Subversion to TFS. I even supported the Source Depot system at Microsoft as an engineer for a year back in the day!
These days I don't use source control a ton. We keep repositories of scripts in GitHub to help manage our scripts as a team, but I don't really use it often enough to learn the command line well.
At first glance, SQL Server's transactional replication seems like it's useful for moving data around in all sorts of situations: it works in Standard Edition, it's not synchronous, and you can have multiple subscribers.
Why People Want Replication to Test and Pre-Production Environments
But Kendra, it can't be that hard... after all, we have synchronous modes in Database Mirroring and Availability Groups, right?
Synchronous Commit doesn't mean "zero data loss"
When we think about limiting data loss, the first thing we think of is a technology that lets us reduce points of failure. If every transaction must be written to two separate storage systems, we have a pretty good chance to have no data loss, right?
Update 2016: Microsoft recognized this problem, and added a new way to add powerful query hints to SQL Server 2016.
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:
We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here's a little glimpse into what we talked about.
For really simple queries, SQL Server can use "trivial optimization". If there's a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!
A question came up on Twitter recently-- how you can you figure out where a rogue backup is coming from?
I've run into this question with clients before. In an enterprise environment where backup tools come and go, it's not unusual to find backups being run by some mysterious account.
Warm up your keyboard, it's time for a contest.
The challenge: Write the creepiest sentence you can come up with. You get 10 words max.
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 then just run a script or push a button?
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:
Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?
There's a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:
Have you ever wondered if you should be a SQL Server Database Administrator? If you think this might be the right career path for you, watch this video and take our quiz!
Got limited time or need to restart the quiz? No problem, we've got a table of contents with time indicators below.
When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries.
But what if you use 'included' columns in the index? Do they get information recorded in the statistics?
It sure can.
The lock risks of shrinking data files in SQL Server aren't very well documented. Many people have written about shrinking files being a bad regular practice-- and that's totally true. But sometimes you may need to run a one-time operation if you've been able to clear out or archive a lot of data. And you might wonder what kind of pains shrinking could cause you.
