Contrary to common perception, today’s Microsoft SQL Server is no longer an off the shelf RDBMS system that you install and run with its default settings. With version 2019 you get one of the most sophisticated and technologically advanced systems on the market comparable to the likes of Oracle.
To help the average user, we have outlined 4 areas to look at with a brief summary and we will be following up with additional articles on the specifics of each.
- Installation
- Windows Performance Monitor
- In-Memory OLTP
- TempDB
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.
To get your SQL Server environment running efficiently, it may only require a few simple changes to the use of SQL Servers advanced features. And where do you start? By simply looking at how your SQL Server is setup and documenting what you find. As you document, you will discover common mistakes and better equip you to tackle the more complex tasks your system requires.
If you would like assistance configuring any of these recommendations, or maybe you are looking to outsource your SQL database administration, Abtech can help. We have an experienced SQL team and are the go to resource for many of our customers. We don’t just service the database, we can also look after your network, Windows systems, VMware, Linux and your Exchange environment to name a few.
For more information, please call us on 1-800-474-7397 or email: info@abtechtechnologies.com
