Have you tried restarting your computer SQL Server?

Everyone at one point or another has had this experience – you have to call technical support for assistance with your operating system or an application and get posed with the question “Have you tried restarting your computer?”

“Ummm…yeah. I did that. I also power-cycled my modem, searched the internet, hacked the registry and walked counter clockwise around my chair three times. I just thought I would call you to chat.”

Anyways…

Occasionally, the same logic is what is needed to resolve issues with SQL Server – restarting the SQL Server service. While we want to think that being on a fairly up-to-date version of SQL Server means things like this don’t happen, this is still software written by humans and every possible situation cannot be accounted for in testing. Sometimes you might find documentation that this is needed. Other times, the solution to the issue you are trying to solve is not as clear.

A couple of examples of a documented situation would be when updates to the server require a reboot or when a known issue in SQL Server is able to be immediately resolved by a restart of the service. Undocumented situations that can arise from different situations can range from changes to server settings that are not taking effect to changes in database mail. I have seen both of these situations in the past year – a server setting change was affecting replication (resolved by a restart) and database mail changes not taking effect, causing mail not to be sent and the mail queue to become backed up (another lesson learned here – after the restart of the service all those messages in the queue will be sent; if you don’t want this to happen the queue needs to be cleared out prior to restart).

Unlike rebooting a computer, restarting this service (or the server itself) is typically something that is done only where the circumstances dictate that it is required. Outside of this, restarting SQL Server is typically done as a last resort and is the last thing that the DBA might think to do. Depending on the server and the processes that communicate with the SQL Server instance, the business other resources may need to be not only notified of the restart, but also why it is needed in the first place. This is made more difficult by the fact that it may be unclear whether or not the restart will solve the problem at hand.

While it might not be documented that certain changes may require a service restart, that doesn’t mean that they don’t, and those situations can be difficult to identify. In these cases, all reasonable measures had been taken without yielding the desired results. Both cases involved changes to a SQL Server instance. Making the decision to restart in cases like this is not something that should be done flippantly, but where do you draw the line at time and effort spent before pulling the trigger? My experience with these issues will dictate how I handle similar cases going forward, but as for resolving issues that I have not had experience in dealing with yet, there very well may be more painful times…followed by sound of my head hitting the desk over and over out of frustration that the solution was something so simple.

#Summit14 Recap…and other things

I just got back from another amazing SQL PASS Summit – this time in Seattle. This year was different in that I knew people going into Summit. Last year I only knew some folks from Dallas and the alumni I was paired up with, Bill Fellows. I met a ton of new people last year and got to reconnect with them this year, and make new friends as well.

This past year also gave me the opportunity to make new friends from all the SQL Saturday events I attended and had the privilege to speak at – Las Vegas, Houston, Oklahoma City, Baton Rouge, Kansas City, Denver, and next month, Washington DC. Never have I traveled so much before but it has been worth it. I have learned so much – not just about SQL Server, but also about speaking and sharing knowledge and experience with others.

One thing that I am still in awe of is the amount of “nerd celebrity” you will meet at SQL PASS Summit and the SQL Saturday events. I am one of those people who could care less about the idea of meeting the typical Hollywood celebs, but put me in front some of the people at PASS Summit and I am starstruck. All that brain power in one location is something awesome to behold. And the sessions aren’t bad either.

A few things to note…

Thanks to Matthew Brimer for the opportunity to attend the Brent Ozar pre-con with him. It was awesome!

Thanks to the Midnight DBA’s. They never fail to impress.

Thanks to all the sponsors that threw all the amazing parties. My liver also thanks you.

To Denny Cherry and the invention of Speaker Idol – what an opportunity for those presenting as well as those of us who were just there to watch. Great idea! Thanks for making this happen.

To all those that belted some tunes at the various #sqlkaraoke events – you were awesome but it is good you have your SQL Server careers to fall back on. 🙂

Finally, to my #sqlbitches – you know who you are. Keep it real, yo <insert secret hand gesture here>.

See y’all next year…if not before.

SQL Saturday #309 – Oklahoma City

It is my second time at the SQL Saturday in OKC and my first time speaking at it. For the people who attended my session on Beginning Automation with Powershell, thanks again for coming. I hope everyone enjoyed the session as much as I enjoyed giving it.

As promised, the code is available for download below. If you have any questions, leave a comment here or reach out to me on twitter.

 

SQLSat#324 – Session Noms

Thanks to everyone who attended my sessions at SQL Satruday #324 in Baton Rouge. This was really a awesome event – thanks also to all the people that helped put it on and all the sponsors who helped make it happen.

I had a great time presenting and I think my demos went ok (for the first time ever doing demos). I also really appreciate the feedback since I want to keep on presenting. If you were in one of the sessions and have any further comments or questions feel free to leave a comment here and I will get back to you.

Also, I have gotten the scripts posted here and the download appears to be working now. Let me know if you have any questions about those. Enter the password below to access and download the scripts from Beginning Automation with Powershell.

Thanks again and see everyone next year!

SQL Saturday #324 – I’m headed South!

It is just a few more days until SQL Saturday #324 – Baton Rouge. I will be giving two sessions there – Making the Leap from Developer to DBA and Beginning Automation with Powershell. So looking forward to this event!

The last time I was in southern Louisiana I was barely in my 20’s and on a family vacation. I found that my normal routine for straightening my natural curls was no match for the level of humidity in New Orleans. This memory prompted me to check the weather for this weekend – yeah…this could get interesting.

See everyone there! 🙂

I’m Speaking – SQL Saturday #308

SQLSAT308_SPEAKING

This weekend will be my second time to speak at a SQL Saturday event, and the first time I will be doing it solo. Being a native Texan, I have to admit it is a little strange that I have never been to Houston before, but I cannot think of a more fitting reason to make the drive down there. There is also that whole Dallas vs. Houston thing….but that is an unrelated post that I think other sites have already covered ad nauseam.

My session this weekend will be on Making the Leap from Developer to DBA – a topic I am all too familiar with since I have lived it. Fulfillment of that desire to innovate and create things can be accomplished as a DBA, but that is only a small part of the role. The business depends on you to make sure data is accurate and available in a timely fashion. Developers depend on you for the same thing but also for things like deploying their code…and trying to prevent them from hanging themselves and you when you do it.

It is still not too late to sign up for the event. If you are in Texas or anywhere near Houston you should go…and come to my session. Hope to see you there. 🙂

SQL Saturday #295 – Las Vegas!!!

I have ventured to Las Vegas this week to be at SQL Saturday #295. This time I am not just attending, but also speaking! I will be leading a panel session – How to Keep Your DBA from Killing You with Ed Watson, Mike Fal and Daniel Janik. Besides these folks there are bunch of amazing people on the schedule. I have been looking for a reason to go back to Vegas (besides the obvious) and this fits the bill.

If you have trouble dealing with your DBA then come to this session – we will be covering some common things that we see with developers, hopefully leaving you with a better understanding of these situations from a DBA perspective and some thoughts on how you could do things differently and improve that relationship with your DBA.

Hope to see you there! 🙂

Kill Oracle Sessions With PoSH

Recently I have been tasked with taking over the database related tasks for an Oracle database that is the backend for a third party application. Two of the monthly processes require the all the user sessions connecting to the application be killed before hand. Straight forward enough…there are just a lot of sessions.

Last time I did this I had forgotten that I had this task and on the day of I was not prepared with any type of automation. As a result, I was killing off connections by hand. Yes, there was sadness with each arrow up and the changing of the SID and SERIAL# values.

Moving on, I knew I wanted to automate this with powershell so I did some searching and came across this article on querying Oracle with PS. I walked each of the segments, modifying the code for our environment and for my needs. Everything was going great…until I decided to run the code in production. There was more sadness –

Once I was done cursing and swearing I started to research the error. Our pre-prod and production environments should be the same. After some digging I found some small differences in the listener.ora files and thought this might be the clue I was looking for.

Cut to Monday moring – a quick chat with our Oracle person (read: the guy on our team that has Oracle training…training that I do not have) brings me my solution – something far more simple than I had imagined. Since the production server was set up with a different port than the one Oracle defaults to all I needed to do was include that with the server reference in my connection string. Duh!

I began testing in the UAT environment and uncovered one small issue – a semi-colon in my kill statement that was not needed and causing my code to fail. Once that was removed everything looked good with the exception of getting error messages when trying to kill a session related to a system process (there weren’t that many sessions to begin with here, not to mention, it didn’t matter if I broke anything on this server).

Feedback is welcome. Hoping you find this helpful. 🙂

Back from SQL Summit

Quick post – I am back at work this week from the SQL PASS Summit and I have to say I think it was the best conference I have ever been to. From the sessions to the people and the events, everything was crazy fun. I now see what people mean by #sqlfamily. Even though it was my first time, everyone was so welcoming that it didn’t feel that way. I am looking forward to more of the same next year in Seattle. Coming up soon is SQL Saturday #255 – Dallas (well, actually, it is in Arlington…hmmm) and then SQL Saturday #233 – Washington DC. Heck, I might even make it to the after events this time. If you want to connect with me at either of these events hit me up on twitter – @texasamy.

Optimizing SQL Server Performance in a Virtual Environment with Denny Cherry

T-SQL performance tuning – no differences between virtual and physical

Check host and GPU numbers – look outside of the VM at the host.

Remote Perfmon users – group you can be added to for viewing performance monitor information for the server; you can be added to this group on the server where your VM is hosted.

ESX Top – task manager for UNIX; command line utility. Check for CPU thrashing – looking for percent used.

Check host and guest for disk IO latency.

Balloon memory driver – should be enabled; used by host os to request memory back from the guest os; if this is turned off – prevents host os from paging physical memory to the hosts swap file; paging will occur and you will have random slowness that will be difficult to diagnose.

Reserved memory setting – How much memory you want and how much memory you really need to have (reserved).

Memory deduplication – looking for and stripping out duplicate instances of processes in memory; great for OS memory. Doesn’t work for all SQL Server – unless multiple SQL Servers have the same pages in cache.

Storage configuration options – IO is the same if the disks are physical or virtual; automatic tier adjusting technology if possible. Storage slow? Get faster storage ($$$). Keep OS, data, logs tempdb on separate disks if possible.

Storage deduplication – can greatly improve overall performance; deduplicating the OS virtual disks = save much less data to the array.

VMWare Paravirtualization Driver – optional driver for vSphere Virtual Machines; recommended for high IO workloads; config via VM Properties – select SCSI controller, change type.

Monitoring – look at more levels of the environment; SQL Server, guest OS, hypervisor, etc.

Counters –
reads/writes per sec – correlated counter at the host level
seconds / reads and writes
disk queue
page life expectancy
system processor queue
VM Disk
VM Memory

Timer inside the VM lies – ugly rumor and nothing more.