Backup database structure only

Sometimes you need to back up the database structure (schema) only – it can be because you simply would like to maintain an audit trail of database schema changes or because you need to compare the schemas of two databases that you can’t access directly from one location.

Whatever your reasons maybe xSQL Object provides the ideal solution to accomplish this – with a click you can take a snapshot of the SQL Server database schema. The schema snapshot contains all the schema information but no data and consequently is a very small size file. You can then use xSQL Object to compare this schema snapshot to other snapshots or to live databases and see exactly what changed from one schema version to another.

T-SQL: set end date to first or fifteenth of the month

Here is a simple assignment: if the subscription date is from first to 15th of the month then the expiration date should be set to the 15th of the same month a number of years later; if the subscription date is from 16th to the end of the month then the expiration date should be set to the first of the following month a given number of years later.

I am sure there are many ways to do this but here is one:

DECLARE @sDate DATETIME -- subscription date
DECLARE @sYears tinyint -- number of years the subscription will last
SET @sDate = getdate()
SET @sYears = 1

IF datepart(dd, @sDate) <= 15
SELECT DATEADD(year, @sYears, @sDate) - datepart(dd, @sDate) + 15
ELSE
SELECT DATEADD(year, @sYears, DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0))

Script Executor - package t-sql scripts into executables

In addition of allowing you to deploy multiple scripts to multiple databases directly from the interface Script Executor also allows you to package all your scripts into an executable.

An executable package in the context of the Script Executor is a small, dynamically generated executable file that contains embedded in it scripts, list of databases against which the scripts will be executed, and a small piece of code to run these scripts. You can run it as you would run any other Windows program.

Executable packages are suited for deploying Sql script to the end-users/clients. A user can run it without having Script Executor, or SSMS installed.

SQL Server execute scripts - multiple scripts, multiple databases

You launch SQL Server Management Studio – open script #1 and execute it against database 1 on SQL Server 1, then execute it against database 1 on SQL Server 2… then open t-sql script #2 and execute it against database 1 on SQL Server 1 etc… what a painful process, you dread the day you have to go through this (sometimes that is everyday). What a waste of resources too – a $100K database administrator spending hours executing scripts on tenths of server!

No more! You can now use Script Executor to take care of this job for you. One time only, you will go through a simple, and may I suggest fun, process of organizing your scripts into virtual containers and your databases into groups; then, map those script containers to the database groups; set an order of precedence if you need to and voila you have a package that you can now execute with a single click; or, you can even schedule it to run before you even make it to the office.

Script Executor will execute each script in the order you have defined against each database based on the mappings you have defined and once completed it will generate a comprehensive report allowing you to see how the script execution went on each target and also easily “flip through” the result sets that those scripts may generate.

Deploying (executing) multiple scripts against multiple SQL Server databases has never been easier.

You can download a free trial version of Script Executor from: http://www.xsql.com/download/script_executor/

A free community edition of Script Executor with limited functionality is also available: http://www.xsql.com/download/script_executor/

Applies to:

• SQL Server execute t-sql scripts
• SQL Server execute multiple scripts
• SQL Server deploy scripts to multiple databases
• SQL Server package t-sql scripts
• SQL Server run multiple scripts

Kindle, Zune, iPod - a free gift with every purchase

This month only we are giving away a free cool gift with every license purchase - kindle wireless, Zune, iPod Touch etc. Check out the details here: http://www.xsql.com/promotions/

Is Bing beating Yahoo already?

Looking at our site stats today I was surprised to see that in the last 30 days we have received about 20% more “organic” visitors from Bing than from Yahoo! Is this just an aberration or is Bing already beating Yahoo? What you seeing on your site? Please answer the poll on the right panel here and leave your comments below.

The poll has been closed - the final result was 60% for Bing and 40% for Yahoo however, the number of responses we got was statistically insignificant so I would put no stock on these results.

$100 for your SQL puzzle

We are planning to bring back the “puzzle of the month” program and this time the puzzles will come from you. We are asking you to send us the hardest SQL puzzles you have come across or that you can come up with.

If your puzzle is chosen to be published on our site we will:

• Send you a $100
• Link from the puzzle page to your blog or your site

The harder your puzzle the higher the chances that it will be picked, however,

The puzzles should fall into one or more of the following categories: SQL Server, T-SQL, Database Management Systems

Submit your puzzle here: sales@xsql.com

To pay or not to pay this is the question

Product reviews are absolutely critical to the success or failure of a product and that is especially true in the software world. Trying a software product takes effort and precious time so we all naturally gravitate towards relying on product reviews that others have done and only take the time to try one or two products that may have been heavily and positively reviewed.

Knowing how important those reviews are we have sent tenths of product review requests in the last 6 years and in the process we have learned the unsettling truth about product reviews: they don’t come by easily. In the case of the big guys (technical magazines and large user group sites) you don’t have to pay for the product review per-say but your product will forever be in the queue unless you are shelling out a few thousand advertising dollars in those medias, only then does your product magically shoot up to the top of the product review queue; whereas in the case of the smaller guys (bloggers and user group contributors) you often have to pay for the review anywhere from a set price of as low as $200 for one product review to a per word price that can be as high as $0.6 per word (this is the highest product review offer we have received).

In the case of the big guys since you are not paying directly for the product review there is no moral dilemma – deep down you know that the review can’t possibly be fair and un-biased. If you are paying $30K / year for advertisements on that media the last thing they would want to do is alienate you with a bad product review, but, it’s easy to shun those thoughts, after all this is your product and you truly believe that it is a great product.

On the other hand, when it comes to paying for the review directly it feels like cheating and unfair to the reader who believes that he is reading an impartial review. For it to feel right one must disclose in big bold letters that “this review has been paid for by the product vendor…”, but such disclosure would likely deem the review worthless. Ideally you would want someone to review your product simply because they like the product and they want to share it with other people but in all fairness reviewing a software product and then writing up your observations and conclusions may take hours of work of which we humans have a very limited supply of. Herein lies our dilemma – to pay or not to pay?

Until now we have chosen not to pay, instead we have only offered the reviewers a free license for the product they decide to review which we think is fair and does not present a conflict of interest. We would certainly be more comfortable continuing with this approach but the results are not very promising hence our pondering.

Let us know what you think – would you pay?

And of course, we would be delighted if you would consider reviewing any of our fine products in exchange for a free license.

Synchronize two SQL Server databases

You are furiously pounding code on your computer and in the meantime making small changes to the local (development) version of the database as the need arises – add a column, change the type and width of another, add a view, make a change to a stored procedure and so on. At some point in the early morning hours as the World wakes up you are ready to publish your work on the production server, somewhere… – just a few clicks and the early risers will see your masterpiece (application) in action. Well, not so fast unfortunately, all those changes you made to the development database must be made to the production database otherwise your application is not going to work, is it? Now, that is some painful and tedious work to say the least. What if you forget something? And here is a twist to make this a real nightmarish scenario – remember last week when the client needed those urgent changes and you were forced to do the “unthinkable” – make changes right on the production database! Worst yet, you realize that you never got around to bringing those changes down to your local, development database – no words can describe the pain you must feel at that moment!

Is this time for panic? No it is not, it is time for xSQL Object – in just a few clicks it will show you exactly where the differences are on both sides and better yet auto-generate the scripts that you need to quickly and safely make the changes.

Download it now and eliminate “the pain” – free lite edition with no strings attached. Supports SQL Server 2000, SQL Server 2005 and SQL Server 2008.

Best way to execute t-sql scripts

Whether you need to deploy / execute multiple t-sql scripts to hundreds of your clients or to hundreds of your enterprise servers Script Executor is your best bet. For starters, it supports SQL Server 2000/2005/2008, MySql 5.0 and higher, DB2 9.0 and SQL Server Compact 3.5. It allows you to quickly organize your scripts into virtual script containers and organize your servers into virtual server groups. You can then map the script containers to the server groups, define execution precedence if necessary, and finally just deploy them OR package the scripts into an executable. Imagine, instead of sending your clients t-sql scripts and telling them to download and install Sql Server Management Studio so that they can run your scripts you can just send them an executable file that anyone can run - no that's easy!

New build of xSQL Bundle available

A new build of xSQL Bundle that includes xSQL Object for database schema comparison and synchronization and xSQL Data Compare for comparing and synchronizing data is available for download from: http://www.xsql.com/download/sql_server_comparison_bundle/

The new build adds the ability to include and exclude object level permissions from the synchronization. Permissions are now shown under each object they're granted to or denied on. Statement permissions are listed under the database node in the comparison grid.

xSQL Builder now supports SQL Server 2008

The newly released xSQL Builder 3.0 now supports SQL Server 2008. xSQL Builder provides for effortless distribution of database changes to remote clients. In just minutes you can create an executable package that contains the schema of the "master" database you wish to propagate to your clients - the client executes the executable and voila, the schema of the client database is upgraded.

No more sending T-Sql scripts to your clients - send them an executable instead!

Download your free trial of xSQL Builder now: http://www.xsql.com/download/sql_database_deployment_builder/

Right tool for the job - what our customers say

It is always nice to see a nice blog post about your products but it is awesome to see a paying customer praising the product without being asked to do that and without asking for any favors in return! I just ran accross one such blog post that was published today:
http://catholicinformation.aquinasandmore.com/2009/05/06/using-the-right-tools-for-the-job/ - it is in such cases that we can proudly say: you don't have to listen to us - see what others are saying...

Execute Sql Scripts on DB2

Whether you need to execute one Sql Script on a DB2 database or many Sql Scripts on one or many DB2 databases there is one solution unlike any other – Script Executor. Its simple and intuitive interface allows you to create deployment projects involving hundreds of scripts and hundreds of target databases and then with a click of a button execute them all based on the order you have determined and precedence constraints you may have set.

You can use Script Executor to execute scripts against DB2 9.0 databases.

You can read more and download your trial copy of Script Executor from: http://www.xsql.com/products/script_executor/

Script Executor now supports DB2 and SQL Server Compact

we just released a new version of Script Executor which now in addition of SQL Server 2008/2005/2000 and MySQL 5.0/5.1 also supports DB2 9.0 and SQL Server Compact Edition 3.5.

Script Executor provides for deploying Sql Scripts to multiple target databases with a click of a button and allows you to:

• organize target databases into logical groups
• organize your Sql Scripts into containers and order scripts within each container
• map script containers to database groups
• order mappings and set precedence constraints
• get a comprehensive execution report
• view execution results in one integrated easy to navigate interface

Download your trial copy today from: http://www.xsql.com/download/script_executor/

Independent consultants can quickly translate hours saved to dollars - employees can't

One would think (or at least I thought) that when it comes to selling auxiliary software tools it would be easier to sell them to an organization where the buyer is using the organization’s funds to purchase the product than to an independent consultant who has to shell out his own money. However having spent the last 5 years in the SQL tools market I have concluded that the opposite is true – generally speaking the independent consultants do not need much convincing, whereas the full time dba/developer in an organization seems to have a much harder time making a $300 decision!

So, that got me thinking: why would someone readily pay a few hundred dollars from his own pocket for one of our tools while a full time employee seems to struggle to make up his mind and appears to be constantly looking to come up with unusual scenarios that the off the shelf tool may not handle? I have come up with three main reasons (not necessarily an exhaustive list of reasons):

1. the value preposition for the independent consultant is very clear – every hour of their time has a price tag – if they find a tool that saves them 1 hour per day they can quickly translate that saved hour into dollars. On the other hand a full time employee can not easily convert “hours saved” to money, nor can his boss or the boss’ boss.
2. The bureaucracy that plagues many organizations significantly dilutes the short term value of such tools - if you as an employee are forced to go through hoops to get approval for purchasing a $300 product that will potentially save you a couple of hours per week then you may decide it is not worth the pain. The independent consultant on the other hand does not need to ask for permission from anyone so there are no “costs” associated with the approval process.
3. A programmers ego: “I can do this stuff myself, why should I pay someone else to do it for me” When it comes to productivity an independent consultant can not afford to have an ego whereas a full time employee can and furthermore, by building a custom solution instead of buying an off the shelf tool the full time programmer will likely consolidate his position with the company. A lot of you may be surprised but I have found that it is often easier to convince the higher ups to let you put 100 hours on a project than convince them to let you spend $300 on a tool.

Please note that this is not a “research based” conclusion – it is simply an opinion supported by personal experiences. Please feel free to add your perspective on this and don't forget to check out our tools - you don't even have to pay anything in some cases.

A real life Sql scripts deployment story

We got a surprise call today - a client that just purchased a license for the newly released Script Executor called to thank us (normally we thank our customers for choosing our products not the other way around)! He told us that for more than 2 years one of his most annoying weekly "chores" was to execute about 120 Sql scripts on a handful of SQL servers. The task was complicated by the dependencies between those Sql scripts - a set of scripts had to execute on Server 1 successfully before another set could be executed on Server 2 and so on. And of course within each set the scripts had to execute on a certain order. So every week he would consume many hours of his life opening and executing those 120 Sql scripts one after the other.

For the last few months he was using the free community edition of our Script Executor and it had helped him a lot but it wasn't exactly what he wanted whereas the new Script Executor 3 gave him exactly what he needed - he spent about 15 minutes configuring his Sql script deployment package and saved the project. Now, all he needs to do is click Execute and a task that once took hours takes him one minute to launch and about 10 - 15 minutes to review the results.

His words: "I would have gladly paid the $179 from my pocket if the company would not have approved the purchase. Thank you for an awesome product!"

Well, thank you! For us there is nothing better than knowing that our work is providing real value to others.

Execution order and precedence

In addition of allowing the user to organize Sql scripts in containers and ordering the scripts within each container the new Script Executor 3 allows the user to also order and set precedence constraints between “mappings”. In other words you may want scripts on the script container 1 to execute against the databases in the database group 1 first and only if those all succeed then execute the scripts in container 2 against database group 2 etc.

This cool feature allows the user to configure highly complex deployment scenarios involving dependencies between scripts and groups of scripts. The picture below shows where the Execution order and precedence constraint can be set for a given mapping.

The 7 countries that have “lost the number” of our site!

-- sorry Mr. Biden, I just couldn’t resist!

Every time I look at the World map on google analytics, that cool map that shows where the visitors came from, I see a handful of white spots – that is countries from which we did not have any visitors in the last 30 days. I am always curious to know which ones those countries are but they are usually on the same general area so I don’t often drill down. Today it occurred to me that it would be interesting to go back in time and see if there were any countries from which no one ever visited our website – I set the range from January 1, 2008 to today and found that in those 14 months we have not had a single visitor from 7 countries out of 200 and some countries. And the “winners” are:

From Africa

• Burkina Faso
• Central African Republic
• Madagascar
• Mauritania
• Western Sahara

From Asia:

• Kyrgyzstan
• Turkmenistan

Please do keep in mind that our website: http://www.xsql.com is a niche site providing database tools to database administrators and software developers and therefore, this “finding” does not necessarily mean anything. I just found this interesting and decided to share with ya’ll!

Executing multiple Sql Scripts

If you work with SQL Server and especially if your job includes DBA tasks it is very likely that you have run into situations when you had to take a bunch of scripts (maybe tenths of them and maybe hundreds) and execute them in a certain order one after the other against a database. It is a tedious task to say the least. Now imagine if you had to do the same thing for 10 servers or 100 servers then you are really in trouble - there comes a point when it will be more efficient for you to sit down and write an application that will help you automate the process then do this job even once.

Well, you don't have to go through that annoying process anymore - with Script Executor, in just minutes, you can configure a highly complex scenario involving hundreds of scripts and tenths of target servers after which your highly complex deployment will require just one click or even none if you use the command line utility.

Download Script Executor now and see for yourself why we are so excited about it!

Where do MySQL users congregate?

We are kind of new to the MySQL world and are trying to figure out what are THE "places" to go if you want to reach MySQL users. Our tools are primarily SQL Server tools but we have just released a new tool, Script Executor that supports MySQL as well. The tool in a nutshell allows the user to execute Sql Scripts against multiple MySQL databases – it is a great tool for any database administrator or developer that works with MySQL. We are looking for two things:

• users who would be willing to review the product
• web destinations where we should advertise

So, if you are either someone interested in reviewing Script Executor or someone who knows where we should advertise please email us at our info address on our xsqlsoftware.com domain or simply put a comment here.

The Script Executor you have been waiting for

We have just released Script Executor 3 with support for SQL Server and MySQL - deploying your Sql Scripts to multiple target servers has never been easier.

The previos version of Script Executor has been renamed to "Script Executor Community Edition" and will continue to be available free of charge from our website.

Check the new Script Executor out - you will be impressed!

Download your free trial now: http://www.xsql.com/download/script_executor/

Best way to compare data in two SQL Server databases

So you need to compare the data in two SQL Server databases – maybe just the data in certain tables, maybe certain rows only and maybe you are just interested in certain columns. And what if the tables you need to compare don’t have a primary key defined (well, they all should but it is not rare to see cases when they don’t) how can you approach the task? A quick search on google will reveal an abundance of tools all claiming to do what you need so how do you know which one to pick?

We have made the task simple for you. We have tested every single one of those tools and the bad news is that with the majority of them you would be wasting your time. There are only very few that do the job right and we are happy and proud to say that xSQL Data Compare is the best tool you will find in terms of breadth of functionality it provides, flexibility it gives the user, performance with large databases and last but not lease cost. No, you don’t have to take our word for it – we have thousands of very happy users the majority of which have not had to pay a dime for it. You see, xSQL Data Compare is completely free for SQL Server Express with no restrictions and it is also free for other editions of SQL Server as long as the databases being compared don’t have more than a certain number of objects.

Here are some highlights of xSQL Data Compare:

• You can compare the data in two sql server databases or just select tables in those databases;
• You can map tables to be compared any way you want regardless of the table names;
• If xSQL Data Compare can’t find a predefined unique key on a table it allows you to define it on the fly as a combination of one ore more columns in that table;
• You can set filters on each table if you wish to compare only a subset of the rows;
• You can include or exclude columns from the comparison;
• You can choose to use bulk insert for the synchronization if you expect that a large number of rows will need to be inserted in the target table.

xSQL Data Compare supports SQL Server 2008, SQL Server 2005 and SQL Server 2000.

You can download your copy from: http://www.xsql.com/download/sql_server_data_compare/

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/

What’s the interest on the $819B stimulus bill?

Assuming that the government would be able to finance the stimulus bill by selling $819B worth of Treasury bonds at let’s say 4.5% to the Chinese government and others the yearly interest WE will pay on those bonds is just about $36.9 billion for a 30 year total of $1,105,650,000,000 – that would be about $1.1 trillion dollars in case you are having trouble reading that number. And just in case you are trying to envision what $1 trillion dollars look like check this out: http://www.cnn.com/2009/LIVING/02/04/trillion.dollars/index.html

So, what does this have to do with SQL Server tools? Nothing really other than these numbers are so amazingly big that it is hard to just ignore them and focus on our job which is to build awesome tools for SQL Server database administrators and developers.

Another presentation on RSS Reporter

Chad Miller did another presentation at the SQL Saturday Tampa 2009 focused on RSS enabling System Administration information - utilizing RSS Reporter to automate the daily DBA checklist by aggregating job status information from across multiple SQL Servers and databases. More details can be found on Chad's blog: http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!267.trak

Deploying database schema changes to hundreds of clients

This post is directed to software publishers who have published software that uses SQL Server on the back end. Here is a simple scenario you run into: you release the software and within a couple of months you have successfully deployed it to tenths of customers. In the meantime you have discovered a flaw on a stored procedure which needs to be changed, you want to add a couple of reports for which you need to add a couple of new supporting views, a column on a table needs to be changed from varchar(50) to varchar(100) etc. – you get the picture. The question is: how do you make those changes on all those customers’ sites? You could send them a change script that will alter and add those objects in the database but a lot of the customers don’t know anything about SQL Server and don’t won’t to deal with those scripts. Furthermore, if this is not the first iteration of changes and the customers are using different versions of your database the script you send must be customized for each version. The main point here is that sending the customers a t-sql change script and asking them to apply it is generally not going to work.

So, what do you do? Well, you use xSQL Builder which was designed to alleviate this kind of trouble. xSQL Builder is extremely easy to use but at the same time, if your scenario is more complicated it gives you all the flexibility to customize it to best fit your needs. How does it work? You go through a simple wizard that takes no more than a couple of minutes and allows you to choose the source or “master” database which you want to deploy to your customers, set a few parameters and generate an executable package that you can then send to your clients. The client runs the executable which compares the embedded schema of the source/master database with the client’s version of the database, generates a synchronization script, executes the script and emails you the results. Now, you have to admit that is a cool and extremely efficient way to deploy your database schema changes.

You can download your copy of xSQL Builder from: http://www.xsql.com/download/sql_database_deployment_builder/

Wrap your insert, update and delete statements on a transaction – ALWAYS

The main objective of a transaction is to ensure that either every statement within that transaction is executed successfully or none of them. So, you would logically wrap in one transaction related statements that should be executed together. A classic example is that of transferring money from one account to another – the action of subtracting the money from account A must happen together with the action of adding that same amount of money to account B (either both or none) otherwise, if let’s say the system crashed right after subtracting the money from account A and before adding them to account B the money will disappear! So, great, when one understands this a legitimate question comes to mind – what’s the point of wrapping a single statement in a transaction?

Well, we are humans and as such we are prone to making mistakes, sometimes, big and costly mistakes so, wouldn’t it be nice to “erase” our mistakes when we realize that? That’s what a simple Begin Transaction statement provides us – the ability to rollback our mistakes. How does one realize that he made a mistake – the first indicator is the “number of rows effected” which SQL Server reports – if that number is larger than you expected then maybe you made a mistake on your statement. How else can I tell? The cool thing is that SQL Server will let you read the uncommitted rows and see what your statement did - so you can issue a select and review the rows you just updated for example. If you do realize that you made a mistake you simply issue a rollback and all is good – it is like it never happened. If you are sure your statement did what you intended it to do then you issue a commit transaction and the changes you just made become permanent.

So again, be diligent and wrap every update, delete or insert statement in a begin transaction / rollback / commit transaction. It is much better to be safe then sorry!

Applies to: SQL Server, SQL Server Mangement Studio, Query Analyzer

SQL Server Query Governor way off on its estimates!

First of all if you are looking for information on how to set the query governor cost limit option you can go directly to the source: http://msdn.microsoft.com/en-us/library/ms190419(SQL.90).aspx or to any of the tenths of blogs that seem to have replicated this information word for word.

So, here is the scenario: as a diligent DBA you audit a SQL Server you just took over and you realize that the query governor cost limit is set to 0 which means in essence that as far as the query governor is concerned any queries can run on the server for as long as they need to run. That’s not good so you go ahead and set a high limit to start with, let’s say 300 seconds. Your thinking is that if a query that is estimated to run for more than 5 minutes ever comes across it should be stopped dead on its tracks before hundreds of calls from angry users reach your desk. To your surprise 5 minutes after you have set that limit you get a call from Jane Doe – she has seen this strange message on her screen she never saw before “you can’t execute this query because it is estimated to take 450 seconds”. Jane assures you that the report she is running usually runs in 5 to 10 seconds!

You go ahead and raise the limit to 500 seconds – Jane runs the report and sure enough the report takes less than 6 seconds! Something is not right – why is the query optimizer so grossly overestimating the time this query will take to run?

The answer is simple indeed – remember the query optimizer is estimating - estimates are made based on available information and if the available information is either missing or not accurate than the estimate won’t be accurate. The information that the query optimizer uses is in the statistics that SQL Server maintains and that is where you need to look. So, check it Auto Create Statistics is on first – if not no then you either need to turn it on or manually create the statistics the optimizer needs for estimating this query (this is the case when the necessary statistics information may be missing). Second check if Auto Update Statistics is on – if not you may need to update the stats to enable the query optimizer to produce a more accurate estimate.

Furthermore, you may find that all is in order that is no statistics are missing and the stats are up to date and yet the estimate is wrong! Try forcing a recompile of the query in question and see what happens. What may have happened is that SQL Server is using a cashed plan that was optimized for the “wrong” parameter values – that is values which are not good representatives of the dataset and based on that plan it is estimating that 10,000 rows will be returned from table a when in fact in 90% of the cases the number of rows returned from table a on that query is under 1000. By forcing a recompile of the query you are forcing SQL Server to re-evaluate and possibly pick a better plan this time based on new parameter values.

The objective of using the query governor cost option is to prevent “stupid” queries from degrading the system performance and not to prevent legitimate queries from running. So you may be forced to raise the limit but you should look at that as a temporary measure until you have dealt with the legitimate queries by taking the above mentioned actions as well as optimizing the query itself. Then, you should go back and start gradually lowering the query governor cost limit.

Why is SQL Server scanning the clustered index when I have an index on the column I am filtering on?

I ran into a very simple SQL Server performance related question the other day. A puzzled DBA was staring at a simple query “SELECT * FROM table1 WHERE col_n = x”. Since this was a fairly common query that was executed very frequently he had appropriately created an index on col_n. Yet, for some reason SQL Server was simply ignoring the index on col_n and instead scanning the clustered index. So the puzzling question on this DBAs mind was “why is SQL Server scanning the table? Isn’t the SQL Server Query Optimizer smart enough to see that it will be more efficient to use the col_n index?

The answer lies in the fact that the SQL Server Query Optimizer is smarter than that (albeit not as smart as I wish it would be). You see, when deciding on a particular execution plan the Query Optimizer has to use an actual value for the parameter x to estimate the number of rows that will be returned on each step. It further assumes that the rows you are looking for are randomly distributed and that it will need to do a page read for every row being returned in addition of the reads it needs to do on the index pages. Depending on this number it makes a determination whether it will be more efficient to just scan the whole table and pull out the rows it needs or go to the col_n index first to get a list of addresses and then go and pull those rows.

So, now that we know why should we just leave it there since it appears that SQL Server is correctly picking the most efficient path? Not so fast! Remember all that evaluation is being done based on a certain value of parameter x. It could be that for most of the values of parameter x scanning the table is more efficient. In that case you can simply drop the index on col_n if it is not needed for any other queries and leave it at that. However, it could be that for 99% of the possible values of x it would be a lot more efficient to utilize the col_n index – it just so happened that unfortunately when generating the plan the value of x happened to fall on that 1% for which scanning is more efficient. In this case you have to options:

• simply force SQL Server to dispose the bad plan that it has cashed and generate a new one; OR
• use a HINT to force SQL Server to use the index. The danger with this however is that with time as the data changes the use of that index may not be optimal so I would recommend that you avoid using HINTS whenever possible and let SQL Server do its job.

One last note: it is often not necessary to return all the columns – in other words instead of SELECT * FROM table1 WHERE col_n = x you may only need to return a couple of columns like SELECT col_1, col_2 FROM table1 WHERE col_n = x in which case it could be worth it to include col_1 and col_2 in the col_n index. That way SQL Server would not need to go to the table at all but instead get all it needs from the index pages. In certain scenarios where you have a rarely updated but very frequently queried table the above approach of including other columns in the col_n index may make sense even if the query returns all the columns.

SQL Server search stored procedures

Anyone who works with SQL Server has at one point or another faced the simple question: “how can I find all stored procedures that reference a given table?”, or “how can I find all the objects in the database the definition of which contains a certain word or phrase?”. While SQL Server makes those tasks relatively easy those are still time consuming and when done on an ad-hoc bases chances of missing or forgetting something are relatively high.

The good news is: you don’t have to struggle with this when there is a really cool and completely free tool called xSQL Object Search available. So how does xSQL Object Search work? Very simple:

• You connect to the server you are interested on
• Choose to search in a certain database or All Databases on that server
• Define your search criteria
  
  • What are you looking for
  • How do you want to search (exact match, contains, starts with, ends with, sql server expression, regular expression)
  • Choose where do you want to look - the object name, object definition, or both?
  • Finally choose which object types you wish to search (all objects or just certain types of objects like stored procedures, views, tables, triggers etc.)
• Click on Find Now (see the screen shot below

xSQL Object Search is a free tool and you can download it from: http://www.xsql.com/download/sql_server_object_search/
Applies to:
SQL Server search tables
SQL Server search views
SQL Server search stored procedures
SQL Server search triggers
SQL Server search functions

Free SQL data compare tool

There are many scenarios in which someone working with SQL Server may need to find out what the data differences are between two databases or just two tables. Furthermore, it is often necessary to synchronize the data, that is, make the data in the target database the same as the data in the source database or vice versa. That’s where xSQL Data Compare comes in – it is a tool that was designed specifically for this purpose. It comes as part of the xSQL Bundle package and just like in the case of xSQL Object chances are you won’t have to ever pay a dime for it.

The xSQL Data Compare licensing works exactly the same way as the licensing of xSQL Object described in detail on yesterday’s post here.

Free SQL Compare tools for SQL Server

If you are looking for a free schema compare tool that allows you to compare and synchronize the schemas of two SQL Server databases, like your development database with the production database, then xSQL Object is the tool for you. Not only is it one of the best tools in the market (don’t take our word for it – give it a try and you will see for yourself) but what’s unmatched in the industry is the way our licensing works. I will give you the details below but the end result is that most of our users enjoy using xSQL Object completely free. Here is how our licensing works:

• everyone downloads the same xSQL Comparison Bundle package that includes xSQL Object and xSQL Data Compare;
• for the first two weeks the applications function as if you had purchased a professional license;
• after the first two weeks the licensing module first checks the edition of the databases you are comparing:
• if both databases are SQL Server Express edition then you see no difference – works the same as before;
• if one of the databases in the comparison is something other than SQL Server Express then the licensing module checks the number of objects in that database (number of tables, stored procedures, views, functions)
• if the number of objects is within the limits of what we call the Lite Edition then again the application works and you don’t see any difference;
• if the number of objects is greater than those limits then you are kindly notified that to compare and sync that database you need to acquire a license. Well, someone has to pay for all that development effort that goes into our products :)

You can download xSQL Bundle from: