Category: Performance

  • Amazon Redshift Views, Sort Keys, and Outer Joins

    Amazon Redshift Views, Sort Keys, and Outer Joins

    My team built a process to load from a couple of base tables, in our Amazon Redshift enterprise data warehouse, into an other table which would act as a data mart entity. The data was rolled up and it included some derived fields. The SQL query had some complicity to it. This process ran daily […]

  • Linked Server Elimination

    Linked Server Elimination

    SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even split it among different servers. Sharding is another term. It refers to partitioning data to […]

  • 5 Extended Events Sessions Your SQL Server Instance Cannot Live Without

    5 Extended Events Sessions Your SQL Server Instance Cannot Live Without

    SQL Server Extend Events is a general event-handling system. It correlates data from SQL Server and, to a limited degree, the operating system. Extended Events are an optimized replacement for SQL Trace. In most cases, Extended Events are much lighter weight and they are always the preferred method for trace events. In addition, there are […]

  • Inside the XEvent Profiler

    Inside the XEvent Profiler

    XEvent Profiler is a new feature of SQL Server Management Studio v17.3. I applaud the SQL Server Tools Team for this feature. I feel like it will go a long way towards putting the legacy SQL Profiler to bed. Sometimes you cannot do any better than taking it straight from the source. Here is Microsoft’s […]

  • SQL query performance tuning tips for non-production environments

    SQL query performance tuning tips for non-production environments

    It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits. […]

  • DBCC CLONEDATABASE

    DBCC CLONEDATABASE

    Often I hear developers claim that they need production data in order to properly tune their queries. There are a couple of reasons that they believe this. The most important one is likely because the execution plans generated in a development environment with limited data will be different than the ones which are generated with […]

  • DBCC OPTIMIZER_WHATIF: Spoofing production hardware

    DBCC OPTIMIZER_WHATIF: Spoofing production hardware

    Performance tuning in a development environment can be difficult. Processor core count and memory are important factors in execution plan generation. The undocumented command DBCC OPTIMIZER_WHATIF will alter the optimizer’s perception of its server resources. Syntax You can use the DBCC HELP command to find the syntax for the DBCC OPTIMIZER_WHATIF command. [sql]DBCC TRACEON (2588) WITH […]

  • Unique Indexes and Computed Columns in SQL Server

    Unique Indexes and Computed Columns in SQL Server

    In the last post of this blog series, I discussed what a non-clustered index is and how to choose an ideal non-clustered index key. In this post, I will discuss indexes on computed columns and unique indexes. Computed columns Computed columns almost do not fit into this series about indexes but I felt it was […]

  • Non-Clustered Indexes in SQL Server

    Non-Clustered Indexes in SQL Server

    In the last post of this blog series, I discussed what a clustered index is and how to choose an ideal clustered index key. In this post, I will describe the non-clustered index and offer design tips as they relate to query performance. Unlike a clustered index, non-clustered indexes do not contain all the columns […]

  • A better way to search dates

    A better way to search dates

    SARGability is the ability of the query to properly search the arguments that you pass it. When your query is not sargable you cannot properly use your available indexes. A few months ago I wrote about sargability in detail. Today I want to show a example of how rethinking a solution, with sargability in mind, can provide […]