Click here to Skip to main content
13,001,514 members (57,723 online)

Welcome to the Lounge

   

For discussing anything related to a software developer's life. Technical discussions are encouraged, but click here to ask your programming questions.

The Lounge is rated PG. If you're about to post something you wouldn't want your kid sister to read then don't post it. No flame wars, no abusive conduct, no programming questions and please don't post ads.
 
GeneralComputer Associates - You Friggin' Morons!!!! (Rant attached) Pin
Roger Wright30-Oct-10 21:48
memberRoger Wright30-Oct-10 21:48 
GeneralRe: Computer Associates - You Friggin' Morons!!!! (Rant attached) Pin
Mycroft Holmes31-Oct-10 1:48
mvpMycroft Holmes31-Oct-10 1:48 
GeneralRe: Computer Associates - You Friggin' Morons!!!! (Rant attached) Pin
Mustafa Ismail Mustafa31-Oct-10 3:10
memberMustafa Ismail Mustafa31-Oct-10 3:10 
GeneralSQL Server.........The story so far......... Pin
DaveAuld30-Oct-10 19:39
memberDaveAuld30-Oct-10 19:39 
GeneralRe: SQL Server.........The story so far......... Pin
Roger Wright30-Oct-10 21:53
memberRoger Wright30-Oct-10 21:53 
GeneralRe: SQL Server.........The story so far......... Pin
DaveAuld30-Oct-10 22:24
memberDaveAuld30-Oct-10 22:24 
GeneralRe: SQL Server.........The story so far......... Pin
Roger Wright30-Oct-10 22:40
memberRoger Wright30-Oct-10 22:40 
GeneralRe: SQL Server.........The story so far......... Pin
Lee Humphries31-Oct-10 20:40
memberLee Humphries31-Oct-10 20:40 
I deal with equally large numbers of rows (although just a months worth) and I found this bit of SQL really helps the performance - run it nightly. Plus you can run it while everything else is going on without it spoiling other operations (for SQL Server 2008):
-- Ensure a USE <databasename> statement has been executed first.
-- Or just run this against the correct database
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 @good float;
DECLARE @bad float;
DECLARE @ugly float;
DECLARE @command nvarchar(4000); 
 
-- Set the threshholds
SET @good = 5.0;
SET @bad = 10.0;
SET @ugly = 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
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @bad AND index_id > 0;
 
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR 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;
        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;
 
-- @ugly (30) is an arbitrary decision point at which to switch between reorganizing and rebuilding.
		SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname
        IF @frag < @ugly
            SET @command = @command + N' REORGANIZE';
        IF @frag >= @ugly
            SET @command = @command + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;
 
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
 
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Never attribute to malice that which can be adequately explained by stupidity.

JokeWhat a coincidence Pin
Mike Hankey30-Oct-10 17:03
memberMike Hankey30-Oct-10 17:03 
GeneralNot that I condone this sort of behavior... Pin
Dalek Dave30-Oct-10 11:52
memberDalek Dave30-Oct-10 11:52 
GeneralRe: Not that I condone this sort of behavior... Pin
Richard A. Dalton30-Oct-10 13:07
memberRichard A. Dalton30-Oct-10 13:07 
GeneralRe: Not that I condone this sort of behavior... Pin
CaptainSeeSharp30-Oct-10 13:54
memberCaptainSeeSharp30-Oct-10 13:54 
GeneralRe: Not that I condone this sort of behavior... Pin
wout de zeeuw30-Oct-10 23:33
memberwout de zeeuw30-Oct-10 23:33 
GeneralRe: Not that I condone this sort of behavior... Pin
wout de zeeuw30-Oct-10 23:34
memberwout de zeeuw30-Oct-10 23:34 
GeneralRe: Not that I condone this sort of behavior... Pin
Brady Kelly31-Oct-10 3:15
memberBrady Kelly31-Oct-10 3:15 
GeneralRe: Not that I condone this sort of behavior... Pin
PIEBALDconsult30-Oct-10 15:45
mvpPIEBALDconsult30-Oct-10 15:45 
GeneralRe: Not that I condone this sort of behavior... Pin
JimmyRopes30-Oct-10 20:31
memberJimmyRopes30-Oct-10 20:31 
GeneralRe: Not that I condone this sort of behavior... Pin
Andrew Torrance31-Oct-10 1:08
memberAndrew Torrance31-Oct-10 1:08 
GeneralRe: Not that I condone this sort of behavior... Pin
Trollslayer31-Oct-10 3:02
mentorTrollslayer31-Oct-10 3:02 
GeneralWhat sort of mind do you have to have to think this stuff up? (Buddhists look away now) Pin
Henry Minute30-Oct-10 11:19
mvpHenry Minute30-Oct-10 11:19 
GeneralRe: What sort of mind do you have to have to think this stuff up? (Buddhists look away now) Pin
Mike Hankey30-Oct-10 11:30
memberMike Hankey30-Oct-10 11:30 
GeneralRe: What sort of mind do you have to have to think this stuff up? (Buddhists look away now) Pin
GenJerDan1-Nov-10 3:39
memberGenJerDan1-Nov-10 3:39 
GeneralRe: What sort of mind do you have to have to think this stuff up? (Buddhists look away now) Pin
Roger Wright30-Oct-10 22:19
memberRoger Wright30-Oct-10 22:19 
GeneralRe: What sort of mind do you have to have to think this stuff up? (Buddhists look away now) Pin
OriginalGriff30-Oct-10 22:33
memberOriginalGriff30-Oct-10 22:33 
GeneralDriven to Bing by Google Pin
cmk30-Oct-10 11:10
membercmk30-Oct-10 11:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Mobile
Web01 | 2.8.170624.1 | Last Updated 26 Jun 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid