Welcome in Dinesh Patel Blog

IF OBJECT_ID('dbo.spSQLPerf') IS NOT NULL    DROP PROC dbo.spSQLPerf GO CREATE PROCEDURE dbo.spSQLPerf  AS      DBCC SQLPERF(logspace)   GO IF OBJECT_ID('dbo.logSpaceStats') IS NULL BEGIN CREATE TABLE dbo.logSpaceStats  (  id INT IDENTITY (1,1),  logDate datetime DEFAULT GETDATE(),  databaseName sysname,  logSize decimal(18,5),  logUsed decimal(18,5)  )  END GO IF OBJECT_ID('dbo.spGetSQLPerfStats') IS NOT NULL    DROP PROC dbo.spGetSQLPerfStats GO CREATE PROCEDURE dbo.spGetSQLPerfStats  AS  SET NOCOUNT ON  CREATE TABLE #tFileList  (  databaseName sysname,  logSize decimal(18,5),  logUsed decimal(18,5),  status INT  )  INSERT INTO #tFileList         EXEC spSQLPerf  INSERT INTO tempdb.dbo.logSpaceStats (databaseName, logSize, logUsed)  SELECT databasename, logSize, log...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT index_group_handle, index_handle , avg_total_user_cost, avg_user_impact, user_seeks, user_scans INTO #PreWorkMissingIndexes FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time , total_logical_reads, total_logical_writes, total_clr_time , execution_count, statement_start_offset, statement_end_offset INTO #PreWorkQuerySnapShot FROM sys.dm_exec_query_stats SELECT [object_name], [counter_name], [instance_name] , [cntr_value], [cntr_type] INTO #PreWorkOSSnapShot FROM sys.dm_os_performance_counters SELECT wait_type, waiting_tasks_count , wait_time_ms, max_wait_time_ms, signal_wait_time_ms INTO #PreWorkWaitStats FROM sys.dm_os_wait_stats WAITFOR DELAY '00:05:00' SELECT wait_type, waiting_tasks_count, wait_time_ms , max_wait_time_ms, signa...

SELECT  sys.objects.name ,(avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact ,avg_total_user_cost ,avg_user_impact ,user_seeks ,user_scans , mid.equality_columns , mid.inequality_columns , mid.included_columns      FROM sys.dm_db_missing_index_group_stats AS migs              INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle              INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() --and (mid.equality_columns like '%Structure%')                         INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID --and name  not in ('AnalysisCode','Contract')     WHERE     (migs.group_handle IN          (  ...

SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,         OBJECT_NAME(I.OBJECT_ID) AS ObjectName,         I.NAME AS IndexName         FROM    sys.indexes I    WHERE   -- only get indexes for user created tables         OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1          -- find all indexes that exists but are NOT used         AND NOT EXISTS (                      SELECT  index_id                      FROM    sys.dm_db_index_usage_stats                     WHERE   OBJECT_ID = I.OBJECT_ID                              AND I.index_id = index_id      ...