This month’s SQL Server Pro magazine sets out to solve your biggest SQL Server performance problem. Here’s a detailed look at the February 2013 issue.

SQL Server Pro is an online subscription-based monthly magazine that has news and articles from across the SQL Server world. The magazine is part of a wider SQL Server website found at www.sqlmag.com. The items in bold below correspond to the name of the article in the current issue.

This month’s cover story provides details of Using Solid State Disks in SQL Server Storage Solutions. For most databases, the limiting factor in performance is physical disk IO. This can be illustrated by the fact that memory access is measured in nanoseconds, whereas disk access is measured in milliseconds (one nanosecond is equal to one million milliseconds). In general terms, physical disks are relatively slow, Solid State Disks (SSDs) are typically much faster than the traditional DAS or SAN disk subsystems, and with their falling prices are increasingly seen as a performance boosting option.

As a personal example, I recently tested physically reading every page in a database (all heaps, clustered and non-clustered indexes) on both a SAN and on a SSD, the SSD was more than 12 times faster than the SAN (134 minutes versus 1821 minutes)

Denny Cherry discusses the optimal placement of SSDs (top tiered storage, cache, then traditional disks), and which databases should be selected first (tempdb and the transaction log). Denny then discusses some problems (and solutions) that faster disk access can cause, together with the more common local SSDs configuration options.

Installing SSDs will probably fix your primary performance problem, however be aware it is still possible to write bad code that will slow your queries.

Itzik Ben-Gan provides a detailed example of using CTEs with Multiple Recursive Members. CTE have many uses, included recursive querying. A typical recursive example will show how a manager/employee table can be queried recursively to find each person’s manager. This, like most other examples, is an example of using a single recursive member. Itzik uses a family tree (of Bilbo Baggins) to illustrate how multiple recursive members can be used successfully. This is a very interesting article with some great supporting code.

Tyler Chessman has a practical article on Monitoring SQL Server and SharePoint BI components. The article is a detailed look at how to build a performance monitoring solution, and nicely follows-on from last month’s related article by Rodney Landrum (Consolidate Data on Executed SSRS Reports for Easy Querying). Specifically, the article discusses how to monitor the usage of:

• SQL Server Reporting Services and Power View
• PowerPivot for Sharepoint
• PerformancePoint Services and Excel Services

Tyler’s ultimate aim is to provide a unified repository for monitoring and reporting on SQL Server and SharePoint BI components, this is certainly a big step in that direction.

A detailed commentary concerning The Trouble with Type Tables is given by Vic Newell. Type tables basically hold domain values for foreign key columns. The author discusses the advantages of these structures, including self documentation and preventing bad data entering your databases.

The editorial discusses Clearing the Big Hurdles to Big Data. It’s a brief introduction to Big Data, which is characterized by the 3Vs, Velocity, Variety, and Volume. It seems everywhere I read Big Data is the next big thing that will change the world, however I suspect it will eventually settle, find its niche, and happily co-exist with the relational model.

This month’s SQL Server Questions Answered relate to SUSPECT and RECOVERY_PENDING states, Shrinking Transaction Log Files, and Why DROP TABLE and TRUNCATE Operations Complete So Quickly. As always, the answers are both interesting and detailed.

A detailed product review of ScaleArc iDB 2.0 is provided. This is basically a scalable caching solution that sits between your database and application. You create caching rules so data is pulled from the cache instead of hitting the database, thus improving performance. It includes features for scalability, high availability, redundancy, firewall, and more.

The Industry Bytes section has Michael K. Campbell making the case for not installing Antivirus solutions on your SQL Server boxes. While it is informative, I’m not convinced, it’s easy for someone to make an error of judgment, even database professionals. Since there is a weight of regulatory compliance which stipulates the use of antivirus software, Michael then proceeds to show how they can/should co-exist.

The New Products section briefly highlight’s Idera’s PowerShell Plus being made available for free, a new version of NetWrix’s SQL Server Change Reporter, and Attunity’s release of Managed File Transfer for Hadoop.

The last article is also my favorite this month. It’s FAQs about Hekaton Query Acceleration Technology. Hekaton is Microsoft’s upcoming in-memory technology for SQL Server, which will move your hot tables into memory and compile your T-SQL into machine code. Both these changes will improve the performance of your queries, initial testing suggest by making them 5 to 50 times faster. You get the benefits for free, without making any changes to your hardware or code. It’s due to ship in the next major SQL Server release. The future looks interesting...