Thanks to BrentOzar for hosting this month of T-SQL Tuesday. Please find the original post here.
Brent asked us to share the last issue that we resolved. I recently resolved a replication issue. I completed a transactional replication which is set between 4 servers. Let’s say, A, B, C, and D servers. A replicates to B which then replicates to C and then from Server C to Server D. The setup is complicated. On a regular process, Server A needs to import the fresh data on Server B tables that are involved in replication. It is not possible to drop the tables as they are currently involved in replication to B Server which then replicates the data to Server C. To make this possible, publications have to be dropped on Server B and Server C before importing fresh data from Server A to Server B. Once the tables are created on the Server B, then the publications are recreated.
As all these servers are involved and linked through transactional replication on the same tables, and publications are created on each of these servers, it gets complicated to drop and recreate the publications on Server B and Server C. We tried automating the process by creating the drop and recreate publication SQL agent jobs and using triggers to trigger the execution of the jobs one by one.
I know we can use other technologies in SQL Server to accomplish the same goal but the requirements and the limitations from vendors made us to stick with this plan.
So, the setup is complete. Soon after, I saw one of the recreate publication jobs fail due to the replication agent account being locked, and the downstream jobs all failed as they were interlinked to each other. Though the resolution is simple to enable the locked account, it is hard to figure out that this is the reason for the replication being broken. Especially, when these jobs fail across multiple servers.
I enabled the replication agent account and rerun the job to success. Then manually executed each of those failed jobs to succeed on the rest of the Servers. I am currently in the process of figuring out why the account is locked in the first place.
I know it is challenging to maintain the replication but it gets even tougher with complicated replication setups across multiple servers.
This is the last issue that I fixed the last Saturday night at 1am, LOL. Glad the issue is resolved.
Though the setup is tough and things seem complicated, I was able to successfully migrate the servers from older versions to new versions without breaking the replication setup that is required between multiple servers.
I am looking forward to reading the other SQL family posts on this month’s T-SQL Tuesday.
If you are not very familiar with the Query Store feature, please check my series of blog posts on Query Store here.
In this post, you are going to learn about the advancements of the Query Store released in SQL Server 2022. This is the first blog post for this series. You can expect more posts in the coming weeks related to the Query store usage in the Intelligent Query processing features released in SQL Server 2022.
Advancements in SQL Server 2022:
Query Store enabled by Default – For any new databases that you create on SQL Server 2022, the Query Store feature is enabled by default. If you are migrating the database from lower versions to SQL Server 2022, the query store feature is not enabled by default. You need to manually enable it if the feature is not enabled manually on the lower version before. While choosing the configuration options, you need to make sure you capture only the required queries and not the insignificant queries that you do not bother capturing. To effectively capture the queries you need and save space for the Query Store data, you can customize the capture policy based on the total compile CPU time, total execution CPU time, and execution count. Example of the custom policy (source)
2. Query Store on Secondary replicas – In SQL Server 2022, Query Store can also gather the information from the secondary replicas and send it over to the primary replica through the same channel that sends the data to the secondary replica. Once the data is captured and sent the data over to the primary replica, the data is hardened to the Query Store schema on the primary database. Query Store for secondary replicas is available for preview. It isn’t available for use in production environments.
You will need to enable the Trace flag 12606 before you enable the Query Store for secondary replicas.
There are some steps to follow in enabling the Query Store on secondary replica –
The primary database should have the Query Store enabled on it and it should be in the read-write mode before enabling the Query Store on secondary replicas.
Since the secondary replicas are all in read-only mode, you need to enable the Query store on the secondary replicas by connecting to the primary and running the below query on each database you would like to enable. This will enable the Query store for all the secondary databases.
ALTER DATABASE [Database_Name]
FOR SECONDARY SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE );
GO
To find more information about the replicas, you can use sys.query_store_replicas. You can find the replica_group_id, role_id, and replica_name.
3. Query Store Hints: We do have this feature available in Azure SQL database and Azure Managed Instance. It is also introduced in SQL Server 2022. When you have a query that will improve the performance with the query hint, you can use the Query store hints. Just like the regular query hint, it is the same way to use but the main important thing is that you do not have to change the underlying code of your query. You can use the system stored procedure designed for the Query Store hint sys.sp_query_store_set_hints.
You can use the Query store hints in the scenarios where the query needs to be recompiled, setting the MAXDOP, setting the memory size grants for your queries, compatability level at the query level, using specific kind of the joints like using Hash join in the place of the nested loop joins.
To see the example of how you can implement the Query Store hints, see my blog post on this here.
Some things to remember while using the Query Store hints:
If you are using a force parameterization option at the database level, remember that the Query hint ‘RECOMPILE’ will be ignored. Any other query hints will be used and execute the query. When this happens, there will be a warning 12461 issued by the SQL Server.
If there is any contradictory hints used while using the Query hints, simply the other hint will be ignored without issues with execution. The query will run fine.
If you are already having any hints at the statement level or any plan guides on your queries, they will be ignored by the Query hints. Query hints will always override these.
Prior versions of the SQL Server 2022 only captured the data using the Query store on the primary replica and Query Store hints are only applied to the primary replica but in SQL Server 2022 – once the secondary replicas are enabled to capture the data from Query Store, Query hints are replica aware for the secondary replicas.
4. Intelligent Query processing features in SQL Server 2022 – uses the Query Store data to capture the information overtime to capture the feedback and use that feedback for better performance of your queries. All the below highlighted features in blue were released in SQL Server 2022. To use these features, you just have to place the compatibility mode of the database to 160.
The only IQP feature released in SQL Server 2022 that doesn’t require the Query Store to be enabled is ‘Parameter Sensitive Plan Optimization” also known as PSP Optimization.
In the coming series of blog posts, I would like to go in detail of each of these IQP features with queries explaining how these features help in optimizing the performance of your queries and how Query Store help support these IQP features.
Thank you Reitse for hosting January month of T-SQL Tuesday year 2024. This is my first blog post in the year 2024. Reitse asked us to write about any lessons learned from the old projects. Please find the original invite here.
I had to work on a project years ago where there was a requirement to migrate the old version of the database (SQL 2000) to SQL 2012/2016. One of the requirements was to use the replication to replicate the data over to another server. This is an OLTP database where there were heavy transactions per second. As a part of the planning, I recommended using the Always on Availability groups instead of the replication. My manager at the time (from my previous company) was a person who trusted and supported male DBAs more than female DBAs regardless of their expertise. I had to respect the decision to replicate the data over using the availability groups for read-only reporting purposes. I didn’t even have the chance to test the scenarios, compare the results from the two technologies, and show the results in the test environment.
Once the project is completed and in production. There comes the problems with replication. There were intermittent network failures, issues with the settings in the files generated by the snapshots, and other typical issues with the replication- there was a heavy lag in transferring the data. If you worked on replication before, you surely know fixing replication issues was no joke. We had to later remove the replication for good and replace it with the always-on availability groups. This has not only improved the performance overall but also required less maintenance.
Though the management understood later that they must have taken a different approach, the cost of it was the time and resources spent on the project.
One of the other projects was about the tools we wanted to purchase for data governance. Depending on one tool is not a good case when you are still in the process of deciding which tool to purchase for your needs. We had to spend a lot of time on one tool for the Proof of concept for months only to decide it didn’t serve our needs. This took a lot of DBA and the other team resources for months. I believe understanding the requirements from the company’s perspective should be the first step in the process. Then compare the tools from the wider perspective of whether they can serve all the requirements we are looking for. Filtering this way can give us the list of tools we can try and test to decide further.
These are the two experiences I would like to share in this post. I am looking to learn more on this topic from others as well.
This will be the last post for this year – writing this a few hours before the end of the year! I will keep this short and not boring.
Would like to thank you all for staying with me along my learning Journey. You are the main reason why I still continue to write these posts. Thank you and Thank you!!
As I look back at the year 2023, the main thing I can say is, that it was super fast. I still remember the day I wrote my 2022 year-end review. Time flies so fast and we get older quick.
I would like to reflect on this year as per my professional growth and personal growth.
Professional
Community Speaking and Other Engagements
Spoke at many local user groups and International conferences like SQLBits and PASS Summit 2023.
Published 17 blog posts in the year 2023 with 17.2K views and 11.7K visitors for this year. Totally, dbanuggets crossed over 56.6K all-time views and 36K visitors.
Personal
I was interested in learning philosophy for a very long time. I had started educating myself on Stoic philosophy starting with Marcus Aurelius Meditations. Started listening to Rober Greene Audio books on Audible starting with The 48 Laws of Power. Recently fell in love with J. Krishnamurthy’s philosophy, this is my all-time favorite I can say because I am finding answers to some complex questions about life and I truly believe Krishnamurthy has answers to my questions. Really enjoying his hour-long lectures.
I am looking forward to continuing to publish more blog posts, community work including speaking engagements, and Mentoring in the year 2024.
To all you wonderful people who read all the above lines and reached this section of this post – These are a couple of the things I learned about life this year. Would like to share some of them here.
Live this life as much as you can – cause you don’t have any other lives to jump into. No matter how much you feel this life sucks, you are traveling on your own road (your life). Stop looking at the other roads cause you don’t know where they turn and how far the other roads lead. Those roads have their own bumps, speed breakers, and dead ends. Focusing on our road is the only way.
If you are trying to solve any particular issue in your life, try to examine it and do research on the issue. Fear disappears once you understand what the issue is. You can find books, and videos talking about the issue in detail. As you do the research, you will find you are not the only one having the same issue – which in itself solves half of your worries. Along the way, you might find a community of people trying to solve the same problem or achieve the same life goal.
Do not bother about what others think of you. Don’t take other people’s negativity towards you personally. They do not know you, your dreams, your ambitions, and the work you do while they are not watching you. Have empathy towards them. They may be going through a lot in their lives and have many insecurities in their lives.
No matter how big of a problem you are facing, many people may be going through the same exact problem at the same time as you. We are the human species that lived on earth for 200,000 years. Problems do repeat themselves but as we are more focused on our own lives, we feel we are the only ones facing the issue.
See your problems from a broader picture – most of the time, you will end up realizing you are giving greater attention than it needs.
At the end of our lives, none of these problems matters.
Thank you my dear Friend Kay Sauter for hosting the December month of T-SQL Tuesday. Here is the invitation. I believe this is the perfect way of saying thank you and Goodbye to the year 2023.
Let’s take a moment here. Seriously, stop scrolling for a second. Can we all collectively pat ourselves on the back for a second? I mean, seriously, YOU all deserve a medal or at least a virtual hug for hanging around and being the absolute best bunch of readers of this blog!
Out of all the other wonderful things around the world to watch for, out of all the other endless distractions we have in this social media world today starting with cute little kitten videos all the way to your favorite actor movies, possibilities are endless for the entertainment. Still, you decided to spend your time chilling here reading all my ramblings. Seriously, I am speechless! Thanks for your commitment and patience with me.
Thanks for even taking my emotional side of things. I am an emotional person and a part of me from that side of my personality should have shown up in some of my blog posts. Especially, when I sit in the night and put my thoughts in here. My thoughts are unfiltered. You have braved to see me from that angle as well. I mean, my Drama! Thanks for embracing the unpredictable side of me.
So, THANK YOU!
Thank you for being my best virtual friends, and for spending your time and attention! Thank you for being so patient with me as I compose and do the posts.
You are all my real MVP’s and these little bi(y)tes of dbanuggets wouldn’t be the same without you!
So, my awesome and amazing readers! Keep being yourself! Never ever let anyone dim your light! Cause, you are born to stand out!
Embrace this life, keep being fabulous, be curious, enjoy the little things in life, cry, scream, and laugh the life. There is nothing wrong with it. After all, we just have one life and we want to be authentic. Show the emotions instead of suppressing them. Tell people in your life you love them. Do not worry about making mistakes. There is nothing bad in being wrong. There is nothing wrong with it. Keep being YOU!!
To enable the SQL Server 2022 on a virtual machine to use Azure AD, we need below things:
Managed identity and its permissions to query Microsoft Graph: When you enable the AD on the SQL Server 2022 and create the Azure AD login and that user trying to login, the managed identity will be used to Query the Microsoft Graph and that is the reason we need to give this identity the permissions to Query Microsoft Graph. The security of this managed identity is at the resource level. Here the resource is a Virtual machine.
To set up the Azure AD authentication for SQL Server on VM, we can do this by using System-assigned managed identity or User-assigned managed identity.
System-assigned managed identity is easy to manage and configure as this identity will be deleted when the VM is deleted. It will have validity until the VM has its life whereas the User-assigned managed identity will be used at scale for many SQL Servers on the virtual machines.
In this post, we are learning how to enable the AD authentication to SQL Server on VM. So, we need the SQL Server 2022 which is installed on the Azure VM and we will have to register the Windows SQL Server VM with SQL IaaS Agent extension.
Permissions:
These are the below permissions needed to connect the SQL Server to VM to the managed identity:
User.ReadALL, GroupMember.Read.All, and Application.Read.All.
The managed identity must also have Active Directory reader permissions. Search for the Microsoft Entra ID
Under the default directory, click on the roles and administrators
Make sure you have Azure AD Global administrator permissions before giving the identity the reader permissions to the Azure AD
If you scroll your bar right side, you can see the ellipses button. Click on that to see the description of the role
Under the directory readers, click on assignments and then click on add assignments
From the list, choose the VM name if you want to use the system assigned managed identity
Click on Add at the bottom of the page
You will see the below screen. Make sure the application (VM name) is mentioned under the select members. This is if you choose the system managed identity. Click next.
As you click next, you will see the below screen. Make sure the assignment type is active and click on the box for permanently assigned. At the bottom of the screen, you will see the assign button. Click on that.
Under the directory readers, you will see the vm system assigned identity is being added.
If you want to choose the user-managed identity, choose it.
Enable the Azure AD authentication in the Azure VM
Under the SQL virtual machine (Make sure your VM is turned on and the SQL virtual machine is online as well to see the enabled security configuration option in the left blade under the security tab). Enable the Azure AD authentication. Here, I already enabled it earlier and so you see it as enabled.
Remember: Once you enable the Azure AD authentication, you cannot disable it.
If you want to enable the Azure AD authentication, you will see the below screenshot. Click on enable and hit apply at the bottom of the page.
In this blog post, you have read the process of enabling the Azure AD authentication on Azure VM. Only Windows Server 2022 VM’s are supported to enable the Azure AD authentication with the SQL IaaS Agent extension.
Thank you for reading!
References:
Microsoft article – Enable Azure AD authentication for SQL Server on Azure VMs
Please excuse me if this post is going to be the funny one, yes it is!
I would like to first thank my dear friend Josephine Bush for bringing up this great topic and asking us to write about how the proper job titles and descriptions should be for the job postings.
I surely have a lot of thoughts on this topic. Two years ago, I was applying for Database administrator positions everywhere. If not everywhere, almost everywhere. Linkedin, Indeed, Monster, CareerBuilder, etc. As I was looking at the Job titles and Job descriptions at the time, my blood boiled at the beginning looking at the requirements. More about this soon. I was already stressed about figuring out a way to get a job soon and on top of that stress, unreasonable job descriptions caused me more stress. After a couple of days, that stress turned out to be a stress reliever. Yes, you read that right. In the beginning, I was madly looking at the job description mentioned for a SQL database administrator role mentioning high expertise in the languages – C++, Java, PHP, Python, etc; but later as I was checking on many of these types of job descriptions made me laugh and had helped me filter out the companies that I can ignore applying.
The other funny thing I observed is the description mentions that the company is looking for a senior-level position and mentions the salary as 15 dollars per hour. The description for this kind of job posting also mentions the certifications as needed or preferred.
To take this to another level, I would like to show an example of postings from one of the best companies (Don’t want to point out the company name here) back in 2020 for the position of “Cloud Native Infrastructure Engineer” mentioning the requirement is to have 12+ years of Kubernetes experience when in fact the Kubernetes technology was released in 2014 which was just 6 years. Source
I believe many companies rely on people who do not have much experience with technology to post about these job openings and their descriptions. Due to this, great candidates who can be a great fit for the position would not even consider reading the entire description. This can ruin the reputation of the company.
One more hilarious experience of mine –
My first-ever job was with the title “Database administrator” but as I enter the company, the first day I received a tag with my name, picture, and with the title “Database Developer”
Hope you enjoyed reading this post and my request to anyone looking for a job actively ignore these type of job descriptions!
I am curious to read all other posts on this topic for this month of T-SQL Tuesday!
SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.
Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –
Azure Active Directory Universal with Multi-Factor Authentication
Azure Active Directory Password
Azure Active Directory Integrated
If you join the Windows Server Active Directory and Azure Active Directory, then you can use both the Windows login and Azure AD login to SQL Server.
Remember:To connect your SQL Server to Azure AD, your SQL Server and the host that runs the SQL Server should be registered in Azure Arc. If you have not already connected your SQL Server to Azure Arc, here is a step-by-step guide to do.
The user that is used to set up the Azure AD should have the contributor role for the server and on the Keyvault.
Prerequisites:
When installing the SQL Server 2022, the extended functionality in Azure will automatically install the Certificate that is stored in the Key Vault and Azure AD application automatically as we create the Azure AD admin for SQL Server.
Setup Azure AD admin
The account that is being used to create the Azure AD admin account must be in the contributor role for the KeyVault and for the Server as well from the Azure Arc.
Permissions are shown under the Access control (IAM) under the KeyVault
Permissions as shown under the Access control (IAM) under the SQL Server-Azure Arc
Setting up the Access Policies under the KeyVault
Under the Key vault, select the permissions as shown below. You will find these under the Access policies within the KeyVault
Figure 1
Setting up the Access Policies under the Azure AD users
Figure 2
While setting up the Azure AD admin, portal functionality will automatically create the Key vault certificate and the Azure AD application registration but you can also manually create your own certificate and also setup the Azure AD application registration.
You can create the Azure AD admin user using the portal/PowerShell/Azure CLI/ARM template. If you are creating using the ARM template then you need to create the Key vault certificate and Azure AD application before setting up the Azure admin.
Azure AD admin setup for SQL Server
In the Azure portal, search for “SQL Server – Azure Arc” and select it from the services listed.
Choose the Azure Arc SQL Servers registered. Select the Active Directory from the left side pane.
Select the user you want to use and select the Service-managed cert and Service-managed app registration. For the key vault, select the key vault and click select. Once you choose these options, click on Save at the top. If you followed all the steps correctly from the beginning, you should not see any errors.
During this process, a call is sent to the Azure Arc agent which will then configure the Azure AD authentication for your specific SQL Server instance. This process will take a couple of minutes to complete.
So while this process is going on, there are certain things that will happen automatically behind the scenes.
The certificate is created in the key vault you selected with the name format hostname-instancename-uniquenumber as shown below
The Azure AD application is created with the name in the format of hostname-instancename-uniquenumber as shown below.
The necessary permissions also will be granted to this application automatically. To see what permissions are provided, please look for the steps below:
Open the application (here, I am using the manually created application name SQLServerCTP1. Once you open the application, in the left side pane click on API permissions.
Click on the Microsoft Graph and request API permissions (see a couple of screenshots below)
3. Then the certificate from the Azure Key Vault is assigned to the application and saves all of these settings to Azure Arc.
Remember: The certificate created automatically is not managed and rotated by Microsoft. It is up to the customer to manage the certificate. For that reason, we can also create our own certificate along with the application for Azure AD admin setup. Follow the below steps to create your own certificate and application.
According to Microsoft docs, Steps that need to install Azure AD to connect to SQL Server are:
Create and register an Azure AD application and Grant permissions to the Azure AD application
Create and assign a certificate
Configure Azure AD authentication for SQL Server through the Azure portal
Create logins and users
Connect with a supported authentication method
Create and register an Azure AD application
In the Azure portal search bar, search for Azure Active Directory and then choose App Registrations from the left pane. Then click on New registration
Go to the application. On the left side pane, click on the API permissions.
Next, give all the permissions needed for the application. Select the Microsoft Graph
Click on the application permissions
In the search bar, select the Directory.Read.All and select the permission and click on add permissions.
Follow the same steps again. Go to the application> left side pane select API permissions> Microsoft Graph> Delegated permissions
Select all these and grant admin consent. As per the Microsoft doc, the user that giving the permissions should be a part of Azure AD Global Administrator or Privileged Role Administrator.
Application.Read.All
Directory.AccessAsUser.All
Group.Read.All
User.Read.All
2. Create and assign the Certificate
Go to the Key Vault account if you have it ready to place the certificate. If you don’t have one, create one. Here I already have it.
Click on Generate/import > Give the certificate name. Check for the validity period and by default it is 12 months which is recommended. rest all the options as is as per default values and click Create.
Click on the certificate later to see if it shows online. If it doesn’t please refresh the page to see it online. Download the CER format of the certificate. You need this download to upload to the certifications under the application.
Go to the application that was registered page. On the left pane, click on the certificates and secrets. Under the Certificates click on upload
Upload the certificate from the downloaded location and click on add.
Next, go to the key vault where the certificate is saved to provide the permissions. Add Access Policies.
Click on the principal>give the computer name>click next> review and create.
3. Configure Azure AD authentication for SQL Server by creating the Custom-managed Cert
Click on the SQL Sevrer instance linked to the Azure Arc for SQL Server> go to the active directory>click on set admin>
Select the Customer-managed cert and chose the certification. From the drop-down menu, you will see all the details. Choose the appropriate ones and click select.
In the same way, you will also choose the SQL Server app registration.
Select the app registration>select app registration>select the app>select
After you have selected it, click on the save button at the top
You should be able to see the login in the sql.server_principles on the server but it is not sysadmin yet.
You can either give the sysadmin permissions through GUI in ssms tool or you can use the below script. Here I am using my email credentials but you can replace that with your AD admin user email credentials.
ALTER SERVER ROLE sysadmin ADD MEMBER [deepthi@dbanuggets.com]
GO
4. Create logins
Once you give the sysadmin permissions to this account, it can go ahead and create any number of Azure AD logins. For example, I took some examples from Microsoft docs.
-- login creation for Azure AD user
CREATE LOGIN [user@contoso.com] FROM EXTERNAL PROVIDER;
GO
-- login creation for Azure AD group
CREATE LOGIN [my_group_name] FROM EXTERNAL PROVIDER;
GO
-- login creation for Azure AD application
CREATE LOGIN [my_app_name] FROM EXTERNAL PROVIDER;
GO
5. Connect with a supported authentication method
You can try to log in now using the Azure AD authentication from ssms tool shown below:
With the multi-factor authentication, you will need to login into Azure and you will be connected to SQL Server 2022 with an Azure AD admin login.
Summary
In this blog post, you have learned how to Create an Azure AD Authentication connecting to SQL Server 2022. We have seen creating the Certificate and the application automatically by Azure to add Azure AD admin to SQL Server but also we have learned how to manually create the Certificate in Key Vault and create an application as we try to add Azure AD admin to SQL Server 2022. Finally, we were able to connect to the SQL Server by using the Azure AD authentication using ssms tool.
If you are selecting this option, be ready to have all the information shown below:
Failure to provide any of the below will throw below error:
I have seen the above error multiple times while I tried to enter the values. I believe that I provided the correct values but not sure if I was missing anything. I decided to install the SQL Server 2022 without including this extension from the GUI but instead install the Azure arc manually later. This blog post shows exactly the same. A step-by-step guide to install and configure connecting your on-prem SQL Server into Azure using Azure-arc.
You need to have an active subscription to install Azure Arc connection to your SQL Server. In the Azure portal search bar, select “Azure Arc SQL” and select SQL Server – Azure Arc from the list.
Click on add
Under Add existing SQL Server instances> Click on Connect Servers
Check all the prerequisites are in place
Provide the subscription, resource group name, Region, Operating system, Servername (Hostname of the server which will include all the instances including the default instance of SQL Server), License type
By default, all the instances will be included as you provide the machine name but if you would like to exclude any instances, you can provide the name of the instance separated by a comma.
I am not going to provide any tags here, click next to run the script
Observe this page properly. Copy the code.
In the second step, it shows “Run the above script on the machine you set up the prerequisites. Make sure the machine has network connectivity to Azure and to your target machine with SQL Server.
I used the execution policy for the current user to run the Powershell script. In PowerShell window, run the below script which will give the current user permission to execute the scripts.
Get-ExecutionPolicy -Scope CurrentUser
Now, Paste the code that you copied in the PowerShell and hit enter.
Azure extension for the SQL Server is installed. Check for the registered SQl Servers in Azure Arc.
Click on the instance to see more details about the server. I have clicked on my named instance>databases from the left side blade. You can see the list of databases
I would like to connect to Azure from SQL Server 2022 on-prem. To do that, you need to enable Azure Active Directory. In the next blog post, I will explain how we can set up the AAD (Azure Active Directory authentication) to SQL Server 2022 and connect from your local SQL Server management studio to Azure.
This month T-SQL Tuesday is hosted by Eric Darling asking us to write about the code that made us feel a way.
I would like to mention the Query Store hints and why I really liked it. If you have a parameter-sensitive query in your stored procedure and you need to use a hint (For example, using RECOMPILE hint in this case) to fix the issue quickly without changing the actual code, using query store hints is the best option. Not only that, but you can also use other useful hints like setting up the MAXDOP, Compat level etc. For the list of supported and unsupported hints, look here.
Remember: This is the best last option to choose when you cannot change the code. It is always best to refactor the stored procedure.
It is very easy to use. You just need to collect two things. The query ID of the stored procedure and the query hint option you would like to use. There is a special stored procedure that you can use to implement this Query hints into your stored procedures.
To find the query_id of your query, run the below code by changing the Query text in the like operator:
/* Find the query ID associated with the query. Source */ SELECT query_sql_text ,q.query_id FROM sys.query_store_query_text qt INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id WHERE query_sql_text LIKE N’%query text%’ AND query_sql_text NOT LIKE N’%query_store%’; GO
Use the Query_id to run the below-stored procedure with the hint you would like to use
Query store can also capture ad-hoc workloads. This can fill up your query store real quick if you have a lot of ad-hoc queries coming from your applications. If you can parameterize these queries, configuring PARAMETERIZATION = FORCED the database can be an option. Look more about Forced parameterization here. If you can’t parameterize those ad-hoc queries, you can set the Optimize for Ad hoc Workloads server option to save the cache memory on the queries that will execute only once. If you do not want to capture this kind of queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.
Remember:
If you enable the forced parameterization and use the query hint RECOMPILE at the same time, the query engine will ignore that query hint and proceed with using any other hints used. If you are using the Azure SQL database, you will see the error with code 12461 when the RECOMPILE query hint will be ignored. Source
Query Store will only store the latest hints that are active. It will not store the history of the hints that were active once. To capture that information, you can use extended events. I have written a blog post here on exactly how you can set this up so you can get the history of the hints.
I am looking forward to reading other SQL family member posts on this month’s T-SQL Tuesday post hosted by Erik Darling!
Deepthi Goguri's SQL Server Blog 