Category Archives: SQL Server

What it took to stump the optimizer

Sometime ago I was working with a Microsoft support technician on a ticket related to some database issues on SCOM. The technician wanted to run a stored procedure to clear out some of the backlog we were seeing with a batch job.

When he ran the procedure (via Query Analyser) – we encountered an error as below. Basically an error that is wee bit rare, the optimizer telling us that it is stumped and the query is too big for it to process. The technician moved on to other ways of resolving the issue as fixing this problem involved touching some procedures that came with the product and was beyond the scope of our call. But after we were done, I was intrigued to find out what caused the optimizer to give up.scomoptimiser

I went to Line 177 of the specified stored procedure and found a statement that looked pretty small on the outside:

DELETE dbo.[CategoryOverride]

FROM dbo.[CategoryOverride] CO

JOIN #BaseManagedEntitiesToPurge BMETP

ON CO.[InstanceContext] = BMETP.[BaseManagedEntityId]

The table CategoryOverride had about 300 rows..and  on checking what went into the temp table – it wasn’t all that large either, only around 100 rows. Looking at the estimated plan from the optimizer gave me nothing – a perfectly ordinary plan with an equijoin on a clustered index. So what was causing the problem?

I looked to see if the table CategoryOverride had any triggers on it. None. Then I went on to looking at keys. The table had one foreign key defined on it.

scomo1

I decided to script this key and see what it showed me.

ALTER TABLE [dbo].[CategoryOverride] WITH CHECK ADD CONSTRAINT [FK_CategoryOverride_ManagementPack] FOREIGN KEY([ManagementPackId])

REFERENCES [dbo].[ManagementPack] ([ManagementPackId])

ON DELETE CASCADE

GO

There was my culprit – an ON DELETE CASCADE. So many other tables is this touching? A quick look at the dependancies on this table showed me no less than 14. For one record to be deleted – there were dependancies on 14 tables. I picked one record on the main table and counted how many dependant records were there to clean up. It was about 6000. So the delete statement was far from a benign straightforward delete – it had a huge amount of data to clean up and the optimizer could not generate a plan across 14 tables some of them having more cascade deletes on them.

Moral of the story – never assume the simplicity of a statement based on looking at it, or what the query optimizer tells you. The truth may be much deeper. If I had the choice of fixing it I would remove the cascade delete and go on to cleaning up data step-by-step in each of the dependant tables.

Tail of the log has not been backed up

This is the second post in my attempt to blog one little thing every day. Today I ran into this message when trying to restore a database over an existing database. I forgot to check the box that says ‘overwrite existing database’ and got this rather informative error. I love this error message since it tells exactly what the user should do

‘The tail of the log of the database <mydatabase< has not been backed up – Meaning there are transactions in the log that need to be backed up.

‘USE BACKUPLOG WITH NORECOVERY’ if you want to backup the log and it contains work you don’t want to lose. ‘- It tells exactly what should user do in case he needs the older copy.

‘USE WITH REPLACE OR WITH STOPAT CLAUSE OF THE RESTORE STATEMENT TO JUST OVERWRITE THE CONTENTS OF THE LOG’ – in other words if am using the GUI I better check the box to tell sQL Server to overwrite the existing database, if that is what I intend to do.

This message tells me what is wrong and also evaluates the two possibilities – I want to keep or discard the old copy of the database, and tells me what to do in both situations. It is an excellent example of user friendly error messages.

blogpost

 

 

SQL Server 2012 – Changes to set up

I plan to write a series of posts while learning more on SQL Server 2012. The changes to set up as I observed while installing SQL Server 2012 RC0 are :

1 Pre requisites include Powershell 2.0 and .NET Framework 3.5
2 If you have Windows 7/Windows Server 8/Office 2010 it expects Service Pack 1 to be installed (does not even proceed until pre requisites if not).

The install is a slipstream process – by that all the necessary pre requisites are part of the media, there is no need to go to separate sites to download/install.
3 There is no reboot required upon install (I only tested it as standalone, with all other programs closed – this may not be true in a cluster install).

4 32 bit install is still supported.
5 Support for Itanium appears to have been removed.

Acknowledgement:

I was inspired to learn more and write by Aaron Bertrand’s presentation on New Features at the PASS conference 2011(http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/07/sql-server-v-next-denali-changes-to-system-objects-in-ctp3.aspx)