Hey all you subscribers to my fancy spancy tech blog, do me a huge favor and subscribe to my feedburner link.  I finally decided to break down and get a feedburner connector.

http://feeds.feedburner.com/LooselyCoupledHumanCodeFactory

One of the notable things I heard at work today which I thought was just hilarious, is that Microsoft still spends significant time and effort publishing marketing crap with FLASH!!!

I’m all ready to jump on the Silverlight band wagon, hell, I already have.  But Flash, why are they still using Flash?  The stuff I’ve seen they could easily do with Silverlight in about the same time with an editing tool like Blend.  In addition to that they could help themselves by getting this pushed across their site.

Since I was snooping around Todd Anglin’s Site, he has a good entry about it too, “Memo to Microsoft Marketing:  Silverlight is out of Beta“.  I almost can’t believe it.

…however…

…all of this silliness from Microsoft brings me to a thought that has popped into my head on a semi-frequent basis lately…

Microsoft doesn’t just produce bloated software anymore, they have become excessively bureaucratically bloated also.  Not in a normal corporate way like they have in the past but in a, “bad for the company it is gonna start sinking the stock price if they don’t become performant”, type of way.  Come on Microsoft, get lean, but don’t be stupid about it and just whack half your employees or anything.  Just wise up about hiring, stop hiring so much, and for the love of sanity let those that aren’t good start leaving or moving on.  I understand there has to be some serious turnover in some departments.

The MVC is top notch, the extensions are great, the CTPs rock, Visual Studio 2008 is great too.  So now the operating system team, the marketing groups, and others need to start opening up and performing in a similar way instead of publishing crap in Flash like Hey Genius (even though it is kinda neat), and…  come on… Defy All Challenges!!!!  Maybe by the time readers click on those links it’ll be re-posted in Flash?

Anyway, Microsoft, keep up the good work and the arrays of confusion!  ARGH!  I look forward to continued efforts and readings on ASP.NET, MVC, and all the good stuff that you produce.  As for the silly out of sync marketing efforts I’ll continue to enjoy those as canon fodder for every geek group in the world.

Thanks Microsoft. 

This entry is going to be a collected list of practices for index, fragmentation, statistics, and general VLDB table and view maintenance.

First off, a few obvious index practices.

1. If a non-clustered index is not used, toss it.  When a table has a record inserted, the duplicate of that is inserted into the non-clustered index.  Each time that record is deleted the same must occur in the index and the same goes for updates.  Most horrendously, if a clustered index is created then the non-clustered index has to be rebuilt.  Major IO hog, if it isn’t used, no point in having it.  For information on how to check if a non-clustered index is being used, check out this blog entry on the SQL Server Storage Engine.  At some point I might shrink that into a short entry and post it as a tip o’ the day.  I’ve had to reference it a number of times and would rather just the quick bullet points.- When checking VLDBs, and especially VVVLDBs, for consistency in sizes exceeding hundreds of gigabytes or even multiple terabytes there are a few options that one can follow:
   1. Use the WITH PHYSICAL_ONLY option for CHECKDB.  It will run similar to DBCC CHECKALLOC and read and audit every allocated page in the database.  It will skip logical errors, inter-page checks, and some other things like the DBCC CHECKCATALOG.- Break up the checks by partition.  The DBCC CHECKFILEGROUP run on a read-only filegroup every week or two works great.  On a read-write filegroup DBCC CHECKFILEGROUP should be run every day or every few days.- Break up the checks into smaller groups by doing DBCC CHECKCATALOG or DBCCCHECKTABLE.  Break them into different groups and then segment the job execution of these checks to different maintenance windows.- Logical fragmentation only affects read-ahead performance, only a rebuild/defrag of indexes will help.- Low page density affects UI throughput and memory usage, which could be a sign of page-splits.  Check this frequently.- If defrag is done instead of rebuild, make sure to update stats.- Be cautious when doing large index maintenance jobs if log shipping or DBM is used, index rebuilds are always full-logged when DBM is present.  (DBM = Database Mirroring)

A few items mentioned by Peter Sampson – Technical Director at Centerstance;  Blogs: Moving to Mac and SQL Janitor, over a recent pint o’ Beer at Bailey’s Taproom.

1. Regardless of OLTP or OLAP almost any table in SQL Server should…
   1. always have a clustered index on the table.- have a clustered index design that either very rarely or never has the clustered index updated.  SQL Server does a DELETE and an UPDATE if you update a column with a clustered index!- use non-clustered indexes in conjunction with the clustered index since they are interleaved.- DBCC DBREINDEX rebuilds the table and indexes depending on the parameters passed.  This is best done on a regular schedule, often by setting a job to execute the task.- FILLFACTOR is a critical parameter for DBREINDEX, make sure it isn’t just set to 100 or some other erroneous value that will cause problems.  FILLFACTOR sets the free space to leave during the rebuild of the Index B-tree structures and is a reverse representation.  100 == no free space, 90 = 10% free, and so on.  For read-only tables do a FILLFACTOR = 95 or perhaps 90.  For other stuff FILLFACTOR = 85 is a good starting point since it gives you some head room for INSERTs and UPDATEs after you run DBREINDEX.- DBCC UPDATE STATS collects new statistics but does not remove fragmentation.  It takes less time than DBREINDEX and can be useful if you’ve done a lot of transactions and don’t quite have time for DBREINDEX.- Peter:  “Auto-update stats I no longer trust to provide accurate statistics over an extended period of time.  Got burned up with this, leave auto-update stats enabled but supplement with regular DBREINDEX or UPDATE STATS as needed.“

Someone asked me during a discussion, and it is often forgotten what VLDB stands for.  To alleviate confusion, VLDB stands for very large database.  When it has extra Vs it means it is REALLY frikkin huge!

A last few links for reference material:

Best practice when optimizing indexes on SQL Server 2005

Best SQL Server indexing strategies

TOP 10 SQL Server Indexing Tips to Improve Performance

Hope that’s useful.

Technorati Tags: FILLFACTOR,DBCC,DBREINDEX,INSERT,UPDATE,DELETE,Index,Defrag,Index Rebuild,DBM,Database Mirroring,CHECKALLOC,CHECKCATALOG,CHECKFILEGROUP,Index Best Practices,Index Strategy,SQL Server,SQL Server 2005,VLDB,VVLDB

del.icio.us Tags: FILLFACTOR,DBCC,DBREINDEX,INSERT,UPDATE,DELETE,Index,Defrag,Index Rebuild,DBM,Database Mirroring,CHECKALLOC,CHECKCATALOG,CHECKFILEGROUP,Index Best Practices,Index Strategy,SQL Server,SQL Server 2005,VLDB,VVLDB

It seems that everywhere one looks in the ETL/OLAP/BI Industry Space there are no real canned applications.  When I say canned application I’m talking about an application that from the ground up is all inclusive, managing the database, the ETL, the warehouse and any possible staging, and finally the Cube itself and reporting that sits on top of it.  Let me break that down real quick:

• Database- Staging- Warehouse- Cube- Reporting

…and of course, one can’t forget…

• ETL – Extraction, Transform, and Loading

I have as of yet seen something, able to be delivered with a single MSI, or even something that is easily installed in one or two steps.  Of course, I saw all this, and would love for someone to comment on a product that is easily installed that utilizes all of these things in one install package.  Especially if it uses Microsoft’s Technology Stack.

In addition to not having seen this yet, I keep reading about the prospective gain in market share of applications that are supposedly just like this.  To that I ask, “How does one scale a canned application”.  The amount of logic and decision branching the application would have to make during the course of the application scaling is practically impossible to do well.  Sure, one can put in some basic checks, but beyond that I would suspect a kind of diminishing return.  The simple fact is, that business intelligence still needs a significant presence of skilled ETL, Report, and Database Developers.

I’m not really complaining, just sort of stating the obvious.  As I dig more into this industry, I can really see how difficult it is to find people that are skilled in the full technology stack or even theory of the entire technology ideology behind business intelligence.

So with the end of the year coming up, I think what I’ll do is just set a new years resolution to get something built with a business intelligence engine as the basis.  Personally or work related doesn’t matter, I just need to create something in this space that brings together these points.

Vista really is a lacking Microsoft Product.  I’m not sure what the hell happened in comparison to Windows 2000 and XP.  Both of those launches went rough, as usual, but not noticeably so.  Vista however is just a dog within the industry these days.  I’m sure it is even ceding a large number of customers to Apple and I couldn’t blame them.

Hopefully SP1 will get this dog running right.

Some rather entertaining quotes top list garnerings published on The Insider: Tech pundits take dim view of Vista.

Michael Gartenburg’s quote I found entertaining.

“I’m starting to think of Vista a little like sushi. I don’t love sushi. If it’s around, if it’s prepared well, if it’s fresh, I’ll eat it. I rarely go out of my way to eat it, but if there’s an alternative, I’ll usually eat that. If you ask me if I like sushi, I’ll usually say it’s not bad.”

Technorati Tags: Vista,Windows Vista

del.icio.us Tags: Vista,Windows Vista