未加星标

[Video] Office Hours 2018/8/8 (With Transcriptions)

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

This week, Brent, Tara, Erik, and Richie discuss troubleshooting port blocking, page life expectancy issues, problems with turning off CPU schedulers, coordinating two jobs across servers, adding additional log files to an almost-full partition, tips for getting a new SQL Server DBA job, using alias names for SQL Servers, database going into suspect mode during disaster recovery, SQL Constant Care “Too Much Memory” warning, index operational statistics, running newer versions of SQL Server with databases in older version compat mode, and more!

Here’s the video on YouTube:

You can register to attendnextweek’s Office Hours , or subscribe to our podcast to listen on the go.

If you prefer to listen to the audio:
[Video] Office Hours 2018/8/8 (With Transcriptions)

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes , Stitcher or RSS .

Leave us a review in iTunes

Office Hours Webcast 2018-08-08 We can’t connect with Telnet. Now what?

Brent Ozar: First up is a mysterious VRP. VRP says, “Frequently, we have an issue of not being able to connect to port 1433. When we check with Telnet…” Oh, I love Grandpa VRP, you’re with me in remembering to use Telnet… “Unable to connect to SQL Server, no ports being blocked from antivirus. After restarting the SQL Server services, we’re able to connect using 1433. What should we do to troubleshoot this next?

Erik Darling: Turn on the remote DAC.

Brent Ozar: Elaborate.

Erik Darling: So usually, when you just suddenly can’t connect and then you restart SQL Server and you suddenly can connect, you’ve hit an issue called THREADPOOL. Tara’s blogged about it. I think everyone’s blogged about it at some point. Don’t feel bad though. You’ve just got to check your wait stats. If you see THREADPOOL creeping up in there, even if it’s like tiny increments, then it’s most likely the problem you’re hitting. It’s usually caused by blocking. It’s usually caused by parallel queries getting blocked because they just take a whole bunch of threads and hang on to them and they get blocked and they hang onto those threads and then, all of a sudden, you’re out of worker threads.

So that’s usually what it is and turning on the remote DAC, enabling that, will allow you to sneak in your little VIP entrance to SQL Server and start figuring out what exactly is causing your THREADPOOL waits. You can run, like, WhoIsActive or BlitzWho or something and off to the races.

Brent Ozar: That’s good.

Tara Kizer: Have any of you guys ever tested Telnetting to the SQL to the SQL Server port when THREADPOOL…

Erik Darling: How old do you think I am?

Brent Ozar: Not when THREADPOOL’s happening though; that’s a great question.

Tara Kizer: I mean, I use Telnet all the time when trying to figure out why I can’t connect to a box, but I just wonder if Telnet would fail when THREADPOOL is happening, because you’re still connecting, just that SQL Server is not allowing you in because the server is out of threads; worker threads.

Brent Ozar: That’s such a cool question. Now, I want to find out but not badly enough that I’m going to go recreate the THREADPOOL waits.

Why is Page Life Expectancy dropping?

Brent Ozar: See, Christian asks, “We have page life expectancy dropping to zero and there doesn’t appear to be a performance dip. I’ve looked for large queries scanning big portions of data along with queries with large memory grants.” Wow, you’re like ahead of two for two, you’re doing good. He said, “What else should I zero in on?”

Tara Kizer: Look at your jobs. See if there’s anything that lines up with when it drops because there’s lots of things that can plummet the PLE, like index maintenance, update statistics; those two. You could also check the error log to see if the, whatever, the DBCC stuff is happening that has wiped it out.

Brent Ozar: Or, when you said error log too, the other thing you could see, maybe something’s forcing external memory pressure, like something else is driving SQL Server low on RAM. Some other process is doing something in SSIS package.

Erik Darling: CHECKDB will…

Brent Ozar: But if nobody’s complaining too, I would go on with your day. Go find the things people are complaining about, like everyone wearing black in the webcast.

Erik Darling: I’m like, what does PLE drop from? Starting from like 100 to zero, then…

Tara Kizer: Yeah, what number and do the math on that because if it’s a number that’s not ever reaching past a day’s worth of PLEs and what minutes see if that number even correlates to how often you’re running some of these jobs. Maybe you’re never getting up to a really high number.

Brent Ozar: Or maybe it’s dropping from 5000 to 4000. Who cares?

Should I leave 2 cores offline for windows?

Brent Ozar: Dan says, “A client with offline CPU schedulers says that they did this on purpose. They want to keep two cores for the operating system. Help me explain why leaving it this way will cause performance problems.”

Erik Darling: How do they know the operating system is only going to use those two cores? What Windows magic do they have? I’ve never seen anyone be able to say, hey, Windows, you can only use these. But maybe they know something I don’t, which is possible; I’ve just never seen it.”

Brent Ozar: Maybe they have some other app that they’ve hardcoded to only use specific cores, although I smell BS too there.

Erik Darling: Yeah, I’ve run into that a few times…

Richie Rump: No programmer’s going to do that of their own volition. It’s like, oh let me go ahead and do core programming, woo.

Erik Darling: I’ve run into that a few times. One person had a bunch of JRE executables that were, like, part of the app on their server and that’s why they left, like, two to four cores offline. Other people have claimed that it’s for SSRS or IS or whatever. I’m like, you can’t just be like, no you only get these; they use what they want. If they can provide some substantive proof that Windows is only using those cores then word-up.

How do I coordinate jobs across servers?

Brent Ozar: This is an interesting one which Richie might be involved with too. Mark asks, “What’s the best way to coordinate two jobs across servers? We’re trying to do backups on one restore and restores on another. We’d ideally like, as soon as the backup job finishes, for the restore to kick off.”

Tara Kizer: Just add another job step to your backup job and have it connect to the other box. You could do a sqlcmd and do sp_start_job on that restore. So the backup job will kick o

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: like,Brent,they,Ozar,SQL,do
分页:12
转载请注明
本文标题:[Video] Office Hours 2018/8/8 (With Transcriptions)
本站链接:https://www.codesec.net/view/587747.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(23)