Performance Tuning with SQLFacts Tools (2023-04-07)

SQLServerCentral Newsletter for April 7, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Performance Tuning with SQLFacts Tools]( - [Democratizing Self Service Cloud Analytics and ELT Supporting Modern Cloud Data Management Approaches]( - [What are the biggest challenges organizations face in their digital transformation efforts?]( - [From the SQL Server Central Blogs - Converting Types in C#]( - [From the SQL Server Central Blogs - KQL Series – KQL the next query language you need to learn – a video from Data Exposed]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Memory Grant Feedback Changes in SQL Server 2022]( The Voice of the DBA  What motivates you to do your best? Today we have a guest editorial as Steve is out of the office. This editorial was originally published on Feb 17, 2017. I would guess many of you have heard of the stick and the carrot. They symbolize two main ways people are motivated by others. The stick is the threat of some sort of punishment, it could be as simple as a verbal reprimand, all the way to getting fired. The carrot is something positive, usually praise or money or a raise/promotion. I believe these are ways to motivate people to do work, but I doubt they help anyone do their best. I grew up in California. I had a volleyball coach in high school that tried to use a stick approach to motivate us as a team to play better. We were a young team and not very good at volleyball yet. When we messed up in practice, he would have us run sprints. That was the stick. You make a mistake, you run. During a game he started to threaten us, if we didn’t play better, or if we lost we could expect more sprints. His threats didn’t really help us play better. If anything we made more mistakes as we were already tired from all the running we did in practice the day before. We were afraid of running more sprints if we lost and that caused us to be even more likely to mess up. We ended up losing the game. It seems the stick approach often does not have the desired effect. In Phil Factor’s [Confessions of an IT Manager]( there is an article called “Training Your IT Manager”. It is a good read and it displays the carrot approach quite nicely. He talked about his experience watching a Comms team in his company enjoying good interactions with their manager. The manager might start off being quite rough, but over time he would change into a very friendly good tempered person. The Comms team members figured out what carrots they could use with their manager to affect his behavior in a positive way. You can read it for yourself to get the details. The carrot approach seems to move people in a positive direction, but I am still not sure it brings out their best. I would guess you have seen similar stick and carrot experiences in your life and in your professional career. We all need motivation so where does it come from? I believe that as much as these outside motivators such as stick or carrot can get us moving forward, they never truly get us to do our best. I believe that for someone to be motivated to do their best it must come from inside. In essence you have to want to do your best. You have to do your best because you know you are capable of it. You have to care and have some pride in your work. In the end I believe the motivation to do your best comes from you. You choose to do your best. I remember when I was 12 or 13 years old. I had generally been a good student up to that point, but my motivation was starting to wane a bit. As my grades started to drop my father had a conversation with me. I told him I was fine getting poor marks and he called my bluff. He said you won’t be satisfied with doing anything less than your best. He was right. When we don’t do our best, we know it, but often try to justify it in one way or another. In the same way, when we do our best we can have immense satisfaction with the knowledge that we did a job well done. So what do you think? What motivates you to do your best? Or do you have a good stick or carrot story? bkubicek [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Performance Tuning with SQLFacts Tools]( Wingenious from SQLServerCentral The need for performance tuning your SQL Server workloads has never been more critical. As workloads are moved into cloud services, their efficiency has a direct impact on the recurring bills. A workload that consumes more resources than necessary costs more to host in the cloud and affects the bottom line for your organization. [External Article]( [Democratizing Self Service Cloud Analytics and ELT Supporting Modern Cloud Data Management Approaches]( Additional Articles from MSSQLTips.com Many organizations struggle with democratizing access to data and analytics in the cloud, particularly for self-service purposes. As a result, data and analytics projects often require significant IT involvement, leading to delays, bottlenecks, and inefficiencies. Additionally, data analytics, ELT, and BI tools often require specialized technical expertise, making them inaccessible to a broader range of users. To address these challenges, organizations can adopt cloud-based data and analytics platforms that provide self-service capabilities and support modern cloud data management approaches. [External Article]( [What are the biggest challenges organizations face in their digital transformation efforts?]( Additional Articles from Redgate As part of a recent Redgate Summit which focused on data modernization, Grant Fritchey interviewed Pramod Sadalage, a Director at Thoughtworks. They discussed data and DevOps, and the typical challenges organizations come across in their digital transformation efforts. Grant Fritchey shares this Q&A. [Blog Post]( From the SQL Server Central Blogs - [Converting Types in C#]( Steve Jones - SSC Editor from The Voice of the DBA I am not a great software developer. I’m OK, and I do know how to use Google and Stack Overflow well. Maybe my best skill is wording searches well?... [Blog Post]( From the SQL Server Central Blogs - [KQL Series – KQL the next query language you need to learn – a video from Data Exposed]( HamishWatson from The Hybrid DBA's Blog I love KQL so much I even made a video and if you compare it to my last blog post – yeah my hair has changed a bit…And my... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Memory Grant Feedback Changes in SQL Server 2022 What changed with regards to the Intelligent Query Processing Memory Grant Feedback feature in SQL Server 2022? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Pausing an Index I start an index rebuild as an ALTER INDEX with the resumable option and a max duration specificed in SQL Server 2022. If I want to pause this operation, what can I do? Answer: You can wait for the time, use ALTER INDEX PAUSE, or kill the process running the ALTER INDEX Explanation: There are actually four ways the ALTER INDEX operation is paused: - max duration passes without completing the operation - ALTER INDEX PAUSE is used - CTRL+C for the running operation - kill the process for the running operation Ref: Online Index Operations - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [reads for DMVs]( - Hi All, I have created a test table with rowsize ~8K. I have inserted 2 rows and when I read data from the table, then it essentially has to show me 2 logical reads but it showing 6. why is it so? Am I missing anything? Second question is why sp_whoisactive is showing this spid […] [predictive query plan]( - Dear Friends, Please advise if there is a way to get a query plan for a query without running the query.Thank you in advance. Thanks and best regards [Significance of Historical waits report in SQL server Performance dashboard]( - I have the following historical wait report for my SQL server. I have noticed that Wait Category Tran Log IO is consuming 27.09% of percentage time followed by Query Store which takes 24.34%. I want to know if this distribution is normal or should I take any adequate steps to improve the performance of my […] [slow processing]( - Hi Experts, Yesterday, we ran a program and it has processed 1500 records in 2 min. Today, it is running for past 90 minutes and processed only 350 records so far. What should be checked apart from blocking?  Cheers, Bob SQL Server 2017 - Development [Weird problem with substring function over 'text' column]( - In the query below, I get the Invalid length parameter passed to the LEFT or SUBSTRING function error, but can't figure out why. Query: select table1.Id, substring( table2.source_data, charindex('',table2.source_data) + 7, charindex('',table2.source_data) - charindex('',table2.source_data) - 7 ) as X from table1 inner join table2 on (table2.Id = table1.Id) where (table1.in_timestamp >= @dateFromIncluded and table1.in_timestamp < […] SQL Server 2016 - Administration [multiple PAGELATCH_UP waits on data file]( - Hi Experts, What is meant by PAGELATCH_UP waittype ? what does it trying to tell me? what is PFS ? why INSERTS are blocking each other? As per knowledge, insert shouldn’t block each other? Since they are getting blocked , waiting for LCK and so went to suspended state.  Does this indicate a highly transactional […] SQL Server 2016 - Development and T-SQL [Excel style Contains filter]( - Hi, I am trying to do Excel style "contains" in SQL, but doesn't work. There are two forename fields in my two tables. I would like to know if first forename is "contained" in second forename field or vice-versa. The name can be anywhere in the other column. Basically identifying individuals who put in their […] [Remove the Dynamic code from my SQL sproc]( - I have a stored procedure that was created to run dynamically. Other than me manually removing all the dynamic parts of the scripts, is there a script or a way to remove all the dynamic parts of it so I can step thru it and test parts of the script?   [dynamic update to SP]( - Looking for suggestions\examples of how to update a SP that the table used changes each month at midnight. The app creates a new detail table it logs information to. example: sqlt_data_1_2023_03 when 04/01 hits a new table is created called sqlt_data_1_2023_04, and how could I use an automated method to update any SP to the […] Administration - SQL Server 2014 [Import DataBase from Access to SQL server 2014]( - Hi I want to import database form access to SQL server 2014 When adding a database (structure and data), the process does not complete and I get an error (0xc0202009) When adding a database (just structure) without data, the operation is successful, but I do not find the database in the list SQL Server 2019 - Administration [I need to import v7r3 ibm i (as400) info to a sql 2019 database]( - I need to import as400 data into sql 2019 database. In Sql i can run a linked server to access the as400 data fine. When i look for the same data source to copy all the data to a database, i don't see the one i need, any help would be fantastic. SQL Server 2019 - Development [Inserting data into Physical Table on Azure Taking Longer Than Expected]( - We have an issue on Azure with inserting data into a physical table . We have done some benchmark testing and we are finding that inserting to a physical table on Azure, is taking 4 times longer compared to using a User Defined Table Type or Temp Table I wanted to know why this might […] [Monitoring third party database to detect updates without modifying the database]( - Hello folks, Even writing the title, it sounds oxymoronic but bear with me. We have a third party vendor application with it's own front end the business uses. Specific tables from the vendors database are replicated into our SQL server via a vendor managed process. (AWS Data Sync) Historically we have made use of the […] Amazon AWS and other cloud vendors [Amazon Redshift Data warehouse setup and bring data from Apps to Data warehouse]( - We are setting up new data warehouse with Amazon Redshift and building integrations to bring data from enterprise apps' specific databases to data warehouse. Would like to hear from experts are there any best practices to do these integrations consistent, reliable and long term easy maintainable. Appreciated for your thoughts. SQL Server 2022 - Development [Is TemporalTable SysStartTime available before performing an insert/Update?]( - I want to align a Date column (datetime2(7)) with the same full resolution date of SysStartTime column populated by SQL in temporal tables. I know this is the "Transaction Start" datetime, and has full resolution (e.g., 2023-03-31 17:08:08.5344576) I've attempted to use the following: SELECT dt.transaction_begin_time AT TIME ZONE 'UTC' FROM sys.dm_tran_active_transactions dt WHERE dt.transaction_id […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -