July 06, 2006

Data Fragmentation in SQL Server 2000

Although SQL Server is a database management system, it will not do everything for you automatically. In many cases, even the most basic administration tasks are neglected by the average user & can result in a decrease in performance. For the purposes of this article we will take a beginners look into maintaining indexes on SQL Server 2000.

To start with, you have the ability to produce a fragmentation report for the data and indexes of the specified table with in SQL Server. Run DBCC SHOWCONTIG using SQL Query Analyser. This command returns a report for either the entire database or for specified tables. It is especially useful to run this if your database has suffered a gradual decrease in performance over periods of time. A gradual decrease will indicate index fragmentation. By scanning through this report you will be able to determine the primary tables that have been affected. Two important areas of this report to look at are:

1. Extents scanned & Extent Switches – in an ideal world you should have one less switch than extents

2. Extent scan fragmentation levels

Once you have diagnosed to problem tables/indexes, you can run DBCC DBREINDEX. This command rebuilds one or more indexes for a single table. Generally, your best bet is to rebuild all indexes on a given table or rebuild the primary index (rebuilding the primary one will automatically rebuild all others). This command can only be run on a table by table basis – i.e. it can not be run on the entire database. In addition to this, the DBCC DBREINDEX command should be run as an offline operation i.e. single user mode. The following example rebuilds all indexes on the PRODUCTS table using the default fillfactor they were originally created with:

DBCC DBREINDEX (PRODUCTS, '', 0)

For more in-depth information about these DBCC commands, including parameter options, please refer to resources such as SQL Books Online.

Oliver Cox

June 27, 2006

Microsoft Vista and the 2007 Microsoft Office System

I attended a Microsoft Partner Community Day in London on 20th June 2006.  This conference focussed on Vista and the 2007 Microsoft Office System, and how ISVs can take advantage of the new functionality and UI (User Interface).

It is worth noting before I continue that Microsoft were heavily pushing the naming of Office to '2007 Microsoft Office System', I think that's a bit of a mouthful, so I will just refer to it as Office from here on in, seeing as we all know that I am referring to the new release.

Anyway, overall it was a very enjoyable day and a lot of information was provided, the only negative point were the chairs, now I know that is a little picky but they were very thin and hard and everyone was packed in, obviously this is not the fault of Microsoft but the hotel the conference was run in (I won't do a name and shame here of the hotel, but the conference attendees will understand what I mean!).

Microsoft came across very enthusiastically and positive about the Exchange, Vista and Office ('EVO', as they call it) launch, and it is right that they should be.  They say it is:

"the most significant release in a decade!"

and I believe them.  The leap forward just in the UI alone is huge, they have obviously spent a great deal of time investing in the "User Experience" (Ux).

The software looks and runs great, even though it is still BETA.  In fact they said that Microsoft had rolled these products out internally already as they feel they are ready. I'd say that the software looks good, but I wouldn't run it on my own machines in place of Win XP and Office 2003.  I'm going to wait until November (the planned release of EVO for businesses) before requesting an upgrade on my office PC.

I'm going to invest in a new PC for home to run Vista, even though the hardware requirements have been set to a minimum 1Gb RAM, it'll be a good excuse to improve the speed and graphics capabilities!  I would also encourage any user to be running at least 2Gb RAM for Vista to run smoothly.

There are many features that stand out in Vista and Office, from the glass effect windows, to Flip3D, searching capabilities, ribbons, gadgets and the improved security.  I won't go into these features in any great detail here as I am sure that most people are already aware of them (you can find out on the Microsoft website if you don't), but I will say that I think they are great.  Vista allows people to work smarter, taking on some of the chores for itself, such as changing the default printer when the machine moves networks and saving the state just before a battery on a laptop dies, simple features but very handy!

With the new Office system having 48 applications in total, including 15 desktop applications and 6 server applications, it makes sense that Microsoft have introduced 'ribbons' and have removed the standard menu system that we are all used to seeing.  I have to admit that when I first saw the ribbons I wasn't too convinced, although they looked nice it didn't seem obvious where all the options were.  However, the more I see them and work with them the better they become.  Microsoft says that it takes the average user between 2 days and 2 weeks to adjust, and that seems accurate to me.  The context sensitivity of them is fantastic, if you are working in a table in Word then the default ribbon is specific to all the options available for tables.  I can see how it will enable people to use more features in their applications than ever before, as users will be able to find options specific to their task a lot easier and faster.  I think Microsoft will see a reduction in users requesting features that are already present!  And companies should see an increase in productivity.

Security was also a big focus for Microsoft, Vista has gone through a security lifestyle review, this is the first operating system to have gone through this, so I hope that we will find ourselves less likely to be 'attacked' by viruses and spyware etc.  IE v7 also includes anti-phishing software and the warnings associated with this make it very clear that users should be careful when IE comes upon a suspicious site, enabling them to report these sites to Microsoft to investigate.

This release is going to change the way we work, it may also help us address some of our workflow processes in our working lives, so that we spend more time achieving and less time running around looking for information and duplicating effort.  Therefore, I do believe that Microsoft are right when they talk about this being a revolution in the way we work (I'll point out here a document that Microsoft have published called "The New World of Work", definitely worth a read, it can be found here

However, a push to improve the wireless network connections in our towns and cities is required to help with these changes, and companies should embrace the concept of employees 'being mobile' working from places other than our desks, with the new technology this will become easier.

So, to conclude, Microsoft have come along way from Windows 95 and Office 97, Vista looks great and I believe it will help everyone in their work and home lives.  Office, once you are used to the ribbons, will stop the frantic searching for options and settings, I hope to hear no more frustrating mouse clicks and complaining in future ;0)

NB You can see some presentations on the Office features, including Excel Advances, Servers Overview and Developing in Word here

Kirsty Lowe