Well, after thinking I had my SQL problem licked, it came back. I finally got a callback from a support guy at Microsoft, and he had me update statistics on all the tables involved. That fixed things, and everything has been working now for the last couple of days. I'm pretty sure I'd updated statistics on all these tables at least once since the original problem occurred, but maybe I didn't, or maybe I just needed to do it two or three times to get everything back to normal.

I've always had a maintenance plan that updates statistics on all my tables once a week, and I don't think I've ever had problems with statistics before.

I've been reading up on this whole statistics thing now, which I admit I hadn't paid much attention to, until this little debacle. Here's a blog entry by Kim Tripp that explains a few things about the auto-update mechanism, and another one by someone at Microsoft that explains a good bit about the way statistics work in SQL 2005.

Labels: SQL, Windows

After all the patching I did over the weekend, it seemed like everything was fine on my main SQL server. However, on Monday, we discovered that a couple of oddball SELECT statements weren't working anymore, returning the error "Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

To make a very long story short, I eventually figured out that I could get these queries working again by deleting all of last year's data from one of our bigger tables. The table in question had about 32 million rows in it. I cut it down to 13 million after getting rid of the 2007 data. The moral of this story, apparently, is that you shouldn't try to store 30 million rows in a single SQL Server table. I say this with tongue in cheek, since, obviously, there are other people out there with very large tables in SQL Server. Apparently, I can't be one of them, though.

I put a call in the Microsoft support this morning, but they never called me back. Maybe tomorrow. If they do call back, and I get a support guy who seems to be a bit less clueless than usual, maybe I'll try to see if we can figure out why my server chokes on this table. If I get the usual clueless support dude, I guess I'll just tell him I figured it out on my own while I was waiting for a callback. I wonder if I get my support incident credited back if I solve my own problem while I'm waiting for the callback?

Labels: SQL, Windows

One of the people I work with was having some trouble with reference vs. value types (in C#) this week. This article seems to give a pretty clear explanation of how these things work. Always good to have a little refresher on the fundamentals.

And, in poking around on this guy's web site, I found LINQPad, a tool for executing LINQ queries. I have to admit that I haven't had any time to play around with LINQ at all, but I'm really curious about it. I like his idea of trying to do all your ad-hoc SQL queries in LINQ for a week, to force yourself to start getting used to it. Some of the stuff I'm doing this week is requiring me to do a *lot* of ad-hoc SQL, though. My brain is so completely wired for T-SQL at this point that I think I'd get really frustrated really quickly trying to use anything else.

Labels: csharp, programming, SQL

I just got done installing VS.NET 2005 on my new desktop machine at home. I'm still working my way through ASP.NET 2.0: A Developer's Notebook. I'm just starting the Data Access chapter. The limited version of SQL Server that comes with VS.NET does not have the pubs or Northwind databases installed by default. If you want to install them, you can find setup scripts for them under "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Samples\Setup", named InstPubs.sql and InstNwnd.sql. To run the scripts, do this:

sqlcmd -S .\SQLEXPRESS -i InstPubs.sql
sqlcmd -S .\SQLEXPRESS -i InstNwnd.sql

That seemed to be worth writing down, just in case I need to do it again...

Labels: asp.net, SQL

Style: Normal Basic