Tag: SQL Server

Sorting in Stored Procedures – Food for Thought

  By

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… Continue Reading

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

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

SQL Server Statistics Health Reminder

  By

I’ve written about statistics in SQL Server a few times now. Through conversations  I am reminded that not everyone keeps their table statistics up to date. Keeping your statistics up to date is one of the more critical components of SQL Server performance issues. Please be sure you are doing more than just index maintenance to maintain your statistics.  Index maintenance  only maintains statistics created by indexes and single field predicate created table statistics. There are also column statistics that need to be maintained that are created by predicates usage in your queries or even manually created. For each insert,… Continue Reading

Quick SSMS Tip

  By ,

It is widely known that I am a horrible speller and hate to type code. I tend to use a lot of code snippets and reuse code to avoid writing it. So, when I find little tips that make coding easier for me, I like to share. Did you know that you can surround your code with a click of a button with IF, BEGIN END, WHILE code blocks? SQL Server Management Studio (SSMS) gives us the basic structure of a Transact-SQL statement code block as a starting point. Take Look In SSMS In a query window, Right Click and… Continue Reading

What SQL Server Performance & Management Tools Do I Use?

  By

Several times I’ve been asked what tools are in my performance tuning and SQL Server management arsenal, so I decided to just create this blog to list them out for you. Keep in mind, these are ones I personally use. There are many more out there that the community uses. Feel free to comment with any you may use that is not on my list. Community Tools (Free)   Ola Hallengren’s Maintenance Scripts- Index Maintenance, Integrity and Backup Scripts https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html   Glenn Berry’s Diagnostic Scripts -Various scripts based on DMVs to extract SQL Performance statistics and information  https://www.sqlskills.com/blogs/glenn/category/dmv-queries/   Adam… Continue Reading

Are My SQL Server Indexes Being Used?

  By

Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as part of your index maintenance procedures that you periodically check to see if your indexes are being used. Many times, indexes are created in the belief they are needed but in fact they are never used. You can reduce that IO overhead on inserts when you remove unnecessary indexes. How Do You Find Them? Glenn Berry (B|T) has a fantastic script as part of his diagnostic scripts (link) that helps identify… Continue Reading