Category: T-SQL Tuesday

By the way, What is New Year Resolution?

I am overwhelmed with gratitude by looking at the number of responses we received for my February month of T-SQL Tuesday invitation. Surely, I see so much interest and curiosity for the newly available features in Azure SQL and SQL Server 2022. Not to mention, for the new year resolutions too 😉

Welcome to all the new T-SQL Tuesday Bloggers! You are surely expanding the blogging party!

I had a great honor on hosting February month of T-SQL Tuesday 2023! Please check here for the original invite.

I had great responses to this invitation and some of them were the first timers to write the post for T-SQL Tuesday. Please see for the summary on the posts written by SQL family.

Chris Wood

Chris mentioned about how executing the view having nested views in it will impact the performance but how the new CE has improved the performance of the query a bit but with the higher estimated number of rows in SQL Server 2019 CU16. Chris was curious to know if the SQL Server 2022 latest features DOP and CE feedback options can fix the nested view performance as these features eventually add hints to the execution plan for later executions. Read the complete post by Chris Wood here.

Rob Farley

Rob explains why ‘IS [NOT] DISTINCT FROM’ is one of his favorite functionality as it solves the NULL issue. Rob says it is better to type ‘IS [NOT] DISTINCT FROM’ than dealing with the ISNULL() with amazing examples and how the indexes are used with ‘IS [NOT] DISTINCT FROM’ in the queries and for better performance of the queries. Check for the complete post here.

Rob Litjens

Rob’s favorite Azure features are Purview, Synapse integration, connecting on-prem servers to the managed instance through distributed Availability Groups. Rob mentions with SQL Server 2022 creating AG in managed instance and how you can fail back to on-prem.

Regarding the resolutions, Rob wanted to be involved more into speaking engagements and he is looking for the organizers to contact him if they are looking for ay of these topics related to platform DBA (like Group Managed Service Accounts, Automated Deployments, Desired State, Ansible, Defender for SQL, etc.). Find all of his new year fun resolutions here.

Brian Bønk

Brian describes about the Optimized Locking feature for Azure SQL Database. He explains the advantage of how only the actual rows that are being used are locked instead of many exclusive locks placed during the transactions. Brian explains the benefits with an example helping the data warehouse scenario and where this feature is currently available based on the locations.

Brain says his new resolutions for this year includes more physical exercises, meditation and being up to date with the Data platform news. Great resolutions, Brian!

Don’t forget to check some of his favorite apps he uses as the reminder for meditation Headspace and Feedly to follow the latest RSS feeds from the Microsoft Data platform. Check for the complete post here.

Magdalena Bronowska

Magda favorite new/updated T-SQL functions are DATETRUNC(), LEAST() & GREATEST(), STRING_SPLIT() with examples. Please see the complete post here with examples.

Magda loves spontaneity than planning for resolutions. She have few resolutions for this year though. Magda resolutions are to take Microsoft Certifications and Workout Wednesday, which is new to me. Read more about it here in her post.

Kay Sauter

Kay favorite feature is STRING_SPLIT() function and the second feature he liked about is the failover an on prem SQL Server 2022 into Azure Managed Instance.

Kay’s resolutions is to blog more and to present more at the conferences in person. Kay is looking forward to the second edition of the DATA BASH. Please look at the full post of Kay here.

Kevin Chant

Kevin explains and did a lot of blogging about the feature Azure Synapse Link for SQL Server 2022 already. He did the file test inserting one hundred million rows while Azure Synapse link for SQL Server 2022 is running. He have explained in this post about the file test. He also provided the GitHub repo for the same.

Kevin resolutions this year is all about certifications. He wanted to take more certifications, renew the existing ones and most importantly, this community super hero wanted to educate people about certifications through his blog posts. Look for the complete blog post here. Keep going, Kevin!

Deborah Melkin

Deborah explains about how the DOP configuration settings are important and how it is not always possible to change these settings due to many limitations like client handling the hardware. In SQL Server 2022, the engine will automatically look for the efficient ways to use the MAXDOP and stabilize the DOP for queries as needed. You can read Deborah full post here.

Deborah mentions that she is looking forward to getting some projects with all of us. This is her year resolution. I am really looking forward to this Deborah.

Reitse Eskens

Reitse favorite feature is Synapse Link and how the On-prem SQL Server 2022 can connect to Azure synapse and how the data changes can automatically be captured by synapse and propagate to the cloud data stores in Azure.

Reitse handles the notifications through parking page. He also mentioned the importance of mental health and how the community members can help each other and he advises to reach out to the employer for any help needed. He suggests to attend as many mental health events as possible. You can find the complete post of Reitse here.

Chris Johnson

Chris mentioned that the last post he have written was TSQLTuesday #138 and my invite has bought an interest in writing the post again. Thanks for taking your time Chris in writing this post. Chris is looking forward to learn about the new features in SQL Server 2022 in the coming months and is really excited to learn more about the latest features.

I am very much impressed on how much Chris is willing to work on his personal development. You can read the list of the things Chris wants to accomplish in this year. Great list Chris. Good luck on your goals. Don’t miss reading the Chris working list for this year here.

Jiri Dolezalek

This is the first ever T-SQL Tuesday post from Jiri. Welcome to the party, Jiri. Jiri wrote about my favorite feature, Query Store and looking forward to know how the Parameter Sensitive Plan optimization works.

Jiri believes in the consistency in doing any tasks and doesn’t like resolutions. Jiri says if you wanted to do anything, there is no specific time and place to get started. It can be any day and any time to make the necessary change and improve. Find the full post of Jiri here.

Andy Yun

Andy had attended the private SQL Server 2022 workshop taught by Bob Ward few weeks ago and as Bob Ward presented the last slide showing Purvi’s list, Andy was impressed with the feature added- Instant File Initialization (IFI) for Transaction Logs. Know about this feature in his full post here. Thanks Andy for sharing your learnings.

Chad Callihan

Chad favorite feature is optimized plan forcing by Query Store feature. Chad mentions that this feature optimized plan forcing is on by default for any databases created on SQL Server 2022 and shows how to configure it using T-SQL.

Regarding the resolutions, Chad wanted to more focus on reading the technical books this year. For anyone who have hard time getting up early in the morning, Chad recommends Sunrise Alarm Clock. Great recommendation Chad. This will help people like me who have hard time waking up early in the mornings.

I learned something new today as I read all of these amazing blog posts from SQL Family. I learned that most of them doesn’t embrace the idea of so called “new year resolutions” but they believe setting the goal and working for the goal consistently is the best way to look at it. Doesn’t matter what time you set a goal and when you get it started, working for it is important.

Hope you enjoyed reading the learnings from the SQL family members about their favorite features in Azure SQL and SQL Server 2022. Also, the new year resolution ideas!

Thanks for reading!

This below post is written and authored by my dear friend Chris Wood. I am very happy to post his writings here on my blog. Credits of this post all goes to Chris Wood. I am just publishing it here.

Thanks to Deepthi Goguri for the idea behind this edition of T-SQL Tuesday. The official title is “What’s your new favorite feature”. 

When I saw the topic, I just knew I needed to tell others about a situation I had experienced. I don’t blog and I’m now retired but I would like others to understand my experience. 

In my last gig I experienced several situations that used nested views. This approach may make some situations easier as you get to call one piece of already written code rather possibly copy in bad code. On the downside is performance. We were running SQL2019 at the CU16 security fix build with the databases at 2016 DB compatibility level and the Legacy CE set to ON. When a query was executed that went down 3 or 4 levels of nested views it would take a long time to actually create the execution plan and start returning rows. If I changed to the newest CE rows get returned much quicker but the estimated number of rows to be returned is higher with a small performance improvement. 

So my most looked for feature would actually be 2 new features. SQL 2022 brings both DOP and CE feedback options that can eventually add hints to the execution plan for later executions. I had seen Grant Fritchey mentioned Cardinality Feedback recently Monitor Cardinality Feedback in SQL Server 2022 – Grant Fritchey (scarydba.com) 

Both of these are controlled by running at the compat level of 160 and by using ALTER DATABASE SCOPED CONFIGURATION options. The options are SET DOP_FEEDBACK = ON and SET CE_FEEDBACK = ON. The CE feedback can also be affected by the query having a coded hint or a query store hint or the execution plan is forced.  

I am reading this from Grant’s Sixth Edition of his SQL Server 2022 Query Performance Tuning and checking against Databases – SQL Server | Microsoft Learn 

As I mentioned earlier I am now retired, this happened just after SQL 2022 RTM was released so I have no idea what could happen with the nested views. 

Chris

Happy new year to everyone. This year January T-SQL Tuesday is hosted by Raul Gonzalez asking us to write about the scenarios which are not necessarily the best practices but scenarios where we had to implement the practices which are supposed to be avoided. Thanks to Raul for bringing this topic as I have some interesting scenario related to replication where I had to replicate the data from SQL Server 2012 to SQL Server 2000. I know, its the old server SQL 2000 which is unsupported.

According to the best practices from Microsoft, it is recommended that the transactional replication can be compatible and supported in going two versions higher including the same version (SQL Server 2012/2014/2016) or two versions lower (SQL Server 2008 R2/2008).

The scenario that I faced was little challenging. We had SQL Server 2012 production server replicating data to a Server 2000 which is used for reporting purposes. Subscriber SQL Server 2000 used by the reporting team were not ready to upgrade the Server as they need to rewrite their entire application as it was using vb6 code. They need a strategy where the data can still be replicated without upgrading the Server.

As I researched, I found that it is not compatible version but planned to test the replication to see if somehow it works. I tested the replication between SQL Server 2012 as a publisher and SQL Server 2000 as subscriber. I was able to setup the transactional replication between the servers for the database but found during the initial initialization snapshot, the ANSI_PADDING setting in the snapshot generated .sch files caused the issue while the distribution job runs. The setting was OFF for the .sch files which I need to turn back on. Please see the below one of the errors I have faced in my situation, took the error reference from here.

“SELECT failed because the following SET options have incorrect settings: ‘ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
Get help: http://help/1934″

It is easy to fix the .sch file manually if it is just one file that needs to be changed for the setting ANSI_PADDING from OFF to ON but there are many .sch files with the same setting “OFF”. The most tedious part is that the error only shows one .sch file at a time. I need to fix the .sch file digging into the details of the error and then run the distribution job again to see the next error for the .sch file.  I found a very handy PowerShell script answered by Asaf Mohammad in Microsoft forum where you can place this script as the last step in the snapshot job. Once the snapshot of the database is created and the .sch files are generated, this script will search for all the .sch files with ANSI_PADDING setting and edit the files having the settings from OFF to ON. This process happens automatically when ever the new snapshot is generated or the reinitialization of the subscription happens as it forces to create the new snapshot of the database.

Once I fixed this error, I was able to replicate multiple databases from SQL Server 2012 to SQL Server 2000 implementing the job step for each database snapshot job. I was able to solve the problem by using the PowerShell script. Since then, I did not see any specific file errors related to this error.

This was one of my real time experiences where I had to walk against the recommended best practices and found success in setting up the replication with some issues which I was able to resolve. I am looking forward to reading the experiences of other SQL family members in seeing successful results in such scenarios.

Thanks for reading!

Thank you Garry Bargsley for hosting the December month of T-SQL Tuesday!

Garry wanted us to write about the year end checklist for the Database administrators. I would like to write about the checklist I prefer to follow every month of the year.

Some of the important monthly checklist are as follows:

1. Backup validation: Making sure your database backups are valid. You can do that by adding CHECKSUM while taking the backups, verify the backups regularly using VERIFYONLY option and also test the restore if it works properly by actually restoring the backup on different environment. Make sure you run the DBCC CHECKDB to be extra safe in making sure your backups are in consistent state.
2. Database monitoring: Making sure the monitoring tool you use are not missing any of the instances. Making sure the alerts are triggered properly and email filters are in place to receive those alerts.
3. SQL Server Security updates: Making sure your Servers are patched properly and all the services are working properly after the patching. I mention this because we recently found an issue with Master data services unable to connect to the database after SQL Server Security patching on either SQL Server Server where MDS database is hosted or on the MDS application server. We need to manually go ahead and update the database from the MDS configuration manager. To run this step of updating the database after patching, the user who runs this update needs to have super user permissions to MDS. If you are planning to take the vacation, make sure these servers specific to MDS are patched and properly updated. If you are in vacation and other DBA’s doesn’t have enough permissions to update the database or if they don’t have proper documentation to the procedures, it would be a disaster.
4. Documentation, please: No matter how small the task and the resolution steps might be, having proper documentation of steps for troubleshooting any issue helps the DBA team members have peace of mind while resolving the issue. Make sure documentation is in place and the location to these documents are shared with all team members.
5. Job failure notifications and please no success notifications: Make sure you only receive failure notifications and disable if you have any success notifications setup on any of the servers you built. Make sure the job owners are valid.
6. Check for any password expirations: If any of the AD accounts are the owners of any jobs from task scheduler at OS level and if those accounts has password expired, you will get continuous notifications for those failed jobs due to password expiration. Making sure changing those passwords before going to vacation helps. I mentioned this example because I have seen cases in my past company where emails are bombarded with the failure alerts due to password expiration.
7. On-Call: If you plan to take vacation and if your DBA team is small, make sure there is a backup for the On-Call person just in case. Having the updated contact information in outlook teams where everyone can access your contact information is crucial.

These are some of my monthly activities to check. I am looking forward to read the posts from other SQL family members on the same topic. I am sure there will be plenty of activities to take a note of.

Thanks for reading!

Thanks to Tom Zika for hosting this month of T-SQL Tuesday! Tom asked us to share about the tips for the perfect production code.

Below are some of the tips I would like to share for the better production ready code:

1. A production ready code is always readable with detailed comments of what changes are being made to the existing code. Detailed documentation is important as this code is going to stay for years moving forward. Why, when and who changed the code needs to be documented. I have seen situations where these obvious details are being missed in production code. Code is here to stay, Developer who have written the code may or may not stay in the same company.

2. Best practices for commenting, I highly suggest you read the BrentOzar excellent post here.

3. Check for the execution times based on the DEV, TEST data. I have seen scenarios where the data size in the DEV and TEST environments are not same as production data. This can cause the difference in the execution times from lower environments to production. It is always suggested to test your code in lower environments having same amount of data as production.

4. Unit testing is important to find out if there will be any resource contention or concurrency issues ahead of time before implementing the code in production. Please find the free unit testing tools here.

5. Error handling your code. You need to have procedures in place to handle and capture the errors if in case you encounter the errors. Rollback procedures should be in place. Please read the details here. This is an excellent article on transaction handling in SQL Server.

6. Tune your code before going to production. Check for the indexes that you can create to improve the performance. Check if there are already existing indexes with the columns before creating the indexes, you may be creating duplicate indexes.

7. For better readability and style of your code, check these formatting tools here.

8. Give the sensible alias names for columns in the code.

9. Test all the use cases. Do you remember any situations where you had deployed your code in production and it failed and now everyone blames the testers and developers for not checking every use case scenario. Thorough testing on all use cases is important for a successful production deployments.

10. Do not run the code you see online directly on production. I know, I know, you will say never! but even the code from the legitimate website you always use, please check the entire code, test the code in test environments before thinking to use the same sample code in production.

These are some of the things that you can check if your code is ready for production deployment. I am looking forward to read the posts from other SQL family members for this month T-SQL Tuesday!

Thanks for reading!

Thanks to Glenn Berry for hosting this month T-SQL Tuesday. Glenn wanted us to write about the new features that we are excited about in the SQL Server 2022. Please find the invitation here.

I am very excited about the performance capabilities in SQL Server 2022. When we talk about performance, you know how much Query Store feature has actually helped in finding and resolving the slow performing queries since this feature got introduced in the year 2016.

If you already used the Query Store feature for your databases, you know it needs to be enabled at the database level. The primary use of this feature is to capture the execution plans and execution stats information for your queries over time so you can identify the regressions between the execution plans and take this information to fix most troublesome queries easily.

Starting SQL Server 2022, the Query Store feature is going to be enabled by default when you create new databases. If you need to migrate databases from older SQL servers to SQL Server 2022, then you need to still manually enable this feature.

In SQL Server 2019 and below versions, there is no support for the read only replicas for availability groups, meaning the query data is only collected to Query Store on primary replica and not the readable secondary replica. In SQL Server 2022, the query data is collected into Query Store on the secondary read only replicas for availability groups. Query Store hints can also be used in SQL Server 2022.

The other feature is the Query optimization for the parameter sensitive queries. In SQL Server 2019 and below versions, the moment the query executes for the first time, it saves the plan in the plan cache with the parameter value used for the first execution. The next time the same query runs with the different parameter value, the initial plan that got stored in the plan cache will be used. If the parameter that is being used for the first execution is sensitive, then it can generate a plan which can have resource consuming operators that can effect the next executions of the query with different parameters. This is because the same stored plan from the plan cache is reused.

In SQL Server 2022, multiple plans are generated based up on the parameter sensitive values and are saved in the plan cache. Depending on the parameter values, the optimizer will sniff the parameter value and use the execution plan based on the parameter value. This is a very useful feature as it can mostly solve the parameter sensitive query issues as the optimizer choses the right optimal plan for your queries based on the sensitivity of your parameter value.

I did not get a chance to test the performance capabilities in SQL Server 2022 but I am going to create a new demo for one of my upcoming sessions related to performance capabilities and Query store enhancements in SQL Server 2022.

Thanks to Glenn Berry one more time for boosting our interest in trying out the new features in SQL Server 2022 and to share the knowledge to the world.

I am looking forward to read this month T-SQL Tuesday posts from other SQL family members and learn their excitement and learnings from the new features SQL Server 2022.

Thanks for reading!

Thanks to Kevin Kline for hosting this month of T-SQL Tuesday! Kevin wanted us to share about the events that has changed our lives in a better way.

Becoming a speaker has changed my life. I decided to become a speaker during the dark times in my life. Speaking has become a therapy to me. It has helped me pass through the rough times in my life.

I am always thankful to the conference “New Stars of Data” for giving me the opportunity and the platform to speak for the first time as a Speaker. My journey begin from here as a Speaker.

I have presented at many events later. Dataweekender is the second conference that gave me the opportunity to speak. This is one of my favorite conferences as well.

As I speak more, I gained a part of my confidence back. It is a continuous process. I took the step and decided to speak at the lowest point in my life. I sincerely can say that New Stars of Data event is a life changing event to me.

To anyone who wanted to become a speaker but if lack of confidence is stopping you from taking your first steps, please rethink. Confidence only comes by practice.

I can say that very sincerely because I spoke at several events later only to build my confidence. The more I presented, the more comfortable I became. Though I only presented virtually till date, I will carry the same confidence I gained when I present in-person.

When I think about the in-person events, I need to write a bit about the PASS Data Community Summit 2022. I am honored to be selected as a speaker at this year’s summit and I chose to present in-person.

I am very nervous to present in-person for the first time ever and that too for the biggest Data platform summit in North America. I believe having nervousness is a good thing when you are presenting because it will force you to prepare more and keeps you laser focused.

As New Stars of Data gave me a life as a Speaker, PASS Data Community Summit 2022 will definitely take me to the next level as a speaker as I will be presenting for the first time in-person.

Attending and presenting at the Conferences like SQLBits was one of the best experiences. Attending other events like EightKB is a great opportunity to learn internals about the SQL Server.

There are other conferences I have attended and learnt from other speakers.

During the Journey as a speaker, I have met new people along the way and few of them became my good friends for life!

I cherish all the moments…

I am very thankful to each and every organizer of the conferences, User groups and to each person who helped me along the way giving me the opportunity and supporting me at each step in my journey as a Speaker.

I would love to read other SQL Family member posts about this topic. Would love to learn from them how attending or speaking at different conferences changed their lives.

Thank you for reading!

Thanks to Deborah for hosting July month of T-SQL Tuesday!

I would like to share a rant on a situation I faced working on the database Server migrations couple of years ago.

In the past few years, I have done several database and server migrations. As you know, there are many things that needs to be taken care of during the testing phase until we do the actual migrations.

It is not simply the database that we migrate but all the related objects needs to be migrated as well. The agent jobs, SSIS packages, linked servers and most importantly updating the connections to the datasource information, not only to the database related objects but also to the related applications.

If you are using the Servername and database name in the connection strings in all the applications connecting to your database being migrated, this process gets tough and tedious to update once the database is migrated to a different server. It is always advised to use database DNS in the connection strings instead of the IP address or the Servername.

This also applies to the report server datasources as well.

I know it is hard to make the change but if the change is good, it is worth the effort.

During several database Server migrations, I have seen developers feeling comfortable in changing their application code to the new Server name instead of taking the advice of database DNS. I know the amount of work that goes behind to find the objects referencing the server names. Missing any one of the locations can absolutely break the code and mess really hard.

I have experienced many times where developers had failed updating the SSIS packages or failed Linked Server connections once the databases are migrated. This problem can be solved simply by using the database DNS instead of the Server name. You will be only updating the DNS entry pointing to the new server name once you migrate the database to point to the new Server environment.

I know many companies already follow the standards of using database DNS but there are still others out there doesn’t want to make the change and instead fix the problem when applications break.

I believe it is not only important to fix a broken application somehow but how effectively and simply you can avoid causing the applications to break in the first place.

I am very interested in reading other SQL family member posts on this topic!

Thanks for reading!

Thanks to Mala for hosting this month of T-SQL Tuesday! Mala wanted us to write about some of the T-SQL Coding standards.

Some of the coding standards that I mention here are obvious but I know how many times I obviously skip the simple coding standards. I believe every bit of simple things we do every day matters!

Here are few quick but effective coding standards I wish I knew earlier:

2. Error and Transaction Handling: Saved many Jobs out there

Don’t wait until you break something and then figure out there is a way to avoid it in the first place. This was me at the beginning of my career. I learnt it in the hard way. These were all my face expressions at my computer as I write something which looks like a code.

From handling and capturing the errors to transactional handling the right way, knowing how to handle the errors and transactions is crucial when troubleshooting and modifying the data. Learn about error handling here and transaction handling here.

3. Are you inserting large number of rows as a schedule SQL agent job? This is for you

This is simple but effective. Add update statistics step as the next step in the agent job for the tables you just imported the data into. This will help optimizer to chose the right operators in your execution plans with the updated stats.

4. Start using SQLCMD mode, you will thank me later

Did you ever faced a situation where you accidentally deleted the rows in production instead of deleting the rows in development environment? If yes, then keep on reading. Start using SQLCMD mode. After you enable this mode, run your query with beginning

:CONNECT Servername                                                                                                      
USE DATABASENAME                                                                                                              
Your query art here   

Make it as a coding standard, this will help you remember where you are running the code no matter what your query editor is connecting to. If you need to run the same code on different server, you just change the server name from the first line. At the beginning, I felt adding this line at the beginning of the code is kind of pain but then after I get used to it, this has become one of the best standards. As I execute any code these days, my eyes automatically rolls to the first line, looking for the server name in the connect statement. This becomes even more helpful when you want to document the code you are running on different environments (DEV/TEST/QA/PROD). Learn about SQLCMD here.

5. Consolidate the number of Indexes: You could have duplicates

If you regularly implement the indexes from the recommendations provided on the execution plans, make sure to check if you already have an index with all the columns mentioned in the recommendations but just a column recommended not in already existing index. Try to fit in this additional column into the existing index if necessary but do not just go ahead and run the recommended index query. You may already have an index. Regularly check for any duplicate indexes you may have costing you lot of resources maintaining them.

6. Checkout the datatypes of variables used in your queries

The variable datatypes that you use in your queries for where clauses should have same datatypes for the columns you are referencing in your tables. If they are not the same, the optimizer have to go through the implicit conversion as an additional step. This may cause performance impact and may not use the required indexes when it have to. This can take more resources to execute the query and may be harder to find the reason later for why the query is running slow.

These are some of the T-SQL coding standards that I use on regular basis. I am looking forward to read and learn from other SQL family members on their T-SQL standards T-SQL Tuesday posts!

Thanks for reading!