A Fine Slice Of SQL Server
TempDB is the SQL Server equivalent of a junk drawer – everyone uses it, nobody monitors it, and eventually it becomes a bottleneck you can’t ignore.
Whether it’s poorly configured from the start or getting hammered by bad execution plans, TempDB often becomes the silent killer of performance. The good news? A few targeted changes can make a big impact.
Read on for some of tempdb’s greatest hits. Kevin also has a few quick tips for tempdb.
Comments closedErik Darling is back with a new episode of office hours:
Do you know of any disadvantages of using a filtered index to filter NULL values? We have a very heavy transactional table, like 10k trans/sec, with a clustered index and one non-clustered index. We don’t have any queries that select rows with NULL values from this table. The DBA team said we should avoid using a filtered index without any proof. What do you think?
Click through for Erik’s answers in video form. I was workshopping a joke around how all of the evidence Erik has of me being mean to him are lies, but couldn’t make it work without riding the line of “Wait…is he serious?”
Comments closedHaripriya Naidu deals with metadata contention:
This feature is specifically designed to reduce metadata contention. Note that adding data files will not resolve metadata contention, as that addresses a different type of contention.
You can learn more about enabling this feature and its benefits here.
A company I used to work for was a perfect candidate for this, except that the limitations meant that we couldn’t actually use it. We ended up switching some of our most frequently recurring temp tables and table-valued parameters to memory-optimized user-defined table types and got us out of our metadata contention mess without using this feature.
Comments closedWhen you first begin your journey into Oracle performance tuning, you quickly discover that you’re not just working with databases, you’re deciphering a complex system of rules, statistics, and estimations. I was reminded by Connor McDonald in a post he made on LinkedIn about one of the most influential turning points in my career as a database professional came when I encountered Wolfgang Breitling’s 2003 paper – A Look Under the Hood of the CBO: the 10053 Event. At a time when I was focused on tuning queries for speed, Breitling’s work redefined how I thought about cost, and more importantly, how Oracle thinks about cost.
Click through for more information. Although SQL Server’s cost estimation process is different, the underlying reasoning and many of the mechanisms involved are the same.
2 CommentsHaripriya Naidu is trying to slam a lot of transactions through the same door:
When operations wait to acquire a latch on a page, you’ll see a wait type called
PAGELATCH. A latch is a lightweight lock.PAGELATCHwaits typically occur in TempDB on pages like PFS, GAM, SGAM, or system object pages.Normally, you won’t see
PAGELATCHwaits in user databases because user objects don’t usually experience the same level of concurrent inserts/updates/deletes as temp tables do.But, there is one case where this can happen:
When many concurrent transactions try to insert into the last page of a table, they all compete for a latch on that page. This results in last page insert contention.
Read on to see when this happens, as well as a demonstration of it. Haripriya then uses a bit of functionality that is available in recent versions of SQL Server to resolve the issue.
Comments closedKellyn Gorman continues a series on performance tuning in Oracle:
Let’s break it down:
- RESMGR = Oracle Resource Manager
- CPU Quantum = The time slice a session is allowed to consume CPU
This wait event indicates that a session is throttled by Oracle Resource Manager because it’s trying to use more CPU than its current consumer group allows. This is intentional: Resource Manager is designed to prevent a single user or group from monopolizing system resources.
Read on to learn more about this wait type and what you can do if you experience it.
Comments closedErik Darling has a new stored procedure:
A comprehensive SQL Server performance diagnostic tool that quickly identifies configuration issues, capacity problems, and performance bottlenecks at both server and database levels.
Erik also has an introduction video on the topic.
Comments closedJeff Iannucci announces a new stored procedure:
Most database folks might know the tempdb database in SQL Server is used for temporary tables, but many folks don’t realize all the other things that use this critical system database.
It’s also used by table variables, cursors, aggregations, joins, and sorts. And by memory spills when you don’t have enough memory. And by integrity checks that you run regularly to check for corruption. And more.
Optimal performance of tempdb is vital, as it is involved constantly with all your database queries. So…is your tempdb configured and running optimally? And if it isn’t, can you tell why?
Click through to see how it works and where you can get a free copy.
Comments closedErik Darling has some doozies today:
Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join?
Click through for the answer to this and four other questions.
Comments closedErik Darling is answering questions again:
My company (using SQL Server 2019 Standard) has an algorithm that keys addresses into a varchar(40) has a cross-reference database that assigns an identity property to each new value, allowing us to post the numeric in our datasets. Production has to search their generated string keys in this database’s table to get the integer key in return. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would your next tuning step be if this was not getting the performance you needed?
There’s a good set of questions this time, so click through for Erik’s answers.
Comments closed