In Memory Table Indexes

  By

Now that I have written about In-Memory Tables and Migrating to In-Memory tables, let’s look at indexes and how they are created and how they work within those tables. As you can imagine indexes, called memory optimized indexes are different for these types of tables, so let’s see just how different that are from regular tables. Before we dive into this subject it is VERY important to note the biggest differences. First,  If you are running SQL Server 2014 memory optimized indexes MUST be created when the table is created or migrated. You cannot add indexes in an existing table… Continue Reading

Memory Optimizer Advisor

  By

Previously I wrote about In-Memory Optimized Tables, in this blog I am going to discuss how to determine which tables could benefit from being In-Memory by using a tool called Memory Optimization Advisor (MOA). This a is a tool built into SQL Server Management Studio (SSMS) that will inform you of which tables could benefit  using In Memory OLTP capabilities and which may have non supported features. Once identified, MOA will help you to actually migrate that table and data to be optimized. Let’s see how it works by walking through it using a table I use for demonstrations in… Continue Reading

Memory Optimized Tables in SQL Server

  By

Sometimes when I try to learn about a concept my brain blocks out everything about it. Talking about anything that uses the “In Memory” concept tends to do this to me on occasion. It’s important to note that “In Memory” is a marketing term for a series of features in SQL Server that have common behaviors but are not inherently related. So, in my next few blogs I am going to attempt to explain some In-Memory concepts as it relates to SQL Server starting with a dive into Memory Optimized Tables. I’ve already written about Columnstore which has vastly different… Continue Reading

Trace Flags That Are Now Defaulted Behaviors in SQL Server

  By

If you have ever attended one of my performance tuning sessions, you know I tend to talk about  trace flags.  Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents.  I have also included a brief commentary on each one.  As… Continue Reading

Introduction to the performance features on SQL Server on Linux

  By

I’ve been tinkering with SQL Server on Linux lately and noted a few things in regards to performance I thought I would share with you. SQL Server 2019 on Linux uses the same SQL Server database engine with many of the performance features and services you would find on Windows. There are more similarities than you would initially think. However, if you’re a Linux user who is new to SQL Server, I thought the following introduction to some performance features on SQL Server 2019 on Linux will be helpful. Columnstore index As I’ve written about before in my 3-part blog… Continue Reading

Install ALL Things SQL Server… What?

  By

Does your server look like this? Many of us have inherited a SQL Server instance that has all SQL Services installed. Someone, maybe even you, went through the SQL Server installation process using GUI and checked every option available to them, then just clicked Next, Next, Next and then Install. If this is your environment, please take a moment to evaluate and decide which of these services that are required. From a performance tuning perspective, it is important to only run the services that you need. Each of these services can consume resources on your server. Sharing resources reduces what… Continue Reading

Simple-Talk Article…Understanding Azure Storage Options

  By

Simple- Talk is technical journal and community hub from Redgate. This week I have the pleasure of being a guest Author and explain Azure storage options. I cover types of storage accounts, supported services, what performance tiers work with each storage account, and what kind of access you have with those storage types. Be sure to check it out. Here’s an excerpt…. Editor’s Intro: The choices found when provisioning storage in Azure can be overwhelming. In this article, Monica Rathbun explains the options to help your organization research which storage might be right for your solution. Understanding Azure Storage Options Azure can be… Continue Reading

I’m Speaking at Pass Summit 2019

  By ,

I am very excited to be heading to PASS Summit 2019 in Seattle next week. It’s one of my favorite weeks of the year. Getting to hang out with and learn from so many data professions is priceless. I am even more excited this year because I will be giving a Pre Con and a regular session for the first time at Summit. If you have ever attended one of my sessions, you know how passionate I am about speaking and being able to share my knowledge. What is PASS Summit? According to PASS it’s “Interactive training on the latest… Continue Reading

Invest in Yourself Stop Making Excuses

  By ,

Bob Pusateri (B|T) tweeted a quote image that really struck a chord with me and elicited a strong reaction from myself. It got me thinking I need to write a blog on this, so here we go. Many times, over the years I’ve had conversations with people in which they have said things like below. “I can’t learn anything new because my work won’t send me to training” “I can’t learn about xyz because my boss or coworkers won’t sit down with me to show me how” “I don’t have time to learn anything new” “We’ll never go to the cloud,… Continue Reading

The FAST number_rows Query Hint

  By

Query hints are always about tradeoffs—typically you are giving up something, such as flexibility, to achieve more consistency, or very specific performance characteristic. One example of this migt when returning code results, that you would want to see the data as soon as possible, even before the complete set of data has been returned. Did you know that you can return a subset of rows to look at BEFORE the entire result set is returned? The FAST n query hint allows the optimizer to return a specified number of rows as quickly as possible. Imagine an application result screen where… Continue Reading