texts: High number of page reads and PLE counter, but only when tracing with Profiler

texts:
A newly developed and deployed functionality on our production server started running slowly. Although prior tested in a development environment, it was not tested at a workload scale similar to prod.
Anyway, the issue I'm facing is that a stored procedure runs in about 13-14 seconds, with a high number of page reads being generated by a single query, which also takes an average of 11 seconds.
While tracing this query in prod, with Profiler, I captured that it does about 975.000 reads and takes about 11.7 CPU time, 258 writes.
While testing this same query on a different reporting database, which has 99% of the same data as prod, the query runs in less than 1 second, with a total of about 14.000 page reads (stats taken with SET STATISTICS IO ON).
After this I took my chances and tested this problematic query in prod, to see if I can replicate the 900K page reads there.
But, I couldn't, although I ran the stored procedure with the same parameters as the original query that generated 900K reads, I could only get the same number of 14.000 page reads (using STATISTICS IO) as in the reporting database.
Monitoring this query over time I could see that it didn't always generated the same number of page reads / sec and that occasionally it generated some "spikes" in number of pages read. Below is a graph of how the # of pages read (metric taken from Profiler trace) evolves through time.

Because of this, I thought of BCHR (buffer cache hit ratio) and PLE, assuming that because of this behaviour my BCHR and/or PLE counters would be very low, as I thought that the pages the query reads are taken out of cache frequently, thus having to be read again from disk (which generated the high number of page reads / spikes).
Taking this to the monitoring tool, SQL Spotlight, to confirm my hypothesis I noticed that the PLE counter is 1d 01:03:44. Which seems to be an amazing feat, but I'm not sure this is real.
Looking at other metrics, during the highest load time of the system, the number of logical pages read per second was very high (in my opinion, although I don't know if this is normal):

~500.000 logical page reads/sec
~750 physical page reads/sec

So, I'm confused at this point, what could generate a very high number of page reads, periodically, but not have an effect on PLE.
Any suggestions on what I can try next? Or how can I identify the cause of why the query is running much better from SSMS directly in Prod than the metrics captured by Profiler?
Update:
Pasted the execution plans of both queries:
Production:

Reporting:

UPDATE (based on @PaulWhite's suggestions):
PROD Paste the plan LINK
REPORTING Paste the plan LINK


texts: what will happen if MongoDB primary host is down and secondary is out of sync. will we lose transaction?

texts: In our environment, we have one primary(A) and two secondaries(B & C nodes) running in Mongodb 4.0 version. secondary(C) server is newly added and sync is happening on that. Cluster is running only with A and B servers. Now, the Primary server(A) was abruptly down due to some host issue and it was not responding and we could also see that secondary server(B) was out of sync for 10 minutes. we were able to bring up only "B" as new primary. what will happen to the transactions that were completed in old primary (A) and that was not synced with secondary(B).? Please answer.


texts: Best way to work around NOT IN NULL issue?

texts:
There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?)

I have 6 optional arguments to a stored procedure that are used to filter a certain value of a query. The previous developer did something like this:

IF @var1 IS NULL AND .....

    select ...

else

    select ...
    where value in (@var1, ...)

I didn't particularly like this. (The query is quite massive), so I decided to opt for something like this: (We had the results in a temp table anyway)

IF @var IS NOT NULL OR ...

    delete from #temp where value not in (@var1,...)

But I realized that this won't work.

The only thing I can think to do is to create another temp table that will hold only the not-null values in @var1, etc. (Either using if statements or deleting where is NULL) and then doing a join delete on this table.

Is there a better way?


texts: SQL Server Trace - Filter on System Processes

texts:
Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don’t mean the usual server process (SPID), but the system processes that append “s” onto the end of a SPID (eg .spid29s) as seen when looking at  the SQL Server Logs.

The system processes are seen a lot when a server is first started.


texts:
This is more of a theoritical question but I need help ASAP. Here it is:

  With the appropriate use of SQL queries, grant as system
  administrators the required access privileges to a database user named
  STD01, so he can create a view of a table named CUSTOMER, which
  belongs to another database user STD00.

Can anyone help me with this?

I know that I have to grant him with the system privilege of CREATE (ANY) VIEW and also give him all the object privileges (SELECT, INSERT, UPDATE & DELETE) on the CUSTOMER table, but I have no idea how can I do that by using SQL...


texts: Installing Postgis and PGRouting via pgenv

texts:
We have a product where our production environment uses Docker and has no issue with installing things like postgis and pgrouting.

However, we have multiple remote developers and many of them (including me) are using pgenv for our PostgreSQL install and I don't know how to get those extensions installed properly. The error looks like this:

acme_test=# create extension if not exists postgis;
ERROR:  could not open extension control file "/home/ovid/.pgenv/pgsql-11.5/share/extension/postgis.control": No such file or directory

I have several postgis.control files, but I don't know how to link them (or if it's safe to do so):

12:11:34 {spike-pgrouting-120} ~/client/acme-api $ locate postgis.control
/etc/alternatives/postgresql-12-postgis.control
/usr/share/postgresql/12/extension/postgis.control
/var/lib/dpkg/alternatives/postgresql-12-postgis.control

I've installed postgis via sudo apt-get install postgis.

This is Postgresql 11.5 on Ubuntu 18.04.4.


texts: Looking for a Trigger to change inserting strings?

texts:
I have an MSSQL Server 2008 Database.
There are some tables in my Database like below :
table1, table2, table3, ....
each table has a NVARCHAR(50) field (Name)
Now, I wanna change the strings that each sql query inserts in this field
e.g :
We have a query that wanna insert "Sample Text" in Name field.
I wanna change it to "Sample Name" with a SQL Trigger

Is it Possible with sql triggers ? if so, How? I'm naive in T-SQL
Is there a better way to do it with SQL Server ?


texts: How is IF NOT EXISTS SELECT THEN INSERT faster than UNIQUE index?

texts:
In SQL Server how is...

Sp:

CREATE PROCEDURE insertToTable
    @field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT * FROM my_table WHERE field1 = @field1)
      INSERT INTO my_table
        (field1, field2, field3)
      VALUES (@field1, @field2, @field3);
    ELSE
      THROW 50000, 'xxxxxx', 1;
    END
GO

Table:

CREATE TABLE my_table (
    field1 VARCHAR(256) NOT NULL,
    field2 VARCHAR(256) NOT NULL,
    field3 VARCHAR(256) NOT NULL
);
CREATE INDEX idx_field1 ON my_table(field1);

the above faster than the below?

Sp:

CREATE PROCEDURE insertToTable
    @field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO my_table
        (field1, field2, field3)
    VALUES (@field1, @field2, @field3);
GO

Table:

CREATE TABLE my_table (
    field1 VARCHAR(256) NOT NULL,
    field2 VARCHAR(256) NOT NULL,
    field3 VARCHAR(256) NOT NULL
);
CREATE UNIQUE INDEX idx_field1 ON my_table(field1);

Sample input:

field1: F56yCgZ9AEm9aFpTyjwhERtqNeglYEow

field2: BD84CE2A514316164B7448C804B178AD8F6F597E8EC6F25F4D6E36287259C65F67E7206E82A4F8EFD2389C0821C0C70E8278DC5F166D220356B5A15A091A6C17$0

field3: A18E9049117A77E6A4D41C6CA3FFDEA65D842BF1F57705405B4E66969531D93D

The input is generated on the fly by the web application and using prepared statements. I use Jmeter to generate requests to my web app.

With UNIQUE index, insert performance degrades after 100K inserts and gets worse.

With NON UNIQUE index and a manual check with IF NOT EXISTS SELECT, performance is constant even with millions of records inserted.

The values are unique enough that there never is a duplicate generated. Even after a few million values inserted.


texts: MySQL JOIN two tables and get latest result


texts: How to Backup/Restore in PostgreSQL pgAdmin 4?

texts:
So I feel like this is something that should be very basic and easy to do, maybe I'm just not doing things correctly.

I want to do the equivalent of SQL Server backups. I want to create the equivalent of a full .bak file and be able to restore a database to its original state.

So far I've tried many different ways in PgAdmin4 to create a backup file from a freshly created database. I migrate my schema which creates a bunch of tables, then perform a restore on the previously created file. What I expect after the restore is for all those tables I created to be gone, I expect the database to be in the exact state that it was when I took the backup.

I've tried many different options like including the Create/Drop Database commands preserving/not saving data on backup/restore but have had no luck.

Anyone able to help me out here?

I am using commands pg_dump mydb -Fc > db and pg_restore db. Maybe there's some magic parameters I'm missing for it to do what I'm asking above?

Tried pg_restore --clean db, same results.

The problem is I create my database, fresh with nothing in it, take a backup of it, create some tables, restore the backup, and the tables I created are still there. I want a restore method that puts the db in the same state as it was when it was backed up.


texts:
I am trying to connect 3 SQL servers using SSMS from a different landing server. The issue is I am able to connect 1 server but unable to connect other 2. The error I get is below:

  A connection was successfully established with the server, but
  then an error occurred during the login process. (provider: SSL
  Provider, error: 0 - An existing connection was forcibly closed by the
  remote host.)

I am unable to figure out what is wrong with other 2 servers and need your help.

Landing server details:

Windows server 2008 R2 Ent Edition
SSMS 2012 SP1

SQL server details:

SQL Server 2012 -- Able to connect
SQL Server 2012 -- Unable to connect
SQL Server 2005 -- Unable to connect

I am able to ping and telnet all 3 servers from my landing server. Also, tried SQL authentication for 2 servers which I am unable to connect but it didn't work. Disabled TCP chimney, didn't work. The same 3 connections are working fine from my other landing server. So not sure what is wrong with this one. Please help me out to fix the issue.


texts:
I've inherited a Maintenance Plans that does the following:

Cleanup old data
Checks DB integrity
Performs Database and Transaction Log Backups
Reorganizes Our indexes
Updates Statistics
Delete old backups and Maintenance Plan files

Of the 23 minute Maintenance Plan, Updating the Statistics takes a staggering 13 minutes. During this 13 minute period, access to the database is blocked (or at least, replication from this DB to our others is paused).

My Question Is:

When should we be Updating the Statistics, and why?

This seems like the kind of thing we should do less frequently than every day. I'm trying to get us out of the "just because" mind-set of doing unnecessary Maintenance.


texts:
I have this table:

CREATE TABLE IF NOT EXISTS `usergroups` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned NOT NULL,
      `group_id` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`group_id`,`user_id`),
      KEY `id` (`id`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5496 ;

when run this query:

EXPLAIN SELECT `UserGroup`.`user_id` FROM   `usergroups` AS `UserGroup` WHERE  `UserGroup`.`group_id` = 1 limit 30

its out put is:

1       SIMPLE        UserGroup      ref  PRIMARY  PRIMARY  2        const  543      Using index

I think something is wrong! because it affected 543 rows but I think it should affect 30 rows at maximum. Is it true?


texts: Migration MySQL to Cloud Environment replication

texts:
I'm having 5 MySQL database server , these  5 server are used for e-business web-server. Now I Would like to move the MySQL database to cloud environment for database continuous running. Recently we face hardware failure (misc), in future we need to avoid any failure the database should continuously up and running that is the main objective

Guys could you suggest best way to proceed this scenario ?


texts:
When I try to process my cube and specifically the Employee_DIM, I get the following error :

  Errors in the OLAP storage engine: A duplicate attribute key has been
  found when processing: Table: 'dbo_Employee_DIM', Column: 'Firstname',
  Value: 'Aurélie'. The attribute is 'Firstname'.

I think that duplicate values for the column Firstname are considered as keys. After checking with the Employee_DIM :

 SELECT [Firstname], COUNT([Firstname]) AS dup_count
     FROM [Database].[dbo].[Employee]
        GROUP BY [Firstname]
             HAVING (COUNT([Firstname]) > 1)
                ORDER BY [Firstname]

Is there any alternative to leave only the business key in Employee_DIM and get Lastname and Firstname without adding them in the dimension for a future use?


texts: Securing Your MySQL Server within your team?

texts:
Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things:

OLD_PASSWORD = 1

(So all passwords on our development server were encrypted with a 16 bit hash, I think, which is really bad)

The GENERAL_LOG is off. This is important as some team members access MySQL via a GUI (MySQL Workbench) and are then able to change the root password by:

SELECT * FROM mysql.user;

And then manually changing the password, and clicking apply.

In my opinion the general_log should be on so we can place the blame on team members. Also could backups of the entire database be made nightly? How would that be done? How does this affect performance?

I know that when accessing via command line, the server stores all MySQL commands in a hidden file in the home directory of a user in .mysql_history. Is this the best option or is there another way of logging MySQL cli commands?
This is also a security threat if someone has access.

What are the best practices in ensuring that other people, cannot change any of the root password or permissions/grants. Would this mean not allowing any users to change permissions entirely. Would they still be able to manually change the mysql.user table.

The team members access MySQL using the root password so I have contemplated creating a new user, that does not have the ability to change permissions and password and even drop tables. Then changing the root password and telling them to use that account.

Also in terms of logging all MySQL command line and the MySQL general log how can I implement log rotation on these files.

Also do you have any other best practise suggestions for a development and production server?


texts: MongoDB is not using the index I created for this specific query

texts:
MongoDB 4.0.

I have a simple query:

db.product.find({
        modifiedOn: {
            $gte: ISODate("2019-08-02T03:00:00.000Z")
        }
    })
    .limit(10)

And a simple index on modifiedOn.

For some reason, the query is taking a long time to run against 40 million docs.

Explain says that the query is using a collection scan (COLLSCAN) instead of an index:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbname",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "modifiedOn" : {
                "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
            }
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "modifiedOn" : {
                        "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
                    }
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "serverhost",
        "port" : 27017,
        "version" : "4.0.11",
        "gitVersion" : "417d1a712e9f040d54beca8e4943edce218e9a8c"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1564756397, 18),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1564756397, 18),
        "signature" : {
            "hash" : BinData(0,"2D2uJyey5qPMYEk17CekUzpdqpU="),
            "keyId" : 6662358951796932610
        }
    }
}

This index took some hours to be created. Is there something I don't know with MongoDB that I need to do after creating an index to make it active?

Using explain("executionStats") I can see NO INDEX AVAILABLE FOR THIS QUERY.


texts: How does the database decide which Index to use

texts:
DDL

create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

create index Example1_Index
  On t(id1,id)

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

Sql Query - select * from t Where id = 107  -- uses Example_Index

Sql Query - select * from t Where id1 = 107 -- uses Example1_Index

Sql Query - select * from t                 -- uses Example1_Index

Confusion - How does the system decides to use Example1_Index

Below are the details...


texts: error in Execute SQL Task with parameter

texts:
I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that should be sourced from a user variable.

When I try to pass the query I get the following error, I searched internet a lot and tried different things, but still get the error message:

"The query failed to parse. Parameter Information cannot be derived from SQL Statements. Set parameter information before preparing command."

What I have set to the SQLStatement is:

INSERT INTO ErrorLog(ErrorDateTime, ErrorDescription) VALUES(GETDATE(),?)

What I have in the Parameter Mapping section of the Task:

The SQLSourceType is "Direct input" and as there is no result to this query the ResultSet has been set to "None".

The ErrorDescription column in the target table is varchar(max).

Any idea how fix this issue?


texts: Custom sort before ROW_NUMBER() for paging

texts:
I have the following

SELECT results.*, coinCat.*, cat.*
FROM (SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.CoinId) AS RowNumber
      FROM coins c) AS results
INNER JOIN CoinCategoriesCategories coinCat ON coinCat.CoinId = results.CoinId
INNER JOIN CoinCategories cat ON cat.CategoryId = coinCat.CategoryId
WHERE RowNumber>= @pageNumber * 1 AND RowNumber<@pageNumber *@pageSize
ORDER BY RowNumber

The statement must be nested in order to please Dapper (ORM),

Before applying/paging, I would like my results to be sorted by Coins.Rank but not sure where to stick that part so that my query can run properly and paging works happily

Also would this statement be any different between SQL Server vs PostgresSQL as an example?


texts:
The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about when these locks are acquired.

Are locks by default acquired at the beginning of a transaction, or just when they are needed? If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?


texts: Database deployment script for SQL Server 2008 has different behaviors

texts:
Using Visual Studio 2008, I have compared schemas between remote SQL Server database with  scripts hosted in a TFS server.

The goal was to generate a migration script that would update the remote database with developers latest script changes located in TFS.

The next step was to get a full backup from remote database, and restore it in my local database, and then execute the migration script in order to test it.

After small tweeks (dealing with constraints) and I have managed to execute the migration script locally and my local database (that is a copy from remote database).

The last step was to get this script and run it in remote database, but for my surprise, the script gets interrupted in a procedure:

Altering dbo.sp_my_procedure_here...
Msg 207, Level 16, State 1, Procedure sp_my_procedure_here, Line 616
Invalid column name 'code'.

Within this mentioned procedure, there is a CREATE TABLE ##TEMP (code INT), a execute command to run a SQL statement contained in a string variable for executing INSERT INTO ##TEMP VALUES (999), a SELECT @myReturnCode = code FROM ##TEMP and a DROP TABLE ##TEMP.

The statement SELECT @myReturnCode = code FROM ##TEMP is not working in the remote database. Since there is no procedure execution (this is only a script modifying database structure). Why the migration script would fire "invalid column" on the remote database?

The remote SQL Server database is version 10.0.2531

The local SQL Server database is version 10.50.2500

Both databases are set to compatibility mode for "SQL Server 2000".
The same migration script was executed in SSMS 2008.

ALTER PROCEDURE [dbo].[sp_my_procedure]
AS
SET NOCOUNT ON

CREATE TABLE ##TEMP (code INT)
DECLARE @sql Varchar(4000)

SET @sql = 'DECLARE @myReturnCode int
               SET @myReturnCode = 999
               INSERT INTO ##TEMP VALUES (@myReturnCode)'
EXEC (@sql)

IF (@@ERROR <> 0)
BEGIN
   RAISERROR('There is something wrong with insert',1,1)
   DROP TABLE ##TEMP
   RETURN -1
END
SELECT @myReturnCode = code FROM ##TEMP
DROP TABLE ##TEMP
SET NOCOUNT OFF


texts: How does the PowerShell SQLPS SQLSERVER provider detect central management servers in SQLRegistration\Central Management Server Group

texts:
I'm experiencing odd behavior with the PowerShell SQLSERVER provider and the SQLRegistration\Central Management Server Group directory. Below are the behaviors. I would like to know how the SQLSERVER provider knows what CMS's are registered so that I can understand why it doesn't seem to notice the registration when done in SSMS 2014 (see below for detailed behavior). Also, why my connection fails when I know the instance is online.

Setup
Machine

localhost

Instances

localhost\SQL2012
localhost\SQL2014
localhost\SQL2014_1

Behavior 1
Using SSMS 2014, I registered localhost\SQL2012 as a CMS. Executing this code returns no items.

PS SQLSERVER:\SQLRegistration\Central Management Server Group> dir

Open and closing the PowerShell console and SSMS does not change the results. If I open SSMS 2012 and register localhost\SQL2012 as a CMS and re-run the above command, I see localhost\SQL2012 registered as expected.

Behavior 2
After getting the server to list successfully...

  Directory: Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQLRegistration\Central Management
      Server Group

  Mode Name

  localhost\SQL2012

And running the below command to try and navigate to my registered server groups fails with the below error, even though the instance is online and available.

PS SQLSERVER:\SQLRegistration\Central Management Server Group> Set-Location "localhost\SQL2012\"

  Set-Location : Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\localhost\SQL2012\'
  because it does not exist.
  At line:1 char:1
  + Set-Location "localhost\SQL2012\"
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQLR...alhost\SQL2012:String) [Set-Location], ItemNotFoundE
     xception
      + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

Update 1
SMO seems to work fine. The below code successfully returns my server groups.

#Load SMO assemblies
$CentralManagementServer = "localhost\sql2012"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups


texts: Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2

texts:
Has anyone seen much slower SQL Server application performance when upgrading from Windows Server 2008 R2 to Windows Server 2012 R2?

We recently did a side-by-side upgrade of an important server with this newer operating system, SQL Server 2012 to SQL Server 2014 SP1, Citrix XenServer to vmWare, and an older Nimble SAN to a newer Nimble SAN with a lot more SSD, but one Delphi XE application went from running a process in 2 minutes to about 42 minutes!

We saw similar performance issues with our PowerBuilder application on Windows 8 and Windows Server 2012 / 2012 R2 and changing from the SQLOLEDB driver to the SQL Server Native Client driver SQLNCLI helped a lot. In this case, we tested the Delphi application with the latest SQL Server Native Client 2012 SQLNCLI11.1 and it didn't help much at all. I read at https://msdn.microsoft.com/en-us/library/cc280510.aspx that Microsoft recommends using the new ODBC Driver 11 for SQL Server - Windows, so that's something else we can try. This really seems like a driver-related issue.

I really didn't find much on the web about similar problems except the Balanced Power savings option as Glenn Berry describes at https://sqlserverperformance.wordpress.com/2010/09/28/windows-power-plans-and-cpu-performance/. I verified that we are using the High Performance setting and confirmed this with the CPU-Z utility.

I have heard of some people experiencing regression queries with the new SQL Server 2014 Cardinality Estimator (CE), but when I changed the database compatibility mode from 2014 (120) back to 2012 (110), it didn't help. Our testing also found that performance was fine on Windows Server 2008 R2 with SQL Server 2014 SP1 and the 2014 (120) compatibility level.

This application is unfortunately using a lot of client-side cursors to query the data one row at a time with many round trips to the server, and the developer has rewritten much of it in a set-based manner with stored procedures which performs much better, but the new approach still has to go through additional testing and QA before we roll it out. I am also concerned about other applications that may suffer from similar problems.

In the meantime we rolled back to the old Windows Server 2008 R2 server with an in-place upgrade to SQL Server 2014 SP1. We will do more comparison testing in a test environment.

Since this may affect other applications, I may have to open a Microsoft ticket to find out what the root cause is, but I wanted to check here first.


texts: Setting up MySQL (Master-Slave) replication with all ready configured databases/tables

texts:
I am trying to configure MySQL databases using the Master-Slave Replication. Before I realized that I had to set up my environment using this replication, I already have 2 separate servers running their own MySQL DB. Each of these servers are configured the exact same. The MySQL DB are configured with hundreds of tables.

Is there a way that i can set up (Master-Slave) Replication using the configured DB's? Or will i have to start from scratch and configure the Replication first and then load in all the DB tables

For the example below, could I replace 'slave' and 'password' with the current username and password I have already configured?

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Same with the commands below:

mysql&gt; CHANGE MASTER TO
-&gt; MASTER_HOST='X.X.X.X',
-&gt; MASTER_USER='slave',
-&gt; MASTER_PASSWORD='password',
-&gt; MASTER_LOG_FILE='mysql-bin.000001',
-&gt; MASTER_LOG_POS=327;


texts: Syncing On Prem SQL Server with Azure SQL

texts:
We have a SQL 2016 instance running production systems at a pretty healthy load within AWS. On top of this we host instances of production databases in Azure for PowerBI and analysis usage.

At present the database is manually handled to be moved in to Azure on a schedule but we would love to have this either synced or automated. We have determined Log Shipping cannot work but are looking for an alternative which is secure and stable.

We have also looked at SQL Data Sync but are struggling to ensure security and encryption between clouds.

In terms of the databases they are about 10gb in size so not small but not huge.


texts: Informix create procedure to query records and drop if value does not exist using select

texts:
I am trying to create a procedure that will query a table column to retrieve table names and using this in proc to run a query to do a select if it exists. If it does not exist, delete row(s) from systables_growth table.

Below is what I have so far:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
      FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
        BEGIN
            SELECT * FROM v_tab;
            ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
            DELETE from systables_growth WHERE tabname = 'v_tab'
            END EXCEPTION WITH RESUME;
        END
    END FOREACH
END function;

I am receiving the below syntax error when creating:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
BEGIN
SELECT * FROM v_tab;
ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
#^
#  201: A syntax error has occurred.
#
DELETE from systables_growth WHERE tabname = 'v_tab'
END EXCEPTION WITH RESUME;
END
END FOREACH
END function;

I am trying to define some logic to query the records pulled from table to run a query doing a select from the table names pulled. If the select says table does not exist then I want the row to be removed from the table. I don't know if I am using the correct logic.

I don't want the procedure to generate any output, I want it to be silent.


texts: Why am I getting cardinality issues when querying a single table

texts:
I have the following query:

SELECT
    [iri].[RateItemKey],
    [iri].[RateItemType]
FROM
    [prism72].[dbo].[ispRateItem] AS [iri]
WHERE
    [iri].[Status] = 1
    AND (
        [iri].[RateItemType] IN (3,2)
    )

The ispRateItem table has the following indexes:

CREATE NONCLUSTERED INDEX [COVIX_ispRateItem_RateItemType_Status] ON [dbo].[ispRateItem]
(
    [Status] ASC,
    [RateItemType] ASC
)
INCLUDE ( [RateItemKey]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CLIX_ispRateItem] ON [dbo].[ispRateItem]
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ispRateItem] ADD  CONSTRAINT [RateItem_PK] PRIMARY KEY NONCLUSTERED
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

I've updated the statistics using:

UPDATE STATISTICS [ispRateItem] WITH FULLSCAN, ALL;

However I index the table I get the same cardinality issue: Est Rows 1565, Actual Rows 4315. I've even tried removing all indexes (resulting in a table scan).

What could be causing this?

Please note that I'm a beginner to query tuning.

Thanks.

Alan


texts: Not able to login on MySQL in AWS RDS using root

texts:
I am trying to connect my MySQL RDS from EC2. Under security groups I have made sure permissions are in place.

$ mysql -h xxx.xyz.ap-southeast-1.rds.amazonaws.com -P 3306 -p
Enter password:

I am sure password is correct; because i reset it using AWS console. I am getting following error:

 Access denied for user 'root'@'ip-xx-xxx-xxx-xxx.ap-southeast-1.compute.internal' (using password: YES)

Looks like


texts: How compatible are Sybase and SQL Server clients connecting to the alternate back end?

texts:
Given the shared history of the Sybase and SQL Server products, and their use of a similar (identical?) wire protocol in TDS, what definite statements can be made, if any, about cross compatibility.

If I have a legacy system (binary, no source) that was written against a Sybase 11 back end, is it likely to run against SQL Server 2008?

Similarly, if I have a Windows binary, linked against sybase libs, would it just work against a Sybase server, in general or of specific vintage/versions?


texts: VACUUM and VACUUM FULL do not return space to OS

texts:
I'm running Postgres 8.3.3.  I have a postgres database on a Linux filesystem that is quite full.  I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS.  When I observe the size of the table in postgres it shows that the table is using considerably less space (down to 80MB form ~800MB).

Is there something else that should be run?


texts: autogrowth set higher than available disk

texts:
First all our autogrow settings are either turned off or the file size is set to the max autogrow size. I guess same difference as I came in after the setup.

Although I've never had this problem, I've been looking for an answer to a possible condition: I need my file to grow for example a tiny amount, say 100 MB for a insert to happen.  However my autogrow is set to say 500MB.  The disk has 450 MB available.  What would happen in that scenario.  And just say this insert happens once in a while at night, so I had no idea that it was going to happen, so I couldn't or didn't pre grow the file.

I'm really just curious about how sql server would handle this.


texts:
Has anybody encountered this problem with a db2 server? I'm running a development database and connecting to the database gives me a password expired error. Trying to reset it will give me an invalid username/password error.

$ db2 connect to mydb user devuser using mypassword
SQL30082N  Security processing failed with reason "1" ("PASSWORD EXPIRED").
SQLSTATE=08001
$ db2 connect to mydb user devuser using mypassword new newpasswd confirm newpasswd
SQL30083N  Attempt to change password for user id "devuser" failed with
security reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001

Everything I've read says that I should reset the password but I can't due to the invalid username/password error.

I don't get this error when connecting using the db2 admin user.


texts: SQL Server 2005: Full-text search, order by matches

texts:
Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches?

For example, let's say I have the following products for sale.

Tennis Racquets
Tennis Balls
Footballs
Hockey Sticks

If I do a search like:

select * from products where freetext(productname,'tennis ball')

The first three results would be returned as they all have "tennis" or "ball".  I would like to order the results so that "tennis balls", the one with two matches instead of one, is on top.

Is there a "match ratio" column, or something that can be used to rank how close the match was?  Is there a better way to do a full-text search that would allow for ranking?


texts: Update field with the longest varchar field

texts:
I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query:
SELECT TOP(1) options
FROM ForrasReportsRicsiTeszt
ORDER BY LEN(options) DESC

Now I need to update it, but I am quite unexperienced with sql. This is a 2005 ms sql server.
my query for updating:
  UPDATE ForrasReportsRicsiTeszt
  SET options = REPLICATE(options, 4)
  WHERE TOP(1) ORDER BY LEN(options) DESC

This gives me an error to TOP(1) which was quite expected. I just don't know the right way to write this query, how could I get to update only the longest varchar field? The one I've managed to get with my select query. Sadly there is no primary key that I could use in my WHERE.
Any help would be much appreciated!


texts: oracle global index vs local index on a partitioned table

texts:
I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this column. And can you explain why?

Thanks,

Sean Nguyen


texts:
I'm using tsrange in my project, but I don't know how to represent something.

I have a product that can be displayed on my website at some times, and can be hidden at other times. But tsrange has only 2 bounds (lower, upper). How can I represent an suite of ranges which can be indexed (by gist) and selected with the same operators of tsrange? (like this: [(,"2019-01-01"],["2019-02-15","2019-03-16"],["2019-10-11","2019-12-20"]])

I tested tsrange[] but I can't index this and I can't use tsrange operators. I found that I can create them.

For this solution, I don't want a table product_range with two column's.

What is the best practice?

EDIT

This is an example what I want :

CREATE TEMPORARY TABLE product (
  id BIGSERIAL,
  name VARCHAR(15),
  selling_dates TSRANGE[]
);
INSERT INTO product (name, selling_dates) VALUES ('Puppet', ARRAY[tsrange(NULL, '2019-01-01'), tsrange('2019-05-03', '2019-08-04')]);
CREATE INDEX product_selling_dates ON product USING GIST (selling_dates);

SELECT * FROM product WHERE selling_dates <@ '2019-06-10'::TIMESTAMP


texts: Amazon RDS low in memory no mysql connections

texts:
I'm running the free instance of amazon RDS connection to a t2.medium ec2 instace. We are running a wordpress website with 20 active plugins and about 800 pages. I can see that I am low in memory (freeable memory) down to 52MB. There is only 1 database connection active and the google analytics real time report show only 1 visitor which is me.

mysql show process list returns

+-----+-------+-------------+-------+---------+------+-------+------------------+
| Id  | User  | Host        | db    | Command | Time | State | Info             |
+-----+-------+-------------+-------+---------+------+-------+------------------+
| ****| ***** | l****:46109 | mysql | Sleep   | 4    |       | NULL             |
| ****| ****  | ****:33918  | NULL  | Query   | 0    |  init | show processlist |
+-----+-------+-------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

what is consuming my memory?


texts: Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench?

texts:
Why doesn't this MySQL 5.6 stored procedure compile with MySQL Workbench?

CREATE PROCEDURE spDataLocationSetActive
(
          IN driveLetter    varchar(1),
          IN active         tinyint(1)
)
BEGIN

if(      Select  DriveLetter
                        From    DataLocations
                        Where   DriveLetter = driveLetter      )
then

                UPDATE
                        DataLocations
                SET
                        Active = 1
                WHERE
                        DriveLetter = driveLetter;
end if;

END

'DriveLetter', 'varchar(128)', 'NO', 'PRI', '', ''
'Directory', 'varchar(200)', 'NO', '', 'SCR\\DATA\\', ''
'MaxUsage', 'smallint(6)', 'NO', '', '95', ''
'Sequence', 'smallint(6)', 'YES', '', NULL, ''
'ErrorMessage', 'varchar(255)', 'YES', '', NULL, ''
'DriveCapacity', 'bigint(20)', 'YES', '', NULL, ''
'DriveFreeSpace', 'bigint(20)', 'YES', '', NULL, ''
'Active', 'tinyint(1)', 'NO', '', '1', ''

Here is the result  of Show Create Table DataLocations.

Table,"Create Table"
DataLocations,"CREATE TABLE `DataLocations` (
  `DriveLetter` varchar(128) NOT NULL DEFAULT '',
  `Directory` varchar(200) NOT NULL DEFAULT 'SCR\\DATA\\',
  `MaxUsage` smallint(6) NOT NULL DEFAULT '95',
  `Sequence` smallint(6) DEFAULT NULL,
  `ErrorMessage` varchar(255) DEFAULT NULL,
  `DriveCapacity` bigint(20) DEFAULT NULL,
  `DriveFreeSpace` bigint(20) DEFAULT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '1',
  `InactiveCount` smallint(6) NOT NULL DEFAULT '0',
  `LastInactiveDateTime` datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `LastEnabledDateTime` datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
  PRIMARY KEY (`DriveLetter`),
  KEY `IX_DataLocations_DriveLetter_Active_Enabled`      (`DriveLetter`,`Active`,`Enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"

and here is the  insert statement

INSERT INTO `scdvr`.`DataLocations`
(`DriveLetter`,
`Directory`,
`MaxUsage`,
`Sequence`,
`ErrorMessage`,
`DriveCapacity`,
`DriveFreeSpace`,
`Active`,
`InactiveCount`,
`LastInactiveDateTime`,
`Enabled`,
`LastEnabledDateTime`)
VALUES
('/',
'SCDVR/',
85,
1,
15,
80000000000,
1,
0,
'2001-01-01 00:00:00',
1,
'2001-01-01 00:00:00');

I was wondering how to fix its syntax. Thank you.


texts: How to prevent locking and possible performance issue

texts:
I'm working on a website that lets users and admins to generate reports, and those reports require many database queries - they can take over one minute to complete. I noticed when these reports are being generated, the website might freeze for all its users.

These reports consist basically of SELECT queries, i.e., they don't update or insert anything into the MySQL database.

How do you guys implement intensive database operations such as report generations in production environments?


texts: Fetch data from database from multiple tables matching condition

texts:
I want to fetch data from the database. I have three tables in my database like listing_master_residential and listing_master_condo and listing_master_commercial. There are three tables and there is one primary key Ml_num in all tables. I want to search the data form one table which matches mls number table.

if(isset($_POST['search'])){
$mls=$_POST['mls_number'];
 $sql = "SELECT * FROM listing_master_residential
 , listing_master_condo
, listing_master_commercial
 INNER JOIN listing_master_residential AS res ON res.Ml_num=Ml_num
 INNER JOIN listing_master_condo AS con ON con.Ml_num=Ml_num
INNER JOIN listing_master_commercial AS com ON com.Ml_num=Ml_num
 WHERE Ml_num='$mls'";
    $result = $wpdb->get_results($sql) or die(mysql_error());
foreach( $result as $row){
echo $row->Lot_code."
";
echo $row->Ml_num."
";
echo $row->Acres;
echo $row->Addr."
";
echo $row->Bath_tot;
echo $row->Br;
echo $row->Br_plus;
}

 }

 }

With the above, I get an error

Column 'Ml_num' in where clause is ambiguous


texts: Simple avg query on large table much slower in PostgreSQL than SQL Server

texts:
I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012).

measurements:

column         | type                 | attributes
---------------+----------------------+-----------------------------------------------------------
id             | int/serial           | (identity) primary key
measuretime    | datetime/timestamp   | not null
parameter_id   | int                  | not null (foreign key) references parameters(id)
item_id        | int                  | not null (foreign key) references items(id)
value          | float                | not null

and two nonclustered index on measuretime and parameter_id

I've inserted 2.609.280 rows in items (half a year with 5 seconds between each) and 31.311.360 rows in measurements (for each item with 12 parameters).

When I now try to query the average value per day per parameter it performs really well on SQL Server (00:00:02) but pretty bad on PostgreSQL (00:00:53).

SQL Server Query:

select parameter_id, convert(date, measuretime), avg(value)
from measurements
group by parameter_id, convert(date, measuretime)

PostgreSQL Query:

select parameter_id, date(measuretime), avg("value")
from measurements
group by parameter_id, date(measuretime)

Is there anything I can do about this? create an index? some server settings? change the query?


texts: Has some command that lists the privileges of a user in db2?

texts:
I'm using db2 9.7 on linux and i want to list my privileges on database...
Ex: SYSADM, SECADM, DBADM, etc.
So, has some command in db2 that lists the privileges of a user in db2?


texts: How do hierarchical databases records link together?

texts:
If I had a hierarchical database containing 3 tables: course, students, grades

Course would be the root table and may have 10 course records in there.

Each course has its own selection of students, if the record doesn't contain keys like a relational database, how does it know which records for the students table are for which course?

Does it work like this or am I mistaken:

Courses Table:
Name : Length : Teacher
Example : 1 : Sir Example
Example2 : 1 : Sir Test

If I wanted to view the students in the example course, I would select this and it then shows the students table for this course.

It would be like the Windows file system, the records work like folders except display the data and are able to be opened to then show the children tables.

Course 1 > Example > Students


texts:
Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008?

For example take the following table.

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235);

What I'd like to be able to do is create a table with this format

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

The only way I could think to accomplish this was to aggregate the vertical table.

By doing something like the following query

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost)
FROM BillingData;

But then I'd have to dynamically join those columns into the Billing table, especially since the BillingData may not be the same from month to month. For example:

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

Is different from

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

So while the columns BillingId, Walks, Baths, Litter change, Groom, Scratching, Usage, Cost are appropriate for April the columns for May would be just BillingId, Pilling, Groom, Exercise, Usage and Cost.

I believe a pivot table may be what I need here but I suspect it may need to be dynamic as columns would need to be different for each month.

I'm not sure the best way to go about doing this. Some help would be greatly appreciated.


texts: I have a view that is being used to export data from my database to a 3rd party system, I have been asked to add a Boolean flag to the view that can be updated if the record has been exported to the other system. Is this possible? If so, how can it be done?


texts: Oracle error 12154 while importing DMP file

texts:
My Setup
I'm running Windows 7 64-bit, using Oracle DB 11g R2 for Windows 64-bit. I have Oracle SQL Developer Release 3.0 (4.0.2.15.21) as the GUI for my database.

Question
I followed the instructions from this post on how to import an Oracle .DMP file. I issued this command into the command prompt: C:\>imp sys@dev/password12345 FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp full=yes
It then prompts me for a password in which I enter the correct one and then I receive this error:
IMP-00058: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
IMP-00000: Import terminated unsuccessfully

Actual Question: How do I fix this error and be able to import the .dmp file successfully? Or what are some steps I can head towards to potentially get this working...?

My Files & What I've Done
I have looked through countless posts on this same issue. My tnsnames.ora, listener.ora, & sqlnet.ora are located in this directory: C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN
My connection in my tnsnames.ora file looks like this (I have also tried to put my computer name as the HOST, instead of 127.0.0.1)...
DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DEV)
  )
 )

PATH Variables
I added the following to my PATH (I realize there may be some unnecessary item in there, I just wanted to be on the safe side. ):
C:\oracle\product\11.2.0\dbhome\bin;C:\oracle\product\11.2.0\client_1;C:\oracle\product\11.2.0\client_1\bin;%ORACLE_HOME%\bin;
I also created two more variables in my System Variables...
TNS_ADMIN C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN
TNS_NAMES C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN


texts: Performance impact of setting PostgreSQL to log all statements

texts:
PostgreSQL 9.2.2 on Windows 7 64-bit.

The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all).  To what extent does setting log_statement = 'all' degrade performance?  I've read that this significantly reduces performance, but these claims come from older articles, so just how much of a performance loss should I expect relative to log_statement = 'none'?


texts: Is it possible to estimate the restore progress in SQL Server 2014 from pages read/written?

texts:
For context - SQL 2014 EE, we're doing tran log backups from our main production box and restoring to a R/O instance. The log is usually around 50GB.

So I'm familiar with the structure of SQL Server restores and know the percentage complete estimate only refers to the data copy phase. For us, this generally runs pretty quickly but the redo phase is where the time is taken.

From sp_whoisactive I can easily get pages read/written by the log restore. What I'm wondering is if I know the size of the log (which I do), can I estimate the progress of the restore from those numbers?

I intend to give is a quick go, but if someone has tried this already and it's a fools errand I'll skip it!


texts: SQL-SERVER 2014: Recommended steps to move Database from one server to another

texts:
I need to relocate a database running sql-server 2014 from one server to another. The source server has three instances with a few databases per instance all relating to the same application. Looking for recommended tips/steps to do this.
Thanks!


texts:
We have 2 SQL Server 2008 environments with some triggers against a table containing business logic

Within the 2 environments, we can see that the triggers are firing in a different order (thanks to print statements in the triggers)

How can I VIEW the trigger firing order within each environment? I know I can use sp_settriggerorder to set them but we want to see what the differences are in the two environments


texts: I have 50,000 unique cards in a table. A minimum of 2 cards and up to 20 cards can be chosen by the user and they can write a blurb about it. There will be times when the cards chosen by different users will overlap. When the exact same cards are chosen, I'd like to store the new blurb but keep the table clean as possible as many people may choose the same 2 cards but write different blurbs about the 2 cards. How should I design my table to store this information?


texts: Restore backup of existing database configured for slony replication

texts:
How to restore a database backup (Postgres 9.3) in a existing database (slony replication activated)?
I always delete the database and create a new one to restore, but in this case I can't delete the destination database because it's configured for slony replication. I tried with pg-admin restore the backup but throws errors.


texts: we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the performance? Suppose we are querying one of the partition. Does the oracle optimizer directly use the partition segment or rely on index(on the partitioned column) to choose the correct partition or does it perform a full table scan (going through all the segments) if we delete the index?


texts: SQL application connection using one Windows user

texts:
We are currently looking to update an old application security structure and would like to go for an approach where the application connects to SQL via one Windows login with the required Read/Write access.

We don't want to give access to the DB to all users otherwise they might connect via another application (SSMS or Excel) and run commands on SQL. I know we can use Application Roles to achieve this however, the application connects to many databases and we wouldn't like to enable the Guest user.

Do you have any other suggested approaches or in that case we would need to use an SQL encrypted login instead of a Windows login?


texts: Implementing versioning system with MySQL

texts:
I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help.

Initially I had my blogstories table with this structure:

| Column    | Type        | Description                                    |
|-----------|-------------|------------------------------------------------|
| uid       | varchar(15) | 15 characters unique generated id              |
| title     | varchar(60) | story title                                    |
| content   | longtext    | story content                                  |
| author    | varchar(10) | id of the user that originally wrote the story |
| timestamp | int         | integer generated with microtime()             |

After I decided I wanted to implement some versioning system for every story on the blog, the first thing that came to my mind was creating a different table to hold edits; after that, I thought I could modify the existing table to hold versions instead of edits. This is the structure that came to my mind:

| Column        | Type          | Description                                       |
|------------   |-------------  |------------------------------------------------   |
| story_id      | varchar(15)   | 15 characters unique generated id                 |
| version_id    | varchar(5)    | 5 characters unique generated id                  |
| editor_id     | varchar(10)   | id of the user that commited                      |
| author_id     | varchar(10)   | id of the user that originally wrote the story    |
| timestamp     | int           | integer generated with microtime()                |
| title         | varchar(60)   | current story title                               |
| content       | longtext      | current story text                                |
| coverimg      | varchar(20)   | cover image name                                  |

The reasons why I came here:

The uid field of the initial table was UNIQUE in the table. Now, the story_id is not unique anymore. How should I deal with that? (I thought I could address story_id = x and then find the latest version, but that seems very resource consuming, so please give your advice)
author_id field value is repeating in each every row of the table. Where and how should I keep it?

Edit

The unique codes generation process is in the CreateUniqueCode function:

trait UIDFactory {
  public function CryptoRand(int $min, int $max): int {
    $range = $max - $min;
    if ($range < 1) return $min;
    $log = ceil(log($range, 2));
    $bytes = (int) ($log / 8) + 1;
    $bits = (int) $log + 1;
    $filter = (int) (1 << $bits) - 1;
    do {
        $rnd = hexdec(bin2hex(openssl_random_pseudo_bytes($bytes)));
        $rnd = $rnd & $filter;
    } while ($rnd >= $range);
    return $min + $rnd;
  }
  public function CreateUID(int $length): string {
    $token = "";
    $codeAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $codeAlphabet.= "abcdefghijklmnopqrstuvwxyz";
    $codeAlphabet.= "0123456789";
    $max = strlen($codeAlphabet) - 1;
    for ($i=0; $i < $length; $i++) {
        $token .= $codeAlphabet[$this->CryptoRand(0, $max)];
    }
    return $token;
  }
}

The code is written in Hack, and was originally written in PHP by @Scott in his answer.

The fields author_id and editor_id can be different, because there are users with enough permissions to edit anyone's stories.


texts: Which instance's timestamp is used as SCN in Oracle 12c rack?

texts:
Let us assume 4 oracle12c rack instance's are running on different nodes. Each node has its own clock. No two clock shows the same time.

Which instance's timestamp is used by Oracle as SCN?
And also upon how linearization is achieved.


texts: How to have MS Analysis server setup of both modes: multi dimension and tabular

texts:
Ref: Is there a way to install both tabular and multidimensional modes?

The answer was .."You answered your own question. You simply install another instance of SSAS."..

My MS SQL 2014 has a database engine installed with AdventureWorks DB and AdventureWorksDW DB. I also have installed Analysis server with a database containing a multi-dim cube.

I tried to install a another instance of SSAS to allow me to test the tabular mode, but the DVD disk did not give me the option to add another instance of SSAS for tabular.

What am I missing?

How do I do this .."You simply install another instance of SSAS.".. for the new instance of SSAS to source data off my already installed AdventureWorkdsDW dataBase.

Thanks - First response was the answer


texts:
We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server.

The spikes seem to fall at an exact time after the hour, each hour.  We have investigated every known scheduled task source (SQL Agent, backups, scheduled SSRS Reports, etc.) and can't find any rhyme or reason to it so far.

We have use Activity Monitor as well, but it has not yielded any answers to date.

How would one go about definitively finding the source of I/O spikes?  Are there monitoring tools (commercial or otherwise) that would help pinpoint the problem?


texts: designing user friendly relational databases

texts:
I want to create a database system that is user friendly with the right relationships. My system should be in such a way that we have a writer's table, orders table, and payments table such that I am capable of knowing which writer did certain orders and shows the amount the writer should be paid for doing an order. The system should also indicate whether the payment has been made for each order done by the writer.

I tried my best and came up with the following tables:

payments table: pay_id (PK), date, mode of payment, amount, balance, writer_id (fk), order_id (fk);
writer’s table: id (pk), name, email, phone numbers, disciplines;
order’s table: order_id (pk), name, order_status (completed, under warranty, completed), writer_id (fk)

Kindly assist me in making the necessary corrections so that all relations can be created and the database becomes human friendly.


texts: Fail to perfom static registration of nomount instance

texts:
I'm trying to make standby to database.
I've insalled Oracle 11g database on virtual machine (host - oratest). Standby is on virtual machine too (standbysrv).

When I'm trying to connect from source database to auxiliary database I'm getting ORA-12528.
The only way I can connect to auxiliary databse is using the 2nd method from the note:

[oravis@standbysrv backup]$ rman target sys/manager@testprod auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 26 19:36:39 2015

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

connected to target database: TEST (DBID=2197628678)
connected to auxiliary database: TEST (not mounted)

But this way does not work for active duplicate.

Here is listener.ora of source database:

    TESTSTAN =
     (DESCRIPTION =
     (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521))
     )
     (CONNECT_DATA = (SERVICE_NAME = teststan) ) )

listener.ora of future standby:

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1)
        (PROGRAM = extproc) )
 (SID_DESC =
        (GLOBAL_DBNAME = teststan)
        (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1)
        (SID_NAME = test) ) )

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )


texts: Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

texts:
Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION.  If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, at least up to 5.5.  If you don't use it, you leak transactions, and drivers such as JDBC auto-commit them instead of aborting them.

What gives?  This sounds...  awful.


texts:
It is second time that records are lost in MySQL specific table.

I am having Master-Slave MySQL replication.

For the 1st time, one of my tables partially lost records on 6 May.
The weird part is, the Master instance records loss is not replicated to the Slave. It couldn't be SQL injection or script issue. I can't really remember much details as it is 2 months ago.

And recently (2nd time), the data loss happens again in another table. The data lost for records after 2015-06-05, and about 60% of the records gone. This time, the Slave has the data loss too.

I checked the Slave mysqlbinlog file, there is no DELETE FROM table command executed during that period.

Both tables are InnoDB, running MySQL server in Ubuntu instance.

I realised the data loss issue only after multiple users reporting the software behaving incorrectly.

May I know what happen to MySQL? This is frightening if the data can be lost out of nowhere. I managed to recover the data with daily backup, but this is definitely not a good option.


texts: I'm wondering if there's any database technology where its structure or schema does not need to be defined upfront? For example it may start off with just one column or row and the user will keep adding more columns and rows to the table on the fly, when they see it necessary.


texts:
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.

Consider the following 3 TABLES:

EMPLOYEE

EMPLOYEE_ID, EMP_NAME

PROJECT

PROJECT_ID, PROJ_NAME

EMP_PROJ (many to many of above two tables)

EMPLOYEE_ID, PROJECT_ID

Problem:  Given an EmployeeID,  find ALL the employees of ALL Projects that this Employee is associated with.

I have tried this in two way.. both approaches differ only by few milliseconds no matter what size of data is used.

SELECT EMP_NAME FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (
    SELECT EMPLOYEE_ID FROM EMP_PROJ
    WHERE PROJECT_ID IN (
        SELECT PROJECT_ID FROM EMP_PROJ p, EMPLOYEE e
        WHERE p.EMPLOYEE_ID = E.EMPLOYEE_ID
        AND  E.EMPLOYEE_ID = 123)

go

select c.EMP_NAME FROM
(SELECT PROJECT_ID FROM EMP_PROJ
WHERE EMPLOYEE_ID = 123) a
JOIN
EMP_PROJ b
ON a.PROJECT_ID = b.PROJECT_ID
JOIN
EMPLOYEE c
ON b.EMPLOYEE_ID = c.EMPLOYEE_ID

As of now, I expect around 5000 Employees and Projects each.. but have no idea about what kinda many-many relationship exists.
Which approach would u recommend?
thanks!

EDIT:
Execution Plan of Approach 1

"Hash Join  (cost=86.55..106.11 rows=200 width=98)"
"  Hash Cond: (employee.employee_id = emp_proj.employee_id)"
"  ->  Seq Scan on employee  (cost=0.00..16.10 rows=610 width=102)"
"  ->  Hash  (cost=85.07..85.07 rows=118 width=4)"
"        ->  HashAggregate  (cost=83.89..85.07 rows=118 width=4)"
"              ->  Hash Semi Join  (cost=45.27..83.60 rows=118 width=4)"
"                    Hash Cond: (emp_proj.project_id = p.project_id)"
"                    ->  Seq Scan on emp_proj  (cost=0.00..31.40 rows=2140 width=8)"
"                    ->  Hash  (cost=45.13..45.13 rows=11 width=4)"
"                          ->  Nested Loop  (cost=0.00..45.13 rows=11 width=4)"
"                                ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"                                      Index Cond: (employee_id = 123)"
"                                ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                                      Filter: (p.employee_id = 123)"

Execution Plan of Approach 2:

"Nested Loop  (cost=60.61..112.29 rows=118 width=98)"
"  ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"        Index Cond: (employee_id = 123)"
"  ->  Hash Join  (cost=60.61..102.84 rows=118 width=102)"
"        Hash Cond: (b.employee_id = c.employee_id)"
"        ->  Hash Join  (cost=36.89..77.49 rows=118 width=8)"
"              Hash Cond: (b.project_id = p.project_id)"
"              ->  Seq Scan on emp_proj b  (cost=0.00..31.40 rows=2140 width=8)"
"              ->  Hash  (cost=36.75..36.75 rows=11 width=8)"
"                    ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                          Filter: (employee_id = 123)"
"        ->  Hash  (cost=16.10..16.10 rows=610 width=102)"
"              ->  Seq Scan on employee c  (cost=0.00..16.10 rows=610 width=102)"

So looks like the Execution plan of Approach 2 is slightly better, because the 'cost' is 60 as opposed to 85 of approach 1. Is that the right way to analyze this?

How does one know it will hold true even for all sorts of many-many combinations?


texts:
Does SQL Server Express 2016 come with SQL Server Configuration Manager?

If not, is there any licenced way to add SQL Server Configuration Manager to an existing installation of SQL Server Express 2016?


texts:
PII, or Personally Identifiable Information, involves information that may include the following, among other data:

First Name
Last Name
Email Address
Phone (any of them)
Billing Address
Shipping Address

Security researchers and legislation are recommending we encrypt the data. In my case, I'm using PHP7 and MariaDB. So, I can store encrypted PII using the latest recommended and battle-tested encryption APIs. However, now when I need an admin system, I need to be able to:

Sort data by PII table columns
Do case-insensitive partial keyword searches

Unfortunately, I'm not clear on how to do that smoothly with encrypted data stored in MariaDB (and which might also use PHP7).


texts: Table design pattern that can support partitioning, clustered columnstore index, and spatial data?

texts:
I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day).

Requirements:

Spatial queries must be fast
Temporal queries (on a timestamp column) must be fast
The table must be partitioned over the timestamp column

The schema might look something like this:

With this schema, I can not use a clustered columnstore index due to the Position column which is a spatial type. My gut reaction to this was to split out the Position column to a new table with a 1-to-1 relationship.

However, this also introduces some problems. The spatial index will not be partitioned, unless I partition the second table. To do that, I will have to duplicate the timestamp column.

Is there a better way to achieve a partitioned clustered columnstore index for this dataset?


texts: DBCC CheckDB Consistency error on a empty table

texts: What could be the reason behind a DBCC CheckDB reporting a consistency error on a empty table in sql server 2008? This DB is restored from sql server 2000. DBCC CheckDB on sql server 2000 doesnot report any errors.


texts: Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON

texts:
I have a table , in SQL Server 2012 enterprise edition, for which I want to have a unique index with IGNORE_DUP_KEY = ON, however I only want this index (and ignoring the duplicates) to apply only to a subset of the table - only rows with FLAG = 1.

CREATE TABLE [dbo].[FOO]
([ID] NOT NULL,
[Value] [int] NULL,
[FLAG] [bit] NULL)

When trying to create the index using below query

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UQ_FL_FOO_VALUE] ON [dbo].[FOO]
([Value] ASC)
WHERE [flag] = 1
WITH (IGNORE_DUP_KEY = ON)

I get an error:

  Msg 10618, Level 16, State 1, Line 1
  Cannot create filtered index 'IDX_UQ_FL_FOO_VALUE' on table 'dbo.FOO' because the statement sets the IGNORE_DUP_KEY option to ON. Rewrite the statement so that it does not use the IGNORE_DUP_KEY option.

Is it not possible to combine a filtered unique index with the IGNORE_DUP_KEY = ON?

Setting the index to non-unique is not an option. I need it to be unique to filter duplicate inserts, but only when inserting values with FLAG = 1. I need the insert of a duplicated row to be ignored, not to fail.


texts: ORA-39142: incompatible version number 3.1 error work-around

texts:
I am trying to import an Oracle backup .DMP file into a new Oracle DB and I'm getting the error "ORA-39142: incompatible version number 3.1".  My research tells me that this is because the version of Oracle that the .DMP file is coming from is newer than the version I have running (running on a VM), but I have no control over the export process for this .DMP file so I can't play games there to "downgrade" the export.

Is there a way import this backup file without fiddling with the export process and without upgrading the Oracle VM I'm using?


texts: How can I query for terms like "@foo" with Postgres full text search?

texts:
I would like to be able to query for documents containing @foo without matching the naked word foo. I'm using the simple dictionary but I notice @ characters are stripped:

SELECT to_tsquery('simple', '@foo');  -- produces 'foo'

Is there a way to force Postgres to treat the @ as significant?


texts: Postgresql cannot peer authenticate using usermap (provided user name (^dbuser^) and authenticated user name (^sysuser^) do not match)

texts:
I am trying to peer authenticate some users using usermap. What I am trying to do is to map user mailreader as you can see:

postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 mailreader |                                                | {}
 postgres   | Superuser, Create role, Create DB, Replication | {}

Into system's user root,dovecot,postfix. Therefore I edited my pg_ident.conf and put the following content:

mailmap         dovecot                 mailreader
mailmap         postfix                 mailreader
mailmap         root                    mailreader

I also edited the pg_hba.conf and appended with the following content:

local    mail        all                     peer map=mailmap

When I try to connect via sudo psql -U mailreader -d mail command I get:

  psql: FATAL:  Peer authentication failed for user "mailreader"

error.

I also tried the following configuration:

local    mail            all                                     ident map=mailmap

Without any progress.

May I have some help?

Edit 1

The logs regarding the error is:

2017-06-27 19:10:10 UTC [1188-1] mailreader@mail LOG:  provided user name (mailreader) and authenticated user name (root) do not match
2017-06-27 19:10:10 UTC [1188-2] mailreader@mail FATAL:  Peer authentication failed for user "mailreader"
2017-06-27 19:10:10 UTC [1188-3] mailreader@mail DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"


texts: What does 0 in DATEDIFF(MINUTE, 0, ) actually mean?

texts:
So, our data team asked for some help in solving a problem they had.  I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using.  While I've solved their problem, It came about that I don't actually know what the 0 turns into when used as they were using it.

I originally thought it was closer to an integer overflow rather than a true conversion error, but that's not it.  I tried it on a SQL 2016 box with DATEDIFF_BIG and same error.  I have a sample for you guys below to play with along with what works and what doesn't.

/** Setup The Sample */
DECLARE @TestValue DATETIME2(7)
SET @TestValue = '0001-01-01 10:30:00.0000000'

/** Conversion Error
Msg 242, Level 16, State 3, Line 10
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
*/
SELECT DATEDIFF(MINUTE, 0, @TestValue)

--Also does not work, same error.
SELECT DATEDIFF_BIG(MINUTE, 0, @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, '1/1/1900', @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)

/** Doesn't Work, you can't cast 0 to a DATETIME2 */
--SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME2), @TestValue)

/** Works (or no error, which is fine)*/
SELECT DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))

Bonus Question, since 0 doesn't work in all cases for DATETIME2, what's the alternative?

WHAT WE DECIDED TO DO

So, I have started recommending my team do the following, since you see 0 in lots of examples for datemath (first day of month, etc.).  So I recommend that you do an explicit cast of 0 to datetime, then continue as you will.  This will avoid the error while still working. So:

DATEDIFF(MINUTE, CAST(DATETIME, 0), <Date>)


texts: Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6

texts:
I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely standalone memcached servers in cacheing scenarios.

We're using HandlerSocket quite heavily, as it provided a massive increase in IO, and from the original papers on the subject (at the time), it was actually faster than traditional memcached access, thus why we pursued it.

However in learning that the new memcache plugin serves the same purpose now, I went searching for benchmarks, comparisons, or even cock-eyed opinion, but wasn't able to find any specifically addressing both.

I'd like to know if anyone has had experience with both, switched to/from either and what their experiences were?


texts: Any Reason to have AlwaysOn Failover Clustering in Azure Cloud?

texts:
Is there a reason to have AlwaysOn Failover Clustering when a database is in Azure Cloud?

I understand the benefits having AlwaysOn Availability Groups in Azure Cloud for Database-Level protection. People can transition heavy read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

However why would someone require Failover Clustering in Azure Cloud for server-level protection? Don't cloud services (both PaaS and IaaS) inherently offer server level protection by itself?


texts: ORA-00823: Specified value of sga_target greater than sga_max_size dba

texts:
I was doing a modification in my database and something I should not have done well and I got the following error :

SQL> startup
ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters

How to solve it ?


texts: Trying to install sql_server_2014_enterprise_edition_x64_dvd Windows 7(Ultimate) 64Bit OS

texts:
Im receiving the following error after the Set-up checks Global rules

It shows the following error. any idea why this issue occurs :( ?by the way this machine was formatted yesterday and this is a fresh installation.!Your help is appreciated..

  TITLE: SQL Server Setup failure.

  SQL Server Setup has encountered the following error:

  No room is available to display rows..

  For help, click:
  http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0x52CFE887%25400xE9BC3D64

  ------------------------------ BUTTONS:

  OK

Extracted from the Log file

  Exception summary: The following is an exception stack listing the
  exceptions in outermost to innermost order Inner exceptions are being
  indented

  Exception type: System.InvalidOperationException
      Message:
          No room is available to display rows.
      HResult : 0x80131509
      Data:
        HelpLink.EvtType = 0x52CFE887@0xE9BC3D64
        DisableWatson = true
      Stack:
          at System.Windows.Forms.DataGridView.set_FirstDisplayedScrollingRowIndex(Int32
  value)
          at Microsoft.SqlServer.Configuration.InstallWizard.RunRuleProgressController.RulesEngineExecutionCompleted(Object
  sender, RunWorkerCompletedEventArgs e)


texts: FOR XML is generating a empty first line

texts:
I'm parsing with flash a XML file generated by this code:

:XML ON
USE MyDatabaseName
GO
SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
FOR XML AUTO,
ROOT ('toflash'),
ELEMENTS

SET NOCOUNT OFF

But I get a XML file with the first line empty. Removing this first empty line in the generated XML works ok with flash, but with the generated XML no.

How can I remove that line? Is my script wrong? I have no much idea about this code.

I'm running SQL Server 9.0.


texts:
I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).

Is it possible to force MySQL not to make the hard drive a bottleneck?


texts: at the moment mysqldump (22 minutes exec time, 5 GB Dump), when to use xtrabackup?

texts:
We use the Puppet code for deploying MariaDB. Those are the backup options.

mysql::backup::mysqldump: Implements mysqldump backups.
mysql::backup::mysqlbackup: Implements backups with Oracle MySQL
Enterprise Backup.
mysql::backup::xtrabackup: Implements backups with
XtraBackup from Percona.

We don't own MySQL Enterprise Backup licenses.

Here the backup script. The big problem at the moment, we don't notice when backup is failing.

#!/bin/bash
#
# MySQL Backup Script
#  Dumps mysql databases to a file for another backup tool to pick up.
#
# MySQL code:
# GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'user'@'localhost'
# IDENTIFIED BY 'password';
# FLUSH PRIVILEGES;
#
##### START CONFIG ###################################################

USER=backup
DIR=/var/lib/mysql/backup
ROTATE=6

# Use a pwd file. See https://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
PWD_FILE=$(mktemp ${DIR}/backup.cnf.XXXX)
echo "
[client]
password=sirugrwiuwiuwguisrkogos
" >> $PWD_FILE

PREFIX=mysql_backup_

ADDITIONAL_OPTIONS="--ignore-table=mysql.event"

##### STOP CONFIG ####################################################
PATH=/usr/bin:/usr/sbin:/bin:/sbin

set -o pipefail

cleanup()
{
    find "${DIR}/" -maxdepth 1 -type f -name "${PREFIX}*.sql*" -mtime +${ROTATE} -print0 | xargs -0 -r rm -f
}

mysqldump --defaults-file=${PWD_FILE} -u${USER} --opt --flush-logs --single-transaction \
 ${ADDITIONAL_OPTIONS} \
 --all-databases | bzcat -zc > ${DIR}/${PREFIX}`date +%Y%m%d-%H%M%S`.sql.bz2

if [ $? -eq 0 ] ; then
    cleanup
fi

rm -rf $PWD_FILE

backup time

# time /usr/local/sbin/mysqlbackup.sh

real    21m35.647s
user    20m47.059s
sys     0m7.843s

backup size

# du -sh mysql_backup_20160223-123920.sql
4.9G    mysql_backup_20160223-123920.sql

Is it in our use case better to use xtrabackup? When to use xtrabackup?


texts:
I have a table lets say
Table1 with columns Point_ID, Size, Age, Dist, Treatment
and Table 2 with columns  Point_ID, Size, Age, Dist, Treatment, Path, Location.
Table 2 has similar data for Point_ID, Size, Age, Dist, Treatment as Table1 for some values.

I want to merge extra columns of Table 2 in Table1 such that there is no duplication and the resulted table looks likes  "Point_ID, Size, Age, Dist, Treatment, Path, Location."
There is data in Table1 in columns Point_ID, Size, Age, Dist, Treatment which does not have path and location, so the path and location should be null for them.
What SQL query to be performed?.


texts: I am installing SQL 2012 and getting error wait on database engine handle recovery failed,  changed the credentials to "NT Authority\NETWORK SERVICE" but getting same error:


texts: Identifying values that are not existent in other table

texts:
I've got two tables that have one to many associations on a pmid.  So if one table has an pmid, the second table should have multiple rows with the same pmid.  However, something went sideways and I'm missing my latest batch of pmids in the second table.  These queries, should help illustrate the problem, but I can't figure out how to get the ids from the first table that are actually missing in the second table.

select count(*) from abstract_mesh am; #2167101

select count(*) from abstract_mesh am
join abstracts a on am.pmid = a.pmid; #2133848

select 2167101 - 2133848;  #33253

select count(*) from abstract_mesh where pmid is NULL; #33253

So as you can see there are 33,253 rows in abstract_mesh that have no pmids.  I simply want to identify which pmids I should be interested in from the abstracts table.


texts: Same query but different plan on different server

texts:
I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (Name) with datatype nvarchar(MAX) was the problem so I changed it to nvarchar(1024). After which execution time reduced to 2s on DEV env. However, when I perform same alter on QA environment I don't see any improvement. Strange thing is that both servers are generating different execution plan, both server has same configuration, same schema. In fact DEV has more data.

Query:

WITH Ancestor (Id, TreeLevel, Branch, Name) AS
(
    SELECT
        I.Id, 0 AS TreeLevel,
        CAST(I.Name AS VARCHAR(1024)) AS Branch,
        I.Name
    FROM
        dbo.Items AS I
    WHERE
        (I.Parent_Id IS NULL)

    UNION ALL

    SELECT
        D.Id, A.TreeLevel + 1 AS TreeLevel,
        CAST(A.Branch + ' -> ' + CAST(D.Name AS VARCHAR(1024)) AS VARCHAR(1024)) AS Branch,
        D.Name
    FROM
        dbo.Items AS D
    INNER JOIN
        Ancestor AS A ON A.Id = D.Parent_Id
    WHERE
        (D.Parent_Id IS NOT NULL)
)
SELECT
    A.Id, A.TreeLevel, A.Branch, A.Name
FROM
    Ancestor AS A

Execution plan on DEV:

Execution plan on QA:

Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?


texts: Mysql query time massively affected by limit

texts:
I have this mysql query, where I am getting 250ms query time when the limit is 500.
If the limit is 1000, it's 1,5 seconds
if the limit is 1300 , it's 2,5 seconds
select hotel_code,
       ST_Distance_Sphere(
               point(longitude, latitude),
               point(-74.007227, 40.713054)
           ) * 0.000621371192 as distance
from hotel_parser_links hpl
where parser_code = 'ST'
having distance is not null
   and distance < 30

Here is the table structure, it has around 500k rows
create table hotel_parser_links
(
    id bigint unsigned auto_increment
        primary key,
    hotel_id bigint unsigned not null,
    parser_code varchar(255) not null,
    hotel_code varchar(255) not null,
    finished_parsing_at datetime null,
    started_parsing_at datetime null,
    city varchar(255) null,
    longitude decimal(11,8) null,
    latitude decimal(11,8) null,
    coordinate point not null
)
collate=utf8mb4_unicode_ci;

create spatial index hotel_parser_links_coordinate_index
    on hotel_parser_links (coordinate);

create index hotel_parser_links_hotel_code_index
    on hotel_parser_links (hotel_code);

create index hotel_parser_links_latitude_index
    on hotel_parser_links (latitude);

create index hotel_parser_links_longitude_index
    on hotel_parser_links (longitude);

create index hotel_parser_links_parser_code_index
    on hotel_parser_links (parser_code);

Here is the explain output
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                        | key                                  | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | hpl   | NULL       | ref  | hotel_parser_links_parser_code_index | hotel_parser_links_parser_code_index | 1022    | const | 270853 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+

Would appreciate any help :)


texts: Relay log read fail, relay or master log currupt ,How to repair?

texts:
I am running mysql InnoDB cluster 8.0.17(group replication)
During the repication the slave disk gets full and no space left at all the thread waits for the disk space to be freed at the same time this was happening the server restarted once server came back mysql error log shows relay log is corrupt and slave cannot rejoin.
my question is How can I repair the slave?

error log
 Disk is full writing ‘./ic-1-relay-bin-group_replication_applier.000039’ (OS errno 28 – No space left on device). Waiting for someone to free space… Retry in 60 secs. Message reprinted in 600 secs.
2019-08-21T19:02:37.095609Z 33 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘group_replication_applier’: Could not execute Write_rows event on table sbtest.sbtest3; Error writing file ‘/tmp/MLfd=28’ (OS errno 22019-08-22T06:55:36.766622Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 910
2019-08-22T06:55:43.510632Z 1 [Warning] [MY-012637] [InnoDB] 81920 bytes should have been written. Only 77824 bytes written. Retrying for the remaining bytes.
2019-08-22T06:55:43.510669Z 1 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2019-08-22T06:55:43.510683Z 1 [ERROR] [MY-012639] [InnoDB] Write to file ./#innodb_temp/temp_6.ibt failed at offset 0, 81920 bytes should have been written, only 77824 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
….
Error number 28 means ‘No space left on device’
2019-08-22T07:02:37.278732Z 1 [ERROR] [MY-012267] [InnoDB] Could not set the file size of ‘./ibtmp1’. Probably out of disk space
2019-08-22T07:02:37.278746Z 1 [ERROR] [MY-012926] [InnoDB] Unable to create the shared innodb_temporary.
2019-08-22T07:02:37.278764Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-08-22T07:02:37.776328Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2019-08-22T07:02:37.776504Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-08-22T07:02:37.779913Z 0 [ERROR] [MY-010119] [Server] Aborting
…
[MY-010818] [Server] Error reading GTIDs from relaylog: -1
Slave SQL for channel ‘group_replication_applier’: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. Error_code: MY-013121


texts: Adding extra total column that sums all column values on each row

texts:
I have the below query

SELECT 'Total_Invoices', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0

Output is:

I want to create an extra column that adds a fourth column with row totals for the three rows. I have tried the below to no avail:

;WITH CTE AS
(
SELECT * FROM
(
    SELECT  SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry1
    UNION ALL
    SELECT * FROM
(
    SELECT SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry2
    UNION ALL
    SELECT * FROM
(
    SELECT (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry3
)
SELECT *, Qry1 + Qry2 + Qry3 AS TotalSum
FROM CTE

How do I achieve this 4th column total?


texts: Is it possible to create a function for transactions that use more than one database?

texts:
As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have them go to the correct database as part of that single call?

BEGIN;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
           VALUES ('Lola', 'High', DEFAULT, DEFAULT, DEFAULT);
  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `charge`, `plea`, `sentence`, `create_time`, `update_time`, `hashID`)
           VALUES (000, 412056888, 0, NULL, NULL, NULL, DEFAULT, DEFAULT, LAST_INSERT_ID());
  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
           VALUES (1, 1, 1985, 34506028, 0, DEFAULT, DEFAULT, LAST_INSERT_ID());

COMMIT;

This might entail, for example, a function that inserts a value to a field only IF that field exists. For example, if the field "create_time" exists in all database.tables, then the value would be inserted in each of them. On the other hand, if "last_name" exists in only one of them, then it would be inserted only in that database.table.

I want a function to store which databases have which tables and use a set of fields that entails a union of all databases. Then when I insert values to fields, without me dictating which database gets what, the function should do that automatically.

Could I create such a function? How would I store it?


texts: Cumulate count between date with 0 when there are records for a given day

texts:
How could one write a Postgresql query that gives both daily and cumulative counts without gaps? E.g. if there is no data for a given day, it would show 0 as a daily count for that that day and the same cumulative sum as the previous day?
I think I'd need GENERATE_SERIES, but I don't know how to do it. I'm also not entirely sure if order by day asc rows between unbounded preceding and current row would work correctly always, but maybe not the biggest issue here.
I have tried to craft the query with PARTITION BY and
Let's say I write a table and add data such as
create table test
(
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    data int4,
    created_at timestamptz NOT NULL DEFAULT now()
);

insert into test(data, created_at) values(1, '2021-04-01');
insert into test(data, created_at) values(2, '2021-04-01');
insert into test(data, created_at) values(3, '2021-04-02');
insert into test(data, created_at) values(4, '2021-04-03');
insert into test(data, created_at) values(5, '2021-04-05');
insert into test(data, created_at) values(6, '2021-04-07');

and then create a queries such as
SELECT
  created_at as "Date",
  count(1) as "Daily count"
FROM test
WHERE created_at >= '2021-04-01'
  AND created_at <= '2021-04-30'
GROUP BY 1

giving

Date
Daily count

2021-04-01 00:00:00
2

2021-04-02 00:00:00
1

2021-04-03 00:00:00
1

2021-04-05 00:00:00
1

2021-04-07 00:00:00
1

with data as (
  select
    date_trunc('day', created_at) as day,
    count(1)
  from test
  group by 1
)
select
  day,
  running_total(count) over (order by day asc rows between unbounded preceding and current row)
from data

day
running_total

2021-04-01 00:00:00
2

2021-04-02 00:00:00
3

2021-04-03 00:00:00
4

2021-04-05 00:00:00
5

2021-04-07 00:00:00
6

But as noted, how could these two be combined without gaps on daily values? Somehow it feels I get close but bump into some (syntax) problem. Maybe those two queries are the simplest and cleanest examples of what I'm thinking.


texts: Why SELECT query is waiting on HADR_SYNC_COMMIT?

texts:
We have two AlwaysOn nodes in sync commit.
Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT.
What is the underlying process that causes it?

SQL Server 2016 SP1 CU5.
client sessions are using READ COMMITTED isolation level
Allow Snapshot Isolation and Is Read Committed Snapshot On are set to True. But snapshot isolation is not being used because client is enforcing READ COMMITTED.

Query example from primary replica:
select * from dbo.photoFileMetaData where FK_mediaId=@P0
Wait info: (780ms)HADR_SYNC_COMMIT
Status: suspended

Since SELECTs are not updating data, why would they need to wait on a replica sync commit?

Update 2018-08-30:
I was able to capture blocking when it was happening. Here is what is in blocking resource:
metadatalock subresource=QDS_INTERVAL_STABILITY classid=qds_interval dbid=7 lockPartition=0 id=lock1e78c13d580 mode=X


texts: I am using mysql 5.6 in my project since last 10 years. The problem is, database size has reached to over 14GB and so system performance is getting lower day by day. Since I only need to keep only last 3 years of data so I am looking for a way to delete data before that limit. How can I do it, is there any tool?


texts: presto from_unixtime to omit milliseconds & timezone string

texts:
This is my query
SELECT
from_unixtime(dispo_epoch,'Africa/Johannesburg')
FROM "analytics"."table1"
WHERE lead_id = 100000

Result returned is: 2020-03-31 12:37:43.000 Africa/Johannesburg
Result I want should be : 2020-03-31 12:37:43 without the milliseconds and timezone string


texts:
I used SQL Server configuration manager to enable TCP/IP on port 1433. I didn't have any problem but when I tried to enable another SQL service (different one) to listen on the same port I couldn't run it. When I stopped the first service there wasn't any problem. I also can run them together when only one of them has TCP enabled.

My question is why I can't run the both services together? I guess it has something to do with port but I thought that they just listen on this port so there shouldn't be problem.