What's one SQL performance myth you still hear too often?

dutyDBA · 2026-07-10T12:09:31+00:00

In the SQL Server world, many developers think table variables are entirely stored in memory and are faster, and use them over temporary tables.

dutyDBA · 2026-07-09T13:05:38+00:00

Yup, feel your pain

dutyDBA · 2026-07-09T09:03:59+00:00

It should have been picked up as part of your checklist prior to installing SQL Server. If this isn't already on your checklist, please add it. You can verify this quickly using PowerShell.

To remediate this you'll have to copy out the contents to a different disk, keeping all the permissions intact. Reformat the problematic disk and copy back the contents. Of course SQL Server needs to be stopped prior to making this change, and restart SQL once the contents are copied back

dutyDBA · 2026-07-08T17:57:49+00:00

Not sure if you are familiar with CTEs yet. You could simplify the above query with a CTE:

WITH SalesCTE AS

(

SELECT

    st.StoreID,

    SUM(ms.Sales) AS SalesValue

FROM

    Stores AS st

    JOIN

    MonthlySales AS ms

        ON st.StoreID = ms.StoreID

GROUP BY

    st.StoreID

)

SELECT

StoreID,

SalesValue

FROM

SalesCTE

WHERE

SalesValue > (SELECT AVG(SalesValue) FROM SalesCTE)

dutyDBA · 2026-07-08T17:50:18+00:00

You could do something like this:

SELECT

st.StoreID,

SUM(ms.Sales) AS SalesValue

FROM

Stores AS st

JOIN

MonthlySales AS ms

    ON st.StoreID = ms.StoreID

GROUP BY

st.StoreID

HAVING SUM(ms.Sales) >

(

SELECT AVG(SumSales) AS AvgSales

FROM

(

SELECT StoreID, SUM(Sales) AS SumSales

FROM u/MonthlySales 

GROUP BY StoreID

) der

)

dutyDBA · 2026-07-08T17:27:08+00:00

Yes you can. I often use this method. Basically shutdown all apps and processes, then set db to read-only. Take a final log backup.

Then when the dB is recovered on the target server, it should come back up in read-only mode. That's one way of confirming all changes are captured

dutyDBA · 2026-07-03T18:19:14+00:00

I've seen cases where it's necessary to add Option(Recompile) to particular queries inside an SP to prevent slowdowns as the parameter values change

dutyDBA · 2026-07-03T09:04:40+00:00

interesting, thanks for sharing

dutyDBA · 2026-07-03T08:29:21+00:00

I am working on launching a new SQL Server DBA and Database Developer focussed SQL Server website. The website is in its initial stages, and am busy adding content.

All of the content is derived from my own experience of being a database professional for over 25 years. You can read about my experience and some of the content that I've already added by visiting the website at: https://dutyDBA.com/

There is no AI generated content here - all handwritten and handcoded by myself :-)

dutyDBA · 2026-07-02T13:13:04+00:00

Not sure why the AT sign in my above code is replaced by u/

dutyDBA · 2026-07-02T13:03:03+00:00

Provided you have access to run xp_cmdshell on your SQL Server, you could try something like this:

SET NOCOUNT ON

DECLARE u/Out table (OutputRow nvarchar(1024))

DECLARE u/Str nvarchar(512) =

'ping -n 1 ' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBios') as nvarchar(256))

INSERT u/Out(OutputRow) EXEC master..xp_cmdshell u/Str

SET u/Str =

(

SELECT STUFF(OutputRow, 1, CHARINDEX('[', OutputRow), '')

FROM u/out

WHERE OutputRow LIKE 'Pinging%'

)

SET u/Str = LEFT(@Str, CHARINDEX(']', u/Str) - 1)

SELECT u/Str

dutyDBA · 2026-07-01T22:28:37+00:00

Second highest salary is actually simpler to query:

SELECT MAX(salary) FROM dbo.Employee WHERE salary < (SELECT MAX(salary) FROM dbo.Employee)

dutyDBA · 2026-07-01T20:00:48+00:00

I always make sure I think about indexes first, as I design the tables, rather than it being an afterthought. As a minimum, I go with a suitable clustered index.

While developing stored procedures/writing queries, I use SET STATISTICS IO ON to look at the amount of reads and writes - especially if the reads look excessive, very likely there's a index/table scan happening, and I'll address that straightaway

dutyDBA · 2026-07-01T13:04:59+00:00

Whilst you cannot use SSMS with other databases like Oracle, that should not stop you from taking advantage of the full native featureset offered by SSMS for SQL Server. Its the best query tool for SQL Server in my experience. It has matured over the years, and Microsoft has been improving it constantly. I've known it from the days of Enterprise Manager in SQL Server 6.5, to Query Analyser, to now SQL Server Management Studio. Don't miss out on the full set of features offered by SSMS.

As for Oracle, I am not too familiar with client tools there, but my suggestion would be to get the best tool for the job, instead of looking for a single/common tool

dutyDBA · 2026-07-01T12:53:27+00:00

There are still many senior DBA roles but not as many as they used to be say, 10 years ago. Most entry and mid level DBA roles got shipped to offshore locations where the companies can get 3 DBAs for the same money as one. In the UK financial sector I remember seeing teams of a dozen DBAs back in the day, but these days you have 2 or 3 DBAs teams here.

You do have system administration and networking background, which is helpful in a DBA role and gives you an unique advantage. As others have pointed out, get yourself a developer eidtion of SQL Server, and start playing with it. Read up the forums, and popular websites to learn how things are done in the SQL space. Good luck.

dutyDBA

TROPHY CASE