Monday, February 04, 2008
#
This is a pretty interesting blog from Microsoft about how networking works in SQL Server:
http://blogs.msdn.com/sql_protocols/default.aspx
I found them when I did a google search for a sql server error 18456. They're encoding the actual problem in the STATE information:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Of course, when you fail to log in using the SQL Server Management Studio it doesn't display state information, so this information is absolutely accurate and completely useless for trying to diagnose connection issues from the studio.
Tuesday, January 29, 2008
#
The smartest guy in video games just did something absolutely incredible.
Steam is really good stuff. Steam encapsulates many of infrastructure needs of a gaming company. Software distribution, advertising, pre-sales, sales, post-sales, special sales, credit card payment, subscription sales, on-line matching, cheat prevention, ranking systems for online matching.
Gabe Newell, the guy who runs Valve, is a genius at this stuff. He's brought us some of the best games ever (Half-Life, Counter Strike, Portal, Team Fortress 2). But this may go down as the biggest thing ever.
OK, here's what he's doing. He's built all that infrastructure, and he's giving it to his competitors to use to distribute their games. Valve gets a cut, but it's a WAY smaller cut than a retail arrangement would take, and that cut gets them vastly more services.
He's empowering his competitors to compete with him on even ground with respect to distribution, AND MAKING MONEY WHILE DOING IT. And helping game developers everywhere spend more time making more games better and less time writing the same networking code over and over again.
So that means that the games need to compete based on quality: is the game reliable and is it fun? That's something that Valve can compete on successfully. And if you don't know why, you haven't played Portal.
Monday, January 28, 2008
#
I was reading an article on Slashdot (
here) about a piece of software that creates a customized Windows Vista installation. Nothing new there, Windows has had functionality to create custom unattended installations for a long time. This particular package, however, is built to make Vista smaller.
I've never actually installed Vista. I read reviews, talked to people and decided it wasn't ready for me. Normally I'd try it out and see for myself, but none of the reviews I read suggested that it was worth the fairly substantial investment required. So I didn't realize that the installation requires 15GB of disk space until I read the article.
Gigabytes. 15 of them.
The mind reels.
All I want from windows is an operating system. That means, to me, device drivers, a reasonable interface that can be used to access those device drivers (i.e. DirectX), a shell to manage which programs I start and stop, and a web browser I can get patches with. The ability to run Terminal Server client is a plus, but for work I have a real computer, with a supported terminal server client.
15gb. What is it doing with 15gb.
Windows Media Player. Why? Don't use, do not want. VLC is the way to go by the way. Free, reads everything, and if you find something it doesn't read it uses plugins so it will probably read it soon anyway. It's a very small download.
Internet Explorer. So, did they integrate IE into Windows to (a) tick off anti-trust regulators, (b) escalate all browser vulnerabilities to OS vulnerabilities, (c) artificially increase their installed base marketshare, or (d) all of the above?
MSN. Um. OK. Anybody really think this should be part of the base operating system installation?
What else is in there that can possibly take up that much space? I don't think there are that many device drivers on the entire planet, and the tales of devices without Vista support are all over the place.
I think their concept of a "MinWin" is a good one. I'd love to see Windows sold as a base operating system with a stripped-down browser that I can completely disable with a password after I get a real browser installed (but don't remove it in case I need to install a different browser, disable it to keep it "pure"). It would reduce the number of "required" updates since the operating system is a very small subset of PC functionality.
Reduced complexity is a good quality for critical components. Operating systems are critical components. Windows is evolving in the wrong direction, becoming more complex and ornate instead of more simple and complete.
Tuesday, January 15, 2008
#
As part of today's MacWorld festivities, I was reminded of something. Office:Mac 2008 doesn't support VBA anymore, and VBA support for the next version of Office for Windows is supposed to be limited to the ability to upgrade scripts to whatever new thing Microsoft has to replace VBA.
This is in addition to the issue I ran into a few weeks ago, where I was upgrading a system from SQL Server 2005 Standard to SQL Server 2005 Standard x64. DTS doesn't work on the x64 edition. Well, it's not supported. I got it to work, mostly, but it's not supposed to work, and I'm dreading the next service pack that may break it.
Many years ago, I helped write a very complicated system that collected data from many sources, transposed it and loaded it into SQL Server tables for use by OLAP. The accepted and probably recommended approach would have been to use DTS. Instead of using DTS, the files were collected with xp_cmdshell, parsed with a Perl script, loaded with BCP (this was before the Bulk Insert command happened) and the data was refined with Transact-SQL. Why did we use this approach? Because it was manageable and looked like it'd last the longest time.
I pity everyone who's developing using SSIS. I hope you enjoy re-doing all that work. Make sure you keep all your specs, in hard copy, because who knows what Microsoft Word's going to be able to read by then.
Friday, December 28, 2007
#
I'm working with a vendor's system that has a very severe performance problem. I'm looking at it because despite the fact that I told the vendor there was a problem with a particular query back in OCTOBER and that the problem was going to degrade linearly with the data size they didn't (1) fix the query or (2) provide us with a data archive system, and now it's an emergency.
Sigh.
The reason the query is performing badly is that it's a query of a view. Not a stored procedure containing a query of a view even, but their application directly does a select statement.
Problem 1: All data access is to be done through stored procedures. All of it. If you are writing an application and don't follow this rule, please just kill yourself now and save me the trouble of having to track you down.
It's a query of two views, using the old join syntax...
Problem 2: It's an OLD join syntax. There is a NEW join syntax. It doesn't suck like the OLD one did. If you use a comman in your WHERE clause, please just go back to writing user interfaces.
One of the views is a 5 part UNION ALL, which contains in one part another view, which is itself a big pile of UNION ALL's.
Problem 3: Excessive use of UNION ALL is prominent in poorly constructed databases. UNION ALL is generally called for when you're generalizing data, making one thing look like something it's related to. If they're close enough to be related together in this way, USUALLY they're the same object and you're thinking about the problem wrong. "We need to use UNION ALL so we get a transaction total across our 5 order tables". Why do you have 5 order tables? Poor design. One order table. 5 order detail tables that differentiate the transaction types.
With all these problems combined, what should have taken about 5 minutes to figure out took more like 4 hours to diagnose, fix, verify, and implement. The problem turned out to be that one of the deepest views was not filtering properly, instead of filtering down at the view level, the SQL Server query optimizer decided to join the 250K rows to a few other tables and then filter it down to 0 rows.
When you write complicated queries, you give the optimizer a LOT of chances to screw up. And it will take every chance it gets to screw up and pick the least efficient strategy possible far more often than you'd like.
"Oh, but if you had up to date indexes and statistics that wouldn't happen"
Uh huh. Right. Pull the other one it's got bells on. Indexes rebuilt nightly, because I have the time to do it.
The optimizer is usually pretty good. "Usually" usually doesn't cut it.
Write small queries, store temporary results, combine the temporary results. "Oh but that's space inefficient". Uh huh. Look at your query plan. See that "Spooling" step? Guess what that does! It creates a temporary table for intermediate results. It's the same work either way. One way you control how the work gets done, and you understand your data and business rules. The other way SQL Server controls how the work gets done, and it doesn't understand your data or business rules. One way you can highlight part of your query, run it, and performance tune it one piece at a time. The other way, you get an atomic operation, maybe not done in the order you'd like, and the order is somewhat arbitrary. The optimizer does things in a specific order based on data size and index selectivity, but it's not always in the RIGHT order.
Tuesday, August 14, 2007
#
Message
Executed as user: CUSTSVCDB01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:00:00 PM Progress: 2007-08-14 16:00:11.66 Source: {4E701E84-81ED-4B7D-A3B4-ACBF7D0CAC3F} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:00:00 PM Finished: 4:00:12 PM Elapsed: 11.344 seconds. The package execution failed. The step failed.
That's what you get back if one of your maintenance plans fails. Let's consider, for a moment, how totally utterly and completely full of fail this error message is.
First, no carriage returns. I guess we just have to scroll right. And scroll right....with the mouse.
Second, every single piece of useful information in the message is truncated. There's no error message, that was too long, so it's truncated. The actual SQL run? Truncated. But, you know, we did have plenty of space to fit in A FULL COPYRIGHT MESSAGE. Because otherwise we might for get that MICROSOFT(R) SQL SERVER(TM) is ALL RIGHTS RESERVED by MICROSOFT(R). And I know, down to the MILLISECOND when the package step execution started. Yup, great, don't truncate off the MILLISECONDS, but do truncate the ERROR MESSAGE. Good thing you fit that entire meaningless GUID in there too. Otherwise you might have had space for, I dunno, an ERROR MESSAGE.
Third: 100% Complete. There was an error but we finished? No.
Fail. This thing is so full of fail, it's like a bucket full of fail, overflowing and smelling of wet dog.
"oh, but there's a whole file that contains the error output..."
FINE THEN GIVE ME A LINK TO THE DAMN FILE NOT A COPYRIGHT MESSAGE. FAIL.
Friday, July 20, 2007
#
Examining execution plans is the key to improving query performance. The knowledge and experience necessary to look at a query plan and say "yeah, that's where it screwed up" and fix it makes all the difference in the world. I'd submit that query plans are one of the single biggest tools in a DBA's toolbox for fixing performance problems.
So why does the UI suck so bad?
I just pulled up a query plan for a fairly complex query. I can't find the problem. Why? Well, across the several hundred steps, I can't see anything that's not taking 0% of the query time. There's no search feature. There's no ranking feature. Here's a feature suggestion for the query execution plan screen: When I hit ctrl-F, let me search for things. And add a new hotkey to take me to the node in the query plan with the most cost, then the next most cost, and so on.
I could print it out and look that way instead of scrolling (A new feature in SQL Server 2005, before we had to printscreen. yeah, printscreen) but it would take 165 pages. And most of those pages are BLANK. And there's no option to supress blank page printing. The first page is literally a line across the middle of the page with 3 nodes on it.
OK, I have to ask. Is there anyone that does usability testing on the SQL Server development tools? Seriously. Anybody? Does Microsoft invite a couple of DBA's, even some SQL Server MVP's, up to Redmond, sit them down and ask them to do things with the user interface and watch them? Why not? Throw them an invite, buy them a plane ticket and have them sit down in front of a computer and look at query plans, print them, and try to do their day jobs with the tools you've provided. Gah. At least get a couple of people from the Office team come over and show you how to make a UI. This thing's abysmal.
Yes, I know that the SQL Server tools are the best of the tools that ship with any DBMS. That's like saying you've got the best congressman though. News flash: The best turd in the world still stinks. Stop playing to the level of your miserable competition and start playing to the level you want to be. Being the best in the industry isn't good enough if the rest of the industry is writing smelly poo.
Tuesday, July 03, 2007
#
I still hate them.
But not as much as I did. With a massive computer upgrade they're now performing tolerably. Not great, but tolerably. They use something like 118 mb of RAM and take forever to load, but not as long a forever as it used to be. I still think there should be a quick to load lightweight easy to use query tool. I just want something to pull up a query window with load/save file options and a quick link in a menu to books online.
The inconsistency of the UI still amazes me. So let's see...if I double-click a maintenance plan, it comes up in a tabbed view, but an error log comes up in a new, floating window.
That said the error log viewer is now actually usable, which is good because my sysadmin hasn't quite figured out how to add me to the remote desktop user's group on the servers I'm managing. That's OK though, the same folks run the phone system, I've been here or a week and my phone doesn't work yet either. Unfortunately these are the same people responsible for my tape backups, so (1) I have to be nice to them, and (2) I'm going to be checking my tape backups every week or so because if they can't get a phone (150 year old technology) working what are the chances the tape drives (which are finicky unreliable horrible tech at best) to work reliably. I'm going to be pushing for a backup-to-disk solution soon I think, at least for the database servers. Gimme a half dozen of those old-school removable slide-in-slide-out Serial ATA trays and a bunch of the new 1 terabyte disks and I'll just do it old school anyway.
Wednesday, June 27, 2007
#
One of the things I'm investigating is DR, actually not really disaster recovery, more of a "service availability" problem. I'm not trying to address the DR situation yet, just the "OMG SERVER CRASH" problem.
So I started doing research on clustering vs mirroring, since I hadn't done it before. If you're researching mirroring, start here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
That's a technet whitepaper on mirroring. After you're done with that, go here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Performance implications of mirroring, along with best practices stuff. The interesting part of the second paper is the performance implications of the encryption options (negligible impact, should always be turned on). I found it fascinating anyway.
Thursday, February 22, 2007
#
I want to see a structure that I can resolve the following for without using a table scan:
Find me all of the appointments that Amy, Bob, Charlie and Dyan have been in that have ever occurred on Wednesdays in July, August, or September between 8 AM and 3 PM between 1/1/2003 and 12/31/2006.
Find a solution that's a better order than O/2, where O is the table size.
The key to the problem is how we're storing dates, and the fact that without doing something crazy with a star schema kind of model it's going to be very difficult to get this information back in a reasonable time, and using a star schema here would be ill advised since it's an OLTP system, sorta. The way to handle this in a relational model would be, I guess, to store year, month, day, hour in separate fields and index them, with a clustered index on the person with the appointment.
Friday, February 16, 2007
#
Anybody upgraded yet?
For gamers, I've only seen very very disappointed reviews about frame rate performance, apparently because hardware vendors weren't quite ready yet, especially for NVidia cards.
Belkin Nostromo drivers appear to be available if you email their tech support folks. They are very slow getting drivers out but their support staff is actually pretty quick.
I've got a machine I've given up on getting Linux to work on, so I'm going to try and snag a copy of Vista Basic or XP Home for, just wanted to see opinions.
Wednesday, February 14, 2007
#
So, occasionally I get a problem where I need to reboot my workstation. It's infrequent, usually I only have to do it every couple of days, but the system starts to run slower and slower and...well, time to reboot. I needed a new cup of coffee anyway.
Get back from the break room with my coffee and my computer hasn't rebooted. A dialog box tells me that it couldn't reboot because a program needed my attention.
The attention was required acrobat reader, which I didn't have open and didn't have a taskbar entry for, and the particular attention required was to hit OK to acknowledge that acrobat reader had crashed.
So, two issues here.
One: When I say reboot, I mean REBOOT. I mean "I'm going to go away now, and when I come back I want to see a login screen". I don't mean "I'm going to walk away now, and when I get back I want to see an unlocked workstation asking me to click OK when there's no other option BUT to click OK."
Two: I uninstalled Acrobat Reader. Adobe can bite me. Acrobat Reader has all of the bloat of Microsoft Office, and none of the QA. It nags. No, I don't have your photo management software installed, why do I need to update it? Why do I need to update Acrobat Reader? Is it not reading things correctly? Why does it insist on asking me EVERY TIME I OPEN A PDF if I want to install photo management software. NO I DON'T. GO AWAY AND LET ME LOOK AT THE DOCUMENT. And now, apparently, even if you close it, it stays in memory, and eventually crashes.
Uninstalled all adobe software from my workstation.
So how do I deal with PDF's now?
http://www.foxitsoftware.com/pdf/rd_intro.php
Free. 2MB download. Installs in 5 seconds. Free. Small. Reliable. Not a browser plugin, so guess what: It's FAST. It's also a very small executable, so it loads fast. It actually WORKS and stuff.
Monday, October 16, 2006
#
So, Dave and I were having a conversation about clustered indexes over lunch yesterday.
Yeah, we're this boring.
Anyway, I have an issue at work where I have a table that has a horrible clustered index. It's not very selective (the first key can return upwards of 80K rows) and the first key changes frequently when the row is updated (leading to fragmentation). It's great for one semi-frequent query in our system, but really isn't useful for most others. The problem is that SQL Server (talking SQL Server 2000 here) uses the index A LOT in the form of clustered index scans. To the point of ignoring every other index on the table. If I go in and search anything but the PK, it will ignore every other index in the table and use the clustered index. Period. No discussion. I've gone to using index hints to force it to use the correct index for the query, which actually improves the search by a factor of 10 to 100 most times.
The problem is that SQL Server assumes that a Bookmark Lookup step has a near-infinite cost. So it will do anything, including the equivalent of a full table scan, to avoid that bookmark lookup. Which is just insane in most cases.
The table also has this nasty trigger on it that basically serializes any rowset insert, updates, or deletes. There are good reasons for this, but it still sucks. There are no set-based operations on any data modifications in this table. As a result, data modifications take a very long itme.
So then the discussion took a turn.
"Why do you have a clustered index".
Well, because you have to.
"No, you don't"
hmm. No clustered index. No bookmark lookups. No completely ludicrous query plans to avoid the bookmark lookup. No tablespace fragmentation. Horrible insert performance, but it's not gonna get any worse than it already is with that damn trigger on there anyway. Every index on the table stand on it's own merits, equally likely to be chosen based on its selectivity, not on some artificial and horrible decision made by the query processor to avoid a bookmark lookup that would likely cost nothing anyway.
Hmm.
If this was true, we'd see behavior where if you have a table with a clustered index, pretty much any non-clustered index that isn't a fully covering index is next to useless. Which is generally the observed behavior.
So the choices are: Clustered index and every other index is covering for a particular query, or no clustered index and let the other indexes fight it out for selectivity for each query.
The nonclustered index approach looks to be more flexible in this case. The cost, of course, is insert performance. Which is what I'll be testing today.
Tuesday, June 13, 2006
#
The company I'm currently working for hosts a web application. The application uses various bits of downloadable componentry to perform fie upload and extremely trivial image manipulation that run on the client side.
Why use web applications over fat applications? The obvious reasons:
Ease of deployment - Web applications don't require deployment of client software to end-users.
Cross platform - Since the application environment is a web browser, and web browsers conform to certain standards, creating a cross-platform web application is easier than creating a cross-platform fat application.
Instant distribution of application updates. Since the software is all centrally contained on a controlled web farm, upgrading the application or applying simple patches is much easier than requiring thousands of clients to update their software.
The problem is, NONE of these are true statements once you start dealing with downloadable components with .Net:
Ease of Deployment: We pay a support person full time to diagnose issues with spyware, adware, malware, and even anti-spyware, anti-malware, and anti-virus software blocking the installation of our components. Why? Because downloadable componentry is such a great vector for infecting computers, and the people who write such applications are very protective of their turf. So once you get a piece of nasty code installed, the first thing it does is secure your system against other invaders. Or from our legitimate, signed code. Anti-spyware software does exactly the same thing, as part and parcel of securing your computer it prevents our components from downloading. We have no ease of deployment. Every deployment is a risky venture. "Oh, but we have a tightly controlled corporate environment where we strictly control what software everyone installs on their computers!" Fine. Install a fat app then. You are gaining nothing by writing a web application, if you have that level of sophistication, you can easily distribute fat applications to your clients, you can do it in a more controlled manner than having them visit an intranet web site, and you can update more precisely.
Cross Platform: So you write a .Net client-side component and expect it to be cross platform. Heh. Very funny. Once you start writing in .Net you lose any pretense of cross platform capability. "Oh, but all my clients are running Windows anyway". Fine. Write a fat app then. Writing things to run inside Internet Explorer is just holding you back, you gain nothing from artifically limiting your architecture to Internet Explorer.
Instant distribution of updates. Our component uses .Net 1.1. If you download .Net 2.0 and access our site, it will crash your browser. So, no, you don't get any updates, and not only that but if you use another vendor's software that requires .Net 2.0, we have to provide you with a "special" patch. Not only are .Net 1.1 and .Net 2.0 not compatible with each other, they seem in our case at least to be actively hostile towards one another, to the point of crashing Internet Explorer. Yes there are workarounds. Unfortunately, our .Net componentry is so sophisticated we have a long development and testing cycle to prevent QA problems form effecting our customers, so we can't update it either.
Using the web server to pass data back and forth, so you need to have a web application? Use SOAP. Transfer data over port 80 to a web service running on your server. Heck use RPC over well defined ports. THAT'S WHAT IT'S THERE FOR. SOAP is a crutch to get out of doing paperwork to get real RPC working through a firewall, and a dangerous crutch to boot, it simultaneously obfuscates your messages so your intrusion detection software can't properly monitor what's going on AND it eats a huge amount of bandwidth.
My favorite part of this was the discussion I had with one of our developers. It boiled down to this: the bane of a web application programmer's existance is the BACK button.
Look. If your application can't deal with a back button, then it shouldn't be in a web browser. Period. Fix your application, don't tell your support staff to tell your users to not use the back button. It's like telling someone not to use the backspace key on their keyboard, and almost as effective.
So why do developers and desginers focus so much energy and effort towards writing web applications instead of fat applications?
Web applications are the new hotness. Fat applications are old and busted. In other words, resume buffage and increased chance of people with the word "Chief" in their title buying into your product and authorizing your budget. So compromise your architecture and the usability and maintainabiltiy of your software in order to get buy-in from the chain of stooges.
Overcoming corporate inertia. In order to drop a new application onto your company's PC's, you have to go through rigorous testing of functionality and interoperability. You can probably circumvent a lot of that by using a web architecture instead, where you can download components onto computers and be guaranteed they won't crash any other fat applications. Unfortunately, you still have all of the version incompatibility problems of a fat application environment, so you're really just cheating to get around the barriers put into place by people trying to provide a supportable environment.
Overcoming Corporate Inertia, Part 2: Using anything besides port 80 traffic on the public Internet would require getting your firewall rules adjusted, while using SOAP gets around that process. Of course, using SOAP also bypasses a lot of the intrusion detection capabilities of your firewall, so you're essentially circumventing the people trying to keep your network safe.
Looks like the main reason developers are biased towards web applications is because corporate IT bureaucracies are broken.
So can anyone explain what I'm missing here? Why does .Net componentry suck so bad, and why do people still use it instead of .Net fat applications, or just plain old fat applications in general?
How do we fix it?
Quit using your wrench as a hammer. Web applications have a lot of advantages, specifically ease of distribution, update, and cross-platform capabilities. If your architecture requires you to use techniques that abrogate those advantages, then don't use a web application. If you require .Net client-side componentry, you are no longer cross-platform. If you have a very stable and capable application deployment mechanism, you don't need easy deployment, you already have it. If you have a long, involved QA process for making server-side changes that turtles along at the speed of smell, then making the application easy to update frequently is a waste of effort.
Wednesday, December 07, 2005
#
Evaluate and comment on the following general statement:
In a well designed database, the clustered index should never be on the primary key.
The point being that the normal use case isn't "I know the primary key and need to find the rest of the columns", but instead "I know data in 3 columns, show me all the records that match so I can find related data using the PK elsewhere in the database".
I think that this is the single biggest performance blunder done by programmers and n00b dba's. It's also incredibly inconvenient to fix, requiring a lot of locking and basically 2 complete table reorgs (someday someone will explain why dropping a clustered index is such a hard operation, but please not in this post).
The only case I can think of where it's always false is a table where you have a surrogate key (usually an identity column of some kind) providing the PK, where the surrogate key is provided to customers (i.e. an invoice number or confirmation number).
I'll question the validity of your business requiring customers to remember invoice numbers and the impact on your perceived customer service later.
rs.