Postgres replication/replication to server outside firewall I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been looking at slony and streaming replication - in both these cases, the slave server needs to talk back to the master, for security reasons, I would like that not to be the case. Ideally, I would like the master just to send the changes off to the slave without expecting any answers. are there any systems that act like this?

(Of course, I know that if the slave goes down, or for some other reason is not able to receive the data, the master will not know, but as we have other tools to keep an eye on server problems, this is acceptable)

You can do this with the log-shipping standby functions, which is really an extension of continuous archiving.

You first configure PostgreSQL to write all* changes to the Write-Ahead Log by saying:

wal_level = hot_standby
archive_mode = on

Once a log file is full (16 MB of writes) or a configurable timeout occurs (archive_timeout), PostgreSQL starts writing to the next log file, and it calls your archive_command with the filename of the log it just finished writing. Your archive_command would be a script that uses some other appropriate technology to push this log file off the master -- maybe you want to upload it to an object store like Amazon S3, or maybe you want to use SCP to push it straight onto the slave, or maybe you want to do something else entirely.

You'd set up the slave similarly to the master, except its data directory would include a recovery.conf saying standby_mode = on. If the master copies logs straight into the slaves' pg_xlog directory, you're done; if not, the slave will need a restore_command script to act as an inverse of archive_command and retrieve the indicated archive log from wherever you put it.

Lastly, copy over a base backup (as described in the continuous archiving documentation), start PostgreSQL, and your slave will be following the master without needing any direct communication.


What's the difference between quiesce the instance and enable restricted session? You can set the database in quiesced mode by the following command.

  alter system quiesce restricted;

After running this command all sessions except the ones that have DBA privileges will hang there.

Also you can enable the restricted session like this:

  alter system enable restricted session;

After running this command, just the users that have enough privileges can connect to the database.

My question is: are there any other differences between them? When should use them?

Thanks,

In quiesce restricted, all running sessions complete their current task, then at the point that they become INACTIVE in v$session, they cannot execute any more SQL until the system is unquiesced. In enable restricted session no new connections can be made, but you will need to kill existing sessions or they will simply continue. Quiescing is much less impactful, as your users might only notice the database pauses then resumes. Restricted session I use most often in combination with startup force if I absolutely need to perform some maintenance that cannot be done online, then I disable it and do another startup force and reconnect the apps.


How Can I properly connect a replica set to split write and read with ApsaraDB for MongoDB As Replica Sets in MongoDB store data in multiple copies for improved data reliability and high service availability.

My Question is that:

How Can I properly connect a replica set to split write and read with ApsaraDB for MongoDB

To correctly connect to a replica set, you must first understand the connection string URI of MongoDB. All official drivers support connection to MongoDB using a connection string.

A connection string includes the following content:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

mongodb:// is a prefix indicating a connection string.

username:password@ indicates the user name and password that must be specified if authentication is enabled.

hostX:portX indicates the IP address and port number of each member in the replica set. Multiple members are separated by commas (,).

/database indicates the database to which the user account belongs during authentication.

?options indicates additional connection options.

To implement read/write splitting, add readPreference=secondaryPreferred to options so that read requests are prioritized to secondary nodes. For more information about read options.

Connections limit: Add maxPoolSize=xx to options to limit the client connection pool to a specified number (xx) of connections.

To confirm writes with the client only after data is successfully written to a majority of nodes, add w= majority to options. For more information about write options.

Connection to ApsaraDB for MongoDB is used as an example here. When you buy an ApsaraDB for MongoDB replica set, you can obtain the name of the replica set and the addresses of replica set members.

For more detailed Answer Please Click Here


How can I setup replication with the DNS name instead of the Server Name We are using SSL certificates from a public CA on each SQL Server in our (push) transactional replication topology, and wish to have the EncryptionLevel = 2 for our Distributor Agents. Currently EncryptionLevel is set to 1, and works fine. (The difference between 1 and 2 is that 1 tells the agent to just to use encryption, and 2 tells it to also validate the certification chain of the SSL certificates)

The SSL certs are from a public CA. The Subject Name is the full Windows FQDN of the server (SERVER1.mywindowsdomain.com) and the Subject Alternative Names list contains the full DNS FQDN (MYAPP.MYDOMAIN.COM).

I can confirm that using SSMS I can correctly connect to each SQL Server without ticking 'Trust Server certificate' in the connection dialog, meaning that the certificate chain is being properly validated successfully.

When I change the EncryptionLevel to = 2, our Distribution Agents can no longer connect to the Publisher/Distributor running on the same server. This would make sense because they are trying to connect to SERVER1\DEV, whereas the SSL certificate is for a public DNS name - MYAPP.MYDOMAIN.COM, and therefore chain validation fails.

I am therefore looking for a way to setup replication from scratch, but using the DNS name (MYAPP.MYDOMAIN.COM) instead of the machine name (SERVER1).

However, when I execute the following command:

exec sp_adddistributor @distributor = N'MYAPP.MYDOMAIN.COM\DEV', @password = N'12345'

Then I get the following error message back:

  Msg 18483, Level 14, State 1, Line 48 Could not connect to server
  'SERVER1\DEV' because 'distributor_admin' is not defined as a
  remote login at the server. Verify that you have specified the correct
  login name. . Msg 14114, Level 16, State 1, Procedure
  sp_adddistributiondb, Line 169 [Batch Start Line 65] The server
  'SERVER1\DEV' is not configured as a Distributor.

Note that this command worked fine in the past when it was this:

exec sp_adddistributor @distributor = N'SERVER1\DEV', @password = N'12345'

After much googling, I have tried many variations on this. I have tried:

Using Client Aliases (32+64 bit) on the Publisher
Varying how my SSMS session is connected to the server, i.e. the connection dialog server name. I've tried using SERVER1\DEV, MYAPP.MYDOMAIN.COM\DEV, and also SERVER1.mywindowsdomain.com\DEV.

Any suggestions on what to try next would be appreciated. I'd be happy to explore either a) getting the Distribution Agents to be able to validate the certificate of the local server [but keeping in mind I cannot modify the SSL certs], or b) re-creating replication entirely from scratch so that it uses the DNS FQDN's, ie. MYAPP.MYDOMAIN.COM\DEV.

 Note: Just to head off anyone that googles that error message and suggests that our SQL server might have been renamed, that is definitely not the case. The issue is the machine name vs the DNS name.

I was able to get this working, but with a fairly unsatisfying workaround - so if anyone else can contribute a better solution, please do. I am concerned that this solution will have unknown side-effects.

The broad steps are:

Remove everything to do with replication from the Publisher/Distributor, up to and including the distribution database.
Rename the SQL instance using the code sample pasted below. The new name should be the Machine FQDN (not the DNS FQDN). This is important so that it matches the name of the SSL certificate. Reboot the server (apparently just restarting the SQL instance isn't enough)
Redeploy the replication setup.
Change the EncryptionLevel setting to = 2 for all Distribution Agents.
Check all Distribution Agents are working fine.

Now, when I get to the sp_adddistributor command, it works fine if I use the Machine FQDN, for example:

exec sp_adddistributor @distributor = N'SERVER1.mywindowsdomain.com\DEV', @password = N'12345'

And indeed, EncryptionLevel can be set to 2 successfully for the Distribution Agents.

Known side-effects:

Replication Monitor no longer connects automatically to the Publisher when it is launched from SSMS. I should note that it does work fine when being launched on the Publisher/Distributor server using Windows Auth, but does not connect when I launch it from our Jump Host and try to connect as 'sa'. To make it connect in this situation I have to add the Publisher, and then subsequently edit the connection properties.  I suspect that this issue may be somehow complicated by the fact our servers are all Case-Sensitive, but I have no evidence to support this suspicion.
When using Distributor View in Replication Monitor, the right-panel list of Publications is always blank. The Publications do however show up in the tree view on the left. There is no problem using the Publishers View.

Code to rename server:

sp_dropserver 'SERVER1\DEV'
GO
sp_addserver 'SERVER1.mywindowsdomain.com\DEV', local
GO


Should I use Log Shipping, Replication, SSIS or something else I have a BizTalk server Environment running and now the time has come that we've all experienced. People are asking for reports of the data flows and statistics on processing quantities. The data resides in a SQL server database where the tables have minimal indexing and in order not to incur any heavier costs for inserts and updates (which is the primary activity in those tables) and in order for us to customize the data a bit we've decided to have a separate application database to be the base of a web application we're setting up.

However, looking into the possibilities of copying the data from our prod. server to the applications db server gives quite a lot of options.

So, what should I opt for? Log shipping, replication, using SSIS to create some form of scheduled ETL or something else?

The basic requirements are:

Data should be in the application DB within minutes (real time not required)
The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)
The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

I'm a bit at a loss here for a clear path forward, so your input is greatly appreciated.

From all the techniques that you are thinking of, only replication will suit your requirements.

  Data should be in the application DB within minutes (real time not required)

Use transactional replication.

  The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)

You can publish what columns you want. Make sure that @replicate_ddl is set to 0.

  The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

This is where T-Rep shines out. You can choose do not replicate DELETE statements in the statement delivery --> Delete delivery format (same for update delivery format as well). This will require reinitialization.

Be careful when generating snapshot as it will overwrite all the data on subscriber side, though there are ways to carefully do it.


Stop SQL job if it runs past a certain time I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database.  Each of the dynamic queries itself loops because the deletes are happening in smaller batches. (As below)
This job runs overnight but I'd like it to stop if it runs to 7:00am, since that will run into production hours.
I know that I could create a separate additional job that runs "dbo.sp_stop_job" to stop my archive job, but I'd prefer to do it within the archive stored procedure itself.
I'd like for the proc to exit at 7:00am regardless of whether the execution is between tables or if it's between delete batches within each table, but don't really want to have to code the same condition over and over all through it.
I know I might be asking a lot here, but any thoughts on this?
DECLARE CURSOR_DB CURSOR FAST_FORWARD FOR
        SELECT DatabaseName
        FROM #Databases
OPEN CURSOR_DB
FETCH NEXT FROM CURSOR_DB INTO @sourceDB
WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = 'DELETE tbl1 in batches'
    EXEC (@SQL)
    SET @SQL = 'DELETE tbl2 in batches'
    EXEC (@SQL)
    SET @SQL = 'DELETE tbl3 in batches'
    EXEC (@SQL)

    FETCH NEXT FROM CURSOR_DB INTO @sourceDB
END
CLOSE CURSOR_DB
DEALLOCATE CURSOR_DB

Add
IF convert(varchar(11),CURRENT_TIMESTAMP,108) >= '06:56:59' BREAK;

Before fetching the new row.
This will stop the cursor after 06:56:59
The current timestamp will be converted in a 24h string and will be compared wuth the choosen time every day.


Difference between init.ora db_name and query in sqlplus I have a question about the content of the file init.ora, according to oracle documentation.

  DB_NAME specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.

In my understanding the command sqlplus knows to which database connect because it reads the information from the init.ora file.

My init.ora file is as follow

db_name='ORCL'
memory_target=1G
processes = 150

I think is the default one, I would think that the name of my database would appeared in the db_name parameter, then I perform a query in sqlplus to get the name of the database and the result is

My question is why the name is BO1 if in the init.ora is ORCL?
By the way I don't have any other init.ora

Thanks in advance

During startup , Oracle searches for initialization file :

  In the platform-specific default location, Oracle Database locates
  your initialization parameter file by examining filenames in the
  following order:

  spfile$ORACLE_SID.ora
  spfile.ora
  init$ORACLE_SID.ora
  The first two
  filenames represent SPFILEs and the third represents a text
  initialization parameter file.

In your screenshot you have spfileBO1.ora, and it seems your $ORACLE_SID environment variable is set to BO1.  If you want to start the server using init.ora, you should do it manually  :

SQLPLUS command:

STARTUP PFILE = /path/init.ora


What are the advantages and disadvantages of Firebase for a database? I am currently developing a site to track credits gained from exams, and how these stack up against the user's goal credits.

I am looking for a database system that will store all the possible exams someone could take in NCEA (New Zealand's qualification system), and store the user's goals and actual results for exams.

I have considered using Firebase, mySQL and JSON storage with GET and POST requests in order to store and change data.

What are the advantages and disadvantages of using Firebase as a database for this?

You haven't really given us much info about what this data is going to be used for. I mean, you have said what data is going to be stored, but what are you going to do with it?

If your purpose is storing the data then reporting on it, then I think you're looking in the wrong place. A simple MySQL or SQL Database would do just fine and the reporting tools are readily available.

However if your going to be linking to something such as a web or mobile application where the data is constantly changing by multiple users (all accessing the same database stored in the cloud) then Firebase is the way to go.

So, your Pro's and Con's:

Pro's

If your app does run of a centralized DB, and is updated by a lot of
users - then it's more than capable of handling the Real-Time data
updates between devices.
Stored in the cloud so readily available everywhere.
Cross Platform API (If you are using this DB with an App)
They Host the data. -Meaning if you are storing a lot of data, you don't have to worry about hardware!

Con's:

Unless your app runs of one centralized database updated by a vast quantity of users, it's a major overkill.
Storage format is entirely different to that of SQL, (Firebase uses JSON) so you wouldn't be able to migrate that easily.
Reporting tools won't be anywhere near the ones of standard SQL.
Costs! -Limited to 100 Connections and 1GB of Storage! (see this for more details on limitations!)
You don't host the data, Firebase does. And depending on which server you get put on, viewing there up time there seems to be a lot of disruption lately.

Note: I included "Who hosts the data" in both the pro's and con's. That's because you never told us how much data you were storing and who was going to be accessing it.


PostgreSQL EXCLUDE USING error: Data type integer has no default operator class In PostgreSQL 9.2.3 I am trying to create this simplified table:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);

But I get this error:

ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define
       a default operator class for the data type.

The PostgreSQL docs use this example which does not work for me:

CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

Same error message.

And this one, which does not work for me either:

CREATE TABLE zoo (
    cage   INTEGER,
    animal TEXT,
    EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

Same error message.

I am able to create this without any problem:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (startend WITH &&)
);

and this:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING btree (user_id WITH =)
);

I've spent quite a bit of time searching for hints about figuring out how to make this work, or figuring out why it won't work. Any ideas?

Install the additional module btree_gist as is mentioned in the manual at the location you linked to:

  You can use the btree_gist extension to define exclusion constraints
  on plain scalar data types, which can then be combined with range
  exclusions for maximum flexibility. For example, after btree_gist is
  installed, the following constraint will reject overlapping ranges
  only if the meeting room numbers are equal:

In modern PostgreSQL you only need to run (once per database):

CREATE EXTENSION btree_gist;

You need to have the "contrib" package installed in your OS first. Details depend on your OS and the software repository used. For the Debian family it's typically postgresql-contrib-9.2 (for Postgres 9.2). Or just postgresql-contrib for the Red Hat family. Consider this related answer on SO:

Error when creating unaccent extension on PostgreSQL


MySQL 5.6 to 5.7 In-Place Upgrade or Fresh Install? I'm preparing to migrate a production database server on AWS RDS to 5.7 from version 5.6.

I'm not sure that I trust the automatic upgrade service. Obviously, I would make a backup prior to doing anything, but is there any reason one should choose to just start a new instance and manually export / import the data (and privileges, of course)?

For such major version change like 5.5 to 5.6 or 5.6 to 5.7 I recommend to export/import rather than in-place.

Why do I prefer fresh-install rather than in-place? :

In MySQL 5.6 you have 29 tables under mysql schema and in 5.7 you have 33 tables under mysql schema.
If you are using GTID then it is not recommended to go for in-place upgrade.
Deprecated commands or data types.
Deprecated UDFs or changed functions.
Chances of password incompatibility.
Deprecated stuff will be thrown out as an error during import, thus gives us alarm. Though we don't go through white paper properly.

How do I proceed? :

Let's say in your case 5.6 to 5.7, on your test bed:

Take mysqldump or mydumper with master status. A consistent backup.
Take all grants out separately into an sql file.
Have fresh installation of 5.7.
Without performance_schema, mysql, information_schema or any system schema, import the dumped data into 5.7 and apply grants. Ensure you verify root and admin users are removed as it is a different server.
Now create slave link and go for down time on 5.6.
Now your test architecture will look like:

APP- > 5.6 SERVERA (Master of) -> 5.7 SERVERB (Master of)  -> 5.7 SERVERC (Slave of SERVERB)

And now do a consistency check of ServerA, ServerB, ServerC by considering frequently updating tables and take the data in chunk sets and verify if the data checksum is same as per its right state of changes.

But before everything we should ask ourselves what is the reason behind to go for different versions. Of course we can make use of new features. But if we verify conflicts in test bed at least we have a ground confidence to do the same to production.


Form isn't reading table records I am really new at building databases. I created a table that reads from other tables. However, I needed to add another table and I placed the input field into the form.

My problem is that the form isn't reading any of the records already on the tables. How did this change and how do I fix it?

Now every time I enter a new record this message comes up.

  The Microsoft database engine can not find a record in the table "  " with key matching fields "  "

This is a common error. Either you screwed up the 1:M relationship or you are trying to enter data into multiple tables incorrectly

From Microsoft's Developer Network MSDN :In a one-to-many relationship, you entered data on the "many" side for which there is no matching record on the "one" side. For example, this error occurs if you join a Customers table and Orders table on a CustomerID field, and then add an order using a CustomerID that does not exist in the Customers table.
From Microsoft Answers: It sounds like you are trying to enter data into multiple tables using one main form. That's not going to work. You NEED to be using subforms. Create your main form bound to the Members table and then subforms to each of the child tables. Use the Form wizard to create your forms.

My guess from your comments is #2. You added a new table but didn't recreate the form using subforms.


MySQL database sync options What are some of the good options to sync two or more MySQL databases of the same schema, situated at different locations; reliably?

We have four database in four different locations.

we tried using the generic master master replication but the link breaks again and again leading to inconsistencies.

There are many things that can go wrong in Master-Master.  Use Master-Master, but do not write to both masters.  Pick one for all writes; leave the other one for failover.


Open files in MySQL increasing over time, eventually causing it to stop accepting connections Every few seconds MySQL is opening a file and not closing it, which we can see in real time by querying

SHOW GLOBAL STATUS WHERE variable_name = 'Open_files';

Looking at the open file descriptors using

sudo ls -la /proc/<pid>/fd | less

shows that these open files are mostly deleted temporary tables:

lrwx------ 1 mysql mysql 64 Apr 17 08:56 990 -> /tmp/mysql_temptable.xTHQV4 (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 991 -> /tmp/mysql_temptable.gr1swq (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 992 -> /tmp/mysql_temptable.sXackV (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 993 -> /tmp/mysql_temptable.Tom8Pa (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 994 -> /tmp/mysql_temptable.OqNhMl (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 995 -> /tmp/mysql_temptable.VOlk8X (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 996 -> /tmp/mysql_temptable.ti1nry (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 997 -> /tmp/mysql_temptable.EeXTiS (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 998 -> /tmp/mysql_temptable.r2GHks (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 999 -> /tmp/mysql_temptable.NDCeta (deleted)

This is similar to the issue reported at MySQL crashing at about 70k open files, although we get a different error when MySQL runs out of file descriptors. There it's mentioned that this is fixed in 8.0.16, but who knows when that version will release. Meanwhile, we're having serious issues...

Is there any way we can find out what queries are causing these temporary tables to be created? Or is there a way to mitigate this problem in some other way?

We've followed the advice at What value of thread_cache_size should I use? and increased thread_cache_size, which was one of the suggested changes at MySQL crashing at about 70k open files as well. This seems to have stopped Open_files from increasing as it was before (it was increasing by 1 every few seconds).

I believe this makes sense: if the bug is that threads aren't correctly releasing certain files, then creating less threads (reusing more cached threads) could result in less files being left open. This is of course just guesswork of mine.


performance impact of using synonyms I have 2 databases that reside on the same server. I am trying to read static data from one database in another database using synonym. And this caused no issues since we are not even using link server or anything. But DBA seems to think this will cause huge performance issue since synonyms use tempdb to create object reference to the other database. I am not sure how this works? Can someone explain if there is really a serious impact and if really tempdb is used? I thought synonym just creates an object that refers to the database object directly with no tempdb involved since they are both on same server.

Synonyms will not cause the use of tempdb. There, however, may be a misunderstanding by your DBA as to the location of the database behind the synonym.

Queries across databases can use a 3 part name to reference a database object: database.schema.objectname.  Queries across linked servers require an additional part, the linked server name.  In my experience, because of this, almost all references to external servers use a synonym. It saves a bunch of typing (note: that is not the reason to use synonyms). However, it is a lot more common to see not use a synonym where referencing a local database.

I speculate this may have lead the DBA to assume that, because you are using a synonym, you are referencing an external server.

Results of queries against an external server do impact tempdb, as this is where it stores the data. So, if the DBA misunderstood the actual relation of the two database, being on the same server, they could have given you incorrect information.


Mysql causing high load average with large amount of server resources left I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot of resources (RAM) left on the server. The load average is about 3~4 and the server is increasing it's response time. Here is the output of mysqltuner.pl:

[--] Up for: 9h 10m 55s (42M q [1K qps], 4M conn, TX: 19B, RX: 6B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 10.2G global + 6.6M per thread (800 max threads)
[OK] Maximum possible memory usage: 15.4G (49% of installed RAM)
[OK] Slow queries: 0% (5K/42M)
[OK] Highest usage of available connections: 36% (294/800)
[OK] Key buffer size / total MyISAM indexes: 100.0M/34.8M
[!!] Key buffer hit rate: 73.3% (45K cached / 12K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 3M sorts)
[OK] Temporary tables created on disk: 0% (54 on disk / 52K total)
[OK] Thread cache hit rate: 99% (478 created / 4M connections)
[!!] Table cache hit rate: 0% (400 open / 78K opened)
[OK] Open file limit used: 0% (3/4K)
[OK] Table locks acquired immediately: 100% (37M immediate / 37M locks)
[OK] InnoDB buffer pool / data size: 10.0G/1.0G
[OK] InnoDB log waits: 0

Server Configuration (note: this server runs ONLY the mysql server):

Intel Xeon E5-2620v3 (6 cores - 12 Threads)
2x 16GB DDR4-2133 ECC (Total: 32GB)
2x 256 GB SSD 2.5" (RAID1)

Based on this Server Configuration, i'm wondering if my my.cnf is with the best options and i want know if there is any sugestions to update this based on the Server Configuation and mysqltuner.pl output. Here's my my.cnf:

skip-external-locking
skip-name-resolve
lower_case_table_names          = 1

wait_timeout                    = 4
interactive_timeout             = 15

key_buffer_size                 = 100M
join_buffer_size                = 4M

max_allowed_packet              = 1M
thread_stack                    = 256K
thread_cache_size               = 250

myisam-recover                  = BACKUP
max_connections                 = 800

#Qcache not enabled due to high tax of inserts/updates
query_cache_limit               = 1M
query_cache_type                = 0
query_cache_size                = 0

log_error = /var/log/mysql/error.log

log_slow_queries                = /var/log/mysql/mysql-slow.log
long_query_time                 = 1

expire_logs_days                = 10
max_binlog_size                 = 100M

join_buffer_size                = 4M

tmp_table_size                  = 30M
max_heap_table_size             = 30M

#InnoDB config's
innodb_commit_concurrency       = 0
innodb_io_capacity              = 90000
innodb                          = ON
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_doublewrite              = 1
innodb_additional_mem_pool_size = 64M
innodb_thread_concurrency       = 12
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 24M
innodb_read_io_threads          = 12
innodb_buffer_pool_size         = 10G
innodb_write_io_threads         = 12
innodb_log_files_in_group       = 2

Note: I have hide some unuseful parameters of my my.cnf.

UPDATE

I'm using Mysql Server 5.5 and i've now increase my table_open_cache (it has it's default value of 64 in the moment of the post). Now, mysql tune shows the following line when talk about table cache:

[OK] Table cache hit rate: 99% (1K open / 1K opened)

About the ENGINE point, i have 87 tables and only 11 of them are MyISAM (12% of the entire database + those tables are "Select/Insert" tables), the other ones are all InnoDB (with a hight tax of select/insert/update). I don't have ANY DELETE clause on my system, instead i set a flag to a row saying that it was deleted (so i can delete it in a non-busy time). I've a lot of queries (especially queries like Select count(*)) tooking more than 1.5, 2 seconds to execute.

About innodb_io_capacity, the server disks are all SSD that has 100.000IOPS.

Are you using MyISAM?  Or InnoDB?  Or both?

  [!!] Query cache is disabled

This is "good", not "bad".

  [!!] Table cache hit rate: 0% (400 open / 78K opened)

How many tables to you have?  Thousands?  Please elaborate on why so many.  (This is usually a design flaw.)  Meanwhile, increase table_open_cache to, say, 2000.  What MySQL version are you running?  Set table_open_cache_instances = 8 if it is a new enough version.

  [OK] Highest usage of available connections: 36% (294/800)

Yeah that is "OK" in one sense.  But in another sense, 294 simultaneous connections is a lot.  Please elaborate.  Perhaps they are failing to disconnect?

  [--] Up for: 9h
  [OK] Slow queries: 0% (5K/42M)
  load average is about 3~4
  long_query_time                 = 1

Those sort of contradict each other.  "Load average", in my opinion, is not very useful.  Do you have a measure of CPU utilization?  Still, if CPU is high, why aren't there more queries taking more than 1 second?  Anyway, please run pt-query-digest and let's see the first couple of queries it finds.  We may be able to speed them up; that may speed up the system.

Update

Thanks for the update.

SELECT COUNT(*) on InnoDB tables is proportional to the size of the table, so it will get slower and slower.  The slowlog will point out which of them are run often enough to be a nuisance.

Between the SSDs and sufficient caching (key_buffer, buffer_pool, table_open_cache) plus innodb_flush_log_at_trx_commit  = 2, you have probably minimized the I/O.  Now to focus on slowlog.


LEFT JOIN based on content of a field in matched rows Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields.

Specifically, I have a users table and a contact table. The contact table ties a user to a piece of contact information (such as phone, email, or a myriad others). But I only want to pull in rows from the contact table that match a specific contact type (i.e. only pull phone records), but I still want to pull all of the records from the users table regardless of whether they have a phone record or not. This is in MariaDB.

Users table (simplified):

mysql> select * from users;
+----+-----------+----------+---------------------------------+
| id | firstname | lastname | title                           |
+----+-----------+----------+---------------------------------+
|  1 | Chris     | Topher   | Ordinary Citizen Extraordinaire |
|  2 | Howard    | Roark    | Solutions Architect             |
|  3 | John      | Snow     | Knows Nothing                   |
+----+-----------+----------+---------------------------------+

Contact table:

mysql> select * from contact;
+----+--------+-------------+-------------------+
| id | userid | contacttype | contactdetails    |
+----+--------+-------------+-------------------+
|  1 |      1 | email       | one@two.Three     |
|  2 |      1 | phone       | 1-800-OHNOES1     |
|  3 |      2 | email       | hroark@amazon.com |
+----+--------+-------------+-------------------+

What I'm trying to select is something like this:

    +-----------+----------+---------------------------------+-------------------+
    | firstname | lastname | title                           | contactdetails    |
    +-----------+----------+---------------------------------+-------------------+
    | Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
    | Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
    | John      | Snow     | Knows Nothing                   | NULL              |
    +-----------+----------+---------------------------------+-------------------+

But my attempts with a left join result in too many or too few records:

mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left join contact c on u.id=c.userid;
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
| Chris     | Topher   | Ordinary Citizen Extraordinaire | 1-800-OHNOES1     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
| John      | Snow     | Knows Nothing                   | NULL              |
+-----------+----------+---------------------------------+-------------------+
4 rows in set (0.00 sec)

mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left
join contact c on u.id=c.userid WHERE c.contacttype='email';
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
+-----------+----------+---------------------------------+-------------------+

Is this possible in a single query? I'm mostly only familiar with the simple syntax of SQL, but I've tried EXCEPT clauses and subqueries to no avail. Redesigning the schema isn't out of the question, but it does have to account for the fact that users will have multiple forms of contact information (most have 4-5 emails/phones) to include contact types that we don't know about yet.

Thoughts?

By moving c.contacttype='email' from WHERE to ON, the LEFT JOIN properties that if it doesn't match a criteria a NULL row appears behaviour occurs:

select u.firstname,u.lastname,u.title,c.contactdetails
from users u
left join contact c
  on u.id=c.userid and c.contacttype='email'


PostgreSQL datasource URL? My first attempt to use PostgreSQL:

I have the elephant icon on the top right: running on port 5432.

I want to use LibreOffice as an interface:

Connect to an existing database: PostgreSQL
Datasource URL: ???

What should I put here? The database will be on the same computer where LibreOffice is running. Tried with localhost:5432 with no success.
By the way, I did not create the database, yet. Can I do it from LibreOffice?

Thank you.

Platform:
  Mac OSX 10.7 - PostgreSQL 9.2.2 - LibreOffice 4.0

From ask.libreoffice, as linked on SuperUser

  With the native postgreSQL connector the connection string is a little
  different. It is not a URL at all, but rather just a text string made
  up of propertyname=value segments, separated by a space.

  The required fields (properties) are, database name and host (or
  hostaddr). If the postgreSQL database is using a non standard port
  (5432) then that must be included also.

  So, if I want to connect to a database, using the default postgres
  configuration, named postgres on my local machine the simplest
  connection string would be: dbname=postgres host=localhost

  Now let's say that there is an older postgres engine running using the
  standard 5432 port and I want to connect to a newer one using port
  5433, and that I want to connect with my user name db_user, with a
  password of libreoffice and I don't want to type that password in each
  time I connect, then the connection string might look like:

dbname=postgres hostaddr=127.0.0.1 port=5433 user=db_user password=libreoffice

  Note that I've used the hostaddr, instead of host here, bypassing any
  need to do a DNS lookup.

  Finally, with the native connector all options in the connection
  string are accepted - you can find a full list of those here:
  PostgreSQL Docs Just remember that it is propertyName=value
  separated by spaces in Base.


Optimise XOR search on int list I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int).

Another large table (TableB) contains the same ID column with some other fields. Currently there is no foreign key constraint or unique index defined on this table (although I'm thinking about adding a primary key constraint here in any case).

The key task is to determine if any of the ID values in TableA do NOT appear in TableB.

SELECT * FROM TableA WHERE Id NOT IN (SELECT Id FROM TableB)

I timed this task on our database server to take approx. 4 minutes. Now I was wondering if there was a way to improve this.

From what I understand the lists may not be ordered BUT it's guaranteed that the Id is Unique for both tables. Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?

Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?
although I'm thinking about adding a primary key constraint here in any case)

Yes.  A Primary Key constraint is implemented by creating a unique BTree index on the target column(s).  A BTree has, at its leaf level, a doubly-linked list of pages that is maintained in index key order.  And all the index key values are stored on these leaf pages.  There are also non-leaf pages that enable efficiently seeking a page containing a particular key value.  You can read all about this here: SQL Server Index Architecture.
So, when comparing two tables on their PKs SQL Server can either

Scan one table and perform cheap lookups on either table (~4 Logical IOs per lookup), using a Nested Loop Join.

or

Perform a Merge Join, where the sorted indexes are scanned in tandem matching rows.

If the PK is Clustered then scanning the just the key values is more expensive, as the rest of the columns are present on the leaf level of the index.  So a non-clustered primary key constraint is probably the best table design.  And a handy complement to a non-clustered PK is to store the rest of the table in a Clustered Columnstore.


Orace tablespace extension operation Could somebody clarify whether tablespace expansion procedure in Oracle 11g database requires any downtime or could impact availability of the database? My understanding that this can be done "on the fly" w/o downtime or restricted-mode (at least when we just allocate free space from physical disk)... but I guess there could be some exclusions caveats to this?

There shouldn't be any impact adding datafiles to an existing tablespace.

Easiest is to let oracle manage the files, make sure you have DB_CREATE_FILE_DEST set:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_BASE%\oradata\';

Now the only thing to do if your existing datafiles are full or hitting the max autoextend limit is:

alter tablespace USERS add datafile;

where USERS is the name of the tablespace.


How to know dependency on a table I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem is all the tables have foreign keys so I want to know the order in which I have to execute the tables.

Is there any way to know it?

You should do it in two passes:

First create the tables:

create table Table1 (..., constraint pk_table1 primary key (...));
create table Table2 (..., constraint pk_table2 primary key (...));
..
create table TableN (..., constraint pk_tableN primary key (...));

Then add the foreign key constraints:

alter table Table1 add constraint fk_table1_tablex
    foreign key (...)
    references TableX (...);
alter table Table2 add constraint fk_table2_tabley
    foreign key (...)
    references TableY (...);
...

This way the there is no order dependency.


Simple date calculation to show old records How to select from v$session records where logon_time is less than 2 days before current date?

SELECT * FROM v$session WHERE logon_time + 2 <= currentDate (???)

WHERE logon_time <= sysdate - 2;

Or perhaps:

WHERE logon_time <= sysdate - interval '2' DAY;


Trigger function does not exist, but I am pretty sure it does I have a procedure:

create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
    update sensor
    set last_read = read_time
    where sensor_id = input_sensor_id;
end;
$$ language plpgsql;

And a trigger that calls it:

create trigger tri_last_read
    after insert or update of report_time
    on report
execute procedure pro_update_last_read(sensor_id, report_time);

However, when creating the trigger, I get the error message:

 [42883] ERROR: function pro_update_last_read() does not exist

Why is this error happening?

You must use a function, not a procedure:

CREATE FUNCTION pro_update_last_read() RETURNS trigger ...

The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function.

The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.


Disabling variable substitution inside SQLCMD script when using :r syntax I am using SQLCMD to deploy a set of scripted Agent jobs. I am running these with a SQLCMD script to calls each script using the :r syntax.

It looks like this:

:ON ERROR EXIT
:r $(scriptpath)\Script1.sql
:r $(scriptpath)\Script2.sql
:r $(scriptpath)\Script3.sql

Is there anyway to disable variable substitution with these scripts (equivalent of the /x switch for SQLCMD)?

I want to run Script1.sql, Script2.sql, etc... without variable substitution.

If you want to do the same thing that you are attempting to do within Management Studio, I think the simplest approach is to write a very simple PowerShell script to run the same thing that goes through each file and then executes the file with SQL CMD.

In my example, I have a directory named e:\test. I have a few .sql files in that directory and I want to execute them and disable variable substitution.  To do that I would use the following script within powershell:

$Path = "e:\test"
$files = Get-ChildItem e:\test\*.sql
ForEach ($file in $files) {
    Invoke-Sqlcmd -ServerInstance localhost -InputFile $file -DisableVariables
}

This obviously takes you out of Management Studio, but it should accomplish the task fairly easily and do so with variable substitution being disabled.


Automatic change of "initial size" of a database? I just had a problem : days ago, on one of my client's databases, the LDF file suddenly grew up to 100% of the disk capacity.

.TRN backups are correctly done every 15min (I don't think reducing this time will help, because this database is rarely used).

I created this database from a backup, two months ago. Everything was OK.

What I didn't understood, is why the "initial size" of the LDF file was... 512GB, which is the size of the disk...
If the initial size had been like that since the first restore, i would have noticed it. So I'm certain that it was not the case.

What I did : backup, simple mode, reducing initial size (it reduced the file size without shrink), full mode, backup.

It's OK now, but i'd like to understand if the "initial size" can change automatically, and if yes, how to prevent it.

Thanks!

There is nothing that allows SQL Server to modify the initial size of a file automatically, by itself.

The file must have been altered by a sysadmin or someone with ownership of the database.

I expect they were setting the max size of the file and set the wrong option.


What is the benefit of marking function as IMMUTABLE? I was looking through some of the plpgsql functions in our codebase and realized that most of them cannot be set IMMUTABLE only because they contain reference to now(), e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;

  FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (now() at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;

      IF (
        extract(isodow from next_time) = ANY(target_dows) AND
        (
          nearest_time IS NULL OR
          nearest_time > next_time
        )
      ) THEN
        nearest_time := next_time;
      END IF;
    END LOOP;

    IF (nearest_time IS NOT NULL) THEN
      RETURN nearest_time;
    END IF;
  END LOOP;

  RETURN next_time;
END
$$
LANGUAGE plpgsql;

However, we can easily rewrite the above function to where now() is passed as a parameter, e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
  seed_time timestamp with time zone,
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;

  FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (find_nearest_time.seed_time at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;

      IF (
        extract(isodow from next_time) = ANY(target_dows) AND
        (
          nearest_time IS NULL OR
          nearest_time > next_time
        )
      ) THEN
        nearest_time := next_time;
      END IF;
    END LOOP;

    IF (nearest_time IS NOT NULL) THEN
      RETURN nearest_time;
    END IF;
  END LOOP;

  RETURN next_time;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

This way the function can be marked as IMMUTABLE.
What is the benefit of marking function as IMMUTABLE in this case?

The PostgreSQL documentation 37.7. Function Volatility Categories states the following on using the IMMUTABLE keyword with functions:

An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever. This
category allows the optimizer to pre-evaluate the function when a
query calls it with constant arguments. For example, a query like
SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ...
WHERE x = 4, because the function underlying the integer addition
operator is marked IMMUTABLE.
For best optimization results, you should label your functions with
the strictest volatility category that is valid for them.

In short, the stricter volatility keyword you can use in your function definitions, the more optimized they'll perform in your queries. I suspect this is especially true when using the IMMUTABLE keyword because of it guaranteeing the function is determinsitic as stated here:

...guaranteed to return the same results given the same arguments
forever.


Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not? I have the following code stored in a script file

create or replace package test_record
is

    type t_test_rec is record(
        name                  varchar2(64)
       ,value                 pls_integer
    );

end test_record;
/

When I execute it from SQL*Plus, the *_IDENTIFIERS data dictionaries are not updated to include the declaration of the type. However, if I copy/paste the same code in SQL Developer and run it as a script (F5), the mentioned data dictionaries are updated.

Even if I force a recompile of the package from SQL*Plus, the *_IDENTIFIERS tables are not updated, no matter what method I use (alter package ..., dbms_utility.compile_schema, dbms_ddl.alter, ...).

How can I force SQL*Plus to behave like SQL Developer in this respect?

SQL Developer automatically sets the below:

alter session set plscope_settings='IDENTIFIERS:ALL';

You can use the same in SQL*Plus.

(The default is IDENTIFIERS:NONE.)


mongodb is using most of disk space for journal and not cleaning itself I am using mongodb on production with journal enabled. I am using mongodb to store application data and also using gridfs to store images. I am using Elastic Block Storage (EBS) with 10GB for each mongodb instances I have (total 3 instances on replica).

When I checked the disk usage then I got surprised that journal folder is using almost all the space. Following is details.

bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ pwd
/home/bitnami/stack/mongodb/data/db
bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ du -h *
65M admin.0
16M admin.ns
65M bhs.0
129M    bhs.1
257M    bhs.2
513M    bhs.3
16M bhs.ns
3.1G    journal
64M local.0
1.1G    local.1
16M local.ns
4.0K    mongod.lock
4.0K    _tmp
bitnami@ip-172-31-25-9:~/stack/mongodb/data/db$ df -h journal
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      9.8G  7.0G  2.4G  76% /

However when I did mongodump on my data then I found that exported data size is only 500MB. I.e. most space is bsing used by journal. I understand journal does keep details on each write operation made which causing large file size. However I am surprised is that its not deleting old transaction.

Ideally I beleive after write transaction is flushed to disk then journal should be deleted. Is this journal folder size expected? Or I am missing some configuration? Should I think to increase the disk volume soon?

Please advice.

MongoDB (for MMAP storage engine) will allocate 3 journal files by default at 1GiB each.  That's where your journal related space usage is coming from, but it will not grow unless you have a very high insert rate.

You can start with the smallfiles option and reduce the size to 3 x 128MiB if you wish, but be aware that your data files will also be reduced (to 512MiB each) so there will be many more of them and they will need to be allocated more often when adding data.

As for whether to increase your storage, that depends on how much data you intend to add to the database - it will need to allocate new data files to store any data you insert, so it really is dependent on your planned usage as to whether 10GiB is enough or not.


Log truncation issue in AlwaysOn AG We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' and took log backup. But we were not able to truncate the log file. The log_reuse_wait_desc status of the database shows AVAILABILITY_REPLICA. We don't see anything in log send and redo queues.

Is there another solution to fix this issue instead of removing the DB from the AG?

Upon troubleshoot suggestion, I have checked everything. There is no blocking in all three secondary nodes. The last_redone_time and redo_queue_size are all in sync with primary replica.

I am not able to suspend and resume the data movement on working days for the time being since it is heavily being utilized. I have checked the log_reuse_wait status of the database, and it is still showing 'AVAILABILITY_REPLICA':

And the loginfo shows that 99% of the log is used.

Most probably this issue due to network latency or long running transaction on primary replica. Thus, in production environment it's always better to have dedicated NIC in each replica that only handles Database Mirroring end-points communication.

However, in your case following steps may help:

Try Suspend data movement and Resume for particular database
Perform LOG backup, check if log_reuse_wait_desc changed

If the AVAILABILITY_REPLICA not changed

Probably one of the replica still got the redo_block which you can identity from effected Replica's Extended Events -> Sessions -> "AlwaysOn_health"

To confirm the blocking is the cause, you can execute following query, look at last_redone_time this would indicate since when the replica got the backlog.

select  db.name, db.database_id,
        ag.name as GroupName,
        state_desc,
        recovery_model_desc, log_reuse_wait_desc,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn

from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4

Query Result  - Replica got more than a hour back-log

By this time, if you look blocking within secondary replica, you may find some active process which causing the block. Most probably that would be read operation trying to block the things to make sure the schema (SCH_M) of the object can’t be changed by DDL activity that been pushed by Primary replica.
Once the blocking process killed, or completed. You can notice that the last_redone_time and redo_queue_size in sync with primary replica. Also, AVAILABILITY_REPLICA should disappeared when you query

select name, log_reuse_wait_desc from sys.databases`

All Databases are in-sync (arrow marked was the one back-logged):

If nothing works, you already got the way to do force fix:

  Remove Back-logged Replica from AG and Re-join, but there is impact on read-only routing of Availability Group


Does schema binding views affect performance? Does schemabinding views affect performance in SQL Server or is just to prevent accidental changes?

With schemabinding can improve performance in some cases.

I couldn't recall all the details, but found an excellent blog post that goes through this in detail:

  With user defined functions that may need to update data. You might
  benefit from improved performance that would prevent an Eager Table
  Spool.
  SQL Server SCHEMABINDING

Edit: Paul White indicated that this doesn't actually impact view table spools just used for joins.

Schema binding definitely adds some additional maintenance when changing schema. This could be considered a good thing, since it prevents underlying schema changes without handling the binding. This doesn't bother me as I'm using Red Gate SQL Compare and it handles this, but if you are not using a tool to generate scripts this could be a factor to consider... (solved by getting a tool to generate the scripts!)

See also Improving query plans with the SCHEMABINDING option on T-SQL UDFs on the Microsoft SQL Programmability & API Development Team Blog.

Additionally, the comment by Kin on your original question brings up a great additional point regarding the potential to index the view once you have schema binding included. This can be considered an additional benefit that might help.


MongoDB delayed member replication mechanism We are considering implementing a delayed member in our MongoDB replicaset to protect us from human error based data corruption, e.g. someone accidentally deletes some data.

How does a delayed member actually delay replication, as I see it there are two possibilities:

the delayed member observes the primary's oplog with a filter of ts < now - delay
the delayed member observes the primary's oplog in real time, stores the oplog locally and replays each action as ts >= now - delay

the reason this is vital for us is we're looking at a relatively large replication delay (24 hours). In normal operations our oplog maintains about 5 days of data, so whether (1) or (2) above is used in replication, we're fine. However, at certain times, our write workload is extremely high, in that situation our oplog will occasionally only contain < 1hr of data. In this situation (1) above will not work for us.

The implementation is as per your first suggestion: a delayed secondary applies operations from the source oplog based on the delay. Your concern is also valid: if the source oplog period is insufficient to cover the secondary delay, the delayed secondary will become stale and require a re-sync.

A delayed secondary will help you with some data recovery scenarios (for example, a collection drop that hasn't been applied yet), but for more comprehensive recovery options I would consider a continuous backup solution such as MongoDB Cloud Manager (or MongoDB Ops Manager, which is the on-premise equivalent). Cloud Manager Backup provides continuous backups with queryable snapshots that allow you to preview and restore a subset of data. Base snapshots are taken every 6 hours by default, with a customisable snapshot interval and retention policy.


MySQL: "FROM_UNIXTIME(0)" gives "1970-01-01 01:00:00", which is 1 hour off Context: MySQL version

On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";)

Context: MySQL timezone settings

and with SELECT @@global.time_zone, @@session.time_zone; yielding

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

...which means the server tracks the system timezone.

Context: System timezone

This being a Linux, we check the system timezone like this (output redacted):

$ timedatectl
      Local time: Wed 2018-02-14 13:21:30 GMT
  Universal time: Wed 2018-02-14 13:21:30 UTC
        RTC time: Wed 2018-02-14 13:21:30
       Time zone: Europe/London (GMT, +0000)

Context: What we know

Time and date values are not stored with timezone information in MySQL (there is no clear description of this in The DATE, DATETIME, and TIMESTAMP Types, except "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)"; note that "converts TIMESTAMP values to UTC for storage" actually makes sense only as an implementor's note, it's not useful to the user - what is important is that TiMEZONE always returns the correct value in whatever timezone one is in, while DATETIME will always return the same value in whatever timezone one is in)

Context: Current time

It's now around :

$ date +"The current time is %s alias %c"
The current time is 1518617572 alias Wed 14 Feb 2018 14:12:52 GMT

Problem

Let's run:

SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());

The output (suitably pivoted by hand; there should be an option to do that):

NOW()                            2018-02-14 14:15:13  -- Display "now" in SYSTEM timetone: OK
FROM_UNIXTIME(0)                 1970-01-01 01:00:00  -- Display 1970-01-01 00:00:00 in SYSTEM timetone: NOT OK
UNIX_TIMESTAMP()                 1518617713           -- Correct unixtime
FROM_UNIXTIME(UNIX_TIMESTAMP())  2018-02-14 14:15:13  -- Same as NOW(): OK

In the above everything is fine, except the FROM_UNIXTIME(0) giving 1970-01-01 01:00:00, which is one hour off. If Unix Time 0 were displayed in UTC, it would give 1970-01-01 00:00:00 (or would it?)

This happens if I connect locally or from a remote machine.

To make doubly sure, I checked by coercing to DATETIME. The same happens:

CREATE TEMPORARY TABLE test (N DATETIME, FUX DATETIME, UXT LONG, NN DATETIME);
INSERT INTO test SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT * FROM test;
+---------------------+---------------------+------------+---------------------+
| N                   | FUX                 | UXT        | NN                  |
+---------------------+---------------------+------------+---------------------+
| 2018-02-14 14:19:51 | 1970-01-01 01:00:00 | 1518617991 | 2018-02-14 14:19:51 |
+---------------------+---------------------+------------+---------------------+

At time 0, "Time zone: Europe/London" was DST (or not DST?); you are currently not in DST (or you are?).  That is probably the 1-hour discrepancy.

Europe/London is not the same as UTC.


What is preventing DROP TABLE from removing a Postgres table? I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command:

DROP TABLE pnt

Unfortunately, this does not appear to drop the table. My best guess is that there is some sort of admin privileges that I have not configured properly. The following shows the database, and commands used to try and drop the table.

What is preventing me from dropping the pnt table?

There is a similar question here, although the user is running into a cache error, which is not happening in my case.

Community Wiki answer, originally a comment on the question by a_horse_with_no_name

You need to end every SQL statement with ;. You didn't do that and thus your statement was not executed at all. Run DROP TABLE pnt; instead. See e.g. here: Understanding postgres prompts


Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters? Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation?

For example:

Create Table #Test
(
Value   VarChar(10)
)
Insert Into #Test
Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789')

Select      *
From        #Test
Where       Value like '[0-9][0-9][0-9][a-z]'

This would return values where the first 3 characters are numbers between 0 and 9 and the last character will be a letter between a and z, so would return things like 123a and 456b but wouldn't return a value of 12ABC.

I want to know if there is an equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters so that it would return AB!23 and C?D789?

If I could use a regular expression I might use the expression ^[a-zA-Z0-9]*$ to match alphanumeric characters in a string.

Where       Value like '^[a-zA-Z0-9]*$'

Is there a SQL equivalent for this?

I know this kind of thing that can be done in RegEx but I need it in T-SQL, I am not able to load any custom assemblies onto this server so can't use regular expressions.

The real column is varchar(200). The collation is Latin1_General_CI_AS. I am using SQL Server 2012 Standard Edition.

The biggest difficulty in coming to a precise solution is in defining exactly what characters are to be included (or excluded, whichever direction makes more sense for the operation). Meaning:

Are we talking about VARCHAR / ASCII data or NVARCHAR / Unicode data? The list of punctuation characters for ASCII data depends on the Code Page which in turn depends on the Collation. (in this Question we are dealing with ASCII data).
Are we dealing with case-sensitive or case-insensitive searches?
What Collation is the column set to? The Collation will tell us both the Code Page and case-sensitivity. (in this question we are dealing with Latin1_General_CI_AS)
is the term "punctuation" to mean just standard punctuation characters (e.g. ., ,, ;, :, etc) or does it mean non-alphanumeric characters?
Are whitespace characters included?
Are Control characters included?
What about currency symbols such as ¢, £, ¥, etc?
What about symbols such as © and ™?
What characters are considered "alpha"? Are non-English characters such as Â, É, Ñ, ß, Þ included?
Since this Question deals with UK keyboards (see discussion for this Question), what about the Æ / æ character?

In order to help facilitate clarity regarding expected behavior, the following query will show all 256 characters of the Latin1 character set (i.e. Code Page 1252) and how two variations of @Shaneis's proposed solution operate. The first field (labeled as Latin1_General_CI_AS) shows the LIKE clause as proposed by @Shaneis (as of this writing) and the second field (labeled as Latin1_General_100_BIN2) shows a modification where I overrode the Collation to specify a binary one (i.e. a Collation ending in _BIN2; the _BIN Collations are deprecated so don't use them if you have access to the _BIN2 versions) which meant I also needed to add in the A-Z range to filter out upper-case letters as the current Collation is case-insensitive:

;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS [Decimal]
  FROM   [master].[sys].[all_objects]
)
SELECT nm.[Decimal],
       CHAR(nm.[Decimal]) AS [Character],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9]%'
               THEN 'x' ELSE '' END AS [Latin1_General_CI_AS],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9A-Z]%' COLLATE Latin1_General_100_BIN2
               THEN 'x' ELSE '' END AS [Latin1_General_100_BIN2]
FROM   nums nm;

UPDATE

It should be mentioned that IF one is truly seeking to find characters that are classified as being "punctuation" (and not "currency symbol", "mathematical symbol", etc), and IF one is not prohibited from using SQLCLR / loading a custom Assembly (SQLCLR was introduced with SQL Server 2005, and I have yet to come across a good reason for not allowing it, especially since Azure SQL Database V12 supports SAFE Assemblies), then you can use Regular Expressions, but not for the reason that most people would guess.

Rather than using Regular Expressions to build a more functional character range, or even rather than using something like \w (meaning any "word" character), you can specify the Unicode Category of the characters you want to filter on, and there are several defined categories:

https://www.regular-expressions.info/unicode.html#category

You can even specify the Unicode Block to filter on, such as "InBengali" or "InDingbats" or "InOptical_Character_Recognition", etc:

https://www.regular-expressions.info/unicode.html#block

There are numerous examples of creating RegEx functions for SQL Server (though most examples don't follow SQLCLR best practices), or you can download the Free version of the SQL# library (which I created), and use the scalar RegEx_IsMatch function as follows:

SQL#.RegEx_IsMatch(Unicode-String-Expression, N'\p{P}', 1, NULL)

The \p{P} expression means \p = Unicode Category, and {P} = all punctuation (as opposed to a specific type of punctuation, such as "Connector Punctuation"). AND, the "Punctuation" category includes all punctuation across all languages! You can see the full list on the Unicode.org site via the following link (there are currently 717 Code Points in that category):

http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AGeneral_Category%3DPunctuation%3A%5D

An updated version of the test query shown above, including another field that uses SQL#.RegEx_IsMatch with \p{P}, and the results of all 3 tests across all 256 characters of Code Page 1252 (i.e. Latin1_General) has been posted on PasteBin.com at:

T-SQL query and results for filtering types of characters

UPDATE
The following was mentioned in the related discussion:

  You've made a good point about accented characters, with them being hotel names from around the world there will be accented characters in the names, for my problem I would want to class these as valid alpha characters.

In this case:

There are 11 non-English characters that are included in the Latin1 character set / Code Page that are not matched by the a-z range. They are: ð Ð  Þ þ  œ Œ  š Š  ž Ž  Ÿ. These need to be added to the wildcard, and while not necessary at the moment, it wouldn't hurt to add in A-Z so that the pattern works just as well on a case-sensitive collation. The end result is:
LIKE '%[^a-zA-Z0-9ðÐÞþœŒšŠžŽŸ]%'
Considering that this data can include "hotel names from around the world", I would highly recommend changing the datatype of the column to be NVARCHAR so that you can store all characters from all languages. Keeping this as VARCHAR runs a very high risk of eventually having data loss since you can only represent the Latin-based languages, and not even fully for those given the six supplemental Unicode categories that provide additional Latin-related characters.


Does using Activity Monitor have an effect on server performance? Our SQL Server VLDB is being monitored by Activity Monitor on a networked machine.
95% of all traced statements can be attributed to this tool, but is it known to have a negative effect on server/network?

YES. Beware of the "observer effect."

This article is about you using trace and extended events directly, but Activity Monitor uses similar technology under the covers - it doesn't have any magical, no-cost ways of collecting performance information.

There are several monitoring tools out there that do a better job at this than Activity Monitor, some more so than others. There are also free scripts like sp_whoisactive and sp_BlitzFirst that are more honest with you about what's really going on on your servers (see this article). I strongly suggest you try some of those out and don't open Activity Monitor or Profiler at all.


This solution gives you the desired output.

DECLARE @TempTable TABLE
    (
      Prime CHAR(2) NOT NULL
    , Percentage INT NOT NULL
    );

INSERT  INTO @TempTable
        ( Prime, Percentage )
VALUES  ( 'a1', 3 )
    ,   ( 'a1', 0 )
    ,   ( 'b2', 25 )
    ,   ( 'b3', 42 )
    ,   ( 'c4', 23 )
    ,   ( 'c4', 0 );

WITH CTE_NonZero
    AS ( SELECT   T.Prime
                , MAX(T.Percentage) AS PercentageToUse
        FROM     @TempTable T
        WHERE T.Percentage <> 0
        GROUP BY T.Prime
        )
    UPDATE  @TempTable
    SET     Percentage = P.PercentageToUse
    FROM    @TempTable T
            INNER JOIN CTE_NonZero P ON P.Prime = T.Prime
    WHERE   T.Percentage = 0;

SELECT * FROM    @TempTable;

Alternatively, you could make the CTE updatable and thus avoid the join:

WITH MaxPercentages
    AS ( SELECT   T.Percentage
                , MAX(T.Percentage) OVER (PARTITION BY T.Prime) AS PercentageToUse
        FROM     @TempTable T
        )
    UPDATE  MaxPercentages
    SET     Percentage = PercentageToUse
    WHERE   Percentage = 0 AND PercentageToUse <> 0;


Postgres CREATE DATABASE already has data and tables When I do a: DROP DATABASE mydb; and subsequently: CREATE DATABASE mydb WITH OWNER user; the new mydb database is created with all the tables and data that I had in the database I just dropped. What am I be missing?

You probably created some tables (with data) in the database template1.

Connect to that database (e.g.: psql postgres template1) and drop everything you don't need there.

More details in the manual: http://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html


convert nvarchar to int I have a table in access linked to SQL.I am doing a query where I am using a field named comment from this table.
The field is designed in SQL as nvarchar(255) . I am writing integers numbers in this field. What I want to do in my query is to convert this field from nvarchar to int so I can use the SUM function.Can anyone help?

Here are some options.

CBool—Converts a value to a Boolean data type.
CDate—Converts a value to a Date data type.
CInt—Converts a value to an Integer data type.
CStr—Converts a value to a String data type.
CVar—Converts a value to a Variant data type.

Syntax

CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)

Converting to an Integer Data Type

The CInt function takes a numeric or string value and converts it to an Integer data type. The argument is required and needs to represent a value within the range of -32,678 to 32,767. If the argument contains a decimal, Access rounds to the next whole number. A value of .5 or higher is rounded up; anything lower is rounded down. Some examples of CInt functions follow:

Dim MyDouble, MyInt
MyDouble = 2345.5678    ' MyDouble is a Double.
MyInt = CInt(MyDouble)    ' MyInt contains 2346.

IN SQL, you could use CAST.

SELECT
   CAST([Column] AS INT) AS [Column Name]
FROM [Table]
WHERE ...

Or, if you wanted to SUM off the SQL query.

SUM(CAST([Column] AS INT)) AS [Column Name]


Multiple availability groups--can each availability group have its own listener? I have created two availability groups containing multiple databases on the same SQL Server instance.  Can I have two different listeners for these two availability groups?

Yes, you can and should create one listener per Availability Group.  This is mentioned in passing in the MS Docs article Configure a listener for an Always On availability group:

  You can create only one listener per availability group through SQL Server. Typically, each availability group requires only one listener.


Datawarehouse indexing I have a table with nearly 6m records.  The business key that identifies a unique row is quite large.  Our update processing is now taking a lot longer since I added this new table for a new cube.  I currently do not have an index on the join columns for the update.  SQL Server estimated execution plan says I should create this index on the business key:

/*
Missing Index Details from Server.db
The Query Processor estimates that implementing the following index
could improve the query cost by 86.9178%.
*/

/*
USE [db]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [schema].[Production]
(
    [ProdId],[PriceCalc],[CalcTypeId],[OprId],[CostGroupId],[Resource],
    [BOM],[ResourceDepartment],[OprNum],[DateWIP],[DataAreaId],[Partition]
)
INCLUDE
(
    [ProdOrderStatus],[ManufacturedItemId],[CalcType],[CalculationLevel],
    [CostAnalysisOrderType],[CostGrouping],[UnitId],[WorkCenter],[Name],
    [RealisedConsumption],[RealisedCostAmount],[RealisedCostAdjustment],
    [EstimatedConsumption],[EstimatedCostAmount],[LotSizeVariance],
    [StandardQty],[StandardCost],[ItemStandardQty],[HasSubstitutionVariance],
    [R2],[R3],[StandardQtyByRAFQty],[StandardCostByRAFQty],[ProductionOrderType],
    [RealisedAllocation],[CostVariance],[QuantityVariance],[SubstitutionVariance],
    [TotalVariance],[ComponentItemId],[InventoryUOM],[InventConsumptionTransUOM],
    [BomConsumptionTranUOM],[TransactionUOM],[TransUOMToInvUOMConversionRate],
    [InventConsumptionInvUOM],[BomConsumptionInvUOM],[TotalNetWeightPerUnitInvUOM],
    [InventoryNetWeightUOM],[ReportingNetWeightUOM],
    [NetWeight_InvUOMToReportingUOMConversionRate],
    [InventConsumptionTotalNetWeightInvUOM],[BomConsumptionTotalNetWeightInvUOM],
    [InventConsumptionTotalNetWeightReportingUOM],
    [BomConsumptionTotalNetWeightReportingUOM],
    [FinancialProductId],[FinancialDepartmentId],[FinancialMarketId],[FinancialCodeId],
    [FinancialTypeId],[FinancialSiteId],[ProdPoolId],[Company_SK],[ComponentItem_SK],
    [EndedDate_SK],[DateWIP_SK],[FinancialCode_SK],[FinancialDepartment_SK],
    [FinancialMarket_SK],[FinancialProduct_SK],[FinancialSite_SK],[FinancialType_SK],
    [InventoryNetWeightUOM_SK],[InventoryUOM_SK],[ManufacturedItem_SK],
    [ProductionOrder_SK],
    [ReportingNetWeightUOM_SK],[TransactionUOM_SK],[BatchRunId],[ValidInd],[ScrapVar],
    [QuantityPO],[ExpectedConsumption],[FibreScrapFactor],[QtyAndSubVariance],
    [EndedDate],[RealisedAllocationCost],[FilmScrapFactor])
GO
*/

it wants to create an index on the key columns, however it wants to include many columns.  Should i listen to it or just include the key columns? Thanks for your help. This is a production issue so I can't go testing different things etc.

We are considering putting in a hashing solution, along these lines:

Using Hashbytes to track and store historical changes for SQL Server data

In the short term, next week is an important financial end period and the system needs to perform well so adding an index seems like a good idea.  So I would like to do whatever I can quickly. Would you recommend that hashing solution for long term/new implementations?

I'm working on the assumption that the table in question is a fact table, not a dimension table with a huge composite key:

Just to fix the performance issue in the short term, I would add all of these key columns as the table's clustered index, which means you won't have to INCLUDE a lot of measures and stuff, like the suggested index does. Also, make the index unique if the data allows for this.

As for the column order of a clustered index on a fact table, it depends on how you're accessing them. If you're only using a cube to read large chunks of data, I would probably prioritize INSERT priority by making the index chronological, i.e. putting the date column first - that way, new rows get added to the end of the index (in the best of worlds).

If you're running user T-SQL queries on the fact table, I would try to arrange the index columns in an order that gives you Index Seeks or Range Scans as much as possible: first, columns that are filtered on single dimension keys (think "year", "type", "unit" or "department"-type dimensions), then those columns that are filtered on multiple dimension members, ranges, or used for sorting.

There are, of course, other schools on how to build indexes - this is not a "single correct answer".

Edit: More on clustered vs non-clustered indexes:

I'm guessing that you already have an existing clustered index, and that's why SQL Server suggests a non-clustered index. However, non-clustered indexes have to be explicitly defined with INCLUDE columns. Clustered indexes define the actual storage/sort order of the table, and as such, they will implicitly include all columns in the table (I won't go into LOB columns like varchar(max) and xml).

The clustered index is normally the "catch-all index" that takes care of queries that are not suitable for an existing non-clustered index, which makes it even the more important (in my opinion) that it's well-designed and not, for instance, just on an IDENTITY() column.

Plus, a non-clustered index will take up more drive space, so a non-clustered index that covers all of the table's columns will in effect take up as much space as the table itself. A clustered index is the table.


SQL Server Slowdown? We're a small shop, and I'm certainly not a DBA by any stretch of the imagination, but I've recently inherited a somewhat large older database (been running since at least 04), that in the past month has experienced a slow down. It started with our more complex queries, but has even started to affect the simpler ones.

Prior to my coworker leaving, he had upgraded the instance from 2008 (Standard) to 2014 (Enterprise). The files were also shrunk on the DB, (which I now know is frowned upon.) However, I believe the instance ran fine directly after the upgrade and the shrink.

We have two dedicated SQL servers, set up with AlwaysOn. The servers are about 5 years old, but they're still good servers (64GB memory, Intel Xeon). It doesn't matter which is primary, both experience the slow down. The servers have a few other instances, but the one I'm trying to troubleshoot is by far the biggest one. A few of these instances are Dev and Staging, both of which are about a month old. A query that I've been running will take two minutes on either of these, but 20-40 minutes on live. I've tried copying live to a new instance, but it too is slow.

I've tried restarting the instance, updating statistics, refreshing views (which we aren't really using), rebuilding indexes, but nothing seemed to help. Any ideas?

Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements.

Wrong memory configuration

As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of the things which may be harmful is a restart or anything like that (e.g. Failover). Maybe your instance has reset their memory limits. This may explain your drastically slowdown. You can check if they still up with your documented max memories. You can get them running the following the command below:

EXEC sp_configure N'max server memory (MB)'

If you just want to see this as a quickshot. You can try to query the dm_os_sys_memory which has a nice column system_memory_state_desc which isn't very detailed but it says you the needed things too. It will display if your memory is low, which means there is a constant memory pressure on the system.

SELECT system_memory_state_desc
FROM sys.dm_os_sys_memory

Statistics are out of date

You might want to update your statistics for your database tables, as they may be too old.

EXEC sp_updatestats

In a situation like yours, it is wise to throw away your cache plan, as it might be useless due to the old statistics. If sp_updatestats shows many tables and many statistics which are updated, it may be a good idea to throw it away and let the SQL Server recreate it.

You can achieve this using this DBCC command:

DBCC FREEPROCCACHE

Fragmented indices

It might be, that your indices are too fragmented which cause a huge load on your I/O subsystem. Before you blindly execute the statement, you should check if your machine has a higher load on your I/O subsystem. This may be easily done by using perfmon for example. The READ UNCOMMITTED should be used as some indices may be written at the moment. You can try to run the following statement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Get Index Fragmentation
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
    , ind.name AS IndexName, indexstats.index_type_desc AS IndexType
    , indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
        ON ind.object_id = indexstats.object_id
        AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 25
ORDER BY indexstats.avg_fragmentation_in_percent DESC
GO

  Beware, it might run very long and on your system which is already under pressure it might kill it. Only run it, if your system may to be affected by it.

Each row which will be returned is an index which is fragmented and may need an REBUILD or REORGANIZE. This may improve your overall system performance too, but during the rebuild itself, it will slow down!

ARITHABORT problem

It may be an problem with ARITHABORT which tends to make problems on older databases (for some users). Maybe you try to enable/disable it.

Many other causes...

As mentioned in the first sentences, it's pretty hard to see where your problem is. It may be another error - maybe even a hardware fault. I've had a error on a switch to my SAN which killed my performance too for some years (before it was redundant).

But if nothing of the above tends to help, you can still provide the output of your dm_os_wait_stats which may help me to get the right view on your system.

SELECT TOP 30 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:

-- Show all active processes with detailed informations
SELECT pr.spid, pr.loginame, pr.status, pr.blocked, pr.waittime, pr.lastwaittype,pr.cpu, pr.physical_io, pr.memusage, pr.last_batch, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.status <> 'sleeping'
    and pr.spid >= 50

Additionally it can be a process which produces locks or consume more than it should. But I think this isn't the best possibility. But anyway, you can take a look in your running processes. Maybe you see something which isn't correct.


How is a join performed by a database engine? How is a join between two tables actually performed by a database engine?

I am sure that listing one tuple against all tuples of the other table cannot be the way to perform the join; it's just a way of understanding what the output will look like. Otherwise, for two tables containing 1000 tuples each, a join would prepare an intermediate data set of 1000*1000 tuples! That's hard to believe.

There are a number of ways depending on what the DBMS thinks you want versus what is helped in the database.

Read a row from the first table, then read any matching rows from the second table.
This is the preferred method when you are requesting a very few rows and there are indexes to support the read of the second table.
Matching index scan, select the required set from an index of the first table, then match this set to an index of the second table (usually after a sort), then fetch the required rows. Usually this method is used where a substantial number of rows are requested in a particular sequence.
Brute force, get all the rows from the first table and sort them into the right sequence, then get all the rows from the second table and sort them into the right sequence, then merge the results. Usually this method is used when there are no usable indexes to support the join. Its a performance pig and only used where nothing else will do.

There are many variations on these three methods which vary from RDBMS to RDBMS and the more expensive commercial databases have dozens of subtle optimizations which they will use depending on the circumstances.


Migrate ssrs 2008 r2 to ssrs 2016 The current setup of the server is Windows Server 2008 sp1, with SQL Server Reporting Services 2008 r2 installed. The databases are on a separate server which is using SQL Server 2008 r2.

I have a new server with Windows Server 2012 r2, I have installed SSRS 2016 on this server and configured it the same as the original server, restored the encryption keys, and connected to an exact copy of the DB server.

Locally on the new RS server I can access and run the reports using the web interface http://{servername}/reports/. However when I try to access the same url remotely I get redirected to http://{servername}/reports/#/browse, I can see the folders and reports, but when I run a report I get a blank report.

It appears as if ssrs 2016 does not support IE9. The machine I was using to access the server remotely only had IE9 installed. When I used the IE development tools on the server itself to browse to the site using IE9 standards, I had the same problems.


How to calculate average of list of qualifications of each pupil MYSQL I have this database and query:

http://sqlfiddle.com/#!2/b4cf72/116

And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are more than 1 row on query:

http://sqlfiddle.com/#!2/b4cf72/115

    SELECT pupils.id_pupil, NAME, surname
       ,IFNULL(qua.media, 0)     media
       ,IFNULL(qua.count, 0)     number_class_qualifications
       ,(
         SELECT AVG(qualification)
           FROM   qualifications
                  JOIN pupils USING (id_pupil)
           WHERE  type_qualification     = 'exam'
                  AND id_trimester       = 1
                  AND LEVEL              = 1
                  AND class              = 'A'
           GROUP BY id_pupil
       ) AS exam_average,
       SUM(CASE WHEN type_incident = 'miss' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS misses,
       SUM(CASE WHEN type_incident = 'delay' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS delays,
       SUM(CASE WHEN type_incident='attitude' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS attitude,
       SUM(CASE WHEN type_incident='miss_justif' AND incidents.id_trimester=1 THEN 1 ELSE 0
           END) AS misses_justificadas
FROM   pupils
       LEFT JOIN incidents
            ON  incidents.id_pupil = pupils.id_pupil
       LEFT JOIN (
                SELECT id_pupil,
                       id_trimester,
                       COUNT(qualification) COUNT,
                       ROUND(AVG(qualifications.qualification), 2) media
                FROM   qualifications
                WHERE  type_qualification = 'class'
                GROUP BY
                       id_pupil,
                       id_trimester
            )                AS qua
            ON  (qua.id_pupil = pupils.id_pupil)
WHERE  LEVEL = 1
       AND class = 'A'
       AND qua.id_trimester = 1
GROUP BY pupils.id_pupil

The problem is on this sentence:

SELECT AVG(qualification)
FROM   qualifications
   JOIN pupils USING (id_pupil)
WHERE  type_qualification = 'exam'
       AND id_trimester   = 1
       AND LEVEL          = 1
       AND class          = 'A'
    GROUP BY id_pupil

How can I solve it?

Your current subquery is returning the avg(qualification) for all pupils but it is not relating each row back to a single pupil.  You need to make this a correlated subquery which will query your qualifications while using the pupils from your outer query.  I would suggest rewriting the subquery query the following way:

(select avg(q.qualification)
from qualifications q
where type_qualification='exam'
  and p.id_pupil = q.id_pupil
  and q.id_trimester=1
group by q.id_pupil) as exam_average

You will notice that this subquery doesn't have the JOIN written inside of it but it is referencing your pupils table from the main ares of your query.  The full query would be written:

SELECT
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  (select avg(q.qualification)
   from qualifications q
   where type_qualification='exam'
     and p.id_pupil = q.id_pupil
     and q.id_trimester=1
   group by q.id_pupil) as exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester,
   count(qualification) count,
     round(avg(qualifications.qualification),2) media
  from qualifications
  where type_qualification='class'
  group by id_pupil, id_trimester
) as qua
  ON qua.id_pupil = p.id_pupil
WHERE p.level=1
  and p.class='A'
  and qua.id_trimester=1
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count;

See SQL Fiddle with Demo

This could also be written without using the correlated subquery and you would JOIN on the subquery similar to:

SELECT
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  IFNULL(q.exam_average, 0) exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester,
   count(qualification) count,
     round(avg(qualifications.qualification),2) media
  from qualifications
  where type_qualification='class'
  group by id_pupil, id_trimester
) as qua
  ON qua.id_pupil = p.id_pupil
LEFT JOIN
(
  select avg(q.qualification) as exam_average, q.id_pupil
  from qualifications q
  where q.type_qualification='exam'
    and q.id_trimester=1
  group by q.id_pupil
) q
  on p.id_pupil = q.id_pupil
WHERE p.level=1
  and p.class='A'
  and qua.id_trimester=1
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count, q.exam_average;

See SQL Fiddle with Demo. Both will generate the result:

| ID_PUPIL |         NAME |          SURNAME | MEDIA | NUMBER_CLASS_QUALIFICATIONS |   EXAM_AVERAGE | MISSES | DELAYS | ATTITUDE | MISSES_JUSTIFICADAS |
|----------|--------------|------------------|-------|-----------------------------|----------------|--------|--------|----------|---------------------|
|      140 |        María |     Adámez Nieto |     4 |                           2 | 4.733333269755 |      2 |      1 |        1 |                   2 |
|      141 |       Daniel | Alonso Fernández |     6 |                           3 | 8.400000095367 |      0 |      0 |        0 |                   0 |
|      142 |        Rocío |   Anos Población |     4 |                           1 | 6.366666634878 |      1 |      1 |        0 |                   1 |
|      143 |       Teresa |  Arribas Miranda |   3.5 |                           2 | 6.766666650772 |      0 |      0 |        0 |                   0 |
|      144 | Isabel María |    Barroso Suero |   5.5 |                           2 |              4 |      1 |      0 |        0 |                   1 |


Inserting unrelated data while a transaction is occuring I'm thinking about implementing transactions to my app to help with consistency issues. I'm using MySQL.

I'm wondering what might happen if while inserting several rows as a transaction, the server will also need to insert a single row, that is completely unrelated.

To be more clear, I will present a scenario (hypothetical) of a store:

The application server in running nodejs, and connected to a MySQL server. It then receives a request to add a stock of 50 new computers, and needs to insert the 50 rows representing their SN to a table stock, plus a row in another table shipments, all 51 rows must be inserted in an atomic manner (all or none). While this is happening, the same nodejs server receives a request to register a new customer, and is required to add one row to a table customers.

In that scenario, what will happen if the transaction fails and must be rolled-back? Will the row in customers be ommited as well? Is this scenario even possible? Or maybe it depends on the implementation, and if so, how to implement it so that this scenario is garanteed never to happen?

If you can handle all the writes in a single connection, then Plan A.  Note:  Moving from one "page" to another in a browser necessarily leads to another connection, hence Plan B.

Plan A:  BEGIN; write...; COMMIT;  And check for errors.  ROLLBACK on errors and retry the entire transaction.  If not successful, tell the user that his seat on the plane was taken and he must start over.

Plan B:  You must set up another table to keep track of "transactions" and be ready to rollback (etc) things outside of what InnoDB (much less MyISAM) can do for you.

Note:  If Plan A takes more than a few seconds, you are asking for trouble and for failures.  If it takes longer, switch to Plan B.