Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

and other pointless query rewrites

In a similar vein to last week’s blog post… I heard an interesting comment recently. “Change that Column != 2 to a Column > 2 or Column < 2 combination, it can use indexes better.”

Sounds like something that clearly needs testing!

I’ll start with simple numbers table.

CREATE TABLE…

Read more

0 comments, 122 reads

Posted in SQL In The Wild on 9 February 2016

On the addition of useless where clauses

I remember a forum thread from a while back. The question was on how to get rid of the index scan that was in the query plan. Now that’s a poor question in the first place, as the scan might not be a problem, but it’s the first answer that… Read more

3 comments, 1,652 reads

Posted in SQL In The Wild on 2 February 2016

Does an index scan always read the entire index?

No.

That’s a bit short for a blog post, so let me explain. First, the difference between a seek and a scan.

A seek is an operation which navigates down the index’s b-tree looking for a row or for the start/end of a range of rows. A seek requires a… Read more

5 comments, 120 reads

Posted in SQL In The Wild on 26 January 2016

Stop using Task Manager to check SQL’s memory usage!

There’s two fairly common questions I see on the forums around SQL Server’s memory usage. Either the question asks why SQL’s using too much memory, or why it’s using too little.

Too much memory isn’t usually a real problem, it’s often due to max server memory being left at its… Read more

4 comments, 144 reads

Posted in SQL In The Wild on 19 January 2016

Natively compiled user-defined functions

One new thing that SQL Server 2016 has added is the ability to natively compile user-defined functions. Previously, native compilation, part of the hekaton feature, was limited to stored procedures.

When I saw that, the first question that came to mind is whether natively compiling a scalar function reduces the… Read more

10 comments, 1,155 reads

Posted in SQL In The Wild on 12 January 2016

Book Review 2015

It’s been a few years since I wrote a review of books I’ve read, so this isn’t going to list everything I’ve read since 2012, just a few of the best books or series. For the entire list of all books, see the library page.

So, 2015. I set… Read more

1 comments, 1,265 reads

Posted in SQL In The Wild on 5 January 2016

When naming transactions causes an error

For the last part of the series on transactions, I’m going to look at a problem that I ran across entirely by chance while doing some performance tuning. A procedure had a transaction in it, and it was named. Something like this:

CREATE PROCEDURE RemoveUser (@UserID INT)
AS

BEGIN TRY…

Read more

4 comments, 1,501 reads

Posted in SQL In The Wild on 15 December 2015

Why would you want to name a transaction?

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

9 comments, 2,027 reads

Posted in SQL In The Wild on 1 December 2015

Savepoints and conditional transactions

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

3 comments, 1,181 reads

Posted in SQL In The Wild on 17 November 2015

A Mess of Nested Transactions

Transactions are an area that I often find are used badly, or not used at all. Transactions without any error handling, nested transactions, named transactions named for no good reason or just plain lack of transactions where they should be used.

This is going to be the first of three… Read more

2 comments, 43 reads

Posted in SQL In The Wild on 3 November 2015

Of clustered indexes and ordering

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

10 comments, 2,244 reads

Posted in SQL In The Wild on 20 October 2015

Index selectivity and index scans

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

1 comments, 1,837 reads

Posted in SQL In The Wild on 6 October 2015

Repairing a replication subscriber

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

2 comments, 400 reads

Posted in SQL In The Wild on 22 September 2015

Unusual errors with Distributed Replay

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

3 comments, 212 reads

Posted in SQL In The Wild on 8 September 2015

How not to do a Masters degree

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

6 comments, 237 reads

Posted in SQL In The Wild on 11 August 2015

It's not you; it's us

Mirrored from http://www.sqlservercentral.com/articles/Editorial/101894/

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

0 comments, 1,351 reads

Posted in SQL In The Wild on 4 September 2013

SQL University: Advanced Indexing – Indexing Strategies

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

0 comments, 1,209 reads

Posted in SQL In The Wild on 11 November 2011

SQL University: Advanced Indexing – Filtered Indexes

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

2 comments, 1,334 reads

Posted in SQL In The Wild on 9 November 2011

SQL University: Advanced indexing – Sorting and Grouping

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

3 comments, 1,464 reads

Posted in SQL In The Wild on 7 November 2011

24 Hours of PASS Questions

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

1 comments, 548 reads

Posted in SQL In The Wild on 27 September 2011

Older posts