Understanding the impact/risk of turning off "verify backup integrity" on SQL backup Currently we use standard maintenance plans for backups on the SQL Server 2005/2008/2008R2/2012 servers in our environment, and the "Verify backup integrity" box has always been checked.

Some of the backups run very long, so I've recommended turning off that option, but management needs me to document the impact and risks of this change.

I understand the use and history of this option, it just seems unnecessary to me to double the time for the backup job when (in my opinion), any error that is likely to occur would occur during the backup step, not during the verify.

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something? (We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Is there any official MS recommendations for when it is safe to turn this off?

Do you run "verify" on every backup in your environment? Do you spot-check them?

EDIT: To clarify, when you check "verify backup integrity" in the maintenance plan, SQL will do a full RESTORE VERIFYONLY on each database immediately after each backup. This is just as data/IO intensive as the original backup, and (basically) doubles the overall time of the backup job. This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something?

No, you are right :-)
RESTORE VERIFYONLY only will not ensure that you will be able to recover your database in the event of corruption. By nature, it will not perform any integrity checks.
A better way will be to periodically take your backups and do a valid restore on a different server and perform DBCC CHECKDB on it.
This is one reason, why I am not a big fan of maintenance plans since the GUI does not expose a lot of options like backup .. with CHECKSUM that can be achieved by T-SQL.
From Paul Randal's Myth blog

24p) using RESTORE … WITH VERIFYONLY validates the entire backup
No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY  and using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.
Do you run "verify" on every backup in your environment? Do you spot-check them?

I dont run the VERIFYONLY. Instead I take backup with CHECKSUM and then have them restored + CHECKDB'ed on a different server. You can follow
Statistical Sampling for Verifying Database Backups approach if you want to be creative.

This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).

You can enable Trace Flag 3023 so that the CHECKSUM option is automatically enabled for the BACKUP command. As always, test the behavior of any trace flags in your environment !
The bottom line is - ditch maintenance plans and use a more sensible backup solution (hint: Ola's backup solution) that will allow you to customize it based on your needs.

(We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Backup up locally to the disk and then have a PowerShell transfer job that will copy the backup locally from the server to a network share (backup server). That will be more faster than to directly copy to a network share.
Also, enable instant file initialization, that will help with auto-growths on data files as well as will help cutdown the restore time (incase if you have to restore your databases). Its always good to have options handy.
A good read will be : Backups: Planning a Recovery Strategy


Is it necessary to create primary and foreign keys in order to use tpc-h benchmark? I'm doing some tests with tpc-h benchmark. The tpch-h benchmark simulates a data warehouse environment. The most important things here are the queries against the data warehouse. Therefore, does it make sense to create primary and foreign keys for this benchmark? I read some articles that do that, but I don't understand why it's necessary.

The specification TPC BENCHMARKTM H, Standard Specification, Revision 2.17.1, can be found here. See section "1. LOGICAL DATABASE DESIGN / 1.5 IMPLEMENTATION RULES". Indexes can be created on combinations of  fields defined in 1.4.2.2 and 1.4.2.3 and on date fields. Indexes may only contain fields of one table. So indexes can be built on the following fields:

Table PART:

P_PARTKEY

Table SUPPLIER:

S_SUPPKEY

Table PARTSUPP:

PS_PARTKEY
PS_SUPPKEY

Table CUSTOMER:

C_CUSTKEY
C_NATIONKEY

Table ORDERS:

O_ORDERKEY
O_CUSTKEY
O_ORDERDATE

Table REGION:

R_REGIONKEY

Table LINEITEM:

L_LINENUMBER
L_ORDERKEY
L_PARTKEY
L_SUPPKEY
L_SHIPDATE
L_COMMITDATE
L_RECEIPTDATE

Table NATION:

N_NATIONKEY
N_REGIONKEY


SQL Server Management Studio 2012, authentic multi-user login Issue:

I set up SQL Server and SQL Server 2012 Management Studio on the Administration login.

When I login on my login I can open SQL Server 2012 Management Studio, however I cannot access the database. I can see the database name but I am not able to open it.

Error message:

  The database xyz is not accessible. (ObjectExplorer)*

You just need to create your user and assign it to login for accessing the database.

Assuming you want to have read/write access:

USE [master]
GO
CREATE LOGIN [Domain\User_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [Database_name]
GO
CREATE USER [Domain\User_name] FOR LOGIN [Domain\User_name]
GO
USE [Database_name]
GO
EXEC sp_addrolemember N'db_datareader', N'Domain\User_name'
GO
USE [Database_name]
GO
EXEC sp_addrolemember N'db_datawriter', N'Domain\User_name'
GO


Need to add leading zeroes to string representing a date I have a string that represents a date in the format MM/DD/YY but some dates that I need to insert into a table are missing the leading zero on the MM and/or DD.
For example, '9/6/02'.
I need to add (when necessary) leading zeroes to the MM and DD portion of the string before inserting the value into a varchar2 column.
How to do this?
Currently, the DBA has the column as a varchar2.  No calculations or comparisons are done with the column so it is just for reporting purposes.  I will recommend changing the column to be a date type, however in the meantime I need a solution that will work with the varchar2 column.

My first recommendation would be to use a DATE column type as it is best practices.
If that isn't possible, you can convert the string into a date, then put that in the format you need:
SELECT
  TO_CHAR(TO_DATE(Column,'MM/DD/YY'),'MM/DD/YY') /* I would recommend ISO standard YYYY-MM-DD so the column will sort properly) */
FROM
  /* etc */


Adding where condition with index make query slower(use filesort and temporary) I'm trying to implement soft-delete so I added deleted_at (datetime) column to users table.
Also there is a query i need to run:

    SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
GROUP BY `users`.`category_id`;

type, possible_keys, key, key_len, ref, rows, filtered, Extra
'index', 'index_users_on_category_id', 'index_users_on_category_id', '5', NULL, '840926', '100.00', 'Using index'

which was running just fine until i added  WHERE clause

    SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
    `users`.`deleted_at` IS NULL
GROUP BY `users`.`category_id`;

type, possible_keys, key, key_len, ref, rows, filtered, Extra
'ref', 'index_users_on_category_id,index_users_on_deleted_at', 'index_users_on_deleted_at', '6', 'const', '420463', '100.00', 'Using index condition; Using temporary; Using filesort'

Of course it becomes slower, i can't find information

why type changed
why key_len changed from 5 to 6
why rows in second query shows half of total number (800k)

And how can i make it faster without using USE INDEX (index_users_on_category_id)

this index actually helped: deleted_at, category_id, however, I tried to do this without adding new index
Since there are much more records where deleted_at is null, i tried next queries:

SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
     `users`.`id` NOT IN (SELECT `users`.`id` FROM `users` WHERE `users`.`deleted_at` IS NOT NULL)
GROUP BY `users`.`category_id`;

And query optimizer used much better schema. I will try it soon on big set and hoe it will work.


Query only for the first n results of like What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up with a solution to the given search.

What I'm doing right now is that I query mysql with jooq like:

dsl.select(re.ID).from(re).where(re.EXPLANATION.likeIgnoreCase("%" + contains + "%")).fetch()

which is equivalent to

SELECT ID from EXPLANATIONS where EXPLANATIONS.EXPLANATION like "%" + contains + "%"

but I really don't need the whole result (or well, not in the first step). The reason for the slow speed is that this database-table contains some 2 million records, and the EXPLANATION field is varchar(2000) which makes it kinda slow to query (there is however an FULLTEXT INDEX defined for this field).

My questions would be

how to, if possible, query only for the first n results of my query
If there is any possible INDEX I could define to make querys like this faster for the given circumstances.

I understand you do not want only to limit the number of returned results (which could be done with a LIMIT at the end of the query), but the number of records that the LIKE operation is applied.

One idea could be, instead of apply the LIKE operation on all records of EXPLANATIONS table, doing on a limited number of items of the table:

SELECT exp.ID FROM(select * from EXPLANATIONS limit 1000) exp WHERE
exp.EXPLANATION LIKE "%" + contains + "%"


When is the modify_date of an object updated? As part of the daily checks I'm setting up, I'm restoring the nightly full backups to our test server and checking the modified date of objects. This is not for any kind of precise change tracking or version control, more for proof that something has\hasn't been changed when somebody says something's different.

I've put a table on the test server and after initially populating it, a stored proc queries sys.objects for each restored database to get the modify_date for all the objects.  This date is compared to the modified date I first recorded. If the date in the table doesn't match that from the restored database then it flags. This produces the correct result in 99.9% of cases but a weird little problem has appeared. There are seven functions in our CRM database that show a modify_date of the date and time that the restore took place rather than a 'modified date'.

I can't see any reason for this at all.  It only happens for a tiny fraction of the objects I'm testing across all our databases.  I can't find any triggers that could be updating the functions.  The databases are on SQL Server 2014 so CREATE OR ALTER isn't a possibility.  I've also checked the production servers and the modify_date for these objects matches the last time a restore was performed.

What could be altering, or appearing to alter, functions during a database restore?

What could be altering, or appearing to alter, functions during a database restore?

You can find it using default trace or use SQL Server DDL Triggers to Track All Database Changes. You can even use XEvents depending on your comfort level.

I would say, there has to be some process that does the alter. SQL Server just do not alter selective objects after a db restore.


Target database memory exceed available shared memory I am trying to install Oracle 12c on a RHEL 6.4 system with 64GB of memory.  When I try to enable automatic memory to 48GB, I get the error:

What do I need to change to allow Oracle to use more than 32189MB?

[INS - 35172] Target database memory (49897MB) exceeds available shared memory (32189MB) on the system.

/etc/sysctl.conf contains the following so I'm not sure why it won't allow me to use more than 32G of shared memory

# Controls the maximum shared segment size, in bytes
kernel.shmmax =  68719476736
# Controls the maximum shared segment size, in bytes
#kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 16777216
#kernel.shmall = 1073741824
fs.file-max = 6815744
kernel.msgmni = 2878
kernel.sem = 250 32000 100 142
kernel.shmmni = 4096
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 3145728
net.ipv4.ip_local_port_range = 9000 65500
vm.min_free_kbytes = 51200
fs.file-max = 6815744

limits.conf contains the following:

oracle          soft    nproc           2047
oracle          hard    nproc           32768
oracle          soft    nofile          32768
oracle          hard    nofile          65536

MEMORY_TARGET needs /dev/shm filesystem with required size. By default /dev/shm size is half of the system memory. So you have to change line in /etc/fstab from:

tmpfs                   /dev/shm                tmpfs   defaults          0 0

to something like:

tmpfs                   /dev/shm                tmpfs   defaults,size=49G 0 0

df -h /dev/shm will show you tmpfs size. To avoid reboot after editing /etc/fstab you can do:

umount tmpfs
mount -a

But with such amount of memory I would go with ASMM (SGA_TARGET) and huge pages. Especially if you will have many clients connected. Overhead to manage 48GB of 4kB pages will be quite big.


MSSQL 2014 "The provided statistics stream is corrupt" Error 9105 I have a database that prevent to see user properties due to this error.
I've begin investigating and the problem comes from sys.asymmetric_keys and sys.symmetric_keys system views inside the database.
Both views uses the sys.sysobjvalues table that contains the following statistics:

Stats Name | Column Name
_WA_Sys_00000002_0000003C   objid
_WA_Sys_00000003_0000003C   subobjid
_WA_Sys_00000004_0000003C   valnum
_WA_Sys_00000005_0000003C   value
clst    valclass, objid, subobjid, valnum

The failure appears on the column "value"
DBCC SHOW_STATISTICS ("sys.sysobjvalues",_WA_Sys_00000005_0000003C) that returns:

Name
_WA_Sys_00000005_0000003C
(1 row(s) affected)

All density | Avg.Length | Columns
0,004830 8 |    7,204199      |      value
(1 row(s) affected)

Range_Hi_KEY
Msg 9105, Level 16, State 25, Line 43
The provided statistics stream is corrupt.

Connected using a DAC connection I've tried to delete it (permissions error) and update it without success. The [UPDATE STATISTICS sys.sysobjvalues WITH fullscan, columns] command fails with the "The provided statistics stream is corrupt " error but  [UPDATE STAT.. WITH fullscan, index] works but the error continues.

DBCC CheckDB returns no errors for this table (sys.sysobjvalues).

The server version is: Microsoft SQL Server 2014 (SP2-CU1) (KB3178925)(Hypervisor)

Any idea on how could I repair this DB without a Restore (All my available restores have the error on it)

Many Thanks

The problem may be in the damaged statistics, and probably in the one that was created automatically

To search for problem statistics you can use:

EXEC sp_updatestats

exec will iterate over all the tables and statistics, and if there are problems, it will show which statistics, more precisely the table, is the problem.

To remove auto_created statistics, you can use the following script:

select 'drop statistics [' + OBJECT_NAME (OBJECT_ID) + '].[' + Name + ']'
 from sys.stats
where auto_created = 1
  and OBJECT_NAME (OBJECT_ID) = 'DB PROBLEM TABLE NAME'

The script will generate rows for deleting auto_created statistics for the table "DB PROBLEM TABLE NAME" table

After running the generated lines, rerun

EXEC sp_updatestats

to check for errors.

Not superfluous would be

exec sp_msforeachtable N'UPDATE STATISTICS? WITH FULLSCAN'


When might a tsvector field pay for itself? I've been experimenting with tsvector indexes for Full Text Search, and see that it's a common practice to generate an store a vector in a column of type tsvector. We're on Postgres 11.4, but I've already seen this very practice used as an example for PG 12 generated columns. (Simpler than using a trigger for the same purpose.)

My question is, what is the benefit? I tried an expression GIN index on the tsvector of a text field, and a GIN index on the stored tsvector. With around 8M rows locally, I couldn't measure any meaningful speed difference. Given that storing the vector as a column and an index takes more space, I'm curious if there are obvious cases where the extra cost is justified. For example, when you've got a lot more roles.

Note: We're storing the text in the database, so this isn't one of those setups where you index an external page/document/etc without absobing the source text into the database.

If you use proximity searching (like "phraseto_tsquery" for example), with a functional index it will have to re-parse each match candidate document to a tsvector and check it for the right sequence and spacing of words.  This can be quite slow, especially if the number of candidates is much higher than the number of final results.  If the tsvector is stored, it can just read it and not reparse the document, this is much faster.  I think other advanced features like "ts_headline" may be in the same situation--but I haven't tested them.

Even if you just use "@@", I think that if the bitmap of the number of results doesn't fit in "work_mem", then it will also need to reparse the documents in order to recheck candidate matching blocks which "overflowed".  Of course in that case increasing "work_mem" would be likely be a better option than adding a column.

For what it is worth, if you use RUM rather than GIN, it will solve the the problem with phraseto_tsquery on functional indexes.


How SQL Server detects a record internally? I am following this SO post where the author (stack programmer) states

  SQL server uses the identity column as the key value to refer to a
  particular row. So only a single identity column can be created.

He also mentioned that

  Also if no identity columns are explicitly stated, Sql server
  internally stores a separate column which contains key value for each
  row.

Could anyone explain the two points(specially the second one) in  more details?

Thanks

Could anyone explain the two points(specially the second one) in more details?

I hope not, since they are both wrong. In SQL Server rows are identified by keys (in B-trees) or by row ids (in heaps). Non-unique clustered indexes have an internal uniqueifier column. The fact that often times an IDENTITY type column is also used as a primary key is orthogonal to the question.

Do not base your knowledge on stuff from posts on forums, including StackOverflow, and including this very post. A good source is the official documentation, eg. Tables and Index Data Structures Architecture. Read a good book, like the SQL Server Internals series.


Transaction Log file size for Index reorg I have a DB that is 100GB. It's fixed this way on purpose. It won't exceed this. I have two indices. One with a single value. One with three values.
I'm not a DBA. I'm a guy who's been tasked with working on SQL DB's part time on occasion. They were created by someone else and I'm on the steep part of the learning curve.
I have the transaction log set to 150GB. It works great until I automatically reorganize the indices. At this point I always get a transaction log full. Even if I set it to back up the log, then reorganize.
If I manually reorg it's fine, because I can do each index separately and I BU the trans log first.
So how big do I need the trans log to be, based on the above info, in order to be able to set up a maint task to reorg? Will it be any better if I do a rebuild instead?

You mention that if you run the index rebuild process manually, and take a transaction log backup in between each index rebuild, then you don't have a problem.

As has been noted, you can create a script to rebuild each index, taking a transaction log backup in between each rebuild.

Alternately, you could increase the frequency of your schedule transaction log backups while the rebuild process is running. For example, if you normally perform tlog backups once an hour, do it every ten minutes for the duration of the maintenance job.

There are multiple ways to do this:

Schedule a secondary tlog backup job for the expected duration of the index rebuild.

For instance, if your regular tlog backup runs at 1AM, 2AM, 3AM, etc., and the rebuild job runs at 2:30 AM and usually takes no more than 3 hours, schedule a secondary t-log backup starting at 2:35 AM, running every 10 minutes, and ending at 5:35 AM.
Have a disabled secondary schedule on your t-log job that you enable at the start of the rebuild job, and disable at the end. In this case (with the same theoretical frequencies as in the example above), this schedule would be set to run the job every 10 minutes starting as 12:05 AM. You should be able to use sp_update_schedule to enable and disable the job schedule. I would make sure the schedule had a unique name, and use that in the stored procedure (as that could remain the same across multiple servers).

If the schedule was named "TLog Additional Runs", You would use:

EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 1;

to turn the schedule on, and:

EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 0;

to turn it off.


Can the FORCESEEK query hint be used on tables being queried against on a linked server? (I.e. as part of a distributed query) I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the original server), I get the following error:

  Msg 7436, Level 16, State 1, Line 4
  The query processor could not produce a query plan because FORCESEEK or FORCESCAN hints on table or view 'TableBeingQueried' cannot be used with remote data sources. Remove the hints and resubmit the query.

Example T-SQL:

SELECT DISTINCT Table3.Field5
FROM Server1.Database1.Table1 AS T1
INNER JOIN Server1.Database1.Table2 AS T2 WITH (FORCESEEK) -- Index exists for T2.Field2
    ON T1.Field1 = T2.Field2
INNER JOIN Server1.Database1.Table3 AS T3 WITH (FORCESEEK) -- Index exists for T3.Field3
    ON T2.Field4 = T3.Field3

You CANNOT use FROCESCAN or FORCESEEK on remote data sources.

Reference: Hints (Transact-SQL) - Table

The FORCESCAN hint has the following restrictions:

  The hint cannot be specified for a remote data source.

When using the FORCESEEK hint (with or without index parameters), consider the following guidelines:

  The hint cannot be specified for a remote data source. Error 7377 is
  returned when FORCESEEK is specified with an index hint and error 8180
  is returned when FORCESEEK is used without an index hint.


Get results from "combined, either inner join" I have a feeling this must have been asked multiple times, but I just cannot find a solution, or even a questions probably because of difficulty in explaining.
We all know that inner join basically gives an "intersection" of two tables. And hence multiple inner joins give the intersection of all tables. What I -however- wish is to get an inner join that gets an intersection of table B with A or table C with A.
Like for a request that gets all organizations I'm either moderator or admininistrator.
SELECT *
FROM organization
INNER JOIN moderator_organization
  ON organization.id = moderator_organization.organization AND moderator_organization.user = 10
INNER JOIN admin_organization
  ON organization.id = admin_organization.organization AND admin_organization.user = 10

Above however would only select the organizations from which the user (with id 10) is both admin and organizer, not one where either is true. To visualize in a diagram I'd like:

Where the red region is what I'd like to request.

Venn diagrams are not ideal to visualize join operations like a_horse commented:

Venn diagrams do not visualize joins, but set operations like union, intersect or except.

And his link to illustrate:

https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/

The twist, I think, is this: the Venn diagram is the right tool to visualize your objective, but the SQL JOIN is the wrong approach. You need a UNION (or equivalent) at the core of your query.
Title and the example query are misleading like that. What's more, you  start with:
SELECT * FROM ...

That gets all columns from all joined tables. Also not the expressed objective:

all organizations I'm either moderator or administrator.

UNION
The core query is:
SELECT organization FROM admin_organization     WHERE user = 10
UNION
SELECT organization FROM moderator_organization WHERE user = 10

UNION. Not JOIN. And not UNION ALL - we don't want duplicate organizations in the result.
The column name organization is also misleading (IMHO). Should really be something like organization_id for clarity.
The resulting set of unique IDs may already be all that's needed. To flesh it out with more (or all) attributes of the organization (columns of table organization), now we JOIN to the table:
SELECT *
FROM  (
   SELECT organization AS id FROM admin_organization WHERE user = 10
   UNION
   SELECT organization FROM moderator_organization WHERE user = 10
   ) x
JOIN   organization USING (id);

It's typically (substantially) cheaper to apply UNION on just the ID column, and then join. It may even be a necessity, if some of the columns have types have data types with no equality operator. More common than one might think. See:

How to sum the values of a json column filtered with regex?

Assuming referential integrity, enforced with FK constraints, nothing is lost in the join.
For convenience, it added a column alias in the subquery (organization AS id), so that we can use the simplified join condition with USING (id) which, in turn, allows us to use the simple (and now correct) SELECT * in the outer query to get all columns of the table organization without a duplicate ID column.
Alternative with EXISTS
Shorter equivalent that also avoids undue duplication or elimination of rows:
SELECT *
FROM   organization o
WHERE  EXISTS (SELECT FROM admin_organization     WHERE user = 10 AND organization = o.id)
   OR  EXISTS (SELECT FROM moderator_organization WHERE user = 10 AND organization = o.id);

Naming convention
A naming convention with descriptive names can avoid some of the confusion and noise. Use organization_id (or org_id if you prefer short names and there is no ambiguity) for all three: admin_organization.organization, moderator_organization.organization, and organization.id.


'Could not fsync file' while running VACUUM FULL on a table I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL.
For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error:

ERROR:  could not fsync file "base/16392/3655838": Input/output error

But for some reason, when i run:
SELECT pg_relation_filepath('qwerty');

It shows the file path as another:
base/16392/3391046

I cant seem to find any information about this online, I would be more than glad if anybody could help!

The file that PostgreSQL tries to sync is probably the new file created during VACUUM (FULL). This system call persists the file to disk so that it cannot get lost during a crash.
Perhaps your disk is having hardware problems, or you are using a file system that has problems with that (which you shouldn't do).


Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE? I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction".

I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement will produce deadlock errors? I know that it is only a read lock so multiple selects won't be a problem, but what if there is an INSERT, UPDATE or DELETE statement (with subqueries possible with joins) and a SELECT statement (possible with joins or subqueries)?

Is it possible that the error will be thrown on the SELECT instead of the INSERT, UPDATE or DELETE.

The story is here if you are curious.

The direct answer to your question's title is No.
SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.
You also have special cases of SELECT where you can lock specific rows on demand.
UPDATE 2011-08-08 16:49 EDT
You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)
UPDATE 2015-09-22 18:40 EST
Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:
SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b ON  b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r ON  r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS p ON  p.id     = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.


How to save working hours of multiple stores and then query it without duplication? I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this schema but unfortunatly I cannot query it like I would like to. The schema looks like this

This is the query I'm using currently (for day: Saturday and hour: 3:15 PM):

SELECT so.day, so.store_id, so.open, so.close
FROM shop_openings so
ORDER BY FIELD(so.day, 6, 7, 1, 2, 3, 4, 5) ASC,
         '15:15' COLLATE utf8mb4_unicode_520_ci
                 NOT BETWEEN CONCAT( LPAD( so.open, 2, '0'), ':00')
                         AND CONCAT( LPAD( so.close, 2, '0'), ':00'),
         LPAD( so.close, 2, '0' ),
         LPAD( so.open, 2, '0' )

But it query just like the screen above shows.

Now here's what the list I want to achieve:

Let we say that today is Saturday. We have 4 stores which only 2 of them works in Saturday. I want to recieve 4 results order by day and open/close hours. Below I selected which rows I want to get:

No other results. Only 4 results as we have 4 stores in store_table. Nearest 2 shops opened in Saturday and then 2 shops opened in Monday.

I have no idea how the schema should like other than mine, so any suggestion are welcome.

Here is a SQL Fiddle: http://sqlfiddle.com/#!9/9709e/1/0

If you only want the next day, and sundays are closed you can use this query,, when you want the next two days You have to UNION another Query and exclude all previous shops like i did in the WHERE clause

You can replace the @searchday session  variable with DAYOFWEEK(NOW()) to get the actual
day

CREATE TABLE `shop_openings` (
  `shop_id` int,
  `day` int,
  `open` varchar(5),
  `close` varchar(5)
  );

  INSERT INTO `shop_openings` VALUES
  (10, 6, '9:00', '16:00'),
  (10, 5, '9:00', '16:00'),
  (10, 4, '9:00', '16:00'),
  (10, 3, '9:00', '16:00'),
  (10, 2, '9:00', '16:00'),
  (10, 1, '9:00', '16:00'),
  (11, 6, '10:00', '18:00'),
  (11, 4, '10:00', '18:00'),
  (11, 2, '9:00', '18:00'),
  (12, 5, '9:00', '17:00'),
  (12, 3, '9:00', '17:00'),
  (12, 1, '9:00', '17:00'),
  (13, 4, '9:00', '16:00'),
  (13, 3, '9:00', '16:00');

✓

✓

SET @searchday = 6;
SELECT so.day, so.shop_id, so.open, so.close
FROM shop_openings so
WHERE `day` = @searchday
UNIOn
SELECT so.day, so.shop_id, so.open, so.close
FROM shop_openings so
WHERE `day` = IF(@searchday = 7 Or @searchday = 6,1,@searchday + 1)
AND shop_id NOT IN(SELECT so.shop_id
FROM shop_openings so
WHERE `day` = @searchday)

✓

day | shop_id | open  | close
--: | ------: | :---- | :----
  6 |      10 | 9:00  | 16:00
  6 |      11 | 10:00 | 18:00
  1 |      12 | 9:00  | 17:00

db<>fiddle here


Confusion with Foreign Keys I've been tasked to build a somewhat basic Prisoner database and I've run into a situation where I don't know what the appropriate route to take is.

I have a Prisoner table, that looks like the following:

  Primary Key                 attributes
 {prisoner_id, person_ssn} -> offense_id, cell_block_id, etc.

I have a Person table and the Primary key is {person_ssn}

 {person_ssn} -> first_name, last_name, dob, etc.

Now I am a bit confused on whether person_ssn in the Prisoner table needs to be a foreign key or not, and why. Because it's a part of the primary key, does that rule it out of being a foreign key?

You should separate the concepts. The combination {prisoner_id, person_ssn} is perfectly valid for Primary Key in the Prisoner Table. But you do not need to do that, if prisoner_id is unique. In that case, adding person_ssn to the PK is not optimal.

Adding a foreign key constraint on person_ssn checks that no person_ssn is added to Prisoner table if there is not a corresponding entry (for the same person_ssn) in the Person table. So, you should definitely add person_ssn as a foreign key in Prisoner table.

If you want fast look_ups on Prisoner table according to person_ssn, you should have an index at person_ssn column at Prisoner table. Luckily as the SO question/answers Does MySQL index foreign key columns automatically? suggest, InnoDB already adds indexes to foreign key columns (from your comments I assumed you are using MySQL.)

So:

Primary keys -> unique identifiers of each table row
Foreign key -> referential integrity with referenced table
Index -> Fast look ups

Having said that and as comments suggest, the same column can be used as a Foreign key and participate in a Primary Key as well. Those two concepts are independent from each other.


MongoDB admin user cannot access system.views collection One of our MongoDB databases contains a collection called system.views and I cannot find any information for this collection in Mongo Doc:

https://docs.mongodb.com/manual/reference/system-collections/

When I use admin user to execute command:

db.system.views.findOne()

It returns an authorization error:

"errmsg" : "not authorized on <database> to execute command { find: \"system.views\", filter: {}, limit: 1.0, singleBatch: true, lsid: { id: UUID(\"98bfa592-4d51-4d4a-964c-687c97444a2c\")}, $clusterTime: { clusterTime: Timestamp(1568022175, 1), signature: { hash: BinData(0, 316762B835CC7EBA0A70B314421B9366D679415C), keyId: 6723325935254241281 } }, $db: \"<database>\" }",

Admin user has roles

"roles" : [
        {
                "role" : "userAdminAnyDatabase",
                "db" : "admin"
        },
        {
                "role" : "clusterAdmin",
                "db" : "admin"
        },
        {
                "role" : "dbAdminAnyDatabase",
                "db" : "admin"
        },
        {
                "role" : "readWriteAnyDatabase",
                "db" : "admin"
        }
]

We are using mongodb x509 authentication mechanism.

My question:

What is this system.views collection?
How can I get my admin user authorised to run command on this collection?

Many thanks.

What is this system.views collection?

As per percona blog here MongoDB 3.4 views are non-materialized views, and behind the scenes the engine runs an aggregation. Creating a view requires that we specify a collection or a previous existing view. When a view is the source collection from another view, it allows us to execute a chained aggregation.
To create a view, we should use the db.createView(‘view_name’,’source’,[pipeline]) command, specifying the view name, the view source collection and the aggregation pipeline. This aggregation pipeline, as well as the other parameters, is saved in the system.views collection. This is the only space that the view will use in the system. A new document is saved in the system.views collection for each view created.
Views appear as a collection when we are listing them. The show collections command shows us views as one collection, but such collections are read-only. To drop a view, we simply execute db.collection.drop(). The collection is removed from the system.collections, but the data remains untouched because it only removes the code that generates the view result.

How can I get my admin user authorised to run command on this
collection?

use admin
db.runCommand({ createRole: "readViewCollection",
  privileges: [
    { resource: { db: "", collection: "system.views" }, actions: [ "find"] }],
    roles : []
})

db.grantRolesToUser('<your_user>',['readViewCollection']);

for further your ref here, here and here


MongoDB sharding multiple collections with the same shard key Lets imagine I have two mongodb sharded collection: likes & comments.

Both collections are sharded, both collections have userId as a sharded key.

Lets imagine that likes collection is huge and comments collection is small. I have 10 shards in total, let's name them shard A to shard G.

Can I assume that if some particular user's likes are in shard A, then his comments will be in shard A as well?

So what I want to achieve is to have any particular user's data in the same shard. Other user's likes may be in shard C, then his comments should be in shard C as well.

Is it possibly to configure MongoDB? ( or it's the way how it's working by default? )

Is it possibly to configure MongoDB? ( or it's the way how it's
  working by default? )

Yes, if you want to control data location, you have to use Zones. Follow this link for a better explanation on how to do it: https://docs.mongodb.com/manual/tutorial/sharding-segmenting-shards/ .


You will need a calendar table to distribute the quantity to each date in the interval:
CREATE TABLE calendar (dt date not null primary key);
INSERT INTO calendar (dt)
VALUES ('2021-01-14'),('2021-01-15'),('2021-01-16'),('2021-01-17')
     , ('2021-01-18'),('2021-01-19'),('2021-01-20'),('2021-01-21');

Now you can use a join for that:
SELECT MAX(qt)
FROM (
    SELECT dt, SUM(quantity) as qt
    FROM timetable t
    JOIN calendar c
        ON c.dt BETWEEN date(t.dt_start) AND date(t.dt_end)
    WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
    GROUP BY dt
) tt;

A calendar table is very useful for many tasks so I suggest you create one and populate it. Many DBMS including newer versions of MySQL support recursive CTE:s which can be used instead.
Getting the totals for each dt:
SELECT dt, SUM(quantity) as qt
FROM timetable t
JOIN calendar c
    ON c.dt BETWEEN t.dt_start AND t.dt_end
WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
GROUP BY dt;

Fiddle


SQL Server 2005 query with multiple conditions, some indexed, some not I have a table with 50 million rows. It is indexed on zip. A query

Select *
from table
where zip = '12345'

returns 20,000 rows in about 2 seconds

When I add an additional condition it jumps to 15 minutes...

Select *
from table
where zip = '12345'
  and otherField = 'Y'

My expectation is SQL would use the index, identify the 20,000 rows and then scan all for that non indexed condition. Maybe a few seconds at max. Instead it seems to be scanning the whole database table.

I cannot add another index... There are 150+ columns we might test, most have Y/N values.

Is there a better way to write this type of query?

Could I have a setting wrong in the server config?

Thank you!

Is there a better way to write this type of query?

No, the way you've written it is the "best" way.  Unless, of course, that way doesn't work.  The joy and frustration of using a declarative language is the optimiser.  It is your best friend when it works and worst enemy when it doesn't.

One way to kick the optimiser into doing the right thing is to re-write your query in a semantically identically way such as:

select <whatever>
from table as t
inner join
    (
    Select <primary key columns>
    from table as b
    where b.zip='12345'
    ) as z
    on t.PrimaryKey = z.PrimaryKey  -- repeat if multi-column key
    and t.otherField = 'Y';

Now the optimiser is starting from a different place and may, possibly end up with a different, better, answer. For simple single-table queries I fear it is unlikely to show much change, however.

I would only suggest you try this after you're absolutely sure you've done all you can with indexes.  Re-writing queries in non-obvious ways adds maintenance overhead to the application.  It may also prove counter-productive when you patch or upgrade SQL Server.


Should I use table hints with a process-keyed table? I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table locks) when using these types of tables.  Here's some background:

I'm developing a batch process that inserts the rows resulting from a stored procedure call into a table.  I have no control over the stored procedure.  The table is keyed by process id (@@spid) because it needs to be read by several stored procedures that follow and should not conflict with other batches running at the same time.  This shouldn't be too big a problem because it is a low volume server, e.g. not too many of these batches will be running simultaneously.  Still, I wouldn't want to create table locks if it can be avoided as I imagine blocking may cause unexpected failures or delays.

Any advice?

You said

.. no control over the stored procedures
.. low volume server, e.g. not too many of these batches will be running simultaneously.

Therefore, I'd do nothing. Hints should only be added very judiciously when required. As it stands, you have no known issue to solve.
If you still want to add a hint, then simply use ROWLOCK to disallow table locks.


Postgresql update a partitioned table, where update affects multiple partitions If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be modified?
If yes does it depend on which postgresql version?

PartitionedTable

PKey    SomeOtherValue
1       10
1       11
2       10
2       11
3       11

UPDATE PartitionedTable
    SET SomeOtherValue = SomeOtherValue + 1
WHERE SomeOtherValue > 10

You can update tuples from multiple partitions in one statement.

If you are using declarative partitioning, then the ability to update tuples in a way that causes them to move partitions was added in v11. If you are using partitioning by inheritance, then what it does in this case is up to your triggers.


MS SQL Server: Transactional replication not working but no errors are present (MS SQL Server 2008 R2) I have transactional replication setup from server A to server B.
Server A is an Oracle server, so the publisher and subscriber both reside on server B (SQL 2008 R2).
It was working for a while, but then something dropped the privileges (within the Oracle server) of my Oracle user's access to the tables being replicated.

I fixed the privileges (via re-granting those tables SELECT to my Oracle user.)
Then I marked my transaction replication's snapshot for reinitialization and generated a new snapshot.
This appeared to work and brought over the most recent data.

Transactional replication is still not working as any subsequent data changes in the Oracle database aren't getting replicated across.
I don't see any errors in the Log Read Agent Status or Synchronization Status (or the Snapshot Agent Status - which succeeded when I generated the new snapshot.)
I also don't see any errors in any of the jobs that belong to my transactional replication.

Thoughts on how to debug this or what could be going wrong?
I would assume this is an issue on MS SQL Server's side with the transactional replication if the generation of the new snapshot worked fine now after I fixed the privileges in Oracle.

I'm still not sure what the issue was because nothing indicated a problem, but the fix ended up being to just generate DROP and CREATE scripts on the Transactional Replication Publication using SQL Server Management Studio and running them. After which, I had to reinitialize and generate a new snapshot since it was a new Publication technically.
I guess the old turn off / turn back on again trick for the win.


Validating the GST Identification Number (GSTIN) The following image describes the format of the GST Identification Number:

The first 2 digits denote the unique State Code in accordance with the Indian Census 2011. For instance, the State Code of New Delhi is '07' and that of Karnataka is '29'.
The next 10 characters denote the PAN (Permanent Account Number) of the taxpayer.
The 13th digit denotes the Registration Number (or Entity Number) of the tax payer with the same PAN.
The 14th digit is 'Z' by default for all – not intending anything currently.
The 15th digit is the Check sum digit – can be a number or an alphabetical character.

Perhaps I can carry out the validation by means of a PATINDEX or a Regex?

To supplement Aaron's completely valid answer, you might decide to do a more in-depth validation of the GSTIN number by validating the correctness of the check-digit, which appears as the last digit in the number.

The code below creates a number of functions that will be used by a CHECK CONSTRAINT to validate new rows, and any modifications to the GSTIN number.

The first two functions provide arbitrary mapping of the ASCII characters contained in the GSTIN number into base-36; that is 0 maps to 0, 9 maps to 9, A maps to 10 and Z maps to 35:

IF OBJECT_ID(N'dbo.map_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.map_char;
GO
CREATE FUNCTION dbo.map_char(@c char(1))
RETURNS int
AS
BEGIN
    DECLARE @val int;
    SET @c = UPPER(@c);
    IF ASCII(@c) >= 48 AND ASCII(@c) <= 57
        SET @val = ASCII(@c) - 48;
    IF ASCII(@c) >= 65 AND ASCII(@c) <= 90
        SET @val = (ASCII(@c) - 65) + 10;
    RETURN @val;
END
GO
IF OBJECT_ID(N'dbo.unmap_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.unmap_char;
GO
CREATE FUNCTION dbo.unmap_char(@v int)
RETURNS char(1)
AS
BEGIN
    DECLARE @c char(1);
    IF @v >= 0 AND @v <=9
        SET @c = CHAR(@v + 48);
    IF @v >= 10 AND @v <= 90
        SET @c = CHAR((@v + 65) - 10);
    RETURN @c;
END
GO

I've been unable to determine for certain if the government of India uses this same encoding, however it seems to work for the values provided.

This code uses the above dbo.map_char function to validate the check-digit in the given GSTIN number.

CREATE FUNCTION dbo.fn_validate_gstin
(
    @inp char(15)
)
RETURNS tinyint
AS
BEGIN
    DECLARE @return tinyint;
    DECLARE @i int = LEN(@inp);
    DECLARE @factor int = 1;
    DECLARE @char char(1);
    DECLARE @codepoint int;
    DECLARE @addend int;
    DECLARE @sum int = 0;
    IF @i <> 15 /* GSTIN MUST be 15 characters to be valid */
    BEGIN
        SET @return = 0;
    END
    ELSE
    BEGIN
        WHILE @i > 0
        BEGIN
            SET @codepoint = dbo.map_char(SUBSTRING(@inp, @i, 1));
            SET @addend = @factor * @codepoint;
            SET @addend = (@addend / 36) + (@addend % 36);
            SET @sum += @addend;
            IF @factor = 2 SET @factor = 1 ELSE SET @factor = 2;
            SET @i -= 1;
        END
    END
    DECLARE @remainder int = @sum % 36;
    IF @remainder = 0 SET @return = 1 ELSE SET @return = 0;
    RETURN @return;
END
GO

Note, there is absolutely no error-checking in this code; I leave that as an exercise for the reader.  The function returns 1 if the GSTIN contains a valid check-digit as the last digit.  If the check-digit is invalid, it returns 0.

Here I create a table that implements the dbo.fn_validate_gstin function:

CREATE TABLE dbo.t
(
    i char(15) NOT NULL
        CONSTRAINT CK_t_valid_gstin
        CHECK (dbo.fn_validate_gstin(i) = 1)
);

Here we insert several "test" GSTIN numbers:

INSERT INTO dbo.t (i) VALUES ('123456789012345');
INSERT INTO dbo.t (i) VALUES ('27AAFFM5744C1ZE');
INSERT INTO dbo.t (i) VALUES ('27AAACE7932L1ZC');

The first insert fails.  The second and third inserts succeed.  Attempting to insert an invalid GSTIN numbers result in this error:

  Msg 547, Level 16, State 0, Line 80
  The INSERT statement conflicted with the CHECK constraint "CK_t_valid_gstin".
  The conflict occurred in database "tempdb", table "dbo.t", column 'i'.

Note, the presence of a scalar function as part of a constraint prevents queries using parallelism.  This may or may not be problematic for your system.  If you need parallelism, you might consider using an INSTEAD OF trigger on the table to check the GSTIN numbers at insert or update.  For example:

IF OBJECT_ID(N'dbo.t_with_trigger', N'U') IS NOT NULL
DROP TABLE dbo.t_with_trigger;
GO
CREATE TABLE dbo.t_with_trigger
(
    i char(15) NOT NULL
);

GO
CREATE TRIGGER t_validate
ON dbo.t_with_trigger
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION
    DECLARE @bOk bit = 1;
    IF EXISTS (
        SELECT TOP(1) 1
        FROM inserted i
        WHERE dbo.fn_validate_gstin(i.i) = 0
        UNION ALL
        SELECT TOP(1) 1
        FROM deleted d
        WHERE dbo.fn_validate_gstin(d.i) = 0
        )
    BEGIN
        SET @bOk = 0;
    END
    IF @bOk = 1
    BEGIN
        DELETE
        FROM dbo.t_with_trigger
        FROM dbo.t_with_trigger t
            INNER JOIN deleted d ON t.i = d.i;
        INSERT INTO dbo.t_with_trigger(i)
        SELECT i.i
        FROM inserted i;
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        DECLARE @msg varchar(1000);
        SET @msg = 'Attempted to insert/update using an invalid GSTIN number.';
        RAISERROR (@msg, 14, 1);
    END
END
GO

Inserting an invalid GSTIN number:

INSERT INTO dbo.t_with_trigger (i)
VALUES ('123456789012345')
    , ('27AAFFM5744C1ZE')
    , ('27AAACE7932L1ZC');

results in this error:

  Msg 50000, Level 14, State 1, Procedure t_validate, Line 37 [Batch Start Line 129]
  Attempted to insert/update using an invalid GSTIN number.
  Msg 3609, Level 16, State 1, Line 130
  The transaction ended in the trigger. The batch has been aborted.

I based my implementation around the LUHN algorithm.


Database design: 4 types of users but have different functionality , separate or one table? I have 4 types of users:

Admins , normal user , company , service provider

Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share some attributes too (id .company name ,phone ,fax ,mail ).

They interact too with other entities in application to access some feature like post job or event or apply for it

Is it better to put them all in one user table like tbl_users or is it better to create separate table to every one ? or add to two tables one for (Admins and normal user) and other for ( company and service provider)

A simple design you can go for is to choose three tables

Table MasterUser

UserId - PK - Identity(auto increment)
phone
mail

Table AdminNormalUser

UserId -- Pk of this table and UserId of `MasterUser`
FirstName
LastName

Table CompanyPorviderUser

UserId -- Pk of this table and UserId of `MasterUser`
CompanyName
Fax

This design will give you a unique Id to each user, no matter a Admin, Normal, Company or provider user, which is a requirement for application that will use these tables, And it will get rid of columns with NULL values.


SQL statement timing from MySQL 5.7 client when using a script When I run a SQL statement interactively in a MySQL 5.7 client it tells me how long the query ran:

mysql> select now();
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:27:42 |
+---------------------+
1 row in set (0.07 sec)

In this case it ran for 0.07 seconds.

But when I run the same SQL statement through a script the output does not include the time the statement took.

>mysql -f -v --table -u myuser -pmypassword -h myhost mydb < test.sql

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:31:00 |
+---------------------+

How can I run a SQL statement from a script and still get back the length of time that it ran using the MySQL 5.7 client?

Thanks!
Bobby

You can use -vvv with mysql command.

mysql -f -vvv --table -u myuser -pmypassword -h myhost mydb < test.sql

> mysql -vvv < mysql.sql
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-10 04:08:33 |
+---------------------+
1 row in set (0.00 sec)

Bye

Hope it will fulfill your requirement :)


SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator I've created the following simple logon trigger on my SQL Server 2005:

CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER
FOR LOGON
AS
BEGIN
  PRINT 'Hello World'
END
GO

Which seems to be working fine in most cases except whenever the DTC is involved. Error messages I'm getting are:

The server failed to resume the transaction. Desc:4d00000273.

and:

Process ID 77 attempted to unlock a resource it does not own: DATABASE: 36 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

I've come across this article which goes some way to explaining the possible cause but the solution at the end isn't quite what I'm after.

Ultimately I'd like to use the trigger to restrict access from certain IP addresses to certain logins.

If you dig through the discussion in the articles comments, you'll find a link to KB951188 which details a bug in SP2 that was fixed in CU7. Check your version level to see if it applies.


Storing a very large matrix on Postgresql I have a list of 19100+ postal codes(with their associated centroid lat/long). Now I am planning to calculate the driving distances between each of them and store it in a database as a ready reckoner for use in another application. I am facing two problems:

Is storing this as a 19100 columns X 19100 rows table feasible (seems wrong in my gut) a viable option?
Since A->B is the same as B->A I can store maybe reduce it to a 9550 X 9550 elements table, which reduces the problem but still seems like optimisation of an incorrect approach.

[ 1 & 2 seem to be non starters as I am told max columns is 1300 in a table]

3.Approach three is storing the distance as key/value pairs. For example to store distance between 400703 and 400705, we store it as

{"400703400705":15"}

Is this the only option?

What's the best way to store this? It will be a write once operation, all future uses will be read only, if that helps in any way. Is there a better approach? (I cannot use Haversine distance formula because it is driving distance I need, not geographical distance)

I would create a table with three columns:

create table distances
(
  from_zip_code int not null,
  to_zip_code int not null,
  distance numeric not null,
  primary key (from_zip_code, to_zip_code)
);

If you want, you can prevent storing the reversed combination through a unique index.

If your ZIP codes aren't numbers, then obviously you need to use text or varchar for those two columns.

While the table is moderately large (9550 * 9550 rows), access through the primary key is typically pretty much constant in time.


MySQL - identifying bottlenecks in complex queries I apologize if this question is too general, I can post and example code, but it may not be reproducible because there is no access to the actual database.

Suppose I have a big MySQL query with a lot of joins and unions, as well as functions like concat, date, various time and date conversion functions. And it uses a lot of tables, nested select queries etc. Lets suppose it is a select query.

My questions is, where would one start, if they need to optimize this script to run faster? Currently its taking hours to complete. Furthermore, If I run stand-alone chunks of code from it (including some nested queries, etc.) they run much faster. Therefore there are one or a few bottlenecks.

I am aware of profiling and bench-marking as well as explain functionality in MySQL they all help us to understand what MySQL does behind the scenes, but all provide the summary for the entire script overall. What would be the best way to identify these bottlenecks without profiling each each portion of the script separately? Are there best practices when faced with such problem?

Again, I apologize for asking a question that may be too broad. I can post and example code, but it may not be reproducible because there is no access to the actual database.

The MySQL documentation includes a section with general query tuning advice. However, if you read that, you'll see that it offers little in terms of specific advice.

That's because there aren't a lot of things that you can do that make every query you write better.

There are a few things you can do, though:

Develop an understanding of how the optimizer works: Look for articles that talk about query tuning in your DBMS of choice, and learn the things that most often result in sub-optimal query plans. Similarly, develop an understanding of your DBMS' query plans: how to see them, how to follow them, and what they mean.
Keep searches SARGable: That means making sure that your query will use indexes wherever possible. The biggest rule to follow for this is to avoid comparisons that require applying functions to your columns in comparisons. If the engine has to apply a function to the data from your table, a standard index can't be used (because the values in it aren't the values being compared).

If you can, write comparisons so the side with the most rows, with an index on the column involved, doesn't have a function applied on it. Apply a more complicated function to a column with fewer rows to worry about, if a function must be involved. Whenever possible, make sure that values to be compared to each other are of the same data type, and are using the same units, so they can be compared directly without needing functions.
Follow maintenance standards: While following the first step in my advice, you may notice some trends in what causes poor query plans. in SQL Server, I've noticed that one of the sings that a plan may be off is when the optimizer's estimate of the number of rows a particular step will return is far off from the actual number of rows returned. One of the most common reasons for these estimates to be off is if SQL Server's statistics (which help it determine the "cardinality" of a piece of a query - how many rows it's likely to bring back. If you've only got 100 rows from one table, the most effective way to join it to another table may be completely different than if you've got 1 million rows.

In SQL Server, these estimates are usually much better if you regularly run a job to update the statistics on your tables. Watch for things like this in MySQL, and make sure you're running the appropriate maintenance jobs as often as you need to.
Avoid pre-emptive "optimization": Some of the tricks used to force queries to perform better involve forcing the query to operate in a specific way; for instance, using hints to force a particular method to be used when joining table, or to use a specific index. In some circumstances, this sort of thing can help; however, whenever you use one of these, you limit the optimizer's options when figuring out the best way to run the query.

If you have a column in your table where there are 100 distinct values, but 90% or the rows all have 1 value, and the other 99 values are split amongst the other 105 of the rows. If you search for a value from that table (using a variable to pass in the value), then a run where the variable is set to the most common value, the optimizer isn't likely to use an index on that column; the value doesn't do a good job of narrowing down the number of rows the query has to deal with. If any of the other values is used, then the index might be useful, because it would tend to limit the number of rows fairly effectively.

If you force the query to always use an index on this column, then it will likely work well any time the value is one of the rare ones, but relatively poorly if the value is the most common one.
Be flexible: Keep learning, and try not to assume you know everything. Most DBMSes receive fairly regular updates, and these can change the rules. Know what works now, but keep an eye out for new articles, and new questions, to see if some new strategy has been added to the toolkit, or replaced an old one.

An example, again from the SQL Server world: for years, it was standard advice that you should regularly defragment indexes to keep performance good. While this isn't bad advice, in the past couple of years more and more people are saying that defragmenting your indexes isn't as necessary as they used to think. You see, when an index is defragmented, its statistics are normally updated as well; and, it seems that the updated statistics (helping provide better query results) are probably more important that the actual defragmenting; and, are less intrusive. A case where the old advice has been superseded by new advice (even for older version of the software).

Generally, assume the optimizer will do its job well 99%+ of the time; don't limit its options unless you see a real reason to do so; and then only after you've tried other ways to optimize the query. You don't know exactly how any query will perform until it's performing in a production environment. Write queries as wisely as you can, but try to start with a query you (and others who may not even be on your team now) can maintain in general. Only twist a query around in weird ways to improve performance when it's been proven to be necessary.


What does Page Life Expectancy say about the instance? I have installed monitoring software on a few SQL Server instances in the environment. I am trying to find bottlenecks and fix some performance issues. I want to find out if some servers need more memory.

I am interested in one counter: page life expectancy. It looks different on every machine. Why does it change often on some instances and what does it mean?

Please take a look at the data from the last week gathered on a few different machines. What can you say about each instance?

Heavily used production instance (1):

Moderately used production istance (2)

Rarely used test instance (3)

Heavily used production instance (4)

Moderately used test instance (5)

Heavily used data warehouse (6)

EDIT: Im adding the output of
    SELECT @@VERSION
for all of these servers:

Instance 1: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 2: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 3: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 4: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 5: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 6: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

I also ran the following query on the machines:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

and it returned 2 or 3 rows for each server:

Instance 1: 0; 64; 1
Instance 2: 0; 64
Instance 3: 0; 64
Instance 4: 0; 64
Instance 5: 0; 64
Instance 6: 0; 64; 1

What does it mean? Do these servers run NUMA?

Taken from MSDN:- https://msdn.microsoft.com/en-us/library/ms189628.aspx

Page life expectancy - Indicates the number of seconds a page will stay in the buffer pool without references.

SQL always looks for data pages in memory. If a data page is not in memory SQL will have to go to disk (perform a physical IO operation) in order to retrieve the data that it needs to fulfil a request. If your PLE counter is low that indicates that data pages in memory are regularly been overwritten with new pages coming from physical IO operations. Physical IO operations are expensive meaning that the performance of your SQL instance will be adversely affected. So you will want your PLE counter to be as high as possible.

Ignore any advice that you see online that mentions 300 as a good threshold for this counter

This threshold comes from the days when memory was limited (think 32-bit systems). Now we have 64-bit systems which can have TBs of RAM so this advice is very out of date.

First thing, have you limited SQL's memory? If so, how much available memory is left? Can the limit be increased?

Second thing I would be looking for on your servers is, are there any maintenance jobs running? Check for jobs performing index rebuilds, update stats or DBCC CHECKDB operations. These perform a large amount of reads and could be the reason for your PLE flat lining,

Next, as you're using SQL Server 2008 +, you can setup an Extended Event session to capture queries coming in that are performing a large amount of reads. Here's the code to do so:-

CREATE EVENT SESSION [QueriesWithHighLogicalReads] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
   ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
     WHERE ([logical_reads]>200000))
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\QueriesWithHighLogicalReads.xel')
GO

This will capture all queries on your server that perform over 200000 logical reads. I don't know how much memory you have on each server so you may want to tweak that figure. Once this has been created you can start the session by running:-

ALTER EVENT SESSION [QueriesWithHighLogicalReads]
ON SERVER
STATE = START;
GO

And then query the session by running:-

WITH CTE_ExecutedSQLStatements AS
(SELECT
[XML Data],
[XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME')    AS [Time],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','int')                        AS [Duration],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int')                        AS [CPU],
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int')                   AS [logical_reads],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int')                  AS [physical_reads],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [SQL Statement]
FROM
    (SELECT
    OBJECT_NAME              AS [Event],
    CONVERT(XML, event_data) AS [XML Data]
FROM
    sys.fn_xe_file_target_read_file
('C:\SQLServer\XEvents\QueriesWithHighLogicalReads*.xel',NULL,NULL,NULL)) as v)

SELECT
[SQL Statement]     AS [SQL Statement],
SUM(Duration)       AS [Total Duration],
SUM(CPU)            AS [Total CPU],
SUM(Logical_Reads)  AS [Total Logical Reads],
SUM(Physical_Reads) AS [Total Physical Reads]
FROM
CTE_ExecutedSQLStatements
GROUP BY
[SQL Statement]
ORDER BY
[Total Logical Reads] DESC
GO

Be careful when running this! The file can grow quite large in size so test it out on a development instance first. You can set the max. size of the file but I haven't included that here. Here's the MSDN link for Extended Events:- https://msdn.microsoft.com/en-us/library/hh213147.aspx

Monitor this session routinely and hopefully it should pick up any queries coming in that are flat lining your PLE.

Further reading -

MSDN blog on PLE - http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

Video on setting up Extended Events - https://dbafromthecold.wordpress.com/2014/12/05/video-identifying-large-queries-using-extended-events/
(It's from my own blog so sorry about the shameless self promotion)


sp_executesql adds statements to executed dynamic script? The Question:
As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does DBCC OUTPUTBUFFER. So:

Is there any way to see the extra statements added to submitted dynamic SQL batches by sp_executesql?
Can anyone confirm definitively that my conclusions about the extra statements are correct/incorrect?

Background
I have a database where some objects (views, synonyms, SPs) are rewritten based on data in a Script table. If the database is moved to another server, a stored procedure loops through the rows of the Script table, replaces certain key values in the supplied SQL script with those defined for the new server context, and runs the script.
Everything was working fine until I made a few tweaks to add support for scripting permissions through this same mechanism. The database integrates with a vendor's product, and in each environment the vendor's database can have a different user that must be given permission to a particular view in my database for reporting purposes. So, I have to query for that user (from the vendor's database) then use that name to create the user in my database if it doesn't exist and finally grant SELECT permission. This required more lengthy scripting and doing dynamic-sql inside of dynamic-sql, so I wanted to pass in my outer script's @Debug parameter so I could see the extra script that was being generated and confirm its correctness before trying to execute it.
Other than changing what object types could be scripted and making the DROP script optional, the only material change I made to accommodate the @Debug parameter was to change this:
EXEC (@CreateSQL);

to this:
EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;

Then I ran into a problem: the one stored procedure in my Script table could no longer be created, though the DROP just before it still worked okay. The result I got was this:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.

This was very confusing, but after wrangling with it for quite some time, I finally figured out the problem: sp_executesql binds parameters to dynamic SQL by secretly adding a DECLARE statement to the top before executing. Since CREATE PROCEDURE must be the only statement in the batch, but there is now an extra statement before the CREATE PROCEDURE line, it throws an error. It does say Line 1--which further misled me--but this is obviously tweaked by the engine so people don't get confused about the line numbers of their own script when dealing with errors.
The solution to the problem was to detect which type of object was being worked with and NOT pass in the @Debug parameter so script that must have no other statements works okay. A quick change did the job:
IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN
   EXEC sp_executesql @CreateSQL;
END
ELSE BEGIN
   EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
END;

I could also have nested my dynamic SQL one level deeper, to create the procedure inside dynamic sql (again, inside the script in the table) but that was a less optimal solution in my case.
I suspect that using OUTPUT variables with sp_executesql would also add one or more statements to the end of the script to enable the engine to capture them, most likely in a SELECT statement that is silently swallowed up.

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx


Does BEGIN TRAN guarantee atomicity? I have a situation where I want to insert records in both a master and details table, but only under certain condition. So when I insert, I do something like this:

set transaction isolation level serializable
BEGIN TRAN

INSERT INTO master (CustomerId, TypeOfTransAction, ...)
SELECT @customerId, 'TEST', ...
WHERE (SELECT SUM(amount) FROM details WHERE customerId = @customerId) < 1000

IF SCOPE_IDENTITY() IS NULL
    RAISERROR('can''t insert now, there''s too many', 16, 1)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

...

COMMIT TRAN

Does this guarantee no more rows will be inserted into the master table (from other connections) until it has finished inserting all rows into the details table?

You have a few different questions in here:

Q: What is atomicity?

Atomicity means your transaction will completely succeed, or completely fail. At the end of your transaction, when you run COMMIT, all of the work should roll forward together in one atomic unit. If, on the other hand, you run ROLLBACK, it should all roll back together.

Q: Does atomicity mean an insert query locks a table?

No. Atomicity is only about the work involved in your own transaction, not others.

Q: If I want to stop other people from touching tables while I work, how do I do it?

You need to explicitly specify that as part of your query, like by using the SERIALIZABLE isolation level. That's generally not a good idea, though: that's the opposite of high performance. You want to write your code in a way that expects other people to be working in the database at the same time as you are.

That concept - why you should avoid exclusive/serializable locks on objects - is a pretty big one to try to tackle in a single answer, though.


What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015? I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to understand what I exactly need to perform this, as the more I read, the more confused I feel about all the tools I am supposed to install.

Here what I have so far:

The SQL Server 2017 DB is on a server. I can access the DB through Microsoft SQL Manager. Also, I have access to the server itself through remote connection but no permission to install whatever I want. However, I guess I could ask for any installation if it was mandatory.
The Oracle DB is on another server. I do not have access to the server itself. I have access to the DB through SQL Developer Tool. I access to the DB using EZ Connect, not a TNS.
On my PC, I have the following tools installed:

Visual Studio 2015
Visual Studio extension Microsoft SQL data tool, in order to develop the SSIS
Visual Studio extension Oracle Developer Tools in order to connect to the Oracle DB through Visual Studio

I can already develop a SSIS and transfer some data from my PC to the SQL server DB. However, I am not able to connect to the Oracle DB through the SSIS.
I am pretty sure there are plenty of details that I am missing so I would be grateful to whoever that would give me any advice.

There are two ways to synchronize data or extract database from one (source) to another (target) database, based on what is a business requirement.
If you need to extract raw data from Oracle to SQL Server, then you can use Transactional Replication which helps you to replicate tables and its data from Oracle to SQL Server.
ETL/SSIS can be used for the same purpose, but it's for more than just replication raw data. It also helps you to extract data from the source, analyze it, manipulate it and load to target.
To configure Transaction Replication for Oracle (as source):

Both databases should be accessible on respective servers.
Oracle Client should be installed on the SQL Database Server.
Configure listener on SQL DB server. The listener It is used while we configure replication.

Rest of the steps are mentioned here.
Thanks


Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them? I was looking though some queries in MS Access, and I found an SQL keyword I was unfamilar with called DISTINCTROW, from what I can tell, it's the same as an SQL GROUP BY which includes all the fields, but I'm not entirely certain about that.  Is this the case, or is there some small difference?

Taken from the MSDN page:

  Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

  If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

  DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

To answer your question, is it the same as a GROUP BY on all columns?  No.

By the way, that was the very first link returned from a search for microsoft access distinctrow site:msdn.microsoft.com


InnoDB: Error: pthread_create returned 12 have installed MySQL in a particular folder. I was able to run the server and create accounts, databases etc. However, now whenever I try to start the server, I get an error:

$ mysqld_safe --defaults-file=mysql.cnf &
[1] 2002
[compute-0-5 /amber2/scratch/myname/mysql]$ 130725 17:56:24 mysqld_safe Logging to '/amber2/scratch/myname/mysql/data/compute-0-5.local.err'.
130725 17:56:24 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
130725 17:56:25 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-5.local.pid ended

[1]+  Done                    mysqld_safe --defaults-file=mysql.cnf

In the error file inside the data folder:

130725 17:17:53 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
2013-07-25 17:17:54 0 [Warning] option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147479552
2013-07-25 17:17:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2013-07-25 17:17:54 28189 [Note] Plugin 'FEDERATED' is disabled.
2013-07-25 17:17:54 28189 [Note] InnoDB: The InnoDB memory heap is disabled
2013-07-25 17:17:54 28189 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-07-25 17:17:54 28189 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-07-25 17:17:54 28189 [Note] InnoDB: Using Linux native AIO
2013-07-25 17:17:54 28189 [Note] InnoDB: Not using CPU crc32 instructions
2013-07-25 17:17:54 28189 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-07-25 17:17:54 28189 [Note] InnoDB: Completed initialization of buffer pool
2013-07-25 17:17:54 28189 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Error: pthread_create returned 12
130725 17:17:55 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-18.local.pid ended

But why am I getting the pthread_create error 12? It seems this is related to not enough space. On the device where the mysql folder (/amnber2//scratch/myname/mysql) resides, I do have space:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              49G  5.0G   41G  11% /
        ...
10.255.255.46:/export/scratch
                       15T   11T  4.2T  72% /amber2/scratch

I also have a few gigs in my home directory quota

$ quota -v
Disk quotas for user myname (uid 41222):
     Filesystem  blocks   quota   limit   grace   files   quota   limit   grace
10.255.255.45:/export/ncms
                22986221  26214400 26214400               0       0       0
10.255.255.46:/export/scratch
                7321108       0       0               0       0       0

i.e., I am using 22G out of 25G:

$ du -sh ~/.
22G /home/ncms/myname/.

I also have free memory:

$ free -mg
             total       used       free     shared    buffers     cached
Mem:            62         41         21          0          0         28
-/+ buffers/cache:         12         49
Swap:           64         13         51

In my mysql configuration file:

myisam_sort_buffer_size=4G
myisam_max_sort_file_size=200G
read_buffer_size=2G

So why am I getting the error pthread_create error while starting the server?

$ /usr/local/mysql/bin/perror 12
OS error code  12:  Cannot allocate memory

Sure, you have memory now but MySQL isn't running now.

Remove those multi-gigabyte configuration entries and verify that you can start the server without them.  I suspect read_buffer_size may be the one that's getting you but they are all unnecessarily large for a new server.  The warnings you're seeing for max_open_files and table_cache suggest that you are trying to tweak MySQL for performance by setting large values elsewhere in your config, too.

That's not going to do what you expect.  When it comes to buffer and cache sizes, more is not always better and often it is worse.  Comment out your custom settings and start with the defaults unless you have a specific reason not to... and when you do make changes, don't change more than one thing at a time unless the changes are directly related to each other.


Select products and include all cities it belong to I have 2 tables :

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+

CITIES
+----------+----------+
| id       |  title   |
+----------+----------+

Where in my products table data stored like : (cities that a product belongs to separated by comma)

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+
| 1        |  some    |  info    | 1,3,4,15

Now , how can i select products and all cities that it belongs to. And i want to search products by city's id too.

I've read some topics also this one , but mine is a bit different

I have a function that I keep on the master database for these cases.
I will start by creating my function as per code below:

use master
go

create function dbo.getCities( @thecities varchar (100))
returns table
as
return

WITH    cteTally
      AS (SELECT TOP 100
            ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
          FROM
            sys.all_columns AS AC CROSS JOIN
            sys.all_columns AS AC2  )

,RADHE2 (THECITIES)AS (
    SELECT
        LTRIM(SUBSTRING(','+@THECITIES+',',N+1,CHARINDEX(',',','+@THECITIES+',',N+1)-N-1)) AS THE_CITIES
    FROM
        cteTally C
    WHERE
        -- need the - 1 because cteTally strts with 1
        n < LEN(',' + @THECITIES + ',') AND
        SUBSTRING(',' + @THECITIES + ',', n, 1) = ','

)

SELECT * FROM RADHE2
GO

then I will create my tables according to your question.
I did not bother about indexes here, but you shoudl have a look at this, because this process is bound to be very process intensive.

SET NOCOUNT ON

DECLARE @PRODUCT TABLE (ID INT,
                    TITLE VARCHAR(100),
                    CONTENT VARCHAR(100),
                    CITY VARCHAR(100))

DECLARE @CITIES TABLE (ID INT,
                   TITLE VARCHAR(100))

INSERT INTO @PRODUCT VALUES (1,'PROD1', 'BANANAS', '1,2,4')
INSERT INTO @PRODUCT VALUES (2,'PROD2', 'APPLES', '3')
INSERT INTO @PRODUCT VALUES (3,'PROD3', 'STRAWBERRIES', '3,5')
INSERT INTO @PRODUCT VALUES (4,'PROD4', 'MANGO', '1,2,4')
INSERT INTO @PRODUCT VALUES (5,'PROD5', 'PEAR', '2,4')

INSERT INTO @CITIES VALUES (1,'CARACAS')
INSERT INTO @CITIES VALUES (2,'RIO')
INSERT INTO @CITIES VALUES (3,'TALLINN')
INSERT INTO @CITIES VALUES (4,'LIMA')
INSERT INTO @CITIES VALUES (5,'MOSCOW')

this gives me the following tables:

 select * from @PRODUCT
 select * from @CITIES

then, the final step, I use CROSS APPLY with the function, as you can see on the code below:

SELECT P.*
      ,C.TITLE
FROM @PRODUCT P
CROSS APPLY MASTER.dbo.getCities( p.CITY) R
INNER JOIN @CITIES C ON R.THECITIES = C.ID
order by p.ID

and that gives the following result, which I believe is what you are looking for:


Always ON synchronization issue I'm having a bad issue with Always ON on a SQL Server 2017 CU3 running on Windows Server 2016 with last cumulative updates (March 2018).
Availability Mode is set to Synchronous commit and when I reboot the secondary node some DBs (randomly) get in a "Not Synchronized" state and they can't go back to "Synchronized".
Everything looks OK, I have another SQL installation with same patching level and Os and it's working fine.
I don't have specific errors on the event viewer. Antivirus is disabled, the nodes are on the same network and they look ok.
I tried to raise the session timeout to 20s but this didn't solve the issue.
Do you have any idea or suggestion?

I had the same issue on SQL Server 2017 CU 11 (latest as of today)  with Windows Server 2016. After rebooting the secondary replica, some of the databases in the AG show not synchronizing. I used ALTER database SET HARD RESUME, but it didn't work. The work around is to restart the SQL Server Services on the secondary replica. Then all databases show synchronized again.


Recover database from uninstalled SQL Server My SQL Server was accidentally uninstalled. I didn't take a backup of all the databases. Is it possible to recover them?

The database files are probably still there - in the directory for data and log files.

Th default one is c:\ProgramFiles\MSSQL\MSSQL\MSSQL10_50\MSSQL\Data.
If not default then you need to locate your files and attach them to SQL Server instance of the same or higher edition.


Get Table Name from Transaction Log query I can't figure out why this join isn't working to return the table name when querying the transaction log file.

SELECT [Transaction ID],
    [Current LSN],
    PartitionId,
    sp.object_id,
    so.[name],
    [Operation],
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN sys.partitions sp ON sp.partition_id = logF.PartitionId
    LEFT OUTER JOIN sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

And here are the results. You can see the partition Id is populated:

And when I execute this query to get the table name, it works using that partition id found in the prior results:

SELECT so.*
FROM sys.objects so
INNER JOIN sys.partitions sp on so.object_id = sp.object_id
WHERE sp.partition_id = 72057594059489280

So why can't it find the table name when I join in the original statement above?

You need to ensure you are running the query in the context of the correct database.

Since system views such as sys.objects are context sensitive, they only return rows related to the current database.

For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect.

Assuming your database name is OTIS you could work around this by adding the database name into your join clause, such as JOIN OTIS.sys.partitions and JOIN OTIS.sys.objects.

Since you've hard-coded the name of the log file itself, something like this should work more reliably:

SELECT [Transaction ID],
    [Current LSN],
    PartitionId,
    sp.object_id,
    so.[name],
    [Operation],
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN OTIS.sys.partitions sp ON sp.partition_id = logF.PartitionId
    LEFT OUTER JOIN OTIS.sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

Alternately, you could simply place USE OTIS; prior to your select statement, so it executes first.


Can we enable alwayson feature of sql 2012 in windows 8.1? I want to enable "AlwaysOn" feature of MS SQL Server 2012 in Windows 8.1

Is it possible to do so?

Short answer is, No.

To enable Sql Server AlwaysOn, the server

  The server instance must reside on a Windows Server Failover Clustering (WSFC) node. Enable and Disable AlwaysOn Availability Groups, MSDN

Server Clustering is only available on Windows Server OS (i.e. Not 8.1)
Windows Server Clustering

You might want to see Overview of AlwaysOn Availability Groups (SQL Server)

Anticipating further questions, you might want to have a look at other disaster recovery options that you might be able to use: Description of disaster recovery options for Microsoft SQL Server
Something like log shipping might be the most feasible and easy to set up (assuming you have limited resources, and that's why you have SQL Server installed on Windows 8.1 and not a server)

Edit
Without lingering on this too much, for the sake of completeness I'll point out there is a difference between AlwaysOn Availability groups and SQL Server Failover Cluster Instances, discussed here, the former only available on Enterprise edition. Still both dependant on WSFC though so it doesn't make a difference for you.


Is there a way to optimize this using indexes? I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds.

SELECT
    C.yearID as year,
    name as teamName,
    C.lgID as league,
    D.cnt as totalBatters,
    C.cnt as aboveAverageBatters
FROM
    (SELECT
        count(masterID) as cnt, A.yearID, A.teamID, A.lgID
    FROM
        (select
        masterID,
            teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    GROUP BY teamID , yearID , lgID, masterid
    HAVING sum(AB) <> 0) B, (select
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C,
    (SELECT
        count(masterID) as cnt, yearID, teamID, lgID
    FROM
        batting
    GROUP BY yearID , teamID , lgID) D,
    teams
WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

I've tried creating indexes for teamid, yearid, lgid, and ab, but performance is the same.

Please help!

Generally indexes will stop working when you run "top" select over a nested select, like:

(select
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C

OR

   (SELECT
        count(masterID) as cnt, yearID, teamID, lgID
   FROM
        batting
   GROUP BY yearID , teamID , lgID) D

You define derived tables C (as well as D etc.). Also your derived tables have GROUP and FUNCTION inside.

When you make the comparison:

WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

it runs over new "tables" and these new tables do not have indexes.