NEERAJPRASADSHARMA

2 Suppoting SPs RUN AFTER TABLE CREATION

Jul 20th, 2017
47
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. CREATE PROCEDURE CleanBufferAndWaitBeforeTest
  4. AS
  5. BEGIN
  6. CheckPoint;
  7. Dbcc DropCleanBuffers WITH NO_INFOMSGS ;
  8.  
  9. DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR) WITH NO_INFOMSGS ;
  10.  
  11. END
  12.  
  13. GO
  14.  
  15.  
  16.  
  17. CREATE PROCEDURE GetStatINFO
  18. AS
  19. BEGIN
  20. --- PAUL RANDAL SCRIPT
  21.  
  22.  
  23.  
  24. ;WITH [Waits] AS
  25.     (SELECT
  26.         [wait_type],
  27.         [wait_time_ms] / 1000.0 AS [WaitS],
  28.         ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
  29.         [signal_wait_time_ms] / 1000.0 AS [SignalS],
  30.         [waiting_tasks_count] AS [WaitCount],
  31.        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
  32.         ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
  33.     FROM sys.dm_os_wait_stats
  34.     WHERE [wait_type] NOT IN (
  35.         N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
  36.         N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
  37.         N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
  38.         N'CHKPT', N'CLR_AUTO_EVENT',
  39.         N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
  40.  
  41.         -- Maybe uncomment these four if you have mirroring issues
  42.         N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
  43.         N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
  44.  
  45.         N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
  46.         N'EXECSYNC', N'FSAGENT',
  47.         N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
  48.  
  49.         -- Maybe uncomment these six if you have AG issues
  50.         N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
  51.         N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
  52.         N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
  53.  
  54.         N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
  55.         N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
  56.         N'ONDEMAND_TASK_QUEUE',
  57.         N'PREEMPTIVE_XE_GETTARGETSTATE',
  58.         N'PWAIT_ALL_COMPONENTS_INITIALIZED',
  59.         N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
  60.         N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
  61.         N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
  62.         N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
  63.         N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
  64.         N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
  65.         N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
  66.         N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
  67.         N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
  68.         N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
  69.         N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
  70.         N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
  71.         N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
  72.         N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
  73.         N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
  74.         N'WAIT_XTP_RECOVERY',
  75.         N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
  76.         N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
  77.         N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
  78.     AND [waiting_tasks_count] > 0
  79.     )
  80. SELECT
  81.     MAX ([W1].[wait_type]) AS [WaitType],
  82.     CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
  83.     CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
  84.     CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
  85.     MAX ([W1].[WaitCount]) AS [WaitCount],
  86.     CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
  87.     CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
  88.     CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
  89.     CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
  90.     CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
  91. FROM [Waits] AS [W1]
  92. INNER JOIN [Waits] AS [W2]
  93.     ON [W2].[RowNum] <= [W1].[RowNum]
  94. GROUP BY [W1].[RowNum]
  95. HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
  96.  
  97. END
  98.  
  99.  
  100. GO
  101.  
  102. CREATE PROCEDURE GETINDEXINFO (@Table_name varchar(50))
  103. AS BEGIN
  104. --Select @Table_name
  105. SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
  106. avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
  107. FROM sys.dm_db_index_physical_stats
  108. (db_id(db_name()), object_id (@Table_name ), 1 ,null,'DETAILED')
  109. END
RAW Paste Data