Mysterious procedure calls without parameters - but no exceptions generated I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName".

If I manually call them as they appear in the trace's TextData, they generate an exception - but no exceptions are being generated in the original trace.  The Error column for these "parameterless" calls does show "1 - Error," but I don't understand how the call is not generating an exception.

At first I suspected it was a glitch with profiler, that "1 - Error" caused parameters to be truncated from the TextData, but I see the same results in an extended events session.

Has anyone seen anything like this in their profiler traces or event sessions?  The app is almost certainly making these calls as part of some edge-case error, but why is sql server not throwing an exception?

SET PARSEONLY ON will do this.  FMTONLY will generate an error.

EG:

USE [AdventureWorks]
GO

set parseonly on
go
exec [uspGetBillOfMaterials]
go
set parseonly off


Method or Counter to track dropped connections in SQL Server? Our production server is running SQL Server 2008 (non r2).

I'm working on investigating and issue that appears to be networking or connection related, where our web server is losing or dropping connections to the database server.

It would assist me greatly to have a method or counter that I can use to track DROPPED connections to the SQL Server. Specifically, we want to know if the ODBC/ADO connection was forcibly closed by the CLIENT.

Conceivably we could also track when a query was cancelled by the client, which may give us the same information.

From my own research I don't see a way to track this with perfmon counters, but I'm not that familiar with them.

To give information on the performance counter side...

The only performance counters I know of that might help would be the ODBC counters for disconnects. SQL Server has a counter for killed connections but those are connections killed by SQL Server, not the client.

You would have to capture these at the client which you could use typeperf command to do this remotely. SimpleTalk has a good article about using this command.

The comment suggestion would be the more human way of doing things.


Composite indexes: Most selective column first? I’ve been reading about composite indexes and I’m slightly confused about ordering. This documentation (little less than half way down) says

  In general, you should put the column expected to be used most often first in the index.

However, shortly after it says

  create a composite index putting the most selective column first; that is, the column with the most values.

Oracle also says it here in other words

  If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.

However, I have found a SO answer that says differently. It says

  Arrange the columns with the least selective column first and the most selective column last. In the case of a tie lead with the column which is more likely to be used on its own.

The first documentation I referenced says that you should first go by the most often used whereas the SO answer says that should only be for tie breaking. Then they also differ on the ordering.

This documentation also talks about skip scanning and says

  Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Another article says

  The prefix column should be the most discriminating and the most widely used in queries

which I believe most discriminating would mean most distinctive.

All of this research still leads me to the same question; should The most selective column be first or last? Should the first column be the most used and only the most selective on a tie-break?

These articles seem to be contradicting each other, but they do offer some examples. From what I have gathered, it seems to be more efficient for the least selective column to be the first in the ordering if you are anticipating Index Skip Scans. But I'm not really sure if that is correct.

From AskTom

  (in 9i, there is a new "index skip scan" -- search for that there to read about that. It makes the index (a,b) OR (b,a) useful in both of the above cases sometimes!)

  So, the order of columns in your index depends on HOW YOUR QUERIES are written. You want to be able to use the index for as many queries as you can (so as to cut down on the over all number of indexes you have) -- that will drive the order of the columns. Nothing else (selectivity of a or b does not count at all).

One of the arguments for arranging columns in the composite index in order from the least discriminating(less distinct values) to the most discriminating(more distinct values) is for index key compression.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx_1 on t(owner,object_type,object_name);

Index created.

SQL> create index t_idx_2 on t(object_name,object_type,owner);

Index created.

SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name ), count(*)  from t;

COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)      COUNT(*)
-------------------- -------------------------- --------------------------      ----------
                 30                         45                       52205      89807

SQL> analyze index t_idx_1 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          2           28

SQL> analyze index t_idx_2 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave  from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          1           14

According to the index statistics, the first index is more compressible.

Another is how the index is used in your queries.
If your queries mostly use col1,

For example, if you have queries like-
select * from t where col1 = :a and col2 = :b;
select * from t where col1 = :a;

-then index(col1,col2) would perform better.

If your queries mostly use col2,
select * from t where col1 = :a and col2 = :b;
select * from t where col2 = :b;

-then index(col2,col1) would perform better.
If all of your queries always specify both columns then it doesn't matter which column come first in the composite index.

In conclusion,
the key considerations in column ordering of composite index are index key compression and how you are going to use this index in your queries.

References:
Column order in Index
It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right) ?
Index Skip Scan – Does Index Column Order Matter Any More ? (Warning Sign)


Scheduling a backup in SQL Server 2005 I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected area. What can be the reason?

This link may help you to back up the data


High rise in SQL Server CPU after adding a DB I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% connections on 1600, after restoring the DB from my side and changing the connection string so that the site will work with the active server hell has broken loose, my connections have stayed on the avg. 1600 but my CPU spiked to an avg. of 70%.
I thought ok, I have some tuning problems, so I have rebuilt all the indexes, and started a profiler on the "new" DB, but alas I was wrong on an avg. hour had 100 SP runs and all of them with less than 50 ms CPU and the highest number of reads were 2.2k (no writes and duration less the a sec for most, highest duration 3 sec).

I would love to get directions on what should I check next, SQL Server side or Web side.

Read this article from Paul Randal on wait stats. You find what sql server is waiting on using the sys.dm_os_wait_stats DMV(Data Management View). It sounds like it MIGHT be a query plan change between versions. But there are a lot of things it could be. Could be storage too. Wait stats should help. Check to see if the MAXDOP setting was different between the versions if possible, you stated the original server died, but not sure if you have the server config saved in a repository for DR purposes. If it's a highly transactional system, parallelism can slow you down. Make sure you know what you are doing before making any changes to MAXDOP.
Also check this article from this thread on SO , it will help you find the worst performing queries. And for more information, check Brent Ozar's Blitz scipt
Hope this helps, Good luck.
Chris


Can the number of tables and functions affect performance? I have some big database with hundreds of functions and tables.
They also have a lot of sequences, custom types and some views and triggers.

There is a point when the quantity of objects will be a problem? Affecting for example parsing?

Did you ever notice some kind of impact for these reasons?

It's a gradual process and the effect is comparatively small. But of course, looking up entries in system tables gets slower with lots of rows. Those are just regular tables. Highly optimized, but regular tables. There are indexes to keep the effect small.

I have never actually noticed an effect in my biggest database with a couple of hundred tables and close to 1000 functions. Sequences, views, triggers .. everything. Your DB seems to have similar numbers - I'd guess you cannot report any observations yourself? Or you would surely have added that in your question.

The cost for other things like backup and VACUUM rises, too, obviously.


SQL Server 2012 CPU core usage Having some trouble figuring out how to alter the core usage on SQL Server 2012 Enterprise. On a server with 16 cores, is there any way to alter which ones will actively use the SQL database, and not on others?

In SQL Server Management Studio, rt-click on your instance and select Properties. Then select Processors. Uncheck "Automatically set..." and set CPU affinity how you would like.


Better way to store file receipts I have a financial system that needs to attach PDF receipts for each record saved on my system.

These receipts will be scanned by a proper device attached to the computer and saved in PDF to be stored in my database.

Today the system saves the PDF file as varbinary(max), but because of the number of rows in my table, the filesize of my DB is increasing too fast. The average file size is about 1 to 2 MB.

What is the best way to store these kind of files without compromising my database performance?

You are not sacrificing performance as in database performance using varbinary(MAX). You are not searching on the varbinary(MAX). You are not indexing the column.

What is nice about in the table is single consistent backup. The down side is a bigger backup.

Delivering the file to the client varbinary(MAX) is going to be less efficient than FILESTEAM or file system.

1 - 2 mb is relatively small.  If it was 200+ mb then you typically would want to avoid storing in varbinary(MAX).

If table size alone is problem then FILESTREAM is probably your best solution. This will not hurt database performance and files are not stored in a table.

  FILESTREAM enables SQL Server-based applications to store unstructured
  data, such as documents and images, on the file system. Applications
  can leverage the rich streaming APIs and performance of the file
  system and at the same time maintain transactional consistency between
  the unstructured data and corresponding structured data.

From a licensing perspective I am pretty sure FILESTREAM does not count as database size.  Like for EXPRESS with a 10GB database limit the FILESTREAM does not count.

Another benefit is if you have OCR on the PDF with an Adobe iFilter you can Full Text search the document.  But you may be able to do that with FILESTREAM also - from what I see no.

Or you can just manage the file totally separate and only store a path in SQL.


How to recreate a SQL Server audit without an audit gap? I have some audits and audit specifications on a SQL Server 2012 instance and I want to reapply the audit and audit spec definition every evening in case the DBAs change it. I have measured the time taken to do this on one of my Dev boxes, and it's quite short, but there's still a window there where some audit data could get lost. From a quick test I did to measure the time taken:

AuditRecreate_ms
----------------
23

(1 row(s) affected)

AuditSpecRecreate_ms
--------------------
16

(1 row(s) affected)

It's possible with SQLTrace because you can create a new trace and then delete the old one, and no audit data gets lost. I'm trying to move away from SQL Trace but retain an uninterrupted audit but at the same time make sure the audit definitions do not change. Any ideas here?

I want to reapply the audit and audit spec definition every evening in case the DBAs change it

Engaging in an arms race against the rightful admins is only going to cause delay and confusion.

Explain to DBAs the role and purpose of the audit, ask them not to change it
Use the audit itself to detect tampering
Follow up upon detecting tampering with administrativia to get the responsible party questioned/rewarded/punished depending on situation

If you cannot enforce above, it means by definition that you are not be able to enforce an audit, so why bother?

As for specifically the question, this advice applies always, to any situation: do not blindly re-apply a template 'in case it changed', only apply it if it had changed.

Ask your DBAs to help you, do not ask us to help you against your DBAs.


Recommended memory configuration for many SQL Server instances per server I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size.

Currently, each instance was installed without setting memory limits, but what we have found is that the first instances tend to use too much memory, leaving later instances (in starting order) with barely 200MB to work with, and the server OS with less than 1% physical memory available. This seems to cause excessive disk swapping, and latency issues.

What is the recommended way to split memory allocation in this case? Is there a formula to determine approximately how much memory one instance needs as a minimum from the number of clients and database size? Could I set a maximum of 300MB per instance and be done with it?

2018-12-13 Final solution

I have finally figured out how to configure everything where it seems very stable and is completely symmetric across all instance, and since it took some digging and doing I thought I should share the solution I came to. This is probably not for everyone, but it seems to be the best solution to me.

The key is in this API: https://docs.microsoft.com/en-us/windows/desktop/api/memoryapi/nf-memoryapi-setprocessworkingsetsizeex

This allows you to set a hard limit on the minimum and maximum working set size on a per process basis. This seems to be the cleanest to me, as it allows me to tell SQL server to use as much memory as it wants WITHIN this working set size, and it seems to optimize adequately by itself instead of running at capacity and trying to hog more memory all the time. Each instance will swap more overall, but since the usage is sporadic for each instance it seems to work just fine, and if it ever becomes a problem I can provision a few swap partitions off an SSD in the server rack as swap space for each server. I set the working set size to 300MB for each instance through a powershell script when each instance launches, and the minimal OS always has 1GB to work with, and everyone seems happy enough.

Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack.
We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size.

IMHO, your total RAM is too low. Please read my answer (with relevant links) SQL Server Maximum and Minimum memory configuration. They change when you have multiple instances of sql server running on a given host.
Capping SQL server max memory on a multi instance server is a balancing act and max memory is applicable to only buffer pool. If sql server needs more memory, it is going to use it.
You can even use Lock Pages in Memory (I would still opt for more memory before enabling LPM in your case).
As a starting point,

Baseline your instances. This will help you gauge what is good / acceptable for your workload.

Use OptimizeInstanceMemory script from Aaron's blog to help you get started. The blog post covers how to balance max memory dynamically when failover happens.

As a side note, you should monitor CPU, memory and disk utilization and based on the usage per client, you should charge them as well. Alternatively, you can move to Azure :-)


From where does the MySQL Query Optimizer read index statistics? I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.

The direct answer for this would be

information_schema.statistics

mysql> desc information_schema.statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

You could SELECT from that table with

SELECT * FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='mytable';

or see the statistics by doing

SHOW INDEXES FROM mydb.mytable;

Please keep in mind that this table is not always accurate in a write-heavy environment. Periodically you will have to run ANALYZE TABLE against all MyISAM tables that are updated frequently.  Otherwise, the MySQL Query Optimizer, which relies on information_schema.statistics, can sometimes make bad choices when developing EXPLAIN plans for queries. Index statistics must be as up-to-date as possible.

ANALYZE TABLE has ABSOLUTELY NO EFFECT against InnoDB tables. All index statistics for InnoDB are computed on demand by means of dives into the BTREE pages. Therefore, when you run SHOW INDEXES FROM against an InnoDB table, the cardinalities displayed are always approximations.

UPDATE 2011-06-21 12:17 EDT

For clarification of ANALYZE TABLE, let me rephrase. Running ANALYZE TABLE on InnoDB tables is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that conclusion as well.


How to ensure that SQL Jobs run in the correct order within a minimal maintenance window? From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files.  According to the documentation, this should happen in a particular order:

  Integrity check after index optimization. This is because index rebuilds sometimes can fix database corruption. Full backup after index optimization. Then the following differential backups will be small. Full backup after the integrity check. Then you know that the integrity of the backup is ok. This means first index optimization, then integrity check and finally full backup.

The script created a job per task.  How can I schedule these jobs without having them interrupt each other and ensure that they are in the correct order?

You can create these jobs using the Maintenance plan wizard, and direct their work-flow. Open up the SQL management GUI (SSMS) then go to: SSMS>Management>Maintenance Plans>New Maintenance Plan. This will open up a subplan editing area in the right-hand side. On your left-hand size, the tool box should also be visible. You can drag and drop components from the toolbox into the subplan area. A little green arrow will be visible for the component dropped into the sublan area, and you can direct these green arrows in the fashion you want your jobs to run. Eg: Create two TSQL statement tasks A and B. Click on A and direct the green arrow from A to point to B. This will ensure that once A finishes, the flow will continue onto B.
The whole Maintenance plan can then be scheduled with one start time.


mysql gtid_executed and gtid_purged variables I would like to know where the gtid_executed and gtid_purged variables are stored in the database engine. If these are just runtime variables how exactly are they initialized?

They are not part of any particular storage engine. GTID variables can be seen by running:

SHOW GLOBAL VARIABLES LIKE 'gtid%';

or

SELECT * FROM information_schema.global_variables
WHERE variable_name LIKE 'gtid%';

When you run RESET MASTER on the DB Server, these values are blanked out.

MySQL Documentation says about gtid_executed

  Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string. GTIDs are not otherwise removed from this set other than when the set is cleared due to RESET MASTER. The set is also cleared if the server is shut down and all binary logs are removed.

MySQL Documentation says about gtid_purged

  Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

When SQL statements are executed, gtid_executed is updated with the GTID Set value.

MySQL Documentation says about gtid_executed

  When the server starts, @@global.gtid_executed is initialized to the union of the following two sets:

  The GTIDs listed in the Previous_gtids_log_event of the newest binary log
  The GTIDs found in every Gtid_log_event in the newest binary log.

  Thereafter, GTIDs are added to the set as transactions are executed.

gtid_purged can only be set when it is blank. Usually, you will set this variable set during the reload of a mysqldump whose source had GTID enabled.


Restore database in workbench from a Linux file We have a database on a Linux server. Instead of restoring using command line, can I restore it from Workbench? My backup file is in a Linux data directory.

Short answer - Yes, you can restore from MySQL Workbench.

Explanation:

Both MySQL Workbench and command line are clients. I hope you know about the MySQL client-server architecture. MySQL Workbench is GUI(Graphical User Interface) client and Command line(MySQL client) is a CLI(Command Line Interface) client tool.

When you have a backup file on the Linux machine data directory, it is very easy to restore using a command line. The syntax for CLI restoration is:

mysql -u root -p dbname < dbbackup.sql

If you prefer to use MySQL workbench. Create a new connection on the MySQL dashboard by giving hostname (Linux server hostname), MySQL username and password. After created new connection, you will see Import/Restore data on the right-hand side of the window. Click on that option and it will provide you an interface to choose database name and backup file with complete path.

My personal choice would be command line interface. I hope this will help you!!!.


High disk IO with MySQL master-master replication We've been evaluating an active-passive master-master replication setup for our MySQL database, running on version 8.0. While diagnosing some general slowness issues, we were surprised to see very high disk IO ranging from 500MB/s - 1500MB/s on both servers.

I've found that stopping the slave on either server brings the disk IO back to expected levels, 15MB/s - 30 MB/s, and I'm concerned that we have a misconfiguration somewhere.

Here is what I've checked:

The server ID's are unique.
We are doing row-based replication.
We are using the semi-synchronous replication plugin.
The writes are happening in an LVM volume that exclusively houses the data directory.
vmstat shows no swapping.
From the network perspective, we see a few megabits per second going between the servers at most.

Here is the redacted configuration:

[mysqld]
...

# General
server_id                        = <unique_between_servers>
event-scheduler                  = ON
sync_binlog                      = 1
log_bin                          = /var/lib/mysql/mysql-bin
binlog_format                    = ROW
innodb_file_per_table
innodb_flush_log_at_trx_commit   =  1
skip-external-locking

# Replication
relay_log                        = /var/lib/mysql/mysql-relay-bin
relay_log_recovery               = ON
log_slave_updates                = 1
sync_master_info                 = 1
sync_relay_log                   = 1
sync_relay_log_info              = 1
rpl_semi_sync_master_enabled     = 1
rpl_semi_sync_master_timeout     = 10000    # 10 seconds
rpl_semi_sync_slave_enabled      = 1

# Fine Tuning
key_buffer_size          = 16M
max_allowed_packet       = 16M
thread_stack             = 192K
thread_cache_size        = 8
myisam-recover-options   = BACKUP
max_connections          = 800
max_connect_errors       = 1000
innodb_buffer_pool_size  = 192G    # Server has 256GB RAM
innodb_log_buffer_size   = 256M
innodb_log_file_size     = 2G
innodb_flush_method      = O_DIRECT
innodb_lock_wait_timeout = 60
...

My problem sounds suspiciously similar to this person's dead thread from 2013 (Who were you, DenverCoder9? WHAT DID YOU SEE!?)

EDIT:

In an effort to zero in on the problem, we simplified the setup for testing. We used one master and one slave, both running 8.0.11, same configuration as above, and we saw the following write patterns according to iostat (master is blue, slave is red):

(The "Time" on the X-axis is the minutes and seconds of the current time.)

Those per-minute 1+ GB spikes on the master were tracked down to a script updating two small tables (1000-2000 rows each), around 10-15 MB in size on disk. These tables had keys, but the keys were not primary or unique. The script would DELETE all rows in the tables and then repopulate them by performing one INSERT statement per row, all within a transaction. Despite the glaring problems, I still don't understand how operations on tables this small can result in this much disk IO.

Without fixing the problems, we then returned to the master-master active-passive setup and saw the following (active master is blue, passive master is red):

So some additional questions I have are this. Why is the slave consistently writing much more than the master in the first graph? Also, why in master-master replication is there the ping-pong effect seen above where writes to the master result in heavy IO on the slave, which results in heavy IO again on the master?

ALTERs being replicated?  They could be low replication bandwidth but high I/O.
How much RAM?  innodb_buffer_pool_size is quite large.
Do your tables have lots of secondary indexes?
Was there previously a lot of activity that led to updating secondary, non-UNIQUE, indexes?  The Change Buffer is in the buffer_pool and it is flushed asynchronously.  See innodb_change_buffer_max_size, but don't necessarily change it.
Lots of TRIGGERs?
How big are the tables?
In spite of ROW, might a full-table UPDATE have gone through as STATEMENT?
LVM's COW may double (or triple?) the I/O activity.  (But this would only partially explain what you are seeing.)
Are some big SELECTs happening?  (Turn on the slowlog, with a small value for long_query_time.)

No, I don't see any configuration changes to make.

More

Some issues that may be relevant (especially after updates to the Question)

For a single UPDATE of 1K rows,  Row Based Replication is rather verbose.  Each row is a separate record in the replication stream, and it is rather large (compared to the update, itself).  These records will be buffered and written to the Master's disk (binlog) and to the Slave's disk (relay log).  I would assume that they are buffered, etc, so that the amount of writing is the same.  But maybe I am wrong.
The "sql thread" on the Slave reads all of these 1-row updates, presumably one at a time.  Again, one would hope that there is buffering such that this does not add to the I/O.
Both machines need to update any secondary keys via the Change buffer.  However, this is a delayed action.  In fact, it might be delayed so long that it won't show up on the graphs you have presented.
In a Master-Master setup, I do not know when the "loop" is stopped.  That is, server_id 123 replicates to its Slave, server_id 234, which then replicates to all of its Slave, including 123.  Upon discovering 123=123, the replication packets are dropped.  But does that happen before getting back to 123 and being written to the relaylogs there?  I don't know.  You can figure it out by looking at the growth of the relaylogs on the active Master.
What other logs does the Slave have turned on?  Presumably the same as the Master.  General log?  Slow log?  Others?

Scenario details

SET autocommit=0;
DELETE all of 1-2K rows in a particular table. If all at once, and Row-based-replication, then a lot is sent through the replication stream.
INSERT one row back in
INSERT one row back in
... 1-2K rows inserted
COMMIT;  It is not until this point that anything goes across to the Slave.  I suspect this is when the binlog is being written all at once.

Well, I guess this still does not answer your question, but maybe it provides some more insight.

How to replace a table

This is a more efficient way to replace all of a table.  It avoids the table ever being out of service:

CREATE TABLE new LIKE real;
load the new data into the table (LOAD DATA or multi-row INSERT(s) is best)
RENAME TABLE real TO old, new TO real;
DROP TABLE old;


DBCC check on Always On Secondary Replica What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time  including all the secondaries. In my environment all the back ups are happening on the Primary Node only. Any expert advice will be highly appreciated.
I intend to use the Ola hallengren scripts.

Checkdb should be run on both primary and secondary replicas. Normally for big databases and where you have busy system and you cannot afford to run checkdb on both replicas in one go specially on primary replica, people run checkdb on all the secondary replica and failover the primary to secondary making secondary new primary. Now you have new secondary on which you can run checkdb. This allows you to run checkdb on all the nodes without affecting primary workload.

When you run checkdb on secondary primary do gets affected because secondary has to create snapshot and run recovery on it so always run checkdb(even on secondary replica) when load on your complete AG is relatively very less or during downtime.

If you can run checkdb on all the replicas once in a week that would be best thing to do. There are options like physical_only which minimises the impact of checkdb but remember if you use this option the checkdb does not do complete consistency check

Must read:

Checkdb From Every Angle
Minimizing Impact of checkdb


Is there a way to run this query in all databases ? ( SQL Server 2008 R2 ) How can I use this query for each database?

I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax error.

It is a query to find a string inside the result set of a column. I have REDGATE SQL SEARCH but it only searches for objects. I need to find, inside every table, every column of every database a certain string.

This is the query:

DECLARE @SearchStr nvarchar(100) = 'SEARCH_STRING_HERE'
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @TABELA nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @TABELA + ''', LEFT(' + @TABELA + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @TABELA + ' LIKE ' + @SearchStr2
            )
        END
    END

END

SELECT  distinct db_name(db_id()), * FROM @Results

EDIT1:

Just to show you, this is what I get with sp_msforeachdb ( I know it's a bad Idea to use this, if someone knows a better way to do this, I would be grateful):

I need something like a simple query result.

This is the query with sp_msforeachdb

exec sp_msforeachdb 'use [?]  ;
DECLARE @SearchStr nvarchar(100) = ''abortion''
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @TABELA nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''''
SET @SearchStr2 = QUOTENAME(''%'' + @SearchStr + ''%'','''''''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = ''BASE TABLE''
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), ''IsMSShipped''
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'', ''int'', ''decimal'')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                ''SELECT '''''' + @TableName + ''.'' + @TABELA + '''''', LEFT('' + @TABELA + '', 3630)
                FROM '' + @TableName + '' (NOLOCK) '' +
                '' WHERE '' + @TABELA + '' LIKE '' + @SearchStr2
            )
        END
    END

END

SELECT  distinct db_name(db_id()), * FROM @Results'

First, create a temporary table (not a table variable) to hold the results:

SET NOCOUNT ON;
GO
CREATE TABLE #results
(
  db SYSNAME,
  [schema] SYSNAME,
  [table] SYSNAME,
  [column] SYSNAME,
  ColumnValue NVARCHAR(3640)
);
GO

Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that query directly against tables, based on the innermost scope (which is dynamic SQL against each database's metadata), and finally execute all of that for each database.

DECLARE @search NVARCHAR(100) = N'%foo%', -- N prefix is important!
  @sql          NVARCHAR(MAX) = N'',
  @cmd          NVARCHAR(MAX) = N'';

-- build a command per database
SELECT @sql += N'EXEC ' + QUOTENAME(name)
  + '.sys.sp_executesql @cmd, N''@search NVARCHAR(MAX)'', @search;'
  FROM sys.databases
  WHERE database_id > 4 AND [state] = 0 AND user_access = 0 AND is_read_only = 0;

-- need an inner command to build a separate query
-- for every string-based column
SET @cmd = N'DECLARE @inner NVARCHAR(MAX) = N'''';
  SELECT @inner += CHAR(10) + N''SELECT
        [db]     = DB_NAME(),
        [schema] = N'''''' + s.name + '''''',
        [table]  = N'''''' + t.name + '''''',
        [column] = N'''''' + c.name + '''''',
        ColumnValue = LEFT('' + QUOTENAME(c.name) + '',3640)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @search;''
  FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239);

  EXEC sys.sp_executesql @inner, N''@search NVARCHAR(100)'', @search;';

-- now multi-nested actual execution
INSERT #Results(db,[schema],[table],[column],ColumnValue)
  EXEC [master].sys.sp_executesql @sql,
  N'@cmd NVARCHAR(MAX), @search NVARCHAR(100)', @cmd, @search;

Now, select the results and clean up:

SELECT db,[schema],[table],[column],ColumnValue
  FROM #Results
  ORDER BY db,[schema],[table],[column],ColumnValue;

DROP TABLE #Results;

This solution only deals with string-based columns; in your question you list int and decimal, but that strikes me as odd...


Logic rules representation and storage in DB column I have two kind of "elements". One that generates time series (lets call them generator element) and the other that based on some logic rules applied to the time series became "activated" (activable elements).

This simplification represent the time series

CREATE TABLE data.time_series
(
    generator_element_id uuid NOT NULL,
    epoch bigint NOT NULL, -- UNIX epoch
    level integer NOT NULL, -- values from 0 to 3
    -- other fields
    -- PRIMARY KEY (generator_element_id, epoch),
    -- FOREIG KEY TO the table with the generator_elements
)

The idea is that the activable elements are activated following rules like

Legend:

Generator element GE
Activable element AE

IF  (GE1 has level > 1 AND GE2 has level > 1) OR (GE3 has level > 2) for a given instant T1  THEN AE1 becames activated

I'm trying to find the best way to represent this logic rules in the database, as those will be configured by the user and may include multiple conbinations of "AND" and "OR" rules

The issue is precesily with this combination of using AND's and OR's, as if it would be only one type of those I could create another table with the relation between the different kind of elements, and the thresholds. But using a combination of ANDs and ORs i'm at loss..

My first thought was to store those relations using a plain string, similar to what I described before, but using the corresponding UUIDs for the generator elements, and then have a service that will process those rules, and decide to activate or not the elements.

I'm searching for alternatives to this approach as I don't think that my approach could scalate well enough, nor I like it personally...

For context I'm using PostgreSQL 12.0 with docker

Edit:

Clarifying about the epochs as it was not clear. The epoch value is not included in the logic rule "definition", that only includes the elements and its levels (actually it includes other ids but to keep simple just this two variables).

As for the actual calculation the records will be retrieved and separated in "bins" for each epoch. This way we apply the rule over the records for that epoch (assuming that there will always be records for all the generator elements for the given epoch)

Also adding some test data as an example. In this case I'll use one epoch value 1581638400 that corresponds to 2020-02-14 00:00:00Z

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000001', -- uuid for GE1
    1, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000002', -- uuid GE2
    2, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000003', -- uuid GE3
    0, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

Here the rule specified as my proposal would be for the activable element AE1:

-- Omiting the 0s for simplicity
"('000...01' > 1 AND '000...02' > 1) OR  ('000...03' > 2)"

With the dummy data, the result will be that AE1 is activated as the first part of the rule is completed

But with the way that the rule is saved in my proposal, i'll have make a specific processing system for that...

Any help is apreciated

I'll write this as an answer, even though it's just a few ideas. Breaking down the logical rules in a relational form is probably a waste of time, I would simply store the rules using XML or JSON. A parser friendly format like:

{ OR
    { AND
          { GE1 has level > 1 }
          { GE2 has level > 1 }
    }
    { GE3 has level > 2 }
}

is maybe beneficial. Note that this is just an example

This format can easily be transformed into SQL like:

WHERE ((ge=1 AND level>1) AND (ge=2 AND level=2))
   OR  (ge=3 AND level>2)

The ge attribute can be constructed as:

WITH T (uid, level, epoch, ge) AS (
    SELECT uid, level, epoch
         , row_number() over (partition by epoch
                              order by uid) as ge
    FROM data.time_series
)

All and all you would end up with a query like:

WITH T (uid, level, epoch, ge) AS (
    SELECT uid, level, epoch
         , row_number() over (partition by epoch
                              order by uid) as ge
    FROM data.time_series
)
SELECT uid, level, epoch
FROM T
WHERE ((ge=1 AND level>1) AND (ge=2 AND level=2))
   OR  (ge=3 AND level>2)

The idea is hence to read all the rules into memory, when a rule is about to be evaluated it is transformed into SQL, and the query is executed against the time_series table.


Syntax problem in union query I hope someone can tell me what I'm doing wrong in this query. I'm unable to find the cause of the error.

I have 2 tables with 2 columns each, see the CREATE statements here:

CREATE TABLE TableA(datum DECIMAL(8,0), colA INTEGER);
CREATE TABLE TableB(datum DECIMAL(8,0), colB INTEGER);

INSERT INTO TableA values('20160104', '1');
INSERT INTO TableA values('20160101', '2');
INSERT INTO TableA values('20160102', '3');
INSERT INTO TableA values('20160105', '2');
INSERT INTO TableA values('20160102', '6');
INSERT INTO TableA values('20160105', '4');

INSERT INTO TableB values('20160107', '5');
INSERT INTO TableB values('20160103', '8');
INSERT INTO TableB values('20160107', '2');
INSERT INTO TableB values('20160101', '1');
INSERT INTO TableB values('20160101', '4');
INSERT INTO TableB values('20160105', '3');

What I'm trying to achieve is to get a result with all the dates and the sum of the columns for that date.
The query that I use is the following:

select datum, sum(resColA), sum(resColB)
from
    select datum, sum(colA) as resColA, 0 as resColB
    from TableA
    group by datum
union
    select datum, 0 as resColA, sum(colB) as resColB
    from TableB
    group by datum
group by datum
order by datum

It keeps telling me:

  [SQL0104] Token ( is invalid. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

Both legs of the union work fine as separate SELECT statements, but when I put them together, the whole thing doesn't work any more.

Does anyone have an idea what I'm doing wrong? I can't figure it out.

You want to group by again the result of the union. For that you need to enclose the union in parentheses and add an alias ("name" it), i.e. make it a derived table:

select datum, sum(resColA) as resColA, sum(resColB) as resColB
from
(   select datum, sum(colA) as resColA, 0 as resColB
    from TableA
    group by datum
  union
    select datum, 0 as resColA, sum(colB) as resColB
    from TableB
    group by datum
) as drv              -- an alias for the derived table
group by datum
order by datum ;

or make it a CTE (common table expression):

with
  cte as
    (   select datum, sum(colA) as resColA, 0 as resColB
        from TableA
        group by datum
      union
        select datum, 0 as resColA, sum(colB) as resColB
        from TableB
        group by datum
    )
select datum, sum(resColA) as resColA, sum(resColB) as resColB
from cte
group by datum
order by datum ;

There is practically little difference between the above 2 options and I think no difference in efficiency in DB2. Pick whatever feels more readable to you. Alternatively, you could first union the data from the 2 tables and then group by. This will probably result in different execution plans, so test which is more efficient:

select datum, sum(resColA) as resColA, sum(resColB) as resColB
from
(   select datum, colA as resColA, 0 as resColB
    from TableA
  union
    select datum, 0 as resColA, colB as resColB
    from TableB
) as drv
group by datum
order by datum ;


Why is this query not using my nonclustered index, and how can I make it? As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default.

This query runs in about 2.5 seconds:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

This one runs in about 33ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'
ORDER BY [DateEntered], [DeviceID];

There is a clustered index on the [ID] field (pk) and there is a non clustered index on [DateEntered],[DeviceID]. The first query uses the clustered index, the second query uses my non-clustered index. My question is two parts:

Why, since both queries have a WHERE clause on the [DateEntered] field, does the server use the clustered index on the first, but not the second?
How can I make the non clustered index be used by default on this query even without the orderby? (Or why would I not want that behavior?)

Expressing the query using different syntax can sometimes help communicate your wish to use a non-clustered index to the optimizer.  You should find the form below gives you the plan you want:

SELECT
    [ID],
    [DeviceID],
    [IsPUp],
    [IsWebUp],
    [IsPingUp],
    [DateEntered]
FROM [dbo].[Heartbeats]
WHERE
    [ID] IN
(
    -- Keys
    SELECT TOP (1000)
        [ID]
    FROM [dbo].[Heartbeats]
    WHERE
        [DateEntered] >= CONVERT(datetime, '2011-08-30', 121)
        AND [DateEntered]  < CONVERT(datetime, '2011-08-31', 121)
);

Compare that plan with the one produced when the non-clustered index is forced with a hint:

SELECT TOP (1000)
    *
FROM [dbo].[Heartbeats] WITH (INDEX(CommonQueryIndex))
WHERE
    [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

The plans are essentially the same (a Key Lookup is nothing more than a seek on the clustered index).  Both plan forms will only ever perform one seek on the non-clustered index and a maximum of 1000 lookups into the clustered index.

The important difference is in the position of the Top operator.  Positioned between the two seeks, the Top prevents the optimizer from replacing the two seeking operations with a logically-equivalent scan of the clustered index.  The optimizer works by replacing parts of a logical plan with equivalent relational operations.  Top is not a relational operator, so the rewrite prevents the transformation to a clustered index scan.  If the optimizer were able to reposition the Top operator, it would still prefer the scan over the seek + lookup because of the way cost estimation works.

Costing of scans and seeks

At a very high level, the optimizer's cost model for scans and seeks is quite simple: it estimates that 320 random seeks cost the same as reading 1350 pages in a scan.  This probably bears little resemblance to the hardware capabilities of any particular modern I/O system, but it does work reasonably well as a practical model.

The model also makes a number of simplifying assumptions, a major one being that every query is assumed to start with no data or index pages already in cache.  The implication is that every I/O will result in a physical I/O - though this will rarely be the case in practice.  Even with a cold cache, pre-fetching and read-ahead mean that the pages needed are actually quite likely to be in memory by the time the query processor needs them.

Another consideration is that the first request for a row that is not in memory will cause the whole page to be fetched from disk.  Subsequent requests for rows on the same page will very likely not incur a physical I/O.  The costing model does contain logic to take some account of effects like this, but it is not perfect.

All these things (and more) means the optimizer tends to switch to a scan earlier than in probably should.  Random I/O is only 'much more expensive' than 'sequential' I/O if a physical operation results - accessing pages in memory is very fast indeed.  Even where a physical read is required, a scan may not result in sequential reads at all due to fragmentation, and seeks may be collocated such that the pattern is essentially sequential.  Add to that the changing performance characteristic of modern I/O systems (especially solid-state) and the whole thing starts to look very shaky.

Row Goals

The presence of a Top operator in a plan modifies the costing approach.  The optimizer is smart enough to know that finding 1000 rows using a scan will likely not require scanning the whole clustered index - it can stop as soon as 1000 rows have been found.  It sets a 'row goal' of 1000 rows at the Top operator and uses statistical information to work back from there to estimate how many rows it expects to need from the row source (a scan in this case).  I wrote about the details of this calculation here.

The images in this answer were created using SQL Sentry Plan Explorer.


MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, student behavior management and grading. We noticed that over-the-shelf products are unable to complete our specific tasks without major customization and costs. Hence, we decided to develop this ourselves since we have full control as to how we want our information to be reported to our education board (as per their specifications) and have control on our complex grading system (which is continuously updated to meet the dynamic benchmark needs of our education board).

We have begun using MS Access and is still in the primary stages of development. We want to know whether we should use MS Access as a front-end and SQL Server as a back-end DB since we want to accomplish the following:

Ability for 80+ employee users to simultaneously access, post, update and delete customer records within the SIS DB (on campus).
Ability for our SIS DB to have web-based functionality with a student login portal integrated within our school website so 1080+ students can simultaneously access a specific web-based DB form from their homes (by using the student login portal) for updating personal records, checking grades, and other performance summaries.
Ability for our SIS DB to be highly secure and functional 24/7 without interruptions and security breeches (we read that MS Access alone cannot accomplish this).
Ability to integrate our SIS DB with another in-house database that will provide digital learning materials to students within the comfort of their homes (also accessed through student login portal). The digital learning materials DB is another DB we are creating that should have ability to store 100 GBs worth of multi-media materials.
Ability for data to be quickly pulled from tables to the GUI without delay.

A major factor for our school in this matter is COST. They want to create the DB under USD 1,500 and would like to have many users being able to access this system at the same time. Please help me to decide how I should go about this situation, both performance-wise and cost-wise. I apologize for the long details provided. I am really confused and would deeply appreciate if any one could help me on this matter.

In addition, we wanted our forms to be professional like the ones seen in major DB systems. We noticed that Access can create forms but only to a certain extent. Is there any other system programming-friendly platforms we can use for creating highly complex and professional DB forms (lke Visual Studio, for example)? Help on this subject as well will be extremely appreciated.

Ankith

This sounds like an enormous project.

My advice is to go back and look again for an open source, off-the-shelf product that meets your main needs.

It is difficult to believe that your school is alone in its needs and that no-one else has similar requirements.

Even if you the system does not do everything you require, as long as it is built in an open way, with a sound underlying data model then you will be able to extend it with small additional components.

I would also be careful to manage expectations and let people know that the system will not meet all their needs to start with.

good luck!


SQL Server 2000 trace file extension Are SQL Server 2000 trace files generated in a .tdf format, the same way as SQL Server 2008 R2?

In SQL server 2000 trace file generated in .trc format for more details please refer below link
http://technet.microsoft.com/en-us/library/dd277388.aspx


Creating a helper UDF or stored procedure and limiting external access I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'.

I'm working on a security system and I want client apps to only access a handful of top-level functions. For example:

CanUserEditObject(@personId, @objectId) -- called to see if an edit is possible
EditableBy(@personId) -- returns a list of editable object ids

These form the API I want client apps to use.

To implement that, I need some other functions which I don't want client apps to call;

GetLocationsAdministeredBy(@personId)
IsUserSysadmin(@personId)

Client apps have no need to call them, and I don't promise to support them going forward -- they're just an implementation detail. So I'd like to hide them. Ideally, any attempt to call it by a client application will fail. Ideally ideally, I'd be able to call them in SQL Server Management Studio to develop them.

Basically, this is inspired by the OO principle of information hiding and language constructs like public and private access modifiers in C++/Java/C#/etc.

I don't need it to be a guaranteed, hard-security kind of solution. I basically trust my clients here, but I just don't want some plucky developer to spot a function I've written and use it, then complain when I rewrite it later.

This is mainly for SQL Server 2014, but I'm interested if there are any 'standard' SQL techniques.

Also, we have a team where some of us are better at SQL than others. The SQL people would add their own objects, but they wouldn't use my 'private' functions. The other devs might discover existing functions rather than add them. I'm looking for a way to differentiate so that no-one accidentally relies on an unsupported function. Something as simple as a naming convention would do but I am interested how other people muddle through with an issue that most non-SQL languages have good support for.

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects referenced within a module so long as as the referenced objects have the same "owner" as the "owner" of the module being executed, and those referenced objects are not being referenced within Dynamic SQL.

Ownership Chaining

The following example illustrates this behavior. The Test Login cannot directly access the Function because no EXECUTE permission has been granted on it. But when it is used in a Stored Procedure that the Test Login / User has been granted EXECUTE permission on, then it works because both are in the same Schema (i.e. dbo). However, the ownership chain is broken by Dynamic SQL, which is why the second Stored Procedure, PublicDoSomethingDS, gets an error.

USE [master];
CREATE LOGIN [Test] WITH PASSWORD = 'test';
GO

USE [tempdb];
CREATE USER [Test] FROM LOGIN [Test];
CREATE TABLE dbo.PrivateTest (Col1 INT);
INSERT INTO dbo.PrivateTest (Col1) VALUES (1), (4), (999);
GO

CREATE FUNCTION dbo.PrivateGetRowCount (@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomething
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT dbo.PrivateGetRowCount(@SomeInput);
GO

CREATE PROCEDURE dbo.PublicDoSomethingDS
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

EXEC sys.sp_executesql
 N'SELECT dbo.PrivateGetRowCount(@tmpSomeInput);',
 N'@tmpSomeInput INT',
 @tmpSomeInput = @SomeInput;
GO

GRANT EXECUTE ON dbo.PublicDoSomething TO [Test];
GRANT EXECUTE ON dbo.PublicDoSomethingDS TO [Test];
GO

SELECT SESSION_USER; -- dbo
SELECT dbo.PrivateGetRowCount(1); -- 1

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- 12
EXEC('EXEC dbo.PublicDoSomethingDS 5;'); -- 15

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT dbo.PrivateGetRowCount(1); -- error:
-- Msg 229, Level 14, State 5, Line 60
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- error:
-- Msg 229, Level 14, State 5, Line 71
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

BUT, if others can create their own objects -- Stored Procedures, Functions, Views, etc -- then Ownership Chaining does't get you anywhere since someone creating their own module in the dbo Schema would then have access to those "private" modules.

In that case, you should be able to accomplish this separation with a little more work by using signatures and module signing.

Module Signing

Module Signing is using an Asymmetric Key or Certificate to create a Login and/or User from, assign the desired permission(s) to that Asymmetric Key-based or Certificate-based Login and/or User, and then "sign" the modules that should be given those permissions using the ADD SIGNATURE command (introduced in SQL Server 2008).

This approach is a little more complicated and takes a little more time to set up, but allows for very granular control of permissions.

Assuming you have fully run the example code shown above, now run the following:

USE [tempdb];
GO

CREATE SCHEMA [Private] AUTHORIZATION [dbo];
GO

CREATE FUNCTION [Private].[GetRowCount](@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomethingPrvt
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT [Private].[GetRowCount](@SomeInput);
GO

GRANT EXECUTE ON dbo.PublicDoSomethingPrvt TO [Test];

SELECT SESSION_USER; -- dbo
SELECT [Private].[GetRowCount](1); -- 3

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT [Private].[GetRowCount](1); -- error:
-- Msg 229, Level 14, State 5, Line 41
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

REVERT;
SELECT SESSION_USER; -- dbo

We created a new Schema and put the new Function in it, yet the Test User was still able to execute PublicDoSomethingPrvt. Why? Because the "owner" of the Private Schema is dbo, which is the same "owner" of the dbo Schema. Hence Ownership Chaining is still allowing this to work.

But we can finally break that by running the following:

CREATE USER [MrX] WITHOUT LOGIN;

ALTER AUTHORIZATION ON SCHEMA::[Private] TO [MrX];

EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- still works for dbo

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 71
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

Now there is no more Ownership Chain. And in fact, even if you were to grant EXECUTE on the [Private].[GetRowCount] Function to the Test User, they would still get an error because there is no more automatic link between the Function and the Table that it selects from.

So now what? Well, now we create the Asymmetric Key, then the User from that Key, then grant that User the desired permission(s), and then sign the Stored Procedure with that same Asymmetric Key:

CREATE ASYMMETRIC KEY [PrvtKey] WITH ALGORITHM = RSA_4096 ENCRYPTION BY PASSWORD = 'silly';
-- RSA_2048 for SQL Server 2012 and older

CREATE USER [PrvtUser] FROM ASYMMETRIC KEY [PrvtKey];

GRANT EXECUTE ON [Private].[GetRowCount] TO [PrvtUser];

ADD SIGNATURE TO [dbo].[PublicDoSomethingPrvt]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 94
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

Almost there. The PublicDoSomethingPrvt Stored Procedure (not the Test User!) can now execute the Function, but that Function still has no inherent link to the Table, which is in another Schema that has a different owner. Now we just add that final permission to the Asymmetric Key-based User and then sign the Function:

GRANT SELECT ON [dbo].[PrivateTest] TO [PrvtUser];

ADD SIGNATURE TO [Private].[GetRowCount]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 12; -- 36
-- works now!!!!!!!!!!!!!!!!!!  :-)

SELECT * FROM [dbo].[PrivateTest];
-- Msg 229, Level 14, State 5, Line 110
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

SELECT [Private].[GetRowCount](12);
-- Msg 229, Level 14, State 5, Line 115
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

As you can see, the Test User only has the permissions explicitly granted to it, namely EXECUTE on the PublicDoSomethingPrvt Stored Procedure.

Notes:

You control this by being the only one to have that "password" (determined when creating the Asymmetric Key, and used when running ADD SIGNATURE).
For security reasons, Signatures are dropped from modules if the definition changes in any way, even the EXECUTE AS clause of the CREATE statement. This ensures that someone doesn't change a situation that you were fine with into something that you aren't ok with. So when definitions change, you need to re-run the ADD SIGNATURE command.
You don't need multiple Users (i.e. MrX and PrvtUser), that was just for the demo code. You could use the Asymmetric Key-based User to own the "Private" Schema.
You don't need to assign permissions to the Asymmetric Key-based User per each Module. Instead, you could grant Schema-wide permissions to simplify things (e.g. EXECUTE on the Private Schema, SELECT on the dbo Schema, etc).
You could further divide the permissions by creating one Asymmetric Key and associated User for allowing the "public" modules to execute stuff in the Private Schema. Then, create another Asymmetric Key and associated User to allow the "private" modules access to objects in the "public" Schema(s).


How to create database, and add user linked to login? created a login and a user in the master database and linked that user to the login.

I created a new database, then went to that database and immediately tried to create a user linked to the same login. I can't because the user dbo is already linked to the login.

In the master database, SELECT CURRENT_USER returns my WMC_SQLMGR user name which is
linked to the WMC_SQLMGR login.

In the newly created WMC_main database, SELECT CURRENT_USER returns dbo as user name which is linked to the WMC_SQLMGR login.

CREATE login   WMC_SQLMGR WITH PASSWORD= 'pwstuff'
GO
(left out addsrvrolemember commands)

CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR
GO
CREATE DATABASE WMC_main ON PRIMARY
   ( NAME=N'WMC_main', ... (left out rest for brevity)
GO
ALTER LOGIN WMC_SQLMGR WITH DEFAULT_DATABASE = WMC_main
GO
USE WMC_main
CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR
   (this fails because the login    WMC_SQLMGR is connected to user dbo.)
***********************************************************
                DB_NAME    WMC_cur_user_name      sess_user_name user_name
---------------- ---------- -------------------- -------------- ----------
after USE MASTER master     WMC_SQLMGR           WMC_SQLMGR     WMC_SQLMGR

(1 row(s) affected)

-------------------------
database WMC_main dropped

(1 row(s) affected)

-------------------------
database WMC_main created

(1 row(s) affected)

                   DB_NAME    WMC_cur_user_name    sess_user_name user_name
------------------ ---------- -------------------- -------------- ----------
AFTER USE WMC_MAIN WMC_main   dbo                  dbo            dbo

(1 row(s) affected)

------------------------------------------------------
now try to create user WMC_SQLMGR for LOGIN WMC_SQLMGR

(1 row(s) affected)

Msg 15063, Level 16, State 1, Line 13
The login already has an account under a different user name.

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database:

CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER foo FROM LOGIN [foo];
GO
-- then permissions:
GRANT ... TO foo;
EXEC sp_addrolemember N'<role>', N'foo';
etc.


SQL Server cluster node fail over details I would like to know the SQL Server cluster node fail over details immediately  as soon as the passive node is active as I have some dependency on active node. For that, I see sys.dm_os_cluster_nodes view will provide the active/passive node details, but I am not able to write a trigger on this view to send a mail automatically.
I spent some time but was not able to find out. I can write a SQL Server job to monitor but I will not prefer that.
Could you please help in guiding me to get a notification mail once the active node becomes passive?
Thank you.

Just to add, your sql agent job could look like this, which works for us (this assumes you have database mail configured properly):
Set the schedule to be when sql server starts as described in Ronaldo's answer
DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)
DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)

SELECT  @recipientsList ='someuser@mycompany.com;someuser@mycompany.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Failover occured for SQL Server Instance '+@InstanceName
SELECT  @body = 'Failover occured for SQL Server Instance '+@InstanceName + 'This instance is currently running on the node '+@NodeName
SELECT  @importance ='High'
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='Dbas',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance = 'HIGH'


Strange CPU load on SQL Server Starting at midnight for 30-40 minutes our application responds slowly (or times-out completely) What would cause this kind of cpu usage on a SQL 2012 server?

I cannot find any scheduled tasks or maintenance plans which run at this time.

You can look at some joined system views like dm_exec_query_stats, dm_exec_sql_text and dm_exec_query_plan. The goal is to look what's on cache.

Look for last_execution_time field and search the time you found the problems. You may get an idea of what was going on during that time.


What determines the default schema name in SQL Server? when I tried to create a table, it is using my ID as the schema name. But when the table is created on my manager's computer, the schema name is "dbo". Why? He might have administrator rights. When I tried to rename by using sp_rename, it is not changing.

In SSMS go to your database user and click on properties.  You will see a field there for default schema.

T-SQL version:

alter user YourDbUser
with default_schema = YourNewSchema


What format is this export of election data in? I have a series of files I've downloaded from the Texas Ethics Commission (a misnamed . You can see an example of their layout in the ReadMe.txt. I've made an etl library which processes the ReadMe.txt to generate the SQL DDL to create this schema, and load it from the CSVs. The thing is, I think this is a standardized format. I imagine it's backed by something like a PICK (which is a BASIC database) or something COBOL-esque -- having worked with them before, and that this is something like a MARC, or ANSI, or ISO standard.

I'd like to potentially abstract out my ETL script to benefit others who use this format.

Some identifying features of the format are that it supports

Arrays and internal one-to-many relations on the record
at least types BigDecimal, Long, Date, String
the export is labeled "Flat File Architecture Record Listing"

Arrays

For example here you see this,

    Array                                                                                4050
    loanGuarantorLoanPersent[5/ROW_MAJOR]         CsvPublicExportLoanGuarantorLoanPersent     810 Guarantors for the loan (maximum 5)
 46    guarantorPersentTypeCd                     String                                       30 Type of guarantor name data - INDIVIDUAL or ENTITY
 47    guarantorNameOrganization                  String                                      100 For ENTITY, the guarantor organization name
 48    guarantorNameLast                          String                                      100 For INDIVIDUAL, the guarantor last name

That defines a structure called a loanGuarantorLoanPersent and essentially declares that there are five of them. So the export CSV will have something like

guarantorPersentTypeCd1,guarantorNameOrganization1,guarantorNameLast1,guarantorPersentTypeCd2,guarantorNameOrganization2,guarantorNameLast2,guarantorPersentTypeCd3,guarantorNameOrganization3,guarantorNameLast3...

You can see an example of this data here,

https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_CF_CSV/ReadMe.txt
https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_LA_CSV/LobbyLAR-ReadMe.txt

I believe this is Political Disclosure Standard Electronic Reporting Format (PDSERF) a format used by [SDR Technologies] which later went off and was acquired by National
Information Consortium Technologies, Inc. in 2000

As for the file format, I found this about it

  This format utilizes EDI, the Electronic Data Interchange, standards developed and maintained by the Accredited Standards Committee X.12 of the American National Standards Institute."

You can tie this into Texas in this proposal by the Texas Ethics Commission which was later subsequently rewarded

  On January 11, 2000 I attended a public briefing in the Capitol Extension held by the Texas Ethics Commission and SDR Technologies, Inc. to discuss specifications for a standard file format for filing reports electronically with the Ethics Commission. This file format will be available to commercial vendors so they may generate campaign finance reports for filing electronically from their software. Additionally, the briefing demonstrated how filers will be able to convert data from spreadsheets such as Excel or Access for filing campaign finance reports electronically. On February 16, 2000 I attended another similar public briefing, which included a filer software demo.

You can also see them talk about it here,

  The document produced by SDR Technologies describing the filing format used for electronically submitting disclosure and registration documents to the Texas Ethics Commission (i.e., containing the technical specifications) is available upon request. It is located in the appraisal files of the Archives and Information Services Division of the Texas State Library and Archives Commission, and is titled "Texas PDSERF/Plus (.PDP) File Layouts."

I think the PDSERF/Plus is an SDR Technologies thing. They also seem to service the Federal Election Commission. You can see them pitch their product to the State of California here, and Michagan, and Louisiana also seem to use it (or have used it).


Like '%' search for value withing longblob column in MySql is slow MySQL version is 5.7.25

I have table like

CREATE TABLE `applications` (
  `id` varchar(25) NOT NULL,
  `application` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

'application' is approximately 50kb xml text.

the table contains around 120k records.

When I am doing search

Select * from `applications`
where
application like '%<l:name>8085%</l:name>%'

the query fail by 30 minutes timeout.

if I do

Select A.* from (
Select * from `applications`
Limit 0,1000
)A where
A.application like '%<l:name>8085%</l:name>%'

the query finished in 6 seconds.

so by doing pagination I can get the query result in 120*6 = 720 seconds

Interesting that same query with Limit 0,10000 finished in 120 seconds, with Limit 0,100000 failed by 30 minutes timeout

from this SO post I understand that probably longblob query performance is not linear from number of rows.

I finally finished with getting query results by doing pagination and concatenating results.

Select A.id from (
Select * from `applications`
Limit 0,10000
)A where
A.application like '%<l:name>8085%</l:name>%'

Select A.id from (
Select * from `applications`
Limit 10000,20000
)A where
A.application like '%<l:name>8085%</l:name>%'
......

The question is how to get results more convenient way.

The search string varies and is unknown in advance, updating column type to TEXT is not an option right now, since that would require application refactoring that no one wants at this point. The query is part of manual update, so the execution time is not really a problem.

  lennart:

    Beside the point, but even though no one is updating the table, you may miss certain rows. You need to do an order by together with limit , if you want to be guaranteed to examine all rows.

Adding ORDER BY and checking for updates make sense, thank you.

The problem was finally fixed by Increasing the INNODB_BUFFER_POOL_SIZE from 256M to 1G
After that the query run less than second.


Index columns on pre-production? If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.

Say your app is new and you have identified columns which are good candidates for indexing (based on how you did your views e.g., WHERE / JOINS).

Do you have to immediately index those columns even before you notice performance issues? Or does it make sense to do them later as to not affect the INSERTs yet?

Do you have to immediately index those columns even before you notice performance issues?

Short answer - Yes.

Where you have joins, you should also have Primary and Foreign Key Constraints and most DBMSs will insist that each of these is supported by an Index (even if it has to create one for you).

The vast majority of traffic in most databases is SELECTs, so putting off the creation of Indexes until performance becomes a problem is just setting yourself up to fail.  You know you're going to need them, so save yourself the grief and put them in to start with.
(And, anyway, it won't be you that notices the "Performance Problem" - it will be your unhappy Users).


Optimizing my update sequence Whenever a user updates his profile, we conduct a set of queries like so:

DELETE FROM `user_likes` WHERE `ID` = $id
INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name)

Is there a way to do this all in one step? There has to be a way, because the interest IDs are all different for each user. Keep in mind that the data never actually has to be deleted - it's a set that is always growing.

If you just updating the interest id and name, use UPDATE

UPDATE user_likes SET
`interest_id` = $interest_id,
`interest_name` = '$interest_name'
WHERE `ID` = $id;

If you want to mechanically do a DELETE followed by an UPDATE, use REPLACE

REPLACE INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name);

Please test these out on a Dev DB Server to make sure this is what you want


Visual Studio 2010 Shell Error - is it Causing this SQL Server error? Visual Studio 2010 Isolated Shell has the infamous "Invalid License Data. Reinstall" error. I have SSMS 2014. I have tried reinstalling several times, I installed Visual Studio 2013 but SSMS doesn't recognize it. When I attempt to open SSMS, it says something like components missing, reinstall. So when I repair it installs the same VS 2010 with same issue.

We have an application with a SQL Server backend database. We have a vendor catalog and then import items to our item database. However, it fails and gives this error code below. So my question is: Could this VS 2010 error be causing this error or is this likely an unrelated issue?

  mscorlib 06/18/2016 8:20:42 PM LoadSettings Object reference not
  set to an instance of an object.
  at PosBeData.Settings.LoadSettings() PosBeData 06/18/2016 8:20:43 PM Time

  Update To: 06/19/2016 12:20 AM 06/18/2016 8:32:47 PM frmImportAdd
  String or binary data would be truncated. The INSERT statement
  conflicted with the FOREIGN KEY constraint "FK_UPC_Item". The conflict
  occurred in database "PosBe", table "dbo.Item", column 'Item_ID'. The
  INSERT statement conflicted with the FOREIGN KEY constraint
  "FK_VendorPartNo_Item". The conflict occurred in database "PosBe",
  table "dbo.Item", column 'Item_ID'. The statement has been terminated.
  The statement has been terminated. The statement has been terminated.

  Server stack trace:     at
  System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message
  reply, MessageFault fault, String action, MessageVersion version,
  FaultConverter faultConverter)    at
  System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime
  operation, ProxyRpc& rpc)    at
  System.ServiceModel.Channels.ServiceChannel.Call(String action,
  Boolean oneway, ProxyOperationRuntime operation, Object[] ins,
  Object[] outs, TimeSpan timeout)    at
  System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage
  methodCall, ProxyOperationRuntime operation)    at
  System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage
  message)

  Exception rethrown at [0]:     at
  System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
  reqMsg, IMessage retMsg)    at
  System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
  msgData, Int32 type)    at
  PosBeData.PosBeService.GetAddCatalogItems(dtoV_VendorCatalog[]
  VendorCatalogItems, String Import_CD, Int32 Category_ID, Int32
  Sub_Category_ID, Boolean CreateInventory, Boolean CreateCase, String
  Default_Mfg, UserAuth ua)    at
  PosBeData.V_VendorCatalogCollection.AddSelectedItems(String Import_CD,
  Int32 Category_ID, Int32 Sub_Category_ID, Boolean CreateInventory,
  Boolean CreateCase, String Default_Mfg)    at
  PosBe.frmVendorCatalog.AddItemsThread() mscorlib

You have several SQL Server errors in the posted message:

  String or binary data would be truncated.

  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UPC_Item". The conflict occurred in database "PosBe", table "dbo.Item", column 'Item_ID'.

  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VendorPartNo_Item". The conflict occurred in database "PosBe", table "dbo.Item", column 'Item_ID'.

Those are the result of the server's attempts to execute SQL statements issued by your application. They cannot possibly be caused by problems with Visual Studio or SQL Server Management Studio. SSMS is essentially just another application that, like yours, can connect to the database server. Your being unable to run it cannot possibly affect how SQL queries sent to the server by your application are executed.


EAV with multiple values per attribute I am working on e-commerce website with wide range of categories (TV, mobile phones, furniture, etc). Since there is such large amount of attributes I decided to use EAV model to store attributes and values. Here is my tables structure:

CREATE TABLE public.product_attribute
(
    name character varying(255) COLLATE pg_catalog."default",
    attribute_type character varying(255) COLLATE pg_catalog."default",
    id integer NOT NULL,
    CONSTRAINT product_attribute_pkey PRIMARY KEY (id)
)

CREATE TABLE public.product_attribute_value
(
    value_text text COLLATE pg_catalog."default",
    value_integer integer,
    value_boolean boolean,
    value_float double precision,
    value_richtext text COLLATE pg_catalog."default",
    value_date date,
    value_file character varying(255) COLLATE pg_catalog."default",
    value_image character varying(255) COLLATE pg_catalog."default",
    entity_object_id integer,
    attribute_id integer,
    product_id integer,
    id integer NOT NULL,
    CONSTRAINT product_attribute_value_pkey PRIMARY KEY (id),
    CONSTRAINT "product_attr_id" FOREIGN KEY (attribute_id)
        REFERENCES public.product_attribute (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Let's assume that I am adding a new TV.

Product
---------
id: 1
name: Sony TV

Product Attribute
-----------------
id: 1
name: Color

Product Attribute Value
-----------------------
id: 1
attribute_id: 1
value_text: white

The problem I am facing is that for some products (such as furniture) there are more than one attribute with same name (Color for example), not only that, I am also required to store the image photo url.

Product
---------
id: 2
name: Table

Product Attribute Value
-----------------------
id: 3
attribute_id: 1
value_text: Pattern 1
value_image: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg
=======================
id: 4
attribute_id: 1
value_text: Pattern 2
value_image: 01/a2/01a234234234234234324324234234.jpg

What is the best way to fetch products with all attributes? I am not worried a lot about query performance for filtering by attribute value (since all data synced to elasticsearch and fetched from there). But there are few millions of products. Is there any way to fetch each  product with all attributes with one query (one product -> one row)?

I think JSONB is not the best idea to store this attributes, since they are updated frequently and I don't want to search and update millions of records to rename attribute name.

Your answer

  more than one attribute with same name (Color for example),

So then create a different attribute with more specificity.

  I am also required to store the image photo url.

This is just another attribute...

Product Attribute
-----------------
2 | photo_exterior

Product Attribute Value
-----------------------
id: 1
attribute_id: 2
value: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg

All of that said, I wouldn't be doing this unless I had to. I would be using JSONB.

  What is the best way to fetch products with all attributes?

I'm unsure what kind of resultset you want. Are you asking how do you query an EAV? If you can make it, you can certainly do the joins to get the result set out of it.

  Is there any way to fetch each product with all attributes with one query (one product -> one row)?

Yes, if you don't know how many attributes you have, you'll likely end up using hstore or jsonb though. See jsonb_agg()

X-Y problem?

Examining speed in order to get you to reasses JSONB and save your life. Create a million products, rename the attribute using jsonb.

CREATE TABLE products(id,name,data)
AS
        SELECT x, 'foo'::text, jsonb_build_object('color', 'blue') AS data
        FROM generate_series(1,1e6) AS gs(x);

UPDATE products
SET data = data-'color' || jsonb_build_object('colour', data->'color');
Time: 5063.010 ms

On my x230 laptop, with an SSD. That's 197 rows/ms? You recreating Amazon that you have to update faster than that?


Simple insert in stored procedure runs twice I have one stored procedure to insert into a table. I have two questions:

Is it a ok?
Sometimes the insert operation runs twice. Why?

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows 8.1 Enterprise 6.3  (Build 9600: )

CREATE PROCEDURE [dbo].[insert_newNEWS]
-- Add the parameters for the stored procedure here
@news_owner_id   INT,
@news_owner_type BIT,
@news_cat_id     INT,
@news_producer   NVARCHAR(1000),
@news_title      NVARCHAR(1000),
@news_sutitr     NVARCHAR(1000),
@news_text       NVARCHAR(1000),
@news_date       NVARCHAR(128),
@news_time       NVARCHAR(16),
@news_link       NVARCHAR(512),
@img_uri_type    TINYINT,
@img_uri         XML,
@newsContentID   INT OUTPUT
AS
     BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     BEGIN TRANSACTION;
     SET @newsContentID = dbo.Biggest_ID(13);
     INSERT INTO [Contents].[news]
     ([id_news],
      [news_owner_id],
      [news_owner_type],
      [news_cat_id],
      [news_producer],
      [news_title],
      [news_sutitr],
      [news_text],
      [news_date],
      [news_time],
      [news_link],
      [img_uri_type],
      [img_uri]
     )
     VALUES
     (@newsContentID,
      @news_owner_id,
      @news_owner_type,
      @news_cat_id,
      @news_producer,
      @news_title,
      @news_sutitr,
      @news_text,
      @news_date,
      @news_time,
      @news_link,
      @img_uri_type,
      @img_uri
     );
     IF(@@ERROR <> 0)
         BEGIN
             ROLLBACK TRAN;
             RETURN 1;
         END;
     COMMIT;
 END;

AND this is Mr Biggest_ID function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Biggest_ID](@table_id INT)
RETURNS INT
AS
     BEGIN
         DECLARE @BiggestID AS INT;
         --another table ID
         IF @table_id = 13
             BEGIN
                 SELECT @BiggestID = ISNULL(MAX(nws.id_news), 0) + 1
                 FROM [Contents].[news] AS nws;
             END;

         RETURN @BiggestID;
     END;

and this table :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Contents].[news](
     [id_news] [int] NOT NULL,
     [news_owner_id] [int] NOT NULL,
     [news_owner_type] [bit] NOT NULL,
[news_cat_id] [int] NOT NULL,
[news_producer] [dbo].[JBText] NOT NULL,
[news_title] [dbo].[JBText] NOT NULL,
[news_sutitr] [dbo].[JBText] NOT NULL,
[news_text] [dbo].[JBText] NOT NULL,
[news_date] [nvarchar](128) NULL,
[news_time] [nvarchar](16) NULL,
[news_link] [dbo].[JBLinks] NULL,
[img_uri_type] [tinyint] NULL,
[img_uri] [xml] NULL,
 CONSTRAINT [PK_news] PRIMARY KEY CLUSTERED
(
    [id_news] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I created a test DB and table, I adjusted the schema to make it a bit easier and also commented out the set in the proc.

USE [Test]
GO

/****** Object:  Table [dbo].[news]    Script Date: 2/4/2017 12:21:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[news](
    [id_news] [int] NULL,
    [news_owner_id] [int] NULL,
    [news_owner_type] [bit] NULL,
    [news_cat_id] [int] NULL,
    [news_producer] [nvarchar](1000) NULL,
    [news_title] [nvarchar](1000) NULL,
    [news_sutitr] [nvarchar](1000) NULL,
    [news_text] [nvarchar](1000) NULL,
    [news_date] [nvarchar](128) NULL,
    [news_time] [nvarchar](16) NULL,
    [news_link] [nvarchar](512) NULL,
    [img_uri_type] [tinyint] NULL,
    [img_uri] [xml] NULL,
    [newsContentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I'm going to answer 2 first. I ran the proc once, it inserted one time. I looked through the execution plan and it shows a straight forward insert. You can see it here: Standard run. I then set your error handling to 0, just to see the plan with that being triggered. Also straight forward. Error handling triggered. I would run SQL Profiler when you see it insert two records or set up sp_whoisactive to catch the plan when it does multiple inserts. My guess is that it's being executed twice.

For question 1, it looks fine to me. You could put in more exact error handling,  but overall I would not say it's bad. I really like this template by spaghetti dba.


Insert self referencing entry into SQL Server I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine.

But People can also add themselves (signup). So the value in the created_by column should be the auto-incremented value of the id column. But that value is obviously not available until after the insert.

So I could either (a) make the reference not to check the values, (b) add a default value in the beginning or (c) make the column nullable. All options seem bad to me.

The MySQL's dialect has this:

SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');
SET FOREIGN_KEY_CHECKS = 1;

...but I could not find something similar for SQL Server's T-SQL.

Insert self referencing entry into SQL server

For the general question as per the title, you can add a direct circular reference in a simple insert such as

INSERT node
       (id , name  , parent_id)
VALUES (123, 'Test', 123      )

because the constraint is enforced considering all the new data: as long as the value the FX references exists once the statement is complete all is well. This is the same as inserting several values in a linked list:

INSERT node
       (id , name   , parent_id)
VALUES (101, 'Test1', 100      )
     , (102, 'Test2', 101      )
     , (103, 'Test3', 102      )

or an indirect circular reference:

INSERT node
       (id , name   , parent_id)
VALUES (201, 'Test5', 203      )
     , (202, 'Test6', 201      )
     , (203, 'Test7', 202      )

  So the value in the created_by column should be the auto-incremented value of the id column

This poses a problem because you don't know what the generated is before you insert, in fact there is no reliable way of knowing. For inserting individual rows you can use SCOPE_IDENTITY() to immediately update the new row:

INSERT node
       (name  )
VALUES ('Test')
-- and now make the circular reference
UPDATE node
SET    parent_id = SCOPE_IDENTITY()
WHERE  id        = SCOPE_IDENTITY()

In the above example if parent_id is a required column (declared NOT NULL with no DEFAULT) then you'll need to provide a dummy temporary valid value instead of leaving it out of the initial INSERT statement, like so:

INSERT node
       (name  , parent_id)
VALUES ('Test', 0        )
-- and now make the circular reference
UPDATE node
SET    parent_id = SCOPE_IDENTITY()
WHERE  id        = SCOPE_IDENTITY()

For dealing with inserts of multiple rows (unlikely in the circumstance you describe but common elsewhere) you can use the OUTPUT clause to read the IDs created for each row for further reference. I'll not go over that here as it is overkill for the current question, see https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql for more detail.

  So the value in the created_by column should be the auto-incremented value of the id column

It turns out that this can be done with a SEQUENCE in SQL Server if you have 2012 or above, which is a neater solution than the multi-statement options above, see SQLing4ever's answer for a worked example. Before 2012 this feature was not available, so you'll need to fall back to the methods in this answer if you need to support older instances of SQL Server.


How to change MongoDB slowOpThresholdMs in mongo shell I need to decrease the slowms trash hold to 50, can't find the way of doing that without restarting the mongod process.

The slowOpThresholdMs setting defines what constitutes a “slow” operation. To set the threshold which the profiler considers operations “slow” (and thus, included in the level 1 profiling data), you can configure slowOpThresholdMs at runtime as an argument to the db.setProfilingLevel() operation.

For Ref setProfilingLevel and
slowOpThresholdMs

Note:  The threshold for slow operations applies to the entire mongod instance. When you change the threshold, you change it for all databases on the instance.

For Example:

{ profile: 1, slowms: 200 }

Database profiling can impact database performance. Enable this option only after careful consideration