The below query uses the sys.dm_os_performance_counters DMV to capture the performance counters of SQL Server. You can change the WAITFOR DELAY options and the Numeric suffix after the second GO command to modify the time interval and the number of times you want the code to be executed.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('Tempdb..#PerformanceMonitor') IS NOT NULL
DROP TABLE #PerformanceMonitor;
CREATE TABLE #PerformanceMonitor
[object_name] NVARCHAR(255) NOT NULL,
[counter_name] NVARCHAR(255) NOT NULL,
[instance_name] NVARCHAR(255) NULL,
[cntr_value] BIGINT NOT NULL,
[cntr_type] INT NOT NULL,
[LoadedOn] DATETIME NOT NULL DEFAULT GETDATE(),
INSERT INTO #PerformanceMonitor
WAITFOR DELAY '00:00:10' --Change the interval here
GO 10 --Change the number of times the data will be captured here
SELECT * FROM #PerformanceMonitor
Once you have enough data and you have narrowed down the counters which are relevant, you can decide upon the best time to run your queries. And also as mentioned in the answer by @Yaroslav ,it is very important to keep in mind what type of query you want to run and decide accordingly.
And the most important thing, be careful with production data!!!