June 12, 2014 at 7:07 am
IT researcher (6/12/2014)
The SQL server instance is used by employees in office. So it will be running only during office timings. So we keep the server OFF during night.
Again, why?
Means you don't have the overnight period to do maintenance. When do you do your backups, integrity checks and index rebuilds if the server's off all night? During peak business time? Also means that you're going to have slow performance every single morning as SQL reloads the data cache and recompiles all procedures
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2014 at 7:18 am
GilaMonster (6/12/2014)
IT researcher (6/12/2014)
The SQL server instance is used by employees in office. So it will be running only during office timings. So we keep the server OFF during night.Again, why?
Means you don't have the overnight period to do maintenance. When do you do your backups, integrity checks and index rebuilds if the server's off all night? During peak business time? Also means that you're going to have slow performance every single morning as SQL reloads the data cache and recompiles all procedures
We do de-fragment and some other maintenance work after 7 PM. During that time usage will be less as only about 20-30 users will be there.
June 12, 2014 at 7:19 am
IT researcher (6/12/2014)
GilaMonster (6/12/2014)
IT researcher (6/12/2014)
The SQL server instance is used by employees in office. So it will be running only during office timings. So we keep the server OFF during night.Again, why?
Means you don't have the overnight period to do maintenance. When do you do your backups, integrity checks and index rebuilds if the server's off all night? During peak business time? Also means that you're going to have slow performance every single morning as SQL reloads the data cache and recompiles all procedures
We do de-fragment and some other maintenance work after 7 PM. During that time usage will be less as only about 20-30 users will be there.
Leave the machine on, shift that work to 2AM and let the business have exclusive access to the server. It's only an 8gb machine. That's tiny. Might as well have all the resources available for the business.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2014 at 2:37 pm
I also suggest considering average wait time as opposed to only using total wait time:
select
case when waiting_tasks_count = 0 then 0 else
(wait_time_ms - signal_wait_time_ms) / waiting_tasks_count
end as avg_wait_time_ms,
*
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by avg_wait_time_ms desc;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply