If anyone had any doubts that a panel discussion where Brent Ozar and Pinal Dave would be answering attendees’ SQL Server performance questions would be a success, those were quickly dispelled during the blockbuster Ask the Experts session Quest hosted in June. Joined by Jason Hall, Senior Technical Engineer at Quest, Brent and Pinal kept the conversation lively, informative and fun as they shared their perspectives and best kept secrets on all things SQL Server performance-related – as well as many other topics.
How their journeys with SQL Server performance began
During introductions, we learned that Brent is a Microsoft SQL Master and owner at Brent Ozar Unlimited. He claims to have done “terrible things” to SQL Server in the course of his career, as he learned what not to do. After getting his start in the hospitality industry, he decided he wanted to work with technology, but not be a developer where he’d have to learn new coding languages – so he chose to dabble in databases. He looked for the most expensive thing in the data center, learned as much as he could and waited for it to break so he could demonstrate his worth. His method seems to have worked.
Pinal is a SQL Server performance tuning expert and consultant at SQLAuthority.com. He recounted his early days as a technician working in a noisy data center where he wheeled hard drives around on a cart and had to swap them in and out of servers. He wisely realized that wasn’t where the money was going to be, so he researched the highest-paying job in his organization, which turned out to be a database administrator. He studied to become one and the rest is history.
The format of Ask the Experts was designed to answer attendees’ top SQL Server performance questions. Here’s a sampling of the candid Q/A.
Q: As someone new to SQL Server performance and database administration, where do you recommend I focus my technical training? There’s so much information out there it’s hard to cut through it all. What would you recommend to a newbie getting started with SQL Server?
A: Brent probably summed it up the best when he said “So, a server never gave anyone a pay raise”. His suggestion is to understand what people are complaining about, what it means to them and who’s being the most vocal about it. Then prioritize from there. Go where the users are unhappy and hear what their complaints sound like.
Q: When you get a complaint that the database is slow, what do you check first on the SQL Server, what metrics do you consider, and what does that process look like?
A: According to Brent, start with blocking, because that’s like a ticking time bomb. If someone started a transaction and they haven’t closed it, you can run into problems where the log file just grows and grows and you run out of space. After blocking, look at wait statistics to get a quick lay of the land.
On the other hand, Pinal wants to know what database professionals might be doing on the system themselves to slow things down, like tracking everything possible in activity monitor and profiler. Then, he suggests looking for jobs that might contain hidden transactions like rebuilding or shrinking an index. He recommends momentarily disabling that agent to see if performance rebounds, then you know for sure.
Q: With 180,000 wait types in SQL Server 2016, what’s the best way to get them and focus on the ones that matter? And where do people go to learn more about wait stats?
A: Brent has written sp_BlitzFirst, an open source stored procedure that you can use to take a five second sample of your wait stats. It filters out the ones you don’t have to worry about, and provides a URL for each one to explain more about them. But sp_BlitzFirst is only a snapshot, so he explains why longer term you would want a database monitoring tool that tracks and stores the information in a repository. (Jason noted that was a shameless plug for Quest Foglight®)
Pinal recommended that beginners just need to know the top five wait statistics that they see on their system. Once you understand what those mean, then start capturing history and try to see patterns in how wait statistics behave day to day. As you get more advanced, look at statement-level wait statistics to understand how each query or stored procedure is operating.
Q: If you’ve got a vendor app, can’t change the code or are hesitant to change the code, but you’ve seen query performance, blocking or deadlocking issues, what can you do?
A: Brent answered, “We only get like two magic buttons in SQL Server, but one of them is called RCSI.” This stands for read, committed, snapshot, isolation and it lets readers and writers co-exist. By default, in SQL Server, readers block writers and writers block readers. What a lot of vendors don’t know is that their selects are causing locking problems. With RCSI enabled, readers and writers can coexist. He suggests asking the vendor whether they support RCSI and support turning it on. However, there are drawbacks, so be sure to research it before using it.
Pinal commented that in addition to RCSI, when you can’t touch the code, you can also turn to server configuration, data configuration and indexes. Indexes are the place you can look for a short-term solution. However, he cautions that any use of magic will have negative and unforeseen consequences (like in Stranger Things and The Mandalorian).
Q: What three performance counters or metrics do you look at to understand SQL performance?
A: Pinal answered that wait statistics are the number one thing to start with when trying to understand SQL Server performance. After that, he looks at some of the dynamic management views (DMVs), particularly index consumption since that shows how the tables are being utilized. And third, he always checks for disk latency because “when the disc is slow, no matter how much you tune, you’re never going to win.”
Brent shared how he recently did a whole webcast on this as part of Quest’s Database Training Days series called How to Measure Your SQL Server where he goes into detail about the three metrics he uses, how to get them and why they matter. For Brent, the speedometer is batch requests per second. He then looks at the total database size, because advice varies for a 10-gig database versus a 10-terabyte database. And finally, he looks at wait time per core, per second because for every second on the clock, the number of seconds SQL Server spends waiting per core can tell you how overwhelmed it really is. Those three numbers, he considers his “dashboard.”
There were so many other great questions and topics covered beyond the realm of SQL Server performance during the session, their top career advice for one, but you’ll have to listen to the recording to take it all in. It’s really a lot of fun to hear the back and forth exchanges.
Here’s a snapshot of some of the other questions covered.
- How do you predict what the cost of the cloud is going to be?
- How important is index management? Would you err on the side of too many or too few indexes?
- When and where should we use column store indexes?
- What is the largest single SQL database you’ve ever personally worked with?
- What is your favorite non-Microsoft database technology and why?
- Is Oracle ever a better option than SQL Server?
- When is the right time to shrink a SQL Server database?
- What is your one top tip or lesson learned that you would tell a database professional looking to ensure SQL Server performance?
The recording is here to be enjoyed by all.
