My photo
Auckland, New Zealand
Smurf sized geeky person with a penchant for IT, gaming, music and books. Half of industrial duo 'the craze jones'. Loves data, learning new things, teaching new things and being enthusiastic.

Thursday, 30 July 2009

Yay - It works! Rebuild/Reorganize all indexes in one go - woo hoo

/*Version 1 of my script to fix all fragged indexes on a database */

/*Must be logged in as [sa] for this to work, if not, you will get permissions errors*/

/*You should be able to copy and paste this script in its entirity to your SQL 2005 or higher query window and run it without altering anything other than the USE .*/

/*Perform a 'USE ' to select the database in which to run the script.*/

USE AdventureWorks
GO

-- Make sure temp table doesn't exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_fraglist]') AND type in (N'U'))
DROP TABLE [dbo].[tmp_fraglist]
GO

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename NVARCHAR (255)
DECLARE @indexname NVARCHAR (255)
DECLARE @indextype NVARCHAR (255)
DECLARE @execstr NVARCHAR (255)
DECLARE @pagecounts INT
DECLARE @frag DECIMAL
DECLARE @reorgFragSize DECIMAL
DECLARE @rebldFragSize DECIMAL
DECLARE @numrecords INT
DECLARE @rowcount INT
DECLARE @errorMessage NVARCHAR (max)

-- Set the fragmentation variables for reorganize and rebuild
SELECT @reorgFragSize = 5.0
SELECT @rebldFragSize = 30.0

-- Create the temp table to store the fragged indexes
CREATE TABLE tmp_fraglist (
RowID INT IDENTITY(1,1),
ObjectName NVARCHAR (255),
IndexName NVARCHAR (255),
IndexType NVARCHAR (128),
PageCounts INT,
AvgPageFragmentation DECIMAL
)
-- Get a snapshot of current defrag state of affairs before we begin
BEGIN
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'
AND avg_fragmentation_in_percent >= @reorgFragSize
ORDER BY TableName
END


-- Populate the temp table with table fragmentation info
INSERT INTO tmp_fraglist (ObjectName, IndexName, IndexType, AvgPageFragmentation, PageCounts)
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'

AND avg_fragmentation_in_percent >= @reorgFragSize
SET @numrecords = @@ROWCOUNT
SET @rowcount = 1

PRINT '@numrecords = ' + convert(nvarchar,@numrecords)
-----------------------------------------------
-- loop through the records in the temp table
-- and reorganize or rebuild where necessary
-----------------------------------------------
WHILE @rowcount <= @numrecords
BEGIN
SELECT @tablename = ObjectName,
@indexname = IndexName,
@indextype = IndexType,
@frag = AvgPageFragmentation,
@pagecounts = PageCounts
FROM tmp_fraglist
WHERE RowID = @rowcount

------------------------------------------------------
-- Reorganize if fragmentation is between 5 & 30%
-- Rebuild if fragmentation is greater than 30%
------------------------------------------------------
BEGIN TRY
IF @frag >= @reorgFragSize AND @frag <= @rebldFragSize
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REORGANIZE'
PRINT @execstr
EXEC (@execstr)
PRINT 'REORGANIZED index ' + @indexname
END
ELSE IF @frag > @rebldFragSize
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE=ON)'
PRINT @execstr
EXEC (@execstr)
PRINT 'REBUILT index ' + @indexname
END
END TRY
BEGIN CATCH
SET @errorMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)
PRINT 'Error number: ' + convert(nvarchar,@@ERROR)+ ' : ' + @errorMessage
END CATCH
SET @rowcount = @rowcount + 1
END

-------------------------------------------------------
-- snapshot of the defrag status after rebuilding.
-- gets all indexes regardless of AvgPageFragmentation
-- as they may be below 5% after rebuild/reorganize
-------------------------------------------------------
BEGIN
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'
ORDER BY TableName
END

-- Delete the temporary table
DROP TABLE tmp_fraglist
GO

Unsolicited calls

Recently (the past year or so), I've been getting majorly inundated with calls. My number is ex-directory (unlisted) so I should not be getting any calls from people unless I have given them my number, however, I'm getting about 42 missed calls a week; an evening doesn't go by without at least 2 intrusive calls from someone wanting 5 minutes (30-40 minutes) of your time. These people must also be ringing constantly during the day to rack up that many missed calls.

It reached the point where about 6 months ago I stopped answering the landline at home and in the past fortnight have now unplugged the phone completely to stop the annoying and very frequent ringing. I am also considering changing my number.

Today though someone very helpfully told me that there was a website you could go to and request having your number removed from a list, they didn't know the link unfortunately but after 10 minutes of googling I have the answer, yay! And I'm sharing it with everyone else so that you too can be free of the blight of incessently ringing phones.

Anyone else wanting to get their names removed from a cold call list, check out these links:




Not sure how useful that last link will be as I still need to fully investigate it, but the first two, those forms were completed about 30 seconds after finding the link.

If anyone finds any other useful methods of stopping the cold calling nuisance, please leave a comment. :)

May 2013: Update: The links above are no longer valid.  You now need to go to: http://www.marketing.org.nz/Category;jsessionid=FC603210CE1DC92FFA9AD96F8B3B9C1A?Action=View&Category_id=116


You can also revoke authorised access to your personal details through your car rego:https://transact.nzta.govt.nz/transactions/PersonalInfoAccess/entry.aspx

Monday, 27 July 2009

It's not that difficult!

<rant>
I am getting very hacked off with the spelling and grammar in this country. I'm convinced they just aren't bothering to teach them English at school any more.

So, in our first lesson on how not to annoy me with your appalling language skills, this is a shout out to all those of you with an apostrophe aversion:

You're = You are - "I can see you're not that good at spelling old chap."
Your = Possessive form of 'you', i.e. it belongs to 'you' - "Is this your appallingly written advert?"

Their = Possessive pronoun, means belonging to them - "Is this their small furry animal?"
They're = They are - it is a contraction of two words - "They're over there.".
There = Over there - "The house was over there.". Use 'there' when referring to a place.

It's = It is or It has- "It's great sitting on the beach in the sun.", "It's been great seeing you."
Its = Possessive pronoun, something belongs to 'it' - "The dog wagged its tail". If you can replace its with her or him, then use 'its', if you can't replace the word with her or him and you can instead replace it with 'it is' or 'it has', then use 'it's'. And just so we're very, very clear, THERE IS NO SUCH WORD AS ITS'.

It's not hard, stop being so damn lazy and spell words correctly. If you're going to put up a poster or advertisment, at least make sure the spelling is correct. Lazy, lazy, lazy!

</rant>

This rant comes to you courtesy of the moron who created an event on Facebook entitled, "Think your smart?", followed by "book a table to ensure your involved." AAAARRRRGGGGGGHHHHHHHH! It hurts my eyes!

And as I'm writing this in full rant mode, I am (I'm) fully aware there will probably be grammar and spelling mistakes contained within this diatribe.... point them out to me at your peril! :-p

Wednesday, 22 July 2009

SQL Performance - Index Defragging

I'm in the process of putting together a couple of short talks for the Auckland SQL Server user group meeting in August.

The first presentation is already available to download on the right hand side of the blog page; as soon as the second part is finished I'll post a link to that as well. Code will be included with the presentations so that you can try this stuff out for yourself.

Part I - Defragging Indexes For Beginners
Indexes over time become fragmented and as a result don’t work quite as well as they should. This short talk will show you how to fragment and then fix an index.

Part II - Effective Indexes For Beginners
Use a DMV to identify which indexes are effective and which can be disabled/deleted. This short talk will show you how and why you should be checking the effectiveness of your indexes.




Another item for the wish list


Nice... Nixie Tube Clocks... droooool... this is now top of the wish list, closely followed by a Steampunk Segway.



Tuesday, 21 July 2009

And today's winner of my favourite person or company is.... Microsoft


Microsoft were the primary sponsors of NZ Girl Geek Dinners for 2008/2009 and they have emailed today to confirm a continuation of this sponsorship until July 2010.

A huge thanks to all the guys at Microsoft NZ for all your help since NZ-GGD started in November 2007. You rock! :)



Friday, 17 July 2009

Oh for goodness sake!

Section 92a rears its ugly head again.

http://www.netguide.co.nz/200907151077/92a-changes-keep-net-termination.php


A selection of rather groovy gadgets

I guess not everyone has a pressing desire to own lots of gadgets, I am, unfortunately, not one of these people. If I were then my credit card bill would be a lot lower than it is. Anyway, during a rather productive lunch break of browsing for new gadgets, I've come across one or two of interest and thought I'd share.

How about a bluetooth retro handset? Could just see myself walking down the street talking on one of these, most amusing indeedy: http://www.thinkgeek.com/gadgets/cellphone/8928/. There's a good review of it here: http://www.thetechzone.com/ttz/index.php/yubz-talk-bluetooth-headset-review/




Gotta love this one, an ant farm but you need to provide your own ants. Not sure the apache server will fit in the slimline case they send out. http://www.geeks.com/details.asp?invtid=ANTW2R&cat=GDT.


Now this one could be useful. I've got quite a bit of vinyl, everything from Pinky & Perky through to Anthrax - I throw nothing away... it's another bad habit along with spending all the money I don't have on gadgets. http://www.geeks.com/details.asp?invtid=E-E038-PB




This next one I envisage using at Goat Island, perhaps in the summer though, bit nippy in the water at the moment. A camera that's built into your diving mask seems like a great idea: http://www.geeks.com/details.asp?invtid=VS-310&cat=GDT




And finally, the ultimate geek must have, well, for this geek anyway - your very own Ultimate Arcade II Cabinet MAME - YES! Though to be fair, the original Tetris arcade machine in my friend's lounge is better, just a little harder to come by. http://www.amazon.com/Ultimate-Cabinet-Multiple-Machine-Emulator/dp/B000LSGZI2.

The tetris machine above is not the one in my friend's lounge, but it is very, very similar.

Should anyone feel a wad of money burning a hole in their pocket, feel free to buy me any or all of the above items. :)

Wednesday, 15 July 2009

The wee beasty is out of its box.


Shiny, new, lovely - happy sigh!



6 RAM slots could be kinda useful


It's a processor that means business when the fan is bigger than your mouse, and hand...


Blue lights are supposedly very calming. This should be perfect for staying cool, calm and collected when battling hoards of zombies.


I guess it might be a good idea to stick an operating system on it now, will need to turn it off and stop looking at the calming blue lights first though.

Woo hooo.....

The new PC has arrived - so shiny, so pretty, and such a HUGE processor fan. I've been sitting with it on my knee for 5 minutes, should probably put it back in its box. Wonder if anyone would notice if I snuck off home now to start setting it up.

Am currently very impressed with Spannerbox as all the spare parts have also been sent with the PC. Another motherboard box full of bits to add to my collection at home.

And as if that wasn't enough great news for one day, I've also received approval to go on the SQL BI course being run by www.wardyit.com. Things come in threes apparently, so with any luck there's a cheque for a squillion dollars sitting in the letter box when I get home. A girl can dream.

:)


Tuesday, 14 July 2009

The Tigger effect


Had an email from the PC supplier.
My spiffy new PC should arrive tomorrow.
Yay!
I'm happier than a Tigger on E.