SQL Server Performance Tuning Part 6

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  

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. [SQLSTATE 42000] (Error 1802). The step failed.

Possible Reasons and Resolutions :

 This problem usually occurs when the user that is trying to create the database doesn't have the necessary permissions in SQL Server. If you use the Administrator's account to create the database, this situation can be avoided. You can also check if the account that failed to create the database has the permissions described in this article.
 The problem can also occur if the name of the database you attempted to create is already in use by another database or if files from a database with the same name remain on the computer. Try creating a database with a different name.
 Check if the disk on which the SQL Server is trying to create the database has enough free space.