Category Archives: Hardware

SQL Server Hardware

From Red-Gate website you can download (for free) the ebook SQL Server Hardware written by Glenn Berry (@GlennAlanBerry).

This ebook will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS. 

The full editorial review is available here.

This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.

Amazingly Useful SQL Server Articles on SQLPerformance.com

Ever wonder which specific T-SQL pattern is best for splitting strings? There are lots of ways to calculate running totals, but which is optimal? What CPUs are best for SQL Server 2012? For SQL Server 2014? Find out more in this post from Kevin Kline….(read more)

Join Me on March 14 in Silicon Valley for a Full-Day of SQL Server Configuration Tuning Lessons

The secret ingredient in this full day seminar is using the benchmarking and load testing tools to visually see the performance impact of the various configuration choices that Kevin illustrates throughout the course….(read more)

I Touched a Screen (and I Liked it)

This is a picture of my son, Stevie Ray, learning SQL on my Surface Pro 2. I bought the Surface Pro 2 the day it was released. I didn’t pre-order it because I wanted to read what others had to say about it first. I’m an early-adopter, but not the earliest of adopters. I did not like it at first. “Why not, Andy?” I’m glad you asked. The form factor was the culprit. That, combined with the touch keyboard, nearly did me in. I took the advice of some friends with Surface 1’s and bought the type keyboard….(read more)

Hardware Considerations for In-Memory OLTP in SQL Server 2014

In-Memory OLTP uses memory and disk in different ways than traditional disk-based tables. The  performance improvement you will see with In-Memory OLTP depends the hardware you use. In this blog post we discuss a number of general hardware considerations, and provide generic guidelines for hardware to use with In-Memory OLTP.

CPU

In-Memory OLTP does not require a high-end server to support a high-throughput OLTP workload. We recommend using a mid-range server with 2 CPU sockets. Due to the increased throughput enabled by In-Memory OLTP, 2 sockets are likely going to be enough for your business needs.

We recommend to turn hyper-threading ON with in-memory OLTP. With some OLTP workloads we have seen performance gains of up to 40% when using hyper-threading.

Memory

All memory-optimized tables reside fully in memory. Therefore, you must have enough physical memory for the tables themselves and to sustain the workload running against the database – how much memory you actually need really depends on the workload, but as a starting point you will probably want enough available memory for about 2X the data size. You will also need enough memory for the buffer pool in case the workload also operates on traditional disk-based tables.

To determine how much memory a given memory-optimized table uses, run the following query:

select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats

The results will show the memory used for memory-optimized tables and their indexes. The table data includes the user data, as well as all the older row versions that are still required by running transactions or have not yet been cleaned up by the system. The memory used by hash indexes is constant, and does not depend on the number of rows in the table.

It is important to keep in mind when you use in-memory OLTP that your whole database does not need to fit in memory. You can have a multi-Terabyte database and still benefit from in-memory OLTP, as long as the size of your hot data (i.e., the memory-optimized tables) does not exceed 256GB. The maximum number of checkpoint data files SQL Server can manage for a single database is 4000, with each file being 128MB. Although this would give a theoretical maximum of 512GB, in order to guarantee that SQL Server can keep up with merging checkpoint files and not hit the limit of 4000 files, we support up to 256GB. Note that this limit applies only the memory-optimized tables; there is no such size limitation on the traditional disk-based tables in the same SQL Server database.

Non-durable memory-optimized tables (NDTs), i.e., memory-optimized tables with DURABILITY=SCHEMA_ONLY are not persisted on disk. Although NDTs are not limited by the number of checkpoint files, only 256GB is supported. The considerations for log and data drives in the remainder of this post do not apply to non-durable tables, as the data exists only in memory.

Log drive

Log records pertaining to memory-optimized tables are written to the database transaction log, along with the other SQL Server log records.

It is always important to put the log file on a drive that has low latency, such that transactions do not need to wait too long, and to prevent contention on log IO. Your system will run as fast as your slowest component (Amdahl’s law). You need to ensure that, when running In-Memory OLTP, your log IO device does not become a bottleneck. We recommend using a storage device with low latency, at least SSD.

Note that memory-optimized tables use less log bandwidth than disk-based tables, as they do not log index operations and do not log UNDO records. This can help to relieve log IO contention.

Data drive

Persistence of memory-optimized tables using checkpoint files uses streaming IO. Therefore, these files do not need a drive with low latency or fast random IO. Instead, the main factor for these drives is the speed of sequential IO and bandwidth of the host bus adapter (HBA). Thus, you don’t need SSDs for checkpoint files; you can place them on high performance spindles (e.g., SAS), as long as their sequential IO speed meets your requirements.

The biggest factor in determining the speed requirement is your RTO [Recovery Time Objective] on server restart. During database recovery, all data in the memory-optimized tables needs to be read from disk, into memory. Database recovery happens at the sequential read speed of your IO subsystem; disk is the bottleneck.

To meet strict RTO requirements we recommend to spread the checkpoint files over multiple disks, by adding multiple containers to the MEMORY_OPTIMIZED_DATA filegroup. SQL Server supports parallel load of checkpoint files from multiple drives – recovery happens at the aggregate speed of the drives.

In terms of disk capacity, we recommend to have 2-3X the size of the memory-optimized tables available.

Want to learn even more about In-Memory OLTP? Download SQL Server 2014 CTP1 and get started today or see more blogs in the series introduction and index here!

 

Hardware 201: Selecting and Sizing Database Hardware

On July 25, I had the opportunity to present Hardware 201 for the PASS Performance Virtual Chapter. You can download my deck here.

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

Attendance was quite good for a hardware-related session, and there were a lot of interesting questions after the presentation.

Just in case this session whetted your appetite for a much deeper level of knowledge about SQL Server Hardware, you should consider attending the upcoming two-day IEHW: Immersion Event on SQL Server Hardware course that I will be teaching in Bellevue, WA on September 18-19, 2013.

Filed under: Computer Hardware, Intel, Processors, SQL Server 2012, Teaching Tagged: Hardware, OLTP

Database Trends & Applications column: Database Benchmarking from A to Z

Have you heard of the monthly print and web magazine Database Trends & Applications (DBTA)? Did you know I’m the regular columnist covering SQL Server ? For the past six months, I’ve been writing a series of articles about database benchmarking culminating in the latest article discussing my three favorite database benchmarking tools: the free, open-source HammerDB, the native SQL Server Distributed Replay Utility, and the commercial Benchmark Factory from Dell / Quest Software. Wondering what…(read more)

Storage Performance

Storage has changed dramatically over the last three years driven by SSD developments. Most of the key components necessary for a powerful storage system are available and the cost is highly favorable for direct placement of data files. Some additional infrastructure elements could greatly enhance the flexibility of storage systems with SSDs. There is still some discussion on whether SSD should interface directly to PCI-E or continue using the SAS/SATA interfaces originally designed for hard disks….(read more)

Hardware 301 Presentation

I had the opportunity to present the latest version of Hardware 301: Diving Deeper into Database Server Hardware two times in one day yesterday! First was a virtual presentation for the PASS Virtual DBA chapter at 12PM, and then I got to do it again at the Rocky Mountain Oracle User’s Group Training Days 2013 at 4PM.

The RMOUG event was interesting, since I had never been to an Oracle conference before. It felt a little strange, since I did not know anyone (and no one knew me). 

I was also surprised that there was a noticeably higher percentage of women in attendance than you normally see at most technical conferences. Typically, you might see only roughly 1-2% of the attendees at most computer-related conferences being female (which is very unfortunate). Just from my casual observation yesterday, it seemed like the female percentage was at least double or triple that (which is still extremely low).

I am not sure what can really be done to change those numbers, besides continuing to encourage and mentor women who want to work in I.T.  I think there are a lot of societal factors that discourage many young women from pursuing I.T. careers.

Filed under: Computer Hardware, Processors, Sandy Bridge, SQL Server 2012 Tagged: Hardware

Speaking at PASS Database Administration Virtual Chapter

I will be speaking at the PASS Database Administration Virtual Chapter on February 13, 2013 at 12PM MST. I will be presenting the latest version of my Hardware 301: Diving Deeper into Database Hardware deck.

The LiveMeeting link is here. If you want a chance to win a $50.00 Amazon gift certificate, you need to register here.

Here is the abstract for the presentation:

Hardware 301: Diving Deeper into Database Hardware

Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

I really enjoy talking about SQL Server hardware, so this should be a lot of fun. I hope you are able to take the time to listen to the presentation!

Filed under: Computer Hardware, PASS, Processors, Storage Subsystems Tagged: Hardware