European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2014 Hosting Spain - HostForLIFE.eu :: How to Check Fragmentation in SQL Server ?

clock December 9, 2014 07:48 by author Peter

When you have performance problems in your MSSQL database, one of the first thing you must to verify is that the fragmentation. When the fragmentation is high, SQL Server has got the chance to both reorganize or rebuild indexes. You are able to detect index fragmentation by making use of Dynamic Management View (DMV) sys.dm_db_index_physical_stats and verify the avg_fragmentation_in_percent column.

Use [MyDB];
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC;


When the value avg_fragmentation_in_percent in among 5% and 30% you ought to perform a reorganize of your indexes, not really a total rebuild. A rebuild you simply need invoked when the fragmentation percentage is more than 30%. No action ought to be taken when the fragmentation proportion is lower than 5% and that is a standard level of fragmentation.

The code below can rebuild all indexes with default fill factor:
Use [MyDB];
EXEC sp_MSforeachtable
  @command1="print '?'",
  @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

Hope this code will helps you!

 



SQL Server 2014 Hosting Netherlands - HostForLIFE.eu :: Index Unused Script in SQL Server

clock December 4, 2014 05:52 by author Peter

As the majority of you will know, an index can enhance the performance of the query most of the time ; internally sql server has to carry out lots of function to keep these indexes in SQL Server 2014. When I started a brand new occupation, I arrived to discover lots of indexes were developed for a few in our production tables. Thus I made a decision to get yourself a script which returns the indexes that’s not active.


Note : it uses the dmv’s so, this isn't a fairly accurate query. However you could use this like a start and apply reasonable just before running the drop index statement.
SELECT  DB_NAME() AS database_name ,
        S.name AS [schema_name] ,
        O.name AS [object_name] ,
        C.name AS column_name ,
        I.name AS index_name ,
        ( CASE WHEN I.is_disabled = 1 THEN 'Yes'
                ELSE 'No'
           END ) AS [disabled] ,
        ( CASE WHEN I.is_hypothetical = 1 THEN 'Yes'
                ELSE 'No'
            END ) AS hypothetical ,
        rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id
                        AND p.object_id = i.object_id GROUP BY p.index_id, p.OBJECT_ID),
        N'USE ' + DB_NAME() + N'; DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) +
                    '.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS 'drop statement'
FROM    [sys].[indexes] I
       INNER JOIN [sys].[objects] O ON O.[object_id] = I.[object_id]
                                       AND O.[type] = 'U'
                                        AND O.is_ms_shipped = 0
                                        AND O.name <> 'sysdiagrams'
        INNER JOIN [sys].[tables] T ON T.[object_id] = I.[object_id]
        INNER JOIN [sys].[schemas] S ON S.[schema_id] = T.[schema_id]
        INNER JOIN [sys].[index_columns] IC ON IC.[object_id] = I.[object_id]
                                                AND IC.index_id = I.index_id
        INNER JOIN [sys].[columns] C ON C.[object_id] = IC.[object_id]
                                        AND C.column_id = IC.column_id
WHERE   I.[type] > 0
        AND I.is_primary_key = 0
        AND I.is_unique_constraint = 0
        AND NOT EXISTS ( SELECT *
                            FROM   [sys].[index_columns] XIC
                                INNER JOIN [sys].[foreign_key_columns] FKC ON FKC.parent_object_id =
IC.[object_id]
                                                    AND FKC.parent_column_id = XIC.column_id
                            WHERE  XIC.[object_id] = I.[object_id]
                               AND XIC.index_id = I.index_id )
        AND NOT EXISTS ( SELECT *
                            FROM   [master].[sys].[dm_db_index_usage_stats] IUS
                            WHERE  IUS.database_id = DB_ID(DB_NAME())
                                AND IUS.[object_id] = I.[object_id]
                                AND IUS.index_id = I.index_id )



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in