Returning a result set with multiple rows based on max date I have a child table that is something like this:

[Cust Date Table]

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           1 | 2012-03-31 |   50.00 |
|           2 | 2012-04-30 |    0.00 |
|           2 | 2012-03-31 |   10.00 |
|           3 | 2012-03-31 |   60.00 |
|           3 | 2012-02-29 |   10.00 |

I would like to be able to get a result set like this - one record for each client with the latest date:

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           2 | 2012-04-30 |    0.00 |
|           3 | 2012-03-31 |   60.00 |

I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):

select top 1  [Some Date], [Customer ID], [Balance]
from [Cust Date Table]
where [Customer ID] = 2
order by [Some Date] desc

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           2 | 2012-04-30 |    0.00 |

But I'm not sure how to get all three of the records I want.  I'm not sure if this is a situation that calls for a sub-query or something else.

Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30).
I have tried

select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance
from [Cust Date Table]
group by [Customer ID], Balance;

The problem is this doesn't return just the one row for each customer - it returns multiple rows.

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;


SQLite size limit and performance I already studied some databases like MySQL but I never professionally work with databases. I am working on a project and we intend to use SQLite (for some reasons: small, lightweight, fast, among others), then I have some questions about it:

SQLite support how many gigabytes of data? Is there a limit? (I will have some huge data tables (e.g. 18 columns X 80.000 rows) for each user)
Is it fast to read a lot of data? (e.g. 18 columns X 80.000 rows)
There is any other relevant information I need to know about SQLite? (Note: I will use it on Windows and OS X)

Extra information:

The software I am working on is a data analysis software, and I just need to use the DB to save the data collected from the users using some sensors.

I am using Qt 5.3.2 (C++) on the project.

SQLite is suitable for storing data locally in user's Machine.

On Database size limit, maximum database size of 140 terabytes.

On reading data SQLite support index so you can think of having indexes based on your select query.

you can read more about it from below link:

https://www.sqlite.org/limits.html

https://www.tutorialspoint.com/sqlite/sqlite_indexes.htm


How to select only the number from "show global status like 'Com_insert';" The title says it all.

How can I select only the number result from show global status like 'Com_insert' ?

If you are using MySQL 5.1+, you can get the number alone in MySQL client

mysql> select VARIABLE_VALUE from information_schema.global_status
    -> where VARIABLE_NAME = 'Com_insert';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 479512448      |
+----------------+
1 row in set (0.00 sec)

mysql>

No matter what version of MySQL 5.x, you can also get the number in the OS:

[root@*** ~]# mysql -root -p -ANe"show global status like 'Com_insert'" | awk '{print $2}'
479512506


Downsides of switching Master to Simple recovery? I've read a lot about this over the last couple of days, but none specifically on the Master db.

If I switch from full to simple, will it flush out any plans, statistics, etc?
Any performance hits?

I know how to proceed, just never seen Master on full recovery before.

TIA

It does not really make sense to have the master database in FULL recovory. The installation process leaves the database in SIMPLE recovery, so this means that somebody changed it afterwards because they could.

I do not think that you perform operations on the master database that require point-in-time recovery and if you do I strongly suggest that you rethink this strategy. Also, take into account that a database in FULL recovery needs to back up its log regularly in order to avoid filling the log files. Is this happening? How are you planning to restore these log backups in case?

That said, I would change it back to SIMPLE without worrying too much about performance hits.


innodb high i/o , some parameters make mysql crash i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp.

the mysql version is 5.7.x

i tried some different configs but when some parameters changing mysql dont start anyway!

1)when i set innodb_dedicated_server mysql dont start

innodb_dedicated_server = true

2)when i set innodb_buffer_pool_size , innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size together mysql dont work too

innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 40
innodb_buffer_pool_chunk_size = 1G

but when i set these configures mysql start working correctly (innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size  are commented)

port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_connection=5000
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

max_allowed_packet=1048576

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

#innodb_buffer_pool_size=40G
#innodb_log_file_size=2G

#innodb_dedicated_server = true

innodb_buffer_pool_size = 20G
#innodb_buffer_pool_instances = 20
#innodb_buffer_pool_chunk_size = 1G

how can i get a better performance?!

updates:

my.cnf

SHOW GLOBAL STATUS;

SHOW GLOBAL VARIABLES;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS;

MySQLTuner.pl

Check the logs.  There should be some clue of what caused the crash.  I don't think the settings that you highlighted would cause a crash, but let's change them some:

innodb_buffer_pool_size = 40G        -- good
innodb_buffer_pool_instances = 16    -- 16 is "good enough"; more is "diminishing returns"
innodb_buffer_pool_chunk_size = 128M -- only needed for dynamically changing bp
max_connection = 1000

('instances' has only a small impact on performance.)  If my advice is not good, read the Windows-specific recommendations in https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

If you have HDD (not SSD), then lower some settings:

innodb_io_capacity = 400
innodb_read_io_threads = 16
innodb_thread_concurrency = 0
innodb_write_io_threads = 16

If you are concerned about performance, turn on the slowlog with long_query_time=1 so you can catch the naughtier queries.

innodb_dedicated_server does not exist until 8.0.3.


SQL Server 2012 Compatibility I'm converting some stuff over to be compatible with SQL Server 2012 and had a couple of questions. I'm taking out all of the sp_dboption commands and making them ALTER commands instead, but there were a few options I haven't been able to tell the equivalent of. I was wondering if anyone here knew.

Here are the sp_dboptions I couldn't find:

bulkcopy
trunc. log
dbo use
single
ansi null default
db chaining

Here is the website I was using if it helps:

http://technet.microsoft.com/en-us/library/bb522682.aspx

They're all listed in Setting Database Options:

bulkcopy: ALTER DATABASE .. SET RECOVERY BULK_LOGGED;
trunc. log: ALTER DATABASE .. SET RECOVERY SIMPLE;
dbo use: ALTER DATABASE ... SET RESTRICTED_USER;
single: ALTER DATABASE ... SET SINGLE_USER;
ansi null default: ALTER DATABASE ... SET ANSI_NULL_DEFAULT ON;
db chaining: ALTER DATABASE ... SET DB_CHAINING ON;


mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0 I'm struggeling the last two days with the following issue:

Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to mysql 8.0(.13) it takes about 40 minutes (with exactly the same configuration). Are there any changes in mysql 8.0 that can cause the performance lack?

The dump was created local on a mysql 5.7 server with the following command:

$ mysqldump --opt -uuser -p database > database.sql

The dump was restored locally with the following command:

$ mysql -uuser -p database < database.sql

The first few lines of database.sql:

-- MySQL dump 10.13  Distrib 8.0.13, for Linux (x86_64)
--
-- Host: xx.xx.xx.xx    Database: xxx
-- ------------------------------------------------------
-- Server version       5.7.19-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,     FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'        */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xxx`
--
[...]

This issue is tested on a virtual testserver with exactly the same hard- and software configuration in all test cases:

OS: Debian 9.6
Disk: SSD
CPU: 4 CPU Cores
RAM: 10 GB

The my.cnf is the same for mysql 5.7 and mysql 8.0(.13) server:

[mysqld]
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
language                = /usr/share/mysql/english
skip-external-locking
skip-name-resolve

innodb_buffer_pool_size         = 2048M
innodb_log_buffer_size          = 128M
innodb_log_file_size            = 265M
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT

max_allowed_packet      = 64M
key_buffer_size         = 2048M

thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

server-id               = 7
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format           = MIXED

lc_messages                     = de_DE
default_authentication_plugin   = mysql_native_password

character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

sql_mode        = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Any suggestions? Thank you folks!

innodb_log_buffer_size should be no more than 1% of RAM.

key_buffer_size should be only 30M, assuming you do not have any active MyISAM tables.

Those changes will let you increase innodb_buffer_pool_size to 7G, which might help.

Do you have any FULLTEXT or SPATIAL indexes?  FOREIGN KEYs?  Stored programs (probably not, since you did not ask for them to be dumped)?

If those don't help, file a bug with https://bugs.mysql.com


Check the time that PostgreSQL is taking to automatically create existing indexes when you do bulk insert using copy command from Spark I am doing a bulk insert from the spark to the postgres table. Amount of data that I am ingesting is huge. The number of records is around 120-130 million
I am first saving the records as multiple csv files on distributed storage location i.e. S3 bucket in my use case. Now I am using multiple copy command to copy the data in the PostgreSQL table. The actual PostgreSQL table has four indexes on it.
The copy command takes around 8 hours to save the data. I created a similar table without indexes and the data got saved in around 28-30 minutes. Based on searches that I have done on internet on multiple sites, they mentioned that indexes can slow down the performance and that is definitely seen based on the time difference that I have specified above.
Now the actual question is how I can identify which index creation is taking more time. Is there any utility,  query or command that shows the time taken to create the indexes on the table when we are doing bulk inserts.
I am using the below query to see the number of multiple copy commands are running on the PostgreSQL instance:
SELECT * FROM pg_stat_activity
where usename = 'xyz' and application_name ='PostgreSQL JDBC Driver'

Is there something like this query or any tool or command that I can use to see amount of time t is taking to create the indexes. Also how much time is taken by each index?
Any idea, guidance or suggestion are welcome. I am not that familiar with PostgresQL.

As long as all indexes are B-tree indexes, modifying them all should take roughly the same time.
Of course, an index with an expensive expression will take somewhat longer, as well as an index on a string column with an expensive collation.
You could compare the duration of CREATE INDEX statements on a filled table – if an index takes twice as long to build as another one, it will probably also take about twice the time to modify.
If the amount of newly loaded data is large, it may be faster to drop and re-create the indexes. You'll have to experiment.


auto increment for the table with 1000 of records in mysql How to reset auto increment for the table with 1000 of records in mysql. I want it to start counting from 1 again.

You can set it to 1 with this command:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;

More info in the mysql manuals, here: https://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html


what's the optimal partition setup for a linux db server? My host wants to know how I want to partition my server before installing CentOS. I need to be able to do use xtrabackup to create quick backups from 20-50GB databases. I know I need to use a LVM, but I'm not sure how I would forward that to my host?

I currently have 1TB of usable space in RAID-10, how should I partition the array? this is only going to be a db server, only mysql and OS will be on this server.

Most of the partitioning of data in SQL Server is done for performance, recover-ability (separating log/data files to different physical disk arrays), and then management of data.  For example, if you have different logical drive letter names, you could look at the IO metrics for them separately through your monitoring solution.

It looks like you only have 1 large RAID 10 with 1TB of total usable storage.  Correct?  If so, at the end of the day, it's not going to matter too much from an architecture point of view since your hands are tied to having everything on the same spindle.

Now as to how to setup the drives on the LinuxOS, it depends on the scope and scale of the servers lifetime.  I'll let your guys decide on that how that should look outside of the vanilla:

1- swap (RAM X2 or whatever you guys decide)
2- /boot (Usually small primary partition - ext3)
3- / (big data drive)
4- / (log drive)
5- / (Backup Drive)

I would advise on separating the data and log drives for more granular performance metric collection, and separating the backup drive so they don't balloon up to a point where your data files have no room to grow.  Make sure to pre-grow your files when installing.

Again this is highly subjective and many opinions might differ.  Implement what makes most sense to you.


Can CLR execution continue after 'THROW' is encountered in T-SQL? I am calling a T-SQL stored procedure from a CLR stored procedure. and the THROW statement in T-SQL stops the execution of CLR without going into the catch block. Is there something I can change in my code to cause the CLR procedure to execution the catch block?

T-SQL:

CREATE TABLE t1 (ID INT PRIMARY KEY clustered);
CREATE TABLE t2 (ID INT CONSTRAINT fk_1 FOREIGN KEY REFERENCES t1(ID))
GO

CREATE PROC dbo.TestThrow
AS
BEGIN
BEGIN TRY
    INSERT INTO t2 VALUES (1)
END TRY
BEGIN CATCH
    THROW
END CATCH
END
GO

CLR:

    public static void TestThrow()
    {
        String query = "EXEC DBO.TESTTHROW";
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            try
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        SqlContext.Pipe.Send("no error");
                    }
                }
            }
            catch (SqlException e)
            {
                SqlContext.Pipe.Send("sqlexception");
                SqlContext.Pipe.Send(e.Message);
            }
            catch (Exception e)
            {
                SqlContext.Pipe.Send("exception");
                SqlContext.Pipe.Send(e.Message);
            }
        }
    }

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect.

A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW raises a ThreadAbortException that cannot be reset via the Thread.ResetAbort method. I tried explicitly catching this exception, and even calling Thread.ResetAbort when catching the generic Exception, but to no avail. So I am not sure if a ThreadAbortException is really being called, but regardless, the current process ends immediately. And, it even displays the error as a T-SQL error and not a .NET Framework error, which is odd.

The reporter of that Connect bug tested on SQL Server 2014, and I tested on SQL Server 2012. I cannot say for certain if this behavior still exists in SQL Server 2016, but I strongly suspect that it does given that there doesn't seem to be much effort (if any) put into fixing and/or improving SQL Server's CLR Integration (i.e. SQLCLR). So for now, and likely the foreseeable future, there are three possible work-arounds that have worked for me:

Replace THROW with RAISERROR(); RETURN; for procs that might be called by SQLCLR objects. The only downsides I can think of are:

Can't set a custom ERROR_NUMBER on-the-fly
Can't re-throw to send the original, system-defined ERROR_NUMBER to the caller

Wrap your query in a T-SQL TRY / CATCH that uses RAISERROR:

  String _Query = @"
BEGIN TRY
  EXEC dbo.TestThrowSql @CauseError;
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
END CATCH;
";

The benefit here is that you can continue to use THROW and it will work as expected when called by non-SQLCLR app code, by SQLCLR app code that is not using the Context Connection, by other Stored Procedures, in SQL Agent jobs, etc. That and you don't have to go back and edit any existing Stored Procedures :-).
Switch to using a regular / external connection string. The downsides here are:

The Context Connection is much faster.
The Context Connection can be done in a SAFE Assembly.
The Context Connection has access to session-based items (i.e. CONTEXT_INFO, local temporary tables, etc.)


Using composite indexes with date ranges I have a table of rules that are activated depending on the date.

id - name - value - start_date - end_date
1  - ABC  - 10    - 2015-12-01 - 2015-12-31
2  - DEF  - 15    - 2016-02-01 - 2016-02-29

My SQL queries will mostly be

SELECT * FROM rules WHERE start_date <= '2015-12-05' and end_date >= '2015-12-05';

Does having a composite index consisting of start_date and end_date help or will worsen the query?

Edit: I'm using MySQL

It's hard to tell if this will hurt or improve performance but adding a composite index on start_date and end_date shouldn't worsen your query, the index will either be used or not used. You didn't mention your RDBMS but I don't think there's a big chance your engine's optimizer will pick a worse plan (but there is always a possibility I guess).

That being said, there might a slight impact on the plan generation time as your optimizer has an extra index to consider, and unused indexes could hurt overall performance since they need to be maintained when updating/inserting.

Whether or not the index will help your query is hard to tell, it depends on the selectivity of your query and cardinality statistics. The optimizer of your RDBMS should pick the fastest way to get your data, and even if an index exists on the columns your query selects on a full scan might still be the better option, especially since you are selecting all fields.

Why is selectivity important?

When an index is used, the index points to the actual record and the record might need to be fetched using the pointer. Most RDBMS work that way.

See for example the Oracle documentation

  In general, index access paths should be used for statements that
  retrieve a small subset of table rows, while full scans are more
  efficient when accessing a large portion of the table

or the SQL Server documentation

  In many cases, the optimizer forces a table scan for queries with
  result sets of approximately 5 percent, although the table scan
  becomes more efficient than index access at selectivities of 8 to 10
  percent.

Why is select * important

If you fetch all fields from a table your database engine will have to fetch the actual record using the pointer stored in the index and you lose the option of using included columns (if your RDBMS supports them) or covering indexes.

See for example this article for SQL server (but the principle goes for a lot of vendors): Using Covering Indexes to Improve Query Performance

  However, under some circumstances, the overhead associated with
  nonclustered indexes may be deemed too great by the query optimizer
  and SQL Server will resort to a table scan to resolve the query.

and

  The observed improvement is due to the fact that the nonclustered index
  contained all of the required information to resolve the query. No Key
  Lookups were required.


Azure SQL Database Restore after Server Failure - Where are the Backups? According to this article, an Azure SQL backup can be used to "Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world."

My question is this. In the event the server my database is sitting on fails how would I be able to access the most recent backup to restore it to another server? For example, if I wanted to restore a copy of my database now, I would select my database and then the Restore option from the menu at the top of the page. However, if I've lost my server (and therefore my database), how do I get access to the backup(s) of the database(s) that sat on the server that was lost?

I think the key to understanding that sentence is what I have marked in bold:

  Restore a database to another geographical region. This allows you to
  recover from a geographic disaster when you cannot access your server
  and database. It creates a new database in any existing server
  anywhere in the world.

See the documentation on Geo restore:

  Geo-restore is the default recovery option when your database is
  unavailable because of an incident in the region where the database is
  hosted. If a large-scale incident in a region results in
  unavailability of your database application, you can restore a
  database from the geo-replicated backups to a server in any other
  region. There is a delay between when a differential backup is taken
  and when it is geo-replicated to an Azure blob in a different region.
  This delay can be up to an hour, so, if a disaster occurs, there can
  be up to one hour data loss.

The difference between this and a regular restore is that there is a bigger latency and possibility of data loss. You also can't do a point in time restore.

So when you restore the backup from an existing database through the azure portal you have access to the backups on that same location.

Microsoft is replicating those backups to azure blobs in different regions in case some serious incident happens in a region.

In order to restore you would add a new database in your portal and select "backup" as a source:

  To geo-restore a database during its retention period using the Azure
  portal, open the SQL Databases page and then click Add. In the Select
  source text box, select Backup. Specify the backup from which to
  perform the recovery in the region and on the server of your choice.

So in case your entire database and data center is gone, you have the option to create a new database  in another data center, and you can use the latest geographically replicated backup (not the latest backup) as a source.


Sum on distinct and group by I have this :

CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time);
INSERT INTO tickets VALUES (0, 'web-mobile', 1, 2);
INSERT INTO tickets VALUES (1, 'web-destkop', 1, 2);
INSERT INTO tickets VALUES (2, 'web-destkop', 2, 2);

SELECT shop_id, source, COUNT(DISTINCT(user_id)) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, source;

Rexster : http://rextester.com/TDS9913

But what I would like is to get the sum of distinct user_id on web-*.

shop_id source  user_count
2       web     2

You need to group by a substring of source:

SELECT shop_id,
       substring(source,1,3),
       COUNT(DISTINCT user_id) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, substring(source,1,3);

http://rextester.com/EFZZSG27212


How to reconstruct functional dependencies from 3NF decomposition? I have the following decomposition for the relation R(A,B,C,D):

R1 = (A,B),
R2 = (B,C),
R3 = (A,D).

It is given that AD is the only candidate key for the original relation and that R is not in BCNF. The decomposition above preserves the functional dependencies as well as is lossless-join decomposition (that is R1 natural join R2 natural join  R3 = R).

What could be a set of functional dependencies for the decomposition?

3NF is always lossless decomposition as well as one that preserves functional dependencies. Therefore, I think the decomposition given is in 3NF. If we were to reverse engineer 3NF decomposition algorithm we could say that the original set of functional dependencies was {a→b, c→b, a→d} indeed R3 contains all of the attributes of the candidate key. But the problem is that AD is not the candidate key of the above set. In order to for AD to be the only candidate key I'd have to either add another dependency or add more attributes to the existing dependencies. In either case the ensuing 3NF decomposition would not result in one like in the OP.

Is there something I'm missing to find an appropriate dependency set?

If AD is the only candidate key of R, then we know that in the original relation the dependency AD → BC holds.

But, since the relation is not in BCNF, we know that there are also other dependencies for which the determinant is not a superkey. Given the fact that the decomposition preserves the dependencies, we know that the other dependencies are constituted by attributes that are in the decomposed relations.

The simplest possible case is that there are dependencies A→B and B→C. So the original relation can be:

R<(ABCD), {AD→BC, A→B, B→C}>

In fact, if we apply both the classical BCNF decomposition algorithm as well as the 3NF synthesis algorithm we obtain the specified decomposition.


How to handle concurrent write access to two related tables Suppose there are two tables t1 and t2. t1 has a boolean column ct1.
There are two scenarios:

If ct1 is false, create new entries in t2 and make ct1 true
If ct1 is true, just return

How to handle above scenario in Postgres if there are concurrent queries like above?

The possible race condition is: the first query sees ct1 as false and then create entries in t2, then the second query also see ct1 as false before the first query can make it true.

An UPDATE takes a write lock on the row automatically, which prevents concurrent transactions from doing the same until the lock is released (your transaction has finished).

So this should do the trick:

WITH upd AS (
   UPDATE t1
   SET    ct1 = true
   WHERE  t1.id = $t1_id  -- your input here
   AND    ct1 = false
   RETURNING t1_id
   )
INSERT INTO t2(t1_id, col1)
SELECT t1_id, 'foo'  -- or your input for t2 here
FROM   upd;          -- only if UPDATE found a row

This assumes a PK t1.id to allow multiple rows in t1. Your example makes it seem like there is a single row in t1. The same solution would work for that simple case, just remove t1.id from the query.

t1.ct1 must be defined NOT NULL.

If the UPDATE finds no row (row in t1 with t1.id = $t1_id is already true or does not exist) then nothing happens.

If concurrent transactions wait to update the same row, they will wake up once this transaction has finished.

If your transaction commits, ct1 is true now, and the recheck for others will return no qualifying row, i.e. concurrent transactions are finished, too.

If your transaction rolls back, the next one in line gets to update t1 (and hence also insert rows in t2).

Note: Normally, queries in CTEs can execute in any order. But since the outer INSERT references the UPDATE, a sequence of operations is established.


How can Oracle use a Range Scan here? I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows.

On this table, I have the index I_LOC_NAME_DATE, such that:

Column          Position  Descend
Location        1         ASC
Name            2         ASC
DateMovedIn     3         ASC

I'm writing a query which selects the customer's who moved into their location between two dates;

SELECT *
FROM   Customer
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

From what I've read about Oracle's use of indexes, in this situation I would expect it to use a skip scan (filter by Location, and then hop betwen the names and get each move in date), but it turns out it uses a range scan.

An EXPLAIN reveals the range scan cost to be 77, and when I apply a index_ss hint as follows, the cost jumps to 401,096 (yes, really).

SELECT /*+ index_ss(Customer I_LOC_NAME_DATE) */ *
FROM   Customer
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

Can anyone explain how Oracle is using a range scan in this circumstance without the Name being specified?

You have a leading column specified in condition (Location = :location) . From here :

  The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
  col1 = :b1
  ...


Run a report by specifying which server to execute the query on I have a couple of stored procedures that I have written that gathers some information about all of the databases on the server it's stored on.  I would like to create a report such that I can select the server I want the report to run on, and then execute the stored procedures there.

This actually wasn't nearly as hard as I initially thought, but I am putting this up here so that others can find this.

The first step is to create a data source that connects to a server that already has the stored procedure/s on it.  You can't skip this step, otherwise you won't be able to easily build your tables, matrixes, etc. because Visual Studios won't know what fields you're working with.

The next step is to lay out report after adding all the necessary data sets and tables, etc.

After the report is roughly how you want it, create a parameter named @Server.  In the properties of the datasource's connection string, click the equation button (fx).  Modify the connection string to look like this, replacing the catalog with the appropriate database name:

  ="Data Source=" & Parameters!Server.Value & ";Initial Catalog=DB"

Now, when you run the query you can type in the server name, and the report will execute the query/s on that server.

If you want to make it a bit cleaner, you could create a list of the appropriate servers in a data set and use that to populate the parameter.


Ubuntu postgresql 2 databas in two hard disks I have two postgresql database in single 1tb hard disk. Each data base sizes more than 300gb. So now I faced a disk space problem and I bought another 1tb hard disk recently. So I need to move one database into this new hard disk. How can I do that in Ubuntu ????

If I move one database to another drive, postgresql can communicate both harddisks at same time? Can I access both databases in postgresql on master hard.

Sorry for my bad English.

PostgreSQL has no problem with the data on 2 hard-disks. The best thing that you can do is create a tablespace on your second hard-disk and move one of the databases to this tablespace.

CREATE TABLESPACE somename LOCATION 'directory-on-new-disk';
ALTER DATABASE name SET TABLESPACE somename;

Also see this post: Postgresql: Move database to new tablespace.

If you use LVM then you can also consider to use this to extend the logical volume on which your database is.


"ORA-01017: invalid username/password" while connecting to an instance remotely as sysdba I'm new to the oracle world; so please pardon my ignorance.

I'm trying to connect to a remote instance as sysdba.
However, I encounter the error: "ORA-01017: invalid username/password; logon denied"

$ sqlplus "sys/xxxx@<IP>:1521/sid1 as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 04:07:34 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

When I try to connect to another instance on the same server, I can get through:

$ sqlplus "sys/xxxx@<IP>:1521/sid2 as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 04:07:23 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can someone point me to what I may have missed here?

Thanks!

Seems simply a password issue. Different instances may have different passwords, the SYS password is not necessarily the same for all the instances on the same server. Make sure remote_login_passwordfile is configured properly in the instances, and the password file exists for instance SID1 on the server in ORACLE_HOME/dbs/orapwSID1 (Linux-Unix) or ORACLE_HOME/database/pwdSID1.ora (Windows).

If you do not know the correct SYS password, you can recreate the password file, for example:

orapwd file=$ORACLE_HOME/dbs/orapwsid1 password=xxxx force=y


Truncate a table with 17 billion rows in an AG I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG.
What will be the effect of this operation on the AG latency and the size of log backups?
Is there a recommended way of doing this?

Logging Extents
The amount of log data generated (and thus sent over the network to your other AG nodes) depends on how big the rows are in your 17 billion row table are.  TRUNCATE will definitely be a tiny amount compared to doing a DELETE.  But it could still be significant, depending on your infrastructure and expectations.
Consider the dbo.Votes table in the Stack Overflow sample database:

Each row is 28 bytes.  A page in SQL Server is 8 KB (8,192 bytes), so you can fit around 292 rows on a page.  This isn't exactly correct, since there is overhead for both pages and rows, but it's a decent approximation for this example.
That means it takes about 58,219,178 pages to hold all 17 billion rows.  When doing a DROP or TRUNCATE, a background task deallocates extents (groups of 8 pages).  Each of these deallocations is logged.  This means about 7,277,397 log records will be created by truncating this table.
Testing dbo.Votes
I tried this out on my copy of that sample database, after setting the recovery model to full, and taking full and log backups to initialize the backup chain.  The dbo.Votes table has 10,146,802 rows.  Based on our previous calculations, this should be around 34,749 pages, or 4,343 extents.
In reality, that table uses 47,721 pages allocated to it (because of the overhead mentioned before), which is 5,965.125 extents.
Now I'll TRUNCATE the table:
TRUNCATE TABLE dbo.Votes;

This completes instantly, but I ended up with 17,605 log records.  It looks like there are really 3 log records per extent (2 for updating IAM and GAM pages, one for updating the PFS page to deallocate the data page).
Those log records only totaled up to ~1.28 MB of log file usage.  But your real table has 1,600 times the rows as this, and your row sizes might be bigger.  This could mean over 1 GB of log data generated and sent to each replica over the network.
The amount of data grows further if you have nonclustered indexes on this table, which are logged in the same way.
Row Size Makes a Big Difference
Another case study could be the dbo.Comments table.  It has 3,907,472 rows, but each row is 1,424 bytes long (maximum - the Text column is nvarchar(700)).
Despite having significantly fewer rows than dbo.Votes, this table has 176,722 pages allocated to it.  TRUNCATEing dbo.Comments results in 63,792 log records and 4.86 MB of log data.
If your real row size is more in this ballpark, that could be over 7.5 GB of log data.
What to Do
Maybe your infrastructure and log files can handle several GB of data easily - if you have a 17 B row table, it seems like they should!  But I thought it would be worthwhile to mention that the amount of traffic is not necessarily insignificant, since the existing answers didn't bring this up.
Test in a non-prod environment if you can.  Measure the log file usage before and after, and make sure your prod infrastructure is set up to handle that amount of data.  Make certain that the log file has been presized to handle this truncate - having an autogrowth occur in the middle of this operation will slow things down a lot, and cause blocking.
If you can't test, do your best to estimate what the impact will be.  Use a query like this one to get the number of pages in the table.  Then divide that by 8 (to get the number of extents) and multiply by 3 to get the approximate number of log records.
My testing has had average log record sizes of around 70 bytes, but I don't know if this is typical.  But you could try multiplying the approximate log records by 70 to get the number of potential log bytes produced by the truncate.
With the AG in the mix, you could also play around with log stream compression.  I haven't used these trace flags, so I'm really just mentioning that it's another knob you can tune.

If you find that the TRUNCATE approach is too unpredictable, or too much for your systems, you could always use a normal DELETE in batches.  This uses more log in total, but you could spread it out over whatever period of time you like.  However, make sure you Take Care When Scripting Batches if you go that route.


Find Job Calling Another Job We have a job that is not scheduled to run but  was kicked off by what we suspect was another job. There is no job history but it does appear in the error logs. How can we find out if this is being called by another job? I know that we could use sp_start_job to immediately call a job within a job step but, if that is the case, how can we find it?

And, why would there not be any history for the job that ran?

Error log entry:

2017-05-01 10:01:50.40 spid6s         SPID: 131 ECID: 0 Statement Type: ALTER INDEX Line #: 1
2017-05-01 10:01:50.40 spid6s         Input Buf: Language Event: EXECUTE [msdb]. dbo.IndexOptimize @Databases = 'Database1,Database2'

How can we find out if this is being called by another job?

Search all your job steps for the string with job name that ran by other job (at least that is what you suspect).
This query is from this website.

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE   js.command LIKE N'%KEYWORD_SEARCH%'
GO

why would there not be any history for the job that ran?

Depending of how your job history is set up.  See my blog post.
Another good reference here.


Why the instance name of SQL Server Express is changing? I'm workig on a web app which is using

  SQL Server Express (Microsoft SQL Server 2016 (SP1) (KB3182545) -
  13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3
  (Build 18362: )).

The web app is using connection string to connect to DB. After searching for a few days for a proper connection string format I managed to find the format, using method described by Nik in this blog post and it is:

Data Source = np:\\.\pipe\LOCALDB#A9DE3E14\tsql\query; Initial Catalog = MyDataBaseCatalog; User ID = myuser; Password=mypassword;

Everything was working fine yesterday, but today it stopped. I discovered that the service name has changed to:

LOCALDB#B87CB983

After replacing the instance name with the new service name in the connection string the web app works well, but it's not an acceptable solution.

Can you tell me why the instance name is changing, is it any Express version limitation? How can configure the name to be permanent?

This app isn't using the regular SQL Server Express Edition. Express Edition runs as a service such that you'd see it in the Services control panel (on Windows), and there would be a stable instance name, as you're expecting.

This service you're connecting to uses the (confusingly named) SQL Server Express LocalDB. This is often referred to as just "LocalDB" to reduce the confusion of the name overlap. LocalDB runs in process--that is, it isn't a service, but is started up by the app that uses is.

LocalDB supports a number of configurations, depending on how the application is intended to be accessed. Check out the section in the docs titled "Automatic and Named Instances" as well as the section titled "Shared Instances of LocalDB".


ulimit 'open files' and MySQL max_connection When running mysql on SSD, does max_connections, if set more than the kernel's ulimit open files, be limited to the kernel's limit?

My understanding is that if the kernel's ulimit open files is 1024 for example, any value of max_connection for mysql greater (e.g, 3000) than the ulimit open files will be limited to just 1024.

My question is, does this also hold for SSD?

max_connections and ulimit and SSDs have virtually no relationship to each other.

ulimit is an upper bound (unnecessarily low at 1024) on the number of files that can be open in any 'process'.  (mysqld is a 'process'.)  It does relate to table_open_cache, which is now set dynamically based on ulimit.  (In older versions, it was not automatically set.)  However, a single table may need to open more than one file, so that 1024 is not really a limit on the number of "tables" in your databases.

See also Open_tables (bounded by table_open_cache) and Opened_tables/Uptime (frequency of opening an uncached table).

The current default for max_connections is 151, which is 'reasonable' for the 'typical' installation.  Almost no one "needs" 3000.

SSDs, as already mentioned, is new technology that is like "disks", but faster.  A few obscure things should be tuned for SSDs, but only if you have a very busy system.


Database design for handling 1 billion rows and counting We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes or so, a service fetches this data, and processes it into trips. Once the trips have been generated, the actual GPS data is usually not so important, only if the user wants to see the route on a map.

The problem is that it seems the database is struggling to keep up with the rate of data being inserted. Sometimes when the load increases, the insert time suddenly increases drastically (> 30 seconds), which in turn allows more data to be buffered, which in turn results in larger inserts and longer insert duration.

I hope to get some comments on the current design, and some of the ideas we have to improve performance, and answers to some of our questions - and any other tips people might have!

Current design

The data is currently separated into tables representing one week, and data older than a year is archived into a secondary database. The whole thing is joined together in an editable view, which is used for both inserts and reads.

Table design

Id (PK, uniqueidentifier)
DeviceId (FK, int)
PersonId (FK, int)
VehicleId(FK, int)
TokenId  (FK, int)
UtcTime  (PK, datetime2(3))
Latitude (float)
Longitude(float)
Speed    (smallint)
Heading (smallint)
Satellites (tinyint)
IOData (varbinary(100))
IgnitionState (tinyint)
UserInput (tinyint)
CreateTimeUtc (datetime2(3))

Indices

DeviceId_CreateTimeUtc_Desc
DeviceId_UtcTime_Desc (Clustered)
PersonId_UtcTime_Desc
TokenId_UtcTime_Desc
VehicleId_UtcTime_Desc

Every week currently takes up around 10 GB including indices, and currently there is around 300 GB data in the main database.

The data tables in the main database have their own filegroup with 1 file, but it is on the same disk as all other tables in the main database. The secondary database is on a different disk, but on the same machine.

I think we are also running an index rebuild job weekly, when a new table partition (week) is taken into use. No shrink is performed.

The machine is an 8-core HP with 12 GB memory, and the disk holding the main database is running RAID 10.

Ideas

Limit the amount of data stored in primary database to e.g. max 1 month. At the very least it would make the database more managable for backup/restoration, but could we expect to see a performance improvement by doing this?
Create 2 files in filegroup for current data, and distribute them onto 2 different physical partitions
Create master-slave databases holding current data, so inserts and reads are performed on different databases
Put files for current data on SSD disks (would mirroring make any performance difference with SSD disks?)

Please let me know if more info is needed. There are horribly many factors influencing performance, and probably equally many ways to tweak it.

5000 inserts per minute are about 83 inserts per second. With 5 indexes that's 400 physical rows inserted per second. If the workload was in-memory this would not pose a problem even to the smallest of servers. Even if this was a row-by-row insert using the most inefficient way I can think of. 83 trivial queries per second are just not interesting from a CPU standpoint.

Probably, you are disk-bound. You can verify this by looking at wait stats or STATISTICS IO.

Your queries probably touch a lot of different pages so that the buffer pool does not have space for all of them. This causes frequent page reads and probably random disk writes as well.

Imagine a table where you only physically insert at the end because of an ever-increasing key. The working set would be one page: the last one. This would generate sequential IO as well wen the lazy writer or checkpoint process writes the "end" of the table to disk.

Imagine a table with randomly-placed inserts (classic example: a guid key). Here, all pages are the working set because a random page will be touched for each insert. IOs are random. This is the worst case when it comes to working set.

You're in the middle. Your indexes are of the structure (SomeValue, SequentialDateTime). The first component partially randomizes the sequentiality provided by the second. I guess there are quite a few possible values for "SomeValue" so that you have many randomly-placed insert-points in your indexes.

You say that data is split into 10GB tables per week. That's a good starting point because the working set is now bounded by 10GB (disregarding any reads you might do). With 12GB of server memory it is unlikely, though, that all relevant pages can stay in memory.

If you could reduce the size of the weekly "partitions" or increase server memory by a bit you are probably fine.

I'd expect that inserts at the beginning of the week are faster then at the end. You can test this theory on a dev server by running a benchmark with a certain data size and gradually reducing server memory until you see performance tank.

Now even if all reads and writes fit into memory you might still have random dirty page flushing IO. The only way to get rid of that is to write into co-located positions in your indexes. If you can at all convert your indexes to use (more) sequential keys that would help a lot.

As a quick solution I'd add a buffering layer between the clients and the main table. Maybe accumulate 15min of writes into a staging table and periodically flush it. That takes away the load spikes and uses a more efficient plan to write to the big table.


migrate data from production to development db Sometimes I need to copy interview bookings made on our production server to test some I have the same data in order to reproduce bugs / demonstrate features with live data.

I know the easiest way is probably to restore the a backup of the live server but its not always possible. Takes ages to restore on my crappy pc. People arent happy when I copy a 3gb file over the network.

I was wondering is way I can write a query to copy the relational data over from a readonly copy of production to my dev db.

for example table a has a relation with b. Table B may also have relations to other tables. I want to copy the data from table A and also have any related records  copied as well.

we use sql server 10.50.1600

First and potentially the easiest option available:
Flash drives are dirt cheap. Literally, you can get a 4gb flash drive for less than top soil.

If you do not have physical access to a USB port then install BIDS or SSDT on your dev machine if you do not already have installed and create a simple Source to Target SSIS package. You need login access to the separate servers and you can copy individual tables as needed.  Eliminates cross server queries/linked servers and can minimize your network use to only necessary data.

Follow a standard BI staging table package for optimal results:
Steps

Truncate destination table
Extract data from source
Write to destination

If you have table constraints I would think any legitimate testing would require them to exist in your test environment, but to make your data import as simple and quick as possible it may be advantageous to drop them temporarily, until you have all your needed data. The following scripts can drop and then re-create your FK constraints. (I've had it saved for a while but took it from a co-worker or blog, if anyone recognizes I'd be happy to update with a source credit.)

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
   U.CONSTRAINT_NAME,
   U.TABLE_SCHEMA,
   U.TABLE_NAME,
   U.COLUMN_NAME
FROM
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
   DROP CONSTRAINT ' + ForeignKeyConstraintName + '

   GO'
FROM
   @table

--ADD CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   GO'
FROM
   @table

GO


AAG expansion between datacenters We have a 2 node AAG setup in the DC data center, and due to an application requirement, it requires a third node to be created at the DR site and the only option I have is at the Data center in San Jose. What I want to know is - is creating an asynchronous connection a feasible option with AAG's (not AAG+FCI) . The 3rd node will be in a different subnet.

Thanks!

You can absolutely add a 3rd node to a DR site and have it be an asynchronous replica. It'll be a multi-subnet configuration, so be sure you add the MultiSubnetFailover=True to the connection strings to get a successful connection, that is if the drivers you are using support it.

You may want to disable the 3rd node's quorum vote in your configuration. I'm assuming you already have quorum setup correctly with a fileshare witness or something else (if you don't, then get that fixed). You don't want a network glitch to take down the cluster if you also have an issue on one of the nodes at the primary site. Be sure you understand quorum and votes and what's needed to keep production up.


Cleanup process after bulk operations Not too long ago I did a bulk operation that I ended up needing to rollback. My transaction log file for that hour was 7 GB. I found that my log file is now over half the size of my data file and I'm assuming that transaction caused that growth since nothing changed with the backup schedule (the log backup has always succeeded and the full has intermittently failed recently due to space).

Both this time and in the future, what operations should I follow up with after doing large maintenance (like index rebuilds, full table updates, etc)?

I'm realizing I may need to shrink my log file back to a more typical size (since my maintenance activity was not a normal operation), but I also hesitate about this - if I have the space (I'm about to make space), should I just leave the transaction file at that size since it did need that space at one point, and will probably again (even if not during normal transactions)?

Are there any other operations I should consider during maintenance/release windows (reduced or full shutdowns)?

For reference, I have in mind our production SQL Server 2016 server with a 2008 compatibility database; currently 30 GB in size in Full recovery mode; full backups are nightly and transaction logs are hourly.

I always truncate the log file after a bulk operation. Also, typically, production log backups are set to at least once every 30 minutes. I prefer 15 minutes, but an hour is a bit long. That helps in keeping log file in check too.


How does my system understand if data got masked? I was trying to understand the difference between encryption and masking
Below statement says that real data replaced and gone!

Masking protects your data by transforming it into a readable format that’s useless to anyone who steals it. The actual data is replaced by fictional information. There is no encryption solution to revert the data to its original state. The real data was replaced and is gone forever.

What does it mean? If real data are gone, then how do I use it in my system?
For example:
I might masked EMAILID and my system wants to do some logic based on the email ID, then how do I compare the email ID in code?
Basically, if I am not using the sensitive data, why do I store it in my database in the first place?

Data masking occurs just before the data is sent to the client (your program, SSMS, etc).  In that sense, it is "replaced and gone" by the time it gets to a client that is requesting the data to view it (assuming the user accessing the data doesn't have the UNMASK privilege).  Thus, you can perform logic on it in your queries (apply where clauses to it, update it, etc).  But you won't see the real data if you include it in a SELECT statement.
However, the data is still stored on the server unencrypted.  And in fact, you can use this knowledge to figure out exactly what data is in the table - even if you don't have the UNMASK privilege.  See Joe Obbish's great post on that topic here: Dynamic Data Unmasking
The difference between that and something like "Always Encrypted" is that only the encrypted version of the data is stored, and a key must be used to decrypt and use / view the data.


creating 2 database at the same time in db2 Do you know whether it is safe to have in one shell user db2inst1 creating a database A  and having another shell the same user creating at the same time database B?

create database A # shell 1
create database B # shell 2

I am using Linux/ Cent os with DB2 V 9.7 installed.

I'm also not quite sure why you are posting this without just attempting it, but yes, this will work without problem.

The only issues you might have are:

If the NUMDB database manager configuration parameter is small enough that you can't activate both databases at the same time (in addition to whatever other databases are activated within the DB2 instance).
If you use the AUTOCONFIGURE option for CREATE DATABASE with some silly default values so you exhaust system memory
System resource contention (disk, CPU) slowing the process down.

This is really an odd question – it's hard to image a scenario where you would need to create multiple databases simultaneously.  Are you coming from a different DBMS (like MySQL or SQL Server), where the concept of a database is more like a schema in DB2?


Improve performance of query using IN() I have the following SQL query:

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

I also have an index on the Event table for the column TimeStamp. My understanding is that this index is not used because of the IN() statement. So my question is is there a way to make an index for this particular IN() statement to speed up this query?

I also tried adding Event.EventTypeID IN (2, 5, 7, 8, 9, 14) as a filter for the index on TimeStamp, but when looking at the execution plan it doesn't appear to be using this index. Any suggestions or insight into this would be greatly appreciated.

Below is the graphical plan:

And here is a link to the .sqlplan file.

Given tables of the following general form:

CREATE TABLE Device
(
    ID integer PRIMARY KEY
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY,
    Name nvarchar(50) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY,
    [TimeStamp] datetime NOT NULL,
    EventTypeID integer NOT NULL REFERENCES EventType,
    DeviceID integer NOT NULL REFERENCES Device
);

The following index is useful:

CREATE INDEX f1
ON [Event] ([TimeStamp], EventTypeID)
INCLUDE (DeviceID)
WHERE EventTypeID IN (2, 5, 7, 8, 9, 14);

For the query:

SELECT
  [Event].ID,
  [Event].[TimeStamp],
  EventType.Name,
  Device.ID
FROM
  [Event]
INNER JOIN EventType ON EventType.ID = [Event].EventTypeID
INNER JOIN Device ON Device.ID = [Event].DeviceID
WHERE
  [Event].[TimeStamp] BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.EventTypeID IN (2, 5, 7, 8, 9, 14);

The filter meets the AND clause requirement, the first key of the index allows a seek on [TimeStamp] for the filtered EventTypeIDs and including the DeviceID column makes the index covering (because DeviceID is required for the join to the Device table).

The second key of the index - EventTypeID is not strictly required (it could also be an INCLUDEd column); I have included it in the key for the reasons stated here. In general, I advise people to at least INCLUDE columns from a filtered index WHERE clause.

Based on the updated query and execution plan in the question, I agree that the more general index suggested by SSMS is likely the better choice here, unless the list of filtered EventTypeIDs is static as Aaron also mentions in his answer:

CREATE TABLE Device
(
    ID integer PRIMARY KEY,
    Name nvarchar(50) NOT NULL UNIQUE
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY,
    Name nvarchar(20) NOT NULL UNIQUE,
    [Description] nvarchar(100) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY,
    PLCTimeStamp datetime NOT NULL,
    EventTypeID integer NOT NULL REFERENCES EventType,
    DeviceID integer NOT NULL REFERENCES Device,
    IATA varchar(50) NOT NULL,
    Data1 integer NULL,
    Data2 integer NULL,
);

Suggested index (declare it unique if that is appropriate):

CREATE UNIQUE INDEX uq1
ON [Event]
    (EventTypeID, PLCTimeStamp)
INCLUDE
    (DeviceID, IATA, Data1, Data2, ID);

Cardinality information from the execution plan (undocumented syntax, do not use in production systems):

UPDATE STATISTICS dbo.Event WITH ROWCOUNT = 4042700, PAGECOUNT = 400000;
UPDATE STATISTICS dbo.EventType WITH ROWCOUNT = 22, PAGECOUNT = 1;
UPDATE STATISTICS dbo.Device WITH ROWCOUNT = 2806, PAGECOUNT = 28;

Updated query (repeating the IN list for the EventType table helps the optimizer in this specific case):

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2,
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND EventType.ID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

Estimated execution plan:

The plan you get will likely be different because I am using guessed statistics. The general point is to give the optimizer as much information as you can, and provide an efficient access method (index) on the 4-million row [Event] table.


Dividing data from one table to multiple storage I have the following tables:

[Table_A]
[Table_A_Changes]

The two tables are pretty large (more then 50GBs). The first one contains some data, and the second one records' changes.The database is in FULL recovery model (AlwaysOn).

WRITES:

[Table_A] - constantly/many
[Table_A_Changes] - constantly/many

READS:

[Table_A] - constantly (in 99% of the cases only data for the past 6 months)
[Table_A_Changes] - rearly (in 99% of the cases only data for the past month)

UPDATES:

[Table_A] - constantly/many (in 99% of the cases only data for the past few months)
[Table_A_Changes] - no updates

I am going to use a cloud storage where the fast storage is too expensive to hold all the data.

So, I want to store only the the new data in [Table_A] on fast storage, and the older data on slow/cheap storage.

Is there a way to achieve this?

Also, since the major part of the data is not (read/update) I want to perform only partial backups on the data stored into the fast storage only.Let's say a full backup each week and a partial backup each 12 hours.

I am looking both for storage and recovery strategies (any links are welcome, too).

Create a new table Table_A_Archive with exactly the same schema as Table_A_Changes.  Partition both these tables by, say, one-month date ranges.  As data becomes stale in Table_A_Changes swap that partition out and swap it into Table_A_Archive.  Your data retention policy can be efficiently enforced this way, too.

A view which unions the two tables can make querying simpler.


SQL Job is getting failed randomly while trying to send the alert email. need help We have configured a alert which triggers the email using a job. But the job fails randomly during the day and we don't receive the alert related mails. If I clear the ExternalmailQueue it starts working again.

Below are the logs I got before the job gets failed.

Windows Event Logs

Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:Exception of type 'System.OutOfMemoryException' was thrown.
Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:The connection is not open.

Database Mail Log

08/30/2017 21:30:31,,Information,15826,DatabaseMail process is shutting down,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Information,15825,Database Mail shutting down. Mail queue has been stopped.,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Success,15824,Activation successful.,,,,8/30/2017 9:30:31 PM,sa
08/30/2017 21:30:20,,Success,15823,Mail successfully sent.
08/30/2017 21:30:19,,Success,15821,Mail successfully sent.
.
.
.

08/30/2017 21:30:10,,Error,15803,

The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2017-08-30T21:30:10). Exception Message: Cannot
send mails to mail server. (Exceeded storage allocation. The server response
was: 5.3.4 Message size exceeds fixed maximum message size).
),2240,19923,,8/30/2017 9:30:10 PM,sa

Sql Agent Log

08/30/2017 21:30:38,,Error,[440] SQLiMail send mail failure (reason: )
08/30/2017 21:30:38,,Error,[298] SQLServer Error: 14641 Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000]

Question

What do we have to reconfigure to reduce the errors when sending mails?

Pretty clearly, the mail server is sending a response to SQL Server:

  Exceeded storage allocation. The server response was: 5.3.4 Message size exceeds fixed maximum message size

The size of the email being sent by SQL Server is too large.


Keeping postgres connection behind load balancer alive during long-running query (vacuum) Background:
I have a postgres instance behind a load balancer (AWS Classic Load Balancer). I want to access the database from another cluster through the load balancer and run queries. I cannot change the database setup (instance behind load balancer).
When executing long-running queries (VACUUM in my case), the connection is dropped by the load balancer after 1 hour. This happens because the connection is inactive and the load balancer terminates it.
tcp_keepalives_idle does not work here and the load balancer checks for actual transmitted data, not just packets (see the update on this question)
Problem:
How can I keep the connection alive while the VACUUM is running?
Is there a way to send data over the connection to indicate that it is still alive?

There is no way to have activity on a database connection that is waiting for a server response.
I'd say that that is a bug in the load balancing software, and you should report it. Your use case is a valid one.


Upgrading the SQL Server version My SQL Server version at home is 661 and at my school's is 655, so every time I try to open my web's SQL Server database at school it says there is an error:

So I thought that uploading the SQL Server version at school will fix it (or downgrading the SQL Server version in my home).

On both computers there is Microsoft Visual Web Developer 2010

How do I upgrade the SQL Server version?

Maybe a compatibility level will help you. You can define it in the options of your database under compatibility level. Change it to a version earlier of your current sql server at home. This way I'm sure it will be correctly used in your school.


Why some files in data directory of a Postgresql cluster are symlink to files of another cluster? I did use pg_upgrade.
I had a cluster of Postgresql with version 9.6 on my machine, and then I did start another cluster with version of v12. Each of them had separate data directories, in a directory scheme like this:
./postgresql/
├── 12
│   └── main
└── 9.6
    └── main

Then I turned off the 9.6 cluster and started using the v12 one. But now, by accident, I found out that some of the files in the base directory of the v12 cluster are not files, but symlinks to files in base directory of the 9.6 one; For example:
./postgresql/12/main/base/16401/35093 -> ./postgresql/9.6/main/base/16402/35093

Why is it so? And, more importantly, how is it so?! I mean, I thought that the files inside base directory are so internal to each DB that this could not be possible at all.
And, can I do something to disable using symlinks?

This is the result of a pg_upgrade with the -k option, and these are not symbolic links, but hard links.
If you didn't manage to start the old cluster after pg_upgrade everything is fine. You just forgot to remove the old cluster after upgrading. Do that right now:
rm -rf /var/lib/postgresql/9.6/main

If you use -k, the upgrade process doesn't copy, but link the files. That makes the upgrade much faster, but renders the old cluster unusable.


The cost of left out join versus a select I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3

I turned on logging for the SQL queries to test some code behavior.

So, assume 2 tables, OneToMany relation. In the code, this will have fields on the objects to represent it.

So for instance, class A will have a list of class B instances as a field.

Suppose I want to get those instances of class B, that are related to class A.

I could simply do return aInstance.bInstances

Or, I could write an Ebean function that asks for the ID of A and gets all the instances from the B table where A is present. The result is the same.

I did both, to see what SQL query Ebean would generate. And it does generate a different one.

The first, asking for the instances related to the object, will get Ebean to generate a statement with a left outer join based on the ID of the instance of A.

The second will do a select on the B table.

I'm wondering about the performance of this. On my test setup, which is fairly small, the left outer join is always faster. But I wonder about production databases that are constantly being updated. I did some googling in to the performance of left outer join and it's pretty much all bad things I've found.

So, what's the hidden cost here? Will left outer join works fast at first but mess things up later? Will the simple select statement prove more reliable, if a bit slower?

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way:

SELECT * FROM b WHERE a_id = ?

If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't clarify).

SELECT a.a_id, b.*  -- often you don't need all columns
FROM   a
LEFT   JOIN b USING (a_id)
WHERE  a.some_column = ?;  -- not defined

One integrated query is typically faster than two separate queries. LEFT [OUTER] JOIN or [INNER] JOIN perform about the same here.


How to get the sum of a particular item id in a column Item         ItemName        PackageID  Specifics    brandname  GRNQty  UnitPric GRNValue
102814742801 ALL BRAN FLAKES 1PKT       Bran.        KELLOGS    7       15.9    111.3
102814742801 ALL BRAN FLAKES 1PKT                    KELLOGS    50      15.9    795
100314860301 ALMOND FLAKED   1PKT       Alm.         HYSON      20      52      1040
100314860301 ALMOND FLAKED   1PKT                    HYSON      30      52      1560
101814899665 ALMONDS ROASTED 1KG                     HOMELINE   250     59.5    14875
101426707397 ANCHOVY FILLETS 1TIN       Anch.        VARIOUS    4       34      136
101426707397 ANCHOVY FILLETS 1TIN                    VARIOUS    8       34      272

I'd like to get the sum of a particular Item in the column. I want to do Sum of GRNQty as well as GRNValue of Same Type Item in the table.

Particular Item

SELECT SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable WHERE Item = '102814742801';

All Items By Item

SELECT Item,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY Item;

All Items By PackageID

SELECT PackageID,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY PackageID;

All Items By brandname

SELECT brandname,SUM(GRNQty) GRNQtySum,SUM(GRNValue) GRNValueSum
FROM mytable GROUP BY brandname;


Log Shipping: Unable to change from Standby mode to No Recovery mode I have tried to change the mode from standby to no recovery in log shipping environment. But the restore job fails automatically. And it did not change to restoring mode. Can anyone help me to resolve this?

If I understand correctly, that is not possible as you described it.

Kendra Little described the actions that are available to you.  Please read her article at:

https://www.brentozar.com/archive/2015/01/reporting-log-shipping-secondary-standby-mode/

A couple of important points:

Logs Restore More Slowly if You Use STANDBY (Readable) Mode
Everyone’s Kicked Out When You Restore Transaction Log

So, you get to choose between (1) slower restores for STANDBY MODE and (2) having faster restores, with the penalty of losing the connection.


MAXTRANSFERSIZE setting causes restore to fail I have a database that seems to be functioning fine with no apparent errors except that any full backup taken is broken - attempting a restore fails with an error "RESTORE detected an error on page (18469:-1767164485)"

DBCC CheckDB on the database completes without errors.

EDIT #1

The backup is created with the following command:

BACKUP DATABASE @DatabaseName
TO
DISK = @backupFile1,
DISK = @backupFile2,
DISK = @backupFile3
WITH COMPRESSION, RETAINDAYS = 0, FORMAT, INIT, SKIP, NAME = @DatabaseName

EDIT #2

I just completed a backup using the GUI database backup in case my script was wrong somehow - This gave the same result of a damaged backup.

--

This only started happening last night.  The only significant change that I am aware of is that I changed the 'auto-shrink' option of the database (it was originally set to true).

Is anybody aware of a problem in this area with SQL Server 2012 (specifically I'm on version 11.0.3128)?  It could just be coincidence or related to something else entirely but that seems the most likely cause at the moment.

In addition does anybody have any advice on what to do in such a situation?  The database is functioning fine (as far as I can determine), but I don't care to be without backups for very long...!

This turned out not to be an error with the backup, but with the restore command that I was using.

RESTORE verifyonly  FROM
DISK = @backupFile
 WITH FILE=1,  STATS=5,  MAXTRANSFERSIZE=4194304

returned the error "Damage to the backup set was detected"
some
whereas

RESTORE verifyonly  FROM
DISK = @backupFile
 WITH FILE=1,  STATS=5

returned "The backup set on file 1 is valid."

Which is ironic given that I was only using the MAXTRANSFERSIZE option to try to speed up the restore, but instead it cost me half a day!

The error only occurs on backups taken from last night onwards.  Earlier backups verify fine whether I specify MAXTRANSFERSIZE or not, which is strange, but I guess I can live with not knowing the reason why that is unless somebody has an insight into that behaviour?


performance difference between < and <= I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue.

I know this is very abstract question, but still if you can assists with what exactly the issue here

For example:

select id
from table
where year < 2010

Against

select id
from table
where year <= 2009

Yes, there is a difference, and the issue is that the estimated selectivity (thus cardinality) will be different in the 2 cases.

For a simple example, lets say the year column has years in it from 2000 (low_value) to 2014 (high_value), so there are 15 distinct values (num_distinct), they are evenly distributed, 1000 rows for each, 15000 rows (num_rows) in total in your table.

In the first case, where year < 2010 (limit = 2010), the estimated selectivity will be:

(limit - low_value) / (high_value - low_value) = (2010 - 2000) / (2014 -2000) = 10 / 14

Cardinality = 10/14 * 15000 = 10714

For the second case, where year <= 2009 (limit = 2009), selectivity is calculated as:

(limit - low_value) / (high_value - low_value) + 1/num_distinct = (2009 - 2000) / (2014 - 2000) + 1/15 = 9/14 + 1/15

Cardinality = (9/14 + 1/15) * 15000 = 10642

Not a big difference, but it can still affect the optimizer so that it choses another plan.

Above is explained in detail by Jonathan Lewis in his book "Cost-Based Oracle Fundamentals".


Mysql query in Database How to get all the tables from database with all values in tables using single query..
I tried using

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);
$arrayCount = 0
while ($row = mysql_fetch_row($result)) {
$tableNames[$arrayCount] = $row[0];
$arrayCount++; //only do this to make sure it starts at index 0
}
foreach ($tableNames as &$name {
$query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
mysql_query($query);
}

but it gives me only tables name

Use the information_schema.columns table

i.e.

select table_name,column_name from information_schema.columns


Sqllogship.exe: replace with powershell? To get more control, and to enable another transport. Is there a direct replacement in powershell?

I coded a script to ship logs manually using PowerShell. You can find it here

Basically it does what native Log Shipping does: it takes transaction log backups, copies to a shared folder and restores to the secondary WITH NORECOVERY.

Some limitations apply:

Only a single secondary is allowed
No support for STANDBY

I coded it this way because it was enough for what I had to do, which is what you do 99% of the time with log shipping.


How to pass SSRS params into a URL I have Native SSRS configured on a 2012 instance (11.0.2100). I have two reports with images that act as buttons to toggle between the chart report and the detail report. I want to configure the action URLs to dynamically pass in parameters when switching between the reports, so that the defaults (derived from a query) are not used when switching views, the manually inputted params would persist.

I have tried the below versions of my URL and, in all cases, the default (query driven) parameters are used to run the report, not the passed in parameters.

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&rs:StartDate=2015-10-01&rs:EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&StartDate=2015-10-01&EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=2015-10-01&EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=10/1/2015&EndDate=10/31/2015

Any ideas on where I'm formatting my URLs incorrectly?

(I don't have an SSRS server handy at the moment to test/confirm this, so I'm going a bit off-the-cuff here, and might not be 100% spot-on)

I've never had luck with using the ReportManager URLs when I want to pass in specific parameters. Try using the ReportServer URLs instead.

BOL gives this example format for passing dates using the ReportServer URLs:
http://myserver/ReportServer/Pages/ReportViewer.aspx?%2fProduct_and_Sales_Report_AdventureWorks&SellStartDate=7/1/2005


Run Query Against All DB's on Server I am trying to run this against all the DB's on my server and not just on "TEST_1".  Is there a simple way to do this?  Ideally the select would also return the DB name.

use TEST_1
go
declare @DataSetID int

set @DataSetID = 1;

declare @Linkdate datetime

set @Linkdate = '8/31/2015';

select distinct I.InterfaceName
    ,case
        when B.brokerCode is null
            then 'Not Populated'
        else B.BrokerCode
        end
    ,case
        when B.brokerDescription is null
            then 'Not Populated'
        else B.BrokerCode
        end
from transactions T
inner join interfaceoriginations I on I.OriginationID = t.OriginationID
left join brokers B on B.BrokerID = t.BrokerID
where linkdate = @linkdate
order by 1
    ,2

OK, replacing the previous answer with this one.  USE OF ms_foreach stored procedures is not recommended.  This is a little more code but it's more reliable.  You can limit the databases you search through, it checks to make sure the transaction table exists, and still provides the database name in the results.  Note - this will provide a separate result set for each DB. If this is not desired, create a temp table and dump the results into the temp table by uncommenting the table creation block for #Results, the INSERT just before "EXEC sp_executesql @runMe" and the final SELECT.

    DECLARE
          @DataSetID INT
        , @Linkdate VARCHAR(19)
        , @runMe NVARCHAR(4000)
        , @dbCnt INT
        , @dbName VARCHAR(500)
        , @tableName VARCHAR(500)
        , @objName VARCHAR(500)

    --initialize variables
    SET @DataSetID = 1;
    SET @Linkdate = '8/31/2015';
    SET @tableName = 'transactions'; --assuming if this is present the other are as well
    SET @dbName='';

    /*
    --CREATE A TABLE TO CAPTURE ALL RESULT
    IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
        DROP TABLE #Results;

    CREATE TABLE #Results
    (
          DBName            VARCHAR(500)
        , InterfaceName     VARCHAR(255)
        , BrokerCode        VARCHAR(255)
        , BrokerDescription VARCHAR(1000)
    )
    */

    IF OBJECT_ID('tempdb..#DBList','U') IS NOT NULL
        DROP TABLE #DBList;

    SELECT name
    INTO #DBList
    FROM sys.databases;

    SELECT @dbCnt=COUNT(1)
    FROM #DBList;

    WHILE @dbCnt > 0
    BEGIN
        SELECT @dbName=MIN(name)
        FROM #DBList
        WHERE name > @dbName

        SET @objName = @dbName+'..'+@tableName

        IF OBJECT_ID(@objName) IS NOT NULL
        BEGIN
            SET @runMe = N'
            select distinct '''+@dbName+''' AS DBName, I.InterfaceName
                ,case
                    when B.brokerCode is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
                ,case
                    when B.brokerDescription is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
            from ['+@dbname+']..transactions T
            inner join ['+@dbname+']..interfaceoriginations I on I.OriginationID = t.OriginationID
            left join ['+@dbname+']..brokers B on B.BrokerID = t.BrokerID
            where linkdate = '''+@linkdate+'''
            order by 1
                ,2;'

            --INSERT #Results
            EXEC sp_executesql @runMe
        END

        SET @dbCnt = @dbCnt-1
    END

    --SELECT * FROM #Results


Do I need SQL_NO_CACHE for write intensive query? When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it !

Questions

Do I need to do SQL_NO_CACHE ?
Is it gonna increase performance and insert rate?

No.

SQL_NO_CACHE is only relevant for SELECT statements. It is not an option for INSERT or LOAD DATA statements. "The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache"

Moreover, the query cache itself has to be enabled, then is invalidated when the underlying dataset has changed.

If you're performing a series of SELECT statements at the same time as the bulk load, then you will save a few microseconds by using SQL_NO_CACHE, but it is only an option as part of the SELECT.


sp_grantdbaccess cannot be run in a stored proc owned by DBO? Maybe I'm getting this wrong, but...
I have a SQL Server 2000 database with a stored procedure that is owned by DBO. This stored procedure is calling sp_grantdbaccess, which can be run by DBO, according to MS documentation.
and I'm running a Windows user that belongs to a database role that has execute permission on this stored procedure.

If I understand ownership chaining correctly (which I obviously don't), I should be able to run this stored procedure, since it is run with impersonation as DBO.

But alas, it is telling me that the user doesn't have permission to run sp_grantdbaccess.

What can I do to solve this?

Ownership chaining works only inside the database. sp_grantdbaccess lives in master and is therefore excluded from ownership chaining. Even worse, according to the sp_grantdbaccess topic in SQL Server 2000's Books Online:

  Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.

Erland Sommarskog wrote a great article on ownership chaining; however, it covers SQL Server 2005 and later, and there were some significant changes in the security model, so proceed with care when reading.

The only way I can think of to make this work is a hack: Create a loopback linked server and elevate the permissions that way: https://stackoverflow.com/questions/982782/sql-server-2000-execute-as

But be aware that that opens a big security hole.

So my best recommendation - I know you probably don't want to hear this now - is to upgrade to a more modern version of SQL Server.


Writing MongoDB logs to a remote logging server I am trying to forward MongoDB logs from several remote servers to a central logging server. To do this, I changed my mongod.conf files to use syslog like so:

systemLog:
  destination: syslog
  syslogFacility: local3

I then configured rsyslog to forward messages like so:

if($syslogFacility-text == "local3") then{
  if($syslogTag contains "mongod") then{
    action(type="omfwd" target="MY.SYSLOG.SERVER.IP" port="PORT" protocol="udp")
    stop
  }
}

My logging server is configured to write any messages sent at that port to my central mongod.log file.

My question is; is there a better way to do this? For example, could I specify my logging server's IP/PORT in mongod.conf so I don't have to rely on each Mongo server's local rsyslog?

Mount a drive from central logging server to the MongoDB server.
Update the MongoDB configuration file to redirect the logs to that
mounted drive.


Default value format of a stored procedure parameter(of datatype : money) With my query:

SELECT (CAST(0.00 AS MONEY))

I am expecting a result : 0.00

But it shows me the result: 0,00(with comma)

I checked this query ,when i found that,the CREATE PROCEDURE script displays the default value as
[money] = 0,0000, for one of the input parameters of a procedure.

How can i make the CREATE PROCEDURE display it with '.' instead of ','?

I checked the Decimal symbol in the Regional settings and changed it from ',' to '.'

Now,the CREATE PROCEDURE display it with '.' instead of ','

Thank you for your suggestions!


Oracle and MS-SQL-like row counts My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing.

Microsoft SQL keeps very good metadata, allowing a query such as:

SELECT SUM(rows) FROM sys.partitions
WHERE object_id = object_id('TABLE_NAME')
AND index_id < 2;

(There are other methods with different metadata). This provides a nearly perfect row count, perhaps omitting certain in-progress transactions or other minutiae. Such techniques work for all but the most demanding situations and are practically instantaneous.

In Oracle, we can use statistics:

SELECT num_rows
FROM all_tables
WHERE table_name = 'TABLE_NAME'

This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

I can sacrifice significant accuracy for some improvement in speed using sampling:

SELECT COUNT(*) * 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)

However this is inaccurate by design, still quite slow (115 seconds on a 500M row test), and is almost useless when the application does not already have a row count estimate. Running that SQL on a table with 800 rows is like asking, "what is the price of that candy bar, give or take $75.00?")

Does Oracle provide a practical means of getting an accurate, fast row count for arbitrary tables, such as that which Microsoft SQL and others provides?

SELECT COUNT(*) * 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)

  …still quite slow (115 seconds on a 500M row test)…

You will find that SAMPLE BLOCK is a great deal faster because it does a tiny fraction of the IO that SAMPLE does. Of course if the data isn't evenly distributed it will cause the result to be less accurate, but this can be more than offset by increasing the sample size.

…but…

  In Oracle, we can use statistics…This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

This is the real problem. Out of date stats cripple the CBO and are therefore "a bad thing". You likely need to change your DBA policy.


mysql 8 information_schema.processlist -> correct approach to cache it? I have a high concurrency mysql 8.0 server.
Thousands of tasks happen per second (up to a million rows per second read in average)

In some of the tasks that can be very performance intense I have an emergency switch to prevent the database queue from overflowing.

I read the number of processes (sometimes specific ones) from information_schema.processlist and if they exceed a certain value the task is skipped until the database is back in time.

So much so good, that worked perfectly fine in mysql 5.7 and before, but in mysql 8.0 they removed the query cache which might be the reason why it can not deliver such performance anymore.
In my case the server quickly is overflown with these SELECTS.

So I decided to run a background task that refreshes a table every second with a dump of the processlist.
I am using Innodb as MEMORY has more blocking issues.

This is the syntax I run every second:
processlist is "LIKE information_schema.processlist but I've changed the Info blob to a varchar"

BEGIN;
truncate table processlist;
replace into processlist SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,1024) AS INFO FROM information_schema.processlist WHERE Command != 'Sleep';
COMMIT;

I am using a transaction to prevent race conditions and just truncate + replace into.

However I am not sure if this is the best solution to populate a "mirror" table.

So my question is: is this a good approach (especially not sure what truncate might  mean on performance) ? Any ideas how to do this better ?

(I agree that Threads_running would be better.)

As for the Processlist, simply have a single program doing:

Collect processlist
parse and analyze it (there is probably no reason to have each worker do the same processing here)
store the minimal amount of info in some table dedicated to this purpose.  This is probably a single UPDATE statement.  (Not a much heavier TRUNCATE.)
Repeat forever -- Do not repeat every second; do it continually or with a slight delay in the loop.

The reason for not using a cron job:  When the server is too busy, you could get a new copy of the job starting before the first finishes.  It is much better to simply avoid that situation rather than use transactions (or whatever) to deal with it.

MEMORY should be fine, but might not be as fast as InnoDB.  The simple UPDATE is minimally invasive, so the storage engine should not matter much.

No transactions; minimal overhead.

Probably step 2 boils down to the equivalent of SHOW GLOBAL STATUS LIKE 'Threads_running';

If it takes more effort to measure how "busy" the server is than to simple forge ahead, you are wasting your time.

(A side note:  REPLACE is worse than INSERT in this case -- REPLACE does a DELETE, but you have already TRUNCATEd!?)


Has the postgres sorting order changed between versions 11.5 and 13 Doing a simple query to a postgres v 11.5 database yields the following results
sql select * from table order by name.;
[2021-03-09 13:27:49] testing.INFO: Hall, Hunter and Allen;;;;Hall PLC;;;;Miller, Lloyd and

However doing the same query on the newer version 13 produces
Hall PLC;;;;;;Hall, Hunter and Allen;;;;;;Miller, Lloyd and Bailey

I can't explain this other than what result i'm given and I can't find any more information on this matter.
Can anyone point out the change thats effected the output?

The sorting order for strings in PostgreSQL depends on the collation used.
This collation is taken either from the operating system C library or from the ICU library; if you didn't specify anything, it is the C library.
So if your sorting order changed, that means that your C library changed. There is no direct connection to PostgreSQL.
Note that when you upgrade the operating system C library, you should reindex all indexes on string columns to avoid data corruption.


Nullable columns or jsonb for storing user profile? I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email and phone. I anticipate that there may be other columns added later, such as mobile and website. Additionally, there may be other non-related fields, such as settings/preferences, saved searches, etc.

I've already decided that I don't want to use any form of key-value store (or any schema involving many-to-many relations) for this, unless there's an extremely good reason for it.

Pros of jsonb:

Can store multiple values for each "column" if necessary
Adding new field just needs JS coding and documentation

Cons of jsonb:

Overhead of storing the "column" name as a string for each "row"
Wonky to perform comparison queries (I think N/A to my usage scenario)
Have to expect the unexpected

Anything else to add to this list of pros/cons? As much as I'd like to simply use nullable columns, I think it would be negligent to ignore jsonb - it seems like a compelling choice.

You can use "best of both worlds". Implement every regular column you know of as dedicated column (with exact proper data type, possibly with NOT NULL / CHECK / PK / FK etc. constraints to enforce detailed rules, default values and comment for documentation) and add a "catch-all" jsonb column for very rare cases / later additions / highly volatile attributes that do not need any of the features mentioned above.

To be clear: "highly volatile" does not mean the value is updated a lot, which would clearly speak against any document type like jsonb, making updates more complex and expensive. It means the attribute per se (on the meta level) changes a lot (different names, rare and and pops in and out of existence over time or similar).

Most people underestimate how cheap NULL storage really is:

Do nullable columns occupy additional space in PostgreSQL?
Measure the size of a PostgreSQL table row

Related:

Does it make sense to store a couple of Boolean values as array?
Is there a name for this database structure?
jsonb and primary/foreign keys: which performs better in PostgreSQL?
Does JSONB make PostgreSQL arrays useless?


What about redundancy if distributor goes down? I'm setting up load balancing, transactional replication and mirroring. However, what I actually thought might work from the beginning, making the system redudant doesn't actually work because a Publisher can't have multiple distributors.

Preferable:

This is what was intended to do from the beginning:

Option2:

This is what we have to go with and my concern is that the weak spot is if remote Distributor server goes down it will stop syncing out the latest data. How can I make the distributor redundant?

There is no support for Distribution failover in MSSQL replication.

One solution to this limitation is to use Windows clustering on the Distribution database. See more about clustering here: http://sqlmag.com/sql-server-2008/planning-and-implementing-sql-server-cluster http://sqlmag.com/sql-server-2008/set-sql-server-2008-cluster http://sqlmag.com/sql-server-2008/upgrading-sql-server-2005-cluster-sql-server-2008