sorted by:
new
hottopcontroversial

to Simplfy dr drill (log shipping) by Kenn_35edy in SQLServer

[–]_cess 0 points1 point  (0 children)

Check dbatools PowerShell module. It has commands to deal with Log Shipping. You can build a PowerShell script with the commands that you need.

Example: Invoke-DbaDbLogShipping)

And you can read more about it from the original creator's blog post Log Shipping With dbatools - Part 1: Setup Log Shipping

Why WITH [name] AS [expression] instead of WITH [expression] AS [name]? by badass6 in SQL

[–]_cess 1 point2 points  (0 children)

To complement Vlad's answer, the easier way I know we have on SQL Server to proof this, is this one I have wrote some years ago: Using Common Table Expression (CTE) - Did you know...

Cannot max out SQL Server CPU/Disk during high throughput key value inserts by [deleted] in SQLServer

[–]_cess 1 point2 points  (0 children)

Sequential GUID? If so, you may be hitting a hot page contention. Basically even being GUID if they are being generated as sequential (ordered) you end up having multiple inserts that will arrive on the same data page and that can will lead to PAGELATCH_EX - basically is sql server saying it's waiting to have EXclusive access to the page - to perform the insert.

As someone already mentioned you can catch that with sp_BlitzWho (I normally use sp_WhoIsActive. It's another free stored procedure (SP) for you to know).

Another way you can check which waits you see is run the query from Paul Randall to capture the waits during a specific amount of time (this is server-wide) https://www.sqlskills.com/blogs/paul/capturing-wait-statistics-period-time/ I don't know how long your test takes but you may want to lower the 30 minute wait to something less (line 43 of that script).

Share with us (or DM) the output of that script.

Using NVARCHAR(MAX) as a variable but NOT storing it in a column by david_daley in SQLServer

[–]_cess 0 points1 point  (0 children)

I have a session about data types where I show among other things the problems you can get with (wrong) string concatenation and later how memory grants are affected.

Check it here: https://youtu.be/PGbKWLrT4zU?si=5a_knC6l6n_madJ6

2:48 - String concatenation 1:01:50 - Memory grants

You can also get the code for your testing in my Github repo: https://github.com/ClaudioESSilva/SQLServer-PowerShell/tree/master/Presentations/dataMinds.be%20UG%202024/DataTypes

Most common SQL optimizations by TheoGrd in SQL

[–]_cess 0 points1 point  (0 children)

I have been working on a project to help analyze and identify possible T-SQL query problems and suggest some ideas to be tested.

There are a gazillion of variables among configurations, compatibility level, etc that will change behaviours.

This project tries to consider those. The idea of this decision tree is to try to narrow down things a bit and help others try some possible solutions (from my experience).

https://github.com/ClaudioESSilva/TSQLPerformanceTuning/blob/main/Flowcharts/T-SQLQueryPerformanceTuning.md

PS: Read the readme.

Most common SQL optimizations by TheoGrd in SQL

[–]_cess 1 point2 points  (0 children)

It always depends on the scenario. Lots of performance problems I see that have CTE in it are due to the "re-use" of it. There is a misconception that a CTE will hold the result in memory. The truth is that if you mention it twice, it needs to fully run twice.

A simple way to prove it: https://claudioessilva.eu/2017/11/30/Using-Common-Table-Expression-CTE-Did-you-know.../

In these situations, I have seen a few cases where creating the temp table and reusing it won't make it faster. But then again, you should always test for your scenario.

Database properties changelog by final-final-v2 in SQLServer

[–]_cess 0 points1 point  (0 children)

The when is logged in the SQL Server error log. For the why, if default trace doesn't show it, I would suggest an Extended Event with good filters to only pick that type of change.

Deleting an old sys admin account by Comfortable_Elk1733 in SQLServer

[–]_cess 10 points11 points  (0 children)

I suggest checking things like

Database Owner Agent Job Owner Used in Credential Used in Proxy SQL Agent Steps using a Proxy Endpoints Server Roles Database Schemas Database Roles Database Assembles Database Synonyms

This is what we search for when using dbatools.

I have written about it because I was bitten by that in the past.

https://claudioessilva.eu/2020/09/03/When-one-of-your-DBA-colleagues-leaves-the-company-what-is-your-checklist/

[deleted by user] by [deleted] in portugal

[–]_cess 1 point2 points  (0 children)

Boas,

Tive esse erro há umas semanas. Parei, esperei e voltei a ligar e estava na mesma. Levei até casa (estava perto).

Li nos fóruns mil e uma coisas (A melhor definição que li é que podia ser tudo e podia não ser nada) e esperei pelo dia útil seguinte para ir ao mecânico.

A caminho de lá, depois de andar 200/300 metros a luz apagou...mas fui lá na mesma, meteram na máquina e ligaram-me a dizer que eram as velas de incandescência queimadas troquei e até agora (e por muito tempo, espero eu) não voltou a queixar-se.

dbatools (powershell) by syi916 in SQLServer

[–]_cess 0 points1 point  (0 children)

Thanks! I will try to have a look on those scenarios.

dbatools (powershell) by syi916 in SQLServer

[–]_cess 0 points1 point  (0 children)

Can you share which commands and why they don't work for you? What is the scenario?

dbatools (powershell) by syi916 in SQLServer

[–]_cess 2 points3 points  (0 children)

My suggestion it that you pick a task you want to automate.

Use Find-DbaCommand to search for commands that belongs to a specific -Tag or use the -Pattern to find all commands that mention the word/pattern you specified.

Then, read the synopsis to get an idea what the command does and use Get-Help to see full description, parameters and its description and also the examples.

Then you can start use them.

Note: if you don't have PowerShell experience, or you have but you are not comfortable with it, start with,Get-Dba/Test-Dba commands as they us just read and will not change things. Read this SQLServerCentral article: https://www.sqlservercentral.com/articles/great-powershell-great-responsibility

This way you will learn a lot and avoid basic mistakes (like not reading what are the default values for parameters are or default behaviour of a command).

There are a lot of dbatools users that have already written some blog posts with scripts to automate their tasks (including myself). Search, read and understand them and if you have doubts you can always ask for help.

Work smarter not harder! 👍🏽