SQL Check index fragmentation

Run this script to check if you have to much fragmentation on your indexes

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent, indexstats.fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 10  and indexstats.fragment_count > 50
ORDER BY indexstats.avg_fragmentation_in_percent DESC

SQL Index optimizing job

Use the script below in a weekly maintenace plan in you database, defrag all tables with more than 30% fragmentation and a fragmention count > 50
{
IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL 
        DROP TABLE tempdb..#work_to_do

BEGIN TRY
--BEGIN TRAN

use [your Database]

-- Ensure a USE  statement has been executed first.

    SET NOCOUNT ON;

    DECLARE @objectid INT;
    DECLARE @indexid INT;
    DECLARE @partitioncount BIGINT;
    DECLARE @schemaname NVARCHAR(130);
    DECLARE @objectname NVARCHAR(130);
    DECLARE @indexname NVARCHAR(130);
    DECLARE @partitionnum BIGINT;
    DECLARE @partitions BIGINT;
    DECLARE @frag FLOAT;
    DECLARE @pagecount INT;
    DECLARE @command NVARCHAR(4000);

    DECLARE @page_count_minimum SMALLINT
    SET @page_count_minimum = 50

    DECLARE @fragmentation_minimum FLOAT
    SET @fragmentation_minimum = 30.0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

    SELECT  object_id AS objectid ,
            index_id AS indexid ,
            partition_number AS partitionnum ,
            avg_fragmentation_in_percent AS frag ,
            page_count AS page_count
    INTO    #work_to_do
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
                                           'LIMITED')
    WHERE   avg_fragmentation_in_percent > @fragmentation_minimum
            AND index_id > 0
            AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1
BEGIN
 PRINT 'partitions CURSOR DELETED' ;
    CLOSE partitions
    DEALLOCATE partitions
END
-- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR LOCAL
    FOR
        SELECT  *
        FROM    #work_to_do;

-- Open the cursor.
    OPEN partitions;

-- Loop through the partitions.
    WHILE ( 1 = 1 )
        BEGIN;
            FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

            IF @@FETCH_STATUS < 0
                BREAK;

            SELECT  @objectname = QUOTENAME(o.name) ,
                    @schemaname = QUOTENAME(s.name)
            FROM    sys.objects AS o
                    JOIN sys.schemas AS s ON s.schema_id = o.schema_id
            WHERE   o.object_id = @objectid;

            SELECT  @indexname = QUOTENAME(name)
            FROM    sys.indexes
            WHERE   object_id = @objectid
                    AND index_id = @indexid;

            SELECT  @partitioncount = COUNT(*)
            FROM    sys.partitions
            WHERE   object_id = @objectid
                    AND index_id = @indexid;

            SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                + @schemaname + N'.' + @objectname + N' REBUILD';

            IF @partitioncount > 1
                SET @command = @command + N' PARTITION='
                    + CAST(@partitionnum AS NVARCHAR(10));

            EXEC (@command);
            --print (@command); //uncomment for testing

            PRINT N'Rebuilding index ' + @indexname + ' on table '
                + @objectname;
            PRINT N'  Fragmentation: ' + CAST(@frag AS VARCHAR(15));
            PRINT N'  Page Count:    ' + CAST(@pagecount AS VARCHAR(15));
            PRINT N' ';
        END;

-- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

-- Drop the temporary table.
    DROP TABLE #work_to_do;
--COMMIT TRAN

END TRY
BEGIN CATCH
--ROLLBACK TRAN
    PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH
}

New owner at Sampdoria

New Sampdoria President Massimo Ferrero has promised fans marvellous victories and spectacular football following his takeover of the club.

The film-producer has bought the Luigi Ferraris side from pervious President Edoardo Garrone and, at a Press conference to announce the handover of control, the Blucerchiati fan paid tribute to his predecessor.

"I'm very honoured to be wearing this shirt," Ferrero, sporting a Samp jersey, told reporters.

"All I can say is thank you to Edoardo Garrone and his family, who have always had Sampdoria in their interests.

"I've produced 60 films and filmed 150. I've acquired Sampdoria because after Sampdoria there is nothing. I've won every prize in cinema there is to be won.

"I'm a Sampdoria fan and it's been two-and-a-half years that I've followed this dream. Garrone is very nice, a person of great humanity and at our first meeting he was very excited.

"Garrone is more important than Obama, he had a protocol to fulfil and it was fulfilled!

"Replicating Mantovani and Garrone is impossible, they are irreplaceable. They are people who have made Italy. I will be happy if I manage to continue the journey."

The new President then discussed what he hopes to bring to the club and spoke glowingly about Samp Coach Sinisa Mihajlovic.

"I can guarantee Sampdoria some marvellous victories and all I want to do is win.

"I still need to meet all the staff. Mihajlovic is the best Coach, the number one, I am one of his fans. I have two models - Garrone and Mihajlovic.

"On the transfer market, I hope the fans are not expecting a bang but they can expect a surprise.

"I want to bring spectacular football and I will look to focus on two things – the business and the spectacle, because the two things must be parallel."

Mihajlovic new manager

Mihajlovic appointed  as new Sampdoria manager

Delio Rossi Sacked

...potential new managers Sinisa Mihajlovic (Serbia) or Zeman (Roma)

Ciro Ferrara sacked

Sampdoria has decided to sack Ciro Ferrara and replace him with Delio Rossi. 

Also the coaches behind Ferrara was sacked today together with the sports director Pasquale Sensible. Pasquale has been replaced by Carlo Osti. 

The season so far has been fair, but with very unstable, good start and then a 7 defeats run then 3 games without loosing and then latest 2 poor defeats agains Udinese (H) and Catania (A), but still didn't see this on coming now, perhaps early in january as an alternative to some big name signings.

anyway good luck to Delio Rossi in 2013.

Sampdoria quick update season 2012

Players In:
Enzo Maresca (Malaga)

Simon Poulsen (free transfer prev AZ)

Marcelo Estigarribia (Le Mans)

Vahid Hambo  (Helsinki)

Tommaso Berni  (Sporting Braga)

Lorenzo De Silvestri  (Fiorentina)

Maxi Lopez (Milan but loaned out to Catania last season)

Paolo Castellini (Parma)

Daniele Padelli (loaned out to Udinese last season)

 

Players Out

Daniele Dessena (Cagliari)

Jonathan Biabiany (Parma)

Luca Rizzo  (Pisa)

Savvas Gentsoglou  (Livorno)

Paolo Sammarco (Spezia)

Vincenzo Fiorillo  (Livorno)

Pasquale Foggia (Lazio end of loan)

Federico Piovaccari  (Novara)

Graziano Pelle (Parma end of loan)

Bruno Fornaroli (Panathinaikos)

Bruno Martella (Perugia)

Vasco Regini (Empoli)

Vedran Celjak (Grosseto)

Simone Zaza (Ascoli)

Francesco Signori  (Modena)

Edoardo Blondett  (Portosummaga )

 

In but not playing:

Maccarone

Semioli

Angelo Palombo (the prev captain for several seasons is back after been loan out to Inter last season but is not playing - will post that story later on)

I anticipate that he will go to Milan before the current transfer window closes.
Andrea Poli (loaned out to inter last season ,want's to go, but clock is ticking)

 

Staff:
Ciro Ferrara is the new head coach

Rinaldo Sagramola will be the new general manager of Sampdoria.

 

Early results:

Got kicked out of coppa Italia by Serie B side Juve Stabia after extra time :(

Won 1-0 against Milan at San Siro ! :)

 

Which means that we have 2 points as we are starting the new season with -1, due to the big matchfixing scandal related to  Christian Bertani now ex-sampdoria player

Find other news here