Twitter Updates

    follow me on Twitter

    Friday, November 21, 2008

    SQL Server Day - Sessions Worldle

    We finalized the agenda for SQL Server Day and published the session abstracts earlier this week. I created a worlde based on the session abstracts. Seems like we are gonna talk a lot about SQL Server, 2008, features and Data!!!

    ScreenShot032

    If you haven't registered yet, make sure you do so and join the 250+ other registered SQL Server Enthousiasts.

    Wednesday, November 19, 2008

    DBCC SHRINKFILE can uncompress a compressed table in SQL Server 2008

    While preping a demo on data compression for my session for the upcoming SQL Server Day I ran into a funny behaviour of DBCC SHRINKFILE.

    In the script below I create a table, fill it with data. The table takes 157 pages.

    image

    After compressing it with page compression the table only takes 15 pages. Which is cool.

    image

    When I run a DBCC SHRINKFILE the table explotes to a to 116 pages.

    image

    I was really confused and tought I hit a bug. But I think I found an explanation. Basically DBCC SHRINKFILE will move used pages at the end of the file to a free spot somewhere else in the data file. The funny thing is that SQL Server is not just moving the pages, but will actually uncompress the pages.

    Which leads me to the following conlusion. Although you use ALTER TABLE, INDEX, PARTITION, ... DDL statements to specify compression, compression is not a TABLE, INDEX or PARTITION level property but a page level property.

    Or did I hit a bug?

    --Use SQL Server Internals Viewer

    USE testCompression

    GO

    ALTER DATABASE testCompression

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    USE MASTER

    DROP DATABASE testCompression

    GO

    CREATE DATABASE testCompression

    GO

    USE testCompression

    CREATE TABLE PageCompressed

    (val BIGINT

    ,st VARCHAR(100)

    )

    GO

    INSERT INTO Pagecompressed

    (val, st) SELECT RAND()*1, REPLICATE('A', 100)

    GO 10000

    SELECT page_count, * FROM sys.dm_db_index_physical_stats (

    DB_ID (),

    OBJECT_ID ('dbo.Pagecompressed'),

    NULL, NULL, 'SAMPLED');

    GO

    -- Turn on compression

    ALTER TABLE Pagecompressed REBUILD

    WITH (DATA_COMPRESSION = PAGE);

    GO

    SELECT page_count, * FROM sys.dm_db_index_physical_stats (

    DB_ID (),

    OBJECT_ID ('dbo.Pagecompressed'),

    NULL, NULL, 'SAMPLED');

    GO

    DBCC SHRINKFILE(testCompression)

    go

    SELECT page_count, * FROM sys.dm_db_index_physical_stats (

    DB_ID (),

    OBJECT_ID ('dbo.Pagecompressed'),

    NULL, NULL, 'SAMPLED');

    GO

    Monday, November 17, 2008

    BI Dutch - new BI Community in Holland

    Johan van der Kooij, Dutch blogger on (MS) Business Intelligence and a long time biTunes fan :-) started a new BI Community in Holland. The blog on bidutch.nl contains quite a few interesting posts in Dutch. You can join the community via the Linked Group on http://www.linkedin.com/e/gis/46641.

    Friday, November 14, 2008

    Data Mining in the Cloud

    I recently ran into this online data mining tool on http://www.sqlserverdatamining.com/cloud/
    (click on Try it out in your browser)

    The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. The goal is to provide services that allow you to build rich, predictive applications without worrying about server infrastructure, and showcase these services with cool applications that give you a glimpse of what’s possible. The guys released a first version, which is available for free online.

    If you wanna get started with data mining without going through the installation hassle, this is a really great online tool. The cool thing is that you can even upload your own set of data in a CSV file.

    Talking about data mining. On December the 4th, Marc and Dennis from IKAN Consulting will give a session on data mining on SQL Server Day. If you haven’t registered for SQL Server Day, make sure to do so on www.sqlserverday.be.