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.

Script Executor for MySQL free today

Today and tomorrow only (February 15-16, 2011) you can get a free license for Script Executor for MySQL - simply plug in the MYSQLEX11 discount code on the shopping cart page - the price will go down to $0 and you will not be asked to provide any payment information. One license per customer only please.

Script Executor is one of the best tools for deploying t-sql scripts to your database server "farm". Whether you are running MySQL, DB2, SQL Server or a combination of those platforms, Script Executor allows you to easily create deployment packages involving hundreds of scripts that have to be executed in a certain order targeting as many servers as you need on any of those platforms, simultaneously.

Download a free, fully functional trial version and see for yourself what you have been missing until now: http://www.xsql.com/download/script_executor/

Don’t rename SPs, UDFs and Views – drop and create instead

When developers at client X need to make changes to an SP (let’s call that SP1) they usually go through those simple steps: 

1. Script SP1 out as CREATE
2. Right click on SP1 on the left panel and rename it to SP1_backup
3. Work with the script and when done with the changes execute it to create the new SP1

If you try this in 2005 you would get an error telling you that the SP1 already exists in the database even though it was just renamed to SP1_backup! There is a perfectly good explanation for this on a Microsoft kb article – basically when you rename an object like a stored procedure, user defined function or view the name of the object in the catalog is changed but the name of the object in the definition of the object is not changed! While this is annoying, at least it stops the user from making a complete mess.

With SQL Server 2008 Microsoft decided to eliminate this little annoyance, so now step 3 above will work just fine, the new SP1 is created successfully and you will be happy until you decide to look into SYS.sql_modules and realize that you now have two SPs with identical names in the definition. Fine, that’s not the end of the World you say. But just for a little fun try executing the sp_refreshsqlmodule 'dbo.SP1_Backup' - this should be completely harmless, simply updates the outdated metadata right? Well, try looking into SYS.sql_modules again after you execute that – surprise, all the work you did on SP1 just disappeared! Now, the definition of the SP1 is exactly the same as that of SP1_backup. Definitely not what you wanted or expected!

The renaming issue has been there forever – they (Microsoft) must have a pretty good reason for not fixing it, so I am assuming it will never be fixed. In the meantime the advise to SSMS user is: when it comes to SPs, UDFs and Views just pretend that the rename functionality does not exist – if you want to rename such an object script it out, change the name in the CREATE statement and execute the script; then just drop the original object.

Outlook takes forever to switch from one folder to another after update Kb2412171

After installing Microsoft Outlook update Kb2412171 it takes forever to switch from one folder to another – uninstall the update and you will be fine. Only do this if you are experiencing the problem – in this case it was Windows 7/ 64 bit running Outlook 2007 with SP2. To uninstall:

• Go to Control Panel \ System and Security \ Windows Update \ View installed update
• Find the above mentioned update and uninstall

Note if you have your system configured to install the updates automatically this will install again so you will need to change your configuration.

Free Executor license with xSQL Comparison Bundle purchase

From now until December 31, 2010 you can choose to take advantage of one of the following special offers:

• A complimentary Script Executor Professional license with the purchase of a xSQL Comparison Bundle Professional license; 
• A complimentary 12 months upgrade subscription with the purchase of any of our products.

As always your comments and suggestions are greatly appreciated. Also, we would like to encourage you to follow us on Twitter http://twitter.com/xsqlsoftware, like us on Facebook http://www.facebook.com/xsqlsoftware, and don't forget to click on that "like" button on our products' pages.

Happy Holidays! May the season be filled with joy and may you have a healthy, happy and prosperous New Year!

xSQL Data Compare new build

New build of xSQL Data Compare that fixes an issue with the sync script serialization is available for download. Under certain conditions the serialization of the synchronization script would fail – while that failure is mostly transparent to the end user as the process will continue normally, it would be exposed in cases when the synchronization script is very large.

xSQL Data Compare provides for comparing and synchronizing data in two SQL Server databases. The current version supports SQL Server 2008/2005/2000 - all editions. xSQL Data Compare is free for SQL Server Express with no restrictions. For other editions of SQL Server it is only free if the number of objects does not exceed certain limits listed here: http://www.xsql.com/LiteEdition.aspx. You can download your free copy from here: http://www.xsql.com/download/sql_server_data_compare/

Script Executor for DB2 free today

Today only you can get a free license for Script Executor for DB2 - use discount code TWITDB2EX when placing the order. Script Executor is one of the best tools for deploying t-sql scripts to your database server "farm". Whether you are running DB2, SQL Server, MySQL or a combination of those platforms, Script Executor allows you to easily create deployment packages involving hundreds of scripts that have to be executed in a certain order targeting as many servers as you need on any of those platforms, simultaneously.

Download a free, fully functional trial version and see for yourself what you have been missing until now:  http://www.xsql.com/download/script_executor/