Strategies for sql server performance tuning

The massive and exponential growth of database sizes requires new strategies for maintaining SQL Server performance. Therefore as data sets grow performance is maintained and also improved , but not to deteriorate. Benchmarking query response times and other key indicators serves as a building block to database performance. The strategy is heavily defined by how detailed a DBA needs to deal with performance bottlenecks. Maintaining benchmark details such as wait stats, IO,memory and CPU yield fantastic benefits. Once you establish the data gathering and methods to access the data , using this information as part of a triage can save... Read more →

Monitoring development v production environment

Should you monitor a non production environment? In large ITIL environments , the focus tends to be on the Production environment. Incident management , Problem management and the change request process are focused on Production. It makes sense. Usually there are limited resources and ensuring there is no service impact is a top priority. But , outages in non production environments can have a severe impact causing project deadline delays and cost impact. Pros of monitoring a non – production environment Developers rely on the Non-Production environment. If the environment is unavailable , developers | testers | vendor support cannot... 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 →

Notes on Performance Tuning very large databases for OLTP

Performance Tuning very large databases with OLTP presents a whole new set of problems. There are thousands of articles and tutorials on tuning databases of a generic shape & size . Here are some thoughts on approaching database servers with a high level of transactions , with minimal downtime. Stability of response times is just as important as improving the amount of queries per second. When faced with millions of queries , a smooth customer experience is critical. Pages hanging because of slow queries is newsworthy in the negative sense. Minimise variance. When there are thousands of requests per second,... Read more →

Database Tuning Advisor Performance

Question: Some developers are requesting access to Database Tuning Advisor (DTA) on a Production Server. I’m not a big fan of DTA , but I can see how developers will find it useful. The main reason why I resist using DTA is it forces DBAs into lazy thinking. I strongly believe best practises for set up , capacity planning and monitoring are the cornerstone of a solid SQL Server environment. I’d like to present some reasons to the developers to resist using DTA on Production. Can you give some real-world reasons? SQL Server Install Checklist Answer: I agree with your... Read more →