Re: Sql Server 2017 Express End Of Life
Malena Bower
We are developing a disconnected app for some of our tablet users (Windows 8.1) who do field work. We are a WPF shop for internal apps and my design is to use WPF and a SQL server express database that we can then synchronize when a connection is available.An objection has been raised to this that running SQL server express could affect battery life. Google has let me down here. I am unable to come up with any specs on how running Express may affect battery life. I suppose I could shut the service down and restart it when the app opens and closes, but we will probably be building more of these apps for the tablets and i would prefer to leave it running. battery life is a bit of an issue for our users.
Is there any info out there about how many minutes per day of battery life we can expect to loose just by having SQL express running in the background? My assumption is that it is minimal, please confirm. Are there ways (settings) to minimize its effect?
Page Life Expectancy (PLE) is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated memory to the SQL Server instance. In most of the cases, a page will be dropped from buffer periodically.
The basic fundamental of Microsoft SQL Server for querying data is to load the necessary data pages into the buffer pool from the disk and return it to the client. If the buffer does not have enough space to process it, then the oldest data pages will be eradicated to make some room as required for new pages.
As a traditional standard, PLE (Page Life Expectancy) should not be less than 300 seconds (5 Minutes), but actually, that value can fluctuate based on the SQL Server load. Anyway, a buffer is managed by the numa node, and that can be divided into the multiple numa nodes. Even, it can be more challenging to manage load distribution for the various numas when heavy workload occurs. PLE can raise performance issues by building pressure on buffer when your PLE goes down and not coming up for a while. There can be many more activities which can cause this issue.
We always assume that the query performance is degrading by the lake of buffer memory, and SQL Server does not have enough buffer pool to cache the information. It is not blaming the side for the worst query performance because a query execution plan is everything. If your query plan is improper, then the table will be scanned with unnecessary loading data to the buffer memory.
The above situations discuss the factors on what PLE can get down. But what about PLE getting down at a specific time? Hopefully, each production SQL Server will have the internal data maintenance task and standard maintenance, which is running by the SQL Server job and executing on the off-peak hours. Even, you can observe fluctuation in PLE (Page Life Expectancy) when any heavy I/O performs by the SQL Server with the big CRUD operation.
PLE counter value is an essential measurement of pressure on the buffer memory in units of seconds. Users can capture it with the help of SSMS, any other third-party tools, or Microsoft performance monitor; It returns average value always. Users can find the current value by the above T-SQL statement. Still, analysis is required in a particular period to see if fluctuation happens in point of time. Users can use the Windows Performance Monitor by adding the PLE counter to track the same.
Multiple SQL Server instances can be configured in single SQL Server. For monitoring a particular SQL Server instance, the user can select in the counter drop-down list of the performance monitor screen. In the below image, you can see that the PLE counter exists in MSSQL$JERRY: Buffer Manager.
For example, the above SQL Server instance service is restarted to get some fluctuation in Page Life Expectancy. After restarting it, the PLE count starts from 0 and increases with the querying data in the background. Again count is decreased when memory allocation is reduced and data is fetched from the tables in the background.
As per Microsoft standard, we follow a rule of 300 seconds. If PLE goes less than 300 seconds (5 Minutes), then memory pressure is very high, and we have to take care of the performance side. Still, that calculation was for just 4GB memory allocation to the SQL Server. For your server, it should be different as per the formula. You should worry about the SQL Server instance when PLE goes down than the below calculation:
No one can say that what number will be recommended for the standard threshold because each SQL Server has different configurations according to the use case and requirement. If your PLE goes down suddenly, then the first thing should be to check memory allocation. Is it enough amount of size for the SQL Server? If yes, then check the currently executing request, is there any long-running thread that consumes more memory? If yes, then review the currently executing query statement.
The performance will be based on the disk, which is a bit slower than the primary memory. Eventually, it produces a lot of disk IO and stresses on the storage because the memory is not getting processed efficiently by the server. Unnecessary memory usage issue should be observed and take a chance to get it optimized with a proper investigation.
Users can monitor it with querying on sys.dm_os_performance_counters DMV to find that PLE threshold is crossing the underline or not. If the counter value found is less than your calculation, then it should trigger a mail to the responsible team to observe and monitor the memory pressure-related parameters. We get the indication regarding the increased pressure on the buffer pool when page life expectancy drops down to a typical value and stays there for a more extended period. Possibly this is the sign that we might need more memory. It is advisable to add more memory when the workload is hiking gradually, and PLE is moving downward sluggishly. However, when something else is using more memory (e.g., plan cache). In this scenario, buffer pool pressure is increased, but there is no change in workload. A consecutive monitor enables the ability to measure stuff and evaluate it over more issues. After doing this, it is possible to let OpsMgr check the value and generate an alert when the value meets a specific threshold for X values.
PLE (Page Life Expectancy) is not a single buffer manager parameter which can affect the SQL Server performance; there are many more in the list of buffer manager. We can consider that the buffer memory pressure can be the primary issue if found. Nowadays, we see bigger memory boxes and quite common to have PLE in the thousands range.
Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.View all posts by Jignesh Raiyani
We have an ancient version on SQL express on a 2008 r2 server. We need to deinstall it. The original media files are not present so this is proving difficult. The control panel De-install program option does not work. The msiinv.exe also fails. Can someone offer advice or consolation? I am willing to play with the registry as a last resort.
When you say the original media files are not present, do you mean the installation files or do you mean the "c:\program files" folder for SQL express got deleted?
If you are just missing the installation files, SQL Express is free to download and you should be able to just re-download it.
If you mean you deleted the files instead of doing an uninstall, you are in for a bit of pain. The easiest way to fix this (in my opinion) would be to find a different computer (VM or physical), install the same version with as best as you can remember, the same settings. Once this is installed, copy the files from the new machine to the old one and then do an uninstall. If the new machine was a VM, then just kill that VM afterwards. If it is a physical machine, uninstall SQL express after this.
If that is not an option, then you are likely going to have a lot of troubles. Removing registry entries always comes with risk. It could be you remove stuff and it is gone from add/remove programs, but some little bits still exist and future SQL installs (express or otherwise) may be problematic. Or, a worst case scenario, you clean all the regsitry stuff up, but accidentally delete a critical registry entry and your computer no longer boots. No boot means you can't even recover the registry from a backup.
Editing the registry should always always always be a last restort (in my opinion).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
The easy way is to use add/remove programs. But if that isn't working, I'd download a copy of SQL Express (it is free), and use that to remove the instance.
I am pretty sure (but could be wrong) that the install and uninstall uses the same setup.exe file as all of the install and uninstall logic is inside the installer.
I downloaded the sql express version closest to the one installed on the server. Afterwards I ran the MSIINV utility to deinstall SQL server. Of course this only removed components that had the media setup files. No joy.
This is a virtual server. At this point I'm thinking a VM refresh is the best option.