What program to use to change settings on db servers? Having decided to set up a Master-Slave replication I found this article, which seems exactly what I want.

So I fired up the MySQL Console in Navicat to give everything a try, and the commands do nothing. So it seems one of these is the problem:

The MySQL Console is not the tool I want
The MySQL Console on Navicat is imperfect
I am doing it wrong

This is the first step in the article I linked:

  /sbin/ifconfig

Does nothing on the remote server connection (using the Console in Navicat) but works fine on the MySQL Console on the WAMP Server on my local machine.

UPDATE:

next day - clear head - realize what I was trying to do is probably not possible and not necessary. Web site is hosted on some rackspace servers in California, and I doubt they want me messing around with their server settings.

I was trying to set up separate databases so that the write-heavy backend did not interfere with reads in the front end. This morning I reworked the calls which were causing table locks & now the slow backend queries should not lock any table required by the front end.

So basically problem solved. This why I should only post on here in the morning, not after 8-10 hours coding.

As a MySQL DBA, may I be so bold as to suggest my favorite tool?

The mysql client program itself

There are also a variety of startup options for the client program.

Examples:

-h hostname or host IP
-P port number
-u username
-p password
-e allows execution of a semicolon delimited list of SQL queries inside double quotes
--skip-column-names will suppress the column heading for output of SQL queries

You see all the options by doing this

mysql --help --verbose

You can see global variables and status in the mysql instance using SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; You can also see session variables and status in the db connection using SHOW VARIABLES; and SHOW STATUS;

Once you have successfully connected to the mysql instance, you have a wide variety of session and server options that you can set for the current session or permamnently for all new sessions.


What's the difference of setting up CPU affinity between by Server's Task Manager and by SSMS? What's the difference of setting up CPU affinity between by Server's Task Manager and by SSMS?
Which one is more reliable?

Thanks in advance.

Update based on comment from Max Vernon.

  Setting the affinity mask with Task Manager will only have effect
  until SQL Server is restarted. Using sp_configure will ensure the
  change remains effective across restarts.

Using task manager make sure you pick the right process especially if you have multiple SQL instance. With SSMS it is obvious which one you are changing.

Microsoft recommend to use:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 38;
RECONFIGURE;
GO

Details here.

Word of caution from the same article.

  Do not configure CPU affinity in the Windows operating system and also
  configure the affinity mask in SQL Server. These settings are
  attempting to achieve the same result, and if the configurations are
  inconsistent, you may have unpredictable results. SQL Server CPU
  affinity is best configured using the sp_configure option in SQL
  Server.


Increment of integer PK values by 1 doesn't throw duplicate key error. Why? As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity after each row. What happens inside? Is this part of the standard of just SQL Server specific behavior? Thanks!

DECLARE @T TABLE(
    x INT PRIMARY KEY
);

INSERT INTO @T VALUES (1)
INSERT INTO @T VALUES (2)

UPDATE @T SET x = x + 1;

-- DUPLICATE KEY ERROR EXPECTED.

SELECT * FROM @T;

--- VALUES HAVE BEEN INCREMENTED. NO ERROR THROWN.

The checks (about UNIQUE, PRIMARY KEY and other, NOT NULL and CHECK constraints) are done after the whole UPDATE statement has finished. This is the expected behaviour in SQL. Operations/statements are performed on sets (of rows), not on each row individually.(*)

And while for NOT NULL and CHECK constraints, it doesn't matter if the checking is done after each row change or after the whole statement, for unique constraints (UNIQUE and PRIMARY KEY) it does matter.

In fact, the SQL standard has 2 options. The checks are either done after the statement (when the constraint is NOT DEFERRABLE) or even later, at the end of the transaction (when the constraint is DEFERRABLE) and the statement is part of a transaction.

SQL-Server supports only NOT DEFERRABLE constraints, so checks are after every statement and not at the end of transactions.

(*)
Perhaps you have been used to MySQL, which shows this non-standard behaviour, checking constraints after each row update (or insert.) See the SQL-Fiddle where the update fails (if you change the DBMS from SQL-Server to MySQL.)


Help me to better design my query so I can gain more performance I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed conception therefore this query can be written much better.
So I am asking you to help me redesign this query to get better performance.
My running database system is  Sql Server 2008

declare @od_datuma datetime
declare @do_datuma datetime

set @od_datuma='2011-12-01'
set @do_datuma='2011-12-31'

select
doc.PAR_ID
,sdo.art_id
,MIN(doc.id) as doc_id
from
dokumenti
inner join DOC on dokumenti.tip=DOC.TIP
inner join SDO on DOC.ID=SDO.DOC_ID
left  outer join (
    select par_id,art_id from dokumenti
            inner join DOC on dokumenti.tip=DOC.TIP
            inner join SDO on DOC.ID=SDO.DOC_ID
            where   dokumenti.NABAVA =1 and
                    dokumenti.KOL_UL=1 and
                    DOC.DATUM<@od_datuma
                )  as stari_uazi on DOC.PAR_ID=stari_uazi.PAR_ID and SDO.ART_ID=stari_uazi.ART_ID
where
dokumenti.NABAVA =1 and
dokumenti.KOL_UL=1 and
stari_uazi.ART_ID is null and
doc.datum between @od_datuma and @do_datuma
group by doc.PAR_ID,sdo.art_id

The first this I would try is to add
where doc.datum>=@od_datuma
       and  doc.datum <=@do_datuma
because the having clause. Maybe it will give a hint to the query planner.
Then the big question is: why the outer join with the subquery?
Does the " where .. stari_uazi.ART_ID is null " mean that you want to exclude all the rows returning from the subquery?
I suspect the subquery is the real killer because the search for DOC is not bounded ( DOC.DATUM <@od_datuma ), so it might fetch a huge number of rows, which have not real purpose since the parent query excludes these rows anyway..

My guess is that if you suppress the subquery ( left outer join .. ) you will have something very close from what you want, much faster. If you need to suppress some rows from the result set you can put them in a temporary table and delete them afterwards, this might be faster than the outer join subquery.

Update: so if you want to keep only the rows between @od / @do _datum, remove the left join and add the condition where DOC.DATUM between @od_ and @do_
You will have the same result and it will be much faster.


Heroku PostgresApp - Fails to start with edited postgresql.conf I am trying to tune my local pg running from the Heroku Postgress App.

I am seeing strange errors in the OSX console whenever I change some of the internal settings:

 PM Postgres[78709]: Could not connect the action selectPostgresStatus: to target of class AppDelegate

In the above case, setting wal_buffers to any value other than the default in the conf results in PG being inaccessible.

Other config settings have a similar effect.

Is there a trick to configuring Postgres.app?

This is probably a kernel resources issue. Check the PostgreSQL log files to see for sure. They should be in the pg_log directory, alongside postgresql.conf.

The PostgreSQL documentation on kernel resources has a section on increasing shared memory limits for Mac OS X.

Note that increasing some settings in postgresql.conf also requires you to increase shared_buffers. This is usually documented in the info about that configuration option on the PostgreSQL site, and you'll be told in the PostgreSQL logs if you need to increase shared_buffers.


SQL Server - Varbinary Column - Extremely Slow Statistics Update I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table had essentially "jammed" on an insert statement.

Checking sys.dm_os_waiting_tasks showed that the insert statement had triggered a statistics update, and that this statistics update was taking a very long time. (17 minutes).

Here's the statement we found running:

SELECT StatMan([SC0], [LC0]) FROM
   (SELECT TOP 100 PERCENT CONVERT([varbinary](200),
        SUBSTRING ([Data], 1, 100)++substring([Data],
        case when LEN([Data])<=200 then 101 else
        LEN([Data])-99 end, 100)) AS [SC0],
        datalength([Data]) AS [LC0]
    FROM [FileSystem].[FileData] WITH
    (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

There are roughly 2000 rows in this table, here's what it looks like:

CREATE TABLE [FileSystem].[FileData]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__FileData__Id__09DE7BCC] DEFAULT     (newsequentialid()),
[Data] [varbinary] (max) NULL,
[FileHash] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileSize] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [FileSystem].[FileData] ADD CONSTRAINT [PK_FileData] PRIMARY KEY     CLUSTERED  ([Id]) WITH (STATISTICS_NORECOMPUTE=ON) ON [PRIMARY]
GO

We're aware this table is pretty weird, and we are careful to only every address it with a clustered index seek.

For the moment, we've simply disabled automatic statistic generation for this table, but I'm wondering if that's really best practice. Will performance eventually become a problem without up-to-date statistics (bear in mind, this table is only every addressed by it's clustered index)?

Update:
Okay I'm pretty sure we've worked out what was causing the statistic to be generated:

GO CREATE PROCEDURE [FileSystem].[FileData_AppendNewData_Easy]
    (@fileDataId  uniqueidentifier )
WITH EXECUTE AS CALLER AS
BEGIN
   declare @testValue varbinary(max);

   set @testValue = 0xabcedf012439;

   Update FileSystem.FileData
   set Data.Write(@testValue, null, null)
   where Id = @fileDataId ;
END

This is a simplified version of the procedure that was causing the issue, it seems Data.Write causes some kind of implicit predicate on the data column? Well that solves that mystery, although I'm still not sure what impact disabling the statistics for this table might have, can anyone comment on that?

This can be addressed in two additional ways:

Enable Asynchronous Auto Update Statistics and the database level (not my preference). If your production is stuck because due to this issue then this is the quick fix as it is instantaneous but it globally affects the database and can produce sub-par query plans since the plan is generated prior to the stats update.

ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

Remove the auto generate stat and replace it with a manually created one with no-recompute. The downside is that, while this is a surgical approach, it takes some time to create the statistic and will cause blocking.

DROP STATISTICS [dbo].[TableName].[_WA_Sys_00000004_7D78A4E7]
GO
CREATE STATISTICS [_Manual_Stat_Name] ON [dbo].[TableName]([Column]) WITH NORECOMPUTE;
GO

When we were forced to deal with a similar issue we went with option 1 first and later option 2. During a "green zone" we reverted the database wide setting and then replaced the column's statistic with the manually created NORECOMPUTE one.


Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving? Context:

Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the master. The standby's recovery.conf doesn't contain a 'restore_command' entry (for restoring from WAL journal files), but does contain a 'primary_conninfo' string (for Streaming Replication).

Question:

If I start the standby again after a day of changes on the master. Will it "catch up" (eventually come into a state which mirrors the master) using only Streaming Replication? Or do I have to enable WAL file archiving and let it apply files archived during the outage to ensure currency?

I've checked the WAL archiving/streaming replication doc here, and it says that you don't have to enable both WAL archiving and Streaming Replication, but it is unclear whether or not catch-up will happen without WAL file archiving being enabled.

Thanks!

Yes, it will catch up, using streaming only, if (and only if), the number of WAL segments generated since the last update on the standby is less than the value of wal_keep_segments in postgresql.conf.  This is covered in this section of the documentation: Replication


SQL Server mdf file too large I have a .mdf file that is 10gb. This is the max file size. How can I reduce the file size? I do not need the data, I just need to be able to write new data to the database.

I have deleted a ton of rows, but the file size has not changed at all.

Any help would be appreciated!

Also, my log file grew by about 50gb when I deleted the rows from the .mdf. How do I make that smaller as well?

You have to shrink database so the deleting the data takes place
Right Click on your database , Tasks => Shrink => Files
In shrink action choose the second Option and enter your file ideal size
You're done
:)


mysql copying to tmp table duration I have a web site which started to have performance issues because of a mysql query which is executed for each request.

I have the following table structure.

Post table (125000+ records)
id: primary key,
index on thread_id and type columns

Thread table (48000+ records)
id : primary key

I'm running the following query

select t.id, t.subject, count(distinct p.id) as postCount
                from thread t
                inner join post p on p.thread_id = t.id
                where p.posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                and p.type = 0
                group by p.thread_id
                order by max(p.posted_on) desc
                limit 0, 25

mysql explain result :

Mysql profile :

As you can see from profiling response main problem is copying the tmp table part. I tried increasing the max_heap_table_size and tmp_table_size parameters to 256MB.

Rather than tweaking configs for query optimizing internals, do the following

SUGGESTION #1

You may want to refactor the query as follows

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc
limit 0, 25;

SUGGESTION #2

Create a compound index for the subquery

ALTER TABLE post
    DROP INDEX type,
    ADD INDEX type_posted_on_ndx (type,posted_on)
;

GIVE IT A TRY !!!

ALTERNATE QUERY

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
    limit 0,25
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc;


Simple DELETE, but complicated execution plan When I run this delete:

DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870

...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and primary key. But (according to the execution plan) it seems to be hitting 361,190 rows and using other indexes. The table does have a field with an XML data type (in case that affects this DELETE).

Any ideas why and how I can speed up this DELETE?

Execution Plan here:
http://sharetext.org/qwDY
Table schema here:
http://sharetext.org/Vl9j

thanks

The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below.

For the other two nonclustered indexes, the optimizer has decided it is best to save the keys of these indexes to a tempdb worktable (the Eager Spool), then play the spool twice, sorting by the index keys to promote a sequential access pattern.

The final sequence of operations is concerned with maintaining the primary and secondary xml indexes, which were not included in your DDL script:

There is not much to be done about this. Nonclustered indexes and xml indexes must be kept synchronized with the data in the base table. The cost of maintaining such indexes is part of the trade-off you make when creating extra indexes on a table.

That said, the xml indexes are particularly problematic. It is very hard for the optimizer to accurately assess how many rows will qualify in this situation. In fact, it wildly over-estimates for the xml index, resulting in almost 12GB of memory being granted for this query (though only 28MB is used at runtime):

You could consider performing the deletion in smaller batches, hoping to reduce the impact of the excessive memory grant.

You could also test the performance of a plan without the sorts using OPTION (QUERYTRACEON 8795). This is an undocumented trace flag so you should only try it on a development or test system, never in production. If the resulting plan is much faster, you could capture the plan XML and use it to create a Plan Guide for the production query.


Limit max CPU usage SQL SERVER with WSRM I have a physical server running one instance of SQL Server.

I notice that quite often this server is running at 100% CPU usage.

My IT team is not happy about this, and suggested we reserve 2 of the 32 cores for the OS.

This works great, now max usage peak just under 90%.  Additionally, slow data retrieval from various users is no longer reported.

Is there any reason NOT to use WSRM (Windows System Resource Manager) in this way - instead of SQL Resource Governor?

Is there any reason NOT to use the approach you've defined? Absolutely.

Imagine you had bought a car - a car that when you hit 50MPH the engine starts to overheat. Would your reaction to this situation be to artificially limit the car to 49MPH, or to find out what the fault is with the engine?

Why should you limit your car to 49MPH? The manufacturer stated that it could drive as fast as 80MPH - you like to drive your car fast so you want to get it to this speed - if it wasn't for that damn overheating issue.

The car you bought was also really, really expensive. Each engine cylinder needs to be utilised to the max so you aren't wasting that money!

By artificially limiting SQL Servers access to the CPU, you are missing out on performance. You may have temporarily resolved the performance issues by ensuring the CPU is available for the OS to use, but you haven't answered the real question - WHY is SQL Server using 100% of the CPU?

My advice is as follows:

Find out what the real issue is, and fix it. Don't cover the issue up with what is effectively a kludge. The issue WILL reappear and smack you in the face down the line when the workload of the server naturally increases with growth.

As a temporary fix, resource governor can be used to lower the CPU used, UNTIL YOU FIND THE REAL PROBLEM.


Counting results for cross join How I can get count results with cross join?

Example cross join:

SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2

You could do this:

SELECT COUNT(*) FROM table1 t1 CROSS JOIN table1 t2;

or this format which will allow you to copy and paste any query without rewriting it and use it as a subquery:

SELECT COUNT(*) FROM (
  SELECT t1.firstname, t2.lastname
  FROM table1 t1 CROSS JOIN table1 t2
) t3;


Display all fields with null values in a particular row in SQL Server I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table.

Some thing like:

select 'all columns with NULL'
   from table_xyz
   where primary_key='pk_row2';

I use SQL Server 2012. Please help achieve this.

SQL as a language allows you to easily dictate which rows to include in output, but not which columns. For this you'll need dynamic SQL (and a consuming app which is equally dynamic). An example, given this table and data:

USE tempdb;
GO

CREATE TABLE dbo.blat(pk varchar(32) NOT NULL, a int, b int,
  CONSRAINT pk_blat PRIMARY KEY (pk));

INSERT dbo.blat(pk,a,b) VALUES
  ('key 1',1,NULL),
  ('key 2',1,1),
  ('key 3',NULL,1),
  ('key 4',NULL,NULL);

One solution:

-- input param:
DECLARE @pk varchar(32) = 'key 4';

DECLARE @sql nvarchar(max) = N'SELECT pk';

SELECT @sql +=
    CASE WHEN a IS NULL THEN N',a' ELSE N'' END
  + CASE WHEN b IS NULL THEN N',b' ELSE N'' END
FROM dbo.blat WHERE pk = @pk;

SET @sql += N' FROM dbo.blat WHERE pk = @pk;';

EXEC sys.sp_executesql @sql, N'@pk varchar(32)', @pk;

Try it with all four combinations. Don't forget to clean up:

DROP TABLE dbo.blat;


SQL Server 2016 - Mirroring Im a complete beginner with SQL server. I'm trying to train myself using the AdventureWorks2016 sample DB on an SQL server 2016. I'm trying to setup mirroring between VM1/SQLServerInstance1 and VM2/SQLServerInstance2. 2 problems which I'm facing:

The option to backup Transaction logs is missing from properties window of the database (Right click on DB > Properties > Backup).
Mirror option is greyed out with the following message: "Database mirroring is not supported for a database with a MEMORY_OPTIMIZED_DATA filegroup. Use AlwaysOn Availability Groups instead". I was hoping to stay away from AlwaysOn Availability groups by using Mirroring.

I've tried googling and this is my last resort. Help would be greatly appreciated.

UPDATE

Issue 1 (No transaction log backup option), was resolved by Changing Recovery mode to Full from simple (Right Click on DB name > Properties > Options > Recovery Mode).

•Mirror option is greyed out with the following message: "Database mirroring is not supported for a database with a MEMORY_OPTIMIZED_DATA filegroup. Use AlwaysOn Availability Groups instead". I was hoping to stay away from AlwaysOn Availability groups by using Mirroring.

The error message is pretty self explanatory. Mirroring doesn't support memory optimized objects (In Memory OLTP you may see it called [IMOLTP] or Hekaton).

Mirroring as noted in the comments is deprecated - it's only AGs going forward. Since 2016 supports a multitude of new options in terms of clustering and availability groups, you should be able to do the exact same thing with AGs as you can with mirroring - albeit a few more steps in setup and configuration.

If you want to use mirroring, then I would suggest using a blank, new, database and configuring it there. Also, don't rely on the GUI.

  The issue with them (correct me if I'm wrong) is that they work with High Availability Cluster service.

Yes, you're correct; they do require Windows Server Failover Clustering. However, I don't see how that's a show stopper. Does it take a little extra configuration - yes. Does it require a little more understanding about your actual failure scenarios and overall architecture - sure... but it also beats having to deal with mirroring.

In fact, there were many, many, updates done to availability groups in 2016 that it is much more performant than mirroring.


Why can't a committed transaction be undone Once a transaction has been committed, we cannot undo its effect by aborting it.
We have the log file that has all information to undo a committed transaction, so why is this not possible? We should be able do the same with a committed transaction as we can with an aborted one.

You can achieve that result in Oracle using flashback (https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131).
IBM DB2 has a somewhat similar feature.
You could use database snapshots in older versions of SQL Server (https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15) and newer versions have temporal tables (https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15).


How to drop multiple tables with common prefix in one query? I'm using  Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query?

something like that table names:

LG_001_01_STLINE,
LG_001_02_STFICHE

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course there are potential gotchas, for example if these tables have foreign key relationships, you'll either need to drop them first, or arrange the output to drop the tables in a certain order.

To just get the list of tables, use:

SELECT s.name, t.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name LIKE 'LG_001%';


what monitor before applying changes on the server? - looking for a basic answer - starting point only these settings were decision of our development team:

there are 2 settings:

1 - max degree of parallelism

2 - optimize for ad hoc workloads

On OUR_DEV_SERVER:

1.  Run the following script:
--//activate ‘Optimize for ad hoc workloads’
sp_configure 'show advanced options',1
GO
reconfigure
GO

--// Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.
sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure
go

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

what should I monitor BEFORE and AFTER the changes in order to conclude whether these changes are worth applying in LIVE?

For how long should I monitor if the purpose of the monitoring is ONLY to prove THESE changes above?

I have put a simple question, expecting just a starting point. I have nothing in place at the moment, and have not started this task so far.

I prefer not to rely on third party tools - for the purpose of this exercise.

I believe starting with Baseline Data would be good to monitor before and after changes.

Information which you might want to capture like:

Basic Information
System Usage
File and Database Sizing Information
Wait Statistics   etc..

If you do not want to use third party tools, you should start looking into collecting above data and when as explained 5 Reasons You Must Start Capturing Baseline Data

Also, in order to set these baselines, you will need to create a database and can capture for duration according to the workload behaviors as explained in
Capturing Baselines on Production SQL Servers.

Also refer to this MSDN forum discussion on same.


Input to be printed in different output format I have a value (e.g. ORACLE) and I would like to print each letter on an individual line using an Oracle query.

Input value:

ORACLE

Output:

O
R
A
C
L
E

With PL/SQL find the length of string first,use substring to extract each character and print with dbms_output

BEGIN
  FOR ctr in 1..LENGTH('ORACLE')
  LOOP
   DBMS_OUTPUT.PUT_LINE(SUBSTR('ORACLE',ctr,1));
  END LOOP;
END;

or with declaring variables

DECLARE
l_str VARCHAR2(20):='ORACLE';
l_len INTEGER;
BEGIN
  l_len:=LENGTH(l_str);
  FOR ctr IN 1..l_len
   LOOP
    DBMS_OUTPUT.PUT_LINE(SUBSTR(l_str,ctr,1));
   END LOOP;
END;

Here is running pl/sql in sql*plus

SQL> set serveroutput on
SQL> DECLARE
  2  l_str VARCHAR2(20):='ORACLE';
  3  l_len INTEGER;
  4  BEGIN
  5    l_len:=LENGTH(l_str);
  6    DBMS_OUTPUT.PUT_LINE('Output:');
  7    FOR ctr IN 1..l_len
  8     LOOP
  9      DBMS_OUTPUT.PUT_LINE(SUBSTR(l_str,ctr,1));
 10     END LOOP;
 11  END;
 12  /
Output:
O
R
A
C
L
E

PL/SQL procedure successfully completed.

SQL>


sql query to check whether multiple entries exist I have a table with the following fileds.Here table name is det
Name ID
A     1
B     2
C     1
D     3

I need a querry to check if multiple entry of name is there for single id. Here iam expecting result like
Name ID
A     1
c     1

WITH cte AS ( SELECT Name, ID, COUNT(ID) OVER (PARTITION BY ID) cnt
              FROM source_table )
SELECT Name, ID
FROM cte
WHERE cnt > 1;

SELECT DISTINCT t1.*
FROM source_table t1
JOIN source_table t2 ON t1.id = t2.id
                    AND t1.Name != t2.Name


Update column with value of another column or another column With PostgreSQL (I'm using the version 9.1) is it possible to do mass update with a single query a column value with the value of another column, but if the other column value is null, to use the value of a third column, and if the third one is absent to use the current datetime (all the column have type timestamp)

I need to change

columnA columnB columnC
null    foo     bar
null    null    baz
null    null    null

to

columnA columnB columnC
foo     foo     bar
baz     null    baz
quz     null    null

where quz is the current datetime.

You can use COALESCE() function to do the update:

UPDATE ...
SET columnA = COALESCE(columnB, columnC, now());

COALESCE will return the first non-null value in the list that you provide.


distinct select from 2 databases I have 2 databases with the same structure massfacturation and massfacturation_old and I want to simply combine 2 queries to extract a count like:

> select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation_old.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       160 | 2017 |
|       262 | 2018 |
|       165 | 2019 |
+-----------+------+
3 rows in set (0.00 sec)

and on the new one

> select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       170 | 2019 |
+-----------+------+
1 row in set (0.01 sec)

The idea is to end up with one result for 2017, 2018, 2019, combining both results.

Solution

Ended up with:

> select count(distinct(sciper)), year FROM (SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation.jobs UNION SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation_old.jobs) AS j GROUP BY year;
+-------------------------+------+
| count(distinct(sciper)) | year |
+-------------------------+------+
|                     160 | 2017 |
|                     262 | 2018 |
|                     188 | 2019 |
+-------------------------+------+
3 rows in set (0.00 sec)

Thanks !

Use sub-query, consider the two outputs are temp1 and temp2:

SELECT
    sum( usercount ),
    `year`
FROM
    ( SELECT * FROM  temp1  UNION ALL SELECT * FROM  temp2  ) AS temp1
GROUP BY
    `year`

Edit   As @ypercubeᵀᴹ mentioned in the comment, UNION ALL will avoid removing the duplicated rows from temp1 and temp2.

Also if your tb and tb_old use the same sciper for the same user,
you may need to UNION before GROUP BY

SELECT
    `year`,
    count( sciper ) AS usercount
FROM
    ( SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation_old
        UNION
        SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation ) AS temp1
GROUP BY
    `year`

In the above query, for every year and sciper combination, the temp1 will only have one row.


Reference a non primary key in another table in postgresql I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.

Postgres supports foreign keys to unique indexes (a primary key is a special case of a unique key). From the documentation

  The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table


Table for optional parent/child relationship Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.

The item can either belong to a parent or child and not both.

I have other tables that are similar to Item and they too can belong to either a Parent or Child.

Should I simply just add 2 nullable FK to them?

Can I enforce that either a Parent or Child must exists using the db?

The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.

In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:

  either a Parent or Child must exists

... you can add a simple CHECK constraint:

CHECK (a IS NOT NULL OR b IS NOT NULL)

Would demand at least one NOT NULL column - but also allow that both parent_id and child_id exist. If you want to disallow that, too, make it:

CHECK (a IS NOT NULL AND b IS NULL OR b IS NOT NULL AND a IS NULL)


Should I use data type SERIAL for table id columns? I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is appropriate for tables that may never get big.

Is there any reason to avoid using SERIAL type for a table id column?

Are there any obvious caveats?

The only real concern would be size.

If a table is really small, why bloat it ? For example, if you have a table will never surpass 255 rows, use TINYINT UNSIGNED for id. No need to bloat the table's column up to 8 times bigger. This would also apply to the primary key.

After loading a table with data, you should run this

SELECT id FROM tablename PROCEDURE ANALYSE();

For any table < 4,294,967,296 rows, PROCEDURE ANALYSE() will never recommend a BIGINT. In terms of byte width, smaller numeric keys are always faster to process than larger numeric keys.

What will be further victimized would be indexes. Why ?

In the Clustered Index (a.k.a. gen_clust_index), row data and BTREE for Primary Key occupy the same space
InnoDB has a fixed size for an InnoDB Page

In light of these two things, the larger the Primary Key, the more space is needed and the more likely extra InnoDB pages are needed.

Therefore, for a populated table, my suggestion would be to let PROCEDURE ANALYSE() tell the correct dataytpe. For an empty table, try to forecast the number of value to expect and this set the data type to the following:

id < POWER(256,1) (256), TINYINT UNSIGNED
id < POWER(256,2) (65536), SMALLINT UNSIGNED
id < POWER(256,3) (16777216), MEDIUMINT UNSIGNED
id < POWER(256,4) (4294967296), INT UNSIGNED


How to handle billions of rows in MySQL for biometric project? My MySQL table looks like this.

animal_id (mediumint) | dna_hash (binary 10)

Currently, we have about 2 billions different dna_hash and it will be increasing every day.

We need to search the animal by dna_hash. So, I have an index on dna_hash.

Currently, I'm starting to worry because after I imported around 20 million rows, my index on dna_hash is over 500MB. If I keep on adding, the final index size will be over 50GB which is very expensive to operate.

So, I just wondering if there are any other possible way to reduce the index size? Or, should I just upgrade my hardware.

Thanks.

Why you are hosed

So, you need to map a 10-byte hash to a 3-byte id, correct?  And there are 2B+ of them?  Nothing else in the table?  (Adding to the table increases the numbers below.)

CREATE TABLE HashToAnimal (
    hash BINARY(10) NOT NULL,
    animal_id MEDIUMINT UNSIGNED NOT NULL,
    PRIMARY KEY (hash),
    INDEX(animal_id)  -- might you need this for maintenance?
) ENGINE=InnoDB.

The hash is very random.  So there are two extreme situations (and not much in between):

The table lives in the buffer_pool.  Accesses would not incur a disk hit (very fast).  2B rows would occupy about 100GB.  This implies more than 100GB of RAM.  (No it won't fit in 50GB; there are several flavors of overhead.)
The buffer_pool is much too small to hold the table.  Now, each SELECT will need a disk hit because caching is 'impossible'.  How many IOPS can you afford on Amazon?  Pessimistically divide that by 3 to get how fast you might be able to fetch rows.

You quoted 1000 fetches took 10 seconds.  That sounds like a cold cache and conventional drives.  100 disk hits per second is a good Rule of Thumb for conventional disks.  (I thought Amazon was all SSD?)  That 10 seconds will get faster as more of the table is cached -- if the buffer_pool is big.  Eventually there won't be any disk hits and my "very fast" will come true.

Another note:  INSERTing into this table has similar caching considerations.  Each insert into the BTree starting with hash (whether PRIMARY KEY or secondary) is very random.  It will be a disk hit if the table is much bigger than the buffer_pool; otherwise it will eventually be cached and fast.

Note:  If Amazon does certain kinds of maintenance, the cache may suddenly become 'cold'.

Hashes, MD5s, GUIDs, UUIDs, etc. are all terrible to use.  You have to pay for either lots of RAM or lots of IOPs.

Thinking out of the box

You usually expect not to get any animals, correct?  So, the preferred test is one of 'existence', not 'fetch'?  So a bit string of 2**80 bits would say which hashes have been seen.  Well, this is really impractical.

Here's a schema that might take 5GB for a table that lets you test the existence of 2B different hashes:

Let x be the first 27 bits of the hash.  Let y be the next 8 bits.

CREATE TABLE CheckHash (
        x BINARY(3) NOT NULL,
        y BLOB NOT NULL,
        PRIMARY KEY(x)
    ) ENGINE=InnoDB;

INSERTing is a bit complicated.  Fetch the row for x, see if y is already in the y column; if not add it.  (If no x row, add one.)

Checking is SELECTing WHERE x=? and looking through y.

There will be some "collisions", but that is OK.

There will be a small percentage of false hits (15%?); you then use the full hash to look them up in the HashToAnimal.  Assuming the percentage is small enough, these disk hits should be small enough to be acceptable.

The goal is to be able to have the smaller CheckHash fully cached, but not worry that HashToAnimal is not well cached.

This table will grow gradually; 4B hashes may take only 8GB.

(If you get beyond, say, 5B hashes, you should rebuild the table with 28 instead of 27.  It's a tradeoff between space overhead versus more CPU time for scanning the blob.)

(Surely there are other techniques.)


Selecting a range of users active in the past month, but not in the past week I have two tables, users and meetings. I'm trying to select all the users who have NOT made a meeting in the past week, but have made a meeting in the past month.

SELECT  u.id, u.given_name, u.family_name, u.email
    FROM users u
    JOIN meetings m ON (u.id != m.user_id)
    AND m.created_at BETWEEN %s and %s
    AND u.state ='active'
    AND u.id in (SELECT user_id
                 from meetings
               where created_at BETWEEN %s and %s)
    GROUP BY u.id, u.given_name, u.family_name, u.email

This is using the psycopg2 adapter, so %s will be replaced by a datetime object. The %s replacement is in the order of:

7 days ago, today, a month ago, 8 days ago.

Also, the reason i did u.id != m.useridis because the userid will not be in the meetings table if the user has not organized a meeting in the last week.

This doesn't seem to be working, as I am still getting people who have organized a meeting in the past week.

You can write a CTE[1] to compute a date interval, take a look:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT * FROM date_list;

To use with your query, put date_list on there FROM clause when adjust your filter, eg:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT  u.id, u.given_name, u.family_name, u.email
FROM users u, last_month_minus_thisweek
JOIN meetings m ON (u.id != m.user_id)
AND m.created_at BETWEEN %s and %s
AND u.state ='active'
AND u.id in (SELECT user_id
             from meetings
           where created_at BETWEEN date_list.start_date and date_list.end_date)
GROUP BY u.id, u.given_name, u.family_name, u.email;

I Hope it helps.

[1] https://www.postgresql.org/docs/current/static/queries-with.html


Does the MySQL primary key affect the efficiency of a SELECT query? Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of perhaps a binary search; but does MySQL actually take advantage of this option?

If so, is the primary key required to be in order? And while we're at this question, if a table is in order of a particular column--whether primary or not--would that aid in the SELECT process if that particular column was specified in the WHERE clause?

If not, what are some of the precautions I could take in building a speed-efficient infrastructure for my database?
For example, I have a table of articles where the first two columns are: an 'ID' as the primary key, and an alphanumeric ID for the URL. To show related articles, I have a table with two columns for relating article IDs. I have noticed that multi-table SELECT query is much more faster when the "related" table logs the primary key ('ID') rather than the alphanumeric ID (which is not primary nor numeric).

All things InooDB, the PRIMARY KEY has a major role. As ypercube mentioned towards end of answer, InnoDB uses a clustered key structure, and the key used for clustering is the PRIMARY KEY.

It is very important to have as small footprint for PRIMARY KEY as possible: the depth of the index is the minimum payment you have to pay so as to get to a leaf node. The depth of an InnoDB PRIMARY KEY is the starting payment for any index search.

My general advice is to use AUTO_INCREMENT primary keys for InnoDB. Here are three blog posts of mine which support this:

Reasons to use AUTO_INCREMENT columns on InnoDB
Simple guideline for choosing appropriate InnoDB PRIMARY KEYs
Reducing locks by narrowing primary key


One MySQL user from 2 hosts not on same subnet I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original server. So far, everything is fine except my slave user is only granted access from one host: 10.10.10.10 . The second slave I'd like to bring up has the IP address 10.10.10.20 and I can't allow access across the entire subnet ( I also can't change the internal IP addresses).

So, I'd like to update the hosts the slaves can access the master to both IP addresses and can seem to figure out how.

Any help is greatly appreciated.

Look at the mysql:user table  The user is likely enabled only from one host (by name or IP).  You can do one of two things:

Add a second occurrence of the user with access from the second host. (Most secure.)
Change the host criteria to be less restrictive. (Less secure.)

See the MySQL Access Restrictions documentation for possible less restrictive host specifications.


The log in this backup set is too recent, but it's from before the last full backup! I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS GUI. The full database backup starts at 12 AM daily, and usually finishes before 2 AM.

First, I restore the full backup with NORECOVERY.:

USE [master]
RESTORE DATABASE [DBNAME] FROM  DISK = N'X:\path\to\DBNAME.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

This completes successfully.

Then, I grab the oldest transaction log and run the following:

RESTORE LOG DBNAME FROM DISK = 'X:\path\to\OldestTransactionLog.trn' WITH NORECOVERY;

However, I get the following error:

Msg 4305, Level 16, State 1, Line 10
The log in this backup set begins at LSN 421814812000000025600001, which is too recent to apply to the database. An earlier log backup that includes LSN 421787067000000013800001 can be restored.
Msg 3013, Level 16, State 1, Line 10
RESTORE LOG is terminating abnormally.

I don't understand why this is too recent!

My backup takes place at 12 AM and finishes by 2 AM.

At first I tried the transaction log from 12 AM, then seeing the "too recent" message, tried the oldest one I have, from 11:30 AM yesterday.

So this transaction log should be half a day older than the most recent full database backup.

Am I missing something here? How can this be too recent?

Any help is appreciated - and this is not on a production system, don't worry!

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence.

So possible cause here being that you are not restoring transaction log backups in order :

Also, you can use the below script to determine for restoring the available backups in order:as mentioned here

    USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.TRN'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd


DBF file compatibility between Oracle 12.2.0.1 and 11.2.0.4? We have a plan to make a copy of our PRD environment into our QA environment to get update data for testing by means of transporting the PRD DBF files over into our QA system.

However there is a discrepancy between the Oracle versions on both systems, on our PRD the Oracle database is 11.2.0.4 whereas in our QA environment Oracle database is newer at 12.2.0.1.

Would the QA environment having a more recent number be able to read DBF data files generated from an older version?

We will be moving all the control files along with the data files too.

Any advice from someone who've pulled such a stun before much appreciated :)

I assume that you will eventually upgrade production to 12.2. If it were me, I would have this scripted. Drop the development database, change /etc/oratab file on the development database to be 11.2.0.4 if you are using Linux/Unix, otherwise drop recreate the windows service using oradim. Clone from prod to development as 11.2.0.4. Do a silent upgrade of the development database using dbca, and be ready to repeat as you get closer to the migration date. When you practice the upgrade procedure enough to work out the bugs, and have tested the performance enough to know that you won't have performance issues in production, you are ready to do the upgrade in prod.

There are some automatic tuning parameters in 12.2 that you may need to disable, depending on how your application performs.


Design: Fitting variable hierarchy of text files into a relational db I have a large data corpus (several TB) made up of data gathered through customer support queries. I've been asked to set up a database to make this data more accessible, but as I'm kind of new to database design, I'm stuck on what may be a fairly trivial design decision.

One of the primary components of this data is a copy of the software configuration for each user's install. The configuration consists of N plain text files, in a fixed directory structure, but each directory can contain a number of different config files depending on how the user has configured their install.

Example hierarchy:

conf
|-- general.cfg
|-- instances
|   |-- exchange_mediator.cfg
|   |-- primary.cfg
|   `-- sip.cfg
|-- settings.cfg
`-- users
    |-- admin
    |   `-- admin_setup.cfg
    |-- admin.cfg
    |-- restricted_user.cfg
    `-- terry.cfg

As mentioned above, different installs may have a different number of files in any given directory, and they may be named differently also.  I'm not sure how to fit the contents of these files (just a simple text blob) into a relational database so that it's easy to search for certain strings in the contents of some or any of these files.

Example queries might be:
+Show me all config files in "instances" containing the string "restricted"
+Show me all support instances with a "user" config file named "admin.cfg"

Any advice would be much appreciated!

Note:
In terms of the specific database I'm using, I'm still researching that. I've looked at NoSQL db's such as elasticsearch and mongodb, as it seems like creating searchable JSON objects would be a great way of handling this.  I'd rather avoid elasticsearch, as their documentation recommends keeping a separate database to store the data reliably (I'd rather avoid that).  I've heard bad things about mongodb in terms of scalability, particularly if you have a large number of indexed keys.

For a more traditional-style SQL db, I'm looking at Amazon RDS or Redshift. Redshift seems to be easy to deploy, and quite capable of handling the amount of data I have, but as it doesn't support datatypes like arrays and structures, I'm not sure how my data could be transformed to fit into a series of tables.

Many thanks for having read this far!

Toby

You could store the text file data in a table like this:

config_files
------------
  ID (PK)
  name
  parent_dir_id (FK to config_dirs.id)
  content

config_dirs
-----------
  ID (PK)
  name
  original_user (key to the table that stores the original user data)
  parent_dir_id (key to config_dirs.ID, needs to be nullable to indicate a root directory)

Populating this structure, you could do something like this:

config_dirs
-----------
ID    | name       | parent_dir_id | original_user
------+------------+---------------+--------------
1     | conf       | (null)        | Toby Derius
2     | users      | 1             | Toby Derius
3     | admin      | 2             | Toby Derius
4     | bob        | 2             | Toby Derius

config_files
------------
ID    | name       | parent_dir_id   | content
------+------------+-----------------+--------
1     |general.cfg | 1               | This is the general config...
2     |admin.cfg   | 3               | admin's config...

This could allow you to query the directory structure and the file contents for specific users. Storing large amounts of plain text is not always a great idea, in terms of performance, so you might also want to consider storing the contents in a separate table file_contents and then referencing the file_contents from config_files. Also look into fill-text search engines.


MySQL - Difference between using count(*) and information_schema.tables for counting rows I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it would solve my problem. Bayuah provided this answer:
SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

Which I liked because it looks like a lookup instead of a scan, so it should be fast, but I decided to test it against
SELECT COUNT(*) FROM table

to see how much of a performance difference there was.
Unfortunately I'm getting different answers as shown below:

Question
Why are the answers different by roughly 2 million rows? I am guessing the  query that performs a full table scan is the more accurate number, but is there a way I can get the correct number without having to run this slow query?

I ran ANALYZE TABLE data_302, which completed in 0.05 seconds. When I ran the query again, I now get a much closer result of 34384599 rows, but it's still not the same number as select count(*) with 34906061 rows. Does analyze table return immediately and process in the background? I feel its worth mentioning this is a test database and is not currently being written to.
Nobody is going to care if it's just a case of telling someone how big a table is, but I wanted to pass the row count to a bit of code that would use that figure to create a "equally sized" asynchronous queries to query the database in parallel, similar to the method shown in Increasing slow query performance with the parallel query execution by Alexander Rubin. As it is, I will just get the highest id with SELECT id from table_name order by id DESC limit 1 and hope my tables don't get too fragmented.

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable where and group by clauses, etc.)

a) the normal way. Just count them.

select count(*) as table_rows from table_name ;

Accuracy: 100% accurate count at the time of the query is run.
Efficiency: Not good for big tables. (for MyISAM tables is spectacularly fast but no one is using MyISAM these days as it has so many disadvantages over InnoDB. The "spectacularly fast" also applies only when counting the rows of a whole MyISAM table - if the query has a WHERE condition, it still has to scan the table or an index.)
 For InnoDB tables it depends on the size of the table as the engine has to do either scan the whole table or a whole index to get the accurate count. The bigger the table, the slower it gets.
b) using SQL_CALC_FOUND_ROWS and FOUND_ROWS(). Can be used instead of the previous way, if we also want a small number of the rows as well (changing the LIMIT). I've seen it used for paging (to get some rows and at the same time know how many are int total and calculate the number of pgegs).

select sql_calc_found_rows * from table_name limit 0 ;
select found_rows() as table_rows ;

Accuracy: the same as the previous.
Efficiency: the same as the previous.
c) using the information_schema tables, as the linked question:

select  table_rows
from    information_schema.tables
where   table_schema = 'database_name'
  and   table_name = 'table_name' ;

Accuracy: Only an approximation. If the table is the target of frequent inserts and deletes, the result can be way off the actual count. This can be improved by running ANALYZE TABLE more often.
Efficiency: Very good, it doesn't touch the table at all.
d) storing the count in the database (in another, "counter" table) and update that value every single time the table has an insert, delete or truncate (this can be achieved with either triggers or by modifying the insert and delete procedures).
This will of course put an additional load in each insert and delete but will provide an accurate count.

Accuracy: 100% accurate count.
Efficiency: Very good, needs to read only a single row from another table.
   It puts however additional load to the database.
e) storing (caching) the count in the application layer - and using the 1st method (or a combination of the previous methods). Example: run the exact count query every 10 minutes. In the mean time between two counts, use the cached value.

Accuracy: approximation but not too bad in normal circumstances (unless for when thousands of rows are added or deleted).
Efficiency: Very good, the value is always available.


SQL Server chooses Nested Loop join with dimensional table and make seek for each row I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.

The Sort operation Estimation is 100 rows instead of 450 K rows and maybe affects the plan selection:

NestedLoop: https://www.brentozar.com/pastetheplan/?id=B110MZ2Pm or
NestedLoop plan

This is within a test DB. We have an additional DB with the same schema and almost the same data.

Running exactly the same query (both from SSMS) generates a different plan using Hash Join and dimensional table scan (32K reads):

HashJoin: https://www.brentozar.com/pastetheplan/?id=r1Jm7b2D7 or
Hash plan

I need a help to understand and solve the issue.

I can work around it by hint Hash Joint, but it does not make any sense that 2 similar DBs on the same instance generate different plans.

Update #1: I found that the estimated cost is different
So when SQL Server executes parallel it will choose a hash join.

With a single thread will be nested loop.

Update #2: The same issue occurred during a SELECT from the same table.
Depend on the number of columns (estimated cost). When I reduce the number of columns, the execution plan falls to a nested loop and seeks the dimension table.

There appear to be three reasons why you get a serial nested loop join plan in one of your environments and a hash join in the other. Based on the information that you've provided, the best fixes involve query hints or splitting the query into two parts.

Differences between your environments

One environment has 480662 rows in your CCI and the other has 686053 rows. I wouldn't call that nearly identical. There also appears to be a hardware or configuration difference between your environments, or at the very least you're getting very unlucky. The serial sort of 251 MB of estimated data has an IO cost of 0.0037538 units. The parallel sort of 351 MB of estimated data has an IO cost of 23.1377 units, even though it is discounted by parallelism. The engine expects to spill a relatively significant amount of data for the parallel plan. Differences like that can lead to different plans between environments.
The optimizer misapplies a row goal cost reduction which can favor a nested loop join plan

The nested loop plan is costed as if only 100 rows need to be output from the sort:

However, the query contains the following in the SELECT clause: COUNT(*) OVER ()

The engine must read all rows in order to produce the correct result for the aggregate. This is indeed what happens in the actual plan, and the index seek is executed 450k times instead of 100 times. This cost reduction appears to happen on a variety of versions (I tested back to 2016 SP1 base), on both CEs, with many different window functions, and for both batch mode and row mode. It is a limitation in the product which results in a suboptimal query plan here.
The nested loop join plan is serial due to a limitation with batch mode sorts

It's possible that your serial nested loop join qualifies for parallelism (depends on your CTFP) and you may be wondering why the optimizer did not find a lower costed parallel plan. The optimizer has heuristics which prevent a parallel batch mode sort from being the first child of a nested loop join (which must run in row mode). The problem is that the parallel batch mode sort will put all rows on a single thread which won't work well with a parallel nested loop join. Moving the sort to be a parent of the loop join won't result in a decrease in the number of estimated executions for the index seek (due to the optimizer problem). As a result you're very likely to end up with a serial plan, even if CTFP was set to the default of 5.

Here is a reproduction of your issue, which I can't upload to PasteThePlan because it doesn't support my version of SQL Server:

drop table if exists cci_216665;

create table cci_216665 (
    SORT_ID BIGINT,
    JOIN_ID BIGINT,
    COL1 BIGINT,
    COL2 BIGINT,
    COL3 BIGINT,
    INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO cci_216665 WITH (TABLOCK)
SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 50
, 0, 0, 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

drop table if exists YEAH_NAH;

CREATE TABLE dbo.YEAH_NAH (ID INT, FILLER VARCHAR(20), PRIMARY KEY (ID));

INSERT INTO dbo.YEAH_NAH WITH (TABLOCK)
SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 'CHILLY BIN'
FROM master..spt_values t1;

GO

-- takes 780 ms of CPU with nested loops
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

-- takes 111 ms of CPU with hash join
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (HASH JOIN);

The most straightforward way to solve your problem is to split your queries in two. Here's one way to do it:

SELECT COUNT(*)
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID;

SELECT TOP (100) *
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

On my machine this is actually faster than the hash join plan but you may not see the same results. In general, my first attempt for a query like yours would be to avoid a window aggregate without an OVER clause when only the first 100 rows are needed.

A reasonable alternative is to use the DISABLE_OPTIMIZER_ROWGOAL use hint introduced in SQL Server 2016 SP1. For this type of query, there's a problem with row goals so this hint directly addresses the problem without any dependence on statistics or anything like that. I would consider it to be a relatively safe hint to employ.

SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

This results in a hash join plan on my machine.


Backup transaction log from secondary database in alwayson AG Is it possible to do the log backup from the secondary databases in the AG?

You can take log backup on any nodes (Primary/Secondary) irrespective of its availability mode. This is what MSDN has to say about this in Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups):

  BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
  Differential backups are not supported on secondary replicas.
  BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
  A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
  To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

The link below begins a good series on backups, which also dives into different scenarios like information stored in MSDB tables for backups across several nodes:

SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica by Lisa Gardner


Which Column is better to be used here VARCHAR or DATETIME? I have a Script that uses Date to pick the day name and number of it, Something like the following

2018-06-04 =becomes=> Monday(04, June)
2018-06-04 =or becomes=> Monday(04) // In no need for Month

So What I do is creating a Column in the table using DATETIME datatype, Then I set the values like this

000-06-04 //case1
000-00-04 //case2

Is that a proper way to do this, Or is it better if I use VARCHAR(18) datatype and write it directly like this

Monday(04, June) //case1
Monday(04) //case2

Using DATETIME requires me to use DATE_FORMAT() later on, And using VARCHAR(18) doesn't.

SELECT DATE_FORMAT(date, '%W(%d, %m)') FROM table //case1
SELECT date FROM table //case2

I'm don't care about ease of use But performance.

As stated by manual DATE type internally represented by 3 bytes that is smallest possible for the required type of data. TIMESTAMP is represented internally as BIGINT seconds since epoch (4 bytes) but TIMESTAMP is always forced to be converted to/from the UTC if server's TZ is not UTC. That can cause significant overhead. DATE is compatible with date-related functions like week() or monthname() so it's look like the best choice.

DATE is far more efficient for search/sort/join/group/between than VARCHAR(18) so VARCHAR() isn't even an option.


Select Ingredients of a product based on unit type I have three tables UnitType,NutritionMaster, NutritionInProduct

UnitTypeID  UnitName
1           100ml contains
2           250ml contains

NutritionID Name    Description
1           Energy  NULL
2           Protein NULL

ID                    NutritionID  ProductID     UnitTypeID Unit
54087                       1       20785           1       2ml(0.5 kcal)
54088                       1       20785           2       4ml
54089                       2       20785           1       10ml
54090                       2       20785           2       50ml

And I want to show the results as

I tried doing it like this

Select ROW_NUMBER() OVER(PARTITION BY unittypeid ORDER BY NutritionInProductid aSC) AS "Row Number",
NutritionInProductid,unittypeid,ProductID,unit from NutritionInProduct
group by  unittypeid,NutritionInProductid,ProductID,unittypeid,unit
having ProductID =20785

but its not what I want ..can you help me.

The simple answer looks something like this:

SELECT nip.ProductID, n.Name, ut.UnitName, nip.Unit
FROM NutritionInProduct AS nip
INNER JOIN NutritionMaster AS n ON nip.NutritionID=n.NutritionID
INNER JOIN UnitType AS ut ON nip.UnitTypeID=ut.UnitTypeID
WHERE nip.ProductID=20785
ORDER BY nip.ProductID, n.Name, ut.UnitName;

I have intentionally not pivoted the results because the PIVOT syntax needs you to hard-code the names of the columns, which in this case are dynamically defined in the UnitType table.

While we're at it, you may wish to consider a few revisions to your schema:

There are some naming issues that can confuse other developers, for instance the "Unit" column should probably be called "Content" or something a bit more accurate.
"Unit" should probably also be numeric.
The unit of "Unit" (i.e. grams, ml, etc) is a property of the "NutritionMaster" table. For instance, for "Energy", the unit is "kcal" or "J", while for proteins, carbs, etc, the unit property is "grams", and so on.
NutritionMaster should probably be called something like ProductContents or ProductValues.
Consider adding a "quantity" column to the product, so you can calculate the numbers dynamically. In your example, you've stored the 100g and 60g values in duplicate - ideally, you would want to store just the 100g values, and then multiply those by (60/100).

Here's how I would design a similar solution:

--- A table for all the products:
CREATE TABLE dbo.Product (
    ID          int PRIMARY KEY NOT NULL,
    ProductName varchar(100) NOT NULL,
    ServingSize int NOT NULL,
);

INSERT INTO dbo.Product (ID, ProductName, ServingSize)
VALUES (20785, 'Spring rolls, oven cooked', 60);

--- A table for the different measures (these are the "rows" in your output)
CREATE TABLE dbo.ContentType (
    ID          int PRIMARY KEY NOT NULL,
    ContentType varchar(100) NOT NULL,
    UnitName    varchar(100) NOT NULL
);

INSERT INTO dbo.ContentType (ID, ContentType, UnitName)
VALUES (1, 'Energy', 'kcal'),
       (2, 'Protein', 'g'),
       (3, 'Carbohydrates', 'g'),
       (4, '.. of which sugars', 'g');

--- And the "fact" table with just the 100g contents:
CREATE TABLE dbo.ProductValues (
    ProductID     int NOT NULL,
    ContentTypeID int NOT NULL,
    Value100g     decimal(10, 1) NOT NULL,
    CONSTRAINT PK_ProductValues
        PRIMARY KEY CLUSTERED (ProductID, ContentTypeID),
    CONSTRAINT FK_ProductValues_Product
        FOREIGN KEY (ProductID) REFERENCES dbo.Product (ID),
    CONSTRAINT FK_ProductValues_ContentType
        FOREIGN KEY (ContentTypeID) REFERENCES dbo.ContentType (ID)
);

INSERT INTO dbo.ProductValues (ProductID, ContentTypeID, Value100g)
VALUES (20785, 1, 201.0),   --- Energy, kcal
       (20785, 2, 3.8),     --- Protein, g
       (20785, 3, 28.3),    --- Carbs, g
       (20785, 4, 3.8);     --- ... of which sugars

... and here's how to query those tables:

SELECT p.ProductName,
       CAST(p.ServingSize AS varchar(10))+' g.' AS ServingSize,
       ct.ContentType AS Content,
       STR(pv.Value100g, 10, 1)+' '+ct.UnitName AS ContentsPer100g,
       STR(pv.Value100g*p.ServingSize/100, 10, 1)+' '+ct.UnitName AS ContentsPerServing
FROM dbo.ProductValues AS pv
INNER JOIN dbo.Product AS p ON pv.ProductID=p.[ID]
INNER JOIN dbo.ContentType AS ct ON pv.ContentTypeID=ct.ID;


MySQL “lost” all data(InnoBD) Today I was making some work with site and I get error what MySQL can't rename file (to make changes in BD). Next I tried to restart MySQL, and it failed to stop MySQL. Then I did go to /var/lib/mysql and all I see is: ib_logfile0+1, ibdata1, and site folder, with tables that I was changing (not tables themselves, just #sql-b61_25a8e.MYI).

Can I recover my MySQL data? I don't need all tables, because I have old backup of them. I just need that one table with all users (yea I was changing table with users) And yes, I did shutdown MySQL for some stupid reason.

The table starting with #sql is just a temporary table and, if it's not in use, can safely be deleted. MySQL creates this temporary table to perform the various ALTER TABLE operations that you request.

If your original operation never completed, then your data should still be safely in its original table. If not, you can always rename the temporary table yourself and you'll get back whatever is in it.


Removing MySQL Orphan Tables in Google's Managed MySQL I have a number of orphan tables that I am unable to remove in Google managed MySQL instance.

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html describes how to find them
using the query:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

and returns the following results

TABLE_ID, NAME, FLAG, N_COLS, SPACE, FILE_FORMAT, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE
'10790334', 's/#sql-ib10651620-992396264', '33', '10', '15069', 'Barracuda', 'Dynamic', '0', 'Single'
'11386494', 's/#sql-ib11176578-758128583', '33', '10', '15865', 'Barracuda', 'Dynamic', '0', 'Single'
'11715795', 's/#sql-ib11550885-968245244', '33', '56', '16593', 'Barracuda', 'Dynamic', '0', 'Single'
'11884112', 's/#sql-ib11854996-1155811138', '33', '63', '16936', 'Barracuda', 'Dynamic', '0', 'Single'
'12648506', 's/#sql-ib11883996-1143057998', '33', '31', '18549', 'Barracuda', 'Dynamic', '0', 'Single'
'12877658', 's/#sql-ib11884055-1196159549', '33', '34', '19856', 'Barracuda', 'Dynamic', '0', 'Single'
'15159365', 's/#sql-ib15000139-4079941189', '33', '58', '24560', 'Barracuda', 'Dynamic', '0', 'Single'

I am unable to select any of these tables, for example:

use s; SELECT * FROM `#mysql50##sql-ib10651620-992396264` limit 10;

Following https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/,
I tried creating a table with the same name.

I figured out which tables these orphans came from with help from the query

SELECT * FROM
 information_schema.innodb_sys_tables
 JOIN information_schema.innodb_sys_columns USING (TABLE_ID)
 WHERE information_schema.innodb_sys_tables.NAME LIKE '%#sql%'

I then copied the create table statement for the related table and edited the table name.

CREATE TABLE `s`.`#sql-ib10651620-992396264` ( `ID` int(11) NOT NULL AUTO_INCREMENT, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and then tried to drop it

drop table `s`.`#mysql50##sql-ib10651620-992396264`;

but it could not find the table.  After dropping the created table with

drop table `#sql-ib10651620-992396264`;

the orphan table still persists.

To my knowledge, with a managed MySQL instance, I do not have access to the machine that MySQL is running on and therefore cannot remove the associated files from the command line.  Logging into the database from the command line and executing a shell command, executes a command on the client's host system, not the system that MySQL is hosted on.

Not a huge issue, but it would be nice to reclaim this disk space.

Your theory is correct about your cloudSQL since it is a Google managed service.

If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in which you will have super user privileges.

Nevertheless, about your current issue please fill a bug here which is under a private component and you can share if there is a need, sensitive information.


MYSQL - How I can filter only Debtor and only Creditors using action_type column? I have created the balance sheet using PHP MYSQL, for admin. Now I want to filter only Debtors and only Creditors from the table.
Here are Debtors and Creditors means:

Debtors: A debtor is a person or enterprise that owes money to another party
Creditors: A creditor is a person, bank, or another enterprise that has lent money or extended credit to another party

Below is my table structure:

I am using below queries to get both different:
To get Debtors only:
SELECT *,
           Sum(Coalesce(CASE
                      WHEN action_type = 'dr' THEN amount
                    end, 0)) AS total_debits,
       Sum(Coalesce(CASE
                      WHEN action_type = 'cr' THEN amount
                    end, 0)) AS total_credits,
       Sum(Coalesce(CASE
                      WHEN action_type = 'cr' THEN amount
                    end, 0)) - Sum(Coalesce(CASE
                                              WHEN action_type = 'dr' THEN
                                              amount
                                            end, 0)) AS total_debtors,
       IF(Sum(Coalesce(CASE
                         WHEN action_type = 'cr' THEN amount
                       end, 0)) - Sum(Coalesce(CASE
                                                 WHEN action_type = 'dr' THEN
                                                 amount
                                               end, 0)) > 0, 'dr', 'cr') AS balance
FROM tbl_balancesheet
GROUP BY area_id
HAVING balance = 'cr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

To get Creditors only:
SELECT *,
   Sum(Coalesce(CASE
                  WHEN action_type = 'dr' THEN amount
                end, 0)) AS total_debits,
   Sum(Coalesce(CASE
                  WHEN action_type = 'cr' THEN amount
                end, 0)) AS total_credits,
   Sum(Coalesce(CASE
                  WHEN action_type = 'cr' THEN amount
                end, 0)) - Sum(Coalesce(CASE
                                          WHEN action_type = 'dr' THEN
                                          amount
                                        end, 0)) AS total_debtors,
   IF(Sum(Coalesce(CASE
                     WHEN action_type = 'cr' THEN amount
                   end, 0)) - Sum(Coalesce(CASE
                                             WHEN action_type = 'dr' THEN
                                             amount
                                           end, 0)) > 0, 'dr', 'cr') AS balance
    FROM tbl_balancesheet
    GROUP BY area_id
HAVING balance = 'dr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

Now, I have issue with the calculation and not getting proper Debtors and Creditors
Also, I have uploaded the dump on SQL Fiddle. Below is the URL:
URL: http://www.sqlfiddle.com/#!9/badc3f/5
Plese help me!!!
Thanks

Test

SELECT -- client_id,
       area_id,
       SUM(CASE WHEN action_type='dr'
                THEN amount END) total_debits,
       SUM(CASE WHEN action_type='cr'
                THEN amount END) total_credits,
       SUM(CASE WHEN action_type='cr'
                THEN amount END) - SUM(CASE WHEN action_type='dr'
                                            THEN amount END) total_debtors,
       CASE WHEN SUM(CASE WHEN action_type='cr'
                          THEN amount END) - SUM(CASE WHEN action_type='dr'
                                                      THEN amount END) > 0
            THEN 'dr'
            ELSE 'cr' END balance
FROM tbl_balancesheet
GROUP BY -- client_id,
         area_id


Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements I have a job in SQL Server Agent (2012 Instance) that deletes rows from a table.  When I manually run the job, it succeeds without issue and in reviewing the table, does exactly what I want it to do.

When I've set it to an automatic schedule, the history indicates that the job has successfully run each step, yet in reviewing the table, the records are not deleted.  The job is set to enabled, and the history shows that it's run on the schedule, even with no records being removed.  If I run it manually, suddenly I see the correct results in the table.

Each step is run under the same login, for instance EXECUTED as user: NT ACCOUNT\SQLSRVAGENT, whether I run the job manually or it's run on its schedule.  The only difference I see between running it manually or the automated schedule is what invokes the job.  When it's the schedule, it reports The Job was invoked by Schedule 1 (Name) vs. when I run it manually, it reports, The job was invoked by User YouUser.  Note that this standard setup exists on other servers and doesn't create the same issue, so even though this is the only discrepancy I can find between how the job is run (manual vs. schedule), this doesn't look to be the issue.

Any other idea what could be causing this?

I have faced the same problem, It is a login problem, please check on your login user name and password.


Availability Group log_reuse_wait = Replication We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center.

We did a large index rebuild on the primary which obviously needs to replicate to the secondaries. I believe this replication has now finished. When I run:

select redo_queue_size, log_send_queue_size
from sys.dm_hadr_database_replica_states
where database_id = 28

I get:

redo_queue_size |log_send_queue_size
0           | 0
0           | 0
NULL        | NULL

Which suggests to me that the replication has finished. However, when I run

select log_reuse_wait, log_reuse_wait_desc from sys.databases where database_id = 28

I get:

log_reuse_wait  | log_reuse_wait_desc
6               | REPLICATION

I found this query:

select *
from sys.dm_hadr_database_replica_cluster_states
where database_name = DB_NAME()

which shows the state of the replication which again suggests that everything is replicated, but it can't be truncated for some reason:

is_failover_ready   | is_pending_secondary_suspend  | recovery_lsn              | truncation_lsn
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001

The Transaction Log backup jobs are running and backing up as expected, but the % of the log file used continues to grow due to normal traffic. How do I get SQL to reuse the transaction log file?

It looks like the CDC Capture job had stopped running. Restarting that (which took a while) then cleared out the log and allowed it to rewrite.


fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows As i am from mathematics background and require to do some huge permutations to start in my work kind help.

If i have a table by name sample1 with n columns what is fastest way to get all the permutations of the rows of sample1 into a new table. n>=12 say.

Please provide kind help with a link where i can get help or any code or query.

An example

say the 6 columns of a row contain

E1       E2      E3        E4        E5        E6

'[0,1]' '[1,2]'  '[2,3]'   '[3.4]'   '[4,5]'   '[9,10]'

I need to include its permutations

like

E1       E2      E3        E4        E5        E6

'[1,2]' '[0,1]'   '[2,3]'   '[3.4]'   '[4,5]'  '[9,10]'

 '[0,1]'   '[2,3]' '[3.4]' '[4,5]'  '[9,10]' '[1,2]'

and other permutations of row 1 above if there are n columns there will be n! permutations i need them also to be generated quickly and put into a new table.
U can the headers as column name each is a varchar.

Like for the above table if there are two rows with same elements in each of its columns how to remove duplicates kind help with any query

This isn't really an answer, more of a discussion of the problem.  If I understand correctly, you would have 1 row with twelve columns turn into 479,001,600 rows.  From the initial row you provided there are no duplicate values within the columns of the same row, so it doesn't appear that you are trying to eliminate duplicates within a row.

You didn't say how many rows are in your initial table, but this should mean that 100 rows in the initial table would equate to 47,900,160,000 rows in the resulting table.  The only way there could be duplicates there is if two rows in the initial table had a row with the same values as another row.

If those values are in the same order, then a simple select distinct * from t1 would eliminate the duplicates.  If instead the values are in a different order, then you actually have to determine the permutations for each row and make sure only one of those is in the initial table.

For smaller values of n, it would probably be easiest just to determine all the permutations of each row and then remove the duplicates.  For larger values of n eliminating from the initial set would certainly be advantageous for lower numbers of rows in the initial table.  As the initial table size grows the benefit of this early elimination is diminished.

Here is a way to get the permutations of one row.  It is likely not the best way.

Here is a way to get rows that have duplicates in a different order in the initial table.


Should date formats be specified in SQL statements? I see code from developers using implicit date conversion.  I would like a definitive answer to why they should not do this.

SELECT * from dba_objects WHERE Created >= '06-MAR-2012';

Because '2012/12/1' in the US is 11 months after the same string date in Europe.

Allowing implicit conversions means you are at the mercy of location settings.

If you can name a business where 11 months is an acceptable margin of error I'll be impressed.


Understanding NOT EXISTS Consider the following table,

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);

ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);

INSERT INTO GapsIslands
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 5 UNION ALL SELECT 1, 6
UNION ALL SELECT 1, 8 UNION ALL SELECT 1, 9 UNION ALL SELECT 1, 10 UNION ALL SELECT 1, 12
UNION ALL SELECT 1, 20 UNION ALL SELECT 1, 21 UNION ALL SELECT 1, 25 UNION ALL SELECT 1, 26;

I am struggling to understand application of the NOT EXISTS condition in the context of a Gaps and Islands solution.

The following query is meant to find the starting points of 'islands':

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS (
    SELECT  *
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)

Now the query within the NOT EXISTS condition,

SELECT  *
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1

returns, the column (let's call this column A) SeqNo as:

 SeqNo
  1
  5
  8
  9
  20
  25

The 'full' column (let's call this column B) for SeqNo is:

  SeqNo
  1
  2
  5
  6
  8
  9
  10
  12
  20
  21
  25
  26

Why then, when we take column B values WHERE NOT EXISTS in column A, do we find the column

 SeqNo
  1
  5
  8
  12
  20
  25

The value 1 in column B is also IN column A, so then why is the value 1 appearing in the column SeqNo of the query as a whole?

Let's move the problematic expression from WHERE condition to SELECT output list. EXISTS gives boolean, and SQL server don't want to display it directly, so we'll use CASE and convert it to readable form.

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
/*   There we insert our EXISTS moved from WHERE   */
/*              and wrapped into CASE              */
, CASE WHEN EXISTS ( SELECT  *
                     FROM GapsIslands AS b
                     WHERE b.ID = a.ID
                       AND b.SeqNo = a.SeqNo - 1 )
       THEN 'Exists'      /* EXISTS is true there, NOT EXISIS is false */
       ELSE 'Not exists'  /* NOT EXISTS is true there, EXISIS is false */
       END AS previous_SeqNo_exists
/* ----------------------------------------------- */
FROM GapsIslands AS a
ORDER BY SeqNo

fiddle

The query gives us the next output:

ID | SeqNo | RowNum | previous_SeqNo_exists
-: | ----: | :----- | :--------------------
 1 |     1 | 1      | Not exists
 1 |     2 | 2      | Exists
 1 |     5 | 3      | Not exists
 1 |     6 | 4      | Exists
 1 |     8 | 5      | Not exists
 1 |     9 | 6      | Exists
 1 |    10 | 7      | Exists
 1 |    12 | 8      | Not exists
 1 |    20 | 9      | Not exists
 1 |    21 | 10     | Exists
 1 |    25 | 11     | Not exists
 1 |    26 | 12     | Exists

It seems it is absolutely clear. SeqNo=1 have no previous value, because there is no such record at all, SeqNo=5 (and all another) - because there is a gap before.

When we use this EXISTS expression in WHERE the records with "Exists" will be removed, and we will obtain only records with "Not exists" (do not forget that there is additional NOT operator, so only records which gives FALSE will be returned).

ROW_NUMBER(), which works after WHERE, simply enumerates returned records in given ordering.

PS. The query under consideration (posted by OP) does not contain ORDER BY clause. That is bad. This may cause the records will be returned in random order. Nevertheless record numbers calculated by ROW_NUMBER() will be correct because the function have its own, local, ordering.


Why is my SQL statement sometimes slow and sometimes fast? I'm using MySQL version 5.5.45 on Windows Server 2016.  I'm executing the following statement in MySQL Workbench version 6.3.

delete FROM childattendance WHERE date < DATE_SUB(NOW(),INTERVAL 2 YEAR) limit 100000;

Essentially I'm deleting any records older than 2 years. I'm not worried about which records get deleted as long as they're older than 2 years.  I actually want to delete all records older than 2 years but I'm limiting to 100,000 rows at a time so that the statement actually finishes before losing connection.

The weird thing is that sometimes this statement times out, and other times it executes within a few seconds.

See this screenshot for the result, running the same query five times:

The first time it ran for 11 seconds before losing connection (I don't know what's causing that either) and the second time I ran it and it executed successfully in 2 seconds.  Ten minutes later I ran it again, failing twice and succeeding once in between.

I've run the same statement limiting to 30000 rows and had the same issue.  Sometimes it goes for 11 seconds and times out.  Other times it executes in 2 seconds.  And I've seen the same thing with other queries.

Coincidentally mysqldump also loses connection to the server when trying to backup this very same table, although it lasts 13 seconds before dying, and it happens 100% of the time.  I don't know if that's related.

This table has approximately 6.4 million rows in 471 MB.

What causes this?

This Answer has a mixture of explanation ("why" slow versus fast), speedup (how to be more efficient), and non-blocking (how to avoid the trouble with mysqldump).

If few rows are that old, the main effort is scanning the entire table.  This can be helped significantly via INDEX(date)
If more than 100K rows are that old, then the main effort is saving 100K rows in case of a undo (rollback).  This can be quite costly.  Don't do more than 1K at a time.
If the count is somewhere in between, then there is both the table scan and some of the undo effort.  Note that it may find rows to delete early in the scan, or late -- thereby making this take an unpredictable amount of time.

While the lengthy DELETE is running, other things may be adversely affected -- such as timeouts in mysqldump.  So, the goal is to keep the DELETE down to only a few seconds, both by speeding it up and by decreasing how much you bite off at a time.

My Big Delete blog explains several ways to significantly improve your task.

Note that one of my techniques focuses on what to do if you are deleting more than half the table.  This involves copying over the rows to keep.  (See the details on RENAME TABLE, etc)


PostgreSQL is running locally but I cannot connect. Why? Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping someone can tell me how to troubleshoot this.

I am unable to connect (but was able to before pre-Mountain Lion):

$ psql -U rails -d myapp_development
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

But Postgres is still clearly running:

$ ps aux | grep postgres
meltemi          2010   0.0  0.0  2444124   5292   ??  Ss   Wed01PM   0:00.02 postgres: rails myapp_development [local] idle
meltemi           562   0.0  0.0  2439312    592   ??  Ss   Wed12PM   0:02.28 postgres: stats collector process
meltemi           561   0.0  0.0  2443228   1832   ??  Ss   Wed12PM   0:01.57 postgres: autovacuum launcher process
meltemi           560   0.0  0.0  2443096    596   ??  Ss   Wed12PM   0:02.89 postgres: wal writer process
meltemi           559   0.0  0.0  2443096   1072   ??  Ss   Wed12PM   0:04.01 postgres: writer process
meltemi           466   0.0  0.0  2443096   3728   ??  S    Wed12PM   0:00.85 /usr/local/bin/postgres -D /usr/local/varpostgres -r /usr/local/var/postgres/server.log

And it's responding to queries (both to a test db and the development db) from a local Rails app

  User Load (0.2ms)  SELECT "users".* FROM "users"
  Rendered users/index.html.haml within layouts/application (1.3ms)

There appears to be no /var/pgsql_socket/ directory, let alone the /var/pgsql_socket/.s.PGSQL.5432 socket file mentioned above!?! Maybe the install of Mountain Lion wiped that out?

$ ls -l /var/ | grep pg
drwxr-x---   2 _postgres  _postgres    68 Jun 20 16:39 pgsql_socket_alt

How can I troubleshoot this?

I found that I had an extremely similar problem, namely that postgres was opening a socket in /var/pgsql_socket_alt where none of my software expects to look, but the solution to my problem was not only a problem with my $PATH.

I had to create the directory /var/pgsql_socket, chown it to myself, and set unix_socket_directory in postgresql.conf (located in /usr/local/var/postgres) to that directory, then use the pg_ctl binary in /usr/local/bin to start the right postgres server successfully (which is where $PATH comes in – make sure which pg_ctl resolves to /usr/local/bin/pg_ctl, or just always call it explicitly).

This might help other users who find this question via the /var/pgsql_socket_alt mention.


Output into temporary table I am trying to create a procedure that will generate an xml document of a given table whilst updating the information in said table. My procedure does not recognise the use of the temporary table unless I use a CREATE TABLE statement (I have not tried table var's yet). Ideally I would not use the CREATE TABLE statement in case I/another developer wanted to add another column(s) in the future.
Here is my code currently:
UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

And it fails with:

Msg 208, Level 16, State 0, Line 24
Invalid object name '##temp'.

It's not the end of the world if I have to use a CREATE TABLE statement, but I would prefer not to explicitly declared it.
Notes:

I know there is no error handling at the moment, I want to get this section working first.
I have also tried naming the columns instead of using Inserted.*
I am purposely setting ErrorReported = 0 as I am in a test environment

You must create a temp table with Audit.ErrorLog Columns and then get output records.

Declare @temp Table (ErrorLogID Int, ErrorReported Int, Error NVarChar(100))

UPDATE Audit.ErrorLog
SET ErrorReported = 0
OUTPUT inserted.*
INTO @temp

SELECT *
FROM @temp
FOR XML PATH('Error'), ROOT ('Errors')


How does "Be made using the login's current security context" pass the users password to remote server Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash.

I have transferred logins from server to server before and i know that the hash alone is stored in SQL and this is often presented as a hex value hex when creating logins with passwords, for example:

create login test with password = '0x22A9AE652CFC38938D56A9C3872B266B192D16E4' hashed

If SQL only has a hash available - can it logon to the remote server using the hash? Or is the original (un-hashed) password kept in the user’s session / memory for the entire duration of their connection which SQL can then retrieve and pass to the remote server for login?

If the server can login with a hash instead of the password – can this be done in normal logins or is it purely an internal feature of linked servers?

Purely a curiosity question – like to understand how these things work.

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.

Here is there full response:

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: If connected to the local server using SQL Server
  Authentication, login name and password will be used to connect to the
  remote server. In this case a login with the exact same name and
  password must exist on the remote server.

  Be made using the login's current security context:

  Specify that a connection will be made using the current security context of the
   login for logins not defined in the list. If connected to the local
   server using Windows Authentication, your windows credentials will be
   used to connect to the remote server. If connected to the local server
   using SQL Server Authentication, login name and password will be used
   to connect to the remote server. In this case a login with the exact
   same name and password must exist on the remote server.

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: Login name and password is passed to the remote server, this
  mechanism/task is handled by the SQL Engine which is hidden and cannot
  be captured.

  If SQL only has a hash available - can it logon to the remote server
   using the hash? Or is the original (un-hashed) password kept in the
   user’s session / memory for the entire duration of their connection
   which SQL can then retrieve and pass to the remote server for login?
   If the server can login with a hash instead of the password – can this
   be done in normal logins or is it purely an internal feature of linked
   servers?

  Answer: HASH cannot be used to login to the SQL server, SQL Engine is
  responsible for handling login process and we don’t have much
  documentation around this.