Long running queries due to table spooling

Yesterday a client of mine reported that one of the Stored procedures that they had written was running extremely slow. So after verifying that the statistics were updated and index were not fragmented, I started to look at the access plan that the SP was using. From the access plan it was evident that a table spooling operator was causing most of the delay. I found that the Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

So any nested query or an Insert statement like 'INSERT INTO ....SELECT FROM....' would cause table spooling.  I could not find any index that would improve the performance, so I recommended the developer to use a temporary table instead of a subquery and that seemed to work perfectly. The temporary table increased the response of the query tremendously. It is strange that both the results of the subquery and the temporary table are indeed stored in the TEMPDB. So I am not sure what the reason is for the increased performance of the query. The use of temporary table and avoiding the tablespooling is just an observation. I don't know the reason and don't know it will work for everyone but wanted to share this experience.

For more details please refer to this excellent article explaining spooling.
http://www.simple-talk.com/sql/learn-sql-server/operator-of-the-week---spools,-eager-spool/

Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

According to Microsoft this error can occur when all the following conditions are true: 

• You want to restore a database backup that spans across multiple backup devices, and you have not specified more than 64 backup devices.
• You created the database backup on a computer that is running SQL Server 2000 Service Pack 3 (SP3) (Build 2000.80.869.0) or a later build of SQL Server 2000 SP3.
• You try to restore the database backup on a computer that is running a build of SQL Server 2000 SP3 that is earlier than 2000.80.869.0.

Ref : http://support.microsoft.com/kb/833710

But today I was trying to restore a database backup that was taken from SQL Server 2005 instance and I restoring this to a SQL Server 2000 instance and I got this error. Unfortunately, I had split the backup into 10 stripes because the database was pretty large. So my initial thoughts suggested that this is due to the fact that I had 10 striped backup files. So I spent sometime in creating a new backup with just one file. But the error kept coming. Upon further research, I found that a lot of folks saw this error when they tried to restore a backup from a 2005 instance to database on 2000 instance.

So the error seems to be misleading in some ways. Due to the fact that the structure of the backup header files were changed in SQL Server 2005, you can not restore it to a SQL Server 2000 instance.
It might be worth trying the detach-attach method to work around this problem. One could also try to use the SSIS export/import method to get the data to a 2000 instance.

For now, I decided to upgrade my instance to 2005 and restore the DB.

Ref :
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/46e3ecf9-6eab-4aa0-9453-11e7269efb6e/
http://dbaspot.com/forums/sqlserver-server/378212-error-too-many-backup-devices-specified.html
http://www.sqlcoffee.com/Troubleshooting023.htm

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

All the SQL Server DBAs know that we are not supposed to delete anything from the 'master' database or for that matter from any of the system databases. ('model' is an exception though). But how many of us have accidentally deleted something from the master database. Not me....until today. For some unknown reason I ran a query to delete all tables from the master database. I queried the sys.tables to list all tables in master DB and dropped them.
Later I got the following message when I tried to query the user DBs.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

There is one more table that I had dropped which needed to be recreated and that was 'spt_monitor'. Fortunately, I was doing this on in my test environment and did have a backup of the master database. But I still wanted to find out about these tables. So after some quick googling I found some information regarding this tables. I also found out that we can recreate these tables using some scripts that are located in 'Your SQL Server Install Dir/MSSQL/Install' directory. The script is called 'u_tables.sql' and once you use that to create these two tables the error went away. In case the scripts are missing from the directory that I mentioned please send me an email and I will reply back with the scripts.

Things learned today.

• Always have backups of all DBs.
• Never delete without taking backups.
• If you accidentally delete something then do not panic. GoogleMan will come to your rescue.