MySQL Partitioning based on addition I cannot find a good keyword to search for this kind of question, so here you go:

Suppose i have this table:

CREATE TABLE test (
 tr_uuid BINARY(16) NOT NULL,
 Customer VARCHAR(10) NOT NULL,
 Data BLOB NO NULL,
 DataTime DATETIME NOT NULL,
 PRIMARY KEY (tr_uuid, DataTime)
);
CREATE INDEX idx_test ON test(Customer);

Are there any bad side if I have this way of partitioning (monthly):

ALTER TABLE test PARTITION BY RANGE ( ( MONTH(DataTime)*10000+YEAR(DataTime) ) ) (
 PARTITION p_422018 VALUES LESS THAN (422018),
 PARTITION p_432018 VALUES LESS THAN (432018),
 PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

or like this weekly:

ALTER TABLE test PARTITION BY RANGE ( YEARWEEK(DataTime) )
(
  PARTITION p_test_201834 VALUES LESS THAN (201834),
  PARTITION p_test_201835 VALUES LESS THAN (201835),
  PARTITION p_test_201836 VALUES LESS THAN (201836),
  PARTITION p_test_201837 VALUES LESS THAN (201837),
  PARTITION p_test_future VALUES LESS THAN (MAXVALUE)
);

The idea is to partition weekly/monthly every year but I dont want to use SUBPARTITION.

Another thing, if there is requirement to store the data for 2 years with data growth of ~6mil/week, and the chance to query the data is quite rare. Using above way will end up with 53x2 partitions after 2 years, according to this blog, 50 partitions will make it inefficient. So are there any better solution for partitioning?

The purpose of partition is not only for query performance, but also to delete the old data smoothly.

Note the MariaDB reference manual (not blog), says 'better fix coming in 5.7', the 5.7 manual includes a big note about native partitioning being deprecated in 5.7.17 and therefore native partitioning is available. So really this advice doesn't apply if you have MySQL-5.7.17+.


SQL Server: Join all tables from a table listing their names I have a table that contains a list of metrics and their IDs in the format:

L6Metrics
| ID   | Name            |
--------------------------
| 734  | L6A_Velocity    |
| 736  | L6B_Velocity    |
| 738  | L6A_Target_Rate |
...

Each of these metrics has its own corresponding table containing timestamped historical data. If the metric has ID X, then its historical data's table name is History_tX. These history tables have the format:

History_t734
| DateTime                       | Value |
-----------------------------------------
| 2018-05-04 19:17:41.972 -05:00 | 33.9  |
| 2018-05-04 19:17:51.972 -05:00 | 36.3  |
| 2018-05-04 19:18:01.972 -05:00 | 35.1  |
...

Assuming that the timestamps across all of the history tables align, how can I use the L6Metrics table to join all of the history tables on DateTime to get the following:

| DateTime                       | History_t734 | History_t736 | ...
----------------------------------------------------------------
| 2018-05-04 19:17:41.972 -05:00 | 33.9         | 34.9         |
| 2018-05-04 19:17:51.972 -05:00 | 36.3         | 37.4         |
| 2018-05-04 19:18:01.972 -05:00 | 35.1         | 36.5         |
...

There are 75 rows in L6Metrics (representing 75 history tables) so there would be 76 columns in my result. I have the ability to modify L6Metrics if needed.

Assuming the list of Metrics.ID is static:

WITH cte AS ( SELECT DateTime FROM History_t1
              UNION
              SELECT DateTime FROM History_t2
              UNION
              ...
              UNION
              SELECT DateTime FROM History_t75
            )
SELECT cte.Datetime,
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM cte
LEFT JOIN History_t1 ON cte.Datetime = History_t1.datetime
LEFT JOIN History_t2 ON cte.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON cte.Datetime = History_t75.datetime

If, as you claim, "Assuming that the timestamps across all of the history tables align" you can avoid of CTE:

SELECT History_t1.Datetime,
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM History_t1
LEFT JOIN History_t2 ON History_t1.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON History_t1.Datetime = History_t75.datetime

The Metrics table not needed at all.

If the list of Metrics.ID is dynamic, use dynamic SQL to build the query text similar to above.


How can I count the students on mongodb I would like to display the list of students who have passed at least 3 courses at ENSA with more than 60.

db.etudiants.insert({
    nom: 'emile',
    prenom: 'josé',
    cours_reussis: [{
        nom_cours: 'SO2',
        periodes: NumberInt(60),
        note: 70,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: 'ENSA'
    }, {
        nom_cours: 'IBD',
        periodes: NumberInt(60),
        note: 80,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: 'ENSA'
    }, {
        nom_cours: 'GEB',
        periodes: NumberInt(60),
        note: 65,
        date_reussite: NumberInt(2016),
        dispense: true,
        nom_ecole: 'ENSA'
    }]
})

db.etudiants.insert({
    nom: "larhemouchi",
    prenom: "zouhair",
    cours_reussis: [
      {
        nom_cours: "PAI",
        periodes: NumberInt(60),
        note: 68,
        date_reussite: NumberInt(2015),
        dispense: true,
        nom_ecole: "ENSA"
      },
      {
        nom_cours: "GEB",
        periodes: NumberInt(60),
        note: 78,
        date_reussite: NumberInt(2014),
        dispense: true,
        nom_ecole: "ENSA"
      }
    ]
})

In truth I tried and I am a bit ready to answer but I do not know how to have the students who have successfully 3 subjects.

here is my answer

  db.etudiants.aggregate([
  {
    $project: {
        _id: 1,
        'nom': 1,
        'prenom': 1,
        'cours_reussis': 1
    }
  },
  {
    $unwind: '$cours_reussis'
  },
  {
    $match: {
        'cours_reussis.note': {
            $gt: 60
        },
        'cours_reussis.nom_ecole': "ENSA"
    }
  },
  {
    $group: {
        '_id': {
            'nom': '$nom',
            'prenom': '$prenom',
            'cours_reussis.note': '$cours_reussis.note',
            'cours_reussis.nom_ecole': '$cours_reussis.nom_ecole'
        },
    }
  }
])

I would just like the students who have successfully taken 3 courses. In our database, the student who has successfully completed 3 courses is "emile josé".

Try the following:

db.etudiants.aggregate([
 {
   $unwind: '$cours_reussis'
 },
 {
   $match: {
       'cours_reussis.note': {
           $gt: 60
       },
       'cours_reussis.nom_ecole': "ENSA"
   }
 },
 {
   $group: {
       '_id': {
           'nom': '$nom',
           'prenom': '$prenom',
       },
       'num_courses' : { "$sum" : 1 }
   }
 },
 { $match : {
      'num_courses' : { '$gte' : 3 }
  }
 }
])

Does this meet your criteria?

Note that the only changes I made to your aggregation were the following:

I limited the '_id' to just the 'nom' and 'prenom' fields
I used $sum to count the number of completed courses
The initial $projection stage was removed, as the aggregation can perform this optimization automatically.


Run stored procedures from specific package/with specific tag at lower priority I have this requirement to run "Maintainance Jobs" for a web application. These jobs will just call a stored procedure from a set of specific packages.

Running these jobs can consume quite a bit of CPU time due to the sheer amount of work they need to do (most of it is read, to determine work to do). Due to that, they compete with actual consumer traffic.

Now, can I somehow tag or otherwise indicate that these jobs have a lower priority than queries executed by the customer facing frontend application?

I've seen Oracle Resource Manager, which would allow me to specify a "Resource Plan" based on a module/action name. But it's not quite clear from the documentation if I have to set this explicitly in the SP (which I'm trying to avoid) or if Oracle would do this for me. I'm also open for suggestions not using resource plans.

I can't personally think of anything better for on-the-fly resource throttling based on to-the-second stats than Resource Manager. It can be a bit of a hassle to set up, but I don't imagine how writing custom code wouldn't just be reinventing the wheel that is Resource Manager.

I'm not aware of any reliable 3rd party vendors at this time.

I do know that you don't have to do anything to your stored procedures. IMO the best way to use Resource Manager and stored procedures is to use DBMS_SCHEDULER to tie the resource plan in with a scheduled job.

Documentation on dbms_scheduler is here: http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sched.htm#ARPLS72235

Also, IIRC, remember when implementing that Resource Manager handles CPU and I/O, but not Memory utilization.


Can i use a custom function with UpdateOne or UpdateMany in MongoDB Shell? I wish to perform update on my database using Update command.
the data structure is as following:

{
    "_id" : ObjectId("5d06285570de7d11606275f8"),
    ...
    ...
    ...
    "ProfilePictures" : [
        {
            "ImageId" : ObjectId("5d0a65d05e82a23764150978"),
            "CloudinaryUrl" : "<fileAddress>",
            ...
        }
    ],
    ...
}

i wish to update my URL, for this first i wrote this function:

var updateHttpToHttps=function(url){ return url.replace(/^http:\/\//i, 'https://'); }

and then i tried to update a new field named CloudinarySecureUrl using data from CloudinaryUrl field, and then i need to fix mismatch in the CloudinaryUrl changing all url starting with https to http:

db.humanResource.user.updateMany({ProfilePictures:{$exists:true}, ProfilePictures:{$ne:null}, ProfilePictures.CloudinaryUrl:{$exists:true}}, {$set:{ProfilePictures.$.CloudinarySecureUrl:updateHttpToHttps(ProfilePictures.$.CloudinaryUrl)}})

formatted version:

db.humanResource.user.updateMany({
    ProfilePictures: {
        $exists: true
    },
    ProfilePictures: {
        $ne: null
    },
    ProfilePictures.CloudinaryUrl: { // I did this because it said for using $ i have to brign a field from that array to my query
        $exists: true
    }
}, {
    $set: {
        ProfilePictures.$.CloudinarySecureUrl: updateHttpToHttps(ProfilePictures.$.CloudinaryUrl)
    }
})

So after writing this update query, i notice, maybe i know that i want to pass the value of ProfilePictures.$.CloudinaryUrl to the javascript function used in this update, but it doesn't necesserity mean that it will be processed, since all i write is a query language, not javascript code, so the system cannot pass the value to the function...
Is there any way to do this? How?

The only way i could find was this, and i ask it in many places, no one answered...:

db.humanResource.user.find({
    ProfilePictures: {
        $exists: true
    },
    ProfilePictures: {
        $ne: null
    }
}).forEach(function (e, i) {
    for(var i=0; i< e.ProfilePictures.length; i++){
         e.ProfilePictures[i].CloudinarySecureUrl = updateHttpToHttps(e.ProfilePictures[i].CloudinaryUrl);
         e.ProfilePictures[i].CloudinaryUrl = updateHttpsToHttp(e.ProfilePictures[i].CloudinaryUrl);
    }
    db.humanResource.user.save(e);
})

Which is based on the answer of this thread:
We can use forEach to decrease load on database and Ram Usage, while updating each record, without loading them all, and then we save that document of collection.

And i could use Print ans JSON stringy as a helping reference before destroying my database:

JSON.stringify is usable in MongoShell with BSON

Print Can be used to print into Mongo Shell

also there was a printjson in the second link, which i didn't notice, at first, i just noticed now.


SQL Server NTFS allocation unit size On Windows 2008 R2 running SQL Server 2008 R2 how imporatant is the NTFS allocation unit size on DISK IO performance. It appears to me that server admin who built the few servers for a mission critical app left NTFS allocation unit size (cluster size) to default 4 KB instead of 64 KB. SQL server is already installed.

Does it worth to take pain -- to uninstall SQL -- format the drive with 64 KB cluster size and reinstall SQL server?

You shouldn't need to uninstall/install: your data and log files should be on separate disk arrays/SAN Luns from the binaries.

Saying that, 64k NFTS cluster is highly recommended all over the place.
SQL Server does IO in extents which is 8x8k pages = 64k, basically.

For actual numbers on performance differences, I can only find this http://tk.azurewebsites.net/2012/08/ (Azure, but still SQL Server)


Creating a summary table for an averaging query In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages.

Firstly, I have a table of activity_scores and members. Below is an example of the activity_scores table:

 id | member_id | score |         created_at         |         updated_at
----+-----------+-------+----------------------------+----------------------------
  1 |         8 |    73 | 2016-11-04 05:32:55.564235 | 2016-11-04 05:32:55.564235
  2 |        10 |    20 | 2016-11-04 05:22:55.564235 | 2016-11-04 05:22:55.564235

The scores are inserted every 10 minutes over time. In response to ypercube, I created a summary table with the constraint unique(member_id, date) for quicker access during a graphing process.

Then, I wrote the following query to store the average of scores for 'yesterday' per member into a summary table.

INSERT INTO activity_score_daily_averages (member_id, date, average_score)
  SELECT
     activity_scores.member_id, TO_CHAR((activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')), 'YYYY/MM/DD') AS date,
     AVG(activity_scores.score) AS average_score FROM activity_scores INNER JOIN members ON members.id = activity_scores.member_id
     WHERE
       NOT EXISTS(
         SELECT 1 FROM activity_score_daily_averages
         WHERE activity_score_daily_averages.member_id = activity_scores.member_id
         AND activity_score_daily_averages.date = date
       )
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') > (current_date-1) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') < (current_date) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
     GROUP BY member_id, date

Yet, running the above query took ~5 minutes. I feel as though there is a lot of room for improvement considering that the table I am working with isn't even considered that large for some of the DBMS masters out there...

Some things I was considering for optimizing this query were:

Somehow using a CTE or variable(?) for COALESCE(members.zone, 'Etc/UTC')
Find a different way to handle DUPLICATE KEY exceptions for entries that have the same member_id and date (i.e., replace WHERE NOT EXISTS(...) with something else)

I would appreciate any constructive criticisms and/or pointers.

Here is a possible scheme:

Add a column to the activity_scores table is_processed which flags whether the row has been summarized into the summary table
Add a partial index to the activity_scores table for only the unprocessed rows, this could include all columns
Now when you go to load yesterday's data you are only looking through rows which you know have not been processed
Identify yesterday's rows as part of the load process and insert them into a temp table, then insert the summary of the rows into the summary table
Join the temp table to the source table on id and mark the rows as processed (should be quick as you have the ids and aren't doing a scan)


SQLServer 2016 continually increasing stolen memory We are experiencing a slow growth of Stolen Memory on our database servers over the space of several days. It appears to plateau around 130-140GB, at which point we start having larger problems such as out-of-memory errors, multi-second freezes & AG failovers. Problems start to manifest about a week after a reboot. I have started recording history of stolen memory, which is shown below:

Looking at sys.dm_os_memory_clerks, it appears as if the bulk of this is coming from non-page memory recorded against the buffer pool on NUMA node 0:

Tracking the total pages_kb for the buffer pool over time shows the decline in number of pages as virtual_memory_committed_kb grows. (On April 13th, the server was rebooted for windows updates. The buffer pool fills to 400GB in around an hour)

Has anyone seen this behaviour before?

We are running SQLServer 2016 CU12 13.0.5698.0 The server is a 64-core AWS EC2 i3.16xlarge instance. We have a number of other clusters of the same size that are all showing this issue. We also have a few clusters on 32-core i3.8xlarge instances that also show the growth in stolen memory, but they don't end up stalling / throwing out-of-memory errors. The only difference (other than scale) is that the 64-core servers have 2 NUMA nodes.

Update:
MS indicated that the bugfix in KB4536005 is not being back-ported to SQL2016.

i have a suspicion.  First - are you able to open support tickets with Microsoft?

Easiest way to check my suspicion is capture [\SQLServer:Memory Node(*)\Stolen Node Memory (KB)] for both NUMA nodes, and compare the sum to [\SQLServer:Memory Manager\Stolen Server Memory (KB)].  If my suspicion is correct, when trouble is brewing the discrepancy between the two - which seem like they should always agree - will be quite high.  The other tell-tale characteristic: up to N-1 SQLOS NUMA nodes may have show this relationship (where N is the count of NUMA nodes)
[database node memory] + [stolen node memory] + [free node memory] > [total node memory]

i describe the problem somewhat in these blog posts.

https://sql-sasquatch.blogspot.com/2018/07/sql-server-2016-memory-accounting.html
https://sql-sasquatch.blogspot.com/2018/10/sql-server-2016-memory-accounting-part.html

The basic accounting problem is that sometimes buffer pool growth occurs in a way that buffer descriptor blocks get allocated from SQLOS node A but the pages referenced in the bdbs actually come from SQLOS node B.  The result of this condition is that a portion of physical memory controlled by SQLOS gets double-counted: the same memory is accounted on node A (where the bdbs live) as [Database Node Memory] AND accounted on SQLOS node B as [Stolen Node Memory].  That situation is confusing and inefficient... but its not the full bloom of the problem yet.

The problem fully blooms when so much Node B [stolen node memory] is also Node A [database node memory] that Node B [database node memory] drops to ~2% of Node B [target node memory].  When that happens, the rate of [\SQLServer:Buffer Manager\Free list stalls/sec] skyrockets - we saw 2000/sec when this happened to us.  SQL Server is trying to correct the issue (too little [database node memory]) on Node B by trimming various types of cache on Node B.  But it can't!! Because the [stolen node memory] isn't in any of the various expected cache types.

Temporary resolution: when [total node memory] approaches [target node memory] but [database node memory] approaches 2% of [target node memory], execute DBCC DROPCLEANBUFFERS.

kb4536005 resolves this issue in SQL Server 2017 CU20 and SQL Server 2019 CU2.
https://support.microsoft.com/en-us/help/4536005/improvement-fix-incorrect-memory-page-accounting-that-causes-out-of-me

There is a similar sounding fix in SQL Server 2016 SP2 CU5, kb4470916.
https://support.microsoft.com/en-ca/help/4470916/fix-out-of-memory-error-occurs-when-database-node-memory-kb-drops-belo

However, I don't believe kb4470916 resolves the issue with double-accounting.  So while it may improve SQL Server response to a single SQLOS node having [database node memory] at the ~2% threshold, i think it leaves open the possibility of poking the bear due to this double-counting.  And that may be the situation you are in.

However, if the sum of [stolen node memory] across the two nodes always aligns with [stolen server memory] in the instance, you can forget all about this as if it were a bad dream. :-)


Optimal Number of MySQL InnoDB Buffer Pool Instances Server Characteristics

Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL)
Number of CPU Cores: 6
I have data in the db amounting to about 2GB
I have InnoDB Buffer Pool Size set to 4GB

Which is better:

Innodb Buffer Pool Instances set to 1?
Innodb Buffer Pool Instances set to 2 (2GB in each)?
Innodb Buffer Pool Instances set to 4 (1GB in each)?
Innodb Buffer Pool Instances set to 8 (the default setting)

I'm thus not sure as to how to reason when it comes to Buffer Pool Instances and also the whole "use instances or suffer OS Swap when having such large InnoDB Buffer Pool Size".

When I go back to MySQL 5.5, I would think about this same thing.

What I learned over those years was the following: If the Buffer Pool was bigger than half the installed RAM and innodb_buffer_pool_instances was 1 (default for 5.5), the threat of swapping was always imminent.

I discussed this before : Is there a rule of thumb regarding the size and number of a buffer pool instances?. In that post, I mentioned an example of a client who had 192GB RAM on the server with 162GB Buffer Pool. When innodb_buffer_pool_instances was 1, swapping happened. When I set innodb_buffer_pool_instances to 2, things got way better.

In your case, since the Buffer Pool is exactly half, a value of 1 may be OK. I would not chance it. I would set it to 2.

Since MySQL 5.6 has a default of 8, you shouldn't have to think about it anymore.

I will say this: akuzminsky's answer has the highest principle. My answer is just shooting from hip based on past experiences (good and bad).


Sql Server Maintenance Plan - Simple Recovery Mode - Adding Shrink Log I inherited lot of old servers and Databases from other department and now I will be the one who needs to take back ups / do Maintenance stuff with it.

I see a lot of databases in simple mode (I checked with the Owners and they say they are fine with loosing one day data in case of disasters)

I see some log files bigger than the databases. I am thinking of Including shrink Log (add a Sql Task ) in my maintenance plan after the back up.

Is it a Good Practice?

The first thing you have to remember is that all transactions are logged to some degree.  There are cases where an action can be minimally logged but there is still some entry in the log.

The recovery modes just change how often the space in the log is freed for re-use.  In all cases the log space is freed up once it is no longer needed.  In the case of SIMPLE recovery there is no backup required for the space to be freed.  However as long as a transaction is active that space won't be freed.  In your particular case you probably have some long running transactions (probably batch jobs) that are holding the space open and causing additional data to be added to the end of the log forcing it to grow.

If the logs are taking more space than you are comfortable with your best bet is going to be to examine any batch processes and try to shorten any transactions they are using.


foreign key on multiple tables based on multiple columns I have a question about design part of my db I have 3 tables like these:
CREATE TABLTE projects (
    uuid uuid
    name text
)
CREATE TABLTE invoices (
    uuid uuid
    cost text
)
CREATE TABLTE fields (
    uuid uuid
    parent uuid REFERENCES (???) ON DELETE CASCADE ON UPDATE CASCADE,
    type varchar(255) (project or invoice)
)

is possible somehow to create one foreign key that look on more then one table and based on 2 columns (parent, type)? or do I need to do something like this?
CREATE TABLTE filds (
    uuid uuid
    project_uuid uuid REFERENCES projects(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
    invoice_uuid uuid REFERENCES invoices(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
)

There are three ways:

If projects and invoices have almost the same columns and are ofhen used together like “get all projects or invoices that fulfill a certain condition” or “there must be a project or invoice that...”, then it is natural to model these two objects with a single table, introducing a column type to tell them apart.
Then you can have a regular foreign key.

If they should be modeled as different tables and have different columns, introduce two foreign key columns in fields, ideally with a check constraint:
 CHECK (parent_invoice IS     NULL AND parent_project IS NOT NULL OR
        parent_invoice IS NOT NULL AND parent_project IS     NULL)

A hybrid solution would be
 CREATE TYPE p_or_i AS ENUM (
    'project',
    'invoice'
 );

 CREATE TABLE project_or_invoice (
    id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
    type p_or_i NOT NULL,
    /* other common columns */,
    PRIMARY KEY (id, type)
 );

 CREATE TABLE project (
    id bigint NOT NULL,
    type p_or_i GENERATED ALWAYS AS ('project') STORED NOT NULL,
    /* specific columns */,
    PRIMARY KEY (id, type),
    FOREIGN KEY (id, type) REFERENCES project_or_invoice
 );

 CREATE TABLE invoice (
    id bigint NOT NULL,
    type p_or_i GENERATED ALWAYS AS ('invoice') STORED NOT NULL,
    /* specific columns */,
    PRIMARY KEY (id, type),
    FOREIGN KEY (id, type) REFERENCES project_or_invoice
 );

Then you would reference project_or_invoice in your foreign key.


How can I alter the type of a money-column to decimal in PostgreSQL? I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead.

How can I do it?

I tried with:

alter table my_table alter column vat type decimal;

But I get this error:

ERROR:  column "vat" cannot be cast to type numeric

Any suggestions?

Try providing USING clause as described in docs:

  A USING clause must be provided if there is no implicit or assignment cast from old to new type.


Join 1 column to multiple columns in another table I have 2 tables portmap and devices

Portmap has the following columns.
ID (which is the PK),
networkDeviceID (FK to device table),
port,
timestamp,
description,
connectedDeviceID (FK to device table)

Devices has the following columns.
ID (which is the PK),
modelID (FK to the model table),
hostname,
serialnumber,
lanmac,
wmac

I'm having a mental block on how to get the correct corresponding hostname to display when doing a join. for example:

if row 1 in portmap table cotains the following
networkdeviceID = 2 and ConnecteddeviceID = 1

how do I get it to display the hostname for each IDvalue instead of just giving me the INT vaules

edit

how do I get the hostname from deviceID to show in the networkdeviceID column and in the conntecteddeviceID column?

edit2

maybe this might help you understand what I need.

I would like have the hostname mvl displayed in the networkdeviceID field and mvd displayed in the connectedDeviceID field on row 1. row 2 when then display mvd for the networkdeviceID and mvl for the connectedDeviceID field.

I need it that way so if say a end user decided to change the hostname mvl to say xyz1234 every record throughout the DB wouldn't automatically update to xyz123 instead of having to changing it by hand. or if in the portmap table they changed row2 networkdebiceID to 7 it would automaticlly display the device table hostname of mvdxxxxx

Do you get what I'm trying to do now?

Try something like this:

SELECT p.ID, d1.hostname, p.port, p.timestamp, p.description, d2.hostname
FROM Devices d1, Portmap p, Devices d2
WHERE d1.id = p.networkdeviceID and d2.id = p.connecteddeviceID;


How to copy tablespace from one database to another database in Oracle 11g How to copy tablespace and datafiles from one database to another database in Oracle 11g in same server ?

While copying a tablespace, it automatically includes data files and indexes associated with it. You need to make sure that tablespace is self contained.

Confirm that tablespace is self contained
execute DBMS_TTS.TRANSPORT_SET_CHECK('<TABLESPACENAME>', true);
Use the TRANSPORT_SET_VIOLATIONS view to check if any violations condition  exists
select * from TRANSPORT_SET_VIOLATIONS;
if any exist then fix them.
Make the tablespace read only
alter tablespace <TABLESPACENAME> read only;
Use EXPDP COMMAND to export the tablespace:

EXPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILE.DMP
LOGFILE=ERROR.LOG TRANSPORT_TABLESPACE=TABLESPACENAME

(You should have the rights necessary to execute IMPDP/EXPDP command and access rights to directory objects which map to the OS folder.)
Copy the datafiles to the target database using ftp or scp or somt other filesystem utility.
Make the tablespace READ WRITE again in the source database
Use IMPDP command to import it into target database:

IMPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILENAME.DMP
LOGFILE=ERROR.LOG

Check logfile for errors, if any
Make the tablespace read write in the target database:

ALTER TABLESPACE <TABLESPACENAME> READ WRITE


Change SQL backup location We need to change the SQL backup location from one network share to another network share. The new share name is different from the old share name. I am thinking to update the "command" column and "output_file_name" column in "msdb..sysjobsteps" table. Is it safe?

Editing the data in that table is not the correct way to make this change, as it is a system table. In fact, I don't think it is even possible, although some system tables outside of master can be updated.

If you want to do this via script, instead of manually editing your backup jobs, try this:

SELECT 'EXEC msdb..sp_update_jobstep @job_name = ''' + j.[name] + ''', @command = ''' + REPLACE(js.[command], '\\OldBackup\Share', '\\NewBackup\Share') + ''';'
FROM msdb..sysjobsteps js
INNER JOIN msdb..sysjobs j ON j.job_id = js.job_id
WHERE j.[name] = 'Backup Job Name'

This will dynamically generate a sp_update_jobstep command and replace the old backup share info with the new backup share info. Copy the generated results into a new query window, validate the job that's being updated is correct and the command looks correct, then you can execute.

Obviously, you should test this first and don't just execute it a production system.

More info:
sp_update_jobstep


MYSQL inner join , if not match try another table I've got a very quick issue here, how can i join a table with two other tables in a way if one's info doesnt match, it goes try another one. something like:

  $properties = $mysqli->query("SELECT *
                                FROM invoices i
                                inner join properties p on i.ref_id = p.property_id
                                if_null_then (
                                  inner join properties2 p2 on i.ref_id = p2.property_id)
                                ") or die (mysqli_error());

Much appreciate any response guys, i'm kinda stuck now :/

Always outer join them, then just use a CASE statement to pick out the info from the relevant table:

SELECT ref_id, case when p.property_id is null then p2.property_id else p.property_id end
FROM invoices i
left outer join properties p on i.ref_id = p.property_id
left outer join properties2 p2 on i.ref_id = p2.property_id;


Does running a COUNT() statement using NOCOUNT ON; consume much On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data?

Sql Server 2008 R2

Also, is running a COUNT() statement on the Primary_Key only, increase performance?

NOCOUNT does not mean "don't count" - it just means "don't report the count." Reporting can result in a lot of unnecessary chatter between server and client (which is usually ignored), and can incorrectly be interpreted as a result set by some APIs. At my previous jobs one of our coding policies was that SET NOCOUNT ON was required in every procedure, and I still promote it as a best practice. There are some APIs that rely on this message, however, and in fact your own .NET code may be relying on .RecordsAffected today.

As for the count, you can use sys.dm_db_partition_stats but be aware that this does not guarantee 100% accuracy - like taking a direct count with NOLOCK, it will not account for transactions in progress.

I also suggest a couple of improvements on @garik's answer:

SELECT
    sch = OBJECT_SCHEMA_NAME(i),
    obj = OBJECT_NAME(i),
    [RowCount] = rc
FROM
(
    SELECT
        i = [object_id],
        rc = SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE index_id IN (0,1)
    GROUP BY [object_id]
) AS x
ORDER BY [RowCount] DESC;

This will take partitioning into account, includes the schema as well as object name, and identifies heaps and clustered indexes explicitly (who knows when Microsoft will start giving hypothetical indexes or other system-defined indexes negative index_id values).

EDIT:

As for the question about counting on a primary key, you will see no difference in performance between COUNT(*) and COUNT(key_column) - if you inspect the plans, they should be identical. Be careful, though, about using COUNT(nullable_column) - personally I think COUNT(*) is safer. That said, for millions of rows, it is quite rarely a realistic requirement to get a 100% accurate count. Retrieving the row counts from the DMV above should be more than adequate and doesn't have a chance of impacting the table itself (or being slowed down by operations on the table).


Is there a function which the opposite of NULLIF? I am aware of the NULLIF function which compares two values and returns NULL if they match.

Is there a function which will compare two values and return NULL if they don’t match? That is filter out the other values.

I know I can do something like that using CASE:

CASE column WHEN value THEN 1 END

I also know that I can write a function.

Perhaps there is a subtle trick to using NULLIF that I am unaware of.

I have listed a few versions of SQL in the tags, but I think NULLIF is generic. I am looking for a solution which is also generic, so that it can be applied to any standard database.

NULLIF(expr1, NULLIF(expr1, expr2)) does what you want.


History log on Ticket system database I'm creating a Support Ticket system and it need to have a history of all changes made on that ticket. For example:

Status change;
Changed the responsible assistant;
Sector/Department change;
Etc..

This is the structure I have at the momment:

I was thinking about it and tought it may be very confusing, or at least not the best option, to create a table structure to store the respective type of change and the id from the correspondent table. For example:

Type of changes:
1-> status change;
2-> Department change;
3-> Assistant change;
[etc]

id  |  ticket_id  |  history_type  |  old_value  |  new_value  |  create_date
1   |      2      |       1        |      3      |      6      |  ....
2   |      7      |       3        |      5      |      2      |  ....

The other option I have, is to write plain text, instead of saving the Id's, for example:

id  |  ticket_id  |  message  |  create_date
1   |     1       |  Status change from 'Open' to 'Closed'  |  ....
2   |     2       |  Assistant change from 'John' to 'Kyle'  |  ....

Is there anything wrong with one or another? Or am I missing a important consideration on doing this type of history structure? Or what would be the best option?

I'd say that as long as you can guarantee that you (or anyone else) won't change the definitions of the '3','5','6','2' values somewhere down the road, it's not a bad idea.

You'll end up having to do a little more work with the first option to get it into a "User-friendly" view (if that's your end goal), but it seems like INT values (old_value, new_value) would be easier to store than full text values (message).  The main advantage to your second option is that it's easier to read and you don't have to link a few tables together to get the user-friendly version.

Another way I've seen it done (Particularly by Salesforce) is to select which fields you'd like tracked in an object.  Say "status" and "assistant" are your choices.  Salesforce will have a structure as follows:

id | ModifiedDate | OldValues                     | New Values
1  | 08/26/2016   | Status: Open, Assistant: John | Status: Closed, Assistant: Kyle

This is if at least one value changed.  So even if the assistant hadn't changed, it still displays all field values.

  id | ModifiedDate | OldValues                     | New Values
  1  | 08/26/2016   | Status: Open, Assistant: John | Status: Open, Assistant: Kyle

It also tracks initial "inserts" as such:

id | ModifiedDate | OldValues             | New Values
1  | 08/26/2016   | Status: , Assistant:  | Status: Open, Assistant: John

Hope that helps a bit.


How is it possible that a key of char+bigint has better performance than key of bigint+bigint? I would like to store some user related data into a table. The data is identified by MD5 hash.

I have 2 options:

option 1 -
have a table with 2 columns: hash_id (unsigned bigint) and md5 (char(32)). Store the actual data in another table where the key is hash_id (unsigned bigint) + user_id (bigint).

option 2 -
Store the data in a table where the key is md5 (char(32)) + user_id (bigint).

Option 1 looks more "normalized", but I prefer option 2 so I wanted to know if using it will cause a major performance penalty.

In order to check, I defined both tables and tried the following operations:

Insert 100K entries to an empty table
Use 10K ids/MD5s that already exist in the table and for each of them run a select query and an update query (to imitate scenario in which I want to set data in an entry that already exist so I need to update it).
Use 10K ids/MD5s that don't exist in the table and for each of them run a select query and an insert query (to imitate scenario in which I want to set data in an entry that doesn't exist so I need to create it).
Delete 10K existing entries

In all tests besides the last one, the results for the second table (the one with the actual MD5) were much better than results for the first (the one with the hash_id).

How is it possible? I assumed that using a char key, which is longer - 32 bytes, would be worse than using a bigint - 8 bytes. What am I missing here?

I use MySQL + InnoDB.

Thanks.

The size and makeup of an index, composite or not, does not make a lot of difference to performance.

However, the randomness of the accesses does.  This combination is a performance killer:

"Random" index (or part of index):  UUID / MD5 / SHA256 / etc.
Index (or table) is bigger than can be cached in RAM (in the buffer_pool).

Let's say the index (or table) is 4 times as big as the buffer pool.  Then the 'next' lookup (either for SELECT or for adding a row, hence updating the index), has a 3/4th chance of being a disk miss.  That is 75% of such operations will incur a disk hit.  If you are using an HDD, that is (a Rule of Thumb) 10ms.  (SSDs are faster, but still not free.

You can mitigate the problem by shrinking the table size.  This can be done by shrinking datatypes.  Don't use INT (4 bytes) for a Yes/No flag, use TINYINT (1-byte).  Don't store the MD5 as CHAR(32) (32 bytes -- or 96 if utf8); use HEX() and UNHEX() when fetching and storing into a BINARY(16) column.

Most other types of indexes have some degree of "locality of reference".  That is, two (or a thousand) consecutive references will be "near" each other, hence the caching of the buffer_pool can help avoid I/O, even if the table/index is much bigger than the buffer_pool.

More discussion on why UUIDs suck, plus a partial remedy for Type-1 UUIDs, which are time-based and it is the type that MySQL uses.

As for your benchmark...

In a 'real' table, there would be several other columns.
You did not increase the number of rows until it started getting a significant number of disk misses.
At that point, the 2-column lookup table could stay cached, but the bigger 'real' table could not.
So, if there was "locality of reference" (such as looking only at 'recent' rows), the MD5 would be jumping all around, but the BIGINT would be hitting more cacheable stuff.


SQL SERVER - GROUPING and Convert Unit of measurement I have given a task, but having a hard time on solving it. Hope you can help me with this.

Here is my scenario.

I have a sales table. the products are breads, cupcakes and cookies.

Here is the condition:

If I found 12 packs of cheesecake or certain product, I will convert it to 1 box.

So If I have

row1 product: cheesecake qty: 5 packs
row2 product: cheesecake qty: 5 packs
row3 product: cheesecake qty: 2 packs

I would get:

row 1 product: cheesecake qty: 1 box

As you can see form 3 rows, it becomes one row.

Would you help me how to do this?

Here is my code:

select
        SoldtoPtNm,
        MatCode,
        MatDesc,
        SU,
        SUM(GrossQty) as total,
        datepart(wk, BillDt) as week,
        BillDt
from tbl_Sales_test

group by
        SoldtoPtNm,
        MatCode,
        MatDesc,
        SU,
        GrossQty,
        BillDt

Right now what I get, is the quantity of the same uom. Please see screenshot for the result set.

Any hint and suggestions would be appreciated. Thanks!

Lets say you have table:

ProductCode ProductDescription  ProductQty
1           Apple               19
2           Banana              1
1           Apple               2
1           Apple               4
2           Banana              10
3           Clementine          8
3           Clementine          1
4           Lemon               12

Query:

Select * into #temp from (
select ProductCode,
       ProductDescription,
       sum(ProductQty) / 12 as Qty,
       case when (sum(ProductQty) / 12) > 0 then 'BOX' else 'PACK' end Package
from Products
group by ProductCode, ProductDescription
union all
select ProductCode,
       ProductDescription,
       sum(ProductQty) % 12 as Qty,
       case when (sum(ProductQty) % 12) = 0 then 'No' else 'PACK' end Package
from Products
group by ProductCode, ProductDescription) as tmp

select * from #temp
where Package<>'No'
and Qty<>0
order by ProductCode

As a result I get this:

ProductCode ProductDescription  Qty Package
1           Apple               2   BOX
1           Apple               1   PACK
2           Banana              11  PACK
3           Clementine          9   PACK
4           Lemon               1   BOX

I hope this is what you are looking.


Memory threshold is attempted on SQL Server Currently we have a monitoring for all the infrastructure (server, DB, network components, etc ...). The alert thresholds, usually saturation, are configured as follows:

90% : Warning
95% : Critical

Once the threshold is reached, an alert is generated and sent as an email or via a ticketing solution.
To be more concrete for our case, we have a server with the following config:

Server memory : 8 Gb
SQL max memory : 7 Gb

Once SQL reaches the max memory, if we also add OS processes, the threshold of 90% is reached, then an alert is generated.
Among the following solutions to avoid the alert, which is the most optimal:

Physical memory extension
Modification of the alert threshold from 90% to 95%
Empty the unused SQL chase (if possible)
Decreasing SQL max memory

Otherwise if you have other solutions do not hesitate to share it.

Sadly, sometimes we get less than helpful information to deal with throughout our busy days.

Q. What do Quantified Alerts tell us?

That service A is doing or enduring something.
That information is happening on a system.

Q. What will it take to Qualify my alerts?

Understand the difference between causation , correlation , unrelated events that a Quantified events have with a Qualified event. Merely having an alert about memory is the same as telling a Sports Athlete that he is using all his muscles during a sprint. It means nothing by itself.
Causation are events that directly contribute to impact of measurable scope. An example would be shutting down a service has direct effects on the users.
Correlation are events that relate to the impact as passive, precursors, or active consequences from the event. An example could be delays on the app server from consistently high Memory and CPU usage combined with Long IOs recorded on the Error logs, which one is the actual issue? The High usage may prevent the new connection, but the limits for current service may be related to the IOS. The passive result may be that some users experience downtime while others still have access (though slow).
Unrelated Events are issues that may or may seem like correlation or causation, but in reality are outliers that have no relevance. An example might be a user that has forgot his email but reports the service is down at the same time other users actually have impact to their services.

Conclusion

Think about the trends of events. Does High Memory usage occur during high load or when off hours occur? Are there bad queries being run at the same time or are many, or large queries happening? The first is an issue and the second and third are expected events on a large server.

At the end of the day, having high Memory usage itself is not necessarily a bad thing.  Your server is being used. You need to determine measurable negative events in order to decide if the alert is good or bad.


Importing Data in Parallel in SQL Server I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the following ones:

How to load 1 TB data in 30 minutes

https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx

Importing Data in Parallel with Table Level Locking

https://technet.microsoft.com/en-us/library/ms186341(v=sql.105).aspx

and the answers in stackoverflow

Fastest way to insert in parallel to a single table

However, none of them have given a simple example with code. I know how to use bulk insert/bcp, but I don't know where to start with parallel import? Can anyone help me with it?

My system is Windows and I'm using SQL server 2016. The source data file is in txt format.

Thanks in advance for your help!

Jason

Borrowing from my old answer

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 and up then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
Real World Parallel INSERT…SELECT: What else you need to know!


MongoDB Connection Issues We have a MEAN application with a 3-member MongoDB replica set. Somehow the total connection count keeps increasing until the primary is not able to take any new connections and goes down, making the application painfully slow. I have set the ulimit to 30000 and restarted the MongoDB and in no time it got 28000+ connection opened and primary went down.

Any idea why this may be happening? Most of the connections are between the different ports on primary with port 27017 on primary (for instance,  primary_server:45002 to primary_server:27017).

Make sure you have right UNIX ulimit Settings.You can see also: https://docs.mongodb.org/manual/reference/ulimit/
Use top command to check if the performance bottleneck is CPU overload.Check the mongodb log file if there is any slow query,and you can use MTools to analyse it.Then you need optimize the slow query.
Use free command to check if not enough physical memory is available.If MongoDB has too many connections,you need to check Stack settings.every connection is a thread,need a Stack,Stack settings usually is too large by default in Linux:
shell> ulimit -a | grep stack
You can see the real used Stack size,like this:
shell> cat /proc/$(pidof mongod)/limits | grep stack | awk -F 'size' '{print int($NF)/1024}'
The total mem consumed by connections is so large,I suggest you set it smaller,such as 1024:
shell> ulimit -s 1024


UPDATE with join condition on matching words in columns of another table I have 2 tables that looks like this:

Table A:

CREATE TEMP TABLE table_a (
  Column_1 text,
  ID_number int
);
INSERT INTO table_a VALUES
  ('foo,bar,baz', 123),
  ('qux,quux,quuz',456),
  ('corge,grault,garply',789),
  ('qux,bar,grault', 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  Column_2 text,
  ID_number int
);
INSERT INTO table_b VALUES
  ('foo','baz',null),
  ('qux','quzz',null),
  ('corge','garply',null);

I'm trying to copy across values from the ID_number column in Table A, where the values in Column 1 & 2 of table B can be found in the same row of Column 1 in Table A.

This is the kind of thing I was thinking of:

UPDATE table_b AS B
SET id_number = A.id_number
FROM table_a AS A
WHERE A.column_1 LIKE B.column_1
  AND A.column_1 LIKE B.column_2

.. but obviously this doesn't work.

How can I translate this into a proper query?

Additional info

table_a.Column_1 contains UK addresses, for example:

'47 BOWERS PLACE, GREAT YARMOUTH, NORFOLK, NR20 4AN'

In table_b I have the first line of the address in Column_1 (so, '47 BOWERS PLACE') and the postcode ('NR20 4AN') in Column_2.

I thought it would be best to simplify things, but maybe the actual data has some relevance in this situation.

table_a has about 30 million addresses. table_b has around 60k rows.

Performance is relevant, the faster this runs the better, and it will likely be repeated in the future.

The key here is that Column_1, represents three possible values for the JOIN. So what you want to use is string_to_array() (so long as those values are comma-separated and can not themselves include a comma).

Run this query,

SELECT id_number, string_to_array(column_1, ',') AS column_1
FROM table_a;
 id_number |       column_1
-----------+-----------------------
       123 | {foo,bar,baz}
       456 | {qux,quux,quuz}
       789 | {corge,grault,garply}
       101 | {qux,bar,grault}

Now, we can run our UPDATE using = ANY(),

UPDATE table_b
SET id_number = A.id_number
FROM (
  SELECT id_number, string_to_array(column_1, ',') AS column_1
  FROM table_a
) AS A
WHERE table_b.column_1 = ANY(A.column_1)
  AND table_b.column_2 = ANY(A.column_1);

You can alternatively use <@

WHERE ARRAY[table_b.column_1, table_b.column_2] <@ A.column_1;

That even makes it a bit more compact..

UPDATE table_b
  SET id_number = A.id_number
FROM table_a AS A
  WHERE ARRAY[table_b.column_1, table_b.column_2] <@ string_to_array(A.column_1, ',');


How we can take backup oplog on every hour and apply on top on full backup for mongodb We have full lvm snapshot backup. We are trying to take oplog backup in every hour and save as a file. We like to apply the oplog backup on top of full backup if we need to. We like to know how we can do this. We are taking everyday LVM snapshot full backup. Our Oplog is 80GB, which can hold 30 days of oplog. But we are facing issue to apply it. I can take the oplog backup in every hour. But question is : how to apply it? We don't want duplicate or any missing data.

The oplog is idempotent, you can run through the operations in it as many times as you want and you won't get duplicates or issues unless you run the operations on a set of data files in an odd state.

However, it should be noted that as long as you have the journal as part of the LVM snapshot, re-running the oplog is not necessary for a consistent backup.

With that said, if you do have a copy of the oplog, you can "replay" it in two ways:

Start up a new MongoDB instance, use it to access the oplog backup, and then create a custom script to read each operation and apply it in order (has the benefit of allowing you to filter and being rather quick)
Use mongodump/mongorestore to replay the oplog (see below)

To use mongodump in this way, you first have to dump it out into BSON format so that it can be resored:

mongodump --dbpath /path/to/folder/with/oplog/files -d local -c oplog.rs -o oplogDump

Next you move the bson file out of the folder (this is basically for convenience):

mkdir oplogRestore
mv oplogDump/local/oplog.rs.bson oplogRestore/oplog.bson

Now you can use mongorestore to replay the oplog and apply it to a given running instance:

mongorestore --host host:port --oplogReplay oplogRestore

You can also use the oplogLimit option if you only wish to replay up to a certain point.  See Asya's excellent answer here for more on that.

Please note that none of this will be particularly quick, nor is it strictly necessary (as mentioned above).


Find Iteration of Day of Week in Month I have a date dimension table in which I need to add a new column in which I define the iteration of the day of the week within the month (2 for the second Mon/Tue/Wed/Thu/Fri/Sat/Sun etc).

Is it possible to do this be making calculations solely on the date column of the table, which is of type 'date'?

It's actually surprisingly easy using DAY(), as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

select case when DAY(yourdate) <=7  then 1
            when DAY(yourdate) <=14 then 2
            when DAY(yourdate) <=21 then 3
            when DAY(yourdate) <=28 then 4
            else 5
        end as occurance
from yourtable;

(case short-circuits)


in-memory table quota reached in error Edit 1:
From article posted by first answer > this:

  Additionally, if you are using memory optimized table in the context of table valued parameters repeatedly, you may encounter this error even you use them in a separate batch and memory gets released.  We are looking to address this issue in future cumulative updates.  For now, you either need to use enterprise edition or periodically restart SQL or offline/online the database.

I use in-memory table valued parameters continually.  However as a troubleshooting step, I re-deployed the solution and removed all memory optimization.  I still received this error message.  After restarting the service the message is gone and I'm able to use in-memory tables once again.

Original Post:

I'm receiving this message (Sql Server Standard 2016 sp1 on VM):

  Could not perform the operation because the database has reached its quota for in-memory tables.

However, in-memory table usage is zero as shown here.  Any idea how to recover from this state or even start to investigate what is going on?

You may be running into this:
https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/


Do I need to Vacuum DB after massive insert? I use Postgres 12.7
I did massive insert using CopyManager.

Input: 320GB of tab separated data

Sample row is:
2021-03-16T07:14:41.121000  588467  1900000 0   0   0   0   S   0   LITTLE_CHAR

-- around 60 bytes
Table schema is:
create table my_table
(
    time         TIMESTAMPTZ NOT NULL, -- 8 bytes
    price        integer     NOT NULL, -- 4 bytes
    size         bigint      NOT NULL, -- 8 bytes
    bid          integer     not NULL, -- 4 bytes
    ask          integer     not NULL, -- 4 bytes
    openInterest integer     not NULL, -- 4 bytes
    marketCenter integer     not NULL, -- 4 bytes
    conditions   varchar(8)  not NULL, -- 8 bytes
    side         smallint    not NULL, -- 2 bytes
    symbol       varchar(16) not NULL  -- 16 bytes
                                       -- 8*2 + 4*5 + 8 + 2 + 16 =
                                       -- 16 +  20 +     10 + 16 = 62 bytes per row
);

It took 1TB on Postgres side. Three times more, what went wrong?
i was advised to run VACCUM. Why would I if there were only inserts? 24 hours of inserts.

Databases format the data for quick future reference and modification, and under ACID conditions.  This involves adding various kinds of headers and dividing the data into pages, and optionally reserving some free space in each page for future use.  But 3 fold seems extravagant if you really have no indexes.  I get each row taking a little over 101 bytes, 81 tuples per 8192-byte page, so quite a bit less than a two fold expansion.
You can 'select ctid from my_table' to see how many tuples are generally packed into each page.  It would be interesting to know if you get less than 81 tuples per page consistently, or if there are inconsistent gaps or regions of lower density.  If the table fillfactor is set to 100 and your load started with an empty table and had no false starts, then you shouldn't have either issue.  If you do, then perhaps a VACUUM FULL will fix it (at the expense of taking a huge amount of time, and scratch space to do the work).  Aside from that, for an insert-only table VACUUM would only be needed to set hint bits, freeze tuples, or set the visibility map; not for space reasons.
If your main goal is to make your data smaller, there are excellent compression tools available.  PostgreSQL is not one of them.


Oracle 11g XE - Using a stored procedure to grant privileges to a table I need to write a stored procedure that will grant certain privileges over a table to a user.

However it appears that GRANT cannot be used within a Stored Procedure.

My Curent code is as follows:

CREATE OR REPLACE
PROCEDURE "setUserPermissions" (u  VARCHAR2, l  INTEGER, tbl VARCHAR2)
AS
BEGIN
    DELETE FROM "userLvl" WHERE "user" = u;
    INSERT INTO "userLvl" VALUES(u,l);
    IF l>0 AND l<6 THEN
        GRANT SELECT ON tbl TO u
        IF l>1 THEN
            GRANT UPDATE ON tbl TO u
        END IF;
        IF l>2 THEN
             GRANT DELETE ON tbl TO u
        END IF;
    END IF;
END;

GRANT is a DDL statement. You can not run DDL statements directly in PL/SQL, but you can use execute immediate:

CREATE OR REPLACE
PROCEDURE "setUserPermissions" (u  VARCHAR2, l  INTEGER, tbl VARCHAR2)
AS
BEGIN
    DELETE FROM "userLvl" WHERE "user" = u;
    INSERT INTO "userLvl" VALUES(u,l);
    IF l>0 AND l<6 THEN
        execute immediate 'GRANT SELECT ON ' || tbl || ' TO ' || u;
        ...
    END IF;
END;


Should I use Database Mirroring? So I have the following scenario on my hands:

I have a product database that runs for a specific app, all inserts into the database come in through three separate connections.

Recently I have developed a new extension to the database in order to create predictions using SSAS.

My boss wants me to run a field test by creating a copy of the product database to see how well the predictions perform.

My problem is twofold:

According to Microsoft documentation of SQLServer 2012 the database mirroring feature will be removed in future versions: https://msdn.microsoft.com/en-us/library/ms189852.aspx The server is currently running 2012 but it might be upgraded in the future so I feel like this method is a dead end.

Should I be trying to use Database Mirroring for this scenario?

What is a good alternative for this way of Database Mirroring?

I think that mirroring is not the the best solution in this scenario, and discontinuation announcement is not even the main reason, especially if you're planning to maintain the test environment only for limited amount of time.

Mirroring is a high-availability solution that was designed to provide hot/warm standby db in case of failure, or offload reporting to exact copy of the database (with snapshots). So the following properties of mirroring are simultaneously its' drawbacks:

Both databases will be fully synchronized; you cannot select part of the db for mirroring, and may end up with high volumes of not needed data being transfered from principal database, reducing performance and increasing storage requirements for mirror.
Changes in principal db are reflected instantaneously on mirror db, impacting production db and increasing network traffic (as opposed to single after-hours updates).
You can only read from mirror db (through snaphot), so you won't be able to insert your own test data.

So if you don't need to update the test db from production environenment very often, the simplest solution would be to just manually restore full backups on your test db. You can even schedule your own COPY_ONLY backups and restores if you need your test db to be up to date.

You can also go with Merge Replication, which has these advantages over Mirroring:

Synchronization schedules: it won't impact production db during working hours
Filtering: you can choose what is being replicated
Conflict resolution: you can apply your own logic for handling conflict, if you want to populate your db with own test data along with production data.

Snapshot Replication and Transactional Replication are also a viable options here.

Hope this helps, and let us know what you finally chose.


How to set an Oracle user's default schema? I created a few new users in Oracle.  However, when running sqlplus, they all need to fully qualified the table names in query.  What's the best way to set a default schema for these new users?

There is nothing like PostgreSQL's set search_path in Oracle.

The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ...

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
EXCEPTION
  when others
    then null; -- prevent a login failure due to an exception
END;
/

If the list of users isn't too long, you can create a database logon trigger so you don't have to create that trigger for each user:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('TOM', 'DICK', 'HARRY')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
  when others
    then null; -- prevent a login failure due to an exception
END logon_trg;
/

Of course the list of users where you want to change the default schema, can also be taken from a table. In that case you only need to insert or delete rows from there in order to "activate" this feature (rather than re-creating the trigger each time).

Another option would be to create synonyms each time you create user that point to the real tables. You could automate that using a stored procedure that loops through all tables in one schema and creates the synonyms for them in the other schema.

Unless all your Oracle users work on the same tables I would strongly advise against using public synonyms which you would have to create only once - they can cause a lot of trouble if different application users exist in your installation.

Edit:

Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
declare
  has_role boolean;
BEGIN

    has_role := dbms_session.is_role_enabled('FOOBAR_ROLE');

    if (has_role) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
   when others
      then null; -- prevent a login failure due to an exception
END logon_trg;
/


MySQL Read Speed and Partitioning on Separate Drives Introduction

Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was partitioned for a fixed field -21 partitions, avg 1Mln Row/Partition- like this:

CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`id`,`fieldC`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

The root of the queries are always "WHERE fieldC = ? ... AND ..." so I always guaranteed access to a partition.

This table can be only MyISAM so I have installed the MySQL server 5.6.11

  Prior to MySQL 5.6.6, a SELECT from a partitioned MyISAM table caused
  a lock on the entire table;  in MySQL 5.6.6 and later, only those
  partitions actually containing rows that satisfy the  SELECT
  statement's WHERE condition are locked. This has the effect of
  increasing the speed  and efficiency of concurrent operations on
  partitioned MyISAM tables.

The setup consist on a Master/Slave topology with same HW (I can distribuite reads across Master and Slave)

Possible solution

I have tree choice for the hardware architecture(6 slot disk 3.5''):

Soloution #1

1xraid1 for OS, /tmp and binlog
1xraid1 partition for mysql data (ex: /diskA)
1xraid1 additional partition for mysql data (ex: /diskB)

Soloution #2

1xraid1 OS, /tmp and binlog
1xraid10 mysql data

Soloution #3 (2 disk waste for tmp!)

1xraid1 OS and binlog
1xraid1 partition for mysql data
1xraid1 /tmp

The first choice can help me to spread the I/O over two disk using the:

CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`id`,`fieldC`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,'')
    DATA DIRECTORY = '/diskA/customer'
    INDEX DIRECTORY = '/diskA/customer',
  partition pValueA VALUES IN('valueA')
    DATA DIRECTORY = '/diskB/customer'
    INDEX DIRECTORY = '/diskB/customer',
  partition pValueB VALUES IN('valueB')
    DATA DIRECTORY = '/diskA/customer'
    INDEX DIRECTORY = '/diskA/customer',
  partition pValueC VALUES IN('valueC')
    DATA DIRECTORY = '/diskB/customer'
    INDEX DIRECTORY = '/diskB/customer'
  -- other partitions
);

The second choice improve the fault tolerance level, but consider that I have a Master/Slave configuration so, in every moment, I can promote the Slave as a Master.

What kind of architecture you advice?

There are problems that at the moment I do not see?

Any other kind of suggestions would be greatly appreciated. :)

Thanks in advance

crx

What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`fieldC`,`id`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

This may help navigate faster through the partitions. The MySQL Query Optimizer may/may not be smart enough to need the reversal.
If you have other columns in the WHERE clauses such as

WHERE fieldC='...' AND fieldA='...'
WHERE fieldC='...' AND fieldB='...'

you will need matching indexes so quickly search within one partition. Otherwise, you may find yourself doing a full table scan with the partition.
With that in mind, please create the table like this:
CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`fieldC`,`id`),
 KEY C_A_Index (`fieldC`,`fieldA`),
 KEY C_B_Index (`fieldC`,`fieldB`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

This will then accommodate such WHERE clauses.
Give it a Try !!!
CAVEAT
I noticed your character fields are CHAR and not VARCHAR. That's great.
If any of the character fields are VARCHAR, please read on.
If there are any VARCHAR fields, you need not convert them manually. Simply change the row storage format as follows:
ALTER TABLE mytable ROW_FORMAT=Fixed;

This will make the table's read speed increase 20%-25%. I wrote about this before:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Sep 20, 2011 : Best of MyISAM and InnoDB Jan 03, 2012 : Optimized my.cnf for high-end and busy server


What do I need to sync a local site office SQL Server with the Head Office SQL Server The company has a desktop application powered by SQL Server from the head office that is dependent on an internet connection to our remote sites.  Usually when we move to a new site there is a good internet connection 5Mb+

We have a new site that is connected by a very slow satellite internet connection in the middle of nowhere.  As you can expect our application is really suffering.
Unfortunately we cannot just install a SQL Server box locally to power the application on site.

The application database has been designed (by a now non-existent 3rd party) that all separate contracts live in the same database and share common tables.

We have 3 projects running all writing to the one database, updates are about 500MB a week during active projects for all 3 projects.

Also there is a project status website powered from the head office SQL Server.  Management would not be happy if this new remote site showed no progress for the duration so we need to factor in getting data back to head office to power the website, again all from the one database.

It is outside the scope of this question to start re-designing the database. (hopefully that’s a project for later in the year)

Is it possible to install and operate a local SQL Server /
application server for the remote site clients that can update back
to the main head office SQL Server as and when it either has an
internet connection and / or enough bandwidth to do so?
How would one achieve this using SQL Server given the super slow Internet access?
What kind of timeframes would be involved to setup and test?

As usual other than money for a desktop box to act as a local server and a SQL Server 2008 Standard licence the budget is £0

EDIT: Added the tracert to show network latency from the remote site, unfortunately we are not the main contractor at this site and have little say in the provisioning of the comms.

1     1 ms     1 ms     1 ms  192.168.2.1
  2   544 ms   552 ms   564 ms  10.190.9.206
  3   543 ms   701 ms   655 ms  79.140.207.252
  4  1461 ms   542 ms   641 ms  79.140.207.254
  5   658 ms   564 ms   555 ms  ae1-143-ycr2.mcr.cw.net [213.38.254.81]
  6   601 ms   617 ms   588 ms  so-3-3-0-dcr1.lsw.cw.net [166.63.209.201]
  7   577 ms   568 ms   564 ms  xe-7-2-0-xcr1.lsw.cw.net [195.2.21.161]
  8   580 ms   564 ms   656 ms  xe-0-1-0-xcr1.lnd.cw.net [195.2.4.194]
  9   566 ms   565 ms   570 ms  ge-3-3-0.mpr1.lhr3.uk.above.net [195.66.226.76]
 10   605 ms   594 ms   569 ms  ge-5-1-0.mpr1.lhr3.uk.above.net [64.125.28.94]
 11   574 ms   569 ms   573 ms  so-0-0-0.mpr1.ams1.nl.above.net [64.125.30.130]
 12   570 ms   573 ms   570 ms  xe-0-0-0.er1.ams1.nl.above.net [64.125.26.81]
 13   635 ms   598 ms   581 ms  213.152.245.50
 14   588 ms   582 ms   582 ms  glfd-bb-1b-ae0-0.network.virginmedia.net [213.105.172.6]
 15   599 ms   618 ms   596 ms  manc-bb-1a-ae3-0.network.virginmedia.net [213.105.175.145]
 16   592 ms   609 ms   576 ms  manc-core-2a-ae0-0.network.virginmedia.net [82.15.206.246]
 17     *        *      592 ms  manc-lam-4-tenge11.network.virginmedia.net [213.104.242.26]
 18   583 ms   596 ms   636 ms  m229-mp4.cvx2-c.man.dial.ntli.net [62.252.216.229]
 19     *        *        *     Request timed out.

I ran into a similiar problem for an accounting package. Instead of trying to work out a way to move the SQL data around it proved to be much easier (and more cost effective) to provide a way to remotely access the application.

I opted for a TS 2008 box.  This includes a large number of international users with terrible bandwidth compared to the states.

EDIT - As syneticon-dj points out there are still potential issues associated with using TS on a satellite link. You would definitely need to look at the latency of your link for a TS solution to work. Some satellite links provide around 300 millisecond latency, others may be over a second.


How should I model a database structure to store word information on flashcards? Words, base words, and flashcards

I recently posted this question, where I asked about modeling words and base_words in a database - now that I've landed upon having separate tables, a second issue has arisen: a user adds flashcards containing words, which link to a base word, and contains a full sentence from one of the texts in our database, along with other grammatical information from our database. I'd also like to keep track of how many words the user has learnt, as well as how many base words.

Considerations

Given that I'd like to keep a count (and keeping track) of the user's learnt words separately from learnt base_words, this involves having a separate table (I think). In this table, in terms of normalization/denormalization, is it ok to store all the user's learnt word_ids in one array, and then learnt base_word_ids in another array?
Given that the words, base words, and sentences will already be in the database, it seems pointless to duplicate the information onto their flashcards - however, given that the student needs to study the flashcards offline, does this require duplication, or am I misunderstanding database structures? My aim is that the student can study offline, and then when he comes back online, if there has been a change to the original DB data that he has on his flashcard, that it will be updated automatically. The only new information that will go on the flashcard is the user's personal notes.

Warm regards,
Michael

Generally speaking, databases don't store arrays.

It sounds like you need several tables to track different things.

A "users" table which will store the userID (primary key), user's name, and so on.
A "base_word" table to store the base word, a BaseWordID (primary key) and such.
A "Users_Words" linking table. This table would have it's own primary key, a UserID foreign key to link to the Users table, a BaseWordID foreign key link to the base_word table, and a Learnt flag to track learnt or not (possibly other columns, too?)

If they need access to the data "offline," that's not duplicating the data. That's generating a "report" from the data. This might be an HTML page derived from the data that is printed (or printed to PDF), for example. Or an Excel file dump of the data.


Moving site to amazon EC2 - better to have 1 large instance or 2 medium instances? I currently have a website which is running on windows 2008 r2 standard along with sql 2012 express. All on the same box. The site gets around 700-900 visits a day,although that is slowly going up (yea us!).

The database itself is around 2gb in size and makes use of full-text search. The bulk of the full text search is done against a product table which currently has about 36,000 records in it and that's slowly growing.

We're leaning towards moving the site over to amazon EC2.

I have 2 basic questions

Would it be better performance wise moving it to EC2 or keeping it on a dedicated box? The reason we're considering the move is the ability to expand capacity if/when the website becomes busier without any downtime. We've been putting a lot of effort into getting more traffic and it's slowly starting to pay off.
If we do move it, would we be better off getting 1 large EC2 instance and putting everything on that one instance or would we be better off with 1 medium EC2 and 1 medium RDS instance? I don't think there's anything really heavy going on with the website itself, most of the resource i think are being used by the database. Highest mem usage for the w3 process is around 200megs.

I don't want the site to run any slower then it's running right now. Faster would always be nice. Budget is around $250/mo but we're willing to go higher if we see performance gains. Using either 1 large or 2 medium instances looks like it'll cost around $170/mo and that includes a WEB license for sql, which would be an upgrade for us as we're running express right now.

Thanks

If you expect your traffic to increase you will have to split your web and database layers at some point. So probably would be better to get separate instances for web and database servers now. Then you can start looking into making your web app scalable (so you could add additional web servers under a load balancer to handle traffic peaks) and tuning your database for better availability and performance.


Syncing data between onprem and Azure SQL I have the following

Scenario: an on-premise SQL Server 2008 R2 and an app using it as db. I want to create an Azure SQL database that will be synced (asynchronous replication) with the on-premise instance. I do not want to have just a one-time migration like you can get with SQL Database Migration Wizard - (if I understand it correctly).

Goal: I need to have the on-premise instance synced, let's say via daily job that will transfer the data, with my Azure SQL database.

Is it neccessary to setup Azure Data Factory for this? What about Data Sync? I can see that it is not available on the "new portal". Any advice how to approach this in a simple way would be great. Thanks!

EDIT: So, I have looked into the case of using transactional replication as Kin suggests. However it states only versions over SQL Server 2008. Any other ideas how to cope with this? Thanks!

No .. Azure data factory is not required.

You can set up transactional replication from on-prem to Azure. Do not use data-sync .. its buggy and have many issues.


Join duplicating the data - SQL Server I am working with SQL Server Always On and I am querying the data as follows:
SELECT
name as AGname,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

This gives me the output I need, however, I also need the DB name so I am trying the following:
SELECT
name as AGname,
DB_NAME(AG_DB.database_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
INNER JOIN master.sys.dm_hadr_database_replica_states AG_DB ON AG_DB.group_id = Groups.group_id
ORDER BY name

However, this is giving me issues as it is duplicating the information.
I also tried to fix this issue by doing the following:
SELECT
name as AGname,
(SELECT DISTINCT DB_NAME(AG_DB.database_id) FROM master.sys.dm_hadr_database_replica_states AG_DB WHERE AG_DB.group_id = Groups.group_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

The latter is giving me "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" error.
Any suggestions how I can get the required output without the duplicate entries?

Read the definition of sys.dm_hadr_database_replica_states at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-ver15 - you’ll notice that it says “On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database.”
I recommend you debug this by adding AG_DB.* to your select clause, to see what is in the rows you don’t want.


Create a result for a line chart with dates I have this table in SQLite which I use to record the risk level identified in vulnerability assesments according to the day the scan was performed.
This table looks like this:

Scan Date  | Risk
2014-04-20 | 26
2014-04-24 |  60
2014-04-26 | 156
2014-04-28 |  26

What I want to do is  to build a table like the following in order to use it to display the results on a line graph day by day (including those days without scanning, where the risk level is the same as the last scanning):

  Date    | Risk
2014-04-20|  26
2014-04-21|  26
2014-04-22|  26
2014-04-23|  26
2014-04-24|  60
2014-04-25|  60
2014-04-26| 156
2014-04-27| 156
2014-04-28|  26

How can I do it using SQL?

It's not pretty but if you have a calendar table (there are plenty of scripts online to create one), you can run the following. I joined calendar to your table and replaced the NULL's (which in your case would not have a scan date) with the last risk number.

Here's a SQL Fiddle

select caldate, scandate,
(select risk from yourTable where scandate = (select max(scandate) from yourTable where
  scandate is not null and scandate <= caldate))
from calendar a
left join yourTable b on a.caldate = b.ScanDate
where caldate between '2014-04-20' and '2014-04-28'


How to configure port for listening to traffic meant for PostgreSQL When setting up postgreSQL with a webserver (all on the same machine), is it critical to have 0.0.0.0:5432 open, or is just 127.0.0.1:5432 enough? Why or why not?

Overall, I'm trying to understand the role ports play in forwarding traffic to the pg service.

The background is that I'm trying to configure pgbouncer (a connection pooler) to work with postgresql, so need to ensure the relevant ports are open. However before I do that, I need to understand the way networking works at this level.

This is basic TCP/IP, rather than PostgreSQL-specific.

If the only things that will talk directly to the database server are on the same host as the database server, you only need listen_addresses = 127.0.0.1, i.e. listen only for local connections. For example, if you have pgbouncer proxying connections and accepting connections on a different port. Or if all user interaction with the DB happens via an application server and webapp.

The address 127.0.0.1 is by convention reserved for the local host. It's for talking to yourself.

There is no practical reason to allow (or block) connections to 127.0.0.1 from outside hosts, because no router will ever send packets to your host with that destination address, and your machine would never respond to ARP requests for that address. Most operating systems would ignore packets destined for 127.0.0.1 coming from external network interfaces as obviously bogus.

So you want to open connections from other hosts. 0.0.0.0/0 on port 5432 is "let anything connect to port 5432". That's an option if you don't want to be more restrictive about who can talk to the server.

Note that you may have to adjust both firewall rules and PostgreSQL's listen_addresses directive. Also possibly pg_hba.conf.

listen_addresses controls which local network interfaces PostgreSQL will accept connection attempts from.

pg_hba.conf controls which remote hosts PostgreSQL will accept connection hosts from, assuming it's even listening.

Firewall rules will stop those connection attempts even reaching PostgreSQL by filtering based on source address, destination port, and other criteria.


Add Check Constraint On Varchar Column to enforce no duplicates I have a varchar(max) column where users will populate a pre-given key from another system. I want to enable that the users are forced to enter a unique key which means no duplicate values on that specific column. Should I change the collation or is there some sort of check constraint that I can add?

Ordinarily, you'd just create a unique constraint on that column. But you can't put a unique constraint on a varchar(max) column. That could be close to 2GB per index entry.

In SQL Server, the maximum size of an index key is 900 bytes. I believe SQL Server enforces unique constraints by using a unique index, so I'd expect that limitation to apply to your problem, too.

In any case, I think this is an XY problem.


How database users see their objects in sql server I have a large SQL Server 2008 database that has many tables, stored procedures, and functions. This database also has several users.

How can I make it so that each user, after connecting to the database via Management Studio, can only see his/her related objects in the object browser BUT can execute functions, stored procedures and select tables owned by other users?

Sounds like you need to employ some database schemas to separate out user objects:

http://msdn.microsoft.com/en-us/library/ms190387.aspx

A database schema is nothing more than a collection of objects within a database.  DBO and SYS are common built-in schemas in SQL Server.  You can create a schema that houses a user's objects (tables, functions, stored procs . . .) and assign a level of security to that schema (owner, reader . . . ).  These help especially in terms of object-level administration.


Remove scientific notation from an XML column I have a XML column as shown below:

<Root>
  <Row>
    <Rowid>1</Rowid>
    <date>2013-05-06</date>
    <Balance>1.0002E7</Balance>
  </Row>
</Root>

When I query to get the balance, I get it as 1.0002E7. But I want to get it like this: 10002000.

How can I do it?

Can I make any changes so that the XML column itself is stored as I want it without scientific notation?

Specify float when fetching the value from the XML.

select @XML.value('(//Balance)[1]', 'float');

To change the value in a XML column with decimals included you need to first extract the value as a float and write it back as an numeric with the appropriate precision and scale.

Something like this.

update T
set XMLCol.modify('replace value of (//Balance/text())[1]
                   with (sql:column("X.Balance"))')
from YourTable as T
  cross apply (select convert(numeric(18,4), T.XMLCol.value('(//Balance/text())[1]', 'float'))) as X(Balance)

SQL Fiddle

The reason for the scientific notation is because it is the way SQL Server handles xs:float when using untyped XML.

From Type Casting Rules in XQuery

  When casting a value of type xs:float or xs:double, or any one of
  their subtypes, to a string or untypedAtomic type, the value is
  represented in scientific notation. This is done only when the value's
  absolute value is less than 1.0E-6, or greater than or equal to 1.0E6.
  This means that 0 is serialized in scientific notation to 0.0E0.


Does anyone deployed a succesful SSAS on docker? I'm trying to create a docker image that contains MS SQL server, analysis services, and restore a db (bak) and a cube (abf). The Sql server part is the easy stuff, and I imagine that the restore will be too quite simple... the unsuccessful part is the installation of SSAS.

I've already tried without success:

Splitting images (1 for MSSQL and 1 for SSAS)
installing from the inside of a container with SQL Server installed
trying differents account (NT AUTHORITY\NETWORK SERVICE,BUILTIN\ADMINISTRATORS)

Any chance that anyone as done something similar or is "impossible" to achieve such a [working] image?

This is a clean sample of what I'm doing:

FROM mcr.microsoft.com/windows/servercore:1903

ENV exe "https://go.microsoft.com/fwlink/?linkid=840945"
ENV box "https://go.microsoft.com/fwlink/?linkid=840944"

ENV sa_password="_" \
    attach_dbs="[]" \
    ACCEPT_EULA="_" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" \
    appname="demo"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

WORKDIR /

RUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \
        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \
        Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup ; \
        .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQL,AS,Tools /UPDATEENABLED=0 /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /BROWSERSVCSTARTUPTYPE=Automatic /ASSVCSTARTUPTYPE=Automatic  /SQLSVCACCOUNT='domain\user' /SQLSVCPASSWORD='somepassword' /SQLSYSADMINACCOUNTS='domain\user' /ASSVCACCOUNT='domain\user' /ASSVCPASSWORD='somepassword' /ASSYSADMINACCOUNTS='domain\user' ; \
        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup

Windocks offers Windows SQL Server containes, and plans to release support for SSAS and SSIS early in 2020 (ie., Jan).


Restarting hot_standby replication when the *slave* fails Context: master-slave replication on Postgres 9.4, slave died a couple of weeks ago:

the slave copied a WAL-File from the master,
told the master that the file is copied,
the master deleted the wal-file,
the slave tried to play the corrupt wal-file,
found that the file is corrupt,
and gives up to start up.

In the meantime the master have accumulated more logs than the database itself.

How can I tell master that the replication is over (to reconfigure the replication)? Any general comments how to proceed with this situation?

You must be using a replication slot.  It is causing the master to retain the log files, because it still thinks the replica will be needing them.  (That is the purpose of a replication slot).  Find the slot and delete it.  Then create a new one for the new replica you will need to seed.


when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools? While applying cumulative updates to sql server 2016 SP1, there are a bunch of services and applications that need to be stopped for the update to go on.

Among those is the very core of the virtual machine, the VMware Tools core service.

Is it safe to stop all the instances of this application in order to go on with the updates?

What could be a safe way of doing it?

None of the services in your screenshot have anything to do with VMWare.

The one you highlighted is Wmiprvse.exe which is basically WMI

  WMI resides in a shared service host with several other services. To avoid stopping all the services when a provider fails, providers are loaded into a separate host process named "Wmiprvse.exe".

The other ones are

SSISTELEMETRY is basically the customer improvement service
Windows Agent Service is probably something for NetIQ.
PBIEgwService is an on-prem Power BI gateway.

In any case, just update the box and reboot it afterwards (I'm assuming you have a maintenance window for the update anyway), I'm fairly sure you can stop those 4 services during the update.


How to monitor deadlocks When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, Instance:_Total, alert if counter: rises above value 3.  Is this a proactive way of monitoring it? What is the acceptable value? I would highly apprecate your help. Thank you!!!

You can use Event Notifications to get notified whenever a deadlock happens on the server:

See:

Immediate deadlock notifications without changing existing code by Mladen Prajdić
Using SQL Profiler to Resolve Deadlocks in SQL Server by Jonathan Kehayias

Additional links:

The Anatomy of a Deadlock - Jonathan Kehayias
Anatomy of a Deadlock - Part Deux - Jonathan Kehayias
Read/Write deadlock - by Remus Rusanu


Performance Metrics After Enabling Auto-Stats Short Version
After enabling AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC on a database, what performance metric will best display the changes?
Long Version
We just realized that the database for a legacy system has never had the AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC turned on (AUTO_UPDATE_STATISTICS was turned on).
As you would expect, after turning those on, our applications have performed much better. Response times are significantly better and the users are not waiting for the screen to refresh or pages to load.
The database is well indexed & we had already manually created stats where we thought they were needed. Immediately after enabling I checked sys.stats (joined on sys.stats_columns & sys.objects) and it only needed to create stats on three objects. (I have these logged in case quantifying these changes lies with them.)
I capture a lot of performance data on this server and I have looked at that data from prior to the change and after, but I have yet to pinpoint where the actual gains are.
I have other similar systems where auto-Stats are still turned off for that database. Those systems would also benefit from enabling them, but I am waiting in case there is some metric that I do not yet have, I can add it before making the changes.
My real question
Aside from the user’s experience, how do I quantify the increases made by changing the Auto-Stats settings on a system?
SQL Server 2008R2 on on Windows Server 2008.

If SET AUTO_CREATE_STATISTICS ON improved performance (anecdotally) for your end users, it likely means that queries with predicates on columns without statistics have gotten better execution plans, and thus shorter runtimes.  A "better" execution plan could result in one or more of these improvements:

Fewer logical / physical reads
Lower CPU usage
Shorter query runtimes / duration

I'm not sure what metrics you're capturing.  If you're able to get to the level of individual query execution time, you could find examples of the same queries before and after the change and confirm that they have reduced runtimes (or less CPU and reads, etc).

For example, if you're logging the contents of sys.dm_exec_query_stats to a table, you could find queries that have the same sql_handle but lower CPU, reads, etc after the setting was changed.

If you are logging execution plan XML, you might be able to look for the presence, and then absence, of warnings like this:

It shows up in the XML like this:

  <Warnings>
    <ColumnsWithNoStatistics>
      <ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Hist]" Column="Num" />
    </ColumnsWithNoStatistics>
  </Warnings>

If you just have server level aggregated metrics to look at, you might still see overall reads or CPU has gone down, but it's probably a lot harder to see that for sure if it's watered down by other workloads and maintenance activities.


Group table records by Item than Year I have a SQL table that is layed out as follows:

{Itemcode}  {FiscalYear}    {FiscalMonth}   {StartingQty}   {PeriodChangeQty}

Ball    2013    01  15000   -7500

Ball    2013    02  0   1000

Ball    2013    03  0   -500

Ball    2014    01  8000    -1000

I want to generate a table that looks like this

{ItemCode}  {FiscalYear}    {01}    {02}    {03}    {04}    {05}    {06}    {07}    {08}    {09}    {10}    {11}    {12}

Ball    2013    7500    8500    8000

Ball    2014    7000

Not sure at all how to do this.  I can get close with a pivot table but not quite.  Any ideas or clues as to how to accomplish this?

If you're on SQL Server 2012 or 2014, this is an aggregate that you can calculate with an ordered window function.

SELECT ItemCode, FiscalYear, FiscalMonth,
       SUM(StartingQty+PeriodChangeQty) OVER (
           PARTITION BY ItemCode, FiscalYear
           ORDER BY FiscalMonth
           ROWS UNBOUNDED PRECEDING) AS EndingQty
FROM dbo.theTable;

PARTITION BY defines a partition that spans over all of the rows for the current ItemCode and FiscalYear. Within this partition, you define a window using "rows unbounded preceding" that spans from the first FiscalMonth up to and including the current FiscalMonth. Those are the rows for which you SUM() the starting quantity and the period change.

All of this assumes that the starting quantity is given for the first month of each year and zero for all other months.

Now, all you have to do is apply a PIVOT to this in order to get the output in neat columns.

If you're on SQL Server 2008 R2 or Azure SQL Database, ordered window functions aren't supported. You'll have to resort to a much less pretty join-based solution, which also comes with a potentially hefty performance penalty. Something like:

SELECT a.ItemCode, a.FiscalYear, a.FiscalMonth,
       SUM(b.StartingQty+b.PeriodChangeQty) AS EndingQty
FROM dbo.theTable AS a
INNER JOIN dbo.theTable AS b ON
    a.ItemCode=b.ItemCode AND
    a.FiscalYear=b.FiscalYear AND
    a.FiscalMonth>=b.FiscalMonth
GROUP BY a.ItemCode, a.FiscalYear, a.FiscalMonth;

Note: This query assumes that (ItemCode, FiscalYear, FiscalMonth) is unique.

I haven't tested these queries, so they may contain spelling (or thinking) errors. Best of luck.