December 20, 2017 at 10:35Windows server for 24x7 tasks - a myth or my “crooked hands”?
History of the last days. We have two SQL Servers (2016 with an SSD drive) and Express Edition (2012 with a traditional HDD). Both computers are approximately similar in hardware (CPU / RAM / LAN). In general, 2016 "gives" data 2-5 times faster, with the exception of a certain set of tables for which 2012 is faster 1.5-2 times. This behavior is completely contrary to any logic. Any manipulations with the database settings only worsen the situation. 2016 is increasingly slowing down, but only for this set of tables.
To understand the paradox of the situation - the same database is deployed on both servers (from the same backup'a file). There are about 600 tables in this database. Those 5-6 that behave surprisingly do not differ from dozens of similar ones (in structure and number of records) with which there is no such problem. On both “servers” - Windows 10 with the latest updates (these are development servers, not productive ones). On both SQL Servers, the latest service packs (without hot fixes). No special "modes" (trace flags, etc.) of SQL Server are included.
This riddle was great, but my colleague solved it. You even know how ...
He rebooted the computer with the 2016 SQL server. After that, all the "relapses" were gone. How!? Or rather, "How long !!!!!!!" I want to exclaim.
What we just didn’t do. What "shamanism" did not indulge in to explain this strange and absolutely illogical behavior. We rebuilt the indexes and changed data types (in one of these “problem” tables we had a data type marked “obsolete” in 2016, the fact that it was used in other “non-problematic” tables didn’t stop us anymore), we calculated the number of records per page and compared the page sizes of SQL servers, increased the database compatibility version and rebuilt statistics and so on, so on, so on.
Another problem with Windows, which was solved as in that classic joke, “Have you tried to just log out and log back in?”
An attentive reader (and a champion of Microsoft) should immediately convict me that I de “distort”. I use a non-server operating system for the database server (Windows 10). The problem is understandable - it's the desktop (!!!!!!!) operating system, but on Windows servers, of course, there is no such problem.
I’m in a hurry to upset you - I observe such “tricks of fate” with Windows servers with enviable regularity.
Well, you know - Windows Updater. He’s setting something in the background there and everyone hopes that he’s going for a ride. I mean that if you replace without restarting some of the executable files of the operating system, some settings, etc. (everything that any software update does), this can unpredictably affect processes that are already running and that cannot be quietly restarted (as in my case with SQL Server). Updating executable files and libraries that are already loaded into the server’s memory will be postponed, most likely, until the computer restarts, but everything else is a mystery. As I see it, a background update to Windows will always lead to such "surprises."
The problem, in general, is as old as the world. The first time I came across such consequences of Windows Updater was back in 2007, it seems. Since then, unfortunately, nothing has radically improved. To justify Windows Updater, I’ll say that I somehow watched as a colleague at the dawn of the 2000s installed a service pack on a Windows Server at lunchtime and could not restart the server until the end of the working day. Interesting effects were observed :)
Perhaps if the executable files were "self-sufficient" - i.e. included in one form or another copies of all the libraries that they use (with the exception of the lowest level). Updates to the operating system were not background ... and we return to the world of early versions of Windows when all (?) Updates were “delayed” until the reboot, users did not wait until the update was installed and turned off computers, and as a result, they ended up with unbootable “OSes”.
Those. most likely for server solutions you can find a solution option - updating everything on command and with the knowledge of the “admin”, and for “desktops” we see the “least of evils”.
As the saying goes, "If you have paranoia - this does not mean that you are not being watched." I mean, the further, the harder and harder it is to disable Windows Updater. Even in the off state (I'm talking about the Control Panel), Windows installs “critical updates” (including server operating systems).
I strongly recommend that everyone for server solutions disable Windows auto-update, but at the same time run it regularly “in manual mode”, as is done on Unix-like operating systems. I'm talking about apt, yum package managers or the same brew for OSX. If something similar exists for Windows servers and updates - tell me about it please.
I want to remind you that even if you do not have Internet access on your windows server, Windows Update can “drag down” updates on the local network (such an infection).
Not really. Critical updates without your knowledge on workstations are rolled out by many suppliers. The same Apple, if I don’t confuse anything (it’s not about notifications for which you need to click something in the App Store, but that it was installed without your knowledge). Link .
I hope no one but Microsoft installs updates on the server without the knowledge of the admin.
In principle, I observed “whistles” even worse under Unix, when the “curve” package brought by apt-get didn’t stop the old service instance and replaced the executable files. The fact that the problem is in the “cant” of the package update and the service before the update needs to be stopped, I guessed after the second recovery of the server from backup (since it was a virtual machine and it was done in a couple of clicks).
I would not use Microsoft server solutions without good reason (I’m not talking about an active domain or a file / print server there), but about services that should work 24x7. I can only say that over the past year I remember at least three situations where problems with our systems were solved by restarting the OS (these were all windows servers of different versions with auto-updates disabled). No, our memory does not “flow”. No, restarting our application servers did not solve the problem. And how do you like the situation when the date comparison operation stops working correctly for some values? Unfortunately, I observed this year. Why we (sometimes) use Windows operating systems on application servers is a customer requirement.
But what about the very SQL Server that started this monologue? How to deal with him !? The answer is simple - SQL Server 2016 on Linux . I, like many, are probably scared to put it into “commercial operation” for now, but if someone has similar experience with him, please share it.
To understand the paradox of the situation - the same database is deployed on both servers (from the same backup'a file). There are about 600 tables in this database. Those 5-6 that behave surprisingly do not differ from dozens of similar ones (in structure and number of records) with which there is no such problem. On both “servers” - Windows 10 with the latest updates (these are development servers, not productive ones). On both SQL Servers, the latest service packs (without hot fixes). No special "modes" (trace flags, etc.) of SQL Server are included.
This riddle was great, but my colleague solved it. You even know how ...
He rebooted the computer with the 2016 SQL server. After that, all the "relapses" were gone. How!? Or rather, "How long !!!!!!!" I want to exclaim.
What we just didn’t do. What "shamanism" did not indulge in to explain this strange and absolutely illogical behavior. We rebuilt the indexes and changed data types (in one of these “problem” tables we had a data type marked “obsolete” in 2016, the fact that it was used in other “non-problematic” tables didn’t stop us anymore), we calculated the number of records per page and compared the page sizes of SQL servers, increased the database compatibility version and rebuilt statistics and so on, so on, so on.
Another problem with Windows, which was solved as in that classic joke, “Have you tried to just log out and log back in?”
The same joke
Three people are driving in a car - a technician, businessman and programmer.
Suddenly the car died out.
The technician got out, opened the hood, and began digging there.
The businessman took out his mobile phone and began to call for
technical assistance.
The programmer looked at them in surprise and asked:
“Guys, didn’t you just try to exit and enter again?”
Suddenly the car died out.
The technician got out, opened the hood, and began digging there.
The businessman took out his mobile phone and began to call for
technical assistance.
The programmer looked at them in surprise and asked:
“Guys, didn’t you just try to exit and enter again?”
An attentive reader (and a champion of Microsoft) should immediately convict me that I de “distort”. I use a non-server operating system for the database server (Windows 10). The problem is understandable - it's the desktop (!!!!!!!) operating system, but on Windows servers, of course, there is no such problem.
I’m in a hurry to upset you - I observe such “tricks of fate” with Windows servers with enviable regularity.
Who's guilty!?
Well, you know - Windows Updater. He’s setting something in the background there and everyone hopes that he’s going for a ride. I mean that if you replace without restarting some of the executable files of the operating system, some settings, etc. (everything that any software update does), this can unpredictably affect processes that are already running and that cannot be quietly restarted (as in my case with SQL Server). Updating executable files and libraries that are already loaded into the server’s memory will be postponed, most likely, until the computer restarts, but everything else is a mystery. As I see it, a background update to Windows will always lead to such "surprises."
The problem, in general, is as old as the world. The first time I came across such consequences of Windows Updater was back in 2007, it seems. Since then, unfortunately, nothing has radically improved. To justify Windows Updater, I’ll say that I somehow watched as a colleague at the dawn of the 2000s installed a service pack on a Windows Server at lunchtime and could not restart the server until the end of the working day. Interesting effects were observed :)
But could it be otherwise?
Perhaps if the executable files were "self-sufficient" - i.e. included in one form or another copies of all the libraries that they use (with the exception of the lowest level). Updates to the operating system were not background ... and we return to the world of early versions of Windows when all (?) Updates were “delayed” until the reboot, users did not wait until the update was installed and turned off computers, and as a result, they ended up with unbootable “OSes”.
Those. most likely for server solutions you can find a solution option - updating everything on command and with the knowledge of the “admin”, and for “desktops” we see the “least of evils”.
My Windows Update is disabled!
As the saying goes, "If you have paranoia - this does not mean that you are not being watched." I mean, the further, the harder and harder it is to disable Windows Updater. Even in the off state (I'm talking about the Control Panel), Windows installs “critical updates” (including server operating systems).
I strongly recommend that everyone for server solutions disable Windows auto-update, but at the same time run it regularly “in manual mode”, as is done on Unix-like operating systems. I'm talking about apt, yum package managers or the same brew for OSX. If something similar exists for Windows servers and updates - tell me about it please.
I want to remind you that even if you do not have Internet access on your windows server, Windows Update can “drag down” updates on the local network (such an infection).
Ha, these are all Windows problems ...
Not really. Critical updates without your knowledge on workstations are rolled out by many suppliers. The same Apple, if I don’t confuse anything (it’s not about notifications for which you need to click something in the App Store, but that it was installed without your knowledge). Link .
I hope no one but Microsoft installs updates on the server without the knowledge of the admin.
In principle, I observed “whistles” even worse under Unix, when the “curve” package brought by apt-get didn’t stop the old service instance and replaced the executable files. The fact that the problem is in the “cant” of the package update and the service before the update needs to be stopped, I guessed after the second recovery of the server from backup (since it was a virtual machine and it was done in a couple of clicks).
What conclusions can we draw from all this?
I would not use Microsoft server solutions without good reason (I’m not talking about an active domain or a file / print server there), but about services that should work 24x7. I can only say that over the past year I remember at least three situations where problems with our systems were solved by restarting the OS (these were all windows servers of different versions with auto-updates disabled). No, our memory does not “flow”. No, restarting our application servers did not solve the problem. And how do you like the situation when the date comparison operation stops working correctly for some values? Unfortunately, I observed this year. Why we (sometimes) use Windows operating systems on application servers is a customer requirement.
Doctor, I do it and it hurts me - don’t do it!
But what about the very SQL Server that started this monologue? How to deal with him !? The answer is simple - SQL Server 2016 on Linux . I, like many, are probably scared to put it into “commercial operation” for now, but if someone has similar experience with him, please share it.