SELECT top 100  Indx.name AS Index_Name,
OBJECT_NAME(Indx.OBJECT_ID) AS Source_Table_Name,
Index_Stat.index_type_desc AS TypeOfIndex,
Index_Stat.avg_fragmentation_in_percent  Index_Fragmentation_Percentage
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_Stat
INNER JOIN sys.indexes Indx  ON Indx.object_id = Index_Stat.object_id
AND Indx.index_id = Index_Stat.index_id
ORDER BY Index_Fragmentation_Percentage DESC

Gather SQL Server Indexes Fragmentation Information

SQL 24X7 0

Read More

DECLARE @dbid INT
    , @dbName VARCHAR(100);

SELECT @dbid = DB_ID()
    , @dbName = DB_NAME();

WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count)
AS
(
    SELECT [OBJECT_ID]
        , index_id
        , SUM([ROWS]) AS 'row_count'
        , COUNT(partition_id) AS 'partition_count'
    FROM sys.partitions
    GROUP BY [OBJECT_ID]
        , index_id
)

SELECT OBJECT_SCHEMA_NAME(i.[OBJECT_ID]) AS SchemaName
, OBJECT_NAME(i.[OBJECT_ID]) AS ObjectName
        , i.name AS IndexName
        , CASE
            WHEN i.is_unique = 1
                THEN 'UNIQUE '
            ELSE ''
          END + i.type_desc AS 'IndexType'
        , ddius.user_seeks
        , ddius.user_scans
        , ddius.user_lookups
        , ddius.user_updates
        , cte.row_count
        , CASE WHEN partition_count > 1 THEN 'yes'
            ELSE 'no' END AS 'partitioned?'
        , CASE
            WHEN i.type = 2 And i.is_unique = 0
                THEN 'Drop Index ' + i.name
                    + ' On ' + @dbName
                    + '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID]) + ';'
            WHEN i.type = 2 And i.is_unique = 1
                THEN 'Alter Table ' + @dbName
                    + '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID])
                    + ' Drop Constraint ' + i.name + ';'
            ELSE ''
          END AS 'SQL_DropStatement'
FROM sys.indexes AS i
INNER Join sys.dm_db_index_usage_stats ddius
    ON i.OBJECT_ID = ddius.OBJECT_ID
        And i.index_id = ddius.index_id
INNER Join partitionCTE AS cte
    ON i.OBJECT_ID = cte.OBJECT_ID
        And i.index_id = cte.index_id
WHERE ddius.database_id = @dbid
ORDER BY
    (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) ASC
    , user_updates DESC;

Unused Indexes

SQL 24X7 0

Read More

/*SQL Differential Backup Size Prediction / Estimation 
 
IF isNULL(object_id('tempdb.dbo.##showFileStats'), 1) <> 1
 DROP TABLE ##showFileStats
 
CREATE TABLE ##showFileStats (
 fileID INT,
 fileGroup INT,
 totalExtents BIGINT,
 usedExtents BIGINT,
 logicalFileName VARCHAR (500), 
 filePath VARCHAR (1000)
)
 
IF isNULL(object_id('tempdb.dbo.##DCM'), 1) <> 1
 DROP TABLE ##DCM
 
CREATE TABLE ##DCM (
 parentObject VARCHAR(5000),
 [object] VARCHAR(5000),
 FIELD VARCHAR (5000),
 VALUE VARCHAR (5000)
)
 
/*we need to get a list of all the files in the database.  each file needs to be looked at*/ 
INSERT INTO ##showFileStats EXEC('DBCC SHOWFILESTATS with tableresults')
 
DECLARE @currentFileID INT,
 @totalExtentsOfFile BIGINT,
 @dbname VARCHAR(100),
 @SQL VARCHAR(200),
 @currentDCM BIGINT,
 @step INT
 
SET @dbname = db_name()
SET @step = 511232
 
DECLARE myCursor SCROLL CURSOR FOR
SELECT fileID, totalExtents 
FROM ##showFileStats
 
OPEN myCursor
FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
 
/*look at each differential change map page in each data file of the database and put the output into ##DCM*/
WHILE @@FETCH_STATUS = 0 
BEGIN
 
 SET @currentDCM = 6
 WHILE @currentDCM <= @totalExtentsOfFile*8
 BEGIN 
  SET @SQL = 'dbcc page('+ @dbname + ', ' + CAST(@currentFileID AS VARCHAR) + ', ' + CAST(@currentDCM AS VARCHAR) + ', 3) WITH TABLERESULTS'
  INSERT INTO ##DCM EXEC (@SQL)
  SET @currentDCM = @currentDCM + @step
 END
 
 FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
END
CLOSE myCursor
DEALLOCATE myCursor
 
/*remove all unneeded rows from our results table*/
DELETE FROM ##DCM WHERE VALUE = 'NOT CHANGED' OR parentObject NOT LIKE 'DIFF_MAP%'
--SELECT * FROM ##DCM
 
/*sum the extentTally column*/
SELECT SUM (extentTally) AS totalChangedExtents, SUM(extentTally)/16 AS 'diffPrediction(MB)', SUM(extentTally)/16/1024 AS 'diffPrediction(GB)' 
FROM
 /*create extentTally column*/
 (SELECT extentTally =
 CASE
  WHEN secondChangedExtent > 0 THEN CAST(secondChangedExtent AS BIGINT) - CAST(firstChangedExtent AS BIGINT) + 1
  ELSE 1
 END
 FROM
  /*parse the 'field' column to give us the first and last extents of the range*/
  (SELECT (SUBSTRING(FIELD,(SELECT CHARINDEX(':', FIELD, 0))+1,(CHARINDEX(')', FIELD, 0))-(CHARINDEX(':', FIELD, 0))-1))/8 AS firstChangedExtent,
  secondChangedExtent = 
  CASE 
   WHEN CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1) > 0 THEN (SUBSTRING(FIELD,(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1)+1),(CHARINDEX(')', FIELD,CHARINDEX(')', FIELD, 0)+1))-(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1))-1))/8
   ELSE ''
  END 
  FROM ##DCM)parsedFieldColumn)extentTallyColumn

SQL Differential Backup Size Prediction / Estimation

SQL 24X7 0

Read More

-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(r.database_id) AS [DatabaseName]
, s.login_name
, s.session_id
, r.status
, r.blocking_session_id
, r.cpu_time
, r.logical_reads
, r.writes
, r.wait_resource
, r.wait_type
, r.wait_time
, r.granted_query_memory
, mg.requested_memory_kb
, mg.ideal_memory_kb
, mg.request_time
, mg.grant_time
, mg.query_cost
, mg.dop
, st.[text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests as r on r.session_id = s.session_id
INNER JOIN sys.dm_exec_query_memory_grants AS mg ON mg.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
--CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE MG.session_id <> @@SPID
ORDER BY mg.requested_memory_kb DESC
OPTION (MAXDOP 1, RECOMPILE)

Shows the memory required by both running (non-null grant_time) and waiting queries

SQL 24X7 0

Read More

-- Find Top 10 Most Expensive Statements by logical read/write or cpu time
SELECT TOP 10 SUBSTRING(qt.TEXT,(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes,
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC; --logical reads
--ORDER BY qs.total_logical_writes DESC; --logical writes
--ORDER BY qs.total_worker_time DESC; --CPU time

Find Top 10 Most Expensive Statements by logical read/write or cpu time

SQL 24X7 0

Read More

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN

('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE) -- percentage threshold

GetTopSQLServerWaits_ObservingWaitStatistics

SQL 24X7 0

Read More

Find Missing Index:

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('Yourdatabasename')

ORDER BY Avg_Estimated_Impact DESC

SQL 24X7 0

Read More

Check Blocking: 

SELECT

blocking_session_id AS BlockingSessionID,

session_id AS VictimSessionID,

(SELECT [text] FROM sys.sysprocesses

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE spid = blocking_session_id) AS BlockingQuery,

[text] AS VictimQuery,

wait_time/1000 AS WaitDurationSecond,

wait_type AS WaitType,

percent_complete AS BlockingQueryCompletePercent

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE blocking_session_id > 0

SQL 24X7 0

Read More

Change collation - 

USE master; GO   -- Kill all spids to a single DB DECLARE @SQL VARCHAR(8000) SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; ' FROM sys.sysprocesses WHERE DBID = DB_ID('DatabaseName') PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute -- Set to single-user mode ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO    -- Kill all spids to a single DB DECLARE @SQL VARCHAR(8000) SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; ' FROM sys.sysprocesses WHERE DBID = DB_ID('DatabaseName') -- change collation ALTER DATABASE [DatabaseName]   COLLATE SQL_Latin1_General_CP1_CI_AS;   GO     -- Set to multi-user mode ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO     --Verify the collation setting.   SELECT name, collation_name   FROM sys.databases   WHERE name = N'DatabaseName';   GO

SQL 24X7 0

Read More

INCREMENTAL SHRINK 

* SQL 2005 -- Shrink data or log files in set increments to a desired target size

*

* Description:

* Shrinking database files can be a very resource intensive task

* when dealing with large files. Performing a DBCC Shrinkfile in

* small increments should help. This script will automate this task.

* Set your desired target file size and set the increment size for

* each shrink operation. Change the increment size accordingly based

* on your database environment. Start with a small increment size of 128MB

* and see how it goes.

*

*

*

**********************************************************************************/

DECLARE @FileSize varchar(20),

                 @LogicalFile varchar(50),

                 @TargetFileSize int,

                 @NewFileSize varchar(20),

                 @IncrementSize int

        

---Logical name of Database file to shrink

SET @LogicalFile = 'DWAnalytics'  

---This is the desired target size in MB after the shrink operation.

SET @TargetFileSize = 820375    

---The increment amount in MB the file will shrink by in each pass (e.g. 128)

SET @IncrementSize = 1024   

SET @FileSize = (SELECT [Size]/128 FROM dbo.Sysfiles WHERE [name] = @LogicalFile)    

SET @NewFileSize = @FileSize -@IncrementSize

WHILE @FileSize > @TargetFileSize

    

    BEGIN

        BEGIN

            EXEC ('DBCC SHRINKFILE ' + '(N' + '''' + @LogicalFile +'''' + ', ' + @NewFileSize + ')')

                        

        END

            

            SET    @FileSize = (SELECT [Size]/128 FROM dbo.Sysfiles WHERE [name] = @LogicalFile)

        

            SET @NewFileSize = @FileSize -@IncrementSize

    END

GO

SQL 24X7 0

Read More