The best way to learn a programming language, environment or database system is to use it. If you have the chance to play with it, everything you read in the books and on-line about it will make more sense. You’ll get to test ideas, see what happens when you try something this way instead of that way and try out all the neat features that make this version (supposedly) better than the last. One problem though, development tools and environments can be very expensive.
I’m a Microsoft programmer primarily, so lets look at some costs for their products
Visual Studio runs from $799 to $2169 depending on the version.
A license for SQL Server Standard is $7,171. A license for SQL Server Enterprise is $27,495 and those prices are per processor! Have a 4 processor server? Well, you can do the math. But don’t panic, they’ve done something wonderful.
You’ve seen various trial editions that expire after 6 months, but you’ve been busy and barely played with it for a few days. You’ve seen express editions that are very limited in functionality and sometimes cause problems when you try to upgrade from them. SQL Server 2008 and SQL Server 2008 R2 have a beautiful solution.
SQL Server Developer Edition for $50 or under. And it isn’t some cut down version either, you get the equivalent of the Enterprise edition with all the Business Intelligence tools: SSIS, SSAS and SSRS. The only catch is that you can’t use them commercially. If you want to learn SQL Server or improve your skills at home, buy a developer’s edition and play with it.
Never underestimate what the right index can do for you. I was running a query that limited results by an integer representing a date range and it was taking around 37 minutes to run. I didn’t think it all that bad since the tables that it was running against range in size from 20 gig to 100 gig. I did want to see if I could get some performance gains though since waiting around for 37 minutes every time I test a change in the query gets old quickly. I used the Estimated Execution Plan and saw one likely culprit for why the query was dragging, a table scan was happening where I didn’t think there would be.
The column I was using to limit the return was part of the Primary Key, but the query plan was using a date index instead and that just wasn’t a good key for this query. I added a simple index on the limiting column, only took a few minutes even though the table is hundreds of millions of rows and over 20 gig, and then I ran the query again. This time it took just over 5 minutes. I like getting a 27 minute savings from a simple fix.
I’ll likely be putting a version of this query into production scheduled for a daily run. It’ll be a scaled down verison, but I’ll leave the index in place as there’ll be other queries using that limiter. The performance gain was so great, especially if I’m running the query a dozen times perfecting it, that I think it’d be worth adding the index even if the final query is intended to be a one-shot. I can always drop the index when I’m done, but before I do I’ll check the other queries I’m running to see if there’s benefit or hindrance. You never know, it could be a boon elsewhere.
This blog won’t all be bugs, but they’re foremost in my mind right now.
I’m fortunate to be on a job right now where I get to use the latest software. We have SQL Server 2008 R2 installed on our new production server. That server has Windows Server 2008 R2 installed on it. I recently went to check how teh system was performing by opening the Activity Monitor. It lookedlike it was going to open, I got the screen with the three graph boxes at the top and the 5 expandable section headers showed up. As did an error message box.
The Activity Monitor is unable to execute queries against server (server instance name).
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.
Category does not exist. (System)
Unfortunately, there’s nothing you can really do to resume the Activity Monitor or get it working. Perhaps an uninstall and reinstall of both the OS and SQL Server, but that might not do it either and it isn’t worth that kind of effort to get the Activity Monitor working again. I talked with someone at Microsoft and was told it is an intermittant problem and they haven’t figured out why it is happening. Some installs get it, but not all.
Granted, this was a few months ago that I talked to someone about it and they may have found a fix by now. I’m figuring there’ll be a fix for it in the first Service Pack. In the meantime, I’ll have to monitor performance with the other tools you get with SQL Server.
Last year I was starting a new contract and needed to install SQL Server 2008 on the machine they’d provided me. This was a machine that had been used by someone before and instead of reimaging it, they just uninstalled most of the programs and gave me a machine with an OS, Office and not much else. It wasn’t a big deal, as a contractor I have to install software on my own fairly frequently. The problem was that it just wouldn’t work. This was my first time installing 2008. I’d installed previous versions of SQL Server before with little problem, but this install just wouldn’t complete. I’d get odd errors 3/4 of the way through the install.
Unfortunately, as this was over a year ago now I no longer have the exact errors I was getting, but I wasn’t able to find any information about it searching on-line anyway. In the comments of some sites, I found some suggestions that it might be registry related, so I looked in the registry and found entry folders for 80 (SQL Server 2000), 90 (SQL Server 2005) and 100 (SQL Server 2008). Apparently the machine had had 2000 and 2005 installed before and they uninstalled them incompletely before passing the machine on to me.
If there’s an install problem towards the end of a SQL Server 2008 install, check the registry for “80″ folders. Delete these folders and start the install again. SQL Server 2008 should install at this point. Don’t delete these folders if you’re going for a dual install and 2000 is still on your machine. If you’re having problems installing then, it is a different problem. Also make sure you back up your registry before deleting these folders. ALWAYS back up your registry before making any changes.
I work with quite a few different technologies and I’ll use this blog to talk about all of them as I find interesting things to say about each. Currently I’m working primarily with SQL Server and that’ll be the primary focus of this blog as well. Databases tend to be a constant that run through all of my jobs, so that’ll be quite a bit of the discussion here.
Some of the posts will be explanations of things I’ve figured out or pieced together from other sources. Others will be links to posts by other people that I find interesting or valuable. I have a feeling most of the posts will be things I find useful that I want to be able to remember again when I need them making this partly a blog and partly a personal reference book.
I hope other people find value in what I’m saying here and welcome other ideas on how to do things or corrections if I’ve misunderstood something. I certainly don’t have all the answers, but I’d like to find them.
Posted in Uncategorized