Quick Tip Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes. Example We are going to create a very simple stored procedure called ConvertExample. In this procedure we will see two things. One, the first procedure we create will declare two variables as VARCHAR( MAX) data types, then in the WHERE clause it will convert a table column…
-
-
When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice. DROP EXSITING=ON which is my preferred method, will DROP the current index only after it finishes creating and building the index with the new definition. The pitfall is that if the index does not exist, you will get an error and must create it without the…
-
Index Maintenance is an important aspect of database health. Above and beyond regular index rebuilds and reorganizations it is important to understand the usage of the indexes on your database. Cleaning up unused indexes can save a lot of overhead on Insert/Update/Delete operations. To achieve that goal, I typically run a script like the one shown below and check out whether or not an index has had any seeks or scans against it as a starting point in my cleanup regiment. SELECT d.name, OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], s.user_seeks, s.user_scans FROM sys.indexes AS i LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] =…
-
Knowing your data is very important when it comes to writing code. Now I’ll admit that I am very far from being a developer, however as a DBA, I spend much of my day’s performance tuning code. In doing so, I get to see many ways code can introduce excess database reads. One of the most recent things I have come across has to do with NULLs. The environment I was working in had no default values in their table design, so it was riddled with NULL values. Over the years they had implemented coding standards to try and mitigate these NULL values within their code. In every column search…
-
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…
-
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 index utilization. In his diagnostic scripts it is listed as…
-
My last two blogs were on understanding columnstore and creating columnstore indexes. In this post, I will touch on one last topic, when to use columnstore indexes. As noted in my prior posts these indexes are designed for large data warehouse workloads, not normal OLTP workload tables. As with any index design it is important to know your data and what you will be using the data for. Be sure to look to what types of queries you will be running against it, as well as how the data is loaded and maintained. Let’s look at a few questions you should ask yourself before deciding to implement a columnstore index.…
-
In my last blog I explained what a columnstore index is, in this blog, we will dive into creating a clustered columnstore index and look at the performance differences the index can make. Let’s get started. Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different product keys. First, we will run the query with no existing columnstore index and only using the current clustered rowstore (normal) index. Note that I turned on SET STATISTICS IO and TIME on. These two SET…
-
Recently I reviewed filtered indexes, this time let’s look at columnstore indexes. These indexes are very useful for data warehouse workloads and large tables. They can improve query performance by a factor of 10 in some cases, so knowing and understanding how they work is important if you work in an environment with larger scaled data. Now, I admit when these first were introduced in SQL Server 2012 I found them very intimidating (additionally, you couldn’t update them directly). For me, anytime you say columnstore, my mind tends to set off alarms saying wait stay away, this is too complicated. So, in this post I am going to try and…
-
In this post, we continue with another beginner’s blog of database features that may be unknown to many. Let’s take a look at filtered indexes. Many database administrators are fully aware of the power of indexes and know how to create them. However, I find that some have yet to dive into fully optimizing their indexes and taking advantage of what filtered indexes has to offer. What is a filtered index? Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product…
