Configurations eating up sql server performance

Question: I’m setting up a best practises document for our SQL Server builds and would like to focus on some configurations which fit in with sql server tuning best practises, What are some of the SQL Server configurations you emphasise when setting up a server? Answer: Firstly – I like this approach of focusing on the configurations at the SQL Server Install phase. This is an important aspect of SQL Server performance tuning. Research should be completed looking at a combination of industry standards, capacity planning , and application specific requirements. A large chunk of problems can be stopped by... Read more →

Guiding principles of wait time analysis

Question: Could you send me some information about the guiding principles of wait time analysis? Answer: Wait time analysis for SQL Server performance tuning is a methodology covering a wide range of problem sets. But at its core are some guiding principles. 1) Identify what the impact is on the end user 2) The main focus is on aiming to to make a positive impact on the application end user. This is in contrast to aiming to deliver a better cache hit ratio or cpu usage etc. This doesn’t mean that cache hit ratio and cpu usage are not important... Read more →

Will placing a large data column as an index INCLUDE impact sql query performance

Question: I’m thinking of placing a large data column as an INCLUDE into an index , a nvarchar(4000). Will it have a negative impact on SQL Server performance? Basically, is it a good idea? I’ve read it’s not a good idea. Answer: I can’t advise you to never implement such an action, but in general I’d avoid placing such a large data column as an INCLUDE of an index. Some of the well documented benefits of including non key columns in the INCLUDE statement is data types not allowed as key columns are allowed and the INCLUCDE columns are disregarded... Read more →

Query to find non – sargable sql statements – SQL Antipattern #009

When a predicate is SARGABLE we mean the sql statement is able to exploit the indexes supporting the sql statement. If the sql statemet is non – SARGABLE – we mean the statement is not capable of exploiting the indexes If a statement is non-SARGABLE it can return the correct data set – as intended by the developer – but the response rate can be slower than expected. As the statement cannot exploit the index – it’s forced into a full scan. For the smaller data sets this may not appear to be a problem, but as the data sets... Read more →

Show me the wait stats

Using wait stats to identify pressure points on your SQL Server is a common method for troubleshooting. Unfortunately , it is very easy to jump to conclusions about problems on the basis of the highest wait stats. My general approach is to use wait stats , but letting them guide me to extra analysis. There could be all sorts of reasons why a wait stat or a combination of wait stats appear . The more you use wait stats , you develop methods for troubleshooting. Interpreting the initial findings will help you start to ask the right question. These are... Read more →