Tips & Tricks for optimizing the performance of your SQL Server
John Daskalakis shared some handy tips. . .Worth bookmarking:
(Thanks a bunch John!)
Here is a collection of some tips and ideas that you can use to improve the performance of your SQL Server. I had not collected all of these tips in a single reference page till now. It’s not necessary to apply all these ideas in your environment to achieve a good performance, as the cost of certain ideas might be high, but all of these suggestions should help improve performance:
Disk Related
· Use a different physical disk for the SQL server data files (.MDF) and for the SQL Server log files (.LDF). The reason behind this is that .MDF files are accessed randomly, while .LDF files are accessed sequentially. So if you put both .MDF and .LDF files on the same disk, the disk I/O performance will not be optimized. · If the database reaches a big enough size you can start splitting it into several .MDF files, which should be in separate physical disks so that you can perform parallel I/O requests. · Do not place LDF files on a RAID 5 disk subsystem, because the disk performance will be frustrating – so always use RAID 10. MDF files can be placed on either RAID 5 or RAID 10. · Split the tempdb database into as many data files as the processors that SQL server has access to. e.g. if the machine has one quad core CPU, split the tempdb into four data files. You can perform this action even if you won’t place the MDF files in separate physical disks, as the overall cpu contention will be reduced. · Do not place the page file in the same disk with the · Sometimes when you see very bad disk performance (i.e. when you use perfmon and you see Avg Disk sec/Read and Avg Disk Sec/Write values bigger than 40 msec), this might sometimes be caused by index fragmentation. I have seen huge values, like e.g. 900msec, which were related to 90% fragmented indexes.
· If one of your tables grows very big in size, consider using partitioning to divide the table across many physical disks.
Blocking Related
· If you face a lot of blocking issues, you may consider setting the ‘Max Degree of Parallelism’ to a value of 1. This may reduce the impact of the blocking issues, until you can identify the root cause of the blocking. · If you face any deadlock issues, you can use the Profiler tool to trace this event: Deadlock Graph Event Classhttp://msdn.microsoft.com/en-us/library/ms177409.aspx
It will present you with a nice graph, explaining how the deadlock occurred.
Memory Related
· Providing more memory to SQL Server is fine, but be careful not to starve the Operating System out of memory. Especially the /3GB switch may cause some issues in machines that are under heavy workload, so I prefer to use AWE instead. · If you use AWE and you see that SQL server does not take up the memory you have allocated to it:o Give to your SQL Server service account the “Lock Pages in Memory” privilege. How to: Enable the Lock Pages in Memory Optionhttp://msdn.microsoft.com/en-us/library/ms190730.aspx If you have SQL Server 2005, install the latest Service Pack, as there were some related known issues in the past that are now fixed. · To see how much memory SQL server uses, do not use the Task Manager. Instead use the perfmon object called Buffer Manager: Total Pages. Multiply this value by 8 KB (each SQL page is 8096 bytes) and you will know how many bytes of memory SQL Server uses. SQL Server, Buffer Manager Object
http://msdn.microsoft.com/en-us/library/ms189628.aspx
Processor Related
· Make sure that the workload is evenly balanced among all the Processors. You can do this by using Perfmon and adding the “Processor: %Processor Time” counter for all processors. If some processors are hardly ever used you may have set a small value to the “Max Degree of parallelism”, you may be using queries with the MAXDOP hint or you may have set an incorrect affinity mask. max degree of parallelism Optionhttp://msdn.microsoft.com/en-us/library/ms181007.aspx General guidelines to use to configure the MAXDOP optionhttp://support.microsoft.com/kb/329204 affinity mask Option
http://msdn.microsoft.com/en-us/library/ms187104.aspx
Database Engine Related
· When you identify some long-running queries, you should make sure that the proper indexes are in place to support these queries. e.g. if you notice that this query is slow: SELECT name, addressFROM customerWHERE id = ‘675544’ You should make sure that you have an index in place on the customer table that covers the id column. · Schedule a rebuild of the indexes at least once a week, especially if the data in your tables changes fast. Or defragment the indexes if you don’t want to rebuild them. If you suspect this may be the problem,
· Update the statistics of the columns at least once per week. Otherwise the execution plans that SQL Server creates will not be optimal.
If I come across any other tips (or if I remember any more :)) I will add them here.
http://blogs.msdn.com/john_daskalakis/archive/2009/05/18/9625064.aspx