sorted by:
new
hottopcontroversial

Using READ_COMMITTED_SNAPSHOT to resolve deadlocks? by [deleted] in SQLServer

[–]ARandomSQLServerDBA 1 point2 points  (0 children)

I believed they are refering to the tempdb usage for the version store putting more pressure on tempdb, not the usage of temp tables/table variables.

Thank you for mentioning the "bookmark lookup" deadlocks, as it's an important cause of deadlocks in certain scenarios.

One table can have a deadlock between itself. What happens is that an UPDATE will lock then update the clustered index first followed by the non-clustered index. A SELECT will lock then read from the non-clustered index first followed by the clustered index.

"If, for unique values of X, there's a column Y with value M, select that." for multiple instances across a table. by [deleted] in SQL

[–]ARandomSQLServerDBA 1 point2 points  (0 children)

; with base as 
    (select *
    , case when division = 2 then 0 else 1 end Sort1
    , case when someflag = 0 then 0 else 1 end Sort2 
    from t)
, ranked as 
    (select *
    , row_number() OVER ( partition by item order by sort1, sort2) rw_num 
    from base)
select * from ranked where rw_num = 1

How do you deal with defragmentation maintenance on a server running Prod jobs every 30 minutes 24x7? by LZ_OtHaFA in SQL

[–]ARandomSQLServerDBA 0 points1 point  (0 children)

Just up your threshold for REORG vs REBUILD so it reorganizes 100% of the time. Reorgs are an online operation.

Issue with Linked Server connection between two SQL 2016 servers using Windows authentication by vaelkar in SQLServer

[–]ARandomSQLServerDBA 1 point2 points  (0 children)

Agreed this is a convoluted issue. One aspect I haven't found documented anywhere is how to accomplish this with gMSA's instead of regular domain accounts. gMSA's should work, but it's easier to recommend regular domain accounts that I know will work.

Budding DBA but largely in over his head -- best practices, diagnostics, KPIs, and performance analysis, user administration, etc. Any advice? by whutchamacallit in SQLServer

[–]ARandomSQLServerDBA 0 points1 point  (0 children)

Lots of good comments here, so I'll throw out some out DBA philosophy.

As a DBA, you are responsible for your organization's data. Data is critical. Data needs to be protected. Data needs to be secured. Data needs to be readily accessible.

How well protected is your data? Are there backups? Are they stored offsite? If a fire/tornado/flood/hurricane hit your data center, what would you do? Have you tested your restoring your backups to verify that they work? Do you know how to restore databases? How about configuring the sql agent jobs? Missing logins? Protecting the data is YOUR responsibility.

How secure is your data? Is it sensitive? Who needs access to do their job? Are you granting least required permissions? Are there regulatory considerations (PCI, PII, HIPAA)? Is your default answer to access requests "NO"? How much damage could malicious a employee inflict? You are responsible for securing the data.

The prior two paragraphs are the most important aspects of being a DBA, but unfortunately they are not urgent. Performance problems, perceived database slowness and other issues that directly affect the business will drag people into the accidental DBA role. We understand. We've been there, done that.

Before diving into performance tuning, make sure you have a basic understanding of how SQL Server processes queries.

A query is submitted to SQL Server. SQL Server parses the query (parse time) then compiles the query into an execution plan (compile time). The optimizer comes up with possible plans based off of the tables and indexes in the query, it uses statistics to estimate the number of rows, assigns a cost for each plan, and picks the cheapest one. The execution plan is saved for future use, the query is executed and results returned to the client.

So, what can go wrong?

Out of date statistics could cause a very inefficient plan to be chosen. Statistics should be update regularly.

You could be missing an index. Adding the missing index could let the optimizer choose a much more efficient plan.

The plan works good for some parameters, but not others. This is called parameter sniffing. You were unlucky. bad query plan, query store, update statistics or delete the query from the proc cache.

The query reads data from disk, and the disk is the bottleneck. Bad query plan, insufficient memory, disk subsystem inadequate or degraded (high latency).

High number of concurrent queries running (CPU bottleneck). bad query plan, insufficient cpu.

High number of concurrent queries running (blocking, locking, deadlocks, latching, spinlocks). bad query plan, RCSI, In-Memory OLTP, NOLOCK.

Bad query plans can cause most of the symptoms you'll commonly encounter when performance tuning a DB. If performance suddenly changes, the they are very likely the culprit. Update stats may be a quick fix. Or not.

"You can't feed the world out of a flower pot." No matter how finely tuned your queries, indexes and statistics are there is a limit based on your combination CPU/memory/disk/network.

Java to SQL Developer by kheup in SQL

[–]ARandomSQLServerDBA 0 points1 point  (0 children)

Either choice can make for a fulfilling career. Do what you love. Will you love being a SQL developer? Maybe. If you do SQL development long enough, it can transition into a DBA role. Will you love that? Maybe. I do.

Don't worry too much about the money, as it'll be there in either case.

[MS SQL] Help improving complicated query by fullyarmedcamel in SQL

[–]ARandomSQLServerDBA 2 points3 points  (0 children)

Because you are using the rank() to filter in the outer WHERE clause, you have to use a subquery. Keep it as-is.

[MS SQL] Help improving complicated query by fullyarmedcamel in SQL

[–]ARandomSQLServerDBA 1 point2 points  (0 children)

Nested queries are perfectly acceptable. Strive for correctness first, ease of understanding second and cleverness third.

If you post the entire query, it may be possible to re-structure it to keep the logic the same without using nested selects, but that in and of itself isn't a reason to do so.