Skip to content
Maxiom Technology - Top Rated Software Development Company in Washington DCMaxiom Technology - Top Rated Software Development Company in Washington DC
  • Services

    Services

    Clutch Global Leader Award Winner

    Software Engineering

    • Software Development
    • Mobile App Development
    • Web App Development
    • Frontend Development
    • Backend Development
    • QA & Testing
    • DevOps
    • Support & Maintenance

    Artificial Intelligence

    • AI Development
    • AI Consulting
    • AI App Development
    • AI Proof of Concept
    • Deep Learning
    • Machine Learning
    • Facial Recognition
    • NLP

    Creative Design

    • Digital Product Design
    • Digital Product Development
    • UI/UX Design
    • UI/UX Consulting
    • UX Research
    • Design Systems
    • Design Experimentation
    • Dashboard Development

    Data & Analytics

    • Performance Marketing
    • Predictive Analytics
    • Data Consulting
    • Data Strategy
    • Data Architecture
    • Data Engineering
    • Data Warehouse
    • Enterprise Data Platform
  • MVP Development
  • Hire Developers
  • Industries
    • Fintech
    • Healthcare
    • Human Resources
    • Non Profit
    • Real Estate
    • SaaS
    • Telecom
    • Other Industries
  • Case Studies
  • Blog
  • Company
    • About Us
    • Careers
    • News & Events
    • Contact Us

Say hello,
and let us help

Thank you for your interest in Maxiom Technology’s software development solutions and services. We are looking forward to working with you on your next software project.

Edit Content
Exceptional dedication and professionalism. Collaborating with Maxiom proved to be an outstanding choice as they addressed our needs with precision, expertise, and unwavering focus.
Laura Ballek-Cole. City of Allentown PA
Laura Quinn
Digital Production Manager

    Software Development, Support and Maintenance

    Reorganize and Rebuild Indexes in SQL Server

    January 14, 2013
    Reorganize and Rebuild Indexes in SQL Server

    Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance.

    Time to clean house

    Reorganizing and rebuilding indexes is a quick way to optimize your databases’ performance.

    We periodically (daily, weekly, or monthly) perform database reorganization on all the indexes on all the tables in our databases and those of clients whom subscribe to our support and maintenance plans.

    From time to time we run into folks who either reorganize OR rebuild indexes at any given time. Their reasoning  is that indexes should be rebuilt when they are over 30% fragmented and they should be reorganized when they are between 10% and 30% fragmented. Also, running both every time could result in a waste of system resources. We agree, however, we have resolved this problem by creating a script (below) that reorganizes and rebuilds indexes only if those conditions specified are met – in one single shot.

    Finally, you might be wandering why we do not use SQL Server’s DBCC DBREINDEX command instead of using a custom script. The reason for this is because DBCC DBREINDEX will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables AND in all the databases within the running instance:

    [code lang=”sql”]
    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; a
    DECLARE @partitions BIGINT;
    DECLARE @frag FLOAT;
    DECLARE @command NVARCHAR(4000);
    DECLARE @dbid SMALLINT;

    — Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    — and convert object and index IDs to names.
    SET @dbid = Db_id();

    SELECT [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag,
    page_count
    INTO #work_to_do
    FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N’LIMITED’)
    WHERE avg_fragmentation_in_percent > 10.0 — Allow limited fragmentation
    AND index_id > 0 — Ignore heaps
    AND page_count > 25; — Ignore small tables

    — Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR
    SELECT objectid,
    indexid,
    partitionnum,
    frag
    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;

    — 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0 SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REORGANIZE’; IF @frag >= 30.0
    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 N’Executed: ‘ + @command;
    END

    — Close and deallocate the cursor.
    CLOSE partitions;

    DEALLOCATE partitions;

    — Drop the temporary table.
    DROP TABLE #work_to_do;

    go
    [/code]

    Now you can wrap this script within a SQL Server Maintenance Plan and have it run automatically at your desired time interval.

    • indexes
    • sql
    • SQL Server

    Post navigation

    Previous
    Next

    Search

    Categories

    • Artificial Intelligence (40)
    • Autonomous Delivery Team (3)
    • Awards and Recognition (8)
    • Case Studies (13)
    • Chatbot Development (5)
    • Data & Analytics (14)
    • Deep Learning (6)
    • DevOps (11)
    • DNN Software (4)
    • Enterprise ERP and CMS (10)
    • Generative AI (6)
    • Hybrid Apps (3)
    • Machine Learning (19)
    • Managed Remote Teams (4)
    • Misc (2)
    • Mobile App Development (25)
    • MVP Development (30)
    • Native Apps (4)
    • Natural Language Processing (16)
    • News & Events (16)
    • On-Demand Developers (4)
    • Progressive Web Apps (5)
    • Robotic Process Automation (1)
    • Software Development (93)
    • Staff Augmentation (7)
    • Support and Maintenance (11)
    • System Integrations (8)
    • UI/UX Design (36)

    Tags

    Agile development AI AI development AI in Healthcare AI in Software Development AI Technology AI Technology Trends App development Artificial Intelligence Awards clutch.io custom software development design Employee Engagement Fintech innovation Healthcare technology HR Technology javascript Language Model Development Language Models Large Language Models LLMs Machine Learning Maxiom Technology Mobile MVP (Minimum Viable Product) MVP Creation MVP Development MVP Process NLP Predictive Analytics project management seo Software software development tag startups StartupSuccess UI/UX UI Design user experience User Interface (UI) UX UX/UI Design UX Design Web Design

    Related posts

    how to fix sql server
    Software Development, Support and Maintenance, System Integrations

    How to fix SQL Server Management Studio COM error on startup

    January 7, 2014

    Every once in a while I run into a weird COM error when running SQL Server Management Studio. Here is a quick article that shows you how to fix it.

    Maxiom Technology - Top Rated Software Development Company in Washington DC

    Maxiom Technology is a leading full-service software development company based in the Washington, D.C. metro area. We proudly serve businesses across the DMV region, including Washington, D.C., Maryland, and Virginia, as well as clients throughout the United States. Our expertise includes custom software development, AI-powered solutions, MVP development, staff augmentation, and digital transformation services for startups, enterprises, and government organizations.

    44927 George Washington Blvd
    Suite 265
    Ashburn, VA 20147

    [email protected]
    +1 (703) 942-9420

    DUNS: 006357922
    CAGE: 7SZQ7
    NAICS: 541511, 541512, 541519,
    541618, 541690, 541720, 541611

     

    Company
    • About Us
    • What We Do
    • Case Studies
    • Blog
    • Careers
    • Free Consultation
    • Contact Us
    Services
    • Artificial Intelligence
    • Software Engineering
    • Remote Teams
    • Data & Analytics
    • Creative Design
    • MVP Development
    • Mobile App Development
    Industries
    • Fintech
    • Healthcare
    • Human Resources
    • Non Profit Organizations
    • Real Estate
    • SaaS
    • Telecom
    • Other Industries
    Industry Partners
    • Hyperlogs
    • Personify ThreeSixty
    • DNN Software
    Innovation Lab

    AI Pilot Project: A Success Guide for Fintech Teams

    Powerful Data Strategy Guide for Nonprofits | Maxiom Technology

    Data Architecture Trends: Unlock Scalability for SaaS Growth

    Data Engineering Solutions: Powerful Boost for Fintech Growth

    Data Warehouse Benefits: Powerful Boost for Healthcare Data

    Enterprise Data Platform: Key to Modern HR Success

    MVP Development Guide: Unlock Powerful Growth for SaaS

    Staff Augmentation Benefits: Perfect for Scaling Healthcare

    Benefits of Remote Teams: Unlock Powerful Flexibility & Growth

    Data Analytics Importance: Unlocking Nonprofit Success

    Clutch Global Leader Award Winner
    Maxiom is Clutch Fall Champion 2023
    Top Mobile App Developers 2022 on Clutch
    Goodfirms Top Software Development Company
    G2 Top 50 Development Products

    © Maxiom Technology. All Rights Reserved.