SQL Server Missing Index Script

 -- Missing Index Script

-- Original Author: Pinal Dave 

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

mysql 5.5 on centos 6

 # For x86_64

yum install http://repo.mysql.com/yum/mysql-5.5-community/el/6/x86_64/mysql-community-release-el6-5.noarch.rpm

yum install -y --enablerepo=mysql55-community --disablerepo=mysql56-community mysql-community-server

SQL Server: Determine Max DoP

https://dba.stackexchange.com/questions/36522/maxdop-setting-algorithm-for-sql-server?noredirect=1&lq=1

Older versions of SQL Server:

DECLARE @CoreCount int;

DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);

SET @NumaNodes = (

    SELECT MAX(c.memory_node_id) + 1 

    FROM sys.dm_os_memory_clerks c 

    WHERE memory_node_id < 64

    );

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */

BEGIN

    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */

    SET @MaxDOP = @CoreCount * 0.75; 

    /* if @MaxDOP is greater than the per NUMA node

       Core Count, set @MaxDOP = per NUMA node core count

    */

    IF @MaxDOP > (@CoreCount / @NumaNodes) 

        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

    /*

        Reduce @MaxDOP to an even number 

    */

    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */

    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));

END

ELSE

BEGIN

    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';

    PRINT 'This is the default setting, you likely do not need to do';

    PRINT 'anything.';

END

Newer versions of SQL Server: 

DECLARE @socket_count int;

DECLARE @cores_per_socket int;

DECLARE @numa_node_count int;

DECLARE @memory_model nvarchar(120);

DECLARE @hyperthread_ratio int;

SELECT @socket_count = dosi.socket_count

       , @cores_per_socket = dosi.cores_per_socket

       , @numa_node_count = dosi.numa_node_count

       , @memory_model = dosi.sql_memory_model_desc

       , @hyperthread_ratio = dosi.hyperthread_ratio

FROM sys.dm_os_sys_info dosi;

SELECT [Socket Count] = @socket_count

       , [Cores Per Socket] = @cores_per_socket

       , [Number of NUMA nodes] = @numa_node_count

       , [Hyperthreading Enabled] = CASE WHEN @hyperthread_ratio > @cores_per_socket THEN 1 ELSE 0 END

       , [Lock Pages in Memory granted?] = CASE WHEN @memory_model = N'CONVENTIONAL' THEN 0 ELSE 1 END;

DECLARE @MAXDOP int = @cores_per_socket;

SET @MAXDOP = @MAXDOP * 0.75;

IF @MAXDOP >= 8 SET @MAXDOP = 8;

SELECT [Recommended MAXDOP setting] = @MAXDOP

       , [Command] = 'EXEC sys.sp_configure N''max degree of parallelism'', ' + CONVERT(nvarchar(10), @MAXDOP) + ';RECONFIGURE;';

Useful query:

select * FROM sys.dm_os_sys_info dosi;

SQL Server / Ola Hallengren: Re-create statistics

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES'

,@FragmentationLow = NULL

,@FragmentationMedium = NULL

,@FragmentationHigh = NULL

,@UpdateStatistics = 'ALL'

,@StatisticsSample=100