Instead of working, I blog.

Man, I still just get so jazzed when I work with powershell. Every time I need to do something long and hard(that was for you Buck), I look in powershell and it lets me just lay back and enjoy the long and hard.

So this time we've got like 85 DB files for our main DB. I know that's a lot but it's been spread across so many LUNs for so long and it's just grown and grown as it's filled up the LUNs. Now we finally got a couple really large LUNs and we're trying to consolidate the files. And now that we've moved most of them to a single LUN, we want to stop the autogrowth on most of them so they don't grow anymore before we empty them and then drop them. Because what's the point really in putting them all on a single LUN if you're not going to get rid of some of them. So we definitely don't want them to grow anymore before we can empty them.

The task at hand was to set all of these files (most of them really) to not autogrow. The choices were do it in the GUI, or write a T-SQL cursor to go through all the files and create an alter database command in dynamic SQL for each one. Neither of those is very attractive considering that there are so many files. Of the 2 of them though, the cursor is the more attractive solution. Then I thought of powershell. I hadn't specifically done anything like this in powershell before, but I figured I'd be able to get some love. I'm not going to teach you powershell here, or explain the script too much. I'm just gonna give you the code and if you have any questions you're free to write, but it's pretty straightforward. I'll be making a video on this tonight for those of you who like to see things in action.

PS SQLSERVER:\SQL\MYSQLBox\DEFAULT\Databases\MyDB\filegroups\data\files> gci | where{$_.Name -ne "MyDB_Data"} | %{$_.set_GrowthType("None"); Alter();}

OK, so a quick glance at the important parts.

1. Make sure you're in the right node. Notice I'm in data\files.
2. Here I'm filtering out the one file I don't want to be effected. So the main data file I want to still be able to grow. I'll talk about that more in a min.
3. Don't forget to use the Alter() at the end. Usually whenever an object has an alter method it wants you to use it. If you don't it'll change it in cache in your powershell session but you won't see the change persisted to the DB. So you're not really making the change to the object in SQL, just in your powershell pipeline. You can tell if something has an alter method by doing this at the cmdline 'gci | gm'. If there's an alter method there, then use it.

OK, you don't have to filter. If you find it too much trouble or if your files are named in such a way that it would be too hard to snipe a single file for some reason, then you can change them all and then just go back to the GUI to change the last one back to what it was.

So there you go. We have a nice 1-line script to alter as many files as you need. And you can clearly tack as many file changes on there as you need. And the best part about it is that the code doesn't really change if you need to do another file operation. All you do is change the method call. Ahhh, the power of powershell.

Today's blog is actually an email chain between me and a user. It's only a single question and reply, but I think it's good info.

Q:
Hey, would you agree that a monthly load process is better served as an SSIS – even if you have to push/pull from text files for now – than as a series of SPs or DLLs?

A:
if you're staying on the same box for the load then SPs can be an attractive offer because they're very fast and the memory stays in sql and can be managed quite well... if you move that process to ssis, and ssis is on the same box, then you have to allocate memory away from sql to run the pkg and house the data while in the buffer...



if ssis is on another box, but the data is still being moved to different dbs on the same box... so if the data is being moved from server1.db1 to server1.db2 and ssis is on server2, then you don't have to fight sql for memory, but now you incur the network cost of moving the data from the box, and then back to it...



if you're moving between boxes, then y, ssis is a better choice because in SPs you have to manage linked servers or openrowset to make that happen and that's not cricket...



however, what makes ssis attractive in the single box scenario is that it handles errors easier and alerting is much richer and easier to come by... you can also more easily fix problems in the data itself and it's easier to extend... so if your requirements change and you need to switch it to another box, or if you need to send a copy somewhere else, etc then that's much easier in ssis... ssis also gives you parallelism that you cant do in sps... you can load several tables at once in ssis where they have to be serialized in sps...



a good compromise in the single box scenario is to keep things moving like they are if possible, and where not stupid... so if they've already got an sp that inserts data into the 2nd db, then just call that sp from ssis... this way you get the insert and select process in sql where it belongs, and the workflow and error handling of ssis, and everything else that goes with it... if a single sp inserts several tables serially though, i'd create several sps and call them individually in ssis tasks... even if you have to keep them seialized (like for PK/FK issues)... because now you can even add things like checkpoints and individual error flows to your pkg steps and have a richer experience...



these things are meant to allow your process to grow and change much easier than is possible with an sp... so if one of the DBs gets too big and has to move to another box, it's a simple matter in ssis... but in an sp, that means you have to create and manage a linked server, which is just one more thing and they come with their own problems...



as for the DLLs... these processes NEVER belong in a DLL... not only do you have to steal memory from sql, but it's not optimized for data flow tasks... it's just a plain DB connection... you can have parallelism though by calling the windows threading model, but they have to manage those threads manually and mistakes can be made... and that takes extra memory, etc... not only that, but the code isn't available for admins to fix problems, and making changes to the compiled code can be dangerous, right... so no, that's not a good idea...



does this answer... i realize you prob wanted more of a yes, do this, but it's not that simple...

Ok, not ‘no more’, but you guys should seriously limit your usage of select * for everything.  I realize that it’s easier to support when you change the data requirement, but it pushes a lot more data than you need and that could really impact network and server performance. 

Let’s say you’re running a web app and you need 3 cols on your page.  And you pull all 37 because it’s just easier to type a * than each column.  That’s fine from your end, but you could seriously impact the server and the network because if one of those cols is really wide, say varchar(200) (or even 400, right…) then you’re taking up that much extra bandwidth and server memory.  Sure it probably won’t effect your session that much now, but when you’ve got 500 people on your site at the same time you’ll start to feel the pain then for sure.

So just code for performance and stop being so lazy about having to type a few chars.  And if you really don’t like typing that much then get yourself a nice code completer like the one from Red-Gate and you won’t have to type nearly as much.  But I’m getting sick of laziness being an excuse for bad coding.

There are some exceptions though.  It is ok to use select * for some things but you have to choose those individually and judiciously.  Let’s say that you’ve got an SP that pulls 65 cols from a complicated set of logic that you don’t wanna have to re-create or maintain separately.  And let’s also say that you only need to query it every now and then, or maybe just 2-3 times a day.  In a case like that, it’s probably ok to go ahead and use the SP even though you’re only using a handful of the resultset.  However, in that same scenario, if you were using that data several times a minute, or even a second, then you’re really better off from a performance perspective to go ahead and create your own SP that returns less data.

Another excuse that gets used is people often tell me that they used select * to make it easier to make changes to the app.  And that is logical to a degree.  But people protect themselves all the time from issues that aren’t issues.  For instance, I had this just a while back where someone gave me that excuse and when I probed, the app had been up for 2yrs and had only ever had one minor change.  So what are you protecting yourself from then?  If the app is fairly static, then grow up and do the right thing.

OK, that’s actually stepping on the toes of another post so I’ll stop here.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA