Something that i keep seeing in documentation, in forum code and in real systems is transactions that have names
BEGIN TRANSACTION VeryImportantProcess <do stuff> COMMIT VeryImportantProcess
Now the question came up on the forums a while back as to what the point of this is. Why would you name a… Read more
This is the second in a short series on transactions. In the previous part I discussed nested transactions and showed that they actually don’t exist. SQL has syntax which may suggest that nested transactions exist, but it’s just a syntactical lie.
One thing I suggested in that post was that… Read more
There is a particularly irritating and persistent belief that indexes (usually it’s the clustered that gets picked on) are always physically ordered within the data file by the key columns. That is, that the data within the database file is always ordered by the key column.
It doesn’t help that… Read more
There was a question raised some time back ‘If an index is not selective, will the query operators that use it always be index scans’?
It’s an interesting question and requires a look at what’s going on behind the scenes in order to answer properly..
Short answer: No, not always. Read more
It’s a question which has come up a couple of times. If a subscriber of a transactional replication publication becomes corrupt, is running CheckDB with repair allow data loss safe?
The theory is, since the subscriber is a copy of another database, allowing CheckDB to discard data in the process… Read more
I’ve come to really like Distributed Replay in the last couple of years. I’ve used it to do a scale test, I’ve used it to test a workload for performance regressions before upgrading. It has a lot of possibilities.
One problem with it is there’s no GUI, so configuring it… Read more
There are many, many guides to successfully completing a post grad degree, so I am not going to add to them. Instead, this is a list of things, based on personal experience, that you can do to make it somewhere between very difficult and impossible to complete that Masters or… Read more
By now I expect this news is no longer new: the MCM, MSA, and renamed versions of those certifications are no longer being offered after Oct 1, 2013. The announcement was made in email, late on Friday night is the US, which was Saturday morning for those… Read more
Right, I know it’s Friday and everyone’s tired and looking forward to the weekend, but I do need to finish off this indexing section and I’ll try to keep this short and interesting and hopefully keep everyone awake.
There’s no shortage of information available on how to create indexes. Hell,… Read more
Welcome back to day 2 of Advanced Indexing. Today we’re going to look at a feature that was added in SQL Server 2008 – filtered indexes.
In versions previous, indexes were always on the entire table. An index would always have the same numb of rows as the table it… Read more
Good day everyone and welcome to another week of SQL University. I know we’re getting close to the end of the year and everyone’s looking forward to a nice long vacation soaking up the sun at the beach, but a little bit of attention would be nice. Thank you.
This… Read more
I finally found the time to work through the questions from the 24 Hours of PASS session that I did. Thanks to everyone that attended the event
Q1: Can you filter execution plans for sort warnings?
No. The sort and hash warnings don’t appear in the execution plan. You’d have… Read more
I want to spend some time over the next few months looking at query compilation and the plan cache, and there’s a couple concepts that I want to get cleared up beforehand. The first of those is around two terms that are often used interchangeably, compile and recompile.
A… Read more
Coming to the PASS Summit in October this year? Excellent!
I say excellent, because Grant (blog|twitter) and I are presenting a full-day seminar on the Monday, all about execution plans. Excited already? If not, let me give you a taste of what we’ll be discussing. Read more
Just short of the winter solstice, I bailed out of a freezing cold Johannesburg for warmer climates; well, actually for London where the weather could almost have been mistaken for a South African winter, except much wetter.
However I wasn’t going to London for the weather (fortunately), nor even for… Read more
When I looked at indexing for queries containing predicates combined with OR, it became clear that the are some restrictive requirements for indexes for the optimiser to consider using the indexes for seek operations.
- Each predicate (or set of predicates) combined with an OR must have a separate index
This should be another quick one.
Earlier I saw a forum post where someone asserted that SQL always executes an update as a delete of the row and then an insert of the row with the new values. Now I need another excuse to play with undocumented features, so let’s… Read more
I’m in the fortunate position of living in a region of the world that’s relatively free of natural disasters. We’re reasonably geologically stable, the nearest fault lines… Read more
There was a question earlier on one of the SQL forums as to whether or not Ghost Cleanup overwrote the deleted rows when it ran so as to ensure no one could read that data again.
I blogged a week ago about the sessions that I submitted for the PASS Summit this year. At the point that I scheduled the post I hadn’t seen the announcement from PASS, hence a second post on the same topic.
This year, you can vote for sessions that you want… Read more