Best way to generate a report on overall SQL Server performance to show management?

I don't think this is necessarily a good fit for the SE Q&A model, but I sympathize with datadawg2000's pain so will add some words that may help.

  ...claiming poor performance on the main server due to too many processes running at once. When I open activity monitor at any given time, I see no more than 5% processor time, 1 mb/sec, etc.

Let's be honest - that isn't the right away to get a snapshot of current server load nor is a screenshot of activity monitor going to be convincing to anyone. And besides, you really shouldn't be relying on Activity Monitor for...anything, anyways.

You didn't specify your SQL Server version, but at your disposal to see performance you have SSMS performance reports, perfmon, any number of third party applications, open source tools like Brent's First Responder Kit and sp_WhoIsActive, and a ton more with a little Googling (or Binging if you really love Microsoft).

Those can help you see if you really have a problem, and where it is. Being able to proactively come to management when there is a problem and/or retroactively assess the root cause will be a huge start.

  I have worked on performance issues with them before but it always
  comes down to code errors, inefficient queries, or missing indexes
  (for example there is one index that according to the missing index
  dmv has an avg_estimated_impact of 33 minutes).

Great! So you have a proven track record of fixing issues. Use that to make sure that you or someone who knows what they're doing is reviewing code, queries, etc. before they get pushed to production. 99% of the time these issues can be stamped out in a lower environment or through proper testing.

  I'm fighting it because it will cost them a lot of money in our org and also I don't believe it will help them with their performance issues that much/at all.

I don't know your setup, but  this will play out in one of two ways assuming you're correct:

They add a new reporting instance, nothing gets better. You can say "I told you so" and proceed to get them to fix their bad code / indexing / testing process.
They listen to you and fix their bad code / indexing / testing process.

Either way, you can win here. Unless the cost of the new server is coming out of your paycheck, make your advisement and then relax. You probably don't have the power to make the final decision, so don't lose sleep over it.

  Does anyone have any nice queries or methods they can share to help
  prove server performance is fine in terms of computing resources? I
  can screenshot activity monitor but I'd like to provide management
  with something more concrete (perhaps over 24 hours) that it is not a
  lack of computing resources that is part of the perceived slowness.

See the list of tools above for this. Painting a long term picture is key here, which third party tools and/or pretty graphs will probably help with. Remember that no one cares if you have good performance for 23 hours out of the day if the Big Boss's report runs slow during the literal 11th hour. Clearly management has valid concerns things are not fine and you've said the same more or less - so don't fight them on that point.

You are disagreeing on what the root cause of the problems are and that is where you need to focus your efforts. Spend some time to agree on metrics that both you and management find to be important and start reporting on those. You can highlight you're meeting RTO and RPO SLAs, have good CPU usage, etc. but also remember they probably care about their reporting SLAs just as much (lets be honest, way more) than the boring technical details. Then, as long as you can show positive trends in making those metrics healthier, it should be easy to get them on your side of reasoning.

Let me also add that I have these and more tools/resources compiled on my site  here if you'd like to explore more free and community driven software/projects.


Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours)

Since your day is divided into 5-second buckets, you'll need to do the same to your timestamp in the table.  Since you're counting the 5-second buckets and not the actual number of updates, you'll need to use either a CTE or derived table.  I'll use the CTE only because I think it's easier to read.

WITH UpdateIntervals AS (
  SELECT
      DATEADD(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5,
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0)) AS Interval,
      COUNT(*) AS UpdateCount
  FROM dbo.YourTable
  WHERE UpdateTimestamp BETWEEN @ReportStartTime AND @ReportStopTime
  GROUP BY DATEADD(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5,
           DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0))
)
SELECT COUNT(*)
FROM UpdateIntervals
WHERE UpdateCount >= 2;

You could just as easily use HAVING COUNT(*) > 1 inside the CTE and then a simple COUNT(*) in the query with no where condition, but to me, this makes it clear what I intended to do when I come back and read this 6 months later.  I'm counting the number of updates per interval, then counting intervals with at least 2 updates.

The nasty-looking DATEADD formula isn't as bad as it looks.  First, it truncates your update timestamp down to the minutes level by calculating the number of minutes between 0-time and your timestamp, then adding them back to 0-time.  The seconds part of your timestamp is then rounded down to the previous multiple of 5, and the rounded value added back to the timestamp to give you 5-second buckets.


Database in simple recovery to save space

First a quick note on moving to SIMPLE recovery.  This is a business decision.  It is not in any way a technical decision.  Your business needs to weigh the cost of staying in FULL (which may mean a new server with additional space, archiving current data to free up some space, etc) vs the cost of losing up to a day or more of data.  Once they have decided then you can proceed.

Based on your question and the comments I see one of two scenarios:

You are in FULL recovery mode but are not taking log backups.

At this point you need to start taking log backups on a regular basis.  In all probability this will actually save you space.  If you take a log backup to clear the log less than once a day (Note: FULL backups do not release log space) then your transaction log is probably larger than it needs to be.  Get your backups in order then figure out how big your transaction log actually needs to be.  In all probability it will be quite a bit smaller than it currently is giving you additional space for backups.  Worst case move your backups to tape more frequently than you currently are.

You are in FULL recovery mode but are taking log backups.

If this is the case then you may need to increase the frequency of your log backups.  This will not incur additional space requirements since the log files will just be smaller (there is some small overhead for header info but it's not that significant).  This will allow your log to clear faster and stay smaller.  Assuming (and this is a big assumption) that you don't have one or more individual transactions that are requiring the log space.  If that is the case then only changing your code to decrease the size of your transactions is going to let you get away with a smaller log file.  Even if you switch to SIMPLE recovery you are still going to require log space for the largest single transaction (or largest combination of simultaneous transactions).  If you shrink your log file and run a transaction that needs more space than is avialable it's just going to grow the log again.

Again switching between FULL and SIMPLE recovery is a business decision.  Making that kind of decision without consulting the business (and then being wrong) is a good way to get yourself fired.


You can achieve this without using Foreach enumerator, just use one data flow task to achieve this.

Building the Package

First of all, add a Data Flow Task to the control Flow

1. OLEDB Sources

In the DataFlow task add an OLEDB Source that Read from Orders Table (the same command used in the Execute SQL Task (first step in your question)

Select * FROM Order

Also add a second OLEDB Source that read from customer table:

select * FROM CRM

2. Sort

After each OLEDB Source add a source component:

The First (order table) you have to select CustomerID and OrderID columns for sorting and select the sort type as ascending
The Second (customer table) you have to select CustomerID (sort type
= ascending) and LevelGrantedOn (sort type
= descending) columns for sorting

3. Merge Join

Add a merge Join component to join both sorted outputs. and select CustomerID column from both outputs as Join Key and select the output columns you need from both tables

4. Conditional Split

After merge Join add a conditional split to filter rows only that match the following expression

[LevelGrantedOn] < [orderdate]

5. Script Component

The final component we have to use is a script component to get only the first row for each customerID (because both source are well sorted just grabbing the first row is similar for Select top 1 ... ORDER BY LevelGrantedOn desc)

In the script component add an Output Column OutFlag of type DT_BOOL and Use the following script:

This script will set OutFlag to True when the customerID occur for the first time (similar to TOP 1)

    Public Class ScriptMain
        Inherits UserComponent

        Dim lstCustomerID As New System.Collections.Generic.List(Of Integer)
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            If lstCustomerID.Contains(Row.ID) Then

                Row.OutFlag = False
            Else

                lstCustomerID.Add(Row.ID)
                Row.OutFlag = True

            End If

        End Sub

    End Class

6. Conditional Split

Add a second Conditional Split to filter Top 1 rows:

[OutFlag] == True

7.OLEDB Destination

Connect the Conditional Split Output top the OLEDB Destination and map the columns.

The Dataflow task must looks like


Find columns referenced by virtual column expression

User_tab_cols shows that there is a dependancy when virtual_column contains 'YES', which answers your need to determine if there is any dependency at all.

In order to determine what the actual virtual column consists of, one still needs to parse data_default.

SELECT column_name
  ,      virtual_column
  ,      segment_column_id
  ,      internal_column_id
  FROM   user_tab_cols
  WHERE  table_name = 'T'
  ORDER  BY column_id;


One thing that can be improved is the choice of data types:

for fields like company or product name varchar(50) (or a bit more) is sufficient. Text is overkill. You are not storing big chunks of text like forum posts.
for age the realistic range would be 18-70. So smallint is more than enough (range: -32768 to +32767)
usage of bigint is not justified in your schema - consider the range, it's totally overblown for your purpose:

  bigint    8 bytes     large-range integer     -9223372036854775808 to
  +9223372036854775807

(source: PostgreSQL 9.5.21 Documentation - Chapter 8. Data Types)

for gender: an enumerated type would be a good candidate because you are really expecting no more than a few options: Male/Female/Other.
for unit_price you chose: float8, if I'm correct the usage range is: 1E-307 to 1E+308. That may be useful if you are trying to count the number of atoms in the universe but no item on Earth can be priced that much. It would be more reasonable to use NUMERIC and choose the precision you want, depending on how many digits you want to the right of the dot. For example retail items may need 2 decimals if you are pricing in cents, when buying in bulk from suppliers prices may be expressed with 4 decimals.
ditto for salary

Here you are doing arithmetic operations like SUM so the performance is impacted by the data type although you may not notice it. On bigger datasets the impact could be more noticeable.

So I think you should review the data types available on that particular DBMS and redesign your tables with an effort to select the most appropriate data type for each field.

You are the designer and maybe the end user too, so you should know more or less what kinds of values are expected and the data types should be chosen accordingly, with some room for growth if you want but stay reasonable.

Since your question is about optimizing, another point that has not been addressed but that is strongly related to performance is indexing.  If you join tables that have lots of records you will need indexes sooner or later, or retrieval times will become unacceptably slow. Good table design is important too.


Synchronizing a RDS instance with another RDS instance

DISCLAIMER : I haven't tried this myself

What you asking for is somewhat rare. Nevertheless, it is possible.

Here is a link to Looker.com to explain how.

Basically, you do the following:

Create a read replica
Create a Custom DB Parameter group
Tweak the read_only setting in the Custom DB Parameter Group
When replica has been created, ...

you modify the instance
set Parameter Group to the Custom DB Parameter Group

Once you have the accessibility, you can create the DBs directly in the Slave.

I would normally say "Give it a Try !!!" but I never did this before. So, try it out and tell us all if it worked.


SQL Server with small database size and huge log file size

Could this be the reason for large log files

Yes, particularly with FULL recovery model and if there is NO frequent LOG backups as the in-active VLFs in LOG file cannot be truncated without backup in full recovery model.

With Simple recovery model LOG backup is not the case, most probably at one point of time there might have been a heavy transaction/Index maintenance online performed and to accommodate that heavy operation LOG size increased, from that moment on-wards the in-active VLF of log file would be truncated automatically in simple recovery model but that would free-up the space in LOG file not reduce size of LOG file unless the truncation performed manually (DBCC SHRINKFILE). To check used and free space of LOG: DBCC SQLPERF ('Logspace')

Shrinking file (in your case with simple recovery) is not recommended as there might possibility same amount of space required in LOG file to accommodate upcoming heavy transaction.

  We have tried shrinking database that didn't make much difference.

Assuming you tied DBCC SHRINKFILE to shrink the LOG file size in full recovery model, if there is no impact with SHRINKFILE command there must still active portion on LOG which would not allow us to truncate, usually when you look at message section after DBCC execution it clearly indicates. Also you can check what stopping the SHRINK operation with following query

Descriptions of log_reuse_waits

select  name,
        log_reuse_wait_desc,
        is_distributor,
        is_merge_published,
        is_published,
        is_cdc_enabled
from sys.databases

You may want read detailed explanation on LOG SIZE GROWTH

If you're not concerned on point in time recovery

Perform LOG backup of a database
Change recovery mode to SIMPLE (ALTER DATABASE YourDBName SET RECOVERY SIMPLE)
Shrink the file size (DBCC SHRINKFILE (2, 1024))

  2 indicates file ID
  1024 indicates new size of file (keep enough space as per your workload)

Since the LOG backups are not maintained, keep the database in simple recovery mode so that it wouldn't grow rapidly


My master database has multiple personnality issues

Alright... I don't know what the underlying issue was, but closing SSMS (2016) and re-opening it seemed to fix it, as I don't have any error messages anymore.

Thank you all for your help and interest!


How does MySQL skip ranges (nodes) in a loose index scan?

I think the Optimizer works something like this:

It can find the first row:  MIN(name) WHERE foo=123
It can find the last row:  MAX(name) WHERE foo=123

Those are done via a drill-down in the BTree, and assumed to be reasonably cheap.  If there are a lot of rows for foo=123, then this is likely to skip over some blocks.  Note: fetching blocks potentially means a disk hit, and disk hits are the most costly primitive operation.  Note also that much of the Optimizer was designed in the days of small RAM and before SSDs.

Once it gets to the first (or last) row, then the B+Tree shines in that it can walk forward (or backward) efficiently.  (But this is not relevant to your Question.)

If, as you imply in your question, there are not many rows where foo=123, then there is not much savings, perhaps no savings.

(What I don't know here is whether and when the Optimizer decides that "loose scan" is likely to be inefficient.  I do know that the Optimizer will deliberately choose to do a table scan instead of using an INDEX when it estimates that the back and forth between the index BTree and the data BTree is too costly.)

A Rule of Thumb:  There are 100 items in each node of the BTree.  (This of course really varies between 1 and several hundred, but 100 is a handy number for estimating things.)  So, a 3-level BTree like you diagrammed may be holding 1 million items and have 10,000 leaf nodes.  Now, depending on the specifics of the schema and the query, a table scan would cost 10,000 disk hits versus only a few disk hits for a loose scan.  (Yeah, the table is likely to be completely cached in the buffer_pool; I do know that the Optimizer does not yet take that into account in its 'cost model'.)

See also: https://dev.mysql.com/doc/refman/8.0/en/cost-model.html


Best approach to mirror All databases of a sql server

I do not see what version of SQL Server you are using and from the tag assuming it is SQL Server.

From Books Online:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On Availability Groups instead.

If you are at 2012 or above I highly recommend you to consider Always ON Availability group. Besides High Availability you can also gain performance benefit by offloading certain maintenance operations and read traffic.  It will depend on your setup, application design and requirement. You can read more in details here.
FYI :This technology is built upon mirroring which you mentioned in the question.
Log Shipping is definitely another option but your read only copy cannot be available for read traffic all the time and not in sync as close to real time as Always ON Availability Group.  See details about Log Shipping here.

Few more options that you can consider based on your requirements:

Failover Clustering
Failover Clustering in conjuntion with storage level replication.
AlwaysOn Failover Clustering Instances
Replication


mssql on ubuntu will not start, cannot update 404

Fixed by guessing this was due to a server update and the source url changed. I added the current source per their instructions https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

This added the NEW source with a sudo add-apt-repository ... per the docs. I then manually comment-out (or you can remove) the old 404 source in /etc/apt/sources.list.

Finally sudo apt-get update -y ... and start the server and I can again see it running (netstat or whatever to see the service up).

To be clear the URL changed:

from
https://packages.microsoft.com/ubuntu/16.04/mssql-server

to https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017

Comment: adding '-2017' to sources, or changing sources for existing installations is highly likely to both break them and makes these sources look dated, possibly all goals driving the change. That sucks.


Replicate data from multiple tables with same structure to a single destination table with additional columns?

Yes this is achievable in Microsoft SQL Server, and there's a couple different ways you can go about it.

SSIS Package: You can create an SSIS package that pulls the data from the different source databases and consolidates it all into one table on the target database with the additional column "TabCode" filled in accordingly.

Replication + View: You can setup replication publications between your source servers to subscriptions on your target server. Then you can use a view that unions all the data together into one view with the additional column "TabCode" filled out.

Some of the things to consider about SSIS packages is that they require some upfront implementation (you create them as a Visual Studio SSDT project type), and they require a job to trigger when they run (typically a SQL Agent job). So they aren't necessarily very real time in data synchronization (though they can be scheduled to ran as frequently as every second - but their actual runtime will depend on how you implement the SSIS package).
Some things to consider about Replication + Views is you can accomplish near real time data synchronization (with Transactional Replication) and they require less upfront implementation, but can be a little tricky getting setup first time around. (Overall they're not too bad to setup and work well when they work.)


There is a built-in function FROM_UNIXTIME() in MySQL, use it:

INSERT INTO yourtable (date_heure, ...) values (FROM_UNIXTIME(1470762668), ...)


SQL NULL Value to = A Sequential Number, Help!

Well, starting with a numerical example pulled from the link at the bottom and then just changing the variable type from int to varchar() works because there is implicit conversion due to the math expression I assume. The field "id" is also a varchar().

  declare
      @start varchar(30) = 100000000;

   update t
      set @start = id = @start - 1
     from #TheTable t
    where id is null;

I came up with this mess before I realized the that the varchar() performed auto conversion. It requires a primary key (pk) but doesn't require a local variable.

  update t
     set id = r.NextId
    from #TheTable t
    join (select pk,
                 10000000 - row_number() over (order by rand()) as NextId
            from #TheTable
           where id is null) r
      on r.pk = t.pk
   where t.id is null;

(Above example was edited to fix bug after receiving comment)

Numeric Solution and many replies that may have an better answer

Update - Responding to comments:  To patch them from 9's to sequential.  Please double check the number of 9's, your comment listed the wrong number I think.

declare
   @fromValue varchar(30) = '9999999';  -- 7 nines

UPDATE t
   SET HardwareNumber = r.NextID
  FROM tblAsset t
  JOIN (select Asset_ID,
               10000000 - ROW NUMBER() over (order by RAND()) AS NextID
          FROM tblAsset
         WHERE HardwareNumber = @fromValue) r
    on r.Asset_ID = t.Asset_ID
 WHERE t.HardwareNumber = @fromValue;


How to make DISTINCT ON faster in PostgreSQL?

For only 400 stations, this query will be massively faster:

SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;

dbfiddle here
(comparing plans for this query, Abelisto's alternative and your original)

Resulting EXPLAIN ANALYZE as provided by the OP:

 Nested Loop  (cost=0.56..356.65 rows=102 width=20) (actual time=0.034..0.979 rows=98 loops=1)
   ->  Seq Scan on stations s  (cost=0.00..3.02 rows=102 width=4) (actual time=0.009..0.016 rows=102 loops=1)
   ->  Limit  (cost=0.56..3.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=102)
         ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..664062.38 rows=230223 width=16) (actual time=0.009$
               Index Cond: (station_id = s.id)
 Planning time: 0.542 ms
 Execution time: 1.013 ms  -- !!

The only index you need is the one you created: station_id__submitted_at. The UNIQUE constraint uniq_sid_sat also does the job, basically. Maintaining both seems like a waste of disk space and write performance.

I added NULLS LAST to ORDER BY in the query because submitted_at isn't defined NOT NULL. Ideally, if applicable!, add a NOT NULL constraint to the column submitted_at, drop the additional index and remove NULLS LAST from the query.

If submitted_at can be NULL, create this UNIQUE index to replace both your current index and unique constraint:

CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

Consider:

Does creating a unique constraint on a Postgres column remove the need to index it?
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

This is assuming a separate table station with one row per relevant station_id (typically the PK) - which you should have either way. If you don't have it, create it. Again, very fast with this rCTE technique:

CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;

I use that in the fiddle as well. You could use a similar query to solve your task directly, without station table - if you can't be convinced to create it.

Detailed instructions, explanation and alternatives:

Optimize GROUP BY query to retrieve latest record per user
Select first row in each GROUP BY group?

Optimize index

Your query should be very fast now. Only if you still need to optimize read performance ...

It might make sense to add level_sensor as last column to the index to allow index-only scans, like joanolo commented.
Con: It makes the index bigger - which adds a little cost to all queries using it.
Pro: If you actually get index only scans out of it, the query at hand does not have to visit heap pages at all, which makes it about twice as fast. But that may be an insubstantial gain for the very fast query now.

However, I don't expect that to work for your case. You mentioned:

  ... around 20k rows per day per station_id.

Typically, that would indicate unceasing write load (1 per station_id every 5 seconds). And you are interested in the latest row. Index-only scans only work for heap pages that are visible to all transactions (bit in the visibility map is set). You would have to run extremely aggressive VACUUM settings for the table to keep up with the write load, and it would still not work most of the time. If my assumptions are correct, index-only scans are out, don't add level_sensor to the index.

OTOH, if my assumptions hold, and your table is growing very big, a BRIN index might help. Related:

Speed up creation of Postgres partial index

Or, even more specialized and more efficient: A partial index for only the latest additions to cut off the bulk of irrelevant rows:

CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

Chose a timestamp for which you know that younger rows must exist.
You have to add a matching WHERE condition to all queries, like:

...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

You have to adapt index and query from time to time.
Related answers with more details:

Index optimization with dates
Add datetime constraint to a PostgreSQL multi-column partial index
Indexed ORDER BY with LIMIT 1


Try cleaning the logs if you don't need them.

there is something freak in 9199 rows and consuming 21GB

see Freeing up disk space in 'mysql\data'


First, do not use either of your two queries. Both have a group by some column (GROUP BY group_id) and then select other columns, non-aggregated (SELECT id, name). This may give you wrong and unexpected results, despite that it may work in your tests, with some small sized table.

Second, the UNION ALL is not a problem. If the two subqueries perform efficiently, then the final union is ok, too. If you need a sort, the efficiency will depend on how that sort differs from the indexes used.

Now, the problem of "groupwise-max" or "greatest-n-per-group" has many solutions (and even a tag, both at SO and here). There are two sub-problems, depending on whether ties can happen and what the wanted results are in those cases.

If you want all the tied rows, the solution with GROUP BY inside a derived table is usually good. In your case, that you want just one row returned per group, another approach is easier to write and usually performs very well when there is a small number of group overall:

SELECT id, name, price
FROM items
WHERE group_id IS NULL

UNION ALL

SELECT i.id, i.name, i.price
FROM
    ( SELECT DISTINCT group_id
      FROM items
      WHERE group_id IS NOT NULL
    ) AS di
  JOIN
    items AS i
  ON  i.id =
    ( SELECT id
      FROM items
      WHERE group_id = di.group_id
      ORDER BY price, id             -- order for resolving ties
      LIMIT 1
    )
ORDER BY
    <some_columns> ;                 -- final order

An index on (group_id, price, id) will be helpful


Transaction Log file size for Index reorg

You mention that if you run the index rebuild process manually, and take a transaction log backup in between each index rebuild, then you don't have a problem.

As has been noted, you can create a script to rebuild each index, taking a transaction log backup in between each rebuild.

Alternately, you could increase the frequency of your schedule transaction log backups while the rebuild process is running. For example, if you normally perform tlog backups once an hour, do it every ten minutes for the duration of the maintenance job.

There are multiple ways to do this:

Schedule a secondary tlog backup job for the expected duration of the index rebuild.

For instance, if your regular tlog backup runs at 1AM, 2AM, 3AM, etc., and the rebuild job runs at 2:30 AM and usually takes no more than 3 hours, schedule a secondary t-log backup starting at 2:35 AM, running every 10 minutes, and ending at 5:35 AM.
Have a disabled secondary schedule on your t-log job that you enable at the start of the rebuild job, and disable at the end. In this case (with the same theoretical frequencies as in the example above), this schedule would be set to run the job every 10 minutes starting as 12:05 AM. You should be able to use sp_update_schedule to enable and disable the job schedule. I would make sure the schedule had a unique name, and use that in the stored procedure (as that could remain the same across multiple servers).

If the schedule was named "TLog Additional Runs", You would use:

EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 1;

to turn the schedule on, and:

EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 0;

to turn it off.


You can use a recursive CTE to achieve this, in conjunction with two windowing functions (ROW_NUMBER and RANK), to produce a running total of the date difference between the current date value and the previous value (by tabname & id group). You then SELECT only those rows with a date difference higher than 1.

If you're on SQL Server 2012 or higher, there is a much simpler way. Use the LAG function to retrieve the previous value (grouped by tabname and id) and performt he DATEDIFF on that.

Examples are included below, and you can see them in action in this db<>fiddle.

LAG Example:

SELECT
  TabName,
  Id,
  AsOfDate
FROM
(
  SELECT
    TabName,
    Id,
    AsOfDate,
    ISNULL(DATEDIFF(DAY, LAG(AsOfDate) OVER (PARTITION BY TabName, Id ORDER BY AsOfDate), AsOfDate), 0) AS PrevDateDiff
  FROM Temp
) t
WHERE t.PrevDateDiff > 1
ORDER BY tabname, id, asofdate

Recursive CTE example:

;WITH CTE AS (
  SELECT
    TabName,
    Id,
    AsOfDate,
    RANK() OVER (ORDER BY TabName, Id) AS Grp,
    ROW_NUMBER() OVER (PARTITION BY TabName, Id ORDER BY AsOfDate) AS Rn
  FROM Temp
), CTE2 AS
(
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    0 AS DateDiff
  FROM CTE c1
  WHERE c1.Rn = 1
  UNION ALL
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    DATEDIFF(DAY, c2.AsOfDate, c1.AsOfDate) AS DateDiff
  FROM CTE c1
  INNER JOIN CTE2 c2 ON c2.Rn = c1.Rn - 1 AND c2.Grp = c1.Grp
  WHERE c1.Rn > 1
)

SELECT
  TabName,
  Id,
  AsOfDate
FROM CTE2
WHERE DateDiff > 1
ORDER BY tabname, id, asofdate


I was running the query inside python 2.7.10, which comes with sqlite 3.8.3.1. It turns out that if I run this directly with the sqlite 3.8.10.2 that comes with OSX, it works fine.

I upgraded my python sqlite to 3.9.1 and it works now.

Instructions per trac.edgewall.org/wiki/PySqlite

Download pysqlite-2.8.1.tar.gz from
trac.edgewall.org/wiki/PySqlite
Download sqlite-amalgamation-3090100.zip from
sqlite.org/download.html
Unzip both and put them in the same folder
Run this command from the folder:

python setup.py build_static install

Note: I'm using OSX El Capitain, and a separate python installation (not the built in apple one) downloaded from python.org, and my .bash_profile is set up to point to this python installation


Defining the host on a new installation of Oracle 12c Enterprise Manager

1- Avindra  follow this link to a list of how to articles on Oracle Enterprise Manager 12c

2- And as for your host question, you need to setup a name for your host and that name must carry an valid ip address, unless you want to monitor all targets on the localhost.

To change your host name follow this article


How to convert different string value to respective 'YYYY' int format?

Get the value from the XML as a string and do some string manipulation in a case statement.

Example with simplified XML:

declare @X xml = '
<YearBuilt>2017</YearBuilt>
<YearBuilt>New</YearBuilt>
<YearBuilt>0-5 Yrs</YearBuilt>
<YearBuilt>70+</YearBuilt>
';

select case
         when T.Value = 'New' then year(getdate())
         when T.Value like '%Yrs' then year(getdate()) - cast(replace(stuff(T.Value, 1, charindex('-', T.Value), ''), 'Yrs', '') as int)
         when T.Value like '%+' then cast(left(T.Value, charindex('+', T.Value) - 1) as int)
         else cast(T.Value as int)
       end
from @X.nodes('/YearBuilt') as Y(X)
  cross apply (
              select Y.X.value('text()[1]', 'varchar(10)')
              ) as T(Value);


OPTIMIZE FOR UNKNOWN in a Stored Procedure

Not exactly, no.

OPTIMIZE FOR is a statement-level directive and as such, it can only be applied to statement-level queries - there's no way to apply an OPTIMIZE FOR directive directly to an entire stored procedure.

However, there is a directive available for stored procedures which may produce the results you are looking for.  First, let's consider what OPTIMIZE FOR actually does.

When you use the OPTIMIZE FOR hint ( and that's what it is at the end of the day, a query plan hint, not entirely different than going out of your way to suggest a HASH JOIN ), you're dictating what the optimizer should do and that can come with a lot of baggage you might not be interested in carrying.  This is not to say "don't do it," but merely as a caution to be sure you know the implications of going down this path.  In the specific case of OPTIMIZE FOR UNKNOWN, you're telling the optimizer to build a query plan for the statement that will be suitable for any parameter that is passed.  When included in a stored procedure, the stored procedure's query plan retains the generalized plan of the statement.  Once complied and executed, the plan is stored in the plan cache and will be reused for subsequent executions, even if the optimizer might have been able to produce a better plan for a different parameter set, through removing the optimizer's ability to regenerate the plan.

Sometimes, the stored procedure is called very frequently and this technique can improve performance by letting the optimizer know it doesn't have to worry about generating plans for different parameter sets, allowing it to skip recompiling and use the "good enough for whatever" plan to produce results reasonably quickly.  More often than not, it is used to avoid the more nefarious effects of parameter sniffing.

CREATE PROCEDURE dbo.SomeProc
    @Name                       sysname
AS BEGIN
    SELECT  TOP 10 *
    FROM    sys.objects
    WHERE   name = @Name
    OPTION ( OPTIMIZE FOR UNKNOWN );
END;
GO

If the need to tune a procedure falls into the realm of eliminating parameter sniffing, another technique which can be applied - on the entire stored procedure level, if desired - is the RECOMPILE option.  This option is basically the absolute opposite approach from OPTIMIZE FOR, which instead of caching a general version of the query plan which can be reused later, tells to optimizer to go ahead and generate a new query plan every single time the procedure is executed, allowing the optimizer to produce an optimal plan for each of those executions.   Generating query plans is CPU intensive and can be quite expensive ( you usually -want- the plan in the plan cache ), but if the stored procedure isn't executed particularly often and / or involves dynamic SQL, using the RECOMPILE option can be a legitimate tactic.  This can be helpful in specific cases where CPU is cheaper than I/O as well, making the processor work harder to alleviate a little disk contention.

CREATE PROCEDURE dbo.SomeProc
    @Name                       sysname
WITH RECOMPILE
AS BEGIN
    SELECT  TOP 10 *
    FROM    sys.objects
    WHERE   name = @Name;
END;
GO

You can use OPTIMIZE FOR ( @variable_name = UNKNOWN ) at the statement level or the old-school method of re-declaring parameters as local variables and then using those throughout the procedure ( SET @p_Name = @Name;, WHERE name = @p_Name; ), both which direct the optimizer to use statistic densities instead of statistic histograms, but often it's better to just let the optimizer be and turn your attention to where the issue frequently is:  The plan cache itself.  Clearing the plan cache of a specific stored procedure that is giving you grief will often alleviate any immediate problems and may even buy you time to properly analyze and address why your stored procedure is suffering from desperate performance, without resorting to OPTION-related measures.

SELECT  ProcName = OBJECT_NAME( st.objectid ),
        PlanHandle = cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text( cp.plan_handle ) st
WHERE   cp.objtype = 'Proc'
    AND st.objectid = OBJECT_ID( 'dbo.SomeProc', 'P' );
GO

DBCC FREEPROCCACHE ( <PlanHandle from above results> );
GO


Rebuilding an index takes more CPU than reorganizing it.  It locks the database so that has to taken into account.  Indexes should be rebuilt when the fragmentation is more than 40% or so.  After that it becomes too slow and cumbersome for the server to reorganize.  You should reorganize an index when the fragmentation is roughly 10%-40%.  So healthy index is basically less than 10% fragmentation, little bit less than 10% perhaps.

These are my rules of thumb and they have worked for me, but they are guidelines.  So many things change the performance so you need to find out what woorks for you in your environment.

I think that you can rebuild an index online nowadays, at least if you use the Enterprise version of SQL Server, the keyword ONLINE if I am not mistaken.


Keeping my database updated every 30 minutes using Log files

With log shipping you can do this. However, during the restore, the connection will be on hold. Query resulting is delay. Unfortunately, log shipping in there is no such solution.


Can a un-used NONCLUSTERED INDEX still enhance query speed?

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan.

I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index.

Edit - This might not be applicable cause I just realized that you have disabled the index.  I have not tested that scenario.


SELECT ROWS WITH SIMILAR ID AND MERGE THE FILED VALUES IN ONE


you can use at the runtime.

SET GLOBAL binlog_format = 'ROW';

or for the session

SET SESSION binlog_format = 'ROW';

see the manual for more information.


How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG

You cannot add a Linux system to a Windows cluster, so you won't be able to add any Linux systems into a SQL Server 2014 availability group. You also cannot do the migration with distributed availability groups because SQL Server 2014 did not have support for distributed availability groups.  From Distributed availability groups:

  Because the distributed availability groups feature did not exist in SQL Server 2012 or 2014, availability groups that were created with these versions cannot participate in distributed availability groups.

In order to be able to do a migration using availability groups, you would have to get the SQL Server 2014 systems upgraded to at least SQL Server 2017 where you can create an availability group with a cluster type of "none," which will allow you to add a replica to a Linux SQL Server.  See Always On Availability Groups on Linux -> Interoperability with Windows-based availability groups and replicas.

Always On Availability Groups on Linux states that you can use Pacemaker, or no cluster for Linux availability groups.


Is it more efficient to subtract a value from a date or use DATEADD with a negative value

The difference, if any, in performance should be so small that it wouldn't be measurable. The calculation is done either way just once. Deleting thousands of rows will surely need far more time to execute than a single calculation.

Having said that, I'd choose the version with the DATEADD() as adding a date with an integer doesn't look nice or elegant. OK, it may look a bit like a cool "hack" but it's not best practice and is relying on the internal representation of date/datetime types.

See Aaron Bertrand's blog: Bad Habits to Kick : Using shorthand with date/time operations, where he explains how this kind of code works ok with DATE or DATETIME but will break your queries when someone changes the type of the column/variable to DATETIME2.


Select XML values when attribute involved

Use the XQuery CONTAINS method, like this:

SELECT CASE @x.exist
(
N'/bikes/Product[1]/Specifications[contains(., "Novice")]'
)
WHEN 1 THEN N'The bike is good for novices'
WHEN 0 THEN N'The bike is for more advanced riders'
END;

Good luck.


Listener alias and Listener name need not be the same. The database resolves the listener alias to the correct listener description by looking up the tnsnames.ora file.


How to do a bulk load of a database when application are relying on it?

Do you need to drop and reload the database?  In a case like yours, I would look at a way to synchronize the database with the reference set (the data you are loading).

An alternative approach would be to have two databases.  Load a different one each day.  Configure the webapp so that you can swap databases on the fly.


How do I load data from a single table to multiple tables & keep referential integrity

output alone is not going to work (easily) as you have two things going on

insert into student (name)
select distinct studentName
  from table;

insert into teacher (name)
select distinct teacherName
  from table;

insert into studentTeacher(studentID, teacherID)
select student.ID, teacher.ID
  from table
  join student
    on student.name = table.studentName
  join teacher
    on teacher.name = table.teacherName;


Why does the scipt executes only once the sqlscript

As you haven't shown us the sql files I'm going to take a guess that you haven't included the exit statement. This means that SQL*Plus will be invoked, run the commands/sql in the first file and then sit there waiting for the next SQL command. This means the 'sleep 2' command is never reached.

Try this in your sql file instead

 update db.table_name set column = bla where column=foo;
 commit;
 exit


Since you are using --master_data to take a consistent value of master status.

The internals of mysqldump will issue below commands to mysql server.

2017-05-31T04:39:05.843130Z    48 Query /*!40100 SET @@SQL_MODE='' */
2017-05-31T04:39:05.843273Z    48 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-05-31T04:39:05.843411Z    48 Query FLUSH /*!40101 LOCAL */ TABLES
2017-05-31T04:39:05.846031Z    48 Query FLUSH TABLES WITH READ LOCK
2017-05-31T04:39:05.846166Z    48 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-05-31T04:39:05.846279Z    48 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-05-31T04:39:05.846413Z    48 Query SHOW MASTER STATUS
2017-05-31T04:39:05.846539Z    48 Query UNLOCK TABLES
..
..... Here it continues to take backup of data and structures .

What had happened?:

Your backup would have just started and there was a query that was going on running on a particular table for a longer period of time before even FLUSH TABLES command and didn't release the lock on the table and FLUSH TABLES must have been waiting for that thread to get completed or keep trying to flush until the revision_version of that table is same as all tables.

Thus you get other threads blocked for other tables as well. As this is entire DBs*.Tables* level lock while flush tables was going on. Finally it would have got accumulated every new connection in processlist and piled up until max_connections and not allowing anyone to login.

Let's say if you have managed to login to the terminal and tried to kill flush tables, I don't think there is a way to pull back or rollback the flushed tables that has been done and release its own thread connection. So it might in a KILLED STATE for longer time. And thus you might have reached the last option that is to restart the server.

How to fix it?:

At the time of issue, when admin managed to login to mysql prompt.

Instead of issuing kill command on FLUSH TABLES thread, if kill was given to the thread running for long SELECT.There are chances that SELECT would have dropped and table is left FLUSH TABLES to acquire and update revision_version and release the lock for new queries. And backup would have continued. Since I don't think anyone expecting an answer on the other end waiting for the result for query running long hrs.

What is the long term solution?:

You have to ensure no such long time queries to be running at the time of backup.
Looks like this could be a new deployment or someone triggered a bad query and didn't bother to close the session.
Try to kill the query if a query is running more than Xsecs (Depends upon your requirement).
      Or
Have an adjustment with the teams to tune the queries or send it to Admin to give them the result data or give them a separate slave all together.


try this:

[mysqld]  # THIS LINE ALREADY EXISTS – DO NOT ADD  THIS. Just ADD the line below.

max_allowed_packet=500M

and restart the MySQL Server.


If application is not reserved for database engine purposes only, it was recommended to limit it to 60%-70% of the total resources available at the server. Enterprise applications those using open transactions when the application is running, most likely to reach maximum memory usage as it can reserve.

Changing maximum server memory via SSMS does the triggering automatically and no restart of the services needed. Hereby you can also use following script to get it programmatically which also doesn't need a service restart afterwards;

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'12000'  // Amount of Memory Limit in MegaBytes
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE ";
GO


Pros and Cons of creating a new table to store matches vs frequently running query to find matches

It really depends on your requirement. Once the user has thier results, how much do you care about what they wanted, got, liked or disliked?
It sounds to me you want to learn from customer experience and preferences, so storing both the query and the response would be useful. Storing that on the db server means you can analyse the queries and results.
Personally i would save the query parameters and results (and thier response), as xml or json in 2 seperate fields.
Xml or json are useful because they allow your query/results to evolve over time with minimal changes to schema.
If you can just store the venue id, the results wont be huge. All depends on how much you need to store. If you can view the results with the query for context it should make sense.
Its worth considering that if venues are added or removed that a user will get a different result if they run the same query at a later date. So you may want go store queries and results in different tables if you want to retain a 1 to many relationship and history over time. E.g if i have my favourite query and run it once a week, do you want to store each result?


Delete all highscores from a certain country

DELETE B.* FROM
(
    SELECT playerId FROM tbl_players
    WHERE playerCountry = 'lt'
) A INNER JOIN tbl_highscores B
USING (playerId);

Make sure you have this index

ALTER TABLE tbl_players ADD INDEX CountryIDIndex (playerCountry,playerId);


Instead of using a trigger, I would use an indexed view.
That way, the two views of the data are automatically kept synchronized by SQL Server, without writing any trigger code. You also don't have to worry about subtle race conditions and other potential concurrency issues.
Indexed view
CREATE TABLE dbo.Upvotes
(
    ImageId integer NOT NULL,
    UserId integer NOT NULL,
    Score smallint NOT NULL,

        CONSTRAINT ValidScore
        CHECK (Score IN (-1, 0, +1)),

        CONSTRAINT PK_dbo_Upvotes__ImageId_UserId
        PRIMARY KEY (ImageId, UserId)
);
GO
CREATE VIEW dbo.Images
WITH SCHEMABINDING
AS
SELECT
    U.ImageId,
    VotesUp = SUM(CASE WHEN U.Score = +1 THEN 1 ELSE 0 END),
    VotesDown = SUM(CASE WHEN U.Score = -1 THEN 1 ELSE 0 END),
    NumRows = COUNT_BIG(*)
FROM dbo.Upvotes AS U
GROUP BY
    U.ImageId;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Images (ImageId);

The query in the view is essentially a pivot. It is not written using PIVOT syntax, since that is not allowed in a SQL Server indexed view.
For Enterprise/Developer Edition, the optimizer will make a cost-based decision to use the view's index(es), or expand the view and access the base tables. You can force using the view indexes with a NOEXPAND hint.
In other Editions you must use the NOEXPAND hint to avoid expanding the view. There are other reasons to prefer using NOEXPAND hints with indexed views.
Example
SELECT
    I.ImageId,
    I.VotesUp,
    I.VotesDown
FROM dbo.Images AS I WITH (NOEXPAND);


Prevent users from disabling FK checks in InnoDB

You can set triggers on the relevant tables to check for foreign_key_checks=0 and signal an error if that's the case.

Call this procedure to check for the setting and signal the error:

DELIMITER $$
CREATE PROCEDURE force_foreign_key_check ()
BEGIN
    IF @FOREIGN_KEY_CHECKS = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'foreign_key_checks is set to 0. Operation canceled.';
    END IF;
END$$
DELIMITER ;

And set your triggers like this:

CREATE TRIGGER force_foreign_key_check_insert_table1
BEFORE INSERT ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_update_table1
BEFORE UPDATE ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_delete_table1
BEFORE DELETE ON table1
FOR EACH ROW CALL force_foreign_key_check;


This is definitely possible both with VMware and VirtualBox and any other mainstream virtualization product as well I guess.

On my VMware vSphere sandbox, I have created an additional virtual switch that is not mapped to any physical network adapters. This network is not visible/reachable from the outside world, but VMs attached to it can reach each other through interfaces using this switch, so it's perfect for a RAC private network with 1 host machine.

In VirtualBox, I use NAT Network (available since version 4.3) for RAC private network. It's a similar concept, a virtual network not visible/reachable from the outside world, but VMs with interfaces in this network can communicate each other through it.

You can use the above for RAC public networks as well, so you don't need to allocate any IP addresses on your real physical network. For example, in VirtualBox, you can use 1 NAT network interface as "management interface", and define port forwarding rules on it, so you can access the VM from your local machine thorugh RDP/SSH without using the VM console. Then use another NAT Network interface for the RAC public network, and another NAT Network interface for the RAC private network, so 3 interfaces in total.

I would not bother with openfiler, both VMware and VirtualBox support shared virtual disks.


How do I restrict PostGIS database with user specific access?

I would use schemas to manage the individual department objects.  For example:

    CREATE SCHEMA elect_dept;
    CREATE SCHEMA water_dept;

Store each department's objects in the appropriate schema and use roles to manage access, for example:

   CREATE ROLE elect_dept;
   ALTER ROLE elect_dept SET SEARCH_PATH TO elect_dept,water_dept,public;
   GRANT USAGE ON SCHEMA elect_dept,water_dept TO elect_dept;
   GRANT SELECT ON ALL TABLES IN SCHEMA water_dept TO elect_dept;
   GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA elect_dept to elect_dept;

In the above example the role elect_dept can view water_dept schema objects and edit elect_dept schema objects.

It may make sense to create the roles as group roles and grant them to individual named accounts.

    GRANT elect_dept to smithj;

A little rough and ready, but with some tidying, should give you what you need.


SQL row data mismatch for different users

Some things must be put in a "transaction".

One example:

BEGIN;
SELECT ... FOR UPDATE;
do some non-sql processing
UPDATE ...  -- specifying the row that was `SELECTed`
COMMIT;

Without the FOR UPDATE, another connection might sneak in and update (or delete or ...) the row that you thought you had.


How to create via sql and detect via ERRORLOG SQL Server 2008 Deadlocks

Here is a link I read awhile back about retrieving deadlock information via extended events.
http://www.sqlservercentral.com/articles/deadlock/65658/

My guess is that if your query is still running then you have blocking not a deadlock.  Are you sure you went back to session 1 and ran the additional update statement?


Is there a way to add a column to a table without affecting performance?

For Postgres, adding a column without a default value is essentially a no-op as only the catalog tables get rewritten.

Apart from the short exclusive(!) lock there is no performance impact.

Although that lock is only held for a very short time, getting the lock on a busy system might be a problem because all open transactions and queries need to be finished before this lock can be acquired. And of course you need to make sure that the DDL gets committed immediately, because every access (including read-only selects) will be blocked until the alter table is committed.

Adding a column with a default value is a different thing though - that will affect performance because it is updating all existing rows with the default value.

If I'm not mistaken MySQL always rewrites the full table (and all indexes) if you add (or drop) a column (but it's been a while since I used MySQL so things could have changed).

Oracle works the same as Postgres.

I don't know about SQL Server, but I would be surprised if it did it differently.


I have dealt with this pesky 0 to infinity and back to 0 issue with Seconds_Behind_Master. Here are my past posts where I have addressed this:

Jun 17, 2011 : MySQL Replication Lag Behaving Erraticly
Sep 24, 2012 : MySQL replication - slave is continuously lagging behind master
May 04, 2014 : MySQL 5.6 showing wrong "second_behind_master"

Although there is a bug fix in place based on binlogs not being flushed properly and if you configure sync_binlog=1 on Master1 and Master2, it may exacerbate the problem.
Even in a perfect world for Binary Logs, sometimes there will be occasions when queries will bunch up on one Master and have the same timestamp. When those queries are completed in parallel on a Master, every Slave processes them in a serialized manner.
EXAMPLE
If 60 queries take one second to run on a Master and all of them complete at the same time...

all 60 queries will be written to the Master's Binary Logs on-by-one
all 60 queries will have the same timestamp
each of the 60 one-second queries are executed one-by-one of the Slave
The timestamp of each query won't move while NOW() on the Slave is moving (The difference between NOW() on the Slave and the timestamp of the query from the Master is Seconds_Behind_Master)
When the first 59 queries are processed, Seconds_Behind_Master will be 59
When the 60th query is processed, Seconds_Behind_Master drops to 0

Given this computation of Second_Behind_Master and Master1 records multiple queries in the same timeframe, Master2 will always experience this time warp.
SUGGESTION
You could try zapping all the relay logs on the Master2.

On Master1, STOP SLAVE;
On Master2, SHOW SLAVE STATUS\G
On Master2, get LOGFIL from Relay_Master_Log_File
On Master2, get LOGPOS from Exec_Master_Log_Pos
On Master2, CHANGE MASTER TO master_log_file='LOGFIL',master_log_pos=LOGPOS;
On Master2, START SLAVE;
On Master1, STOP SLAVE;

Once you run these steps, you have a clean set of relay logs on Master2, but you still subject to Slave Lag emanating from Master1 due to multiple queries completing at the same time.
All the numbers I mentioned in the Example assumes that Master1 and Master2 have identical  Storage Engine configs, identical OS, identical hardware, identical disks, and excellent network communication. If Master2 is inferior to Master1 in any of these aspects, perhaps the 60 seconds to process the queries on the Slave will be more like 70 or 80 seconds.
If the hardware, software, and Storage Engine configs are not identical, then make them identical and restart MySQL as needed.
These are the facts. There is no "Magic Bullet" for this phenomenon so long as your hammer multiple INSERT, UPDATEs, and DELETEs on Master1.
LOOKING OUTSIDE THE BOX

What you need to look for in the Master1's Binary Logs is 15-second Windows of Time among the timestamps and see how many INSERT, UPDATEs, and DELETEs and being done.
If your application performs these writes with the same rhythmic pattern, then you need to look at the application as to how it does writes in InnoDB and MyISAM.
Look around for any cronjobs that perform any scheduled writes


17 minutes is not a reasonable time to wait. Try to provide an index hint:

SELECT *
FROM contact USE INDEX (IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS)
WHERE list_id = '014c7cba-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0


How to rewrite subquery to return only 1 row

Change your subquery select to count(*) e.g.

select o.OrderID, o.OrderAmount
from Orders o
where (select count(id) from Disputes d where d.CustomerID = o.CustomerID) <= 0


What is the best practice for mapping from natural keys to integer-based keys? (ETL)

I have found building a relationship table between the old (natural) and new (integer) keys works well in conversion processes such as this.  The integer key is built using the standard auto-increment method for the database.  Maintain unique keys on the data which forms the natural key in the new tables.

Develop a repeatable data synchronization method that can be applied as often as required.  Depending on your needs this can be daily, weekly, monthly, or on-demand.  Make the synchronization modular so it can be updated as required.  In some development processes you may end up feeding from the new system back to the old system.

As you develop and deploy the system, track which system is the system of record for which data.  Move control to the new system as possible.  You may want or need processes to identify and deal with changes which occur on the system which is not the system of record.

Manage change overs from the old system to the new.  If possible, disable updates in the old system as the system of record is changed to the new system.  This will require a feed from the new system to the old.


How to properly restore database from file groups backups?

The sequence may be a bit off for a piecemeal restore you're attempting, in particular when to apply the tlogs. This is what TechNet has documented.

  Piecemeal Restore Under the Full Recovery Model

  Under the full recovery model or bulk-logged recovery model, piecemeal
  restore is available for any database that contains multiple
  filegroups and you can restore a database to any point in time. The
  restore sequences of a piecemeal restore behave as follows:

  •Partial-restore sequence

  The partial restore sequence restores the primary filegroup and,
  optionally, some of the secondary filegroups.

  The first RESTORE DATABASE statement must do the following:

  ◦Specify the PARTIAL option. This indicates the start of a piecemeal
  restore.

  ◦Use any full database backup that contains the primary filegroup. The
  common practice is to start a partial restore sequence by restoring a
  partial backup.

  ◦To restore to a specific point in time, you must specify the time in
  the partial restore sequence. Every successive step of the restore
  sequence must specify the same point in time.

  •Filegroup-restore sequences bring additional filegroups online to a
  point consistent with the database.


SqlPS and PowerShell with Import-Module SQLPS different behaviors

This is how I alter TCP to use a specific port including enabling TCP at the end -- You have $Tcp.IsEnabled = $false is that correct?

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer .
$Instance = $mc.ServerInstances[$InstanceName]
#Change TCP Port
$TCP = $Instance.ServerProtocols['Tcp']
$AllIps = $TCP.IPAddresses['IPAll']
$AllIps.IPAddressProperties['TcpDynamicPorts'].Value = ''
$AllIps.IPAddressProperties['TcpPort'].Value = $Port
$TCP.IsEnabled = $true
$TCP.Alter()

so I would do the following

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer .
$Instance = $mc.ServerInstances[$InstanceName]
#Enable TCP
$TCP = $Instance.ServerProtocols['Tcp']
$TCP.IsEnabled = $true  ## or false to disable
$TCP.Alter()


How to remove specials characters, but replace spaces?

Do the space-to-underscore replace() first, and your existing expression will do the rest.
testdb=# select regexp_replace(replace('ABC%$! DEF*& GHI', ' ', '_'), '[^\w]+','','g');
 regexp_replace
----------------
 ABC_DEF_GHI
(1 row)


I would use separate tables for the project-sponsor and event-sponsor relationships.  You are making two many-to-many relationships here, and that just seems like the right way to do it to me.

Can each project/event/org have more than one leader?  If not, why not simply put the leader id in the project/event/org table?  Otherwise, I would use three tables for the same reason as above: you are describing three many-to-many relationships.


How to check the program/applicationname that triggered function?

You can use select current_setting('application_name'); but I've seen instances of client program (not written by me) not giving application name after a connection reset. You should also see comment from @a_horse_with_no_name above.


ALTER fixed-length COLUMN on compressed table

Documentation

There is no documentation I am aware of for metadata-only ALTER COLUMN operations on compressed tables, beyond the very general statement:

  Changes you specify in ALTER TABLE implement immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows.

One could interpret that as meaning that SQL Server may avoid a size-of-data operation if the particular storage format in use means the data does not change. Continuing in that generous vein, one could argue that whether a particular change is metadata-only or not depends on what has been implemented as well as what is logically possible.

Documentation notes were added when SQL Server 2012 added support for adding a NOT NULL column with a runtime constant default value, and the documentation has been updated for new ONLINE operations. If you feel documentation would be useful for metadata-only data type changes on compressed tables, you should submit feedback. I would not personally want to be the person tasked with that effort, or keeping it up to date.

In the meantime, I wrote what I have been able to discern about this behaviour in New Metadata-Only Column Changes in SQL Server 2016.

Does SQL Server 2017 improve this?

From my testing, the new behaviour was enabled in SQL Server 2016. It can be disabled with undocumented startup or global trace flag 3618:

DBCC TRACEON (3618, -1);

A demo:

CREATE TABLE dbo.test1
(
  c1 bigint NOT NULL,
  c2 integer NOT NULL,
  c3 char(4) NOT NULL
  primary key (c1)
)
WITH (DATA_COMPRESSION = ROW);

INSERT dbo.test1 WITH (TABLOCK)
(
    c1,
    c2,
    c3
)
SELECT
    N.n,
    CONVERT(integer, N.n),
    LEFT(N.n, 4)
FROM
(
    SELECT
        n = ROW_NUMBER() OVER (ORDER BY @@SPID)
    FROM sys.all_columns AS AC1
    CROSS JOIN sys.all_columns AS AC2
    ORDER BY n
    OFFSET 0 ROWS
    FETCH FIRST 5 * 1000 * 1000 ROWS ONLY
) AS N;

-- Both metadata only
ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint NOT NULL;
ALTER TABLE dbo.test1 ALTER COLUMN c3 char(8) NOT NULL;

DROP TABLE dbo.test1;


Using a combination of block storage and object storage for serving photos. Good idea?

The problem with block storage is that you loose all the benefits of integrity constraints - photos will get forgotten when listings are deleted etc...

Block storage is likely to be faster and is easier to diversify if there are speed problems,  so your trade-off is speed and scalability vs ease of development and management.


If the table tMaster have the same pid inserted but is not in the table trendContentMaster it will update 20*(rows) depending how many times pid is inserted in tMaster table. This should work for you:

    DELIMITER $$
    CREATE PROCEDURE updateTrendContentStats(
    IN mongoid VARCHAR(255),
    IN numericid INT,
    IN points INT,
    IN ctype INT,
    IN activity INT,
    IN created INT)
     BEGIN
     DECLARE vCount INT(1) default 0;
     DECLARE vCount02 INT(1) default 0;

     SET vCount=(SELECT count(*) FROM trendContentMaster WHERE pid=numericid);
     SET vCount02=(SELECT count(*) FROM tMaster WHERE pid=numericid);

     IF (vCount = 0 && vCount02 = 0) THEN
        INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s) VALUES(mongoid,numericid,ctype,points,points,points,points,1);
     ELSE
        UPDATE tMaster
        SET tcd = tcd + points,
            tcw = tcw + points,
            tcm = tcm + points,
            tcf = tcf + points WHERE pid=numericid;
     END IF;
     INSERT INTO tTransaction(tci,pid,tct,act,acp,cd) VALUES(mongoid,numericid,ctype,activity,points,created);
     END $$
    DELIMITER

PD: You should make a Foreing key in tMaster.pid.


This is standard SQL

SELECT
   *
FROM
   EXAM E
WHERE
   NOT EXISTS (SELECT *
       FROM
          CANCELED_EXAM CE
       WHERE
          E.StudentID = CE.StudentID AND
          E.SubjectID = CE.SubjectID AND
          E.SchoolYear = CE.SchoolYear AND
          E.Period = CE.Period )


SQL Server: How to get total number of backups done in a month?

You might use below script to get history of backups in last 1 month:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Here 30 is a variable, which can be changed with any number up to which you want backup details however this would work only if you are not archiving backupset table in msdb database.

Please refer below links for detailed info on these backup sets and what is backup fails:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Query that displays backup information (successful and failed) SQL Server

Hope this helps.


Table with relationship to multiple tables

Your current diagram is great as long as everyone only has one address, and a max of two phone numbers (apart from a fax).

One simple way this schema falls apart is if you have a client with a street address for their location, but a PO Box for their mailing address.  This is a fairly common setup for rural communities in the United States.  When I lived in Jackson, Wyoming I had a street address which had a different zip code from my PO Box. In addition my work address was in another city. The core point is people can easily have more than one address, which conflicts with your schema.

Personally I would break apart the address from the phone number. This gives you more freedom over your data.  Entities can have addresses without phone numbers and phone numbers without addresses without risking a foreign key to a row full of nulls.  Furthermore I would add a foreign key to the address and phone number tables that is just a link to a description.  That way you don't need to remember "PhoneNumber2 is always a cell phone, unless this is a business in which case it is the point of contact's direct line" or other such nonsense. The foreign key to the description table will make it clear that Client X has two home landlines and no cell phones.  You gain flexibility and clarity.

Overall, I still would keep one address and one phone number table that the various entities would link to via foreign keys.  There is no need to duplicate schema.  Keep in mind it might be advantageous to have the foreign key to the address/phone table on the entity for some entities and other entities might use a join table instead.  That can be a per entity judgment call which can be adjusted over time with relatively little pain.

Also you might want to associate the "next_of_kin" table with someone (your clients?), because it seems kind of weird for them not to have kin...  :)


Tempdb log file was growing continuously on SQL Server 2008 R2-SP2. Could a pending insert statement which uses temporary table be a cause?

on this question:

How to identify which query is filling up the tempdb transaction log?

you will be able to see a lot more, in different ways and with more technical information as why this is happen and what to do regarding it.

below is a script that I have to check what is filling up the tempDB.
it may be a good starting point for your quest.

use tempdb

SELECT top 100
tsu.internal_objects_alloc_page_count,
tsu.user_objects_alloc_page_count,
es.host_name ,
es.login_name ,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,

SUBSTRING(st.text, er.statement_start_offset/2 + 1,
          (CASE WHEN er.statement_end_offset = -1 THEN
               LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE
               er.statement_end_offset
           END - er.statement_start_offset)/2) as Query_Text,

tsu.session_id ,
tsu.request_id,
tsu.exec_context_id,

(tsu.user_objects_alloc_page_count
  - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,

(tsu.internal_objects_alloc_page_count
 - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,

er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,er.writes,
er.logical_reads,
er.granted_query_memory

FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er
        ON (     tsu.session_id = er.session_id
             and tsu.request_id = er.request_id)

inner join sys.dm_exec_sessions es
       ON ( tsu.session_id = es.session_id )

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

WHERE (tsu.internal_objects_alloc_page_count
       + tsu.user_objects_alloc_page_count) > 0

ORDER BY (tsu.user_objects_alloc_page_count
          - tsu.user_objects_dealloc_page_count)
        +(tsu.internal_objects_alloc_page_count
           - tsu.internal_objects_dealloc_page_count)

DESC


How to install and configure Postgres-XC in windows?

Postgres-XC does not run on Windows:

  Postgres-XC currently runs on the Linux Operating Systems running on
  64-bit Intel(R) processors.

  The development team tested this with Cent-OS 5.3 and 5.4.

You might be able to build the data nodes and possibly the coordinators on Windows, but the GTM will take major work to port to Windows.


Mongo connection failing with 'Transport endpoint is not connected'

I'd really need to see the mongod logs to be sure here, but the two most common causes are covered by doing 2 things:

Check your ulimits on the client and the server (the too many open files page is a good start)
Lower your tcp_keepalive_time from the default (again client and server)

For load testing you should just set the ulimit settings (-n -u etc.) to anything over 20,000 - that is the limit internally in MongoDB currently for open file descriptors.  You may also want to make sure that the Apache user you are running under is similarly OK in terms of its ulimit settings give that you are using PHP.

Looking at the output of lsof should give you clues as to where the open file descriptors are coming from.


What is the SQL Standard method of doing LIMIT OFFSET?

SQL 2011 Spec

The syntax in the spec is defined as,

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

You can see here that <result offset clause> and the <fetch first clause> are both optional and stand by themselves, moving on

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
  <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

<simple value specification> ::=
  <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

RDBMS-Specific

PostgreSQL

The SQL:2011 Spec supports a bit more than PostgreSQL, namely a WITH TIES option (F867) and the ability to specify PERCENT option (F866). The query is defined as,

PostgreSQL only documents this, AFAIK, in SELECT,

SELECT
...
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

For more information aside from PostgreSQL, and the spec see the tag-info for offset-fetch


Using dynamic date filter causes data not to return

Okay, figured it out, leaving this here in case anyone else comes across a similar issue.

For some reason Oracle will not compare a DATE data type to a SYSDATE when it has been place through a function such as:

SYSDATE - (TO_CHAR(SYSDATE,'d')-1)

To overcome this limitation, I used the NEXT_DAY() and TO_DATE() functions to build the following query:

TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')

Using this method, the below filter returns a result set, and has a low cardinality only slightly higher than a static filter.

      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')
  OR
      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-371,'Monday'),'DD/MON/YY')

If anyone has any comments about WHY oracle requires you to use TO_DATE(), please extrapolate, because this confused the daylights outta me XD


Importing a mysql database after drop filling up limited / in /tmp deleted file - A problem?

SHOW FULL PROCESSLIST -- there is probably a complex query running, creating a huge tmp table.

Kill that process (go into mysql commandline tool, do SHOW PROCESSLIST to get the pid, then do kill, in the tool, with that number.)

Then scratch your head as to why it is taking so much room.  A likely case:

SELECT ... FROM a, b;

and failing to restrict how a and b should be JOINed.  This is called a CROSS JOIN; it can be huge.


How do I properly design a many-to-many (charges/payments) accounting system?

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.

This is the problem. There are several styles of accounting. Are you using double-entry? I would recommend learning double-entry accounting. It's complicated but it works.

Is this for an actual assignment or for learning?

Here's a system that may be easy enough to dig into to give you the concepts you need to move forward? I know the author (by way of this podcast but not personally) but I haven't looked at the codebase. I just know that it does DE accounting.

http://twit.tv/floss150


sql producing duplicate column id on join?

SELECT products.*, productimg.url
  FROM products JOIN productimg
  ON products.id = productimg.id
  WHERE products.id IN (1, 2, 3)


Extended event (sp_statement_completed) firing multiple times

Looks like the issue is because my stored procedures and ad hoc queries might be running more than one sql statement ... thus creating multiple hits. Really what I need is batch complete. Alas, it does not exist in SQL Server 2008. Probably gonna have to resort to a server trace.


How to search one bible verse from a source that may contain ranges?

This is very doable, assuming you setup the structure correctly.

create database dba248824;
use dba248824;

This post table is a placeholder to make the queries work. This table already exists in your project, under some name (I don't know what), and holds your posts that have references to Bible verses.

CREATE TABLE post (
`post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`post_text` MEDIUMTEXT NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Next up, we have a table to define all the books of the bible. It is not strictly necessary to have a separate table, and purists will lash me for not making use of natural keys, but I choose to include this table to minimize the size of the indexes in other tables. This only needs to be populated once, with all the books of the Bible.

CREATE TABLE bible_book (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`bible_book_name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`bible_book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The bible_verse table also only needs to be populated once, and would contain every verse in the Bible. Actually, if you only need to reference the verses and not return the text of the verses you can completely skip this table.

If you choose to include this table, you may choose to add a FOREIGN KEY constraint from post_bible_verse to reference this bible_verse table.

CREATE TABLE bible_verse (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
`text` VARCHAR(255) NOT NULL,
PRIMARY KEY (`bible_book_id`,`chapter`,`verse`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The post_bible_verse table is the work-horse of this setup. Here, you recover each individual verse that is referenced in a post. You'll have to translate ranges into a list of individual verses using some server side code, but once done you can easily insert into here each time a post is made referencing some verses.

CREATE TABLE post_bible_verse (
`post_id` INT UNSIGNED NOT NULL,
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`post_id`,`bible_book_id`,`chapter`,`verse`),
UNIQUE KEY (`bible_book_id`,`chapter`,`verse`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

To see how this setup works, lets populate some values.

First, the bible_book table. I used the "number" of the book in the Bible as the bible_book_id primary key. That is, John is the 43rd book of the bible, so it gets bible_book_id of 43. Etc.

(Remember, this only has to be done once in your case, to include all books.)

INSERT INTO bible_book
(bible_book_id,bible_book_name)
VALUES
(43,'John'),
(40,'Matthew'),
(41,'Mark');

Then, we populate all the Bible verses. As mentioned before, this is not necessary if you don't need to return the actual text of the verses.

This INSERT statement includes all the verses you mention in your example.

INSERT INTO bible_verse
(bible_book_id,chapter,verse,text)
VALUES
(43,5,1,'actual verse text here'),
(43,5,2,'actual verse text here'),
(43,5,3,'actual verse text here'),
(43,5,4,'actual verse text here'),
(43,5,5,'actual verse text here'),
(43,5,6,'actual verse text here'),
(43,5,7,'actual verse text here'),
(43,5,8,'actual verse text here'),
(43,5,9,'actual verse text here'),
(43,5,10,'actual verse text here'),
(40,3,2,'actual verse text here'),
(41,1,1,'actual verse text here'),
(41,1,2,'actual verse text here'),
(41,1,3,'actual verse text here'),
(41,1,4,'actual verse text here'),
(41,1,5,'actual verse text here');

Create a post to represent what is in your system. (You won't need to do anything in your real setup, as your blog posts already exist and presumably have some id column to use.)

INSERT INTO post
(post_id,post_text)
VALUES
(1,'some blog post here');

And finally, insert all the verses that correspond to this post. You'll have to include code to insert these verses when a post is made or updated, as a text range will not work.

INSERT INTO post_bible_verse
(post_id,bible_book_id,chapter,verse)
VALUES
(1,43,5,1),
(1,43,5,2),
(1,43,5,3),
(1,43,5,4),
(1,43,5,5),
(1,43,5,6),
(1,43,5,7),
(1,43,5,8),
(1,43,5,9),
(1,43,5,10),
(1,40,3,2),
(1,41,1,1),
(1,41,1,2),
(1,41,1,3),
(1,41,1,4),
(1,41,1,5);

Now lets see it in action.

User searches for John 5,3. Server parses that and runs the following query:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 43
  AND pbv.chapter = 5
  AND pbv.verse = 3
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And it returns post_id of 1 (which is the post that referenced those verses.)

User searches for Matthew 3,2:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

Again found our post.

Now Mark 1,2.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 41
  AND pbv.chapter = 1
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And finally, search for Matthew 3,1, which is NOT referenced by your post.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 1
  AND pbv.post_id = post.post_id
);

Empty set

And we verify that the post is NOT found this time.

So, yes, it is very possible to create a database structure that allows you do search for posts based on Bible verses, but it does require an appropriate setup, and parsing of the ranges into specific queries.


relationship between transaction chaining and autocommit

AutoCommit true is identical to chained off

AutoCommit false is identical to chained on

In chained mode your first statement will start an implicit transaction, which will stay active till you manually commit or rollback.

Even though the manual says the default mode is unchained this is not true for client libraries. The JDBC and Python drivers use a default mode of chained unless you overwrite that.


not-yet-assigned record is indeterminate, but triggers on update (postgresql 9.3)

My mistake. Trigger was for each statement; should have been for each row.

CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test
    FOR EACH ROW EXECUTE PROCEDURE simple_trigger();


PostGIS Intersects Query takes lot of time to get the result

I suggest to calculate the intersection of the geometries beforehand only once and store it in an additional table evidensapp_seniangcbr_polystructures_intersection containing an id, evidensapp_seniangcbr_id pointing to evidensapp_seniangcbr.id and evidensapp_polystructures_id pointing to evidensapp_polystructures.id.

Then you can get rid of the geometry intersection in

INNER JOIN  evidensapp_polystructures ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )````

and instead use a join using the ids

INNER JOIN evidensapp_seniangcbr_polystructures_intersection intersec ON evidensapp_seniangcbr.id = intersec.evidensapp_seniangcbr_id
INNER JOIN evidensapp_polystructures ON evidensapp_polystructures.id = intersec.evidensapp_polystructures_id

Off course this only works, if your data does not change. Otherwise you need a trigger doing the intersection and filling the evidensapp_seniangcbr_polystructures_intersection table.


How to make a PostgreSQL trigger thread-safe

According to this post, all triggers execute as part of the same transaction as the original query, so you would not need a lock statement:
https://stackoverflow.com/questions/10727335/after-insert-trigger-in-separate-transaction


Constructing Circularly Referenced Tables

You must add the foreign key constraints after the tables have been created, using: alter table table_name add constraint constraint_name <constraint clause>;

There is no other way.

Note that, due to your table design, you may have to create the constraints with the  initially deferred deferrable clause. This forces Oracle to defer constraint checking until commit time & may be necessary if you cannot use an INSERT ALL statement and need to insert dependant rows into the two tables with separate statements.


MYSQL - Unable to change 'old_passwords' variable

I have 3 additional suggestions (choose one):

Downgrade the PHP drivers (which understand the 16 character encrypted password)
Upgrade to the latest MySQL (where you can set old_password=1 from inception)
Change all the passwords in mysql.user to 41 character encrypted password format

Whatever you do, do not use the MD5 function to make new passwords. Use the PASSWORD function. It is very different from MD5:

The password function PASSWORD function is the equivalent of

SET @my_new_password = 'WhateverIWantAsThePassword';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_password))))) MySQLPWDHASH;

I learned that like 2 years ago from this PalominoDB Blog Post.

You can QA the PASSWORD function with OLD_PASSWORD function. Compare the output of SELECT PASSWORD(@my_new_password); WITH SELECT OLD_PASSWORD(@my_new_password);.


To find rows where at least three out of four of those columns match you can use.

SELECT  D1.ID, D2.ID
FROM   DATA D1
       JOIN DATA D2
         ON D1.SSN = D2.SSN
            AND D1.ID > D2.ID
            AND 2 <= CASE
                       WHEN D1.FNAME = D2.FNAME THEN 1
                       ELSE 0
                     END +
                     CASE
                       WHEN D1.LNAME = D2.LNAME THEN 1
                       ELSE 0
                     END  +
                     CASE
                       WHEN D1.DOB = D2.DOB THEN 1
                       ELSE 0
                     END
UNION ALL
SELECT D1.ID, D2.ID
FROM   DATA D1
       JOIN DATA D2
         ON D1.DOB = D2.DOB
         AND  D1.FNAME = D2.FNAME
         AND  D1.LNAME = D2.LNAME
         AND D1.SSN <> D2.SSN
         AND D1.ID > D2.ID

The top branch gets all rows where the SSN are the same and at least 2 out of the three other columns are the same. The join on SSN is likely to be pretty selective in itself.

That just leaves one other possible three column combination left which is dealt with by the second branch.

Both branches of the UNION ALL have an equi join so it should perform better than a join with some complicated OR condition.


One option would be to use a not exists to filter out any rows that appear for a later date.

select date, count(*)
  from your_table a
 where not exists( select 1
                     from your_table b
                    where a.id = b.id
                      and b.date > a.date )
 group by date


There are more efficient ways of doing this if your employee table is large, but this is the way I find easiest to understand :)

testbed:

create table employee( employee_id integer primary key,
                       name varchar(100) not null,
                       hire_date date not null );

insert into employee(employee_id, name, hire_date)
values(1, 'Alice', to_date('20090909', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(2, 'Bob', to_date('20101010', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(3, 'Chris', to_date('20111111', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(4, 'David', to_date('20101231', 'YYYYMMDD'));

query:

with w as ( select e.*, ( select max(add_months(hire_date, 12*level))
                          from dual
                          connect by add_months(hire_date, 12*(level-1))<sysdate )
                      as next_anniversary
            from employee e )
select w.*, round(months_between(next_anniversary, sysdate)) as months_from_now from w;

result:

EMPLOYEE_ID            NAME       HIRE_DATE                 NEXT_ANNIVERSARY          MONTHS_FROM_NOW
---------------------- ---------- ------------------------- ------------------------- ----------------------
1                      Alice      09-SEP-09 00.00.00        09-SEP-12 00.00.00        10
2                      Bob        10-OCT-10 00.00.00        10-OCT-12 00.00.00        11
3                      Chris      11-NOV-11 00.00.00        11-NOV-12 00.00.00        12
4                      David      31-DEC-10 00.00.00        31-DEC-11 00.00.00        1

You may prefer floor or ceil to round.


MongoDB denying API access, while console shell works

The cause of this problem was likely a MongoDB server configuration issue, relating to IP address binding. Changing 127.0.0.1 to 0.0.0.0 resolved the problem.


mysql column naming conventions for foreign keys

I prefer user_id but I think it doesn't really matter as long as you stay consistent and not mix them up so you keep your overview structured and readable.


Backup and restore SQL server database with FILESTREAM filegroup

I would like to know if is it possible to modify the content of all filestream columns when performing a backup

No.

I would create my test database with the same schema as the production database, and then write a custom script to sync the data in the primary filegroup (this is fairly easy as your production system is the source, so the sync is only in one direction).

Then with the filestream data, I would stub the files, which could be done any number of ways. Off the top of my head, I'd somehow generate zero-byte files on the test side.


Got it !

SELECT
case jsonb_typeof(json)
    when 'string' then json->>0
    when 'array' then jsonb_array_elements_text(json)
end
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json;


An option you can try is to combine partitioned views with the table level partitioning.  By having a partitioned view which is current and historic tables unioned together, you will be able to have different table options i.e. fill factor etc.  This also with give better statistics on the active portion of the data as it's no longer skewed with all the historic data.

USE master;
GO
DROP DATABASE PartitionDemo
GO
CREATE DATABASE PartitionDemo
GO
USE PartitionDemo;
GO
CREATE PARTITION FUNCTION everyHundredThousand(int) AS RANGE RIGHT FOR VALUES(100000,200000,300000,400000,500000,600000,700000,800000,900000,1000000)
CREATE PARTITION SCHEME allPrimary AS PARTITION everyHundredThousand ALL TO ([PRIMARY]);
GO
CREATE TABLE    dbo.HistoryTable
(
    ID  int NOT NULL /*Note not an identity*/,
    Filler char(200) NOT NULL,
    CONSTRAINT PK_HistoryTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=100)
) ON allPrimary(ID)
GO
CREATE TABLE    dbo.CurrentTable
(
    ID  int NOT NULL IDENTITY(0,1),
    Filler char(200) NOT NULL,
    CONSTRAINT PK_CurrentTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=80)
) ON allPrimary(ID)
GO
/* Now generate some data */
INSERT  CurrentTable
        (
            Filler
        )
SELECT      TOP 1000000
            ''
FROM        sys.objects     a
CROSS JOIN  sys.objects     b
CROSS JOIN  sys.objects     c
CROSS JOIN  sys.objects     d
GO
/* Rebuild Indexes to set Fill Factor */
ALTER INDEX ALL ON dbo.CurrentTable REBUILD
GO
/* Create Partitioned View */
CREATE VIEW CurrentPlusHistory
AS
SELECT      ID,
            Filler
FROM        HistoryTable
UNION ALL
SELECT      ID,
            Filler
FROM        CurrentTable
GO
/* Move some data to History Table */
ALTER TABLE CurrentTable SWITCH PARTITION 1 TO HistoryTable PARTITION 1
ALTER TABLE CurrentTable SWITCH PARTITION 2 TO HistoryTable PARTITION 2
ALTER TABLE CurrentTable SWITCH PARTITION 3 TO HistoryTable PARTITION 3
ALTER TABLE CurrentTable SWITCH PARTITION 4 TO HistoryTable PARTITION 4
ALTER TABLE CurrentTable SWITCH PARTITION 5 TO HistoryTable PARTITION 5
ALTER TABLE CurrentTable SWITCH PARTITION 6 TO HistoryTable PARTITION 6
ALTER TABLE CurrentTable SWITCH PARTITION 7 TO HistoryTable PARTITION 7
ALTER TABLE CurrentTable SWITCH PARTITION 8 TO HistoryTable PARTITION 8
GO
/* For the Partitioed View to work we need a check constraint to define the key range */
/* as we've not created one this query will look at both tables */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
SELECT MAX(ID) FROM dbo.HistoryTable

/* Notice the range for the each table is define so SQL Server can use this value to select the target table */
ALTER TABLE dbo.HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<800000)
ALTER TABLE dbo.CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=800000)
GO
/* Notice in the output this time SQL Server doesn't look into the current table */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Now rebuild HistoryTable to see that its Partitions take the FillFactor */
ALTER INDEX ALL ON dbo.HistoryTable REBUILD
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Move some more data into the History Table, the CHECK CONSTRAINT range has to be managed as well so this is a coding overhead */
ALTER TABLE HistoryTable DROP CONSTRAINT HistoryTable_ID_Range
ALTER TABLE HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<900000)
ALTER TABLE CurrentTable SWITCH PARTITION 9 TO HistoryTable PARTITION 9
ALTER TABLE CurrentTable DROP CONSTRAINT CurrentTable_ID_Range
ALTER TABLE CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=900000)
GO


generate_series for multiple record types in postgresql

I usually solve such problems by setting up a table for all the possible data points (here the pests and dates).  This is easily achieved by a CROSS JOIN, see the WITH query below.

Then, as the finishing step, I just (outer) join the existing measurements, based on the pest ID and date - optionally giving a default for the missing values via COALESCE().

So, the whole query is:

WITH data_points AS (
    SELECT id, name, i::date
    FROM pests
    CROSS JOIN generate_series('2015-01-01'::date, '2015-01-05', '1 day') t(i)
)
SELECT d.name, d.i, COALESCE(p.cnt, 0)
FROM data_points AS d
LEFT JOIN pest_counts AS p
    ON d.id = p.pest_id
    AND d.i = p.count_date;

Check it at work on SQLFiddle.

Note: when either the table(s) or the generated series are big, doing the CROSS JOIN inside a CTE might be a bad idea. (It has to materialize all the rows, regardless of there is data for a given day or not).  In this case one should do the same in the FROM clause, as a parenthesized sub-join instead of the current reference to data_points.  This way the planner has a better understanding about the rows affected and the possibilities for using indexes.  I use the CTE in the example because it looks cleaner for the sake of the example.


You must specify the condition in a new column where you select the OrderID column.
So, in the 3rd column select Table tblOrderProduct and Field OrderID and place [Enter Order ID] on the Criteria line of this column.
Replace the * by -. This will subtract the Quantity from the ProductStock.
Well, and you don't need the Quantity column. Remove it.
In the SQL view you will see
UPDATE tblProduct
INNER JOIN tblOrderProduct ON tblProduct.ProductID = tblOrderProduct.ProductID
SET tblProduct.ProductStock = [ProductStock]-[Quantity]
WHERE (((tblOrderProduct.OrderID)=[Enter Order ID]));