Installation

First, are you using the right version of the SQL Server on the right platform and is it installed correctly? There are different versions of SQL Server and you may be either paying for features you are not using or not using features, in different versions, that may enhance or alleviate issues you’re currently experiencing. Also, SQL Server is not Microsoft Windows server bound, it can be installed other platforms like Linux.

Is the SQL Server installed properly following Microsoft’s best practices?

For instance, are your datafiles all installed in the default locations in the “C” drive and is the correct transactional logging methods being utilized for maximum performance and protection? With a simple audit of the SQL Server environment, you will be able to answer these questions and discover any additional problems that should be addressed. In most cases, the solutions to basic installation mistakes are easily fixed, but you need to know what they are before they can be fixed.

Windows Performance Monitor

Whether your underlying system is a physical server or virtual, do you know if it has the necessary resources to support the demands of the databases running on the SQL Server instance? This is easily done by using a tool most of us are familiar with and that’s Windows Performance Monitor.

At it’s most basic, you can quickly monitor if there is a bottleneck with memory, cpu or disk; which will tell us if more physical resources need to be added. But the Performance Monitor can also be used to perform more advanced SQL Server monitoring by using specialized SQL related counters. These specialized performance counters can help us fine tune the database environment, discover unaware problems or more importantly, help troubleshoot existing performance problems.

To start, we recommend everyone to get familiar with the Performance Monitor tool and the SQL related counters. There is very little risk in using these counters and ,with a bit of research you will be on your way to learning exactly how the SQL environment is running.

In-Memory OLTP

In-Memory OLTP are specific database structures that are stored in memory rather than disk. They offer in-memory optimized tables and indexes, temp tables in memory and natively compiled stored procedures.

As we all know data stored in memory offer a significant improvement in performance and can elevate a system that is experiencing high contention. While implementation of In-Memory OLTP is not a simple task, and requires the necessary physical resources to support its function, it is a feature that should be researched as it’s benefits can be very pronounced.

TempDB

Better management of the tempDB can provide a simple route to performance improvement. In the SQL Server world, tempDB contention has always been a common performance problem. By following best practice recommendations for setting up tempDB some of the issues can be alleviated but never completely removed.

Here are some common things to do in configuring the tempDB:

• create multiple datafiles with the same size and same auto-growth settings and make sure trace flag 1118 and possibly 1117 is enabled.
• In addition, as mentioned above, utilizing In-memory technologies of SQL Server can help to greatly alleviate the resource contention and allow more flexibility in managing the tempDB.
• For every active and transaction heavy systems, it is highly recommended to implement the tempDB in-memory.