How to use Database Compatibility to increase your migration success

Understanding SQL Server database compatibility levels helps in understanding risk in upgrades Read more →

Most important SQL Server wait types

Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types. For example , you may see the sql wait type WRITELOG as high on the list of waits on a SQL Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems. Based on monitoring and analysing various SQL Server performance... Read more →

UPDATE STATISTICS GETTING SLOWER

Question: The Statistics tasks in the SQL Server Maintenance plan is getting slower? When I first implemented the task it used to take 1 hr – recently it’s built up 4-5 hrs. I’ve checked underlying resource issues – and everything looks OK. Could you advise on how I can progress with troubleshooting. I’ve included a screeshot of Update Statistics Task. Answer: It is not unusual to find UPDATE STATISTICS getting slower over time. Looking at the way you have this configured All existing statistics – this includes all index and table statistics Sample size of 25 % It is very... Read more →

Type Conversion may affect cardinality estimate in query plan

Type Conversion may affect cardinality estimate in query plan is a warning found on Execution Plans. This may negatively impact the correct index usage. Cardinality is a critical part of how indexes are used. Data type matching has many benefits. It’s a practise often neglected in database design but which can have a number of long term consequences for query access and database performance One long term cost of neglecting data type matching is it can lead to more memory required. When data types are matched , this leads to less bytes requires , leading to less data pages in... Read more →

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 →