Performance | Convergence of Data and Infrastructure

SQL Server Large Memory Pages

May 062019

SQL Server Enterprise Edition can leverage large memory pages to reduce the amount of memory pointers required for larger SQL Server deployments. Reducing the number of pointers makes the database engine more efficient, especially for SQL Servers with greater than 32GB of RAM. A normal memory block is 4KB, and many thousands of pointers are required to manage the memory underneath a larger SQL Server. Large memory pages can change the block size to 2MB, greatly reducing the number of pointers required for memory management.

Leverage SQL Server trace flag 834 to enable this setting and restart the instance to validate.

Note: Large memory pages requires the use of lock pages in memory for the service account before large memory pages are enabled.

For maximum performance of SQL Server in a virtual machine, the use of large memory pages is highly recommended, as it reduces the number of translation lookaside buffer (TLB) misses, which will improve the performance of the SQL Server. Large memory pages are fully supported within all modern hypervisors.

Note: Do not use large memory pages if columnstore indexing is in use in any SQL Server database on this instance.

Note: Be careful if the VM is also under memory pressure, as if the operating system is squeezed too hard, SQL Server will not return memory to the OS and system instability can occur.

To enable large memory pages, add trace flag 834 to a startup parameter for the SQL Server service through the SQL Server Configuration Manager. Restart the instance once this trace flag is added.

To validate that large memory pages are enabled, execute the following query.

You can check the error log for entries related to large memory pages on instance startup.

If the results show large memory page allocations greater than zero, large memory pages are enabled.

Now, validate your system performance before and after! Let me know the results!

Discount Code for SQL Saturday Atlanta 2019 Precon

Cloud, Events, Performance, Professional Development, SQL Server, Storage, Technology, Virtualization No Responses »

Apr 302019

We have a discount code for last-minute attendees looking to go to our exciting preconference training session at this year’s SQL Saturday Atlanta on May 17th, 2019, called “Level Up Your Data Platform Cloud Infrastructure“! The discount code is PAPERJAM, and enter it on the EventBrite signup page. If you’re even remotely thinking of the cloud for any of your servers, this precon is for you!

Abstract: While your on-premises infrastructure is likely maintained by another team, in the cloud this responsibility falls on you. Whether your organization is just planning for the cloud, all-in, or leveraging a hybrid architecture, this course is designed to ramp you up on maximizing your investment in the cloud components that you utilize.

Infrastructure skills are essential when managing your environment in the cloud. Knowledge is power; key items such as firewalls and connectivity, storage presentation and performance, baselining, and even platform selection are all necessary areas of concern. Capacity management is also crucial, as the old ways of “throwing hardware” at a performance problem will translate into a larger bill each month.

Attend this session to learn how to efficiently manage cloud resources, set yourself apart from your peers, improve the performance of your mission-critical workloads, and potentially save your employer money!

Register for this exciting preconference training session today! If you’re even remotely interested in the cloud, this session is for you!

PASS Summit Cosmos DB Demo Code

Cloud, Events, Performance, Personal, Professional Development, SQL Server, Technology, User Groups No Responses »

Nov 092018

As part of the PASS Summit conference session that Bob Pusateri and I are presenting right now called “Cosmos DB for SQL Server Pros“, we are demonstrating a number of Azure Cosmos DB features that we feel strongly about helping you leverage through code. As a result, all of the code that we’re demonstrating today is now available for you to download and explore.

This code demonstrates how to collect Windows Perfmon metrics with C#, package them up into a Cosmos DB document, insert the document into Cosmos DB, and then fetch and query individual and multiple documents based on a SQL-like interface demonstration.

As always, there’s no warranty with this code, please don’t distribute as your own, yada yada yada. I’m not the best programmer, so if anyone has improvements, I’d love to have you send me your updates to check out!

PASS Summit 2018 Activities

Best Practices, Business, Cloud, Events, Performance, Professional Development, SQL Server, Storage, Technology, Virtualization No Responses »

Nov 062018

What a whirlwind year this has been! I’m thrilled to invite you to my sessions at this year’s PASS Summit, happening this week in Seattle WA.

Tuesday 11:30am to 1:00pm I’m part of the cloud availability panel over lunch at the SentryOne Data Day precon at the Summit. I hope you already registered for this exciting opportunity to learn more about performance and availability-related topics in this whole-day discussion.

Next, for those of you that still have your mission-critical workloads in your on-premises datacenter (or even for those who have them in the cloud, because almost all of the topics directly apply!), come to my session Wednesday morning at 10:45am entitled “On-Prem SQL Servers, Interstellar Performance“. This deep-dive on virtual SQL Server performance will help you squeeze the most performance from your virtualized SQL Servers, and will help you communicate with your infrastructure team on things that matter the most to performance-oriented SQL Servers. You’ll be sure to have quite a bit of real-world tips and tricks to take back to the office as soon as you return from the conference.

Thursday morning I’m part of the SIOS PASS the Bacon breakfast panel, where we’ll get a good chuckle while we discuss a number of topics pertinent to today’s DBA, such as availability both on-prem and in the cloud, disaster mitigation and preparation, and other availability topics. Come register here, get a great breakfast on the house, and learn while you laugh!

Next, Bob Pusateri and I have a session Thursday afternoon called “SELECT STARS: A SQL DBA’s Introduction to Azure Cosmos DB”, where we discuss and present Microsoft’s new Cosmos DB offering on the Azure platform. This platform is a completely different model of databases than what most SQL Server professionals are accustomed to, and we’ll give you the details you need to expand your horizons and learn about this exciting platform. This session is more of the ‘what’ session to get you amped to learn more about the platform on Friday.

Friday my final session of the conference is entitled “Cosmos DB for SQL Server Pros”, designed to give you more of the ‘how’ when continuing your exploration of Cosmos DB. Bob Pusateri and I will share more of the use cases, scalability details, and live code demos of how to access and alter data in a Cosmos DB database.

I hope to see you at the show! If you want to sync up and talk geek, make sure to ping me to set up some time over your favorite form of caffeine.

SQL Saturday Lincoln Precon this Friday Oct 26

Best Practices, Cloud, Events, Performance, Professional Development, SQL Server No Responses »

Oct 232018

I’m presenting an exciting preconference training session on Friday, October 26, ahead of the SQL Saturday in Lincoln, Nebraska. This all-day training class, entitled “Level Up Your Cloud Infrastructure Skills“, is designed to help you, the SQL Server professional, ramp up your knowledge so you know how to better manage workloads in the cloud.

Think infrastructure in the cloud is still just for sysadmins? Think again! As your organization moves into the cloud, infrastructure skills are more important than ever for DBAs to master. Expert knowledge of cloud-related infrastructure will help you maintain performance and availability for databases in the cloud. For example, know what an IOP is? How many does your database consume during a given day? Properly sizing a cloud database depends on your knowledge of this metric. Failure to properly configure storage performance at the time of deployment will slow down your SQL Server considerably. Come learn many of the key cloud infrastructure points that you should master as the DBA role continues to evolve in this all-day boot camp on ramping up your knowledge on cloud management!

RSVP for this exciting preconference training session here for just $135! Seats are limited, so register soon!

VMworld 2018 SQL Server Sessions

Best Practices, Cloud, Events, Performance, Professional Development, SQL Server, Storage, Technology, Virtualization No Responses »

Jul 242018

I hope to see you all at this year’s VMworld 2018 USA conference where I’m lucky enough to have been selected to present four SQL Server-themed sessions!

The first is an all-day boot camp (VAP3768WU) with Oleg Ulyanov from VMware Corp. where we take participants through a comprehensive journey of understanding how to manage, performance tuning, and business continuity planning for high performance SQL Servers on the VMware virtualization platform. It’s the Sunday before VMworld officially ramps up, and if you have infrastructure engineers attending this conference, please tell them to register for this boot camp.

Next, Thomas LaRock from SolarWinds (@sqlrockstar) and I will be presenting a session called “Performance Deep Dive for Demanding Virtual Database Servers” (VAP1425BU). We’ll be discussing advanced performance-related topics such as CPU scheduling and vNUMA, “right-sizing” for both performance and licensing, virtual disks and performance discrepancies, and general operational efficiency.

Next, Michael Corey from LicenseFortress (@michael_corey) and I will be presenting a session called “Database Virtualization (Monster VMs) with VMware vSphere: Doing IT Right” (VAP1296BU), which is an updated version of our continued track of performance-related tips and tricks. Come see us laugh our way through over a hundred slides packed full of things you can take back to your environments!

Finally, I have a solo session called “Database vDisks and You” (VMTN5520B), where I’ll talk about all of the various layers of queueing between the SQL Server database and the enterprise SAN, and how to measure and monitor these layers, plus discuss ways to performance tune each layer.

As always, it’s going to be a great show! Ping me if you want to meet up in person while at the show, and let’s geek out and talk shop! Register for this conference here, and make sure to enroll in the SQL Server on VMware boot camp!

Older Entries