How To Optimize Your SQL Server Performance Terry Webb Wringing optimal performance from your SQL server is not always a straightforward process, and you may not even know what is the cause of a particular problem without doing some serious digging. Here are some tips on how to avoid suboptimal performance rearing its head in the first place. FRAGMENTATION CAN BE CAUSED BY DATABASE SHRINKING Deliberately choosing to shrink your database in order to make full use of the space you have available might seem like a good idea on paper, but there is hard evidence that this can be the enemy of optimal performance because of the way it creates a cycle of fragmentation. The answer is to avoid the temptation to shrink databases where possible, since this can actually create more problems when the index is rebuilt because of the way that SQL server functions. QUERY OPTIMIZATION IS ESSENTIAL The more work your database has to do to get you the desired information, the greater the performance overhead will be. As such it is very inefficient to rely on general queries that are designed to draw down a variety of data points when in reality the user or application needs only a specific subset of this larger group. Thankfully with SQL server performance monitoring it’s possible to review pertinent statistics and identify issues quickly. In short, it makes sense to be more precise when working out which fields should accompany a given query statement, such as SELECT. Taking a broad ‘select all’ approach will obviously put unnecessary strain on the server, whereas if you have chosen pertinent fields you will only call up this information from the table, reducing the impact on hardware resources. A similar strategy will work in the context of attempting to ensure that no duplicates are delivered by a particular query. The more fields featured, the more optimal the performance will be. CHECK WAIT STATISTICS TO FIND SOLUTIONS Wait statistics are generated in the event that an SQL server hits some kind of roadblock in the course of normal operation. This information can be very useful in letting you know exactly what caused the obstacle, whether it might be some kind of hardware-related bottleneck, or an indexing issue. Knowing that wait statistics are available is only half the battle; you actually have to remember to take advantage of them when you are troubleshooting server performance problems. The longer something takes to fix, the more it costs the company, so even small tweaks can result in big savings and also productivity increases. INCREASE AVAILABLE MEMORY You can obviously upgrade to more powerful hardware if you are having persistent performance troubles with your SQL server, but this is not always viable for a variety of reasons. You can actually make your existing hardware go further if you check to see whether the full amount of memory is being leveraged. There are many different memory configuration options, and it is worth noting that you should not set the available value to the very upper limit as this will compromise performance by not leaving enough headroom for the OS and other solutions to operate alongside the database. Furthermore you may be more comfortable leaving the default setting on memory usage, or allow for dynamic adjustment if this makes more sense. Even so, increasing the allowable allocation could net you performance gains. Other hardware changes can boost performance as well, such as switching to solid state storage or upgrading the CPU. However, it is generally better to begin your optimization attempts by seeking out software issues that can be addressed without needing any complex and potentially costly work. Source: TopTal