t-sql Archives - John McCormack DBA

T-SQL STUFF – Taking it to the limit

11th April 2023 By John McCormack 3 Comments

I don’t often go down code based rabbit holes, its just not what I do day to day but a while back, someone asked on twitter’s #sqlhelp hashtag if there was a character length limit to the STUFF function. The documentation didn’t have an answer. For that reason only, I decided to take a look. By some coincidence this month on behalf of T-SQL Tuesday, Reitse Eskens (b|t) asks:

“What are your most fun script, procedures or statements that you’ve written”

T-SQL Tuesday #161 Invitation. Having fun with T-SQL

So, I thought I’d share my code. Spoiler alert I ran it up to 50 million characters on my laptop and it kept working but it started to take a long time (4H, 28 minutes). Going up to these limits is not your day to day typical use of the function, it’s quite unusual and not really a production scenario. I’m not sure this code serves any useful purpose but by sharing, it saves it sitting smugly in my “SillyStuff” folder doing nothing.

What is the point of the STUFF function

There’s no point in me paraphrasing, here is Microsoft’s own explanation: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

So in my example where I am trying to stuff text into 50 million character long strings, it’s a mostly pointless exercise in terms of reality. A good use case might include obfuscating something sensitive like PII data, formatting dates and times with non standard characters or simply inserting a string at a specific place within another string.

The Code

This came about after @PamelaMooney tweeted: #SQLHelp Is anyone aware of a character length on the STUFF function?

I still don't have a definitive answer but I can get it working up to 50 million characters. If you are stuffing beyond that, good luck to you.

DECLARE @var NVARCHAR(MAX) = N''

DECLARE @counter int = 0

WHILE @counter < 200001

BEGIN

SET @var += '500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv'

SET @counter +=1

END

SELECT STUFF(@var, 5, 1, ' Still works! ')

SELECT LEN(@var)

/* This came about after @PamelaMooney tweeted: #SQLHelp Is anyone aware of a character length on the STUFF function? I still don't have a definitive answer but I can get it working up to 50 million characters. If you are stuffing beyond that, good luck to you. */ DECLARE @var NVARCHAR(MAX) = N'' DECLARE @counter int = 0 WHILE @counter < 200001 BEGIN SET @var += '500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv' SET @counter +=1 END SELECT STUFF(@var, 5, 1, ' Still works! ') SELECT LEN(@var)

/*
	This came about after @PamelaMooney tweeted: #SQLHelp Is anyone aware of a character length on the STUFF function?
	I still don't have a definitive answer but I can get it working up to 50 million characters. If you are stuffing beyond that, good luck to you.
*/

DECLARE @var NVARCHAR(MAX) = N''
DECLARE @counter int = 0
WHILE @counter < 200001
BEGIN
SET @var += '500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv'

SET @counter +=1
END
SELECT STUFF(@var, 5, 1, ' Still works! ')
SELECT LEN(@var)

I’m going to leave it there for now, its a waste of CPU cycles if I keep trying to break it. If anyone wants to push this to the limit, please feel free to let me know the results in the comments. I hope it was a fun read, back when I hosted T-SQL Tuesday, I asked about snippets of useful code. Although this is not so useful, it’s the type of topic I like to see offered.

Using ChatGPT for the first time

7th December 2022 By John McCormack Leave a Comment

I played with ChatGPT for the first time today. For those who don’t know what ChatGPT is, I asked it and this was the response.

ChatGPT is an open-source chatbot framework that enables developers to quickly create natural language understanding chatbots using the GPT-3 language model.
https://beta.openai.com/playground

If that still doesn’t make sense, I asked it to explain in a less technical style.

ChatGPT is a tool that makes it easy for developers to create chatbots that can understand and respond to natural language conversations.
https://beta.openai.com/playground

[Read more…]

T-SQL Tuesday #143 Wrap Up

19th October 2021 By John McCormack 2 Comments

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

I learned so much by hosting this and made sure I gave due care to reading every post. It was also a lot of fun and allowed me to interact with people in the community that I haven’t met before. If you haven’t hosted T-SQL Tuesday before, please contact Steve Jones as we are always looking for new hosts.

Wrap Up

Rob Farley – Short and to the point like I asked for, Rob details a quick way to find objects. And he was happy to clear up for the reader that I didn’t mean GOTO as in the old BASIC syntax you could run on your commodore 64. (For me it was an Amstrad CPC464)
http://blogs.lobsterpot.com.au/2021/10/12/go-to-scripts/

Koen Verbeek – Koen shows us numbers tables, tally tables and a dates table. These are really useful constructs for allowing your queries to go “set based”. Essential reading for anyone who cares about performance. https://sqlkover.com/t-sql-tuesday-143-short-code-examples/

Aaron Bertrand – Aaron shows us how he “bulletproofs” his answers for dba.stackexchange and Stack Overflow. db<>fiddle was new to me. I love some of Aaron’s demo database names like [master (Restoring…)]. I ran the create command on my test instance and had to drop the DB right away as it was giving me the chills.
https://sqlblog.org/2021/10/12/t-sql-tuesday-143-worst-metadata

Deborah Melkin – Deborah shows us a really useful debugging trick when creating stored procedures that use dynamic sql. Many of us have been lost in dynamic sql at some point, and this snippet is great at helping you see where you are.
https://debthedba.wordpress.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Kenneth Fisher – Kenneth shares a compendium of previous posts which all require some serious reading. My favourite was “all jobs that ran during a given time frame”
https://sqlstudies.com/2021/10/12/code-examples-t-sql-tuesday-143/

Jeff Hill – Jeff shared 4 great PowerShell snippets. True to this month’s request, they are short and incredibly useful. Want to know what version of Windows you are running on your Server or when it was last rebooted, look no further.
https://sqladm.in/posts/tsql-tuesday-143/

Chad Baldwin – Chad is a newcomer to T-SQL Tuesday and chipped in with a stellar first post. I must admit, I’ve never given much thought to how to format a result set as I’ll usually do it in the client, but when you need to; it is possible as Chad shows. But that’s only the start. There’s too much to discuss in this digest as he also covers tally tables, random numbers and overcoming the divide by 0 problem. Did I mention he also covers docker, monitoring/filtering log files and setting aliases. Cap doffed.
https://chadbaldwin.net/2021/10/12/tsql-tuesday-short-code.html

Andy Yun – Random numbers, random delays (I wonder if Scotrail use this script) and random strings. Thanks Andy for a great post. There are great scripts on their own and for building into more complex ones.
https://sqlbek.wordpress.com/2021/10/12/t-sql-tuesday-143-random-fun/

Kevin Chant – Kevin discusses just how to get the most out Glenn Berry’s diagnostic scripts, specifically in relation to missing indexes. He also shows a create table syntax and highlights how effective it has been for him in his training sessions around Dev Ops.
https://www.kevinrchant.com/2021/10/12/t-sql-tuesday-143-two-of-my-personal-go-to-scripts/

Andy Mallon – Andy stores all of his useful scripts in a DBA database. It’s a popular approach and I was hoping someone would mention this. Andy goes beyond this though and has converted a lot of scripts into Stored Procedures. Whilst having a local scripts folder is great; if you can put your code into a stored procedure in a database which you deploy to all the servers you manage, there is no need to panic and find the scripts when the pressure is on. I must admit I love this approach and I’ll be downloading Andy’s database to look further into it.
https://am2.co/2021/10/t-sql-tuesday-143-my-favorite-short-scripts/

Jason Brimhall – Jason talks about all things endpoints here and I found the code examples so handy. I’ve already used them. Not only can you use them to validate your endpoints, but you can also use them to fix some issues as well. On a personal note, just what I needed.
http s ://bit.ly/3lAOMmF

Tom Zika – Tom shares loads of useful snippets including regex and t-sql. Wow – one regex snippet shows us how to find table variable declarations and turn them into temp tables. You could make a killing selling this one trick to consultants. Tom also shares a mega handy way to check permissions using impersonation as well as a great method to find referencing objects.
https://straightforwardsql.com/posts/short-code-examples/

Mikey Bronowski – Mikey shows us a handy way to execute multiple queries including dynamic ones and also tells us about agent_datetime() function. I have to admit I’ve never used that function but it looks so useful for when you are interrogating those msdb agent job tables. I will definitely be adding it my list. Finally, he shares a useful query for pulling back table data with his added enhancement (however a nice little plug for DBATools hints he now has a better way of approaching this).
https://bronowski.it/t-sql-tuesday-143-short-code-examples/

Todd Kleinhans – Todd focuses on Python and he is the only person to do so. I won’t give away his one liner but it’s just the sort of thing I was looking for. Are you feeling Zen?
https://toddkleinhans.wordpress.com/2021/10/12/t-sql-tuesday-143-import-this/

Mala Mahadevan – Mala shares some top class queries for interrogating query store. Query store has so much useful data that knowing how to get started querying it will be a big win for some.
https://curiousaboutdata.com/2021/10/12/tsql-tuesday-143-short-code-examples/

Chad Callihan – Chad mentioned 3 handy t-sql snippets and then shared a gem for keeping Brent Ozar’s First Responder Kit up to date. (Hint he uses DBATools). DBATools and FRK are amongst the most essential free tools for any DBA and beyond. If you run anything like sp_blitz or sp_blitzcache, it’s worth keeping it up to date and this method shows how to do it in only a few lines of code.
https://callihandata.com/2021/10/12/t-sql-tuesday-143-handy-short-scripts/

Deepthi Goguri – Deepthi shares some of the best of the rest by highlighting some of her favourite community scripts. From help with migrations to troubleshooting replication, it just goes to show that there’s no need to reinvent the wheel when there’s a perfectly good script out there that meets your needs.
https://dbanuggets.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Steve Jones – Did you know that you could get a tally table with just 4 key strokes? Steve shows you how, leveraging on the power of SQL Prompt by Redgate. This is taking snippets to a new level.
https://voiceofthedba.com/2021/10/13/t-sql-tuesday-143-short-code/

Jess Pomfret – Aloha to Jess who squeezes her entry in on time due to the Hawaiian time loophole. Want to find out if certain accounts are local admins on remote servers? Jess shares a quick and efficient method for finding this out. Being Jess, of course she is using PowerShell to make her life easier. I for one will be stealing this.
https://jesspomfret.com/t-sql-tuesday-143/

Eitan Blumin – Eitan takes the opportunity to link to some of his past blog posts which are full of useful code however he doesn’t stop there. With a new entry for T-SQL Tuesday, Eitan shows us how to move database files to a new location in Always On Availability Groups without breaking HADR. Ok at 374 lines, it’s a bit more than a snippet but it’s really great code so we’ll let that one slide.
https://eitanblumin.com/2021/10/13/t-sql-tuesday-143-powershell-move-db-files-alwayson-availability-groups/

Shane O’Neill – Shane also mentions agent_datetime(). It’s a cool function for converting the very user unfriendly ms format that we see in msdb tables. Shane points out it might not be the most efficient function however when you don’t have much data to bring back, it’s much quicker than rewriting the thing. Shane being Shane (Big Powershell fan) also points out a few great PowerShell commands for formatting and sorting and shows how they can be used in conjunction with other commands that yield really useful results.
https://nocolumnname.blog/2021/10/12/t-sql-tuesday-143-short-code-examples/

P.S. I’ve taken every bit of care to check my comments and on twitter but if I have missed your post, please let me know and I’ll include it immediately.

T-SQL Tuesday #143 – Short code examples

4th October 2021 By John McCormack 28 Comments

T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

e.g. I manage a lot of SQL agent jobs. Quite often, I need to find out which job has a certain t-sql string in the command so I’ll run:

SELECT * from msdb..sysjobs sj

JOIN msdb..sysjobsteps sjs

on sj.job_id = sjs.job_id

where sjs.command like 'backup log%'

SELECT * from msdb..sysjobs sj JOIN msdb..sysjobsteps sjs on sj.job_id = sjs.job_id where sjs.command like 'backup log%'

SELECT * from msdb..sysjobs sj 
JOIN msdb..sysjobsteps sjs 
on sj.job_id = sjs.job_id 
where sjs.command like 'backup log%'

Of course, there are many other ways to find this out including DBATools commands but sometime I just revert to memory for convenience.

Another one I like is to get the estimated completion rate of a backup or restore. Now there are better scripts than this but sometimes, nothing beats getting a quick estimation back from a couple of lines of memorised t-sql.

SELECT percent_complete pc,*

FROM sys.dm_exec_requests

order by pc desc

SELECT percent_complete pc,* FROM sys.dm_exec_requests order by pc desc

SELECT percent_complete pc,*
FROM sys.dm_exec_requests
order by pc desc

My invitation to you for this month’s #tsql2sday is…

I would like you to share with the community what your go to script snippets are and why you find them useful. By sharing these, you will undoubtedly be helping someone who hasn’t thought of doing it that way, and hopefully you’ll pick up some handy hints as well.

Any language is fine, not just t-sql
Please share as many as you wish
Perhaps you never do this and always work off saved scripts or convert your snippets to stored procedures? Tell us why this works for you.

*** The Rules ***

Your post must be published on Tuesday, October 12th 2021 (in any time zone).
Include the T-SQL Tuesday Logo and make it link to this invitation post.
Please add a comment to this post with a link to your own so I know where to find it.
Please tweet about your post using the #tsql2sday hashtag.

Thanks for taking part
John

Optimising a slow stored procedure

14th July 2020 By John McCormack 1 Comment

Optimising a slow stored procedure

In this blog post, I describe optimising a slow stored procedure. It’s part of a series of posts by members of the SQL Server community in relation to T-SQL Tuesday. For this month’s T-SQL Tuesday, Kerry Tyler asks:

Tell me (us all, obviously) about something recently that broke or went wrong, and what it took to fix it. Of course, the intent here would be for this to be SQL Server-related, but it doesn’t have to be. We can all learn from something going wrong in infrastructure-land, or how there was a loophole in some business process that turned around and bit somebody’s arm. It doesn’t even have to be all that recent–maybe you’ve got a really good story about modem banks catching on fire and that’s how you found out the fire suppression system hadn’t been inspected in years. Just spitballin’ here. If you’ve got an incident whose resolution can help someone else avoid the same problem in the future or improve a policy as a preventative measure, let us hear about it.

The situation

I received a call out of hours that a key web page in the application was suffering from timeouts. It was a page for managing important finance data and our team in the US were unable to work. I needed to work out what had changed and how to fix it?

Identifying the problem

All http requests are logged so I could took a quick look at the logs to see if there were any problems. It stood out that one stored procedure in particular was timing out. I quickly ran a trace (sorry xe fans) and found that one particular proc was running for 30 seconds every time. (This is the application timeout value). I took the proc name and parameters and ran from SSMS and found that the procedure took 4 minutes to complete.

How to fix

So why had this procedure suddenly gone bad? Well the fact is it was poorly performing anyway and finance users were frustrated on the whole. My first thought was recompile it quickly and see what happens. Like flicking a switch, it was back to around 20 seconds and the finance page would load again, albeit slowly. So, the issue here was that a bad execution plan had been cached and was being used for each subsequent execution.

This is however a very unsatisfactory fix. First of all, you are not stopping the problem from reoccurring. Secondly, you are only improving the situation from broken to slow. Hardly something for the CV.

The next morning, I took a look at the stored procedure code and could see it needed to be optimised. I ran the proc on a test system, and collected the key metrics such as logical reads and cpu duration by running [SQL]SET STATISTICS TIME,IO ON[/SQL]. To simplify the output, I always copy this into Richie Rump’s Statistics Parser.

This output showed me logical reads into the millions for multiple tables. Starting from the tables with the highest reads, I worked through all of the high ones, looking at which columns were being queried and which predicates were being used. I used this information to design and test alternative indexes, each time remeasuring the output from SET STATISTICS TIME,IO ON. Once I had the correct indexes in place, I was able to submit a PR with the changes. Once it went to production, it resulted in sub second page loads which made the finance team a lot happier.

One other thing that I did that is worth mentioning is I used Sentry One Plan Explorer (It’s free). The query plan was one of those intimidating ones, with hundreds of nodes. When I look at these in SSMS, it’s sometime difficult to know where to start. However in Plan Explorer, there is an option to ‘Show Cumulative Costs’ which helps you can see which branches of the plan can be minimised as the have little cumulative impact, rather than the impact of each node within the plan. This makes reading the rest of the plan much easier because it gets a lot smaller.

What is the point of the STUFF function

The Code

Wrap Up

Optimising a slow stored procedure

The situation

Identifying the problem

How to fix

Whilst you’re here, you may find the other posts interesting.