Category Archives: Development

Admin, Development, Time-savers, Uncategorized

Red-Gate is Badass!!

5 Comments

Ok, so the other night I uninstalled my R2 install on my box and put on the new SQL2012.  I know upgrade exists, but I just wanted the thing on there and didn’t feel like messing with anything stupid going wrong.  So anyway, I was half way through my uninstall when I remembered, Oh Crap, I’m gonna lose SQLPrompt cause SQL2012 just came out.  I was pretty upset because I use that tool all the time.

So While Denali was installing, I went out to Red-Gate to see if they had it listed anywhere when SQLPrompt would support Denali… and you know what?  It already does.  I installed the newest version and it works beautifully in SQL2012.  And that’s not all.  They’ve got this kewl new thing they’re doing where they let you see beta features.  They call it Experimental Features and it’s at the bottom of the SQL Prompt 5 menu.  Here’s what it looks like.

These are beta features and you’re welcome to install them and try them out as you wish.  Personally I love this because I quite often want stuff like this and even if there are a couple bugs it’s still better than not having it at all.

So Red-Gate, I salute you for having this ready in time for the release and for doing such a good job with presenting us with these new features.  I want to have your babies.

Admin, Development, Time-savers

Partitioning and the free lunch

Quite often there’s no benefit to a feature if you’re not using it, but this time that’s not the case. Even if you’re not using partitioning you can still take advantage of it in a really cool way.

Let’s say you’ve got a table that you load every night, and you want to move the current data to a new table and truncate the current one. Here’s how you’d do something like that today.

1. Select * into T2 from T1
2. Insert into T2 select * from T1
3. Rename T1 to T2. Then recreate T1.

But let’s face it, none of those choices are really ideal as they take time. Though of those I would choose 1, 3, 2 in order. I don’t like renaming objects because the new object now has a new ID, and it’s hard to track them over time if they keep changing IDs. So those are your choices if you’re not taking advantage of partitioning.

If you ARE taking advantage of partitioning however, you’ve got a different choice. Now you can switch the data out of the current table and into the 2nd w/o any data actually moving at all. And the best part is that you don’t even have to have partitioned your tables at all. That’s right… your tables are automatically partitioned as a single-partition table so you’re using partitioning whether you know it or not. So that means that in order to truncate T1 and move its data to T2 you only have to do this:

alter table T1
switch partition 1 to T2

Now there are some caveats, but as with most things it’s not too bad. The biggest 3 are:
1. The table structures have to be identical.
2. They have to be on the same filegroup. So the partition you’re switching out and the one you’re switching it into have to be on the same filegroup.
3. The target table/partition has to be empty.

There are some others that are advised, but those 3 are the only show STOPPERS. And once you’ve got the data into the new table, you can then move it to a new filegroup, or do whatever with it you like.

So anyway, it’s a wicked cool method for getting rid of your current data even though you’re not officially partitioning your table.

Admin, Development

A Round of Patching

1 Comment

I just finished an interesting email thread with a user that’s not at all an uncommon scenario.  They were just installing a CU to some of their SQL boxes, and there was evidently a new bug that was introduced into that CU that causes their SSRS reports to rendor incorrectly.  The sad thing is that this is the kind of thing that should have been caught in testing.  Now of course, I don’t expect them to find everything during testing, but the simple rendering of reports should have caught early on.  Because the second they turned their reporting structure back on, report rendering started messing up.  Now, he assures me that they tested it, but what exactly did they test?  Did they do any user testing at all or did they just test the install of the patch itself?  I see that quite often.  I see shops claim to do patch testing, but all they’re concerned with is whether the install itself will fail.

Most of the time I blame managers and companies in general for his because the IT staff knows what’s at stake more than anyone but they’re not given the proper time and resources it takes to properly test.  Managers always claim to know the business needs better, yet they never seem to fully grasp the full implications of putting untested code into production.  All they want to know is will this bring down my server.  Unfortunately that always means something different to them than it does to us.  What they mean is will this fail and put us into an emergency situation?  And the answer is probably not.  However, disabling a critical piece of your business is putting you into an emergency situation.  And in this case they’re lucky it’s just report rendering.  What if it had been something more serious that caused some kind of logical corruption in their processing?

I would say that quite often the biggest problems caused by hotfixes are performance-related.  And by not testing a CU or hotfix properly you could bring your entire business to its knees.  You can slow your website down so much that nobody can get any real work done.  And what does that do for the reliability and confidence of your business?  It would be really nice sometimes if we in IT didn’t have to rely solely on people who know nothing about it to get our jobs done properly. 

And what’s amazing to me is that companies today still, after all this time and all the mistakes, don’t think it’s necessary to hire competent people code and test.  Oh sure, they perform lip service about it all day long, but what do they really do about it?  Do they really slow down their dev cycles enough to do benchmarking and architect solutions properly?  Do they spend the money necessary to ensure that their code and servers are solid by hiring enough competent people?  Because every shop I’ve been in works their people so hard there’s no time for anything unexpected to happen.  So I think I’ve said this before, but now I’m saying it again.  It’s time to stop being children about IT.  It’s time to stop letting people who know nothing about it make the technical decisions that effect entire projects.  And it’s definitely time to stop thinking we can get by without testing things properly.  And that includes functional testing as well as performance benchmarking.

Development

A dumb SSRS problem

I spent about an hour last night working to solve this stupid problem with a report so I thought I’d share it with you guys.  I’ve had this problem before, but for some reason I never remember it until I’ve had to work through it for a long time.

The problem is that I was trying to put a simple graph on the page and I often like to work with really simple test data.  So I chose a column that had the same value all the way down which would give me a straight line on my graph.  The problem is that I had 3 spikes.  There shouldn’t be any spikes;  the data’s all the same.

I laid the data out into a table in the report and the data was certainly all the same.  Then I ran the report query in SSMS and verified again that the column only had a single value in it.  So where were the spikes coming from?  So as it turns out the problem was with both my query and the collections.  I was collecting disk data every 10mins from a server and putting it into a table.  What I wanted was to graph today’s data by time.  And what I forgot to do was to limit the query to just today.  And since it collects the data every day at more or less the same times, there were some times from yesterday that were the same as the collection today, so SSRS was adding the values together.  And once I limited it to just today, then the spikes went away.

I consider myself lucky that I only spent an hour on this.  But it just goes to show that when data’s involved, debugging instantly gets harder because you tend to make assumptions one way or another.