High CPU usage on SQL server - Slow queries Our MS SQL Server is using about 95% of the CPU-power.

After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.

When it's over 80%, every query is extremely slow.

Our website is dealing with alot of big queries, so some of them takes 45-60 seconds.
After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.

How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?

There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.

Most of them are already pretty well optimized.

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

SQL: Microsoft SQL Server 11.0.2100.60
OS: Windows Server 2012 x64
Processor: 2.30 GHz
RAM: 4.00 GB

Affinity does not "adjust the CPU usage" (e.g. in your case make the CPUs perform less work), it allows you to either turn off a CPU (perhaps to make it available to another instance on the same machine) or set a CPU to aid with I/O only. Even if you had multiple CPUs, you wouldn't be able to use the former to help with your goal, and it is impossible for us to guess on the latter because we don't know what is driving your CPU usage so high. It could be due to extremely poor indexing, excessive compilations, abundance of scalar UDFs, I/O thrashing, who knows? (And the reason I/O could be the cause is that if your database is larger than 3 GB or so, it will constantly have to swap data in and out of buffer pool memory, and this takes its toll on CPU.)

CPU cache, also, is a rabbit hole you don't need to be going down. I highly doubt your CPU is thrashing at 95% because of problems with your CPU cache.

To help narrow down the source of CPU pressure, and assuming you're using stored procedures, you can take a look at this diagnostic query from Glenn Berry (sourced from here) - make sure you run it in the context of the right database:

-- Top Cached SPs By Total Worker time (SQL Server 2012).
-- Worker time relates to CPU cost  (Query 44) (SP Worker Time)

SELECT TOP (25)
  p.name AS [SP Name],
  qs.total_worker_time AS [TotalWorkerTime],
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
  qs.execution_count,
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
    AS [Calls/Second],
  qs.total_elapsed_time,
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

If you're not using stored procedures, then this example from John Samson can help isolate ad hoc queries (sourced from here):

SELECT TOP (25)
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can also take a look at Adam Machanic's sp_WhoIsActive, a stored procedure that can quickly analyze all of the currently running queries, and allow you to sort it however you want (e.g. in your case @sort_order = '[CPU] DESC').

The first thing I would do, though - particularly if this really is mission critical for search and rescue teams - is buy better hardware. You should have more CPUs and more RAM to service your application. You also absolutely need better high availability (e.g. clustering, mirroring or Availability Groups). There is no reason that a reboot of a physical machine should take your application completely offline - we have better solutions for that problem. And finally, I presume this "server" only has one spinny disk drive. This means that all I/O - from the OS, from SQL Server data files, log files, tempdb, etc. all go through a single controller and share read/write activity on a single drive. Get more disks. Get SSDs if/where you can. Use RAID and try to spread the I/O out as much as possible.

That all said, throwing hardware at the problem is not going to be the only part of the fix. You need to isolate exactly what is causing excessive CPU usage and then attack those problems no matter what hardware you're on.

Also see this StackOverflow question for some other ideas:

https://stackoverflow.com/questions/945063/how-do-i-find-out-what-is-hammering-my-sql-server


Should I create new table for each User to store their product List? I'm creating a Web based billing and inventory management system. I need to store the product details such as name, price, stock left and also want to store details of every bill for each user uniquely. Should I create separate table for every user to store these information?

Creating a unique table for every user to hold their billing/purchase details would be an absolute nightmare to maintain.

It would be best to create different tables for each entity. For example, you could create one for all users, one for all bills/purchases, and one for all products. You can connect the user table and billing table by a user ID. In the billing table, each row can have a user ID to signify which user that purchase record belongs to. The user ID would be a reference back to the user table. The billing table should also have a reference to a products table which would hold the product name, stock left, etc.

This is just an example though and depending on your system design, you may need more tables.


calling mysql procedure return empty result when i call procedure it gives me empty result

   call hussian('prstate','table_1','column1,column2,column3',1)

it run but gives empty result, please help me...

 DELIMITER //

 CREATE PROCEDURE hussain(IN src_col varchar(20),
                     IN tblname varchar(100),
                     IN col_names varchar(100),
                     IN _leadid int)

 BEGIN
    IF (col_names  = 'column1' AND col_names  = 'column2'  AND col_names = 'column3')
THEN

    SET @sql = CONCAT('insert into ', tblname, '(',col_names,')', ' select ' ,src_col,',','firstname,lastname from tbllead where id = ',_leadid);
    PREPARE s1 from @sql;
     EXECUTE s1;
    DEALLOCATE PREPARE s1;
 END IF;
  END //
  DELIMITER ;

it insert data into table when i romove if condition.....

Basically

'column1' 'column2' 'column3' is different than 'column1,column2,column3'

So, you must compare the whole string:

 BEGIN
IF (col_names  = 'column1,column2,column3')
THEN
.
.
.

If you want to compare each variable separately, then you must pass them separate in your procedure call:

Call:

call hussian('prstate','table_1','column1','column2','column3',1)

Procedure:

CREATE PROCEDURE hussain(IN src_col varchar(20),
                         IN tblname varchar(100),
                         IN col_names1 varchar(100),
                         IN col_names2 varchar(100),
                         IN col_names3 varchar(100),
                         IN _leadid int)


Controlling the order in which merge articles are applied by snapshot at initialization? I have a replication scheme that was created several months ago.  Since that time the schema has had a variety of changes.  All existing subscriptions were updated as these changes were made as the publication is set to push schema changes as well.

Recently however when trying to add a new subscription the initial snapshot fails to apply.  The cause of the error is that two different views fail to apply as tables they rely on have not been created by the snapshot yet.

I know that I can specify processing order of articles via the sp_changemergearticle stored procedure.  But as I understand... the processing order setting here is for specifying the order in which data changes are applied to subscribers during sync.  Is there a way to specify the order in which articles are applied when a new subscription is initialized?  Does it use the same setting?

I figured that generating a new snapshot would cause the engine to figure out the proper order but in the case of my current publication it did not.  The new subscription fails to apply the snapshot even after I have generated a new snapshot.  It tells me the exact script that fails and when applying by hand it's obvious that it's failing because a table needed by the view does not yet exist.

What is the best way to fix this?  Does setting the processing order of an article affect the schema order as well as the data order or do I need to look somewhere else to set the order that articles are built on new subscriptions.

From what I can tell the processing order setting has no effect whatsoever on the order in which the snapshot applies its articles during initialization.  Mostly because currently all my articles have a value of 0 for their processing order.  So, is this something I can control or am I at the mercy of the snapshot generation process?

Yes, utilizing sp_changemergearticle to change the processing order will alleviate this issue.


Data Flow Transform Not Automatically Mapping Columns I'm in the midst of creating a set of SSIS 2014 packages (using Visual Studio 2013) importing data from 50 tables. Since most of the tables are being filtered on the same column and I'm importing all columns into my staging area, I opted for a reusable design where my package name matches the table name, my OLE DB source query is defined with an expression-based variable (starting with SELECT * FROM <tablename>...) and my OLE DB destination table is also defined with an expression-based variable (in this case schema.tablename).

Both the source query and target table have matching column names and match in most cases on data types and sizes.

As I'm cloning my packages for each table, I copy/paste a previously-created package and visually inspect the mappings to confirm the expressions and the data flow mappings are valid for the new package name. I'm seeing that the expressions are working as planned, but I have to manually do the drag/drop mapping of most columns in the destination editor.

Is this expected behavior of the editor as the design metadata of the original package is invalid under the new package name and is there a way to have Visual Studio delete and remap all columns in the data flow based on column name?

Copying and pasting SSIS packages can lead to a few issues. It sounds like you have simple packages - a dataflow with a source to destination. Generally, those don't have the hangup I've run into but I have seen situations where people were inconsistent in their definition of data types. Email - varchar(40), varchar(80), varchar(120), nvarchar(256) all in the same database, just in different tables. What can happen is that you first build your package using the 80 size. When you copy/paste and fix your source table, the editor may not pick up on the data size change when you goto the 40 table because it fits within the old sizing. But I've also seen the reverse - it might not pick up the increased size. Resolution isn't horrific, just change your query to select 1 AS x, let it set the metadata and then fix your query back to the proper select.

As Dave suggested, this task is ideal for an entry level Biml task. In fact, as along as you don't mind not having your explicit source query, my Biml replicate-o-matic is pretty much exactly what you're describing. Earlier incarnation is on SO and I'm linking to it to hedge against link rot.

If you like your approach, you can keep your approach. But, save yourself the clicking and dragging in your destination and replace it with six clicks: three  right, three left. In your mappings tab, right click to bring up this context sensitive menu

Left click "Select All Mappings"

Right click and the left click "Delete Selected Mappings"

Right click and then Left click "Map Items by Matching Names"


What is the actual difference between innodb_autoinc_lock_mode 0,1,2 I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.

I see in "0" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. In this case it doesn't jump the Auto Increment numbers.

Also I see in "1" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. And then it sets the max(AI_column)+some_creepy_jump.

But I see in "2" option, it doesn't lock the entire table. Instead it keeps inserts for each process simultaneously and inserting records for which ever request falls in randomly and ends all threads with average time around (1.21 sec for  7 parellel threads using load data having 1000000 record each). In this case it has multiple transactions in mixed order. And then it sets the max(AI_column)+some_creepy_jump atlast.

I'm using mysql 5.1.61 .

My question is what is the use of option 1?
And why does it stay as default later versions of Mysql 5.1.22 ?
Have anyone comeaccross any disasters with option 2?

As my project demands multiple processes using load data for a table. And I have tested the above options and finalized to go with option 2. Kindly correct me if my experiments are wrong.

What replication method are you using?  Option 2 is fine with ROW based, but for STATEMENT replication your auto inc values on the slave are not guaranteed to be the same.

Option 2 also means that your auto inc values probably won't be consecutive, but maybe that does not matter to you.

Option 1 is the default because it is the safest method with STATEMENT based replication (which I think is the default).  It is more scalable than option 0 since it only does a table level auto inc lock for bulk inserts but it still provides consecutive auto inc values with no gaps.  Some folks rely upon that behavior.

Sounds like for your use case you're fine with the setting you have selected, as long as your replication settings are OK.

Hope that helps.


Why does In-Memory feature not support foreign keys? Why does Sql-Server 2014 In-Memory feature not support primary/foreign key relations? Can anyone explain what the problem with foreign key relationships and in-memory is?

I guess just and only this limitation alone may be a show stopper for 90 or more percent of all Sql-Server customers for usign the in-memory feature.
Does anyone know if this will be added in future versions?

Because they had to ship the feature. If they waited until it was complete, we'd still be waiting for it, and all of those customers who didn't need foreign keys (or were willing to sacrifice them temporarily for the performance gains) would never have been able to deploy.

As with many large features with deep roots into the engine (remember the first iteration of ColumnStore? How about the limited surface area in the inaugural version of Azure SQL Database?), the v1 always has a limited amount of features.

They come around later, though. For example, in SQL Server 2016, you will be able to have a foreign key reference to a primary key constraint (but I don't believe to a unique constraint). You can see a lot of the enhancements that will be available in "v2" in the following posts:

SQL Server 2016 : In-Memory OLTP Enhancements
In-Memory OLTP - what's new in SQL2016 CTP3
SQL Server 2016 Community Technology Preview 3.1 is available


Is configuring Database mail considered as a security threat? Is configuring Database mail and sending email using sp_send_dbmail considered a security threat?

In a vacuum, any activated feature is an additional attack vector (or, additional "surface area" for the system). That said, I've never read or heard of any attack or exploit using DB Mail.


SQL query for next occurrence of specific date I am working on a function for our database that contains contract data. Each contract expires on a certain date, and then each contract has an expiration type that determines what happens after that primary expiration date.

For example, one expiration type is Year-to-Year. So, if the contract expires on 6/1/2016, after that date, the contract again comes up for expiration on 6/1/2017. My query that handles this expiration type is as follows:

SELECT next_dt
  FROM (SELECT add_months('1-JUN-2016', rownum * 12) next_dt
      FROM all_objects
     WHERE rownum <= 500
     ORDER BY 1)
 WHERE next_dt > trunc(SYSDATE)
   AND rownum = 1;

However, I have one expiration type that says after the initial expiration, the contract again expires the next June 1 or December 1. So, if the contract initially expires on 10/1/2015, the contract comes up for expiration again on 12/1/2015. If it expires on 12/5/2014, it comes up for expiration again on 6/1/2015.

I'm struggling with how to do this. Again, this is a function so we're not limited to a single query to return the desired result.

Any help would be appreciated. Using Oracle 11g.

Overly complex but doesn't need using any other tables:

SELECT ADD_MONTHS(d, 6-MOD(1+MONTHS_BETWEEN(d, TRUNC(d, 'year')),6)) AS next_dt
FROM
  ( SELECT TRUNC(dt, 'month') AS d
    FROM <table>
  ) x ;

Tested at SQL-Fiddle


How to choose working database in DB2? In SQL server I can use the USE statement to switch between working databases. Is there a similar command for DB2?

The concept of a "database" in SQL Server is much closer to a schema in DB2.  An entire MS SQL database engine is much closer to a DB2 database:

master database <======> SYSIBM/SYSCAT schema
msdb   database <======> SYSPROC       schema

It's not a perfect comparison; there are a number of differences:

DB2 does not have an equivalent to the model database.
tempdb doesn't really map to a schema; its purpose is handled by system temporary tablespace(s) like TEMPSPACE1.
Each database in MS SQL has separate log files, where DB2 uses a single set of transaction logs for the entire database

Therefore, technically the closest equivalent to MS SQL's USE statement is SET SCHEMA.  Issuing the SET SCHEMA x statement tells DB2 where to look for unqualified objects.

However, it's not a requirement to issue the SET SCHEMA statement if you fully qualify your object names.  The following statements:

set schema x;
select c1, c2 from mytable;

are equivalent to:

select c1, c2 from x.mytable

Using set schema simply allows you to use unqualified objects.


SQL Server 2008 R2 services not coming online I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition.

Before applying CU-14 package on the server, I stopped the DB Engine services from Configuration Manager. I started the patching and this finished successfully without any issue and I rebooted the machine too.

The thing is that Services were not coming online even if it was in automatic mode. I started the services and tried to connect and I got an error message:

  Server is in script update mode

So I waited for some time because it is in update in progress. After that when I connected to that instance I was getting

  network related instance specific error

And interestingly SQL Server services was stopped automatically, again I was trying to start the services and the same scenario is happening repeatedly and I even uninstalled the CU-14 package from the server as well.

As a workaround I included "-T902" flag at the end of the parameters from config manager and started the services successfully and I am able to connect. But running the service by using the flag is not a good workaround.

Still I am experiencing the issue and please suggest: What are the steps we have to perform to resolve this issue?

Thanks for understanding!

Issue was resolved.. Actually improper build of sql server caused for issue. Check the registry key level for mssql server for what the default data, backup path existing in the host.. Go for same path in the explorer and check whether the database files existing in the same folder.. if not then msagent certificate will be not created in that path.

Eg. Create a sample database in the server without mentioning a physical name then you will get a operating system error saying wrong path.

So after applying the patch also one more msagent certificate will be created showing with version number..  this is the root cause of the issue


Viewing the SQL of a view from a backup set I have a full backup of my database from 4 days ago. It is a standard SQL 2008 .bak file. What I'd like to know is if its possible to view the SQL to a View that is in the database I backed up without restoring the entire database?

Using native tools, no.  Idera Virtual Database will allow you to read the backup file in the manner you're describing.  Other tools, such as SQL Litespeed and Red Gate SQL Backup will allow you to do object level recovery.  However, you will have to pay for these tools (though you might be able to get away with using the trials short term).

Otherwise, you'll need to restore the database file somewhere.  This is why it is important to also back up your schema to script files or store your database object definitions in source control.


Audit full text activity I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.

You have to use DDL event groups that can be used to run a DDL trigger or an event notification, and also the Transact-SQL statements they cover.

Refer BOL for more details on what can be tracked.

I have posted an answer here for How to get notified when a deadlock occurs using Event notification. The concept is similar and can be applied to audit Full text catalog.


sql command utility version is not working I have two versions of SQL Command Utility installed on my system as mentioned below(I checked the versions by going to Control Pannel->ProgramsandFeatures).

Microsoft Command Line Utilities 11 for SQL Server
Microsoft Command Line Utilities 15 for SQL Server

However, when I go and check the version in command prompt by typing 'sqlcmd -?' it shows the Version 12.0.2000.8 NT.
Could anyone advise why the system is not taking the version 15 and how it will utilize version 15? Since I want to use '-G' switch but it is not available in the current installed version (12.0.2000.8 NT).

If you don't qualify the SQLCMD.EXE location at the command prompt, it will use the first one found in your search path. So you need to either fully qualify the path to the executable for the desired version or modify your search path so that the newer version directory is before the older one.


Database user permissions: GRANT VIEW DEFINITION but DENY on some object types I'm trying to figure out a combination of permissions that will allow a partner to see basic db schema but not expose objects including code. So,

VIEW and SELECT on TABLES (including keys)
Prevent/DENY on Views, Stored Procs, Functions.

I've thought about going through and denying on individual objects, but this won't protect any that are newly created.

Added consideration - I need to get this to work in SQL Azure.

You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.

Below is an example to get you started :

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the databas
GRANT
      SELECT

ON SCHEMA::dbo
      TO TableSelector
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector


How to determine the collation of a table in PostgreSQL? I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search.

Can anyone tell me how I would check this?

To check for non-default collations on columns, you can use the following query:

select table_schema,
       table_name,
       column_name,
       collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
         table_name,
         ordinal_position;

To find the collation of the database, you need to query pg_database:

select datname,
       datcollate
from pg_database;

Here are the relevant pages of the PostgreSQL manual:

http://www.postgresql.org/docs/current/static/infoschema-columns.html
http://www.postgresql.org/docs/current/static/infoschema-collations.html
http://www.postgresql.org/docs/current/static/catalog-pg-database.html


SQL Server Replication Agent Permission Error messages:
  The schema script 'teste2_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

  The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VUZIQ_ENTP\MSSQL\ReplData\unc\ADSERVER$VUZIQ_ENTP_TESTE2_TESTE2\20130626155609\teste2_2.sch' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
  Get help:

I have one sql from ip 199.168.***.*** working as Publication and another server 64.37.**.**. working as Subscription. The local Subscriptions of 199.168.***.*** is working fine but the external access from 64.37.**.** cant load file.

How can I add a login for 64.37.**.** server to have read/write permission to 199.168.***.*** server ?

OS error 3 means path not found.  You must specify a UNC network share such as \\computername\snapshot rather than a local path when using pull subscriptions and/or a remote Distributor.  This is covered in Secure the Snapshot Folder.  I have also covered this in a blog post found here or here.

Also, ensure your replication agent process account has read permissions on the snapshot share.  This can be done by right-clicking on the snapshot folder -> Properties, and adding the user on the Security tab with Read permissions.

If you have any questions please let me know.


Questions Concerning Database Snapshots One of our divisions is running SQL Server 2012 SP1 Enterprise edition for no particular reason (I know, I know... the decision was made before my time and they are resistant to change). What I'm curious about, since I have a lack of experience with Enterprise ed., is why database snapshots would be created when no one at the division has any idea why either. Unbeknownst to me, they've been manually deleting these for years.

As I understand it database snapshots can only be created via T-SQL (though I believe DBCC CHECKDB may create them behind the scenes) so what exactly is happening here? Is there a good way to track down the process that is creating the snapshots? Is there a setting I've overlooked?

The instance in question is a dedicated SharePoint instance.
The server itself has space constraints making the snapshots that much more of an issue.

When you create a database snapshot, it logs a message in the Error Log that says 'starting up database...' and the database name. You could filter the error log on those messages.

CREATE DATABASE test_snap ON
(
NAME = StackOverflow,
FILENAME ='D:\Data\stack_snap.mdf'
) AS SNAPSHOT OF StackOverflow;

When you run checkdb, the filename looks like this, and it's not listed under the Database Snapshots folder.

As for tracking the process down, I'd probably create a DDL Trigger, and use EVENTDATA() to figure it out.

CREATE TRIGGER database_create
ON ALL SERVER
FOR CREATE_DATABASE ...


Database growing like crazy - what do I do? I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ago (go-live), and has grown steadily to 80gb+. They did schedule a maintenance job to Shrink all DB's each Sunday night, limit 50mb, free space 10%.

At it's current rate of growth the server's drive space will be maxed in less than 90 days, and I'll be forced to either upgrade the server or move the DB to a SAN. Overall system performance is terrible, system is just plain slow.

When I run the "disk usage by top tables" report I see the following:

Number of records: 115,117,737
Reserved (KB): 83,609,584
Data (KB): 81,867,248
Indexes (KB): 52,263,296
Unused (KB) 1,742,336

I am waiting to hear back from Epicor regarding proper care and feeding of their system, but the Indexing seems to be using an insane amount of space. As stated, I only recently took over this system and I'm trying to figure it out as quickly as I can.

Any help would be appreciated.

EDIT: Warning to anyone who reads this. Dropped the duplicate indexes as described in the answers here completed hosed by test Epicor 9 database. E9 apparently uses indexes in a non-standard way. It has a Progress layer which optimizes the indexes for Progress and 4GL (aka ABL) code. I can't state definitively that this caused the problem, but it seems the most likely culprit. Attempting to log into E9 Test gives a "SQL Scripts need to run" error and the database appears to be stuck in an ALTER state.

Take a look at this blog Removing Duplicate Indexes by Kimberly Tripp and use the scripts to check if you have a completely redundant/duplicate indexes


Postgresql: large table or union of small tables I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.

From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?

Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.

Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.


Oracle not able to add Default value in PL/SQL I trying to create table in PL/SQL, the problem is the at DEFAULT ' '.

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||' (STORE VARCHAR2(4) DEFAULT ' ' NOT NULL)';

END CREATE_TABLE ;

Error:

   PLS-00103: Encountered the symbol " NOT NULL)" when expecting one of the following:

  * & = - + ; < / > at in is mod remainder not rem return
  returning <an exponent (**)> <> or != or ~= >= <= <> and or
  like LIKE2_ LIKE4_ LIKEC_ between into using || bulk member
  SUBMULTISET_
  The symbol "*" was substituted for " NOT NULL)" to continue.

But i can add the default if i do it in sql tools. How can i add the default value in pl/sql?

CREATE TABLE Inventory
(
  STORE VARCHAR2(4) DEFAULT ' ' NOT NULL,
)

Single quotes inside single quotes need to be duplicated: default '' '' not null

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||
                    ' (STORE VARCHAR2(4) DEFAULT '' '' NOT NULL)';

END CREATE_TABLE ;

See the manual for details and alternative ways of doing that:

Text Literals | docs.oracle.com


Local Backup from Amazon RDS PostgreSQL database I have a PostgreSQL database on an Amazon RDS instance.

I want to have a local dump of this instance.

So far, I've tried with this:

PGPASSWORD="Password" pg_dump -h mydb.xxxxxx.us-east-1.rds.amazonaws.com -p 5432

But it keeps throwing an error, saying that the server refuse the connection, and check if the server is actually running on port 5432, which is actually doing.

All my credentials and DB path/codes are correct.

You must be running in a Security Group firewall problem.

Go to your RDS Dashboard, select Instances and open the instance you want to connect to.
Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups panel.
Select your security group and open the Inbound Tab in the lower panel.
Click on Edit and Add Rule. Don't forget to set the Port Range to 5432 and select My IP in the source.
Click on Save.
You should be able to connect to your RDS Instance from your local IP.


Can Sql Server Backups be replaced by a netapp SnapManager? In the process of getting some new magic and high speed SAN devices I was told by those crazy magic-SAN-things-administrating IT people that it will be obsolete to perform ANY Sql-Server Backups in future, as soon we start using this netapp snapmanager snapshot magic. Unfortunately I was not involved in product selection process or any meetings or demos.

So the one person that still is very sceptic about this and not yet able to be a part of all those excited and euphoric celebrations is - me...   :-)

I do not really know much about database snapshots and (because) did not really use it so far, and I do not know if this netapp snapshot is the same snapshot/technology that sql supports... But if so, I fear that any of those snapshots are pretty useless as soon we have lost our source database, right?

So, is it really true, that we can replace database backups, transaction log backups and any other backups using those products and technology and still stay able to respond to point in time recovery requests for our databases?

Not at all. A SAN snapshot is a bitwise copy of raw disk state. If and only if all the responsibility for business continuity is on SAN storage team, this might be acceptable. For most of the business cases, this will present such RPOs and RTOs that the solution is not going to be feasible. What's more, restoring a SAN snapshot to a stable state is problematic. If the server was up and running during the snapshot, the server will return to state like someone hit a reset button. That is, a dirty filesystem, incomplete transactions and stuff. To get a clean snapshot, the server must be shut down a priori - and that's a business outage.

With SAN snapshotting only, you'd miss at least point in time recovery, as all the databases must be in simple recovery model due missing log backups. This is a killer for most recovery objectives.

Denny Cherry has written a whole article about failure points.


MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column? Here's what I'm trying to achieve: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns.

For example, the incoming query is:

update table set column_1 = 'a', column_2 = 'b', column_int = 100 where id = 1

I want to use trigger (or is there any other way?) to execute this query in the way:

update table set column_1 = 'a' where id = 1;
update table set column_2 = 'b' where id = 1;
update table set column_int = 100 where id = 1;

And of course after the three query we don't want to run the original update query (skip it to avoid run the query twice on the table).

Is this possible? The use case is that in MySQL you can update columns in a view, as long as the columns are from just 1 base table. So if I update two columns from different underlying base table MySQL will throw error, hence I want to use trigger to divide them.

I suggest that using a VIEW is the wrong starting point, and that TRIGGERs are just adding complication.

Instead of using Views and Triggers, have a "database layer" that segregates the Application code from the Database.

The layer could be written as subroutines in your programming language or as Stored Routines in MySQL.

The application would ask the layer to perform, say, an API like Set_Foobar('a', 'b', '100') and the function would do whatever it takes.  Under the hood might be one UPDATE today, maybe 2 or 3 tomorrow.

With this technique, you have the freedom to rearrange the schema without touching the application code.  But you will have to change the layer.  And that will be easier than trying out how to implement the Trigger you are asking about.


Why do I need to use a sub query to filter down a grouped select? If I do this --

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name

I get this error --

  Msg 207, Level 16, State 1, Line 17 Invalid column name 'DE'.

If I do this --

SELECT Name, DE FROM (
    SELECT dv.Name
          ,MAX(hb.[DateEntered]) as DE
    FROM
        [Devices] as dv
        INNER JOIN
        [Heartbeats] as hb ON hb.DeviceID = dv.ID
    GROUP BY dv.Name
) as tmp WHERE tmp.DE < '2013-03-04'

it works as expected.

Can someone explain why I need to nest my main query as a subquery to limit my data set?

Also, is there maybe a better way to achieve the goal here? Retrieve all records from one table, and the single "top" related record ordered by [DateEntered] descending?

You can't reference an alias in the WHERE clause - this is just because of the order in which SQL Server parses the statement.

There have been many discussions about this here and on StackOverflow. A couple of examples that give some background:

https://dba.stackexchange.com/questions/19762/why-is-the-select-clause-listed-first/

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

An alternative would be:

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';


Postgres: Read CONSTRAINTS definition I'm able to read CONSTRAINTS in the Information Schema, but how can I get all parameters associated with that CONSTRAINTS to get a full CONSTRAINTS definition by using SQL ?
For example,

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This will give me list of CONSTRAINTS, but let say, I got a CONSTRAINTS FOREIGN KEY  "user_role_id" and its full definition (via pgAdmin) is

ALTER TABLE app.user
ADD CONSTRAINT user_role_id FOREIGN KEY (role_id)
REFERENCES app.role (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE;

So how do I get this definition by just writing SQL that give me all those options associated with that CONSTRAINTS?

This solve my problem

    SELECT conrelid::regclass AS table_from
      ,conname
      ,pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;


Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs? Let's say I want to escape these to see a similar string

SELECT *
FROM ( VALUES
  (E'\r\n\t FOO BAR \t\n\r')
) AS t(str);

How do I get the c-style escapes, \r\n\t FOO BAR \t\n\r back out?

SELECT *
FROM ( VALUES
  (E'foo\nfoo',E'\n\tBAR\t','baz'),
  (E'quz','qaz','quz')
) AS t(x,y,z);
  x  |        y         |  z
-----+------------------+-----
 foo+|                 +| baz
 foo |         BAR      |
 quz | qaz              | quz
(2 rows)

Want instead..

  x       |        y         |  z
----------+------------------+-----
 foo\nfoo | \n\tBAR          | baz
 quz      | qaz              | quz
(2 rows)

You can use string escape syntax and replace(string,substring,replacement) function like below.

Please note it's heavily dependent on standard_conforming_strings setting. Should be on. You can find details here, here and here.

postgres=# \d t
     Table "public.t"
 Column | Type | Modifiers
--------+------+-----------
 s      | text |

postgres=# SELECT s, md5( s ), replace( replace( replace( s, E'\n', '\n' ), E'\t', '\t' ), E'\r', '\r' ) FROM t;
    s     |               md5                |  replace
----------+----------------------------------+------------
 newline:+| 75c093ed14f1239a3510006326a1a260 | newline:\n
          |                                  |
 tab:     | 355393c5cab9aa324c6ec90682b13d7e | tab:\t
 cr:\r    | 094911687582e40cfeb6217f8c760543 | cr:\r
(3 rows)


mysqldump, preserve update_time and modify_time attributes as listed by show table status from Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?

If not, is there an option that does this?

From the manual it looks like mysqldump preserves this data if you export to XML.
EDIT: Well the data is included in the export. Whether it's read in at the other end I'm not sure.

Is there a way to do this with a normal .sql dump?

There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamica entities. For instance, look at INFORMATION_SCHEMA.TABLES:

mysql> show create table INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(80) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

It is a temporary table. If you restart mysql, how does it get populated? Evidently, it is popoulated during mysql startup. What would be included? Retrieve OS metadata of the table.

Let's look at mysql.user from the OS

[root@*** ~]# ls -l /var/lib/mysql/mysql/user.*
-rw-rw---- 1 mysql mysql 10466 Jun  5  2012 /var/lib/mysql/mysql/user.frm
-rw-rw---- 1 mysql mysql  2144 Jul 15 23:29 /var/lib/mysql/mysql/user.MYD
-rw-rw---- 1 mysql mysql  2048 Dec 10 18:25 /var/lib/mysql/mysql/user.MYI

Let's look at mysql.user from the mysql client:

mysql> select * from information_schema.tables
    -> where table_schema='mysql' and table_name='user'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 31
 AVG_ROW_LENGTH: 69
    DATA_LENGTH: 2144
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 2048
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2012-06-05 23:45:23
    UPDATE_TIME: 2012-07-15 23:29:05
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)

mysql>

Please notice the following

The Creation Time

file datetime for .frm is Jun  5  2012
CREATE_TIME: 2012-06-05 23:45:23

The Update Time

file datetime for .MYD is Jul 15 23:29
UPDATE_TIME: 2012-07-15 23:29:05

The OS metadata for mysql.user is loaded into INFORMATION_SCHEMA.TABLES on mysql startup and is updated through INSERTs, UPDATEs and DELETEs.

SUMMARY

While you can mysqldump INFORMATION_SCHEMA.TABLES, it is impossible to manually reload it. Theoretically, you could shutdown mysql, tweek the OS clock, tweek every file, then startup mysql. It's not worth the effort.


postgresql backup and recovery I’m trying to set up backup strategy for a production postgreSQL DB.
It will have large amount of data and it should be running 24 X 7.
Could you recommend some backup & recovery strategies that can meet the following criteria?

Large amount data (about over 200GB).
Hot backup (online backup)
Minimum Impact on the DB performance.
Minimum restore time.
Allow PITR(Point-In-Time Recovery)

Can we execute backup with above criteria on slave server of replication?
If you know backup strategy using storage snapshot, Could you let me know?

It sounds like you'd be best suited by using a physical base backup + WAL archiving, with regularly updated snapshots of the base backup. I strongly recommend taking regular dumps anyway.

Using newer PostgreSQL versions (9.2 and up, IIRC) you can take fresh base backups from a replica server so you don't have to disrupt the master.

File-system or logical volume level snapshot backups work fine with PostgreSQL so long as your snapshots are atomic. Restoring one is like starting PostgreSQL back up after unexpected power loss or OS reboot, not a big deal.

See also:

The manual
A blog entry I wrote on this topic
Postgres Backup queries on AWS after reading Postgres docs
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
PostgreSQL "freeze"/"unfreeze" command equivalents
How to do incremental backup every hour in Postgres?
Running pg_dump on a hot standby server?
PostgreSQL crash recovery


Restoring a backup of a database from SQL Server 2014 into SQL Server 2012 Is it possible to load a backup of SQL server 2014db into SQL server 2012?

Trying to import/restore is throwing backward compatibility issues.

Thanks

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server (2014) down to an older version (2012) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances
otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool
or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.


Browser service in sql server clustering Straightforward this one - when failing over from one sql server 2008 node to another, should the browser service be running on the passive node ? At the moment, on attempting to failover, a an error is produced saying that certain clustering related dlls can't be found. The dlls are present, I just wondered whether this browser service was stopping access.

There really isn't a short answer because there are a few hidden questions in the question.

A few thoughts to help here:

1.) The Browser service is not cluster aware, so it generally would be just running on each node. The browser is really used to handle incoming connections to a SQL instance. When you don't have a fixed port, are using named instances and in other situations the browser handles the "finding" of the instance a client desires to connect to. So if you have it running on the active node and it is being used to direct connections, I would make sure it is automatic and running on each node.

2.) That said the browser shouldn't prevent DLLs from being found or take any part in preventing or allowing a failover. So the issue you are having with failing over is most likely not related to the browser but something else. Instead you should be looking at things like - Have you failed over before? Is this a new install of a second node? Have there been any required restarts missed on that node? Did the install throw up any errors? Are there issues in the clustering logs? Can you post the exact error you are receiving? Have you searched for that exact error?


Non-clustered index with WHERE clause I have a large table (around 100 million rows). I am trying to optimize searches.  Most of my searches use two columns: bar nvarchar(64) and foo nvarchar(64).
I am only interested in foo when:
foo = 'string01'
OR
foo = 'string02'
OR
foo IS NULL

I was thinking on creating the following non clustered index:
CREATE INDEX ixbar
ON myTable (bar)
INCLUDE (foo)
WHERE foo IN ('string01', 'string02') OR foo IS NULL

But the OR statement is not allowed in a WHERE clause for an index. This makes me think that probably I am not approaching this correctly.
I would appreciate any suggestion on the most efficient way of doing this.
Thank you!

Handling NULLs is tricky, sure. You could get around needing two indexes with either a computed column, or by updating the NULLs in your current column to some canary value.
ALTER TABLE dbo.MyTable ADD foofighter AS ISNULL(foo, 'canarystring');

CREATE INDEX fooey ON dbo.MyTable (bar) INCLUDE(foofighter)
WHERE foofighter IN ('string01', 'string02', 'canarystring');

Adding non-persisted computed columns has fewer locking implications up front, and you can still index them any which way makes you happy.


How to design a database with rare multiple values I have a "user" table, with many attributes including "nationality".
the user can have multiple nationalities, so it sounds as if I have to design a new table to store this multiple value.

but what if I stored the first nationality in "user" table, and each of the remaining nationalities belonging to the same user would go to the nationality table.

I am thinking about this solution since it's pretty rare to have a user with multiple nationalities.. so, I can keep my design clean this way.

Is this feasible? is it a bad design?

If it was me who doing the design what I will do is create a table with name nationality each nationality have ID  ...and since the the user have multiple nationality ...in the user table I will create two columns (foreign key) for nationality nationality1 and nationality2 referencing the nationality table .

the above scenario in case for two nationality .

for multiple nationality (unlimited) check the below scenario

the scenario for this situation will be similar to Customer and orders (one-to-many) ...one customers have many orders ...you will need 3 tables user nationality user_nationality in the user_nationality table you will the following columns id,user_id,nationality_id ...the user_id is foreign key column referencing the user_id in the user table and the nationality_id is a foreign key referencing nationality_id for the nationality table .

hope that will help .


Availability Group Read Only Routing Not Working for SQL Logins We have following config of Availability Group:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name

Recently we have setup Read Only Routing (ROR)
Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433

Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3

It worked fine when tested in SSMS using Windows Authentication Login (Domain\MyLogin) with Options >> Additional Connection Parameters tab -> ApplicationIntent=ReadOnly

While server1 is primary, I connect in SSMS to AG Listener (sql-listener.domain.com) using above parameter and run select @@servername, and it shows server 2, which means ROR works

Issues/Questions:

When I try to connect in SSMS to AG Listener using SQL Authentication Login (SQLAuthLogin), and ApplicationIntent=ReadOnly parameter, and then run select @@servername, it shows me server1

>>> So in my case, ROR does not work with SQL Authentication Login
is there any way to fix this ?

add: double checked SQLAuthLogin's SID on server1 and server2 - SID is the same, SQLAuthLogin can login to both servers and query MyDatabase just fine

The issue was not in SQL or Windows Authentication methods, but in default_database setting
Found that for my Windows Authentication Login, default_database = [MyDatabase] (which participates in availability group), while my SQL Authentication Login has default_database = [master]
Because of above setting, while connecting in SSMS using Windows Authentication, Read Only Routing worked right after ApplicationIntent=ReadOnly was specified. For the SQL Authentication Login, not only ApplicationIntent=ReadOnly needs to be specified, but also it should contain Initial Catalog=[MyDatabase]
Adding Initial Catalog=[MyDatabase] fixed the issue, SQL Authentication Login could  connect and was directed to Read-Only secondary


How to journal tables in SQL? Say I have a C# script that creates 50 tables in MYLIB.

Currently, after running the script, I have to go into System i Navigator and manually attach the MYLIB/USRJRN to each of the tables' journaling.

How can I make my script do this instead?

You may start DB2 table journaling with the QSYS2.QCMDEXC procedure and a corresponding text of the STRJRNPF CL command as an argument. Something like this:

CALL QSYS2.QCMDEXC('STRJRNPF FILE(MYFILELIB/MYFILENAME) JRN(MYLIB/USRJRN) IMAGES(*BOTH) OMTJRNE(*OPNCLO)');


SQL Server Index: One new table joining to three old tables, how to? how can I index this?  In a legacy system, I need to store (new table) and search on three possible natural key combos.

I'm thinking my new table will be:

declare @TableA table (pk int identity(1,1) primary key
                      --.more columns
                      ,fk1    char(4) not null
                      ,fk2    char(2) not null
                      ,fk3    char(3) not null
                      ,fk_a1  char(5) null
                      ,fk_a2  char(2) null
                      ,fk_b1  char(6) null
                      ,fk_c1  char(1)
                      --more columns...

this new table needs to join to three different tables on these columns:

LegacyA: fk1, fk2, fk3, fk_a1, fk_a2
LegacyB: fk1, fk2, fk3, fk_b1
LegacyC: fk1, fk2, fk3, fk_c1

I'm thinking to add the following indices:

fk1, fk2, fk3, fk_a1, fk_a2
fk1, fk2, fk3, fk_b1
fk1, fk2, fk3, fk_c1

since I'll be searching and joining to LegacyA, LegacyB, LegacyC independently and separately, these indices should be used correct?  Does this look like a sound approach?

You could possibly just use the first index and "include" the fk_b1 and fk_c1 columns. It will depend on your data so you'll need to test, as mentioned previously.


Bad Practice to Store Calculated Data in Database? Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database.

Storing in the database avoids the need to calculate multiple times, but if an error is made then data needs to be updated rather than just changing the application level calculations.

I think the latter is better, but is there a general rule of thumb?

I need to, for example, calculate total daily nutritional intake of foods. So various portions of energy of foods. I can either calculate the portion energy based on the corresponding food and store the energy of each portion in the portions table OR I can calculate from the join with the corresponding food every time.

You can imagine if you had to calculate yearly averages, monthly averages, daily averages, etc. for a long period of time it could get quite unwieldy.

What about using materialized views that would get recomputed every time old data, say a week or older, gets updated based on a trigger or something along those lines?

If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database normalization.

Examples would include:

The extended cost on an invoice line that carries price and quantity fields.
The total cost of an invoice’s lines.

In the real world, we do sometimes choose to violate normalization. The motivation is usually for performance reasons. Never do so without much thought, and hopefully a consultation with another DBA or database developer. And always document thoroughly your decision and its specific motivation.

  is there a general rule of thumb?

Yes: Normalize, and test/profile.

Always start with a normalized design. Load with fake data, test performance. Verify any discovered bottleneck is indeed due to your on-the-fly calculations.


Help with Many to Many Table Data Entry I've got a question on how to "prettify" data entry and removal for a many to many relationship.
Imagine if you will a DB with 3 tables. Tables called Users, Roles, and RoleAssignment.
-- `Users`
CREATE TABLE `Users`
(
 `userID`   int NOT NULL AUTO_INCREMENT ,
 `userName` varchar(40) NOT NULL ,
 `realName` varchar(40) NOT NULL ,

PRIMARY KEY (`userID`),
UNIQUE KEY `username` (`username`)
) AUTO_INCREMENT=1;

-- `Roles`
CREATE TABLE `Roles`
(
 `roleID`       int AUTO_INCREMENT NOT NULL ,
 `roleName`     VARCHAR(40) NOT NULL ,

PRIMARY KEY (`roleID`)
);

-- `RoleAssignment`

CREATE TABLE `RoleAssignment`
(
 roleAssignID int NOT NULL AUTO_INCREMENT ,
 roleID       int NOT NULL ,
 userID       int NOT NULL ,

PRIMARY KEY (roleAssignID),
FOREIGN KEY (userID) REFERENCES Users(userID),
FOREIGN KEY (roleID) REFERENCES Roles(roleID)
) AUTO_INCREMENT=1;

This is a database where RoleAssignment is essentially a child table only meant to connect two Parent tables together. I made them this way because the user<->role relationship is many to many.
I found a way of getting this to output just relationships between users and their roles... Both queries output the same data but query 2 is better organized.
SELECT
    Users.userName,
    Users.realName,
    Roles.roleName
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID;

SELECT
Users.userName,
Users.realName,
GROUP_CONCAT(Roles.roleName)
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID
GROUP BY Users.userID;

Is there an easy way to add an entry to the RoleAssignment table by name instead of ID in a single query? i.e. "Add user jack to role moderator"? Or would I need to write a script to find the ID of the user, then the ID of the role, and finally add an entry to RoleAssignment?
Any help or guidance would be appreciated!

Even if you have a UNIQUE constraint on username.
You must replace all referenced Columns with the username and change the dataatype of column.
A text field is also usually slower that an Integer.
So when you see all negative points, there is no good reason to change it now.
Besides, what does it really matter, if you have a numeric id that you have to use to identify the user or another unique text filed.


Help understanding mongodb explain results I have an index on field f1. {f1:1}

Running an explain on the following query

db.coll.find()

says that the winning plan is a CollScan (expected)

However, for db.coll.find().sort({f1:1})

the explain says

"stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

Thank you

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

If you fully iterate both of your queries they will end up fetching all documents from the collection, but they take different approaches in terms of query planning and execution.

A collection scan means an index was not used and documents were retrieved in natural order (which is the undefined "order of documents on disk"). An index scan indicates an index was used regardless of whether that stage returns some or all documents in a collection.

If you review the full query explain output for a collection scan you should see totalKeysExamined is 0 and totalDocsExamined equal to the number of documents returned.

  db.coll.find().explain('executionStats')

The  winningPlan for your first query should be a simple COLLSCAN stage:

    "winningPlan" : {
        "stage" : "COLLSCAN",
        "direction" : "forward"
    }

  db.coll.find().sort({f1:1}).explain('executionStats')

In your second query example the index supports the requested sort order, so you should see totalKeysExamined and totalDocsExamined are equal to the number of documents returned (assuming that f1 is not not a multikey (array) index).

The winningPlan for this should be an index scan (IXSCAN) which iterates the f1 index in sorted order to fetch matching documents. You should see the IXSCAN as the inputStage for a FETCH:

    "winningPlan" : {
        "stage" : "FETCH",
        "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "f1" : 1
            },
            ...
    }

If the winning plan did not support the requested sort order, you would still see an IXSCAN or COLLSCAN but it would be part of an in-memory SORT stage (limited to ~32MB of documents):

    "winningPlan" : {
        "stage" : "SORT",
        "sortPattern" : {
            "f1" : 1
        },
        "inputStage" : {
            "stage" : "SORT_KEY_GENERATOR",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    ...
                },
                ...
            }
        }
    }


Matching a smaller piece of text in a local variable to a larger piece of text that I have pulled in a query I think this is a simple question, but google searching for 30 minutes was mostly wasted time as all I can find is matching a variable to a 'randompieceoftext'.

Anyways, suppose I have a local variable called @ServerName. This server name will be something like CCPWIQAUL. I need to match this server name to various path names, which is of the form:

serverName.something.somethingelse.com

These path names are pulled from a database, and will be in the column vManagedEntity.Path

How do I do something like this?

Is @ServerName is IN vManagedEntity.Path?

You can use the CHARINDEX() function to do this:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE CHARINDEX(@ServerName,Path) > 0

Or, you can craft a LIKE query:

Assuming you want to do a Starts With match on the Path field:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE @ServerName + '%'

If you want to do an Ends With, change to:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE '%' + @ServerName

Or, a Contains:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE '%' + @ServerName + '%'


Impact of switching a column's encoding from utf8 to utf8mb4 in MySQL Background Info

Let's say I have a table in MySQL that has +1 billion records. I have a column with that contains some description of a post. We don't index, search, or sort this column in any way.

Currently the description column has an encoding of utf8, which in MySQL doesn't support the full unicode standard. I want to add support for emojis, other languages, etc. so I need the column to be utf8mb4

Question

If I run something along the lines of:

ALTER TABLE
user_posts
CHANGE post_description
VARCHAR(300)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

What actually happens in the background, and would it have a massive performance impact during the operation on a very large table with (+1 billion rows)?

Since there is no index, sorting, or searching in this column, would it need to modify each record? oOr since the current utf8 records won't be changing will it just change the table schema. O(n) or O(1)

First, a syntax error:  it's MODIFY, not CHANGE, unless you are changing the column name, too.

I ran an experiment with only 1/2 million rows.  ALTER to change one column from utf8 to utf8mb4 took 1.6 seconds.  I conclude that the table was copied over; that is O(n).

When the developers added the various speedups for ALTER, I suspect they overlooked the case of utf8->utf8mb4, or decided that it was a risky special case -- what if later the charsets diverged?  Or the collations diverged?  Granted, without any index on the column, that should not matter.  (But that would be more more thing to test for before doing the Alter some faster way.)


DBeaver not showing any Comments/Remarks in MSSQL Recently, I am immigrating some data from Postgresql to a MSSQL2012 server. In SQLWorkbench, I can see comments on tables and columns in SQLServer.

But in DBeaver, I can't see any comment or remarks on any object. The Description column in Properties tab are always empty.
Is this a configuration issue, or is DBeaver just not ready for this ?

Both SQLWorkbench and DBeaver are using MS' JDBC driver.

The Microsoft JDBC driver does not return that information through the standard JDBC API. I think the main reason for that is, that the driver uses sp_columns internally (at least the last time I checked - which is a couple of years back) and that procedure doesn't return that information.

I guess DBeaver simply has no special code to retrieve the "extended properties" for tables or columns for SQL Server, whereas SQL Workbench/J "knows" those extended properties


Mysql - Opitmizing a range query with group by I have a table with the following definition:

CREATE TABLE `sales` (
  `saleId` varchar(100) NOT NULL,
  `orderId` varchar(100) DEFAULT NULL,
  `shopId` varchar(100) DEFAULT NULL,
  `fulfillableQuantity` int(11) DEFAULT NULL,
  `fulfillmentStatus` varchar(30) DEFAULT NULL,
  `grams` double DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `productExists` tinyint(1) DEFAULT NULL,
  `productId` varchar(100) DEFAULT NULL,
  `variantId` varchar(100) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `requiresShipping` tinyint(1) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `taxable` tinyint(1) DEFAULT NULL,
  `title` text,
  `totalDiscounts` double DEFAULT NULL,
  `variantTitle` text,
  `cogs` double DEFAULT NULL,
  `customerEmail` varchar(100) DEFAULT NULL,
  `discount` double DEFAULT NULL,
  `processedAt` varchar(100) DEFAULT NULL,
  `shippingCost` double DEFAULT NULL,
  `rowStatus` varchar(10) DEFAULT NULL,
  `processedAtNano` bigint(20) DEFAULT NULL,
  `lastUpdated` bigint(20) DEFAULT NULL,
  `productType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`saleId`),
  KEY `i_shopId_nano` (`shopId`,`processedAtNano`),
  KEY `i_sales_orderid` (`orderId`),
  KEY `i_sales_variantid` (`variantId`),
  KEY `date_and_product_index` (`shopId`,`processedAtNano`,`productId`,`rowStatus`),
  KEY `i_sales_custemail` (`customerEmail`) USING BTREE,
  KEY `i_sales_test_index` (`shopId`,`processedAtNano`,`rowStatus`,`customerEmail`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and I'm trying to run this query:

SELECT
    get_total_freq(MAX(s.processedAtNano),
        MIN(s.processedAtNano),
        count(s.customerEmail)) AS a
FROM
    sales AS s
WHERE
    s.shopId = 'p2k-c5k-o100k'
    AND s.processedAtNano < 1525132799999
    AND s.customerEmail IS NOT NULL
    AND s.customerEmail != ''
    AND s.rowStatus = 'ok'
GROUP BY
    s.customerEmail
HAVING
    count(s.customerEmail) > 1)

but this just won't use the indexes and would start using where and filesort

below is the explain for this query:

1|"SIMPLE"|"s"|"ref"|"i_shopId_nano,date_and_product_index,i_sales_test_index"|"i_shopId_nano"|"103"|"const"|277686|"Using index condition; Using where; Using temporary; Using filesort"|

I have experimented with all logical permutations of indexes that I can think of but still, the query time is 11sec, while without the group by its 630ms

definition of get_total_freq:

CREATE FUNCTION `get_total_freq`(max BIGINT, min BIGINT, no_of_orders INT) RETURNS double
begin
    declare value double;
    set value = DATEDIFF( from_unixtime(floor(max/1000)),
from_unixtime(floor(min/1000)) )/ (no_of_orders-1);
    RETURN value;
end

UPDATE

Solution I ended up using thanks to both Rick James and jonggu as it was a mix of both

The final index that I created

"i_sales_stack" => "shopId,rowStatus,customerEmail,processedAtNano"

And the final query I'm using

SELECT
    avg(b.total_freq)
FROM
    sales AS s, (
        SELECT
            saleId,
            get_total_reorder_freq (MAX(a.processedAtNano),
                MIN(a.processedAtNano),
                count(a.customerEmail)) AS total_freq
        FROM
            sales AS a
        WHERE
            a.shopId = 'p2k-c5k-o100k'
            AND a.rowStatus = 'ok'
        GROUP BY
            a.customerEmail
        HAVING
            count(a.customerEmail) > 1) AS b
    WHERE
        s.saleId = b.saleId
        AND s.processedAtNano < 1531440000000
        AND s.customerEmail != ''

Try this.

Change index like.

KEY i_sales_test_index (shopId,rowStatus,customerEmail)

SELECT
    1 AS sortKey,
    avg(avgs.a) AS reorder_freq
FROM (
    SELECT
        b.total_freq AS a
    FROM
        sales AS s, (
            SELECT
                saleId,
                get_total_freq (MAX(a.processedAtNano),
                    MIN(a.processedAtNano),
                    count(a.customerEmail)) AS total_freq
            FROM
                sales AS a
            WHERE
                a.shopId = 'p2k-c5k-o100k'
                AND a.rowStatus = 'ok'
            GROUP BY
                a.customerEmail
            HAVING
                count(a.customerEmail) > 1) AS b
        WHERE
            s.saleId = b.saleId
            AND s.processedAtNano < 1525132799999
            AND s.customerEmail IS NOT NULL
            AND s.customerEmail != '') AS avgs


Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow? I've and Oracle (11 Enterprise) schema with a table

 CREATE TABLE USER.WSP_BUNDLE (
    NODE_ID     RAW(16) NOT NULL,
    BUNDLE_DATA BLOB NOT NULL
    );
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ON USER.WSP_BUNDLE(NODE_ID);

and 3'rd party library (Java 6, JDBC - latest jdbc driver) that manipulate it.

Oracle profiler (tkprof) shows that about 50% of overall time the lib execute such statement:

update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2

TKProf data

Plan Hash: 4085453680
update WSP_BUNDLE set BUNDLE_DATA = :1  where NODE_ID = :2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      264      0.00       0.00          0          0          0           0
Execute    400     30.59     382.88     141451    1623163    3233827         400
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      664     30.59     382.88     141451    1623163    3233827         400

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us)
      1   INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    141251        5.53        328.04
  direct path write                             402        0.09          0.43
  SQL*Net more data from client              142158        1.04         11.89
  direct path read                              200        0.03          0.07
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                     400        0.00          0.00
  SQL*Net message from client                   400        0.29          0.50
  log file switch (private strand flush incomplete)
                                                  5        0.05          0.23
  asynch descriptor resize                   139723        7.46          8.57
  buffer busy waits                               2        0.00          0.00
  log file switch (checkpoint incomplete)         3        0.18          0.27
  log file sync                                   2        0.00          0.00

Could anybody explain/hint me what's going on? Why the update is so slow?

The table WSP_BUNDLE contains about 200+k rows. At the same time I've other tables in the same schema with blobs (CLOB to be more concrete) that contains 600+k rows where similar updates work correctly.

Already answered at a parallel thread on serverfault:  https://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588

  In Oracle, LOB (including BLOB) is stored as:

  in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you
  specify DISABLE  STORAGE IN ROW
  normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:

  a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
  there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
  access is multi-level and thus relatively slower
  with NOCACHE option, the waiters are "direct path read" - the default
  with CACHE option, the waiters are "db file sequential read"

  where CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

  Therefore, if your LOBs are larger than 4 kB they will get relatively
  slow, and this may simply be your case. I would examine the sizes.

  I would examine USER_LOBS (or DBA_LOBS) to see how the "good" and
  "slow" LOB columns differ in their definitions.

  The Metalink note ID 66431.1 describes this and may be of interest to
  you, if you have access there.

UPDATE - see link above, I've had some thoughts.


Converting date stored as a varchar I'm creating a query that has a column of dates stored as a VARCHAR in the format DD-MMM-YYYY.

Could you tell me how I can convert this into date format please?

I don't want to alter the original table in any way, as it's not mine to tinker!

You can use CAST to convert your VARCHAR value into DATE format.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

Sample execution: You can convert into DATE alone or DATETIME format:

DECLARE @TestDate AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate AS DATE);

Output: 2015-05-22

DECLARE @TestDate1 AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate1 AS DATETIME);

Output: 2015-05-22 00:00:00.000


Column or global variable ROWNUM not found I have made an attempt at the ROW_NUMBER(), and when exporting the results in a csv it was worked perfectly. The issue I’m having now is in the where clause to bring back records where ROWNUM = 1.

I am getting the error message:

  Column or global variable ROWNUM not found.

I have tried using FETCH to bring back one row; this works however the following join does not get considered.

Do you know what the best method is to bring back only records with ROWNUM = 1?

LEFT JOIN
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
    WHERE ROWNUM = 1
)C ON C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF

Lets see what we have here. Inside the parentheses we have a derived table (often called a subquery although this is a more general term), which is then aliased with the name C:

...
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
    WHERE ROWNUM = 1
) C  ...

Column ROWNUM is not a column of the base table (PY015L1) but defined in the SELECT clause. And there lies the problem. The logical processing of a query (and any subquery including a derived table) has a specific order which is (some parts not included):

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So WHERE is before SELECT and cannot use any new columns defined there. That's the reason for the error.

You might ask? Isn't ROWNUM a real column? Haven't we just defined it?

Yes we did but it has a certain scope that can be used. C table is also a real table (it's just not a base table). We can use them after they has been defined, so ROWNUM can be used  in the following clauses (ORDER BY which is after SELECT) and outside the parentheses, wherever the C table can be used. Both table C and column ROWNUM are real tables and columns but only after they have been defined. It's like Pinocchio ("See dad, I'm alive! And I'm a boy, I'm a real boy!"). He is a real boy but only at the end of the story, after he has been transformed).

In your case, what is probably wanted is to remove the WHERE ROWNUM = 1 and use the column in the following clauses, ON or WHERE. I'd guess the ON clause:

LEFT JOIN
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
) C ON  C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF
    AND C.ROWNUM = 1


SQL UPSERT in Postgres I have the following SQL:

UPDATE table_name as t
  set value = c.value
from (values(10, 'key_1', 60),(11, 'key_2', 10)) as c(id, key, value)
where t.id = c.id and t.key = c.key

This works fine only in the case if the given 'id' and 'key' already exist in the table. However I need to make it UPDATE the value if 'id' and 'key' exist in the DB and INSERT a new row with the given 'id', 'key' and 'value' if it does not.

I got this example working as an UPSERT but for one set of values only:

WITH upsert AS (
 UPDATE table_name
   SET value=80
 WHERE id=10
   AND key='key_1' RETURNING *
)
INSERT INTO table_name (id, key, value)
SELECT 10, 'key_1', 10
WHERE NOT EXISTS (SELECT * FROM upsert);

I'm not sure how to incorporate this into my case to work with multiple values (first example with UPDATE).

I do not have a unique constraint or index on (id, "key")

Use insert ... on conflict

insert into table_Name
  (id, "key", value)
values
  (10, 'key_1', 60),
  (11, 'key_2', 10)
on conflict (id, "key") do update
  set value = excluded.value;

This assumes that you have a unique key (or index) on table_name(id, "key")

Without a unique constraint or index you can do it like this:

WITH new_values (id, "key", value) as (
  values
  (10, 'key_1', 60),
  (11, 'key_2', 10)

), upsert as (
    update the_table t
        set value = c.value
    FROM new_values nv
    WHERE t.id = nv.id
      AND t."key" = nv."key"
    RETURNING t.*
)
INSERT INTO the_table (id, "key", value)
SELECT nv.id, nv."key", nv.value
FROM new_values nv
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = nv.id
                    AND up."key" = nv."key")
;

The above is NOT safe for concurrent insert/updates - you can still wind up with duplicates. The only reliable way to avoid those duplicates is to use a unique constraint or index.


Virtualized SQL Server Memory Oddity I've not seen this before, though perhaps it is common.  I am looking at a virtualized (VMWare 5.5) SQL Server (2008R2 on Windows 2008 R2). What I am seeing is that in Task Manager*, sqlserver.exe is using about 163 MB of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

The VM has 32GB of RAM and Task Manager shows 31.7 GB of that RAM being used.  The VMWare Perfmon counter does not seem to be indicating any ballooning (perhaps I read something the wrong way).

Thoughts/guidance appreciated.  I am trying to right size a new SQL VM.  At this point I have not yet obtained access to vSphere or the vCenter database.

* Using Task Manager because I was looking at overall memory utilization, not just SQL Server. I was warned by a performance touchy admin to keep my treading very very light.

What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

You should never look at task manager to check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 and above, to check SQL Server memory utilization.

select
(physical_memory_in_use_kb/1024) as PhyMemory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 ) as Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 ) as Total_MemoryUsed_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Note:

PhyMemory_usedby_Sqlserver_MB is the committed memory and is backed by physical RAM.

Total_MemoryUsed_in_MB is the total memory used by SQL Server (RAM and page file)

You are making a mistake, which probably many 'newbies' make when referring to memory used by SQL Server. It would never show you correct memory utilization in task manager if Locked pages in memory privilege(LPIM) is there for the account running SQL Server service.  This is because the task manager only shows Process Private Bytes, the memory allocated via Virtual_alloc function which is pageable. Now if the SQL Server service account has LPIM, most part of the memory allocation would be done by AWE API. The memory allocated via AWE API is NOT pageable and thus does not shows up in task manager, which, in the end shows you different (incorrect) values.

Further reading:

Fun with Locked Pages, AWE, Task Manager, and the Working Set… (CSS SQL Server Engineers)


MongoDB all replica set members are "OTHER"? I am setting up a MongoDB replica set using EC2's image: https://aws.amazon.com/marketplace/pp/B00CO7AVMY/ref=dtl_recsim_B007IBMJPI_B00CO7AVMY_3

MongoDB version 2.6.5

I have managed to set up replicaset by not enabling "auth=true" in my config file.

I went ahead to add "auth=true" back into my config file and restarted mongodb on all 3 of my instances.

sudo service mongod restart

After restarting, I got into mongo shell and it is displaying

myReplicaSetName:OTHER>

I am unable to run rs.status() because I don't have a PRIMARY.

 "errmsg" : "not authorized on admin to execute command { replSetGetStatus: 1.0 }"

When you enable authentication you must also specify (at the very least) a keyfile also (see step 1 in the procedure for enabling auth), setting auth=true without a keyfile will mean that the mongod processes will not be able to authenticate with each other (this is what the keyfile is used for).  Hence they cannot communicate, elect a primary, respond to heartbeats etc.

Follow the guide/docs linked above to create and configure a keyfile and your replica set should come up and elect a primary without an issue.


Restart of rownumber based on count in other table in oracle sql I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) while table2 presently has 80,000 rows (row count varies from day to day).

TABLE1 :- NAME_VALUES
NAME_VAL
--------
TOM
DICK
HARRY

TABLE2 :- CUS_TABLE

CUS_ID
---------
401795480
201134211
137643082
876450821
777290153
111035791
579865552

I want to have something like below as output

401795480 TOM
201134211 DICK
137643082 HARRY
876450821 DICK
777290153 HARRY
111035791 TOM
579865552 DICK

My idea was to assign rownumber for each table. For table 2 I would like to restart the rownumber once the maximum count of table 1 is reached, something like below, but I am unable to figure out how can I perform this

Table1
    NAME_VAL   TABLE1_RN
    ---------------------
    TOM          1
    DICK         2
    HARRY        3

Table2
    CUS_ID    TABLE2_RN
    --------------------
    401795480 1
    201134211 2
    137643082 3
    876450821 1
    777290153 2
    111035791 3
    579865552 1

Now I have a key and can easily map to fetch the details I require.

Please suggest if any method can satisfy my requirement.

Continuing on Lennart's answer, here is how you might do it in a single query.

  with table1 as (
    select cus_id, rownum rn from cus_table
  ),
  table1_count as (
    select count(*) cnt from cus_table
  ),
  table2 as (
    select nv.name_val, mod(rownum, t1c.cnt) + 1 t1_rn
    from name_values nv, table1_count t1c
  )
  select t1.cus_id, t2.name_val
  from table1 t1
    inner join table2 t2 on t1.rn = t2.t1_rn;

First, since you don't have any windowing in your query, you don't have to use row_number() analytic function, you can just use the rownum pseudocolumn. Secondly, note the + 1 in mod(rownum, t1c.cnt) + 1 - mod(x, y) goes from 0 to y - 1, but rownum goes from 1 to y, hence we need to increase the modulo by 1.

The complete script with which I tested is here. http://pastebin.com/DTGir8XK The query runs in well under a second on my home computer.

However, note that the query is non-deterministic due to lack of any ORDER BY! You might get different results each time, so perhaps you should reconsider what exactly you are trying to do.

(In response to a now deleted self-answer) If table1 contains 40k rows and table2 80k, then you get only 40k rows as a result because there are "ids" in table1 which are not present in table2, so inner join eliminates such rows from the result.

You could modify the query to reverse the roles of table1 and table2 to get all 80k rows, however, it might be better for you to try to explain your situation and what exactly your ultimate goal is and we might help you better.


Optimize a WHERE IN condition using an index I have a table that looks like the following one:

CREATE TABLE foo (
    type       TINYINT,    -- low cardinality (2..5)
    id         BINARY(16), -- high cardinality
    other_data OTHERTYPE(n),
    CONSTRAINT foo_PK PRIMARY KEY (type, id)
);

One application accessing the database that contains that table runs point queries by type and id, and queries that filter by multiple (type, id) pairs.

The question is: What index would perform better in these cases? Currently, I group type column and do UNION on SELECT * FROM table WHERE type = ? AND id IN (?)

The rule of thumb to use the highest cardinality column first would still work best, or should I define a (type, id) index?

Note: The database is running on top of MySQL, but answers considering other RDBMSs would be interesting too.

Caveat: This answer applies to InnoDB in MySQL.  It may not apply to other MySQL Engines, nor to other RDBMSs.

A PRIMARY KEY is

Clustered with the data, and
A uniqueness constraint, and
An index

SELECT * FROM table WHERE type = ? AND id IN (?,?,?) is best handled by PRIMARY KEY(type, id) with the columns in that order.  (Second best is INDEX(type, id)).

Being an index, the lookup does not need to scan the entire table.
Being clustered, the lookup and the * (of SELECT *) are done at the same time.
Being unique is not relevant to this SELECT.
The column order is required because the = must come first.
If there is only one item in the IN, the IN will be optimized to =; the index is still optimal.

Cardinality of type versus id is, contrary to a well entrenched wives' tale, irrelevant, at least for this query.

Side note:  The order of items in the WHERE has no impact on performance; the order in the index does.

In this case the = should come first in the index, then more complex item(s), IN in this case.

id smells like a packed UUID.  For huge tables that is a terrible type of index, probably in all RDBMSs, since it is so random that caching is impractical.

Index Cookbook for MySQL/MariaDB.


Does Microsoft provide paid support for SQL Express? For instance, if we run into any kind of issues with SQL Express, if we call Microsoft,  will the provide technical support?

I can't find any great documentation to back this up other than articles referring to SQL Server 2005 like this one here.

I also just tried to create a ticket for SQL Server Express, and the support site lists Express as an option. You can see for yourself by starting here. I've chosen support, developer or IT support, SQL Server, then SQL Server 2016 and finally the option for edition came up and I chose Express.

Microsoft provides support to customers of SQL Server express in the same manner as SQL Server Standard and Enterprise - if you have a support agreement with hours or pay for hours, you'll receive support. If an item is found to be a bug in the product, typically the case hours or money are refunded/not applied, if it is user error/application issue on your end/etc the hours are used like normal. SQL Server licenses of the paid versions don't automatically give you access to support. Support is a separate offering, effectively.


InnoDB: What does Data free mean? I have a pretty large Mysql table, over 25GB. When extracting an overview with Table Inspector in MySQL Workbench, I noticed the field Data Free Because the textfields are not selectable, I'm posting a picture of it. Does it mean some sort of reserved space? Or is it actually free space due to a size limit? Or something else?

What does "Data_free" mean?  Not much.  Seriously, there are several areas in a table where there is "free space"; Data_free refers to only one of them:

An InnoDB block is exactly 16KB; there is almost always some wasted space in a block.
A block in a BTree is normally between 50% and 100% full.
Groups of blocks ("extents") are allocated in chunks of 8MB(?).  Your 7MiB is part of one such extent.  You will very often see 4, 5, 6, or 7, in spite of the main allocation unit being 16KB.
If you are talking about the main tablespace (ibdata1), Data_free refers to how much space is "free" in it; this is usually a large number.

If you were to OPTIMIZE TABLE, the table might, or might not, shrink from the current 25.6 GiB; it could even increase!  The 7.0 MiB would probably stay 7, or might change to 4 or 5 or 6.

"Does it mean some sort of reserved space?" -- Partially a fluke of the way it allocates space.  But mostly it is a useless number.  If it is bigger than 7 MiB, then there is likely to be some space that could be given back to the OS with OPTIMIZE -- _assuming it is file_per_table.  But don't use OPTIMIZE unless you have both file_per_table and Data_free is a significant fraction of the table size.  (This may occur after a big DELETE.)

"Or is it actually free space due to a size limit?" -- Tables under some threshold are allocated 16KB at a time; over that threshhold, they are allocated 8MB at a time.


compare the same table I am facing an issue with the following query. When I execute the query, it takes very long. I broke the query into two parts, compared with a shell script, but is there any chance to go with one query?

Any suggestion welcome.

select distinct substring(mobile_num,3,12)
from mobile
where  status ='INACTIVE'
  and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)
  and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')
order by updtm;

| mobile_num  | varchar(12)
| keyword     | varchar(45)
| sub_date    | datetime
| unsub_date  | datetime
| circle_name | varchar(45)
| type        | varchar(45)
| status      | varchar(45)
| operator    | varchar(45)
| act_mode    | varchar(45)
| deact_mode  | varchar(45)
| id          | bigint(20)
| updtm       | timestamp

QUERY IMPROVEMENT / DATA INTEGRITY
You have what seems to be two conflicting parts of the query
PART 1 : where  status ='INACTIVE'
and
PART 2 : and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')
This query would need both WHERE clauses only if there exists both an ACTIVE and INACTIVE row for a given mobile_num. If that is the case, you should create a table with nothing but ACTIVE mobile_num values
CREATE TABLE active_mobile_num
SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE';
ALTER TABLE active_mobile_num ADD PRIMARY KEY (mobile_num);

then perform a LEFT JOIN so that mobile_num is NULL on the right side as follows:
select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN active_mobile_num B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

NOTE : I changed
and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)

to
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)

because your clause converts the datetime to a date and compares, whereas my proposed clause use the constant value of midnight 90 days ago and compares then as datetimes.
ALTERNATE QUERY SUGGESTION
If you prefer not to form another table so as to have to drop it afterwards, make that other table a subqery and join to it as suggested before:
select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN
(SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE') B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

This might be a little slower since you cannot index the subquery like the first query shows
PROPER INDEXING
What may also help both queries go faster is to index status and unsub_date
ALTER TABLE mobile ADD INDEX status_unsub_date_ndx (status,unsub_date);

Give it a Try !!!


SQL Server 2014 Restore DB only has "Verify Backup Media" option I just downloaded SQL Server 2014 Management Studio. I got the backup files ".bak" from my colleague. When I try to do a restore from "Database > Restore Database...", the window dialog only has "Verify Backup Media" instead of "OK", "Cancel", etc.
Clicking the button the message on top of the dialog stated "Backup media verified successfully", but the database is not restored.
Did I miss out any configuration during installation or perhaps user permission issue? I'm using "sa" user.

After lots of searching it seems abit hard to figure out why restoring database only has the "Verify Backup Media" option in my SSMS.
I tried "Restore Files and Filegroups..." but hit another error "The backup set holds a backup of a database other than the existing", until I came to this post, writing the script instead to restore the ".bak" file works for me.