SHARE
TWEET

2 Suppoting SPs RUN AFTER TABLE CREATION

NEERAJPRASADSHARMA Jul 20th, 2017 31 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top