In a recent SQLSaturday conference, I walked into the speakers room and asked the question–What is the first thing you change after you install SQL Server? It was interesting to get their take on the server setup and I think you will enjoy the conversation. There are various answers on this one but some of the speakers have mentioned stuffs like set auto grow files, SQL Prompt, SQL parameter class and max memory among others. I would like to thank Kevin Feasel, Jonathan Stewart, Eugene Meidinger, Raymond Kim, Tracy Boggiano, Mindy Curnutt, Thomas Grohser, and Vladimir Oselsky for their suggestions.
“I would say that now I’m basically a broken person without SQL Prompt.”
“One of the things that I recommend all of our customers… is an administrative failed logging attempt alert system.”
Listen to Learn
What people say is the first thing to change about a SQL Server installation.
It should be noted that the suggestion on the auto boost was said in jest. 🙂
Transcription: The First Change
*Untranscribed introductory portion*
Brian Carrig: 226 which is the first thing I would change from the default. Second thing I would change from the default is the set auto-grow all files for user databases. And SQL 2016, the default behavior is that single file growth rather than auto-grow all files. Previously, everybody would have enable trace flag for that behavior that’s ignored now so you have to set a setting that says auto-grow all files.
Mindy: I’m staying away for the totally obvious ones so… let’s do come up with.
Mindy: My name is Mindy Curnutt. One of the things that I recommend all of our customers put on their servers and that I’ve scripted up and I give to them is like a complimentary script is an administrative failed logging attempt alert system. So it basically goes out in it is querying the error log, event log to look for the 18456… What is it? 18… No, it’s 18, failed log-in attempts basically. I think it’s 18456, event class, something severity, whatever. I used to know it at the top of my head but I’ve gotten too administrative. So anyway and then I have it set where if there is like if someone is trying to get in with an account that has administrative level rights, like SA for example, and someone has try to get in with that account with the wrong password more than x amount of times and it’s configurable within a configurable time window. It will send an email to the administrator so you know someone is trying to guess your admin password and you got their IP address and you immediately know that’s happening.
Carlos: What’s a little scary there is I’m constantly surprise that the number of error, SQL server logs that I see where I’m just constantly seeing like failed SA log-ins. Like back to back to back to back… And normally it’s like some machine that just forgot to change their password but it just constantly banging on the server and you’re like, “Hmm, that seems a little weird.” Anybody else, here we go.
Alex: I would recommend customers take closer look what a developer is using on their code to dynamically execute string. The problem what I fixed and forcing C# developers to be more specific especially with SQL parameter class that they are using default and the default is nvarchar. And most our databases we have a varchar which is a trigger convert implicit. Nobody can see it but it cost lots of problem in their performance. So number one I forcing developers if they don’t want to use, it’s called SqlDbType, implementing have to be varchar because by default it’s nvarchar. So to avoid such problem better to use store procedure which is much more manageable or just to keep an eye on your ideal code that’s much more for C# developers and be specific with data types.
Carlos: So that’s kind of an interesting take, right, so like setting a policy before you get going?
Alex: An interesting part they say when I’m done in presentations about. They say it’s
[00:05:00] the best practice to specify data type in request code. I have a code, I wrote a code for them, and guys, that’s what you should do, that’s have to be. So sometimes we’re teaching .NET developers something else.
Carlos: Sure. Here we go.
Jeremy Fry: My name is Jeremy Fry and I agree with the whole room that these changes that people are indicating that are best practice to change or reasons why they would change it. Mine is max memory and the reason why it’s because I am a BI guy, and so other tools need to run on a server analysis services, SSIS. Although I would like to in real world scenario and in a best use case scenario break those components apart where I’m segregating if I have a warehouse my analysis services on its own server. An instance versus on the production server where your transactional information is held and your day to day business is occurring. But in a perfect world, that’s doesn’t always happen so with that being said sometimes I see memory the analysis services utilizes memory is it starts to cut things off at about 80% utilization. And so if you have high level of things happening on your transaction system that’s utilizing a lot of memory then you start to see a bottleneck down the line in other tools as well.
Woman: Ok, nobody said it so I was leaving it there. When I install a new instance of SQL server and it depends upon the resources that are in the actual operating system for the SQL server and of course also what other instances of SQL might be on that server so there is no tried and true setting that I use to put it at but I always go in and look at the cost threshold for parallelism and the max degree of parallelism because the settings that they are currently set at I don’t think are realistic for today’s hardware. And Microsoft, of course, likes to be always backward compatible so those values are kind of sitting at levels that are no longer appropriate for either today’s hardware or the platform anymore. Now what you set them at can vary but you need to look at that I think. Too much parallelism could not, you know, it’s like too much cookies, right?
Carlos: There you go. That’s a knowledge that I haven’t heard before – cookies to parallelism.
Alex: [inaudible – 7:58] package but you’re just killing parallelism. It’s not right for every single, there should be balance. I can’t find formula what the number of CPU and.
Woman: It depends on the code.
Man: Yeah, I start with 25 and I go up as far as a hundred.
Alex: They start it from 80 go through to 300. I told them, guys, I’m only one who is fighting a whole team including manager. I’m opposition. I hardly convince manager to jump over 2014. Skip upgrade 2014. Go over to 2016 directly because 2014 doesn’t do for application. Absolutely, CPU utilization, that’s true. Other than that, 0, just jump. I was like this to be fired because our manager he doesn’t like any oppositions. Somehow I got another back from my Pennsylvania team, he was my manager in Ireland. And the Pennsylvania team backed me up that’s why I still in here. He doesn’t like any opposition.
Tracy: Yeah, definitely query store then. Change the size. It only keeps 100MB of data. Change it up to like 2GB or something and tell it how many days you want to keep because it keeps up to a year. And get it off of the primary file group. There is a connect item for that. I twitted it out this morning so you can find it and upload it.
Woman: Ok, here is one. How about optimized for adhoc workloads? I’ve read that here has never been one negative, somebody is going to like respond, but I’ve read no one has ever ever ever seen anything negative or bad ever ever happened turning that on, ever. There you go, one more.
Carlos: So I do know that [word unclear – 12:29] experienced it once that when he turned it on he actually saw a CPU spike that he couldn’t explain. So you could toggle it, right? Turn it back off, right, CPU would. I can’t remember what the spike was but I think it was may have been negligible. But basically he could flip it and then he would see CPU differences. Now, I don’t know if you eventually tribute that to something else and it was like luck that somebody just happen to be doing something else while he was toggling that. That was the one freaky thing which I need to follow up back with him. But I don’t how he resolved that. I know that’s what [name unclear – 13:09] was complaining about.
Man: The one application that ran the same query plan only twice for every single. The one really efficient. If it comes exactly two times.
Woman: It was a query that checks to see if there were records were there before it actually runs the query. It seems like …
Man: It could be.
Woman: Really, [inaudible – 13:31] You’re like, go to the grocery store to see if they are open and you go back home and you get your car.
Man: I see you’ve met my developers.
Man: This is like one really [term unclear – 13:54] kind of thing. But for SSRS I would advice there are logs for when stuff gets used, and the default, I forgot, I think it’s like a month, maybe two or three but you can set it all the way to like 6 months. And that logs are really useful when you need to go back and say is anyone still using this report, because usually the answer is no. I mean, I know we’ve got a bunch of reports that are just aren’t being used and at some point we need to go back through, and it’s really nice to have 6 months of data that says the only time it was ran was when you ran to if it is still working. So that’s a nice change for SSRS is just get that log because at some point you’re going have to do cleanup because you just get report blown. It’s a problem.
Mandy: This is Mandy again. So that makes me think of having a SQL agent job that once a week or something cycles your error logs so you don’t end up when some things actually not going well and you want to go in the error log and take a look and your server has been up for a year. Yeah, good luck with that, right? You’re
[00:15:00]like waiting, and waiting, and waiting, and like two hours later the thing might come up, right, so awful.
Tracy: Hi, Tracy Boggiano, again. Can we make the Hackathon noise go away and error logs please?
Man: Oh my god, error logs, gigabytes.
Tracy: Gigabytes in a day. System [inaudible – 15:30] points getting full [inaudible – 15:31] gigabyte log file so we only keep 30 days.
Man: All the messages are really scary. Couldn’t identify this file, couldn’t do a check point. All these horrible stuffs here that’s Microsoft, they are like…
Woman: Oh, your favorite settings. First is SSMS, if you like to have it, do certain things, and you want to see sp_helpindex, and you want to see sp whatever, right?
Woman: Don’t you go and setup hot keys. You don’t have to sit there and do that. You have a certain way of working.
Man: I’m not smart enough to do that. I don’t have any ITs that… I know what I like to do, that’s why.
Woman: I know there was one version of SSMS were they got rid of control [inaudible – 16:28] I couldn’t even work because I don’t hit that [inaudible – 16:30] some kind of road thing. You put it back, I think there are so many people like, “Arhaha”.
Kevin: This is Kevin Feasel. I must confess I’ve been lying the whole time. The first thing I do is install/configure PolyBase. The second thing I do is install R. The third thing I do is change the log growth.
Brian: This is Brian again, while we’re doing confessions, I would say that now I’m basically a broken person without SQL Prompt.
Carlos: We got a couple of concurrence over here. Yeah, SQL Confessions, oh boy!
Man: Just consider it the [inaudible – 17:24] DBA you got to buy SQL Prompt, just get it over with, alright?
Carlos: Yeah, I guess I’m interested when they are going to. Is there a competitor for the SQL Prompt. I know that Compare.
Man: I don’t use SQL Prompts.
Carlos: Oh, what are you using?
Man: I use DevArt SQL Complete.
Carlos: Oh, DevArt, ok. Yes, such true, they are there. I feel like I see a lot more publicity on the other tools maybe because of a compare and all, you know.
Man: [term unclear – 17:54] has also a SQL completion tool. I think it is also named SQL Complete.