When using a Geo Replicated Azure SQL Database Readable Secondary there are a few things to consider when it comes to performance tuning. Check out this episode of Data Exposed: MVP Edition as we discuss what you need to keep in mind with Microsoft’s Anna Hoffman, @AnalyticAnna.
-
-
Lots of exciting things are happening with Data Saturdays and we want you, the #SQLFamily, involved. Last week, Rob Sewell (B|T) announced the new automation and setup process for events. This week I am announcing a new logo and branding initiative. With the generous donation from Denny Cherry and Associates Consulting, DCAC, we have commissioned 99Designs artists to create a new logo for branding. The Data Saturdays Admins reviewed over 220 submissions these were considered the likeability, accessibility, inclusiveness, and ease of multi-use. We have narrowed down the choices to these four. Please take a moment to click the link below and rate your favorite, we will use your influence…
-
Synonyms inside SQL Server are one of those useful but forgotten features. A synonym is a database level object that allows you to provide an alternative name for another database object such as a view, user defined table, scalar function, stored procedure, inline table valued function (tvf), or extended stored procedure. They can also be used for CLR Assembly related stored procedures, CLR tvf, CLR scalar functions or even CLR aggregate functions. There are many practical uses for synonyms, and I’ll explain how to create them and some use cases. Read the full article here at Red-Gate’s Simple Talk
-
Continuing with our Performance Tuning series as you have learned baselining and knowing your normal system performance metrics is an important part of performance tuning. In part 1 we discussed collecting vital system metrics such as CPU Utilization and Memory usage. In part 2 we took a glimpse into how we can gather those metrics and other using the different tools that are available to use like Performance Monitor, Profiler, Extended Events and DMVs. In this part 3 we will look at how identifying the appropriate waits events and corresponding resolution will be critical for resolving performance issues. But first you need to understand what waits are and where to find…
-
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…
-
Performance Monitoring Tools In this second post we are diving into what tools you can use to capture the important performance metrics you need to gather for baselining your environment. Be sure to read part one first. Performance Monitor First let’s start with what comes native to Windows Server platform, whether you are on an Azure SQL Virtual Machine or your standard SQL Server instance install on premises you have a native tool, called Performance Monitor (affectionately known to most as PerfMon). This reliable tool allows you to easily monitor performance metrics through a Graphical User Interface (GUI), by selecting performance counters for the operating system as well as installed applications such as SQL Server. It…
-
I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you. What is TempDB? TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn’t fit in memory. It is always database_id number two in…
-
Occasionally, we must resize or realign our Tempdb log file (.ldf) or data files (.mdf or .ndf) due to a growth event that forces the file size out of whack. To resize we have three options, restart the SQL Server Service, add additional files, or shrink the current file. We most likely have all been faced with run away log files and in an emergency situation restarting the SQL Services may not be an option but we still need to get our log file size smaller before we run out of disk space for example. The process of shrinking down that file can get tricky so I created this flow…
-
SQL Server 2017 (compatibility 140) brought us many Intelligent Query Processing (IQP), formally known as Adaptive Query Processing, features that improve performance on workloads straight out of the box with no code changes to implement. One of those features introduced was Adaptive Joins. In this feature the join operator is dynamically determined at runtime by the optimizer, which defines a threshold number of rows and then chooses between a Nested Loop or Hash Match join operator. This operator switch can produce a better join and improve performance of your queries without you having to lift a finger. Not all queries will qualify for this new feature. The feature only applies…
-
Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal. By right clicking on your database properties and choosing the Configure SQL page you can change your Subscription and Azure Location. Even more and what is really cool is I can also scale up and down my Edition (which is generally referred to as Service Tier outside of SSMS), Size and Storage Tiers as well.…
