• Increase font size
  • Default font size
  • Decrease font size
Home SQL

SQL

Ten days left

zondag, 20 juni 2010 11:21 administrator
E-mail Afdrukken

As some of you know, I was awarded Microsoft Most Valuable Professional (MVP) for SQL Server in July last year. Now it's time to see if I get my MVP renewed, or if I lose the award.
I honestly believe it's a good thing MVP status only lasts for one year at a time. Knowledge is fresh. Things that worked in the past may not work any longer due to evolvement, and there are smarter ways to do things now, than before.

Being an MVP is a responsibility. It does mean you have more than average knowledge of SQL Server and how to work out the best from the product. It doesn't mean you know everything about the product. Noone does.
Being an MVP means you have benefit of the community of your peers. I have learned more hardcore things only the last year, than I knew from the previous five years. And sometimes I get lucky to contribute to the MVP community myself.

That's how things work. Me being around knowledgeble people makes me learn, or understand, same things they do. The same thing apply to beginners on the forums. They most often look up to MVP's and learn one or two things from them.

The last year I haven't been around the forum as much as I want to. The reason is that I started (together with SQL server MVP Thomas Ivarsson) a PASS chapter in Sweden. It has taken a lot of time in the beginning and still does take some time to plan and invite interesting guest speakers for our meetings.

We are currently working to get a full day event in place in Sweden on the 10th of September. That's 3 months away but it feels there is not enough time! I guess every planner feels that way.
However, this is in my "line of duty", to continue to help and educate the SQL Server community,

Hopefully I get my MVP status for another year. It means my peers have faith in me and my ability to support the community.
I would like to thank a few people for the collaboration last year, which problably wouldn't have happened without my MVP status:

1) Itzik Ben-Gan for incredible insight and the email correspondence we shared the last year.
2) Joe Celko, for some fun competitions and coding help. I do love to see my [weighted] moving average in your upcoming SQL for smarties book.
3) Phil Factor, who have thought me a lot of driving and encouraging people to participate.  And of course, some Speed Phreakery!
4) Tony Davis, who gave my the opportunity to be a technical reviewer.

There are a lot of other people whom I have shared ideas with. You know who you are.

I know a few people who probably will receive the MVP award this quarter. I wish you good luck and god speed!

//Peter


Original
Lees verder
Geef commentaar (0)
Hits: 0
 

Application Shortcuts in Google Chrome - Awesome!

zaterdag, 19 juni 2010 01:56 administrator
E-mail Afdrukken
I use Google Chrome as my primary browser in Windows (it is blazingly fast and minimal). (I use Safari on my Mac’s) .. one awesome thing I have started to utilize is Application Shortcuts in Windows 7 and pinning them to the taskbar..


Which are apps and which are just browser app shortcuts? Pretty sweet. I set up Google Apps (Gmail), Reader and Unfuddle as my shortcuts and I love the one window no hassle that comes along with it, allows you to focus on the app instead of the browser/tabs. Pretty nice feature.


Original
Lees verder
Geef commentaar (0)
Hits: 0
 

New to Microsoft PowerPivot? Join me on June 30th for a free webinar.

vrijdag, 18 juni 2010 23:00 administrator
E-mail Afdrukken
New to PowerPivot? Have a POC to get done? Join myself and Attunity on June 30th to kickstart your journey!

Original
Lees verder
Geef commentaar (0)
Hits: 0
 

SQL Server 2008 Compression

donderdag, 17 juni 2010 16:16 administrator
E-mail Afdrukken
Hi!

Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.

However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around.
So I decided to give compression a go, since we use Enterprise Edition anyway.

This is the code I use to compress all tables with PAGE compression.


DECLARE @SQL VARCHAR(MAX)
 
DECLARE curTables CURSOR FOR
            SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                    + '.' + QUOTENAME(OBJECT_NAME(object_id))
                    + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
            FROM    sys.tables
 
OPEN    curTables
 
FETCH   NEXT
FROM    curTables
INTO    @SQL
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL IS NOT NULL
            RAISERROR(@SQL, 10, 1) WITH NOWAIT
 
        FETCH   NEXT
        FROM    curTables
        INTO    @SQL
    END
 
CLOSE       curTables
DEALLOCATE  curTables


Copy and paste the result to a new code window and execute the statements. One thing I noticed when doing this, is that the database grows with the same size as the table. If the database cannot grow this size, the operation fails.
For me, I first ended up with orphaned connection. Not good.

And this is the code I use to create the index compression statements


DECLARE @SQL VARCHAR(MAX)
 
DECLARE curIndexes CURSOR FOR
            SELECT      'ALTER INDEX ' + QUOTENAME(name)
                        + ' ON '
                        + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                        + '.'
                        + QUOTENAME(OBJECT_NAME(object_id))
                        + ' REBUILD PARTITION = ALL WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE)'
            FROM        sys.indexes
            WHERE       OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
                        AND OBJECTPROPERTY(object_id, 'IsTable') = 1
            ORDER BY    CASE type_desc
                            WHEN 'CLUSTERED' THEN 1
                            ELSE 2
                        END
 
OPEN    curIndexes
 
FETCH   NEXT
FROM    curIndexes
INTO    @SQL
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL IS NOT NULL
            RAISERROR(@SQL, 10, 1) WITH NOWAIT
 
        FETCH   NEXT
        FROM    curIndexes
        INTO    @SQL
    END
 
CLOSE       curIndexes
DEALLOCATE  curIndexes


When this was done, I noticed that the 90GB database now only was 17GB. And most important, complete database now could reside in memory!

After this I took care of the administrative tasks, backups. Here I copied the code from Management Studio because I didn't want to give too much time for this. The code looks like (notice the compression option).


BACKUP DATABASE [Yoda]
TO              DISK = N'D:FileshareBackupYoda.bak'
WITH            NOFORMAT,
                INIT,
                NAME = N'Yoda - Full Database Backup',
                SKIP,
                NOREWIND,
                NOUNLOAD,
                COMPRESSION,
                STATS = 10,
                CHECKSUM
GO
 
DECLARE @BackupSetID INT
 
SELECT  @BackupSetID = Position
FROM    msdb..backupset
WHERE   database_name = N'Yoda'
        AND backup_set_id =(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = N'Yoda')
 
IF @BackupSetID IS NULL
    RAISERROR(N'Verify failed. Backup information for database ''Yoda'' not found.', 16, 1)
 
RESTORE VERIFYONLY
FROM    DISK = N'D:FileshareBackupYoda.bak'
WITH    FILE = @BackupSetID,
        NOUNLOAD,
        NOREWIND
GO


After running the backup, the file size was even more reduced due to the zip-like compression algorithm used in SQL Server 2008. The file size? Only 9 GB.

//Peso

Original
Lees verder
Geef commentaar (0)
Hits: 0
 
Meer artikelen...
JPAGE_CURRENT_OF_TOTAL