Tuning 3rd-Party Applications

 

BLOG ENTRY – Tuning 3^rd-party software products

 

The consensus amongst CIO-intelligentsia states that you shouldn’t buy a software tool that provides less than 95% of what you need – it’s cheaper to build.

To answer this, many 3^rd-party products provide 500% of what you need, knowing that you’ll use the components of your application that you need and leave what you don’t use.

The problem with this is that with thousands of companies and tens- to hundreds of thousands of users, the product is used so many ways that it’s difficult for the vendor to tune for your environment. So, you have to tune your own databases in a way that does not change the logic of the application.

The way to do this is with SQL Server-based applications is to use a standard, “Black box” approach:

1. Monitor the database environment (While we like SolarWinds DPA & SQL Sentry, there are many others on the market)
2. Validate physical resources: CPU, IO, Network, etc.
1. Do NOT make any hardware changes in the first iteration; this is your baseline. After that, you might consider more CPU or faster storage
3. Use the database engine tuning advisor (DETA) to identify missing indexes (the easiest way to improve performance without changing the code or logic, which is likely to void a software warranty agreement)
1. The easiest way to do this is with the missing index script in my BLOG entry, mssqlperformance.blogspot.com “Missing indexes” – make sure you read the notes about duplicate indexes
2. While it is extraordinarily unlikely that adding an index will negatively affect performance (as opposed to making it much faster), some 3^rd-party software providers don’t ‘have a sense of humor about it. We recommend a frank conversation with their support team.
3. Before adding the indexes, create a script to roll the indexes back out of the database. While I haven’t needed to do this in well over a decade, better safe than sorry.
4. Repeat