Below are the few setting in SQL Server that
can improve the performance of your queries.
Ø Statistics
Update – enables the
optimizer to create better execution plans
Ø Tempdb
Configuration –
improves concurrency
Ø Max
Degree of Parallelism(MAXDOP)
– improves query performance
Ø Cost
Threshold for Parallelism
- improves query performance
Ø Instant
File Initialization –
faster file growth
Ø Optimize
for Ad Hoc Workloads –
reduces cached plan bloat
Ø Memory
Configuration – how
much memory to give to SQL Server
Ø Lock
Pages in Memory – prevents
swapping to disk
Ø Query
Governor Cost Limit -
allows resources to be shared fairly
Counters from Perfmon :
Ø Page
Reads/Sec – indicates
time spent reading data
Ø Page
Writes/Sec -indicates
time spent writing data
Ø Page
Life Expectancy - how
long data lives in cache
Ø Buffer
Cache Hit Ratio – is
data coming from cache or disk
Ø %
Processor Time – CPU
usage
Ø Processor
Queue Length – waiting
on CPU
Ø Locks – indicates reduced concurrency
DMV’s :
Ø sys.dm_io_virtual_file_stats – IO and wait information
Ø sys.dm_db_index_usage_stats – how a table/index is used
Ø sys.dm_exec_query_stats – records time, IO, CPU etc used by
queries
Ø sys.dm_os_sys_info – OS information e.g. CPU count, server
memory
No comments:
Post a Comment