As discussed in part 1 of this series, the architecture of SQL Server R Services is designed to protect SQL data while R scripts are being run. Part 2 of the series reviewed security and scalability. With the plethora of moving parts, it’s no surprise that effectively using Microsoft SQL Server R Services can be complicated. To successfully run queries and obtain usable data, follow these best practices:
1. Pay attention to how the system’s privileges are configured. The default privileges are intended to work well, so if you implement changes users may find it difficult to gain access to the SQL Server.
2. Implement processes for cleansing your data prior to running queries. If you run R scripts on your data without first cleansing the data, you risk producing flawed, potentially useless data. Note that Microsoft SQL Server R Services does not include built-in data cleansing, although there are ways to implement algorithms for data cleansing.
3. Before running queries, develop an understanding of statistics. Since most programmers and DBAs lack extensive knowledge of advanced statistics, the complexities of the R language often presents a roadblock. By partnering with a data scientist, DBAs can gain the expertise necessary to make sense of R.
4. Use machines with the core memory and disk speed you need to perform R scripts efficiently. Microsoft recommends a minimum of 32 Gb. For analyzing particularly large data sets, use a 64-bit machine with high RAM.
5. Utilize the high performance power option for best R SQL performance. Although this option consumes a lot of energy, it’s necessary for completing R scripts at optimal speed.
6. Make sure the memory server of your SQL server is appropriately configured and balanced. Microsoft offers these tips on server memory configuration.
7. Launchpad needs specific privileges to operate; ensure Launchpad has access to them. Also make sure SQLRUserGroup (a default username) has local login rights. You will need to log in as SQLRUserGroup to remove ODBC execution and perform other critical functions.
8. Do not stop and start the SQL Server Service, but restart when necessary. Launchpad is dependent on the continued operation of SQL Server Service.
9. Enable 8dot3 notation on your server for full functionality.
10. Remember that the basics of SQL Server Query and index design still apply when using SQL Server R Services. Design your scripts and queries to work with your existing database infrastructure rather than against it.
11. R scripts will often require fine-tuning to be fully functional. Expect that you will be spending time tinkering with them. If you’re stuck, it’s helpful to take advantage of the R language community. Useful resources include stackoverflow, SQL Server According to Bob, and specialized LinkedIn groups devoted to R.
12. Monitor your external resources pool. The allocation of resources plays a major role in overall system performance. Resources to monitor include DMVs, Perfmon counters, extensibility LOGS and R Services LOGS, and procexp and procmon from sysinternals.
Learning SQL Server R Services is an ongoing project for most programmers, but the unique advantages provided by the R language are worth the extra work.
If your organization needs help implementing R Services on your SQL services, contact Ntirety. Our team of SQL experts can walk you through the process of implementing R SQL.