Why is SQL Server doing a clustered index scan?

This is a simple real life scenario I ran into recently. Client is experiencing performance issues with SQL Server. A quick investigation reveals that a certain table, let's call it MyTable which contains over 50 million rows seems to be the source of contention. In particular there is a frequently run, innocent looking query that is causing the trouble. The query looks something like this

SELECT c1, c2 FROM MyTable WHERE MyTable_PK BETWEEN 5 and 9

The execution plan shows that the SQL Server query optimizer has decided to do a clustered index scan on the primary key (terribly expensive on a large table like this). Why can't the query optimizer "see" that an index seek would be significantly more efficient than the scan? Upon closer examination I realized that an implicit conversion of the PK is happening. Now I can see the light – I check the table structure and realize that the Primary Key column has been defined as a varchar and that is being compared with integer values. That is what's "tricking" the query optimizer into picking the wrong plan. A little tweak:

SELECT * FROM MyTable WHERE MyTable_PK IN (' 5', '6', '7',' 8', '9')

Success - now instead of scanning 50 million rows the query optimizer has picked the right plan, that is, it is doing a quick index seek and getting the rows we need in a fraction of the time. All the contention disappears – everybody is happy!

Note: the real query was much more complex and the tweak is not necessarily as simple, but the point here is to highlight the fact that if the type of the index column does not match the type of the values with which it is being compared then the index is rather useless.

Documenting your databases – one tool does it all

Documenting anything is usually a painful task that everybody talks about but no one likes to do. I have seen one client after another with virtually no database documentation and no awareness of the incredible amount of time that especially newcomers spend on understanding how the objects relate to each other, how the data is stored, and what kind of constraints they need to be aware of!

Here is a perfect excuse that I have heard way too often: “by the time we are done documenting things have already changed so the documentation becomes outdated before it is even completed”. That might have been true in the distant past but not any more. With the right tool you can document your databases like SQL Server, DB2, Oracle, MySQL, Informix virtually any platform, in minutes – you can even create a job that automatically updates the documentation every day. One of the best tools out there to do the job is xSQL Documenter which allows you to simultaneously generate compiled CHM and / or HTML documentation for all your databases.

What’s even better, a great tool like this does not cost a fortune – for less than $200 you will get an incredible tool that will save your organization hundreds of hours of work.

xSQL Documenter supports the following platforms: SQL Server, Oracle, DB2, MySQL, Informix IDS, Sybase ASE, Sybase SQL Anywhere, PostgreSQL, Access, VistaDB, ENEA Polyhedra;

You can download your copy of xSQL Documenter from http://www.xsql.com/download/database_documenter/

Is there such thing as free software?

The market is flooded with free software, or is it? Every software publisher out there is trying to lure the customers in with the promise of free software. So much has been the “free” aspect emphasized that it has created the illusion that software is cheaper than water – you don’t really need to pay for it unless you are too lazy to search! It is mind boggling to see the percentage of google searches (at least those that hit our site) that have the “free” word as the central piece: free sql server tools, free database comparison, free database deployment, free “anything”… Go to craigslist and you will be amazed at how many postings are soliciting free help to build a website or do some “minor” database work or whatever else. Not only that but some posters have the audacity to present those shameless solicitations as like they are doing a favor to the software developer who in their mind must be looking to “enhance the portfolio”.

Be that as it may, when you hear “free software” keep in mind that it does not always mean the same thing – often you may end up paying through your nose for the "free" software.

Let's take a closer look - there are 4 main categories of what's advertised as free software (not talking about open source here – that’s a whole other discussion):
- the spyware, adware, virus infested software developed by despicable low forms of life (I have strong opinions as to what should be done with those people but that is a subject of another discussion) – what is clear here is that you are paying a very hefty price for the “free” software;
- free version with limited functionality and no expiration – the intention of the software publisher in those cases is to let you “take a free bite” of that great dish that you could have for a price. The hope is that once you taste it you will not be able to resist paying for the dish. I think this is ethical and great way to lure the customers but is this really free software? Is a great free bike with a missing pedal worth much if to get the missing pedal you have to pay the full price of the bike? You can certainly see how great the bike is, you can even get on it and ride it pedaling with one foot but it does not cut it does it?
- fully functional trial with expiration – the intention of the software publisher in those cases is to let you have a “free lunch” in hopes that you will like it so much that you will want to come eat here everyday despite having to pay for it. Is it ethical – yes, I don’t see anything wrong with it. Is it really free - yes it is – you are getting the benefit of using it for 2 weeks, or whatever the trial period is, with no restrictions. Is it effective? It depends on the type of functionality that the software provides. In case of an infrequent task the potential customer may take the free trial and finish the job now and two months later when he needs it again simply gets another trial version under a different name, different hardware etc. – so it may never turn into a customer.
- free software with no strings attached – the intention of the software publisher in those cases is to build brand awareness and make you a customer albeit a non paying one. An analogy would be that of a grocery store deciding to give the milk out for free – yes there is a possibility that you may forever go to that grocer and get the milk but never purchase anything, but chances are that once you see how professional and courteous they are, how great their service is and how good their other produce is chances are that at some point you will become a paying customer. Is what you are getting in this case really free – yes, absolutely. Is it effective for the software publisher? It really depends on how much patience the vendor has – it will eventually pay off but it may take a long time.

So where do we stand on this? Well, just have a look at our product offering and you will see that we favor the “free with no strings attached” approach:
- our xSQL Object for SQL Server schema comparison and synchronization and our xSQL Data Compare for comparing and synchronizing the data in two SQL Server databases are free with no strings attached for SQL Server Express edition. You take them and use them – no annoying reminders popping up on your screen, you use them free and clear.
- Both of the above products are also completely free for other editions of SQL Server as long as the databases you are working with do not exceed certain number of objects. To continue the milk analogy this translates in something like: “if you don’t need more than 1 gallon of milk per day you can get it free always but if you need more than 1 gallon you will have to pay for it”.
- Our RSS Reporter for SQL Server, a great tool that allows you to get an rss feed with job status information or even have the results of any T-SQL query “fed” to you in the form of an rss feed, is also free with no strings attached for one SQL Server. Same analogy as above applies here.
- xSQL Builder which provides for automating deployment of SQL Server databases is the only tool on our line up where we have gone for the fully functional free trial approach.
- xSQL Script Executor which allows you to execute multiple T-SQL scripts in one big transaction is a completely free tool as is the xSQL Object Search which allows you to search for objects the name and /or definition of which meets some search criteria.
In case you want to check any of those products out the links are on the right panel.