texts: Data from different tables into one table

texts:
I've requirement where I need to store information from different sources in my system and the process that data. When I say from different sources it will be completely different table structure from all the sources and these sources will be infinite.

If I've to maintain the same table structure of source then I've to create multiple tables for each source and I'll have thousands of sources so my database will be bloated with lot of tables. All this information is accessed within my system so for each source I need to maintain mapping information with that I can query particular table to get that source data.

Hence I came with a single table structure to store that data and it looks like below - tables with column information:

TableMetaData
Id, TableName, SourceId

TableFieldMetaData
FieldId, FieldName, TableMetaDataId

TableFieldValues
Id, MetaDataFieldId, Value

Example source table

Customer
CustId, Name, Location
1, ABC, London

above table will be stored as below in my three tables:

TableMetaData
1, Customer, XYZSource

TableFieldMetaData
1, CustId, 1
2, Name, 1
3, Location, 1

TableFieldValues
1, 1, 1
2, 2, ABC
3, 3, London

I want to know whether this is the best approach to store data or not? I know this single table will have millions of records and retrieving the data will be very tedious too. Is there any better approach for storing this kind of data? Or should I go with multiple tables approach which I mentioned initially where every source will have replica of their tables in my system.

What is the industry standard for this?


texts: Is there a way to check which computer or IP used a particular script?

texts:
As the Title suggest, is there a way to check what IP used a particular SQL Script?

The scenario is, there is a recreation script in a email and we hypothesize that somehow, that recreation script was used and the data inside the database was removed. We want to know which computer used the script. we know that when was the script was used. But somehow the user name we used is SA which can be used by other programmers as well (sorry for this).

Is there a script or a utility that can tell us the said information?

Sample of said script:

if  exists
            (
                select  *
                    from    sysobjects
                    where   id  =   object_id   (   N'dbo.DSTRNP'   )
                        and OBJECTPROPERTY  (   id, N'IsUserTable'  )   =   1
            )
    drop    table   dbo.DSTRNP
go

create  table   dbo.DSTRNP
    (
        TEST    char(12)    not null
            ,   constraint  pk_c_DSTRNP primary key clustered
            (
                TEST
            )
    )
go


texts:
I'm trying to select * records. The problem is some only have values ('MNT', 'NPT', 'OM') for attrib_type 1-3 in this case. Attribute types 4 & 5 do not exist in the older records therefore there are 4500 records that are not being selected. I would like to be able to select ALL records even where there is no attrib_type 4 & 5 value.

The SQL I have tried is below

SELECT
   central_site.site_code,
   central_site.site_name,
   ward.ward_name,
   area.area_name,
   feature_type.feature_type_code,
   feature_type.feature_type_name,
   feature.feat_cent_east,
   feature.feat_cent_north,
   feat_measurement.feature_quantity,
   feat_attrib_type.attrib_value_code,
   feat_attrib_type_2.attrib_value_code,
   feat_attrib_type_3.attrib_value_code,
   feat_attrib_type_4.attrib_value_code,
   feat_attrib_type_5.attrib_value_code
FROM
   area,
   central_site,
   feat_attrib_type,
   feat_attrib_type feat_attrib_type_2,
   feat_attrib_type feat_attrib_type_3,
   feat_attrib_type feat_attrib_type_4,
   feat_attrib_type feat_attrib_type_5,
   feat_measurement,
   feature,
   feature_type,
   site,
   ward
WHERE
   central_site.site_code = site.site_code AND
   site.site_code = feature.site_code AND
   ward.ward_code = feature.ward_code AND
   area.area_code = feature.area_code AND
   feature_type.feature_type_code = feature.feature_type_code AND
   feature.site_code = feat_attrib_type.site_code AND
   feature.plot_number = feat_attrib_type.plot_number AND
   (feat_attrib_type.attrib_type_code = 'MNT' OR feat_attrib_type.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_2.site_code AND
   feature.plot_number = feat_attrib_type_2.plot_number AND
   (feat_attrib_type_2.attrib_type_code = 'NPT' OR feat_attrib_type_2.attrib_type_code IS NULL) AND
   feature.site_code = feat_measurement.site_code AND
   feature.plot_number = feat_measurement.plot_number AND
   (feat_measurement.measurement_code = 'NSNP' OR feat_measurement.measurement_code IS NULL) AND
   feature.site_code = feat_attrib_type_3.site_code AND
   feature.plot_number = feat_attrib_type_3.plot_number AND
   (feat_attrib_type_3.attrib_type_code = 'OM' OR feat_attrib_type_3.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_4.site_code AND
   feature.plot_number = feat_attrib_type_4.plot_number AND
   (feat_attrib_type_4.attrib_type_code = 'PMNT' OR feat_attrib_type_4.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_5.site_code AND
   feature.plot_number = feat_attrib_type_5.plot_number AND
   (feat_attrib_type_5.attrib_type_code = 'WMNT' OR feat_attrib_type_5.attrib_type_code IS NULL)
ORDER BY
   central_site.site_code ASC


texts: How to know what server and port to select in MongoVue

texts:
I am unsure about which server and port to select when trying to start up MongoVue.
FYI, MongoVue is the windows GUI for using mongodb (for those macusers who may want to answer this question).
I have a windows laptop and have already installed mongodb.

I have received a data dump with a lot of json and bson files.
The files are on my computer.

I don't know how to connect and upload all these json and bson files onto my MongoVue interface.

I am asked to fill in a server and port name. When I tried to type "localhost" for server name, it didn't work.
I would appreciate any help in this matter.


texts: How can I query for a sequence of values in a table


texts: How to return duplicates if IN (...) has duplicates?

texts:
I have a list containing 1, 1, 2.

I am using it in a query like this:

SELECT A.* FROM A WHERE id IN (1, 1, 2)

The output is this:

id
---
1
2

I want the results to be like this:

id
---
1
1
2

How can I achieve that?


texts: How to create a normalized database to manage inventory for a small business?

texts:
I am writing a Java application to manage the inventory of a small furniture business. I know little about databases, I have a bit of experience with SQL queries and more experience with Java programming, however I have never created a program that requires a database and I do not know how to properly design the database tables for maximum efficiency and no redundancy.

The program requires tables for the items being sold and the customers they will be sold to. The main product being sold is carpets and I expect it to have its own table? Other products like vases, desks, etc. can be under one table because they don't have unique details like the carpet size.

Thus far my database is looking like this:
(Primary and foreign keys are in italics)

Customer:

-CustomerID PK,

-FirstName,

-LastName,

-Address,

-Phone,

-Email

Order:

-OrderID PK,

-CustomerID FK >- Customer.CustomerID

OrderDetails:

-OrderDate,

-SoldPrice,

-OrderDescription,

-InvoiceNumber,

-OrderID FK >- Order.OrderID,

-ProductID FK >- product.ProductID

Product:

-ProductID PK,

-ProductName,

-ProductPrice,

-ProductCode,

-ProductTypeID FK >- ProductType.ProductTypeID

ProductType:

-ProductTypeID PK,

-ProductTypeDesc

CarpetProduct:

-CarpetID PK,

-CarpetName,

-CarpetCode,

-CarpetLength,

-CarpetWidth,

-CarpetDescription,

-ProductID FK >- Product.ProductID

OtherProduct:

-OtherProductID PK,

-OtherProductDescription,

-OtherProductCode,

-ProductID FK >- Product.ProductID

Please help me organize this database by adding, removing, or joining tables and fixing any issues. I know this table is far from being normalized and efficient. Any help would be greatly appreciated. Let me know if further details are required.

All the carpets follow one ID system (starting from 1 to around 4000) and all other items (that are not carpets) follow a separate ID system. So their are two separate codes used depending on whether it's a product or not.


texts:
I've been using PostgreSQL 10 and 12 on Windows successfully with DBeaver in the past. Now I've set up a new development machine and installed the current PostgreSQL 13.0.1 and the current DBeaver 7.2.4 on Windows 10.2004. When connecting to the local Postgres server, I got the message that a connection wasn't possible in the Postgres 10 style and I should check the hb_pga.conf entries.
I went to my hb_pga.conf file and changed all of the shiny new scram-sha-256 entries back to the old md5 and restarted the database server.
But DBeaver won't connect. Is this a problem with the latest DBeaver or Postgres or what? Has anybody managed to get these two to talk to each other with the latest versions?
Unfortunately DBeaver seems to be based on Java and is limited to JDBC which doesn't seem to be maintained anymore so it can't use the current Postgres authentication scheme. I've looked for multi-DBMS alternatives to DBeaver but couldn't find any that looks convincing. Same old problem, good GUI database clients are very expensive, the free ones are often of poor quality. I hoped DBeaver was a good solution.


texts:
I set up transactional replication between two servers on various tables. I noticed that there were a few deadlocks around the time of the snapshot generation(which it appears is expected).

What I didn't expect is a flood of deadlocks about 15 minutes after replication had completed. Example deadlock:

user stored procedure has Sch-S lock on Table 1
replication stored procedure(sp_MSsetfilteredstatus) has Sch-M on Table 2

user proc is waiting for Sch-S on Table 2 while replication stored proc I believe is waiting for a Sch-M on Table 1.

I was under the impression that the transaction log was read from after the snapshot was generated so this would have little to no impact on the server, am I mistaken? Is there something I can do to avoid the ongoing deadlocks?


texts: Upgrading SQL Server 2008 R2 Standard edition to SQL Server 2008 R2 Enterprise edition what is the process and the impact?

texts: We are using SQL Server 2008 R2 Standard edition with Visual Studio 2010 (Web API). Also we have created SSIS packages using business intelligence. If we decide later on to upgrade to SQL Server 2008 R2 Enterprise edition, what is the process and the impact on the .NET environment?


texts: Resource Governor on 2014 - CPU Not Being Throttled

texts:
We have a specific query ran from our platform that consumes a lot of CPU on a box that already has Enterprise, so Resource Governor seemed like a good solution. I'm working on configuring it on our Dev server, but I'm not seeing the behavior I expected. The server has 4 cores and 16GB of memory with SQL Server 2014 on Server 2012 R2. I modified my resource pool and workload group like so:

USE [master]
GO
ALTER RESOURCE POOL [CRTestRP] WITH(min_cpu_percent=5, max_cpu_percent=5,        cap_cpu_percent=5,
min_memory_percent=0, max_memory_percent=100, AFFINITY SCHEDULER = AUTO, min_iops_per_volume=0,
max_iops_per_volume=0)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
ALTER WORKLOAD GROUP [CRTestWG] WITH(group_max_requests=0, importance=Low,     request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25, request_memory_grant_timeout_sec=0, max_dop=1) USING [CRTestRP]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE

I realize that Resource Governor won't kick in unless there's resource contention. To test the effects, I created a simulated workload on the server (it's a node that holds secondary replicas for Availability Groups, so mine are the only queries against) that raised CPU to an average of 88% using HammerDB. I've verified my test queries are getting correctly put under my workload group by the classifier function, and I have a few test queries ready to go.

After kicking off HammerDB and letting it run for a few minutes, I kicked off one of the CPU intensive queries. It does throttle that one effectively, holding it to ~5% CPU, however, when adding additional connections running the same query, CPU Usage for that workload group shoot up and negatively affect the default pool:

In the graph above, CPU is at 100% for the entire time frame. The query in question doesn't seem to be relevent, other than the fact that it would go parallel if not for the MAXDOP 1 on the workload group.

Has anyone else seen this behavior? Has anyone had success with Resource Governor at all? What I really wanted out of it was that these queries wouldn't EVER be able to use above a certain amount of CPU for all of them combined at any one time, but it doesn't look like that's the case. It seems like there should be a configurable value for what CPU % constitutes "contention".

In any case, thank you all for your help!


texts:
Let´s say I have 3 entities. Can I have them all linked to each other "forming a circle" where each one is linked to the other trough a many to many relationship.

Cheers


texts:
We have a 4 node AG set up across two data centers using 4 replicas with a file share witness. Nodes 1 and 2 are in data center 1 and Nodes 3 and 4 in data center 2. Node 1 is primary and is set to synchronous commit with automatic failover to node 2 in the same datacenter. Node 1 is set to asynchronous commit to nodes 3 and 4 in data center 2. Node weighting is set to one for nodes 1 and 3 (main node in each data center) and zero for nodes 2 and 4.

Please, can someone tell me what the behavior would be if we had an unexpected SQL Server shut down at node 1?

Would the AG attempt to failover to node 3 in data center 2 due to the node weighting, fail and not attempt to fail over to any other replicas. Or would the AG group ignore node 3 due to it being async and try to fail over to the first synchronous replica with automatic failover based on the preferred owner's list?


texts: Can I restore a SQL Server .bak files without SQL Server?

texts:
I have some large .bak files from a SQL Server 2005 dump.

Can I restore these without using SQL Server, either to PostgreSQL, MySQL, or to flat text files?

An open source solution would be most useful.


texts: Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

texts:
I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins and password.

But is there a way I should see if all the login and password are moved correctly?

Is there any documentation to follow during migration? I tried looking online couldn't find one.

Thanks


texts: Schema for social network using the supertype/subtype pattern

texts:
Scenario

I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure if it should be the same kind of Post, or a different type of post for diaries, because I am not sure how to implement it with a schema design).

In the business environment of relevance:

Users could post Posts, and inside them have Photos.
Comments, Posts and Photos can have Likes and Comments.
Posts can have several collaborators/owners (which is why I added the Posts Participants table in the first diagram shown below).
Users can search for Posts either by searching for keywords inside the posts texts, or by the hashtags of the posts (that's why I used tsvector type for both of them, indexed with the GiN index type)
Users can also have private diaries, and each diary has a posts of its own.

First diagram

What I did at first is just have separate tables for everything, like here (without the diary part):

Second diagram

And then after reading this post and MDCCL's answer, I'm trying to implement this with the supertype/subtype approach. So far, this is what I got:

Considerations and questions

But I have a few problems here:

The Photo table has two references, one to Regular Post and one to Diary Post, which is a violation again of the subtype approach.

Should I make the Post as a general supertype, and then have "child" table Regular Post and Diary Post, or should I just combine them into one Post table?
The Comments can be liked. So on the one hand it acts as a supertype, with Likes having reference to its id.  But on the other hand, comments are also an independent type, just like Likes, because users can put comments on Posts and Photos.

Is that something that is supported?


texts: Log shipping restore: Only members of the sysadmin fixed server role can perform this operation

texts:
On my secondary, I am seeing many messages like these in sysjobhistory from the LSCopy and LSRestore jobs for each database:

Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping)
Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider)

The job steps are being run by "SQL Server Agent Service Account". That service account is a domain service account which has a login with the sysadmin role.
What else should I be looking for to find the true cause of the messages?
The msdb.dbo.log_shipping_monitor_secondary table hasn't been updated in months based on the values in last_copied_file, last_copied_date, etc., yet the LSCopy and LSRestore jobs are succeeding (and show current file names in the messages). (The LSAlert job is disabled.)
One interesting thing is that the names in the log_shipping_monitor_secondary.secondary_server column is the name of the primary server. From what I can tell, the secondary server was cloned from the primary and renamed before log shipping was set up, but the SQL instance name wasn't changed.
I'd like to resolve all this without completely rebuilding this secondary server.


texts: Which SQL Server/Windows logins require the sysadmin role?

texts:
I'm setting up a new SQL Server instance. Which of these SQL Server/Windows logins should have the sysadmin role?

NT AUTHORITY\SYSTEM
NT SERVICE\SQLWriter
NT SERVICE\Winmgmt
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT


texts: SQL Server 2008 R2 Service pack 3 won't recognize all instances

texts:
I have two instances on one of my servers, and when installing service pack 3 the GUI installer does not recognize one of the instances. Server is Windows Server 2008R2 Enterprise x64 SP1

Both instances are standard edition 64 bit, both instances are named.

Before attempting the service pack one instance was 10.50.4033 and the second instance was 10.50.4000. When only the first instance appeared I went ahead and applied the service pack successfully, restarted, and ran the installer again hoping the second instance would appear miraculously. It did not. I am local server admin, sysadmin, all that jazz. I attempted to run the installer as admin, no luck.

I have scoured the SQL Server logs, application logs, system logs, and can't find anything that would point to why it is not picking up the second instance.

What next? Why is the installer not recognizing the second instance? Where else can I look for clues?

EDIT: Results for Kin's TSQL script

PhysicalMachineName servername
SQLServerName       servername\instance1
ServerEdition       Standard Edition (64-bit)
ProductVersion      SQL Server 2008 + SP3 + (Build 10.50.6000.34)
O.S.                Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Collation           SQL_Latin1_General_CP1_CI_AS

PhysicalMachineName servername
SQLServerName       servername\instance2
ServerEdition       Standard Edition (64-bit)
ProductVersion      SQL Server 2008 + SP2 + (Build 10.50.4000.0)
O.S.                Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Collation           SQL_Latin1_General_CP1_CI_AS

EDIT 2: The installer logs show that it is recognizing both instances and all shared features. In the User Input Settings it shows ALLINSTANCES: False

EDIT 3: The details log led me to KB969052 and the script that comes with it has revealed several missing .msi files. Going to attempt to fix that and see what happens.

EDIT 4: created all the missing directories and moved copies of the .msi files from the installer cache and ran the update again. Nothing. Just shows grayed out boxes for the one instance already at SP3 and the shared features.

I even ran the Windows Installer Cache Verifier Package from Microsoft to check if the installer cache had been compromised. No errors or missing files were found.

This is a test system, and on a lark I checked the prod setup. Found the same thing. Main instance at 10.50.4033 and second instance at 10.50.4000, attempted to run the SP3 installer and it only recognized the main instance.

Both servers show that at some point in the past KB2977320 Security update for SP2, which would brings the instance up to .4033 failed to install. I am going to assume the failure is on the second instance for both servers. Downloading the KB and attempting to run the GUI also fails to recognize the second instance.

EDIT 5: I am officially perplexed. The setup bootstrap logs for the instance that the x64 installer recognized are in the C:\Program Files directory. The setup bootstrap logs for the instance that was not recognized by the installer are in the C:\Program Files (x86) directory. I tried the x86 installer and it recognizes the second instance although it fails to upgrade the instance.

This is a standalone database server built for one specific vendor application because they were being difficult, and it has been plagued with problems from the start. I would not be surprised if half the problems I have come to SE with have been because of this application. At this point I am going to assume the vendor has done something in the past that is causing this and leave it where it is. The problematic instance also shows up under the Wow6432Node registry keys for what it's worth.


texts:
How to specify query from mongos shell which is inteded to query data from secondary?

The goal is not to bother primary load on write and use data in secondary shards instead.


texts: MySQL read only slave - how to create views

texts:
After a lot of resistance I have recently been given MySQL access to our CRM system, via a read-only slave.

I have an issue now in that I am really limited to what I can do with the data in MySQL - cannot create views etc. The IT head said I should continue to use Access, with the tables from the slave imported using ODBC connections. I can do this but I'm not sure it's the best plan long-term - I had planned to move my processes into MySQL.

Is there any way around this? Can I somehow link the read-only tables to another MySQL database which I can edit?

Thanks


texts: How to keep track of functional dependencies when performing a BCNF decomposition

texts:
I have the following relation and functional dependency set:

R(A,B,C,D,E,G,H)
F = {AB -> CD, E -> D, ABC -> DE, E -> AB, D -> AG, ACD -> BE}

I am really confused about how to keep track of functional dependencies when decomposing R.

For example, if we decompose:

R1 = (ABCD)
R2 = (DEGH)

then (the easy ones):

F1 = {AB -> CD}
F2 = {E -> D}

So what I want to know is, what is the best STRATEGY of keeping track of the rest of the functional dependencies in F1, F2.

Is it enough to find the closure of all subsets of ABCD to complete F1? or should I find closures of all subsets of ABCDEGH before the decomposition occurs? I'm actually not quite sure how this would happen. for example:

Before the decomp:

D+ = DAG

After the decomp

D+ = D?


texts:
I am a SQL Server DBA configuring an SSIS package that uses a connection to a remote vendor MySQL database instance.

The vendor has sent us 3 files to implement SSL transport to their MySQL database. The filenames are: ca.pem, client-cert.pem, client-key.pem. We're currently connecting to their instance via ODBC driver MySQL ODBC 5.3 ANSI Driver, version 5.03.09.00 supplied by Oracle.  I see that there is a selection for working with SSL when configuring the ODBC connection (see screen shot). Can someone advise me on how to set up these 3 files in this context?


texts:
Given two primary PG12 servers, A and B, let's setup logical replication from A to B.
A --> B
Both servers can and do receive writes, and don't use sequences or unique constraints so there is no conflict.
Is it possible to set up replication from B to A, without replicating the data replicated from A?
Writes from A:
A --> B -x-> A
Writes from B:
B --> A -x-> B
End result is both databases have the same data set.


texts: MySQL multiple rows with same id meeting two different conditions at same time


texts: Please enlighten me about FILESTREAM and BLOB files

texts:
This is a really confusing topic for me.

I can understand what a BLOB is, I can easily use it.
I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database and restore it with the folder created to store these files. Things like these, I think, I can understand well.

What I don't get is:

What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?
When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.
With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file? As it is inside the database, I can easily recover it.

I'm using .PDF as an example

I can use and implement both approaches but it still not clear to me what happens with them. For me, it's the same thing, but one is stored outside the database, and the other one inside the database.


texts:
When creating a new remote tasks via Sybase Central for mlagent, it is possible to import task from file.

How to export remote task to a file so it can be imported into another Sybase Central installation (e.g. remote)


texts:
Apologies if this is not the right SE site to ask.

I'm designing an application for catalogue management.
Any element in the hierarchy has to be versioned, i.e. when I change a property of it, a new version is created. The new version can also have different children than the previous. The user can switch between versions when she manages the catalogue, so I'll need to list all the existing versions of a child. When switching version, all the descendant will be updated).

My first thought was something like

| A            |  | B            |  | C            |
|--------------|  |--------------|  |--------------|
| ID  (k)      |  | ID  (k)      |  | ID  (k)      |
| VER (k)      |  | VER (k)      |  | VER (k)      |
| ...          |  | A_ID (fk)    |  | B_ID (fk)    |
|              |  | A_VER (fk)   |  | B_VER (fk)   |
|--------------|  | ...          |  | ...          |
                  |--------------|  |--------------|

Where the entities' key is always something like (ID, VER) and the parent's fk is (P_ID, P_VER).

Is this a good way to implement it?

Update: It's apparently more complicate than that. The meaning of the versioning is that elements have a validity time and we need to keep the older version. E.g. a client buys product with price x. After 1 January it's gonna cost y.
New clients after 1 January get new price y, but old client keeps paying x.

Adding a timestamp column is probably the best option here.


texts: How to calculate query that access system view cost in Oracle?

texts:
For a query that returns if there are any active transactions within current session ,

  SELECT COUNT(*)
  FROM
  v$transaction t
  INNER JOIN v$session s ON (t.ses_addr = s.saddr )
  INNER JOIN v$mystat m  ON  (s.sid = m.sid )
  WHERE ROWNUM = 1;

EXPLAIN shows 0 cost. However, that doesn't seem to be true. Periodically running this query in high load environment causes server to waste almost all its resources on it.

What is the right way to estimate impact of such queries (I believe the same problem occurs not for just this particular query, but anything that involves system views)?

Thanks.


texts: SSIS Dataflow package works with Native Client but rejects data with OLE Db connection

texts:
I've been trying to improve an SSIS ETL package to see if I could get better throughput.
I was trying to change the buffer settings as per this link, and thought i'd try using a newer driver/provider.
The package is now failing. On 1 table containing 4500 rows, the Dataflow is rejecting ~1/3rd of the data. The error message provided is:

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on
Import data from Source Table returned error code 0xC02020C4.  The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information
about the failure.

In this case only thing changed is the Provider from Native Client to OLE Db. The tables, data and package are otherwise unchanged.
The table contains, varchars, ints, nvarchars(nn), nvarchar(max), geometry, bits and datetimes. Are any of these incompatible with the OLE Db Provider?


texts: How I can find the most resource-intensive queries that has ever run in my database and use a specific table?

texts:
In my database I want to check whether my idea on an improvement upon table schema works. Therefore I want so look which are the heaviest select queries ever run into our database and use a specific table that I want to do the changes.
My idea is to check the speed and database load of our heaviest queries in my database, then do the schema changes (or possible query refactoring as well) and rerun them so I can benchmark my idea with real data.
Hence I can have a proof that my changes actually improve search speed.
So how I can search which queries upon a postgresql RDS instance even run and how heavy they are?


texts: how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10

texts:
I have written program something like below as per my question:
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);

BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username
    from gui_audit_trail
    WHERE messageid='LS_REF_DATA_ROW_DELETED'
        and description like '%BASE_HOST%'
        and to_char(recorded,'YYYY-MM-DD HH:MI:SS')=to_char(OLD.last_update,'YYYY-MM-DD HH:MI:SS');
        RAISE NOTICE 'username is (%)',username;
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();

But "username" is not populating in output, expectation is "administrator" as output.
The two table data is as below:
select * from gui_audit_trail;
 id  |    userid     | moduleid  |    classid    |     typeid     |        messageid        |        recorded
 718 | administrator | eventlink | CLASS IGNORED | METHOD IGNORED | LS_REF_DATA_ROW_DELETED | 2021-03-10 18:48:02.439

select * from hist_base_host;
 zone_id | site_id |   org_id   | start_date |  end_date  | reserved_capacity |        last_update         | chg_code |   user_id
       5 |       9 | PBTP       | 2002-01-01 | 2015-01-01 |              0.00 | 2021-03-10 18:48:02.413111 | D        |

in select query in above function, I want to cut time till "2021-03-10 18:48:02" from both columns recorded and last_update, but seems its not working, Is there any way to achieve the same functionality?


texts: CROSS JOIN with later PIVOT going rather slowly

texts:
I have an operation that is going rather slowly, probably because my CROSS JOIN first creates a ton of rows for SQL Server to work on, which are later GROUPed and PIVOTed into far fewer rows and a bunch of columns.  The aim is to get the "times each record was downloaded" per "unique organization name", where the unique organization names are the unique OrganizationName entries in the AdamUser table and the times downloaded is the number of associated entries in the MaintenanceJobHistory table.  Lastly, Record is joined onto each row to output more info about that record.  Here's the query:

SELECT *
FROM (
    SELECT
        rec.[Id]                                      AS RecordId,
        CONCAT('Downloads_', usr.[OrganizationName])  AS OrganizationName,
        COUNT(hist.[Id])                              AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 12:00:00' AND [CreatedOn] < '2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationName] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationName] IN (...~200 columns...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

And here's the execution plan:

https://www.brentozar.com/pastetheplan/?id=ry8tXM3mg

It does give me the correct output, but I noticed that the Sort operation before the Stream Aggregate is taking a long time, and spills into tempdb.  This may be why the query takes ~5 minutes with the ~200 columns I have for the normal dataset, whereas my test dataset with 6 columns in the PIVOT only takes maybe half a minute.  In terms of how many rows it's dealing with, the two tables being CROSS JOINed (Record and AdamUser) have 38397 and 1017 rows respectively.

Is there a way I can speed this up, or is it necessary to take this long for the number of rows and columns I have?


texts:
One of our ancient PostgreSQL server's used disk space has increased to 90%. First I've checked the archive command:

archive_command = 'rsync -aq %p /data/wal-archive/%f'

The problem with that command is rsync is not installed. So I've replaced it with:

archive_command = 'test ! -f /data/pgsql/data/mnt/server/archivedir/%f &&
    cp %p /data/pgsql/data/mnt/server/archivedir/%f'

After the replacement here is the output of postgresql.log:

  cp: cannot stat `pg_xlog/000000010000000000000001': No such file or directory

I presume beginning of the pg_xlog files removed by hand. Do you have any idea how to make this archive process work?


texts:
Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process?

BACKGROUND:

I have Change Tracking on database with retention set to 1 minute:

ALTER DATABASE [test] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 1 MINUTES)

I do some updates in my test table and then wait a bit longer than the retention period (lets say 3 minutes). As the cleanup process is asynchronous, I do understand that there NO guarantee that Change Tracking tables will be cleaned up after 1 minute. It can happen at any time that is > 1 min.

I found this procedure to call the cleanup process manually:

EXEC sp_flush_commit_table_on_demand 100000

But it does not work as expected.. (Change Tracking rows are still in place after retention period)


texts:
I have the following query:

SELECT
    CONCAT(user1.name, ' ', user2.name)
    IF(user1.salary < user2.salary) as userSalary
FROM `users` AS user1
LEFT JOIN users AS user2 ON user1.id = user2.id
ORDER BY user1.salary ASC

I am trying to find all users which have a lower salary than the next user (in the same table). The table has a simple structure:

Fields:

ID (PK) | name | age | address | salary

This is the error I am getting at the moment:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(user1.sallary < user2.sallary) as userSallary
FROM `users` AS user1
LEFT JO' at line 3

What am I doing wrong here?


texts: SQL Indexed View for Datamart Reporting with Transactional Replication

texts:
Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables?

We have a Customer timeline of tables which can be simply joined on CustomerId in each tables.

Indexed views definitely should not be created in high volume OLTP environment database, due to write latency in creating tables. See Brent Ozar Indexed View
 Data Modifications .

However, we want utilize transactional replication of all tables into a separate ReportingDB on another server to create indexed views. This way indexed view will not have any performance reduction on the actual OLTP.

Example OLTP Tables:

Initial Sales tables,
Purchase order tables,
Shipping table, and
Customer Product feedback/Returns table


texts: Oracle-xe on Ubuntu 18.04 - Installation and impdp

texts:
Setup:

10 GB partition ext3 mounted at /u01
5 GB swap
Oracle-xe oracle-xe-11.2.0-1.0.x86_64.rpm - installed with alien and then dpkg
Followed instructions from https://community.oracle.com/thread/2229554

Problem:

After installation I do /etc/init.d/oracle-xe configure all default values. All went well.

And what comes next?

I have to import db.dmp.

But there isn't any db mounted.

So I tried createdb.sh from /u01/app/oracle/product/11.2.0/xe/config/scripts

It goes well.

Next I execute sqls that creates scheme.

Next I would like to import dump file.
But the impdp \"/ as sysdba\" schemas=DBV remap_schema=DBV:application directory=DATA_PUMP_DIR dumpfile=db.dmp logfile=db-import-$(date +%d-%b-%H_%M).log EXCLUDE=ROLE_GRANT
 reports the following:

UDI-01034: operation generated ORACLE error 1034
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
UDI-00003: all allowable logon attempts failed

When I execute createdb.sh and then startdb.sh impdp reports:

Import: Release 11.2.0.2.0 - Production on So Lut 23 17:55:32 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39006: internal error
ORA-39213: Metadata processing is not available

I can connect to DB via sqldeveloper. The dump file is verified to be working.

My question is, what steps should I take after installation with dkpg to create empty oracle-xe db ready to use and import dumps?

Below I provide outputs for some commands suggested in comments:

$ ps -ef | grep smon
oracle   16930  2196  0 17:04 ?        00:00:00 xe_smon_XE
oracle   18847 14970  0 17:56 pts/0    00:00:00 grep --color=auto smon

$ env | grep ORA
ORACLE_SID=XE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

SQL> exec dbms_metadata_util.load_stylesheets;
BEGIN dbms_metadata_util.load_stylesheets; END;

*
ERROR at line 1:
ORA-31609: error loading file "kualter.xsl" from file system directory
"/u01/app/oracle/product/11.2.0/xe/rdbms/xml/xsl"
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2397
ORA-06512: at line 1

SQL> Select comp_id, comp_name, version from dba_registry ;

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
XDB
Oracle XML Database
11.2.0.2.0

CONTEXT
Oracle Text
11.2.0.2.0

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------

CATALOG
Oracle Database Catalog Views
11.2.0.2.0

CATPROC
Oracle Database Packages and Types

COMP_ID
--------------------------------------------------------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
11.2.0.2.0


texts: How to fix my Merge Replication upload order?

texts: I have several Merge Replication instances in production on SQL 2008 R2.  One of them has started to behave oddly in that it appears to attempting to upload the children tables before it uploads the parent.  This fails due to the foreign key of the children not having a valid parent row to reference.  Eventually the parent row is uploaded and then the children are retried, but often this can take an hour or so of retrying the rows in the children tables before the parent row is uploaded.  How can I track down and fix what is wrong with my merge replication so that it consistently uploads the parent rows before the children?


texts: MySQL on insert in table update field in another table?

texts:
I'm creating one PHP script and I need to update table Topics when someone insert something in table TopicAnswers.

Is there any SQL query to do something like this, for example something as FOREIGN KEY?

UPDATE:

mysql> SHOW CREATE TABLE MintForum.Topics\G
*************************** 1. row ***************************
       Table: Topics
Create Table: CREATE TABLE `topics` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CategoryID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicTitle` varchar(255) NOT NULL,
  `TopicQuestion` longtext NOT NULL,
  `Visits` varchar(255) NOT NULL,
  `Answers` varchar(255) NOT NULL,
  `CreateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TI
MESTAMP,
  PRIMARY KEY (`ID`),
  KEY `CategoryID` (`CategoryID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_3` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_5` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_7` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE MintForum.TopicAnswers\G
*************************** 1. row ***************************
       Table: TopicAnswers
Create Table: CREATE TABLE `topicanswers` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TopicID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicAnswer` longtext NOT NULL,
  `PostDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIME
STAMP,
  PRIMARY KEY (`ID`),
  KEY `TopicID` (`TopicID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topicanswers_ibfk_1` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_3` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_5` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_7` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Insert Query:

INSERT INTO TopicAnswers (TopicID, UserID, TopicAnswer) VALUES (:TopicID, :UserID, :TopicAnswer);

Update Topics Query:

UPDATE Topics SET Answers = Answers + 1;

New Problem:
How I can implement this query:

UPDATE Topics SET Answers = Answers + 1 WHERE ID = TopicID;

instead of:

UPDATE Topics SET Answers = Answers + 1

Where TopicID must be TopicID of just inserted row?

SOLVED!

I used :

UPDATE Topics SET Answers = Answers + 1 WHERE ID = NEW.TopicID;


texts:
Say I have a C# script that creates 50 tables in MYLIB.

Currently, after running the script, I have to go into System i Navigator and manually attach the MYLIB/USRJRN to each of the tables' journaling.

How can I make my script do this instead?


texts: execute [SSISDB].[catalog].[create_execution] with 'execute as"

texts:
We are testing a 3rd party tool to schedule/execute jobs in our environment so trying to test launching SSIS packages within the integration service catalog.   I have found examples of the code to run the procedures to execute the SSIS jobs which works fine but I don’t want to grant the 3rd party’s user account full access so like to wrap a procedure around the SSISDB.catalog procedures necessary to run the SSIS job using ‘execute as’.

On a dev server I created the test proc within the SSISDB DB (will change this to a separate db later, just trying make this work) using ‘execute as’ referencing a sql account but got error saying it must run as an AD account.  I then tried using a full AD admin account on the box as the ‘execute as’ but get errors like:  Msg 27146, Level 16, State 1, Procedure create_execution, Line 180
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

Hoping to find out if someone has done something similar and the steps they have taken as I’m concerned I’ll over complicate this or grant more permissions to accounts than necessary.

I appreciate any help or being pointed in the right direction!

Thanks,
Jeff


texts: How to isolate general Oracle 10g issues through SQL

texts:
Background:

I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in mind. The issue still feels too wide and general to only be caused by bad SQL. It's not really a huge database (the largest couple of tables contains like 8million rows, with less than half a k of data per row) and the server specifications are not that bad either (Ultrasparc, access(they say) to 96 hyperthreads @ 1.2ghz, 2gb sga). We've tried increasing the sga to near the size of the whole db (20gb), seeing only a few percentage is speedgains, which made us willing to rule out I/O issues. The maintenance dept cant see anything wrong HW or OS wise, but arn't too helpful either.

Question:

There are several possible bottlenecks in a DB like this. I list the ones I can think of below. Is there any perticular actions(preferably SQLs) I, with only "a few" DBA permissions, can perform to get measurable values that can help in deciding where my issues are?

Disk I/O speed. How do I get a good measurement right now?
CPU througput. How much data can 1.2ghz shovel?
Number of CPUs, How many are accessible really?
Memory / SGA size. Index swapping etc.
Sorting area size
Temp tablespace (for materializations etc)

Please suggest more categories if you think of something I miss.


texts:
Here is my query .

 SELECT user_record.password,user_addresses.street
 FROM `user_record`
 FULL OUTER JOIN `user_addresses`
     ON user_record.id=user_addresses.user_id
 ORDER by user_record.id

here is the error which is happening when i run my query in Phpmyadmin .

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTER JOIN `user_addresses` ON user_record.id=user_addresses.user_id ORDER by us' at line 1


texts:
We're debating between:

R820 - E5-4620 v2 - 2.6GHz - 8 cores, 20MB Cache, 1333MHz max memory speed - max of 768GB ram - 8 core
R730 - E5-2620 v3 - 2.4GHz - 6 cores, 15MB Cache, 1866MHz max memory speed - max of 768GB ram - 6 core

I understand that we'd see better performance in the R820 for parallel processes, but would the R730's actually be better for SQL Server?  Using a benchmark site it appears that the E5-2620 is faster (86.72 vs 70.30) but rarely is CPU the bottleneck on a SQL Server.

How should we decide?  I am not asking for a product recommendation but what way to choose (SQL Server 2014 / rack mounted).


texts:
I am wondering what is the maximum setting for hash_area_size parameter?
I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour.  10Gb is not that much by today's standards.  I have the RAM available in the system.  Should be possible to process that in memory.  Is there a way to tell oracle to do that?  When I try to set hash_area_size to 10Gb it complains because it seems to be limited to 32bit integers.

I've also tried
alter session set "_smm_max_size" = 10000000; -- 8 zeros

because I read that it is in Kb, but it doesn't seem to make a difference.

UPDATE:
11g Enterprise Edition Release 11.2.0.3.0 - 64bit
The query is a join of 3 wide tables of equal size by 4 columns.  Each column has about 10M rows. No indexes.  The plan is 2 HASH JOINs.  Hash joins are performed in TEMP space.


texts:
I want a query to select 2 rows for every NumberId from the table  that occur without being interrupted by another NumberId, and same for another NumberId and so on

ID      Name         NumberId
---------------------------
1       Abc          1
2       bcd          1
3       cde          1
4       def          2
5       efg          2
6       fgh          2
7       ghij         2
8       defg         1
9       efgj         1
10      fghi         1
11      ghij         1

I want to select 2 rows from NumberId 1  and 2 rows from NumberId 2  and again 2 rows from NumberId 1


texts:
Im trying to to dump an old database system to migrate the data.

# mysql --version
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

The weird part is this:

# mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
#

As you can see, the mysql client does not prompt me for the password, despite me having set -p. Same thing happens with --password. Anything I can do to fix this?
mysqldump behaves in the same way by the way.


texts:
My SQL Server is located in US Mountain Standard Time where the Daylight Savings is not observed. My office is in US Central time zone. All schedules are kept in Central time by business.
I am trying to figure out how to allow business to enter job schedules in a table in Central time and convert those to Mountain, taking Daylight Savings into account.
TIA!


texts: How to best manage a queue of external processes

texts:
My scenario is a web app that creates jobs that need to be processes by an R script. We are currently have the web app execute a SQL stored proc that executes the R script. However, we quickly realized this is unworkable. The first job eats a lot of memory up and then all subsequent jobs fail. We did set aside 40% of the servers memory for external processes, as described in MSFT's docs here:

https://docs.microsoft.com/en-us/sql/advanced-analytics/administration/how-to-create-a-resource-pool?view=sql-server-2017

I appreciate any advice or recommendations you may have.


texts: How to get same column values to make multiple columns?

texts:
I don't know if my question is valid or not but I'm working on an attendance system, and I want to get some output for my project.

I have an attendance table that holds three columns: emp_id, atten_type and date. The atten_type column can contain one of three values; that is 'present', 'absent' and 'leave'.

Now, I want the output like this:


texts: What is the permission needed to add permissions?

texts:
I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.

So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.

Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.

BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.


texts: Design for tables with the same column value but different names

texts:
I have the following three tables:

doctors (Doc_id, Doc_name)
patient (patient_id, patient_name)
admission (patient_id, sup_Doc, date)

What is the correct way to go about linking the doctor and admission tables, given that sup_Doc is just a Doc_id?

Do I change the name of the sup_doc or just link them as is?

I believe this is a theory question.


texts: Oracle 12c - Opening cursor with remote tables over db link

texts:
We just upgraded from 11g SE to 12c SE.   Please note, this process worked perfectly under 11g, and only started failing once under 12c.
The procedure puts together a query, and then opens up a REF Cursor to return the data.   The query has remote tables which are accessed via DB Link.   If I take that query by itself, and paste it into SQL Plus, it works fine.
But, if I run the procedure, and try to print the REF Cursor, it just hangs, forever.
What changed between 11g & 12c to make this start failing?


texts: Do I need to reindex my table in mysql after delete?

texts:
I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as engine. Do I need to change it? I think its better for concurrent insert and select. pls give your suggestions. Do I need reindexing?

Thanks in advance..


texts: Total Memory capped far below available memory

texts:
We are testing out a new install of SQL Server 2012 on a virtual server and the memory allocated to SQL Server seems to be capped around 16 GB even though much more is available. What could cause this?

I've tried bumping up the SQL minimum server memory setting, but the change had no effect. I've listed some of the server details below.

SQL Server 2012 Enterprise
Windows Server 2008 R2 Enterprise x64
29.93 GHz (8 processors)
77 GB RAM
Min Server Memory: 48 GB
Max Server Memory: 56 GB
Database size: 44 GB

PerfMon info:

Working Set: ~17 GB
Page life expectency: ~7,500
Target Server Memory: ~73 GB
Total Server Memory: ~15.7 GB


texts: How we take dump with batch of records option?

texts:
we have 30 GB database.we want to take backup of this database.

here the question is we want to take the backup batch of every 10k records.

How we can take batch of records backup using mysqldump


texts: mongodb wont read config file using upstart

texts:
The problem started where mongo wouldn't start because of low disk space. So I enabled the small files directive in the config file. Now it starts if I run:

/usr/bin/mongod --config /etc/mongod.conf

and it starts

service mongod start

and it fails from the same error about disk space, leading me to believe that it's just not reading the config file.

i added an echo statement to the upstart script and i see this:

Starting database: mongodstart-stop-daemon --background --start --quiet --pidfile /var/run/mongod.pid --make-pidfile --chuid mongodb --exec  /usr/bin/mongod  -- --config /etc/mongod.conf

Config file: https://gist.github.com/macdabby/3ee375be72ab3a3f11a7

Upstart script: https://gist.github.com/macdabby/192891a1ae690d4ec57c

Debian wheezy and mongodb 3.0.1

thanks in advance!


texts: MS SQL Server 2017 for Linux - Installing to non default paths

texts:
I want to install SQL Server 2017 for Linux on RHEl7.4.. but I want to use LVM to create a series of volumes for each of the important volumes.

/mssql/EBSData
/mssql/EBSLogs
blah blah blah

Is there a way to do this without mounting the volumes, symlinking the paths, then installing?

I'd assumed the installer might have had additional args/flags to change the default installation path, but doesn't seem too.


texts: What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool?

texts:
We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance.  The basic resources for that instance type are:

7.5gb RAM
2 VCPU

RDS defaults to a calculation that allocates 75% of RAM to innodb_buffer_pool_size.

{DBInstanceClassMemory*3/4}

This ends up being a 6gb allocation:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           6.000000000000 |
+------------------------------------------+

With MySQL 5.7 if you do not set the number of buffer pool instances, then MySQL now defaults to 8 pools.  However the MySQL 5.7 manual states this when discussing buffer pool instances:

  The total size you specify is divided among all the buffer pools. For
  best efficiency, specify a combination of innodb_buffer_pool_instances
  and innodb_buffer_pool_size so that each buffer pool instance is at
  least 1GB.

For this reason, I plan on reducing the innodb_buffer_pool_instances to a number <= 6.

Here are my questions:

Is there some correlation to be made between the number of CPU's and
the buffer_pool_instances setting?
Would it be better in this scenario to go with 4,5, or 6
buffer_pool_instances, and if so, what is the reasoning behind that
recommendation?  My simplistic response would be to change the setting to 6 buffer pool instances to satisfy the "at least 1GB" recommendation, but I would be interested to hear from anyone who has looked into this further, or done benchmarking of this setting whether that be under RDS or not.


texts: How to securely connect app and database servers?

texts:
(Updated) For a start, I have my app and database on separate servers. It's easy to connect them, except I am not sure how to secure my database server.

Here's what I've already done:

Ran mysql_secure_installation right after installing MySQL. So, all of these have been taken care of:

strong 64 char root password
no anonymous users
root login only on localhost
no test databases

A non-public network for the inter-server communication (in my my.cnf, there's something like this: bind-address = 10.128.2.18, where 10.128.2.18 is the private network IP address of the MySQL database server).
A separate user for the database, an unguessable username and 64 char strong password to go with it; and the ip addresses of the user accounts set to the private IP addresses of the app server. I created the user with command like this:

GRANT ALL ON `app_db`.* TO 'db_user'@'10.128.2.9' IDENTIFIED BY 'password';

The app is wordpress, so I need GRANT ALL ON to avoid any unexpected issues.

Options considered (but not employed):

I've been told that technologies like SSH Tunnel, SSL, OpenVPN, Tinc, and IPsec are not generally used because they have a performance cost (resource usage due to encryption, latency, etc).

So, whatelse do I need, or am I good enough? How else do others do it? Please be as detailed as possible (link to a tutorial or something you are suggesting would help a lot).


texts:
I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well.

Maybe someone with a deeper knowledge could explain to me what the difference is between these two?

OUTER APPLY (SELECT UserID
                , ActionPerformedDate = MAX(ActionDate)
            FROM dbo.AdminUsage
            WHERE SubscriberID = S.SubscriberID
                AND ActionPerformed = 'Some Action'
                AND Description = 'True'
            GROUP BY UserID) AS AU

LEFT JOIN (SELECT SubscriberID
                , UserID
                , MAX(ActionDate) OVER(PARTITION BY SubscriberID) AS ActionPerformedDate
            FROM dbo.AdminUsage
            WHERE ActionPerformed = 'Some Action'
                AND Description = 'True') AS AU
    ON AU.SubscriberID = S.SubscriberID

Is there a preferred approach in this case?


texts: Safe to delete old data after pg_upgrade?

texts: I am not a DBA but I am responsible for a modest pg installation. Recently as part of a larger program of system upgrades I upgraded from 9.5 to 10. Everything went great, except that our monitoring system is now regularly sending warnings about disk space. I see that there is still a big chunk of space taken up by /var/lib/postgresql/9.5/ -- is there any good reason not to delete all that? What if any additional steps should I take to verify that it's safe to delete the contents of that directory?


texts: Queries using GIN index on f_unaccent() seem slow?

texts:
I have almost 20M rows in one table that contains product, including their names.

I wish to search fulltext and fast by the name so I created this index:

CREATE INDEX uprice_item_occurrence_unaccent_name_trgm_idx ON price_item_occurrence USING gin (f_unaccent(name) gin_trgm_ops);;

I was hopping the following query would take less then (lets say) 500ms

select * from price_item_occurrence as oo
where f_unaccent(oo.name) % f_unaccent('iphone');

But it takes almost 2s:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone');
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=1956.42..63674.14 rows=16570 width=287) (actual time=247.918..1880.759 rows=94 loops=1)
   Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
   Rows Removed by Index Recheck: 87838
   Heap Blocks: exact=76663
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..1952.28 rows=16570 width=0) (actual time=195.418..195.418 rows=88962 loops=1)
         Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.444 ms
 Execution time: 1880.833 ms

It's possible that the DB is busy, but i am not sure.

I tried to play with select set_limit(0.9); (increased) and it helps a bit but not much.

I am using Postgres 10, can change the Postgres config and I am open for suggestions.

I tried it with ilike and it improved by some:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) ilike ('%' || f_unaccent('iphone') || '%');
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=3135.08..416823.45 rows=166075 width=286) (actual time=50.258..670.085 rows=65917 loops=1)
   Recheck Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
   Rows Removed by Index Recheck: 10
   Heap Blocks: exact=59750
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..3093.56 rows=166075 width=0) (actual time=37.385..37.385 rows=67700 loops=1)
         Index Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
 Planning time: 0.545 ms
 Execution time: 675.776 ms
(8 rows)

About 2x faster.

i tried limit 10:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone') limit 10;
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=373.27..410.51 rows=10 width=287) (actual time=268.718..589.131 rows=10 loops=1)
   ->  Bitmap Heap Scan on price_item_occurrence oo  (cost=373.27..62493.45 rows=16680 width=287) (actual time=268.715..589.123 rows=10 loops=1)
         Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
         Rows Removed by Index Recheck: 18917
         Heap Blocks: exact=17100
         ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..369.10 rows=16680 width=0) (actual time=165.958..165.958 rows=69268 loops=1)
               Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.397 ms
 Execution time: 589.187 ms
(9 rows)

this is also faster, maybe almost good enough


texts: How to setup transaction in partitioned table with Always On?

texts:
I am currently creating a project that will use a table that will contain around 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second are to be inserted into the table, which gives a daily volume of around 21,600,000 records. We have to keep the last 14 days in the table, so there will be about 300,000,000 records in total. We also use the "Always On" solution (Aviability mode = Synchronous commit). Every day in the evening, records older than 14 days will be deleted. Next to it, I also have a partitioned service table (in the same way as the main table) for which the PK is the same as the main table. Obsolete records (in terms of business) are removed from the service table. Therefore, there will be a lot of DELETE operations on the service table and it will contain at a given moment from 0 to several thousand records.
A certain procedure requires an UPDATE operation of one value in the main table and a Delete record (DELETE operation) with the same PK in the service table. Both of these operations should be in the transaction.
I wonder which approach will be better:

Is it better to create a procedure that will collect a list of overdue records by a SELECT query (here can be from 0 to even 1000 records) and in the transaction do the above operation. The procedure would be run e.g. once a minute in the job.
Or is it better to create a procedure that will check if a given record is out of date during processing, and perform the above operation for a single record in a transaction.

Thank you in advance for your answer


texts: Opening MySQL connection is sometimes very slow

texts:
I have a very weird issue with an older MySQL server (v5.0.45) and a Java application.

From time to time getting a connection from the database takes 7 seconds, 15 seconds, 30 seconds etc (most of them are 15 seconds) or sometimes times out. This happens just from time to time. I don't see a pattern when it does happen. The rest of the time connections are created in 2-3 milliseconds.

The database is running on Linux CentOS 6.4. The servers that have issues while connecting are on Linux CentOS 7. I'm using the latest MySQL connector (currently at v5.1.38), Java 8, with a connection pool (be it that of Tomcat or c3p0) or with no connection pool (just opening and closing connections), server in load or with few clients, a few simultaneous connections or up to 100 connections. It's the same result.

Multiple applications on multiple servers connect to this database. Only on two of them this occurs, the rest have no issues, the network seems fine, the other machines are in the same network.

QUESTIONS

How can I debug this issue and find the cause?
Has anyone seen this behaviour before?

EDIT: We've norrowed down the issue to the actual servers. Don't know if it's a hardware problem or an OS/kernel/drivers one. Still investigating.


texts: Should I drop dbo from the database role db_owner?

texts:
I did a Vulnerability Assessment for my database today. I found VA2108 and it suggests me to
ALTER ROLE [db_owner] DROP MEMBER [dbo]

But it's the default setting of any SQL instance.
Should I go for it? And is there any risk?

Thanks


texts: How to store schema-less data efficiently in a relational database?

texts:
I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.

Option 1:
Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.

   id  |  name   | meta
1       Bob       {"city":"GoTown","birthdate":"1980\/8\/14","cat":"Felix"}
2       Alice     {"city":"Streamville","birthdate":"1986\/6\/6","dog":"Rex"}

Option 2:
Store keys and values together in a stack.

user_id  |    key   |   value
1         name       Bob
1         city       GoTown
1         birthdate  1980/8/14
1         cat        Felix
2         name       Alice
2         city       Streamville
2         birthdate  1986/6/6
2         dog        Rex

Option 3:
3 tables: entites, keys, values. Store each key name only once.

user_id  |   name
1         Bob
2         Alice

key_id   |   keyname
1         city
2         birthdate
3         cat
4         dog

user_id   |   key_id   |   value
1          1            GoTown
1          2            1980/8/14
1          3            Felix
2          1            Streamville
2          2            1986/6/6
2          4            Rex

Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.

EDIT: Added some data to represent schema-less data


texts: make MariaDB Auto-Increment-Increment dynamic

texts:
In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 and the auto_increment_offset accordingly. When I go over the 30 masters, this system is going to crash, so I would like the following:

The Auto-increment value is created by using the following calculation:

auto_increment_offset + N × auto_increment_increment

Is it possible to make the auto-increment-increment dynamic like this:

(SELECT COUNT(id) FROM Clients) + N x (SELECT COUNT(id) FROM Clients)

When I add a client to the table, the other clients will automatically be upping the increment.

to be on the safe side I probably should add a buffer:

(SELECT COUNT(id) FROM Clients) + N x ((SELECT COUNT(id) FROM Clients) + 5)

Can anyone tell me if this could be done, or if there is another way to accomplish this?


texts: SQL server AlwaysOn database not automatically seeding, cannot find logs with reason

texts:
I've added a database to an availability group that already has a few databases, all of which seeded and syncing fine to the secondary.

This last one gets added to the availability group, it would seem, but is marked as "not synchronizing" and no errors are returned.  I've tried it multiple times with the same result.

I've looked in extended events, Event Viewer... nothing.  Clicking on "warnings" in the dashboard says nothing useful.  The database files do not exist on the secondary at all.  I suspect that it was blocked right at the start of its seeding process (but clearly after the initial validations in the "add to AG" wizard).

I have this distinct feeling SQL server must be logging a relevant failure message to some DMV I'm not aware of.

Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?


texts: how to get weekly unbound retention from bigquery?

texts:
I Need to get the weekly retention users.
If user has made the transaction in week 4, that user is there in Week 0, week 1, week 2, week 3, week 4.
If User made transaction in week 0, and week 3 then user will be there in Week 0, week 1, week 2, week 3
Thanks @bbaird,
I am updating the question with precise information and question
Here is my table data

TxnId
userid
transactedWeek

T1
U1
0

T2
U2
0

T3
U3
0

T4
U4
0

T5
U1
0

T6
U2
0

T7
U6
0

T8
U1
1

T9
U8
1

T10
U12
1

T11
U3
1

T12
U5
1

T13
U9
2

T14
U15
2

T15
U20
3

T16
U15
3

T17
U15
3

T18
U8
3

T19
U9
4

T20
U20
5

The Result I am looking for

Week
active_users_count

0
11

1
8

2
4

3
4

4
2

5
1

Users count is kinda cumulative but unique users.
For example if user-1 has performed transaction 3 times in week-0, then count for week-0 is 1
If User-1 has performed 3 transactions in week-0, 2 transaction in week-2, 1 transaction in week-3,
then count will be like this
week-0 -> 1  (i.e. he has performed transaction >= week 0)
week-1 -> 1  (i.e. he has performed transaction >= week 1)
week-2 -> 1  (i.e. he has performed transaction >= week 2)
week-3 -> 1  (i.e. he has performed transaction >= week 3)
There can be any number of weeks. It is not fixed to 4 weeks or 8 weeks.


texts: clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

texts:
After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.

Below is the actual command:

clusterdb -v -h localhost -p <port#> -U postgres -d <dbname>

And this is the response that I'm getting:

clusterdb: could not connect to database <dbname>: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port <port#>?

I'm able to connect to the server locally and from outside. I can also use another utilities  such as pg_dump with no problems.

My pg_hba.conf file below:

# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the replication privilege.
local all postgres md5
host all all x.x.x.x/x md5         #my network
host    all all  x.x.x.x/x  md5    #internal ip of the server

Are there any security changes were implemented in PostgreSQL 9.2 vs 9.1 that may prevent clusterdb to work properly?


texts:
I have a question

Find the titles of all movies not reviewed by Chris Jackson

Where the relations are 3
movie(mID, title, year, director),
reviewer(rID, name),
rating(rID, mID, stars, ratingDate)

I have tried this query so far. Any idea of how to improve it or what am I doing wrong?

SELECT DISTINCT M.TITLE
FROM MOVIE AS M
JOIN RATING AS R ON R.mID = M.mID
WHERE R.rID NOT IN (SELECT Rating.rID FROM Rating JOIN Reviewer ON Reviewer.rID = Rating.rID WHERE Reviewer.name = 'Chris Jackson');


texts: Delete duplicate records and update the mapping table

texts:
I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map  and retain the latest row based on updated_date because i want to put constraints in store_id and item_id. But I also want first to update the item_price_map as seen below before deleting old duplicate records.
Current Table

Expected Result

Here's the script
CREATE TABLE store_table_map (
    map_id bigserial NOT NULL,
    store_id int8 NOT NULL,
    item_id int8 NOT NULL,
    updated_date date NOT NULL DEFAULT now()
);

INSERT INTO store_table_map (store_id,item_id,updated_date) VALUES
(1,123,'2020-07-09')
,(1,123,'2020-07-10')
,(2,456,'2020-07-08')
,(2,456,'2020-07-10')
;

CREATE TABLE item_price_table_map (
    map_id bigserial NOT NULL,
    store_table_map_id int8 NOT NULL,
    price float8 NOT NULL,
    updated_date date NOT NULL DEFAULT now()
);

INSERT INTO item_price_table_map (store_table_map_id,price,updated_date) VALUES
(1,99.0,'2020-07-09')
,(2,199.0,'2020-07-10')
,(3,299.0,'2020-07-08')
,(4,399.0,'2020-07-10')
;

postgres version: PostgreSQL 9.2.24


texts: Why is it specifically Immediate If (IIF)?

texts:
I am quite new to MS Access (and databases in generally) but I have noticed that IF() statements are actually written using the IIF() function.

A quick search online told me that it stands for Immediate If, I assume because it is evaluated immediately.

Support.Office.com tells me that IIF() has the same functionality I am used to from IF() in other programs and languages, so my question is why is it IIF() and not IF()?

I am unable to find this answer anywhere.

Is there a "delayed" IF (Or any other kind of IF) that justifies the need for Immediate IF? If so, what are the possible use cases for the other if?

If there is not a different IF, why is the IIF() function not just called IF()?


texts: Improving row estimates for query with jsonb_object_keys()

texts:
I have a query that looks something like this:
SELECT jsonb_object_keys(json) AS key,
       json
FROM (
       SELECT json_object -> 'some_key' AS json
       FROM my_json_table
     ) j;

The planner currently estimates 100 rows to be returned by the jsonb_object_keys call for each from the inner query. In my case, it's 2. Since this underlies some larger queries in my database, it's critical that I get a better row estimate.
I would like to avoid creating a materialized view. Is there another approach to improve the query plan?


texts: In the domain of SQL Server backup, what is a backup set, backup device, backup media, backup media set, backup media family

texts:
I'm not a native English speaker and I've read the descriptions in BOL but still can't understand what exactly are they. Can anyone explain in an easy understand way?

Thanks.


texts: Combining multiple rows into single column

texts:
The return from my MySQL statement displays an example below:

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person
001  | open   | 011  | john | male

How would I combine the multiple rows to the category column? Like the example below.

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person, male


texts: Hallengreen's Index maintenance query not using all indexes

texts:
I'm using Hallengreen's Index maintenance To reorganize/rebuild indexes on my databases.
The problem is, when I execute it:
EXECUTE dbo.IndexOptimize
@Databases = 'MyDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

It only finds 2 or 3 indexes (On Messages Window). And I have a lot of them with more than 90% fragmentation:

Is there some information I'm missing?
Maybe there are not enough pages, so there's no performance impact, and then the script just skips the index ( the first index has 240 pages)?


texts: Sql Server 2005 - sqlcmd with output to shell and file

texts:
I do some command line batch (.bat) with sqlcmd as this way:

sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE

and i need an output file (it's works currently) and also the output trought the screen to do something like:

@echo off
echo The result of the query was:
    sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE
pause
CHOICE /C:YN /M "Is the result accord?"
IF ERRORLEVEL 2 GOTO ENDWITHERROR
IF ERRORLEVEL 1 GOTO STEP2

Note:

Yes, the script works suscefull, it's not an issue question.
And yes, I've a "print "something" on my sql. The log file gets the
output.

Thanks a lot!


texts: How to avoid log space error while executing SSIS packages?

texts:
I'm executing ssis package with more than 70 million data's. But I'm facing issue as
log space full error message while executing the package.

Is there any option to resolve this issue?


texts: Should I disable MongoDB balancer to prevent moveChunk to block database due to "W" lock

texts:
In my team, we have a MongoDB 3.6 with 4 shard cluster.
My teammate built up Ops Manager and we found out at some point the queries are slow and they are all waiting for database level lock.
    "Database": {
      "acquireCount": {
        "w": 1
      },
      "acquireWaitCount": {
        "w": 1
      },
      "timeAcquiringMicros": {
        "w": 5383162
      }
    },

Dig inside and we found out the MongoDB was moving chunk and command require "W" exclusive lock at database level.
We feel quite confusing because

We use hashed _id for shard key, which should be distributed evenly and every document size are quite the same.  Is there any way to find out why MongoDB trigger moveChunk? Or any statistics for me to dig inside ?
List all the slow query, Ops Manager didn't record the moveChunk command as slow query. How should I change my config or anything I should try ?
Based on my case, should I try to disable balancer to prevent moveChunk and observe by myself ?

[Updated].
Here is the query which is lock on database "W" lock.
{
            "host" : "....",
            "desc" : "migrateThread",
            "active" : true,
            "currentOpTime" : "2020-08-31T06:34:34.194+0000",
            "opid" : "sh1:-472303473",
            "secs_running" : 15,
            "microsecs_running" : 15562026,
            "op" : "none",
            "ns" : "collection",
            "command" : {
            },
            "msg" : "step 2 of 6",
            "numYields" : 0.0,
            "locks" : {
            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : 4,
                        "w" : 4
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "w" : 3,
                        "W" : 1
                    },
                    "acquireWaitCount" : {
                        "W" : 1
                    },
                    "timeAcquiringMicros" : {
                        "W" : 15665
                    }
                },
                "Collection" : {
                    "acquireCount" : {
                        "w" : 2,
                        "W" : 1
                    },
                    "acquireWaitCount" : {
                        "W" : 1
                    },
                    "timeAcquiringMicros" : {
                        "W" : 7247
                    }
                }
            }
        }

Thanks


texts:
I am trying to assign a SQL Server database role to a user who just needs to be able to see data across all tables.
I was wondering what is the difference between db_datareader and db_denydatawriter?
From the documentation I don't seem to realize any difference.

Thanks,


texts: Using Percentile_Disc to compute Median/Interquartiles

texts:
SQL Server Developer Edition 2014

I have a table that looks like:

+ ID + CC +  Xa  +
| 1  | 0  | 53.1 |
| 1  | 1  | 46.1 |
| 1  | 2  | 107.5|
| 2  | 0  | 36.5 |
| 2  | 1  | 41.5 |
| 2  | 2  | 61.8 |

Xa are floats in the table, but I rounded to make it less messy. I had heard of PERCENTILE_DISC from MSDN, and made a query that I thought would give me the median of Xa for each ID:

SELECT DISTINCT s.ExperimentID,
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY s.ExperimentID)
FROM Simulations s

I threw in the DISTINCT ExperimentID because when I ran the query without it, it was looking to return the median of all ~150M rows for each individual row, which doesn't really make any sense. I did get results, and I think they're right, but I'm not certain. I compared the results of some individual IDs vs what I got from MEDIAN() in LibreOffice, and the results are slightly different. Any idea why?


texts: SQL Server unpredictable select results (dbms error?)

texts:
Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error?

After some investigation we reduced the search area to union clause in subquery, which selects one record from "men" table

It works as expected in SQL Server 2000 (returns 12 rows), but in 2008 and 2012 it returns only one row.

create table dual (dummy int)

insert into dual values (0)

create table men (
man_id int,
wife_id int )

-- there are 12 men, 6 married
insert into men values (1, 1)
insert into men values (2, 2)
insert into men values (3, null)
insert into men values (4, null)
insert into men values (5, null)
insert into men values (6, 3)
insert into men values (7, 5)
insert into men values (8, 7)
insert into men values (9, null)
insert into men values (10, null)
insert into men values (11, null)
insert into men values (12, 9)

This returns only one row:   1  1   2

select
man_id,
wife_id,
(select count( * ) from
    (select dummy from dual
     union select men.wife_id  ) family_members
) as family_size
from men
--where wife_id = 2 -- uncomment me and try again

Uncomment last line and it gives: 2 2   2

There is a lot of odd behaviors:

After series of drops, creates, truncates and inserts on "men" table it sometimes works (returns 12 rows)
When you change "union select men.wife_id" to "union all select men.wife_id" or "union select isnull(men.wife_id, null)" (!!!) it returns 12 rows (as expected).
The strange behavior seems to be unrelated to datatype of column "wife_id". We observed it on development system with much greater data sets.
"where wife_id > 0" returns 6 rows
we also observes strange behavior of views with this kind of statements. SELECT * returns subset of rows, SELECT TOP 1000 returns all


texts:
I'm working on a database that stores address information. Each address has a Street, Number, Bus (as in the apartment they live in) and CityCode parameter. The CityCode parameter is a FK to the City table which has the postal code/zip code as PK but I think this is not important for this question.

I'm not sure what to do with the Address table. A Person will have an Address so these 2 tables will have a relationship. A combination of a Street, Number and Bus is as far as I know a unique entry in a database leading me to think that Street, Number and Bus could be a PK consistent of 3 columns.

On the other hand I would have to add 3 extra columns instead of 1 into my Person table to relate to the Address of the Person.

I've done some looking around and what I found is that PK's should be as short as possible for good performance. On the other hand, having just an auto-increment Id as PK makes it harder to view the raw data without joining tables.

What should I do in this case?


texts: How can I view real-time locks in innodb when information_schema.innodb_locks is not available?


texts: How to identify what is causing unusual high CPU load in MySQL?

texts:
I have a MySQL 5.7 server that is since recently under very high CPU load, but no clue where this load comes from.

Looking at the MySQL processlist, it does not look like the server is doing much. There are some connections, but nothing heavy or special that could explain the high CPU.

In efforts to improve the situation I have been doing some data cleanup and applying some advice given by the mysqltuner script, but nothing seams to help much.

My last thought are that maybe something is damaged (some tables?) and MySQL is busy in the background, but I dunno if this makes any sense...

So my question is really, how to go about this? Looking at the processlist like everyone is saying, is not giving me the answers I need. Something inside MySQL is definately eating CPU behind the scenes.

PS: This server is quite powerful and there is plenty of RAM and CPU cores, so this is certainly not a hardware limit.


texts: MySQL: How to decrease sleep process's time out?

texts:
When I run 'show processlist;', then I get so many sleep processes.

I set wait_timeout and interactive_timeout to 60 in my.cnf. But, sleep process is not died when time 60 on processlist. I found that sleep process is died when time is 7900. What happend?? How can I decrease sleep process's time out?

If you need other information, I will give information as I possible.

UPDATE

I use root, read_only and deploy account on mysql. And I found root and read_only accounts run correct. Query -> Sleep -(60sec)-> release process.

But deploy account, using to connect between rails server and mysql, run wrong. Query -> Sleep -(7900sec)-> release. So, I think that deploy account or Rails is problem. However, I dont have any idea to fix this.


texts: Benefits of making a sql server database as read-only?

texts:
I am using a reporting database, which for all practical purposes is a read-only database. It is created data and dynamic reports are generated and viewed on this database.

I was thinking of making this database read-only.

Some of things I want to ask regarding read-only databases are

Is only the data read-only? Can we still create indexes or views on this database?
What are the performance benefits of using a read-only database? e.g. SELECT queries using shared locks, etc...
Is there any disadvantage of using a read-only database?
Are there any best practices to follow, while using read-only databases?


texts:
I was clearing tables by using query in SQL Server. A query that normally takes 2 minutes took the better part of 3 hours... and it wasn’t even close to being complete. Therefore, I killed SQL Server in task manager because I felt like I had no other choice.

Upon logging back in, the database has been (in Recovery). According to various posts I read online, simply detaching it and reattaching it should work fine however, when I try to run queries to detach it I get the following error:

Alter database failed because a lock couldn’t be obtained for database <sel>

I also read that this was due to a connection not being terminated but everything I tried gives that same lock error. I made sure I was in master, I reset the PC, un-plugged network table, tried the commands in the GUI. Nothing is letting me detach said database or remove it from In recovery.

What can I do?


texts: Cassandra multi data centre cluster configuration

texts:
We are decided to use "cassandra" as a database and now want to go with cluster with two datacenters DC1 & DC2. This DC2 is for backup purpose and it is replica of DC1.
Initially we are expecting the data size going to save into db is about 2 Terabyte and this size grows eventually.

As I am new to cassandra, I need some help here in getting the estimate for server/node configurations for this cluster like

how many nodes are enough handle this data. Its' cpu configurations like RAM, ROM and number of cpu cores

It would be grateful if someone can help me with atleast rough estimations.

Note : We are going with opensource cassandra of 3.11 version


texts: Copy last modified files from network path into SQL server box and rename them using Agent job

texts:
I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job.

This is what i am doing.

Created a SQL server agent job

1) XCOPY source *.bak /Y destination < This copies all the files, but I need the latest file >
2) Ren *bak mydbbackup.bak <**added this into step 2 of job **>

Questions:

What mistake am i doing here ?

*Are there any xcopy **/**commands which can copy the latest files only and ignore old files ?

Is there a way i can do that using another SQLCMD command besides creating a stored procedure*

Thanks,
learner


texts: Upgrading from 10.2g to 11g by incrementals

texts:
I know it's possible to generate a full rman backup in a 10.2g database, move it to another server containing 11g, restore it there and finally do an upgrade.

Since I have to do this to a large database and there are several things involved (apps, servers, network, etc), it could take me a few days and not only one night.

Could I make a full rman backup in 10.2g, restore it in 11g, upgrade, and in the next days go making incremental rman backups in 10g and restoring it in 11g, until we decide to shutdown 10g and use 11g in production. Something like a standby database, but in this case current database would be 10g and standby database would be 11g.


texts: Attributes from an hstore-column as separate columns in a view?

texts:
Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs.

You could query that column as such:

SELECT name, attributes->'device' as device
FROM products
WHERE attributes->'edition'= 'ebook'

Can you create a view for this table where the keys/values appear as columns in the view instead of all in the hstore column?

Example: I have an hstore key where it might have the key of amenity.  I want to create a view showing the the ID and the amenity values in a new view.


texts: MS SQL 2017 Batch insert performance degradation

texts:
This one's a bit strange so bear with me:

I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection was not closed properly).

Pre incident, insert performance was roughly 100m rows/hour with 500k batches (about 20 seconds/batch).

Post incident, batch inserts with more than ~80k rows will never complete (left it running for more than a day) whereas an insert with 20k rows completes in ~95 seconds but that results in an insert performance of only around 18m rows/hour. The machine the SQL server is installed on does not experience any load. As far as I can tell the server is idling.

What trips me up about this is that dropping the database or even reinstalling the server instance does not resolve the issue. I simply can't reproduce the performance I was seeing pre incident. On top of that, I had observed this exact phenomenon with Azure SQL instances, except that dropping the database "solved" the issue there.

The way this issue presents itself indicates that the problem is not actually with the SQL server but rather on the insert side / the driver. However I've tested basically all possible ways to connect to a MS SQL instance from python with the same result.

The SQL Server 2017 instance is updated and running on a fairly powerful dedicated machine with NVMe drives located in the same datacenter as the client machine.

The inserts are executed using:

ubuntu 16.04
Python 2.7
pyodbc 4.0.23 (also tested pymssql+freetds and pypyodbc)
Microsoft ODBC driver 17.2.0.1


texts: Using group by on several tables with problems

texts:
I have a poorly-designed table in an Oracle database like this:

sr:
id  an_rap
21  2015

rap:
id      cf
28185   123-123

ch:
id      fk_id_rap   FK_ID_SR
12348   28185       21

dma:
ch_id   FK_AMB_ST
12348   18059

cant:
id      FK_ID_MUN   CANT_VAL    COD_VALORIFICARE
18059   12348       18.56       R12

col:
FK_ID_COL_DMA   CANT_VAL    COD_VALORIFICARE
18059           1134        R10
18059           1234        R3

if I made an:

select
    SR.AN_RAP as AN, RAP.CF,
    max(CANT.CANT_VAL) as CANT_VAL,
    max(CANT.COD_VALORIFICARE) as COD_VALORIFICARE,
    max(COL.CANT_VAL) as kk0,
    max(COL.COD_VALORIFICARE) as kk1
from dma
    inner join ch on ch.id=DMA.CH_ID
    inner join cant on CANT.FK_ID_MUN=ch.id
    full outer join col on cant.id=COL.FK_ID_COL_DMA
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
where RAP.CF='123-123'
group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
    CANT.COD_VALORIFICARE,COL.COD_VALORIFICARE
order by sr.an_rap desc,rap.cf,cant.id

the result is:

an      cf          CANT_VAL    COD_VALORIFICARE    kk0     kk1
2015    123-123     18.56       R12                 1134    R10
2015    123-123     18.56       R12                 1234    R3

how can I make something like this - without changing the structure of the database???

an      cf          CANT_VAL    COD_VALORIFICARE
2015    123-123     18.56       R12
2015    123-123     1134        R10
2015    123-123     1234        R3

Thanks!


texts: Which MySQL directories to back up using FTP

texts:
My server has been hacked and I only have FTP access to recover my data before reinstalling the OS.

Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its initial state if I were to copy these directories back to the freshly installed server?


texts:
I have this code :

Select name, age , row_number() over (partition by name order by age desc) rn from my table

(goal : select the oldest guy among same names)

If I want to select all the row numbered records which is 1 ,I guess I must do :

Select ... from ( above query) where rn=1

But this  force me to wrap it with another select.

Is there any other solution instead of wrapping in select ?  (cte is also like wrapping)