Slow Rollback, Reboot fixed, why?

I posted this on SQL Server Central, where it will get more attention than it will here, but wanted to put a copy here as well.

Today I ran an 'emergency' script to fix a problem in production. The script does 40,000 updates, each of which fires a trigger that does 1 insert. I should have disabled the trigger first, but didn't (mistake).

This is a very slow (Pentium 4) server, but it has 2 CPUs. MaxDOP is set to 1 (disabling parallel execution), and hyperthreading is turned off in the BIOS.

After 5 minutes, I realized my mistake not dropping the trigger, and canceled my job, effectively rolling back the changes. I waited another 15 minutes, and as expected the CPU usage of the machine was at 50% during this time. I later found out it was not 1 CPU being used 100% as I expected, but both CPUs actually were used 50%, which I still don't understand. During this time, no one was able to use SQL Server on this machine to do anything, which was also unexpected.

I suggested rebooting, saying SQL would continue doing what it is doing after it rebooted, so logically this makes no sense, but I hate just waiting and not doing anything. After the reboot, I expected the database to take forever to recover, and I came back online almost instantly.

Can anyone tell me what happened and why?

SQL Server Dependencies are Fixed! (almost)

If you look at any good article on what is new in SQL Server 2008, buried at the bottom under a bunch of nonsense about spatial data types (I still haven't found anything I cannot do just as with standard data types, in the unlikely event I will ever need to) and other bell's and whistles, it is possible they will mention dependency tracking has changed. With the possible exception of the new date data types (again, not at the top of most lists of changes) this is the biggest improvement vs. SQL 2005.

For some strange reason, SQL Server's syntax always let you reference a non-existent table without giving you any warning, even though referencing a non-existent column in an existing table gave you a syntax error. Both of these should have been warnings, but should let you create your stored proc anyway, in case the non-existent reference exists at runtime.

What SQL 2008 fixed is it keeps track of the missing dependency - as opposed to early versions which just ignored it, and did not update a reference to it. SQL 2008 is still broken - references can still get out of sync. Before doing anything important, you can updated all the dependencies with:

SELECT
'EXEC sp_refreshsqlmodule N''' + s.[name] + '.' + o.name + ''';' AS [stmt]
, s.[name] AS [schema_name] -- schema name
, o.[name] AS [object_name] -- procedure, function, or view name
, o.[type] AS [object_type] -- type (P, FN, IF, TF, or V)
FROM sys.objects o
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ('FN','IF','TF','P','V')
ORDER BY
o.[type]
, s.[name]
, o.[name]

I apologize if you wrote this script - I did grab this from the Internet without noting the author.


You can then view all your "missing" dependencies by running:

SELECT s.name [schema], o.name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referencing_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE referenced_id IS NULL
ORDER BY s.name, o.name, d.referenced_entity_name


This is still broken in one more import way - if you have code like:

UPDATE T
SET Price = Price + 1
FROM Table1 T

You will get an unresolved reference to an unknown object 'T' rather than a correct reference to Table1. I believe you can change any update query to a syntax that will work correctly, and you can easily find all the places you used the "problematic" sytnax with the above query.


If you aren't entrenched in SQL, this change may sound trivial, but I have had problems with dependencies since SQL Server 7, and having this fixed is by far the biggest reason for me to use SQL 2008.

Another SQL Bug

In our development environment, I found no one could use SQL Management Studio to update data.

We would get:
Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

And then get:
No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The updated row has changed or been deleted.
Correct the errors and retry or press ESC to cancel the change(s).


A search of google and MSDN revealed a lot of people with similar errors, but they had no primary key on the table, or actually had other people change the data. There were also a lot of "why do you want to do this" posts - I really hate it when someone uses that as a reply to a technical question.

Then I found:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289535

I hate having SET NOCOUNT ON, SET NOCOUNT OFF in each of our 1000+ stored procedures, and worse, people do not set these consistently. So I set SET NOCOUNT ON by default. Apparently the team that wrote management studio just assumed NOCOUNT would always be false, and relied on the return values without even checking to see if it was on. Worse, they didn't give me anything in the GUI to use any connection settings other than the default. So if you use SQL Server 2005, you can either get the reasonable default setting of NOCOUNT to ON OR use Management Studio to enter data. You cannot do both.

I am really hoping this is fixed in SQL 2008, which I haven't had much time to try. Given the virtually unlimited money and years of time Microsoft invested, these things annoy me.

BTW - NOCOUNT=TRUE is like a double negative - this is a bad name. Why not SET COUNT=OFF insead of SET NOCOUNT=ON?

Both of my coworkers ran into this issue, but ignored it instead of figuring it out.

Science Cafe

Monday, I went to the Science cafe (http://www.greatlakesbrewing.com/communityEvents.php?action=viewEvent&calendar_id=00000084) which had a presentation by Dr. Jerrold Vitek and Dr. Jay Alberts from The Cleveland Clinic. They did a community presentation on deep brain stimulation, which can be effective for diseases like Parkinson's. Some of the things I found fascinating:

1) This and related research was started relatively recently, by randomly lesioning and/or stimulating random parts of the brain. While this technique does have dramatic improvements for some patients, it was essentially created by trial and error with little regard for the theory of what effect the lesioning or stimulating actually had on the brain.

2) One of the presenters (I am still not sure which) had a Physiology degree. I was surprised to hear that advances in our understanding of how the brain works since this technique has started haven't helped much. He said better understanding of what structures do what things has given them ideas for a starting point, but again, progress is largely by trial and error.

3) Lesioning and stimulating seem to have similar effects. Obviously, stimulating is less permanent, and allows better fine tuning.

4) A friend and former coworker had this procedure done, with good results so far, probably by the people doing the presentation.

5) The effect of stimulating is not always immediate. Sometimes turning on/off the stimulation changes the problem like a light switch. In other cases, it can take days before the effect starts, and the effect can last for weeks weeks after stimulation stops.

6) Bilateral stimulation seems to result in a drop of cognitive ability, where unilateral does not. The research gave a vague answer when I asked why, and seemed to be much more interested in helping patients avoid the effect then explaining why this happens.

7) The researchers complained about lack of funding when asked one question about further research. I doubt leading researchers at The Cleveland Clinic are not worried about where their next meal comes from, but still don't understand how we can spend nearly 20 billion/year on NASA exploring outer space, but have little to no funding to understand something far more interesting and relevant. I follow NASA a lot, and their money is far better spent than much of what our government does, but they are an easy target having a large budget publicly available.

8) At least in one case, an insurance company paid for probe's to be implanted in someone's head, but denied the cost of the device to do the stimulation. I guess they thought wearing the probes would somehow help.

GAC

If I am on a computer named "box1" and type "\\box2\c$\WINDOWS\assembly" into my run directory and see a GAC window, you might assume I'd be looking at the GAC on box2. You'd be wrong.

Pulling up this directory on in windows explorer causes Windows to display the contents of the GAC on the local machine instead of the actual directory's contents. Browsing to this folder over the network does the same thing - show the GAC of the LOCAL machine.

Carnegie Mellon

I can finally finish my application to earn a PhD at Carnegie Mellon. I am posting this entry since they ask for a homepage on my application, and I don't even have anything yet on this blog related to what I want to work on. This is not because I haven't been reading and working on anything related to my PhD, or I'd rather write about business software, just that this blog has been about what I am working on, not what I would like to be working on. There are no jobs for researchers in anything interesting with only an M.S. (which I only recently finished anyway), so I do a lot business programming, accounting, sql databases, websites, etc. What I do for a day job is not bad, and certainly could be worse, but I want the PhD to be able to work on what I find interesting, not what employers find profitable.

I may start adding research notes to this blog. So far, despite having a small number of entries, it has been useful, if only just for my own reference. I doubt anyone from CMU is going to look up this page, but if you are from CMU reading this, please post a comment to prove me wrong. A blog is not the best source for determining how good a student is, but it is probably better than just going by GRE scores.

log4net FileNotFoundException

For the longest time I was getting a FileNotFoundException debugging on my local when trying to use log4net in an aspx app. I would always break on ConfigureAndWatch in the Global.asax. Since I knew log4net was working, and the correct version was loaded from the GAC, I aways just hit "continue" and never spent any time on it.

Today I noticed log4net itself appeared in the call stack when this exception was thrown. This is odd, the assembly is throwing an exception looking for itself. I loaded the debug symbols for both log4net and the system assemblies, which is a great thing to be able to do, and found it was thrown from System.Type.GetType which was being used to dynamically load the PatternLayout class from log4net. Somehow this was incorrectly specified in the logging.config file I used to configure log4.net. Somehow log4net was not smart enough to know I incorrectly specified a loader, and the system exception, that the assembly could not be found, was being thrown.

SQL Server Statistics

Today I found a problem with indexed view performance getting worse over time. This is another post I am creating mostly for myself, but this may be useful for others since it was pretty hard to find all the details on the web. One of the most important factors to SQL Server performance is up to date statistics. Using automatic statistics (enabled by default) does a good job, but we found the need to run sp_updatestats during periods of low activity. We use a Prestonia based server (waiting for Nehalem) for a 5 GB database with 100+ active users, so every bit of performance helps. Automatic statistics and sp_updatestats handles everything well except indexed views.

sp_updatestats ignores indexed views. Microsoft claims this is because statistics on indexed views are ignored except when using the WITH (NOEXPAND) hint. You need Enterprise Edition to use indexed views without the (NOEXPAND) hint, so the only explanation I can think of for this behavior is somehow Microsoft thinks screwed up statistics will sell more Enterprise licenses. If they sell more of anything, it will be Oracle licenses. I have never seen automatic updates work on indexed views either, although I haven't seen this documented.

The result of this is that indexed view that sped everything up when you created it is getting further out of sync with its statistics. Eventually this leads to horrible execution plans that just keep getting worse. The solution, first you should run:

SELECT so.name,
si.rowmodctr,
si.rowcnt,
CASE WHEN si.rowcnt > 0 THEN 100.0 * si.rowmodctr / si.rowcnt ELSE 0 END PercentError
FROM sys.objects so
INNER JOIN sys.sysindexes si ON si.id = so.object_id
WHERE is_ms_shipped = 0 AND indid = 1
ORDER BY 4 DESC

Running this periodically will show you the state of all clustered index statistics, including indexed views and regular tables, and is a good thing to pay attention to regardless of if you have Enterprise edition or use indexed views.

You will need to update stats for each indexed view similar to 'UPDATE STATISTICS vAcBalanceBill', I have not found another way to do this. Setting this up in SQL Server Agent mostly takes care of the problem.