Every day, more IT organizations decide to move their SQL Server databases to Azure. In fact, over a million on-premises SQL Server databases have been moved to Azure. There’s an interesting blog about how Microsoft is faster and cheaper than its competitors that’s worth a read. To assist with your move to Azure Microsoft offers a number of migration tools and services to make this move as smooth as possible which I think attributes to their success. Two of those options are below with some informational links. If you’re migrating a number of large SQL Server instances, Azure Database Migration Service is the best way to migrate databases to Azure…
-
-
In celebration of Women’s History Month starting next week, I was asked to write this blog about my experiences as a woman in the SQL Community, and it really got me thinking. At first, I thought I should be very politically correct and only talk about the great stuff and hype up all wonderful experiences I’ve had but then I thought that wouldn’t give a true picture. So, I am writing this as openly and honestly as I can. Here it goes. The Ugly I am going to start with the negatives to get those out of the way and draw attention to things that are still happening not only…
-
I am very excited and fortunate to be chosen to speak at both SQLBits and DataGrillen this year. These two conferences are incredible community run events and if you can attend either of them, I would highly recommend it. Here’s a little bit about each event and information on how you can register. If you can attend be sure to check out my sessions. SQLBits SQLBits the largest SQL Server conference in Europe, being held in London England March 31st– April 4th. It is a conference for leading data professionals with over 200 sessions from speakers all over the world. https://sqlbits.com/ Join Me -Thursday April 2nd 17:10 Room 10 Always…
-
Did you know that a native STRING_SPLIT function built into SQL Server was added into SQL Server 2016? As a consultant I see so much code that call out to a scalar function that are used to split out string delimited variables into a usable list. For those that use this method I suggest you look at this function. STRING_SPLIT is a table valued function that returns a single column of your string values split out by the delimiter. This is an unusual bit of T-SQL, in that compatibility level 130 or higher is required for its use (Microsoft didn’t want to induce breaking changes into existing user code). Using…
-
We know that sorting can be one of the most expensive things in an execution plan as shown below. However, we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance. Many of us writing procedures tend to write in code blocks. We write the SELECT, JOINS, FROMs and WHERES then immediately follow it up with and ORDER BY as…
-
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 without dropping and recreating the table. After 2016 you now…
-
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 AdventureWorks2016CTP3. Since this is a smaller table and doesn’t incur…
-
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 use cases to In Memory OLTP, and you can find…
-
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 with any change, you should be sure to thoroughly test…
-
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 series that you can find here, a columnstore index allows…
