Category Archives: DBA

For this T-SQL Tuesday we are asked to look into our crystal SQL Server ball and predict what will be happening at the time of T-SQL Tuesday #200. I went to the garage, dug that thing out, cleaned it up, and boy it had a lot to say!

Assuming we’re all friends here, and there is some fun to be had with this…

T-SQL and JSON had a baby. All queries in SSMS resemble a hybrid of the two languages.

SSDT has been replaced by VSDT. Nothing has really changed but the acronym. As always, you can still expect some things to break when you do updates.

Microsoft bought NHibernate. You still have all the same issues as before but now you post them to Microsoft Party (it replaced Collaborate…after that replaced Connect) and actually watch them not get fixed. And you can’t post work-arounds in MS Party (so it’s not much of a party).

MS NHibernate still generates SQL queries that are long and redundant, but it’s not handling the TSQL-JSON baby very well. So there’s that.

Microsoft acquired ActiveBatch and it is now called SQL Server Batch and has replaced SQL Server Agent for scheduling jobs in SQL Server 2026. Consequently, companies have been reluctant to upgrade from SQL Server 2023 (especially the ones that have used ActiveBatch).

For the companies that are upgrading, they have found that calling PowerShell scripts from scheduled tasks to be a good way to bypass using SQL Server Batch. Increase the in the demand for DBA’s with extensive PowerShell experience sky rockets!

The rumors back in 2018 proved to be unfounded – DBA’s are still in high demand. All the talk of SQL Server tuning itself turned out to be DTA 2.0.

Microsoft brought back the MCM. And then killed it again the next year.

Azure has been replaced by Rainbow. Data is no longer in the “cloud” – it is in “rainbows.” Pricing is based on the colors of the rainbow and the color names are garnet, citron, lemon, lime, azure, and violet.

PASS still exists. Due to some bylaw changes, elections have not been held since 2019. Grant Fritchey [B|T] is still president and attends meetings remotely from his nursing home.

Just kidding – Grant’s not in a nursing home. That’s just where he says he is. There was some backlash when PASS did away with SQL Saturday events. Grant’s really in witness protection and goes by the name Thomas LaRock [B|T].

Thanks to Adam Machanic [B|T] for hosting the T-SQL Tuesday this month, and for coming up with this whole thing to inspire all of us to write more and continue to share knowledge. While there was absolutely no knowledge in this post, I do hope that I got a giggle from at least one person.

When performance issues rear their pretty little heads I tend to look at the simplest things last. I am the person who is looking at the code first – because in many cases, if your code was better we wouldn’t be having this discussion. Whether it’s a series of nested views or one single user-defined function that doesn’t play well with the optimizer, there is usually something in the code itself that is contributing to the performance issue.

But what about the times when it isn’t the code or you can’t touch the code (I’m looking at you NHibernate…and all of your red-headed step-child offshoots and fellow ORMs). What about the cases when the poor performance situation only occurs periodically, or once a week on the same day, every week, and while you can see the slow-downs in your queries, and all of your waits and plans, the source of the problem isn’t any particular smoking gun – it’s lots of guns, firing off at random times, and the casualties are in your interfaces that depend on the data from the queries.

The one thing I don’t immediately go to is UPDATE STATS – and I should be looking at this. It’s something easy to run and if you still have issues, you have ruled it out.

Over estimation on rows = bad times for the optimizer.

In this case, I ran UPDATE STATS on one table in particular and immediately saw a marked improvement in my test query.

Ahhhh….much better.

This morning I ran the same query and things were back to where they were with performance. This was somewhat disheartening, but the troubleshooting must continue.

But what about auto-update stats? Is this on? Yes, but auto update statistics is only going to update based on a percentage sample of the records in the table. If your table contains millions of records, you may not be updating your statistics sufficiently to see an improvement. I am updating statistics with a FULL SCAN of the table. For larger tables, this may take some time, but you may also see a difference in your execution plan versus only updating statistics based on a sample.

In an effort to rule out whether or not statistics are definitely a factor, I want to UPDATE STATS on all the tables in my query, and at a specific time – sometime the day before we expect our slowdown to occur. I also want to be able to easily see how long the process ran and the duration of the update for each table. I could write all this to a table, and maybe I will do this later, but viewing this from job history is what I want right now – it is easy to look at and easy for everyone else to find and see as well.

Creating a job with multiple steps can sometimes be a bit painful and tedious, if the job is going to have A LOT of steps. With PowerShell, a server name and a database, I can dynamically create this job.

PowerShell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

<#

Name: create update stats job for specified tables.ps1

Author: Amy Herold

Date: 8 February 2018

Purpose: With a specified server, database and list of tables, create a job to update stats.

The result from this is a job that will update statistics on your list of tables, one at a time. All you have to do from there is give it a schedule.

All those steps…courtesy of the magic that is POWERSHELL!

For this script, I was getting my list of tables based on a specific query that was problematic. If you are doing this for a stored procedure, you can query for the list of dependent tables instead, and instead of hard coding the tables in this code, pass in the name of your stored procedure and get the list that way.

I hope if you have a situation where some one-off stats updates are needed you find this script to be useful. As always, comments and feedback are appreciated. 🙂

When I was growing up I remember my mom talking about an old, scary, movie that she saw when she was young. In the movie some teenagers were making prank phone calls saying “I saw what you did and I know who you are.” One of the calls happened to be made to a guy that just killed his wife. Joan Crawford played a woman that was romantically inclined to said murderer. She eventually meets her demise when he stabs her because she knew about the first murder.

How every DBA feels when there is blocking

While blocking in SQL server might not be a felony offense (it isn’t…but it should be – WHO’S WITH ME?) as the DBA you not only want to know what is being blocked, but also who is doing the blocking and what in the H-E-Double-Hockey-Sticks they are doing.

At SQL Saturday Orlando I talked about this very thing and the query I defer to for the information.

IMA GONNA HUNT U DOWN!

It might look complicated but it is actually very simple – query sys.sysprocesses with a cross apply using the sql_handle to get the text of the query, and then an outer apply with the same query again but you are joining to the blocking spid so you can get the text for the query that is doing the blocking. Beyond that, you can filter on various columns and refine your output

WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF?

Of course, I know you can’t run this without the code (and I know that’s why you’re here…because I SAW WHAT YOU DID!)

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SELECTx.textASblocking_text

,x.nt_username

,x.nameASblocking_db

,st.text

,a.nt_username

,d.nameASblocked_db

,a.status

,a.*

FROMmaster.sys.sysprocessesa

INNERJOINsys.databasesdONa.dbid=d.database_id

CROSSAPPLYsys.dm_exec_sql_text(a.sql_handle)ASst

OUTERAPPLY (SELECTblock.text,aa.nt_username,aa.spid,dd.name

FROMmaster.sys.sysprocessesaa

INNERJOINsys.databasesddONaa.dbid=dd.database_id

CROSSAPPLYsys.dm_exec_sql_text(aa.sql_handle)ASblock

WHEREa.blocked=aa.spid)x

WHERE1=1

--AND a.hostname LIKE 'SOME-HOSTNAME%'

--AND a.program_name LIKE 'Some program name%'

--AND a.dbid = ????

--AND a.spid IN (???)

--and a.status not like 'sleeping%'

----AND (st.text LIKE '%text string you want to isolate%')

---and a.blocked != 0

--AND (a.nt_username LIKE '??????%')

If you are looking for a good way to troubleshoot blocking I hope this helps. If you have some folks running queries that are making you stabby, run this, find out what is going on, and then remove their access try to help them so they aren’t making you stabby any more. Then tell them they need to buy you a beverage because they are still alive.

I discovered something last week – I had not blogged about little things that I thought I had blogged about. What the heck does that mean? It means that I tried to reference my blog for something because I thought “I totally blogged about that”…and found out that was not the case.

Starting now, I am fixing this situation. There was something that popped up today that called for a PowerShell script and the Get-ADGroupMember cmdlet – get a list of users from a list of groups. Some users are in there more than once so this needs to be a distinct list, unless you are into manually cleaning up things like this, and then I will be sad for you. Because that is kinda sad.

I originally wrote a script with two arrays (one for the initial list and one for the de-duped list of users), but even though this is quick and dirty, that was a little too dirty. Enter the Group-Object cmdlet – it takes this list of names and groups them. No black magic this time. Just a cmdlet, that comes baked into PowerShell giving me what I need.

What? You wanted the code too? Oh, OK.

PowerShell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<#

Name: activedirectory get list of users from multiple groups.ps1

Author: Amy Herold

Date: 24 October 2017

Purpose: Get distinct list of users from multiple AD groups.

#>

$groups=@('Admins','Managers','Some Other Group')

$people=New-ObjectSystem.Collections.ArrayList;

$people.Clear();

#--------------get list of users from list of groups--------------

foreach($gin$groups)

{

$people.Add(@(Get-ADGroupMember-Identity$g|selectname))|Out-Null;

}

#------------use group-object and get a distinct list of names--------------

$people.Name|Group-Object|selectname

There you have it – quick, dirty and to the point. Enjoy. 🙂

UPDATE: Mathias Jessen tweeted a one liner for this….so no need for the one array! Woohoo!

PowerShell

1

('Admins','Managers','Some Other Group'|Get-ADGroupMember|Group-Object-PropertyName-NoElement).Name

I was trying to do this but was also just trying to get it done, and if in doubt, I slap things in arrays. Thanks Mathias!

Next week I am returning to Orlando not only to visit the mouse at his house, but also to speak at SQL Saturday Orlando! Last year was my first time there and I had a great time. This also happened after the event was pushed back to November 2016 because of Hurricane Matthew.

This year I will talking about deadlocking and blocking – something that is an issue for so many DBAs. Whether this issue is indexes or bad code…or you have been trying in use indexes to cover up bad code, blocks and deadlocks can happen in even the best environments under the right circumstances. If you are going to be at SQL Saturday Orlando come to my session where we will talk about detecting and preventing these arch enemies of the DBA!

This Saturday will mark the fourth year that I will be speaking at SQL Saturday Baton Rouge at LSU. Out of all the SQL Saturday events I have participated in since 2014, Baton Rouge is one of the few that I have been to every year since. Houston is another one…and actually, these may be the only ones only because they have had an event every year.

Not only am I speaking on Automation with PowerShell and Deadlocks and Blocking, but I am also participating in a panel discussion on Careers in IT. I’m excited to be invited along with some of the other speakers to be a part of this. Looking back on my own school days, I knew I would have a career in IT, but little did I know I would detour from a path in development to the world of SQL Server, and becoming a DBA.

How do they make this happen? Work. Lots of hard work. After helping with SQL Saturday Dallas 2015, joining the NTSSUG board in 2016, and then having an organizing role in 2016, I found out how much goes into the planning of these events. If you have attended a SQL Saturday or you are going to in the future, be sure to say “THANK YOU!” to all the organizers and sponsors. If you want to get more involved in the SQL Server community, SQL Saturday is a great way to do that – just show up at the event, find an organizer and tell them that the SQL Kitten sent you to be their humble servant volunteer for the day…or you could just say you want to volunteer and leave out the other stuff because it might make it weird.

UPDATE #1 –
The following changes were made to the PowerShell script for the PASS Summit 2017 session scrape:

Abstract field is now varchar(max). ‘Cause precons get all the chars.

Added SubmittedSession column.

Upper bound increased to include precons.

UPDATE #2 –
Before I could get update #1 posted, it appeared all the general sessions and lightning talks were removed from the submissions page and replaced with submitted precons. Don’t fret – the PowerShell script will still grab all the submitted sessions (for now). There were also some more precons added so the upper bound has been changed yet again.

The deadline for PASS Summit submissions came and went. Either you got yours in or you didn’t. What does this mean? Time to gather the data.

Go get it! You know you want to!

Never having done a web scrape before, this was the perfect subject for my first time – grabbing all the sessions submitted to PASS Summit 2017…and doing it with PowerShell! Here is the script I used for this. I have accounted for the following:

Apostrophes (aka single quote). They will break your insert unless you have two of them, and for some reason, people seem to use them all over the place.

Formatting the string data for insert. No, your data will not magically come out right in your insert with single quotes so you need to add them.

Additional ID and deleted fields.

Speaker URL and ID. Will be using this to scrape speaker details later.

Accurate lower and upper bounds. These were arrived at by trial and error (you’re welcome), as well as the clean up of the data I scraped. More on this later.

Now for the code –

PowerShell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

<#

Name: summit_sessions_scrape.ps1

Author: Amy Herold

Date: 27 March 2017

Purpose: Pull down all the session submitted to PASS Summit 2017. Because data. And because you want to.

14 June 2017

Updates:

Abstract field is now varchar(max). Cause precons get all the chars.

Upper bound to include precons.

Added SubmittedSession field.

Note: You will need to create the following table in a database to write all this to -

There will also be some webids that error out – this means the session doesn’t exist for that specified webid. You will see an error in the output to the PowerShell window, but the script will keep running.

Once you run the script and it completes, you will have your table of session data. You will notice that there are more sessions there than have been submitted, according to the website. If you do a count of the sessions by speaker you will also find that there are more than three sessions (the maximum allowed) for quite a few speakers. Why is this? You are pulling from a data source that contains sessions other than the ones that were submitted to Summit (apparently). This means you will have to clean up the data – I included the deleted field for this purpose. Look at the duration and filter for the 75 minute and 10 minute sessions and update your deleted field accordingly.

After this, you will have sessions that meet the duration, but you will find the speaker is not listed on the submission site when doing an advanced search. Clean the data some more.

Are you done cleansing your data yet? You might think so, but you aren’t. You see sessions that look like they were submitted for Summit but are not on the site. These appear to be sessions that were created and saved, but not officially submitted. You can rule out quite a few of these if you look for speakers that have more than three sessions. Get those counts, check the website for those speakers, and clean your data.

“Good lord, am I done yet?”

No. No you’re not.

There will be speakers that have three sessions in your table but only submitted one or two. This sounds worse than it is, but it is a little painful. How do you do this? You’re going to search the speakers with the advanced search on the website. Do these in groups based on the number of sessions a speaker has, ordering by speaker name. You can exclude the the speakers you have already reviewed in the previous clean-up steps, deleting their names from your list as you go. When you find a speaker with three sessions in your list and fewer sessions submitted, mark the session that was not submitted as deleted. Next, do the speakers with two sessions as they may have only submitted one. Finally, look at the speakers with one session as there may be ones that didn’t officially submit anything.

“But what about the code for the clean-up?”

No. You know how to do this, and may write it differently (and better) than me. My code was ugly and I don’t feel like cleaning it up and posting it here. I gave you the script to grab it all. Stop complaining or I will send you to time-out.

The speaker info grab will be a separate blog post. With the script in this post you might be able to use the syntax and craft your own PowerShell script for this. Or you could wait for my blog on it.

After whining about not blogging enough, I am going to do something about this. Whether it is PowerShell or SQL, simple or complex, I know that others can benefit from my knowledge and expand their skill sets.

And you are like “That’s great Amy…where’s the PowerShell we are here for?” Ok, Ok….keep your drawers on! 😉

Recently I was tasked with gathering the system information from all of the servers at a client. Another opportunity for some PowerShell dominance.

get_sysinfo.ps1

PowerShell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

<#

Name: get_sysinfo.ps1

Author: Amy Herold

Date: 03 May 2017

Purpose: Get the system information from a list of computers/servers, doing one machine at a time and outputting server name

when you can't connect. Output information to a NFO file with the name of the machine.

Notes: Get your list of servers and update the $servers variable below. Make sure they are also formatted the same way as in the sample.

Update the $path variable with where you want to save the NFO files.

#>

<#------------variables you need to change---------------#>

$servers=@('server1','server2');

$path='C:\Where_You_Want_The_Files\system_info\';

<#-------------------------------------------------------#>

foreach($sin$servers)

{

#------as long as we can connect to the machine, get the system info--------

if((Test-Connection-Cn$s-BufferSize16-Count1-ea0-quiet))

{

$filepath=$path+$s+'.NFO';

$cmd="C:\windows\system32\msinfo32.exe";

$args="/computer $s /nfo $filepath /categories +all";

#if you can connect to the server, gather and save sysinfo

Start-Process$cmd$args-Wait;

}

else

{

"Cannot connect - $s"

}

}

With this script you can generate system information files and save them to a specified location. It makes sure a connection can be made to the server first, and then outputs the file. The files are created one at a time, so if you pass in a longer list of servers, you shouldn’t crash your machine. From my testing, this will take some time to run as these files don’t output quickly. Despite that, the output is worth it. This can be modified to pull your list of servers from a file or from a Central Management Server (CMS) instance.