Optimize join - group by Oracle SQL filter I have a few tables:

CHEST:
    id  fk_id_rap   fk_id_ses_rap
    1   1           1
    2   2           1
    3   3           1

SES_RAP:
id  an
1   2015
2   2016

RAP:
id  name
1   rap1
2   rap2
3   rap3

anexa10
id  fk_id_cht
10  1
11  1
12  2
13  2

anexa10_rows
id  cod         cant  tip   fk_id_anexa10
1   01 01 01    45    r2    10
2   01 01 01    34    r3    11
3   00 00 00    10    r5    12
4   01 03 04    70    r6    13

If i try something like this:

SELECT RAP.name, ses_rap.an, A10.cod,
            (SELECT SUM (COALESCE (A10.CANT, 0))
               FROM ANEXA10_ROWS A10B
              WHERE A10B.FK_ID_ANEXA10 = A10.ID AND
                    A10B.COD = A10T3.COD)
               AS CANT,
        MIN(A10R.TIP),
       FROM CHEST cht
            INNER JOIN rap rap
               ON cht.fk_id_rap = rap.id
            INNER JOIN SES_RAP ses_rap
               ON ses_rap.id = CHT.FK_ID_SES_RAP
            FULL OUTER JOIN ANEXA10 A10
               ON A10R.FK_ID_CHT = CHT.ID
            FULL OUTER JOIN ANEXA10_ROWS A10R
               ON A10R.FK_ID_ANEXA10 = A10.ID
      WHERE SES_RAP.AN = '2015' AND A10.COD IS NOT NULL
   GROUP BY RAP.NAME,
            ses_rap.an,
            CHT.ID,
            A10R.COD,
            A10R.CANT,
            A10R.TIP
   ORDER BY RAP.NAME

The result is:

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01   45   r2
rap1    2015    01 01 01   34   r3
rap2    2015    00 00 00   10   r5
rap2    2015    01 03 04   70   r6

How can I get the result?

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01    79  r2r3
rap2    2015    00 00 00    10  r5
rap2    2015    01 03 04    70  r6

----------------EDIT1:
if I remove A10R.CANT from GROUP BY it gives me that it's not a GROUP BY expression on

(SELECT SUM (COALESCE (A10.CANT, 0))
                   FROM ANEXA10_ROWS A10B
                  WHERE A10B.FK_ID_ANEXA10 = A10.ID AND
                        A10B.COD = A10T3.COD)
                   AS CANT,

Check https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle. Here the LISTAGG function is explained. This functionn delivers what you need.


Return Current Connection CName I'm wondering if anyone knows if there is a way to return the name that was used to connect to the SQL server rather than the actual server's name. For instance I typically connect to the server via a DNS CName. But if I run this ..
select @@SERVERNAME

... I get the actual server name. I'm looking to find a way to get the CName with which I connected if it is even possible. Thoughts?

The SQL Server instance has no notion of the name resolution the client used to connect to the server IP address so what you want is not possible.
@@SERVERNAME returns the server name or FCI network name, plus instance name for a named instance. The value is unrelated to the client connection specification.


For a InnoDB only DB, which of these elements can be removed? So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming InnoDB.  So of this list of configs, which are irrelevant if you're building for InnoDB.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
open-files-limit = 4096

[mysqld]
port = 3306
user = mysql

default_storage_engine
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
basedir = /usr
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
local-infile = 0
automatic_sp_privileges = 0
safe-user-create = 1
secure-auth = 1
secure-file-priv = /tmp
symbolic-links = 0
key_buffer_size = 32M
myisam-recover = BACKUP,FORCE
concurrent_insert = 2
max_allowed_packet = 16M
max_connect_errors = 1000000
datadir = /var/lib/mysql
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 1
query_cache_size = 0
query_cache_limit = 8M
query_cache_min_res_unit = 1K
default-storage-engine = InnoDB
thread_stack = 256K
thread_cache_size = 128
max_connections = 128
open_files_limit = 65535
skip-locking
skip-bdb
server-id  = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_cache_size = 256K
sync_binlog  = 256
expire_logs_days  = 14
max_binlog_size = 1G
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
max_user_connections = 150
key_buffer = 16M
key_cache_block_size     = 4K
bulk_insert_buffer_size  = 8M
innodb_buffer_pool_size  = 6G
myisam_sort_buffer_size  = 64M
join_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 64M
table_cache = 4096
table_definition_cache = 4096
table_open_cache = 16384
optimizer_search_depth   = 4
collation-server = utf8_general_ci
interactive_timeout = 400
wait_timeout = 300
connect_timeout = 10
thread_concurrency=8
back_log = 2048
open-files = 10000
query_prealloc_size = 65536
query_alloc_block_size = 131072

Please remove or change  the following:

skip-locking
skip-bdb
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
default_storage_engine
default_storage_engine = InnoDB
thread_concurrency=8
sync_binlog=256
bulk_insert_buffer_size = 8M

Here is why:

binlog_do_db and binlog_ignore_db are only for including/bypassing the recording of SQL  targeted for specific databases.
skip-bdb is obsoloete since the BDB storage engine is no longer include in MySQL 5 starting with MySQL 5.1
If you install MySQL 5.5, InnoDB is the default storage engine. No need to include it in my.cnf
thread_concurrency can go since it only works with Solaris
sync_binlog does an OS flush of binlog changes every 256 binlog writes. Increase this if any long write bottlenecks manifest themselves
Your bulk_insert_buffer_size is set to the default. That needs to be way bigger (at least 256M). This would accommodate bulk loading of rows from LOAD DATA INFILE or heavily grouped row from the loading of mysqldumps.

There is so much to add based on the info in Drupal 7 Uses InnoDB, so do I need other configs?


Can I have a sql 2017 read-scale AG on different Windows server OSs; i.e. Windows server 2016/2019? I hope the title is self-explanatory. We want to add another node to our sql server 2017 read-scale AG. I would like to go with Windows server version 2019, and cannot find any information that says I cannot. Any concerns, comments or experience on this?

Availability Groups are built upon Windows Failover Cluster.  As such, it won't (probably?) be a supported scenario to run long term with different OS versions in the same cluster.  Assuming it lets you set it up in the first place.
You might ask in System Administration if it's possible to setup a windows cluster between a OS 2016 and 2019.  If you can and if it's supported, then SQL won't have any issues.
You could do a distributed Availability Group as that is not built upon Windows Failover Cluster, but you lose a lot of advantages (no listener, to start with).


Cassandra Cluster storage I want to try out Cassandra Cluster. My main question is about scalability.

Every node in a Cassandra cluster has the same copy of data. So when I have in total 1 TB and have 5 nodes -> 5TB.

This at some point will get huge. How do I scale Cassandra so that the storage can be distributed? Do I need to shard manually then again?

The data is automatically distributed in the cluster based on the value of the partition key of your tables. So you need to take care about creating correct data model - don't have the partitions that have hundred thousands of rows, etc.

If the data model is correct, then you can scale Cassandra by just adding the new nodes, and then data will be redistributed between nodes.


Can we configure local and azure servers in a WSFC for Always On Availability Group? Can we configure a local server and an azure server as a two node cluster for Always on Availability Group?
Thanks!

Yes, you can
I've had 2 local (on-premise) SQL Servers and 1 VM in Azure, configured in Availability Group of three servers


SQL Server 2008 R2 – Track Database Growth (Production Database) My requirement is to get the database size information collected daily, I did not find any option to get database growth over a period of time.

Someone please advise what the correct method for this could be.

Refer the link which details as creating a table, stored procedure and a SQL Agent job to execute that sp and save details in that table everyday

http://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/


How can I rectify an "arithmetic overflow occurred error" when trying to merge these two queries? Let us consider the first query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

This gives us a result set as follows:

Here is my second query:

SELECT cf.cnum, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date  = '9/30/2016'
GROUP BY cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

I attempt to merge these two queries so that I would get a result set which lists 4 columns:

cnum | name | assets | sums

Here is my attempt:

SELECT cf.cnum as cnum, c.name as name, r.assets as assets, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM research..complex c, research..retirement_simple_ira r, retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date = '9/30/2016'
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

and that is when I receive the error. What am I doing incorrectly?

FWIW ... and in case you're (still) not sure why the original (combined) query was generating an overflow error ... consider:

missing join clause between the c and r tables (c.cnum = r.cnum)
missing join clause between the c and cf tables (c.cnum = cf.cnum) or r and cf tables (r.cnum = cf.cnum)
missing 'group by' clause

The missing join clauses means your query was generating a cartesian product between the c/r/cf tables (ie, generating a lot more join records than what you wanted).

The missing 'group by' clause means the sum() was being applied across that massive set of (cartesian product) join records; and as Henrico has suggested the sum() thinks it's working with integer datatypes so the sum() result is assumed to be integer; Sybase's integer datatype has a max value of 2B so when the sum() (sans a 'group by'; applied to massive cartesian product) hits above 2B you get the overflow error.

Your latest query includes those pieces (joins, 'group by') missing from the first attempt hence you're no longer generating an overflow error. ("Duh, Mark!" ?)


SQL Server - Get a new columns with name of the columns have the values X base on rows I want to get a new columns on table and this new columns need to contain another columns name separate by - example: (field_1-field_2) where the values = X

I can not hard code the column names because each time these column name are different depending on the projects.

|  id  |  field_1 | field_2  | field_3 |  field_4 |  field_5  | new_column              |
|  1   |     X    |  NULL    |      X  |  NULL    |     X     | field_1-field_3-field_5 |
|  2   |  NULL    |     X    |      X  |  NULL    |  NULL     | field_2-field_3         |
|  3   |  NULL    |  NULL    |   NULL  |     X    |  NULL     | field_4                 |

If you don't know the table structure, one possible approach is to generate and execute a dynamic statement. Use the information from sys.columns and sys.tables views to generate the statement.

Table:

CREATE TABLE Data (
    id int,
    field_1 varchar(1),
    field_2 varchar(1),
    field_3 varchar(1),
    field_4 varchar(1),
    field_5 varchar(1)
)
INSERT INTO Data
    (id, field_1, field_2, field_3, field_4, field_5)
VALUES
    (1, 'X',  NULL, 'X',  NULL, 'X'),
    (2, NULL, 'X',  'X',  NULL, NULL),
    (3, NULL, NULL, NULL, 'X',  NULL)

Statement:

-- Declaration
DECLARE @stm nvarchar(max) = N''

-- Columns names
SELECT @stm = CONCAT(@stm, ', CASE WHEN ', col.[name], ' = ''X'' THEN ''', col.[name], '-'' ELSE '''' END')
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
WHERE (tab.[name] = 'Data') AND (col.[name] LIKE 'field_%')

-- While statement
SET @stm = CONCAT(
    N'SELECT *, CONCAT(',
    STUFF(@stm, 1, 1, N''),
    N') AS NewColumn FROM Data'
)

-- Execution
PRINT @stm
EXEC sp_executesql @stm

Output:

-------------------------------------------------------------------
id  field_1 field_2 field_3 field_4 field_5 NewColumn
-------------------------------------------------------------------
1   X       NULL    X       NULL    X       field_1-field_3-field_5-
2   NULL    X       X       NULL    NULL    field_2-field_3-
3   NULL    NULL    NULL    X       NULL    field_4-


Disk space requirement for REINDEX in Postgresql There is a server with postres 13 running out of disk space.
It contains a table metrics that occupies 90% of the entire database.
I have deleted old records in this table.
Due to this table is used in production, I cannot run "VACUUM FULL metrics".
Since I can't afford to block it.
After deleting old records I want to REINDEX CONCURRENTLY bloated indexes to free space.
But first of all I would like to know how much space is needed to reindex them. If the server runs out of space Postgres will crash.
So, is it possible to calculate a space requirement for REINDEX in Postgresql?

Up to three times than the size of new index:

index structure itself
WAL records during create index
temporary files while sorting index values

I have deleted old records in this table

It is possible to reclaim space using the pgcompacttable tool. pgcompacttable was designed specifically for compressing tables when there is not enough space for a copy of the table.
During an update or insert, postgresql will write the new version of the tuple to a free space at the beginning of the table. Therefore, if we somehow update all tuples at the end of the table, the subsequent vacuum will find only empty pages at the end of the table without actual (live) data and will return those pages to the file system. So the size of the table on disk will be reduced. pgcompacttable performs exacly such updates + vacuum.


MySQL - Creating an associative table I have the following table structures:

Cases:

(PK)
case_id | case_notes | time
1       | blah       | timestamp
2       | blah       | timestamp
3       | blah       | timestamp
.       | .          | .

There will be an "infinite" amount of cases so each has it's own unique ID.

I now have a "tags" table.  There can be an "infinite" amount of tags so each tag has it's own unique ID:

(PK)
tag_id | tag
1      | tag-1
2      | tag-2
3      | tag-3
.      | .

Now I would like each case to be associated with n'-many tags.  Due to this I need an "association" table (is that the right word?) to link cases to tags.

(PK??? not sure)    (FK)      (FK)
id | case_id | tag_id
1  | 1       | 1
2  | 1       | 2
3  | 2       | 2
4  | 3       | 1
5  | 3       | 2
6  | 3       | 3
.  | .       | .

In the above example case 1 is associated with tag-1 and tag-2, case 2 is associated with just tag-2, and case 3 is associated with tags 1,2, and 3.

I want to be able to do the following SELECTS:

SELECT all cases (including their notes) which are associated with tag-1
SELECT all tags associated with a particular case.

On a high level you can imagine the system as the following giant table:

case_id | case_notes | time      | tags
1       | blah       | timestamp | (tag-1, tag-2)
2       | blah       | timestamp | (tag-2)
3       | blah       | timestamp | (tag-1, tag-2, tag-3)
.       | .          | .         |

For that many-to-many 'relation' table, don't bother to have an id.  Simply have

PRIMARY KEY(case_id, tag_id),
INDEX(tag_id, case_id)

Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other.  (Use ENGINE=InnoDB.)

To get (tag-1, tag-2, tag-3), use GROUP_CONCAT().

FOREIGN KEYs are optional; I would not bother.


What event information can I get by default from SQL Server? I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:

Who last executed stored procedure dbo.MyProcedure?
Who updated the salary column in the dbo.Employees table?
Who last queried the dbo.Orders table from Management Studio?

But there are several other events that SQL Server does track temporarily by default, and can natively answer questions about, such as:

When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?
Who deleted the dbo.EmployeeAuditData table and when?
How many memory-related errors have happened today?

How do I get this information, and how long does it stay available?

There is quite a bit of valuable information that SQL Server tracks for you by default. Since SQL Server 2005 there has been a "default trace" that runs in the background, and since SQL Server 2008 there has been an Extended Events session automatically running, called system_health.
You can also find certain information from the SQL Server error log, the SQL Server Agent log, Windows event logs, and additional logging from things like SQL Server Audit, Management Data Warehouse, Event Notifications, DML Triggers, DDL Triggers, SCOM / System Center, your own server-side traces or Extended Events sessions, or third-party monitoring solutions (like those made by SentryOne). You can also optionally enable a so-called "Blackbox trace" to assist in troubleshooting.
But for this post I'm going to focus the scope on things that are generally enabled most everywhere: the default trace, Extended Events sessions, and the error log.
Default Trace
The default trace is usually running on most systems, unless you have disabled it using sp_configure. As long as it is enabled, this can be a rich source of valuable information. The following lists the trace events that are captured:
DECLARE @TraceID INT;

SELECT @TraceID = id FROM sys.traces WHERE is_default = 1;

SELECT t.EventID, e.name as Event_Description
  FROM sys.fn_trace_geteventinfo(@TraceID) t
  JOIN sys.trace_events e ON t.eventID = e.trace_event_id
  GROUP BY t.EventID, e.name;

You can get into more detail by joining to sys.trace_columns to see which events come with which data, but I'm going to skip that for now, since you can see what you have when you actually query the trace data for specific events. These are the events that are available on my system (you should run the query on yours to be sure they match, though this is still the same set of events through SQL Server 2019 CTP 2.4):
EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful

Note that the default trace uses rollover files and so the data available to you will only go back so far - the date range of available data depends on how many of the above events are being captured and at what frequency. If you want to ensure that you keep a longer history, you can set up a job that periodically archives away the currently inactive files associated with the trace.
Examples
In the question I asked a couple of questions that I have found. Here are example queries for pulling that specific information from the default trace.

Question: When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?

This query will pull all of the AutoGrow events in the AdventureWorks database, for both log and data files, that are still in the default trace log files:
DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.
DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

There is a complication here, and it is very edge case but thought it prudent to mention anyway. If you use multiple schemas and may have the same object name in multiple schemas, you won't be able to tell which one this is (unless its counterpart(s) still exist). There is an outside case that UserA might have dropped SchemaB.Tablename while UserB might have dropped SchemaA.Tablename. The default trace does not track the schema of the object (nor does it capture TextData for this event), and the ObjectID included in the trace is not useful for a direct match (because the object was dropped and no longer exists). Including that column in the output in this case might be useful to cross-reference against any copies of the table with the same name that still exist, but if the system is in this much disarray (or if all such copies have been deleted) there still might not be a reliable way to guess at which copy of the table was dropped by whom.
Extended Events
The following was the list of data you could cull from the system_health session in SQL Server 2008 and 2008 R2 (that list is more complete in modern versions):

The sql_text and session_id for any sessions that encounter an error with severity >=20
The sql_text and session_id for any sessions that encounter a "memory" type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
A record of any "non-yielding" problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
Any deadlocks that are detected
The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
The callstack, sql_text, and session_id for any session that have waited for an extended period of time for "external" waits or "pre-emptive waits".

From Use the system_health event session (MSDN), the list is somewhat expanded in SQL Server 2012 (and remains the same for SQL Server 2014):

The sql_text and session_id for any sessions that encounter an error that has a severity >=20.
The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.
A record of any non-yielding scheduler problems. (These appear in the SQL Server error log as error 17883.)
Any deadlocks that are detected.
The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds.
The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.
The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.
The callstack and session_id for CLR allocation and virtual allocation failures.
The ring_buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
System component results from sp_server_diagnostics.
Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
CLR Allocation failures.
Connectivity errors using connectivity_ring_buffer_recorded.
Security errors using security_error_ring_buffer_recorded.

In SQL Server 2016, two more events are captured:

When a process is killed using the KILL command.
When SQL Server shutdown has been started.

(The documentation hasn't been updated yet, but I blogged about how I discover these and other changes.)
To get the more cryptic configuration applicable for your specific version, you can always run the following query directly, but you'll have to interpret the names and parse the predicates to match up to the more natural language lists above:
SELECT e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name = N'system_health'
 ORDER BY e.package, e.name;

If you are using Availability Groups, there are also two new sessions that you will find running: AlwaysOn_failover and AlwaysOn_health. You can see the data they collect with the following query:
SELECT s.name, e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name LIKE N'AlwaysOn[_]%'
 ORDER BY s.name, e.package, e.name;

These event sessions use ring buffer targets to store the data, so - like the buffer pool and the plan cache - older events will get phased out, so you won't necessarily be able to pull events from the date range you want.
Example
In the question I posed this fictitious question:

How many memory-related errors have happened today?

Here is a sample (and probably not very efficient) query that can pull this information from the system_health session:
;WITH src(x) AS
(
  SELECT y.query('.')
  FROM
  (
    SELECT x = CONVERT(XML, t.target_data)
      FROM sys.dm_xe_sessions AS s
      INNER JOIN sys.dm_xe_session_targets AS t
      ON s.[address] = t.event_session_address
      WHERE s.name = N'system_health'
  ) AS x
  CROSS APPLY x.x.nodes('/RingBufferTarget/event') AS y(y)
)
SELECT
  x, ts = CONVERT(DATETIME, NULL), err = CONVERT(INT, NULL)
INTO #blat FROM src;

DELETE #blat WHERE x.value('(/event/@name)[1]', 'varchar(255)') <> 'error_reported';

UPDATE #blat SET ts = x.value('(/event/@timestamp)[1]', 'datetime');

UPDATE #blat SET err = x.value('(/event/data/value)[1]', 'int');

SELECT err, number_of_events = COUNT(*)
  FROM #blat
  WHERE err IN (17803, 701, 802, 8645, 8651, 8657, 8902)
  AND ts >= CONVERT(DATE, CURRENT_TIMESTAMP)
  GROUP BY err;

DROP TABLE #blat;

(This example borrows loosely from Amit Banerjee's introductory blog post on the system_health session.)
For more information on Extended Events (including many examples where you can query for specific data), see this 31-part blog series by Jonathan Kehayias:
https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/
Error Log
SQL Server by default keeps the current plus 6 most recent error log files (but you can change this). Plenty of information is stored there, including startup information (how many cores are in use, whether lock pages in memory is set, authentication mode, etc) as well as errors and other scenarios severe enough to be documented (and not captured elsewhere). One recent example was someone looking for when a database was taken offline. You can determine this by scanning through each of the most recent 7 error logs for the text Setting database option OFFLINE:
EXEC sys.sp_readerrorlog 0,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 1,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 2,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 3,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 4,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 5,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 6,1,'Setting database option OFFLINE';

I covered some other details in this recent answer, and there is also some good background information at toadworld and also in the official documentation.
One group of "errors" the error log does track by default - and can make important information fall off the tail much quicker - is every successful backup message. You can prevent these from filling up the error log with noise by enabling trace flag 3226.


addition in datetime column during insertion in mysql i have a table with below 5 columns..timestamp is data type for timedate related column :

 Region     event_time     start_time     mid_time   end_time
 India   2013-11-03 13:00:00
 America
 Holland
Switzerland

I have source from where data for above column is coming at a particular interval..now while insertion i need to add time .

For ex. if in csv file i have event_time of 2013-11-03 13:00:00 , then for India time gets added by 5:30 ( due to GMT ) before insertion..and final inserted values should be 2013-11-03 18:30:00..same applies for rest of Regions and datetime columns..how should i do this...Also daylight saving will include for other Regions that do have this

I tried adddate , addtime functions..but this doesn't work...i tried updating after insertion , but this is not a solution i want.....any help ??

I doubt what are you trying can be done in a single go.You could do it specifying timezone for all inserts for a particular country.
Or something like this after insert:

UPDATE....
  CASE WHEN Region='India' THEN event_time = event_time+INTERVAL 330 MINUTE
  CASE WHEN Region='America' THEN event_time = event_time+INTERVAL someother MINUTE
  CASE...


Get table Data from single table but from different paths A greet to all Experts here at StackExchange.

The problem I have faced seems to be a simple one, it's just my low knowledge of SQL that unables me from doing it.

I have a Table called 'ApplicationPlans' with data about plans in each application of mine.
This table is connedcted to the 'Applications' table with ApplicationId as foreign key.
The 'Applications' is connected to the 'UserRoles' table once directly and once through the 'Clients' table. Allowing us to set access levels on the application level and/or client access level.

The figure can be seen

My goal is to fetch data from 'ApplicationPlans' table for a user according to the roles he has.
Since this data is going to be in a grid, it's better that I dont use a UNION of two select queries like image

of course that is my idea, you might say it works with same performance and I will do it.

So that was my situation,
Any suggestions is appreciated

For sake of clearification I must add,
I need the those data from 'Applicationplans' which my user have access to. This is determined by first finding roles for this user and then in two phases:

Find clients that this user have access to, then find their applications and then find their ApplicationPlans.
Find Applications that this user have direct access to, then find ApplicationPlans that they have.

The result intended to be a union of these two sets together.

Not sure I understand what the problem is, but you can use a derived table like:

select ur.id, c.scopeid
from userroles ur
left join clients c
    on ur.scopeid = c.id

and then join that with the rest of the tables. Something like:

select ...
from (
    select ur.id, c.id as clientid, ...
    from userroles ur
    left join clients c
        on ur.scopeid = c.id
) dt
join applications a
    on dt.id = a.id
    or dt.clientid = a.clientid
join applicationplans ap
    on ap.applicationid = a.id

In general DDL (create table ..., etc) is preferred over images for describing tables.


How to create a table with a field 99% INT; 1% STR values? I am creating a table in PostgreSQL and looking for a best approach that I should follow.

One of my fields are mostly consists of integer values. But really mostly.

Like: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 and over

Last one, 20 and over, kills everything. If it wouldn't be there I am going to create that table with integer field.

Should I create the field in VARCHAR(11) type, just because of the 1% string field? Or is there any other method to solve this issue smoothly?

Apparently you want to store some kind of range where most of the ranges just contain a single element and one of them is open ended.

You did not explain how you want to use those values.

If you only use them for display purposes and never, ever under no circumstance (ever!) have to do any calculations on them, a text column might be enough.

However, I have never encountered a situation where numbers weren't intended to be treated as numbers in the long run.

Depending on how you use the values, I can think of two solutions: either use a range type where most ranges only contain a single element. Whether or not that makes sense depends on the way you use that value. Maybe the 20 single values actually mean a range from 1 to 20 - it's hard to tell from your question

Or use a two columns: one integer and one indicating the "and above". If you only have the choice between "exactly that value" and "that value and above", I would probably use a boolean column that indicates that the value is the lower end of a range.


How to use comma separated columns in CASE WHEN Statement? I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check and advice why its not working?

WHERE supplierAccountDetail.SupplierMasterID IN (
        CASE
            WHEN @SupplierMasterID IS NOT NULL
                THEN @SupplierMasterID
            ELSE (sales.ParentSupplierMasterID
                ,sales.DivisionSupplierMasterID) //Error - Syntax Error
            END
        )

If you want to keep the CASE WHEN statements then you have to specify a new CASE WHEN statement per column / value in the IN() statement.

Example:

    WHERE supplierAccountDetail.SupplierMasterID
    IN(
    CASE
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.ParentSupplierMasterID END
    ,
    CASE
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.DivisionSupplierMasterID END);

This does mean that you will compare your column to the variable twice.

Alternatively you could change the query to what Akina mentioned in the comments:

WHERE (supplierAccountDetail.SupplierMasterID  = @SupplierMasterID AND  @SupplierMasterID IS NOT NULL)
OR
(
@SupplierMasterID IS NULL
AND
(
 supplierAccountDetail.SupplierMasterID IN(sales.ParentSupplierMasterID,sales.DivisionSupplierMasterID) )
);


Is Oracle's dbms_utility.get_hash_value algorithm documented do reverse engineered anywhere? I'm working with an existing database where passwords are hashed like this:

DBMS_UTILITY.GET_HASH_VALUE(the_username || the_password, 1000000000, 1073741824)

I know that this is bad for various reasons (not a proper cryptographically safe hash and the algorithm oracle uses may change between versions). But that database exists and it's unlikely that we can get them to change this since they have lots of users and cannot simply switch to a proper hashing algorithm without having each of them re-enter or reset their password.

Now I need to use this database for LDAP authentication (based on OpenLDAP). Unfortunately there is no way to make OpenLDAP pass the plaintext password to the database (where I could then simply call the hashing function on the plaintext password). However, modifying the LDAP client would be possible, so if I could create the hash there everything would be fine.

I'd rather avoid connecting to the Oracle database to do this since that would require the Oracle client, database login data, etc. on that machine, too. Therefore I wonder if there is any information about the algorithm used by GET_HASH_VALUE() available. I couldn't find anything on Google but maybe it's buried between all the somewhat unrelated results... In case the algorithm changed recently, the server version is 11.2.0.3.0.

You cannot reverse DBMS_UTILITY.GET_HASH_VALUE. It is simply not possible.

Your problem is different: you have to identify an user thought an compliant LDAP directory (like OpenLDAP). Look for Enterprise Users or LDAP authtication in the Oracle documentation and you will find all you need ...


How can a raised alert fail to notify an operator via e-mail? SQL Server 2008: A message with @msgnum = 75000 and @severity = 16 was added to the SQL instance. An alert was added that is raised when error number 75000 is raised. The alert is enabled and an operator, who is also enabled, is set to be notified via email. The alert history shows that the alert is triggered, but the operator is not notified. The operator history shows "Most recent notification attempts: (Never e-mailed)".

The operator has a correct e-mail address. DBMail is setup correctly since I can sp_send_dbmail from and to that e-mail address.

Where do I begin to troubleshoot?

You might check the on duty schedule for that particular operator.  Did this alert happen while the operator was not on duty?

Is SQL Agent set to use Database Mail and a specified Mail Profile for the Alert System?  (Right Click SQL Server Agent > Properties > Alert System)

These are two things that I can immediately think of that could cause this behavior.

Hope this can help.


How to know a backup command failed or worked in a script I have a command in a script to do a 'backup database [name] to [file]  and all the other parameters.
Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing the message:
BACKUP DATABASE is terminating abnormally.
As if there is no problem... no exception is tossed, or anything... so the next command in the series, to do a restore of that database to another database... fails... and the script keeps on running...
Why doesn't it throw an exception when there is a catastrophic failure like either of these?
I've seen some suggestions that we should query log files to see if there was a database backup failure, but good grief, it should just DIE when you run the script.
Do I need to catch the exception, and throw the exception with a higher severity (I'd assume that the other one is thrown with the max severity, but maybe someone thought that a complete failure of a backup is just minor?)
I've seen other code / questions where people are doing a try/catch around the backup, but I have no interest in catching the exception, I want it to Abort right there, die, and report the error up the chain..
Instead, it just keeps going..

the problem is the error severity of the backup exception.
I simulated your situation with this script:
BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The first command generate an error of severity 16 (path not found)
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(path not found.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
and then sql server continues to execute the next command:
11 percent processed.
21 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
61 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 504 pages for database 'test', file 'test' on file 2.
Processed 2 pages for database 'test', file 'test_log' on file 2.
BACKUP DATABASE successfully processed 506 pages in 0.029 seconds (136.179 MB/sec).
Completion time: 2021-01-07T14:06:19.6844320+01:00
As you can see here:
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15#levels-of-severity
The error severity 16 do not breaks the batch.
But if you use XACT_ABORT ON...
    SET XACT_ABORT ON

    BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

    BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

The second backup isn't ever reached.
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(Impossibile trovare il percorso specificato.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Completion time: 2021-01-07T14:34:39.0700755+01:00


Can a primary key of one table be a foreign key of another table? I am working on a scenario like this :
I have an employee table which has columns like this

Employee_ID
First_Name
Last_Name
Employee_Designation

Also I have a Joining_Vital table

Employee_ID
Joining_ID
Joining_Date

How can I link this two tables ?
As I guess it would be a many-to-one relationship(as one joining_vital can contain many employees) from Employee table to  Joining_Vital table

I cannot establish the foreign key on the Employee_ID column of the Employee table.
I am naive to Database thing
Could you guys please help me out how to do it?
I cannot figure out the scenario
Please explain me out. Am I missing anything here?

Hi and welcome to the forums. A simple Google led me here.

CREATE TABLE employee
(
  Employee_ID INT,
  First_name VARCHAR(25),
  Last_name VARCHAR(25),
  PRIMARY KEY (Employee_ID),  -- Must be present - otherwise you cannot create the FOREIGN KEY
  Employee_designation VARCHAR(10)
);

CREATE TABLE joining_vital
(
  Employee_ID INT(10),
  Joining_ID INT(10),
  Joining_date DATE,
  FOREIGN KEY (Employee_ID) REFERENCES employee (Employee_ID)
);

Take a look here and also here - help us to help you! These forums are a great resource and you will get more out of them if you take the trouble to follow the guidelines.


Execute SSIS (package) in SSMS using T-SQL and return a value Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I successfully achieved this using Script Task and Dts.Variables and F5/Run to see successful results. What I want to achieve now is to return (like an out parameter) an Id returned to me by the Web API. I want to have this Id returned in the SSMS Results window when I execute the SSIS script in SSMS.
Here is what I have done:

VS > Open dtsx file in Designer and in the "Control Flow" tab, right clicked to add variable:

Right clicked the Script Task I had created and added the variable created above in the ReadWriteVariables section:

Using "Edit Script" to code the Web API call and return a value and set it as follows:

//MessageBox.Show(id);
Dts.Variables["User::Id"].Value = Convert.ToInt32(id);

The MessageBox was just to test if the id is returned by the Web API and I get an alert of the "id" when I Run/F5 the SSIS project in Visual Studio. The second line is where I have set the Variable.

I execute the SSIS in using T-SQL in SSMS from an example here:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'ssisWAP.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIFolder', @project_name=N'SampleProject', @use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 sql_variant = N'username'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Username', @parameter_value=@var0

DECLARE @var1 sql_variant = N'password'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Password', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

The above executes properly, calling the API and an out @execution_id is shown in Result window. But no sign of the variables User::Id I created earlier. Is it possible to show that value in the Result window? If not, is there a way to see that the data in that variable in SSMS after SSIS execution (even from designer)?
I already had a look at this Stack Overflow Q & A where the solution is to use "Execute SQL Task" and "pass" the variable into a procedure. This I guess is one way to do it but I want to just get the Id in the return when I execute the script as shown above.

Fun question!
When you hit F5/Debug within Visual Studio, that spins up a special environment to allow for breakpoints, debugging and graphical interaction (MessageBox). That can't happen when the package runs on the server because who's going to log into the server and hit the [Ok] button on that message box? DTS, the precursor to SSIS, did allow you to have message boxes and yes, I've logged onto servers only to be assailed by all these popups...
Instead, when the SSIS package is running in unattended mode, if you left code in there that interacts with the UI, the package run-time will throw an exception. Instead, what we have from a command line execution perspective, is what you see in the Output window. The Results window is a graphical representation of package execution progression. It's fine, but you can't copy from there and it truncates messages. Instead, click to the Output window to see and copy all the information.
How do things "get" to the Output window? Events. In your code, instead of writing MesssageBox.Show(id); raise events instead.
bool fireAgain = false;
Dts.Events.FireInformation(0, "ID results", id.ToString(), "", 0, ref fireAgain);

Now when you run your packages, assuming you request that Information events are reported
dtexec.exe /file foo.dtsx /rep eiw

you will see the value in your run log. This covers Visual Studio execution, but what about the SSISDB? That's a different animal.
The SSISDB captures all the output information from SSIS packages and stores it into its own structure. I tech reviewed Tim Mitchell's book "The SSIS Catalog: Install, Manage, Secure, And Monitor Your Enterprise ETL Infrastructure" and he does an excellent job covering the catalog's structure and how to use it. (I get no compensation for mentioning the book).
In the above TSQL, you will see that creating an instance of execution generates a bigint that is assigned into @execution_id. That is the key to the kingdom. Assuming the value is 531463, the following script would tease out the value I fired off in the Information event above
DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;

SELECT
    -- Script Task:Information: 42
    EM.message
    ,   EM.*
FROM
    SSISDB.catalog.event_messages AS EM
WHERE
    EM.operation_id = @execution_id
    AND EM.message_type = 70 /*Information*/ ;

event_messages and operation_messages are generally the same data, just in different views but you'd see the message value in an information message type (70) is recorded as Script Task:Information: 42 which shows the 42 which was my id.  If your SQL Server supports STRING_SPLIT, it's a simple modification to extract the value itself
DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   D.value
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
    CROSS APPLY
(
    SELECT
        *
    FROM
    (
        SELECT
            SS.*
        ,   ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS row_num
        FROM
            STRING_SPLIT(OM.message, ':') AS SS
    ) AS SSO
    WHERE
        -- skip the first two elements as they are Script Task and Information
        SSO.row_num > 2
) AS D
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;

Finally, you could always control your own destiny by having a custom table for storing these values.
CREATE TABLE dbo.PackageRunLog
(
    PackageName nvarchar(255) NOT NULL
,   ExecutionId bigint NOT NULL
,   ExecutionDate datetime2(0) NOT NULL
,   MyId int NOT NULL
);

Use an Execute SQL Task to populate that and the first 3 parameters would by System scoped variables from the SSIS package: System::PackageName, System::ServerExecutionID, System::StartTime. The final would then be @[User::Id] or whatever you needed.


Setup the user for edit/view permission to assigned one assigned entity of three (tables) available I have three entities of store, business, and corporation. User can edit/view/etc (permission) of entities they are assigned to. User can only be assigned to one to the related entities. The above entities can have multiple of all the users in the database.

Your question is ambiguous. Do you wish for a user to see all rows in one table, but only one of the three tables? Should a user be limited to only certain rows within a table? Must this be enforced within the schema or is it OK to use other aspects of the application and infrastructure? Anyway, here are some ideas.

Most DBMS have quite fine grained permission systems. It may be possible to GRANT just the permission required on each table.

Create views that expose just the correct tables, columns or rows. Grant permissions on the views but not the underlying tables.

Write stored procedures for each read & write. Grant access to these SPs but not the tables. Alternatively, let everyone execute all SPs but have the code reject unauthorised actions. Triggers can achieve a similar end.

In the application have separate screens for each read or write action for each entity type. Limit access to the screens through the authorisation system.

For a SQL-only solution for row-level access, add further tables, each keyed by userid and the store or business or corporation id. There will be one further column which is the permission granted. For each statement touching a base table join to this permission table and qualify on the userid and action. If the user does not have rights zero rows will be affected.

These permission tables can get big. It may be useful to put users into groups and assign rights to the groups. If there are rules to control which, say, stores a user sees these can be modelled instead of the naive approach above. The precise table definitions will depend on how the rules normalise. This can get quite tricky and will require a lot of testing.


Does PGStrom calculate relational comparisons? I prefer index calculation and comparison be executed on the GPU.

I have stumbled upon PGStrom.

It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be made explicitly, and I stopped assuming years ago.

As an example, would PGStrom calculate this where condition:

SELECT * FROM some_parent, some_child
    WHERE some_parent.bigint_id = some_child.parent_bigint_id

Does PGStrom calculate relational comparisons?

no, pgstrom cannot do that. it only able to scan relations - it is not able to join.
what you an do is pack an operator into a GPU routine but = is not a good candidate for that.


SQL Server 2016 slow restore We have a 300 GB database that I am test restoring to a new SQL Server 2016 instance, on a virtual Windows 2016 server, used for testing purposes.

The VM has 32 GB RAM, running on a 1 socket CPU with 10 logical processors. (E5 - 2670 , 2,6GHz). Not sure about the power setting, will check that with the guys who set up the VM.

The first time I restored, I did it from a .bak- file from a 2014 instance.
The second time (still running) I did it from a backup made on the SQL Server 2016 instance.

Both times it took over an hour before the "5 percent processed" message was shown. Before that the percentage was on 0% (using a script that shows progress of restore). The restore was finished after about 2 hours the first time.
I especially wonder why it takes so long for the process indicator starts going. What is going on in when that stays on 0%?

Is this is normal on a SQL Server 2016 instance, or could something be wrong? On the production server, a restore will go much faster, but it is difficult to compare since the prod is a dedicated server with multiple spindles, while the test-server is a virtual server.

The problem was, as proposed by some in comments, that Database Instant File Initialization was not enabled on the instance. I enabled this by giving the user under which the SQL Server service was running the needed permissions, and then restarted the SQL Server Service. The steps to take can be found in the following link: Database Instant File Initialization

Thanks for all the suggestions, some of them pointed me to the solution.


Tools and techniques for untangling monster views (nested views) First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface.

First few weeks with this client and users are complaining about performance constantly with even the most bread and butter reporting functionality. I finally get DB access and discover the worst set of nested views (Is nested view a good database design?) I've seen in my life. We're talking a tree of views that is 5 nodes deep and each node has 3-5 leaves. So a single query has 20+ views behind it. It's mind bogglingly bad. The explain plan looks like a fractal after ten million iterations. I can't even read it.

I need to untangle at least this one monster view. But I have no ideas on how. I found this post (http://www.midnightdba.com/Jen/2010/06/detangling-nested-views/) which has a handy SQL script that lists the referred objects but that information doesn't get me terribly far on getting the actual table references for each field in the parent view. If I were to do it by hand, it would probably take 2 weeks. If I got it down to inline form, are there any tools that could remove redundancies and unused fields and optimize the query?

You can enumerate nested views through this query:
SELECT DISTINCT
    sd.referenced_major_id,
    OBJECT_NAME(sd.referenced_major_id) as This_view,
    sd.object_id,
    OBJECT_NAME(sd.object_id) as need_this_view_to_work

FROM
    sys.sql_dependencies sd
WHERE
    EXISTS (SELECT 1 FROM sys.views v WHERE sd.object_id = v.object_id)
    AND EXISTS (SELECT 1 FROM sys.views v WHERE sd.referenced_major_id = v.object_id);


SQL Server Auto-growth Handling I have this scenario:

DB Log with initial size of 1193 and auto growth by 100 MB unlimited
Everyday at 8am, I have 20 auto-growth events firing per second which
is my problem

Is the correct solution here is to adjust the size of log file so that auto growth will not fire frequently?  After adjusting the db log file, should I also change the auto-growth size to like 500mb to at least limit the number of auto-growth event when the log file reach the max file size?

You can reduce the number of growth events by increasing the growth factor, but be careful. Log files cannot take advantage of instant file initialisation, so if you increase the growth factor too high you can cause delays during auto-growth events.

You also need to consider the number of VLFs that will be created based on the growth increment and the number of growth events. Check out this article by Kimberly Tripp for VLF info.

If your log file is constantly increasing in size, this growth ongoing so you should consider increasing your growth factor to reduce the number of events if they are impacting performance.

If your log file is being shrunk every day, this growth is repeating, meaning it wouldn't keep increasing the log file size each day if you grew the log to a reasonable size. Stop the process that shrinks your log file and grow the log file manually to a suitable size to mitigate the high-frequency log growth events.


Does UPDATE without WHERE clause lock a table in PostgreSQL? Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted?

E.g. if I run
UPDATE t1 SET key = 'value'
Can I expect no new rows will be inserted into t1 during the UPDATE execution?

If no, can I expect UPDATE will update even the rows appeared after its start? (key has no DEFAULT 'value' in its definition)

An UPDATE without a WHERE clause will lock all rows in the table, but will not lock the table itself for DML.

The rows can not be deleted from a different transaction because they are locked.

But you can insert new rows without problems (assuming they do not violate any constraints).

Any row that is inserted after the UPDATE will not be seen by the UPDATE statement and thus they won't be changed.


nvarchar(max) in Included Columns of NC index Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?

By "rebuild issues," I mean taking a very long time to rebuild the index. I thought that the "included" columns were treated differently from key columns and would not have as big of an impact on the cluster.

Yes, rebuilding that index will probably be a lot slower.

Baseline it will be an entire extra copy of this data on your disks.  If the column is updated frequently, then it could cause the index to fragment more quickly.  And the more data is in the index, the longer it will take to drop and recreate ("rebuild").

It's generally not recommended to add large data types as included columns in a nonclustered index.  It causes fewer rows to fit on a page (or pointers to off row storage), which causes more pages to have to be read whenever you query this index.  And as you can imagine it will require more RAM to keep these index pages in memory.

You would want to do performance testing to make sure that the benefits of this included column (presumably to avoid a key lookup?) outweigh the costs mentioned above.

Of course, the level of impact will depend greatly on how much data is actually stored in this column in practice.

You're right that included columns are treated differently, in that they don't affect the order that index data is stored.  But they are still included at the leaf level of the index, so they will definitely cause your rebuilds to take longer.  Included columns will generally cause less overhead than key columns when it comes to updates, since they won't cause the rows to be shuffled around if the order changes.  But rebuilds are really just dropping and recreating the whole index.


Low Memory Usage High CPU mysqld I'm managing a server with medium to high traffic.

It serves a wordpress website.

It seems that it use a lot of CPU (over 300% at some points) and always stack around 15% of memory usage.

It is a MySQL 5.7 server with InnoDB engine under nginx and php7-fpm.

Server has 16GB RAM

I tried to tune with mysqltuner but that doesn't help

Here is my mysqld.cnf

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 192M
query_cache_type        = 1
innodb_buffer_pool_size = 10G

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/lib/mysql/Ubuntu-1604-xenial-64-minimal-slow.log
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
#long_query_time = 5
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Let me know what to tune on the above

Kind Regards

Update

The problem was solved but not on the current server.
I created an image of the VPS and requested another virtual machine. Copied that on the new server and the problem solved.
I can only imagine that there was a faulty hard drive on the initial machine.

I accepted @Rick James answer as correct because after debugging mysql slow query log, as he suggested, I found that all the slow queries reported didn't make any sense.

There are inefficiencies in the Query cache.

query_cache_size        = 192M

Change that to 50M.

That is one of the few cases where tuning can help with "high CPU".  Usually you need to check the indexes and query formulation.  So...

Find a couple slow queries, present to us SHOW CREATE TABLE for the table(s) involved, and EXPLAIN SELECT ....  Then we can continue this discussion.

Oh, good; I see that the slowlog is already on, with a useless value for long_query_time; change it to 1.  After a day, use pt-query-digest (or mysqldumpslow -s t) to find the 'worst' queries.


Slow Queries Not Logging I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors or anything of the like, it just doesn't seem to be logging the slow queries. I have remembered to restart mysql after my config changes.

I am using MySQL Ver 5.1.61 . Here is what I have in my.cnf:

slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

the file /var/logs/my.slow.log has mysql as the owner, also in the interest of debugging I gave read/write to all on the log file.

I have the long_query_time set to 1 in the above as I just want to see if it is working. I have tried setting it lower(e.g 0.3) but I'm still not getting anything logged. I know that the queries my app are running take longer than 1 second and I've also ran intentionally log queries (SELECT sleep(10);) in the terminal for testing and the log is still empty.

I've looked through the docs, from what I can see this should be working. Anyone have any suggestions as to what I am doing wrong? Any advice would be appreciated, thanks much!

Edit: As asked in comments I ran the query:

 `SELECT variable_value FROM information_schema.global_variables WHERE variable_name IN ('slow_query_log','slow_query_log_file','long_query_time');`

The result:

10.0000000
/var/run/mysqld/mysqld-slow.log
OFF

Obviously my configuration changes are not being taken into account as I believe these are defaults. I am quite certain the my.cnf file I am altering is being parsed as if I put in an invalid value mysql will error on restart. What could be going on here?

Another Edit:

After taking @RolandoMySQLDBA 's advice and moving my slow query config lines to under [mysqld] my settings seem to be saving. Now the result of the above variable_value query is :

1.0000000
/var/logs/my.slow.log
ON

However I am still not seeing the file my.slow.log being written to. I don't think it is a permissions issue as the file is owned by mysql and I've added all permissions for all users on the file. Could anyone think of a reason why this would not be working?

Edit: Solved! The path to the slow query log was incorrect, it should have been /var/log/my.slow.log instead of /var/log*s*/my.slow.log . Thanks to all for the help, I've learned allot!

I think I got the answer:
You need to put those options under the [mysqld] section
[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

and restart mysql
UPDATE 2013-03-05 16:36 EST
I don't know why this is still happening, but please try this:
service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log


Is there any point to SQL partitioning if I index properly and avoid full-table scans? General overview of what I'm building:

Storing time-series data so I expect millions of rows added per month.
Table has 4 columns, 2 of which are indexed, 1 is a numeric foreign key and the 4th contains a non-searchable JSON blob.
4th column is indexed in Elasticsearch for full-text search needs.
For every 10000 rows inserted, ~1000 queries are immediately run to retrieve the last 60 days worth of data for each distinct entity. On occasion 10 users have access to a frontend to manually run queries for data of any age; UX is important so queries must return within seconds.
I only need to keep a rolling year's worth of data.

The only upsides I'm seeing to partitioning is the ease of dropping entire partitions at once when purging, like with ES indexes. It is also suggested queries limited to recent data perform better when partitioned by temporal grouping.

But if I maintain proper indexes and only query on those, at what point do I need to consider partitioning?

Thanks!

Partitioning has a few other benefits as SQL Server almost sees it as a brand new table.  Thus it will use more threads and run concurrent scans on the GAM/SGAM/etc. pages to find your data.  It'll also lock only that partition if it needs to escalate which is a nice to have, assuming you are partition aligned.  So if you're running a query that hits multiple partitions and you have partition elimination working, it will be able to do the metadata lookups and scans on all those in parallel.

It gives you the benefit of having a particular index for the top (or first) partition when it is queried so heavily but drop the index or add other ones for historic data.  This way you can maximize your index usage that way as well.

You can even compress data per partition if you'd like, although you're using newer versions of SQL Server you might get some good compression per partition or just table.

There could be more but those are the main ones I can think of.


Cannot execute job that runs a shell script I have the following pl/sql script that should execute the shell script script.sh. When I runt the code below In SQLDeveloper I get:

    Error report -
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing login executable failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sjseccel 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 20
27370. 00000 -  "job slave failed to launch a job of type EXECUTABLE"
*Cause:    The scheduler ran into an error when the job slave tried to start
           a job of type EXECUTABLE. The rest of the error stack will provide
           more detailed information on what the exact problem was.
*Action:   Correct the problem specified in the error stack and reschedule
           the job.

The file exists and has the correct permissions on my system. Is something that I am doing wrong? Also, the provided credentials seem to be the correct ones.

An here is the code:

set serveroutput on;

BEGIN
    DBMS_SCHEDULER.create_credential(
        credential_name => 'user_cred',
        username        => 'user',
        password        => 'pass');
END;
/

begin
      DBMS_SCHEDULER.CREATE_JOB (
        job_name           => 'test_prog',
         job_type           => 'EXECUTABLE',
        job_action         => '/home/user/Desktop/foo/script.sh',
         enabled            => TRUE,
         credential_name => 'user_cred'
      );
    dbms_scheduler.run_job('test_prog');
end;
/

It looks like external scheduler jobs are not available in the XE edition.


Oracle silent install on Redhat Linux (The Global database name was left blank.) I am installing Oracle 11g in silent mode using the response file that is in the database/response directory. It is three response files in that directory(i.e. dbca.rsp, db_install.rsp and netca.rsp). I am using db_install.rsp and customize its properties to use it in runInstaller command in database directory.

At first I want to know is it the right rsp file to install the Oracle server.

When I use this rsp file it seems it doesn't use the values I set in it fr example it asks me to give the My Oracle Support accounts while I set the DECLINE_SECURITY_UPDATES to TRUE. After I add that parameter in the command line as follow:

  ./runInstaller -silent responseFile=/home/oracle/db_install.rsp -showProgress DECLINE_SECURITY_UPDATES=TRUE

it asks me to specify the "Global database name" as follow:

  Starting Oracle Universal Installer...

  Checking Temp space: must be greater than 120 MB.   Actual 44950 MB
  Passed Checking swap space: must be greater than 150 MB.   Actual 8015
  MB    Passed Preparing to launch Oracle Universal Installer from
  /tmp/OraInstall2014-02-14_06-40-50PM. Please wait
  ...[oracle@somethin database]$ [FATAL] [INS-35071] Global database
  name cannot be left blank.    CAUSE: The Global database name was left
  blank.    ACTION: Specify a value for the Global database name.

I am sure I set that value in the rsp file. But it seems it doesn't recognize it. As I check the installation log in oraInventory/log directory it didn't recognize the SID value I already set.

Even before all the issues above it didn't recognize the ORACLE_HOME and ORACLE_BASE that I have had set in the sb_install.rsp and I set it in environment variables in bash_profile.

I guess it is the wrong rsp file and the runInstaller cannot recognize the parameters in it.  I need to know how to force runInstaller to read the install configuration from the rsp file.

./runInstaller -silent responseFile=/home/oracle/db_install.rsp
  -showProgress DECLINE_SECURITY_UPDATES=TRUE

The above line is wronge. I changed it like the following and it works fine.

  ./runInstaller -silent -responseFile /home/oracle/db_install.rsp -showProgress


Create one-to-many to two fields in different table I have a database where I am trying to figure out how to join a table that contains an object called ModelType.  The calibration table contains two fields (EffModel and BkgModel) that are based on a ModelType.  I cannot figure out how to join these two fields. Below is an image of the stucture.  I am trying to make sure that if I were ever to update one of the model types, that I would not have to update the reference Calibration.

I thought about creating a table called EffModelType and BkgModelType, which would separate the two fields depending on the same table but cannot for the life of me think of what would go in it.

Updated Info:
I may have misspoken about EFFModel and BkgModel Being based on Model type.  Essentially an example table model type is as follows:

ModelTypeID  | ModelName    |   ModelNumber
+++++++++++++++++++++++++++++++++++++++++++
     1       |  Linear      |       1
     2       |  Exponential |       2

and the EFFModel will be assigned a model type such as 1 (linear) and the BkgModel will also be assigned a model type, such as 2 (Exponential). Also the ModelNumber is an identifier from another application that will eventually be deleted so it can be ignored.

I would examine your model and see if it needs some normalization.

It would appear that the two types of model are sub-types of a common model.  I have found this kind of implementation requires some design.  Using the same id sequencing for both tables opens up a number of options for implementation.

Creating an intermediate Model table containing the Model id, modelTypeID, and any common fields contained in the subtypes also helps.  Depending on the query requirements you may be able omit either the subtype tables or the model table from some queries.

If there are few columns in the sub-type tables (EffModel and BkgModel) you may want to add them as nullable columns in the Model table.  Alternatively, you can implement the normalized model and use a UNION query, or outer joins to both subtype tables.

In any case you will need to ensure the integrity of the data and ensure the records created match the ModelType.


Capture and Replay workload We have a new server which we are migrating too and want to see the potential benefits of the hardware.  So the idea is that we capture a workload and replay this against our new box.

My question is however in regards to seeing the performance benefits.  I will be running some perf counters but ideally we want to see how much quicker individual queries are.

I will have this information in the initial workload capture, however when I replay this on my new server do I simultaneously need to run a trace to capture the new workload information also.  In effect running SQL profiler twice at the same time for replaying and capturing?

Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page).

Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis results. The results are shown using a tool called Reporter, which is just reporting services repackaged for RML Utilities.

ReadTrace also produces the RML intermediate files that you can use for replay against the new instance. The tool for replay is called Ostress.

Before attempting the replay (or even the capture), read carefully the manual
in order to understand how to synchronize the capture with the backup you will need to ensure that the workload runs against the same data, so that you get consistent results.

Before performing the replay, set up a trace on the new instance using the same template used for the initial capture. When the replay is over, you can analyze the workload using ReadTrace and use the compare feature in Reporter. It will show you which queries improved or got worse in the two captures, by CPU, elapsed and I/O.

It's not trivial, but it can be done with some patience and a bit of trial/error. Good Luck!

PS: the new version of RML utilities can also analyze a workload captured using Extended Events. In my experience, the files are significantly bigger this way. The trace templates used by RML and Distributed Replay are different, so don't try to use them interchangeably. If I recall correctly, there is a way to feed the Distrivuted Replay with Extended Events files, but I might be wrong. In my opinion this is an option you should be investigating only if Ostress can't keep up with an highly concurrent workload.


Log report 'bad password', but still grants access Original symptom: User sa locked out, too many bad attempts.

Problem was sudden (no issues before), and pervasive (couple of fails in one day).
As this was a new roll out of SQL backend to Sage300, Crystal Reports and Misys inventory package, we could reboot to recover.
(We have since confirmed a Windows Login will allow us to unlock account. No more reboot.)
After seeing the recommendation on how to "disable enforcement policy", we halted the lockouts.
(And yes, there was a windows update, and SQL "uses the policy of the underlying OS" - did Windoze something change?)

Ahh! But here the mystery deepens!
Our log indicates a login failure for user sa at each Crystal/Sage report generated.

Login failed for user 'sa'. Password did not match that for the login provided. [CLIENT: <localhost> -or- 192.168.x.x of machine]
Error: 18456 Severity: 14 State: 8

The report IS generated and delivered to the user. No indication of an issue/error.
We tried to intentionally enter a bad password; exact same error in the logs, BUT the user gets a failure dialog box.
Please note: with the exception of occasional fat-fingered logins, we had no login failures in the logs up until this started. The verbose message is present (duplicated) in both the WindowsNT:Application and the SQL server logs.

So the problem is still there! But the user doesn't see it. But it is in the logs.
WHY? I expect this will come to bite me later, if I don't address today.
Since this is new roll-out, and end-user expects some teething pain, I have some leeway to try things, now....
Maybe not so much, down the line, if/when this blooms into a real issue.
Any suggestions??

Alright, I have a resolution maybe, and an explanation for the log entries.
Following the advise of @John-Eisbrener & @Danielle-Paquette-Harvey (did I do the attribution right?) In particular John's advise of a different user for Crystal I learned the following.

Issue: Log entries that say that the user (sa originally) has entered bad credentials.
Discovery: When a Crystal Report 'template' is opened, there is no log entry.
When the report is 'executed', a dialog box is presented with the users name pre-populated.
Behind the scenes, Crystal apparently sends a request to the OBDC connector with the username that the report database was connected to, without a password, which (of course) fails.
This is the source of the non-fatal, 'credential failure', log entry.
[This was reported (tangentially) here: I am getting a failed login message in the SQL Server error logs however, nothing actually failed? four years ago!]
Note that if a report has five 'template pages', for say an end-of-month report; five attempts rapid fire, and can cause a user lockout unless 'enforcement policy' is loosened! Thus my first problem.
At this point the user enters the (presumably) correct credentials and the report queries the database and populates the report.
If the user has an open instance (say Sage300, with credentials); then the report is generated without a credential dialog box or an failed password in the logs.

So, I will call the mystery log entries 'benign', and close that issue. I will (of course) take the advise for further lock-down of my systems. I may be small, but that does not absolve me of 'best practices'. (Read: Bullseye Breach -G.Scott)
Thanks again to the community. -30-


INSERT INTO a large amount of data with many clients running SELECT I need to run a query like this to insert a large amount of data (millions of rows) into a MySQL table (InnoDB, 5.6.10):

INSERT INTO target_table (a)
SELECT id FROM source_table
ON DUPLICATE KEY a=a;

This usually takes around 200 seconds to run when no clients are accessing target_table.

target_table now has around 20 clients 'polling' to see if data has arrived, so we now have a lot of clients performing queries like this one:

SELECT a FROM target_table WHERE a LIKE '0%';

I'm having trouble with the INSERT INTO statement now that these clients are polling - it fails with the error Lock wait timeout exceeded; try restarting transaction.

I'm a bit confused as to why whatever lock is required for INSERT INTO cannot be acquired because clients are running SELECT statements.

EDIT:

The CREATE statements for these tables are as follows:

CREATE TABLE `target_table` (
  `a` varchar(32) NOT NULL,
  PRIMARY KEY (`a`),
  CONSTRAINT `target_table_source_table` FOREIGN KEY (`a`) REFERENCES `source_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `source_table` (
  `id` varchar(32) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In source_table, are there often duplicate values?  I assume a is the PRIMARY KEY of dest_table?  If there are often dups, change to

INSERT INTO target_table
    SELECT DISTINCT a FROM source_table
ON DUPLICATE KEY a=a;

DISTINCT moves work into the SELECT, thereby lessening the conflicts over the INSERT.

Better yet, do this:

INSERT IGNORE INTO target_table
    SELECT DISTINCT a FROM source_table;

IGNORE is probably less work than IODKU.

Please provide SHOW CREATE TABLE for both tables; there could be subtle issues, especially with the PRIMARY KEY, timestamps, and secondary indexes.


InnoDB foreign key and index duplication? I'm have a part table like this:

    part_id            INT
    manufacturer_id    INT
    supplier_id        INT
    part_number        VARCHAR(50)
    part_description   VARCHAR(120)

with foreign keys:

    CONSTRAINT fk_supplier_to_part FOREIGN KEY supplier_id REFERENCES supplier.supplier_id
    CONSTRAINT fk_manufacturer_to_part FOREIGN KEY manufacturer_id REFERENCES manufacturer.manufacturer_id

and indexes:

    KEY ix_supplier_id (part.supplier_id)
    KEY ix_manufacturer_id (part.manufacturer_id)
    KEY fk_supplier_to_part (part.supplier_id)
    KEY fk_manufacturer_to_part (part.manufacturer_id)

My question is, are the ix_supplier_id and ix_manufacturer_id indexes required? I know that a foreign key needs a 'matching index' but does the index need to have the same name as well as matching columns?

ix_supplier_id and ix_manufacturer_id are not required; as long as you have the fields intended for foreign keys indexed by themselves (as in: (part.supplier_id)) , the names of the keys are arbitrary. You can also have them in composite indexes, but there must be at least one index of the whole field and nothing but the field. So help me DDL =).


MS SQL Server Error 18456 after database move I have a windows service connecting to a SQL database. Recently, I was told to change the DB name in the connection string, all else was the same. When I attempted to connect, it threw Error 18456. With some research I found:

If I debug against the DB in visual studio on my machine, I get data
If  I use SQL Server Mgmt Studio v18.2 (newest version?) on my machine, I get
data
If  I run it in SQL Server Mgmt Studio 2008 on my machine, I do
NOT get data unless I run as Administrator

I am not SQL Server savvy and I don't know what the issue is or how to describe the issue to the DBA. Can someone help me identify the issue or give assistance in how to bring this to the DBA's attention? Thank you.

EDIT: I've installed the service code on a different server and it properly returns data. So this appears to be specific to the server and it's ability to get data from a SQL server. Additionally, both servers I am testing on have an operation which are able to connect to the Oracle. maybe this detail will spark some ideas? Unfortunately, I am not allowed to remote into the issue server as it is production, so I am unable to get much more info. Thanks again.

First, the error seems something wrong with login (as per following query)

select * from sys.messages
where language_id = 1033 and message_id = 18456

When you told to simply rename DB Name in connectionstring, did you verify which username was used, and the same username exists as login with appropriate permissions on SQL server, you can verify this via SSMS.


SQL Select query is taking so long time on new Server I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point:

One of query which is running within a second on live server, it's taking around 5 mins to execute in aws server.

I have already checked about configuration of mysql and its same. We keep same configuration as live for mysql, Tried to import database thrice but same problem persist:

Here is mysql Versions of both server:
Rackspace - 5.5.61
AWS       - 5.7.29

Here is that query:

SELECT DISTINCT(listname),
               `tbcc_l_lists`.`id`,
               `tbcc_l_lists`.`reportcode`,
               `tbcc_l_lists`.`list_type`,
               `tbcc_l_lists`.`list_url`,
               `tbcc_l_lists`.`researchby`,
               `tbcc_l_lists`.`linkassigndate`,
               `tbcc_l_lists`.`startdate`,
               `tbcc_l_lists`.`completeddate`,
               `tbcc_l_lists`.`team`,
               `tbcc_l_lists`.`status`,
               `tbcc_l_lists`.`priority`,
               `tbcc_l_lists`.`list_url_path`,
               `tbcc_l_lists`.`comments`,
               `tbcc_l_lists`.`added_by`,
               `tbcc_l_categorygroup`.`name`,
               `tbcc_l_listtype`.`listtype_name`,
               (SELECT COUNT(tbcc_l_contacts.primary_list)
                FROM tbcc_l_contacts
                WHERE tbcc_l_lists.id=tbcc_l_contacts.primary_list )   AS `contactcount`,
               (SELECT COUNT(tbcc_l_contacts_rejects.primary_list)
                FROM tbcc_l_contacts_rejects
                WHERE tbcc_l_lists.id=tbcc_l_contacts_rejects.primary_list)   AS rejectcount,
               `tbcc_l_admins`.`username`,
               `tbcc_l_admins`.`fname`,
               `tbcc_l_admins`.`lname`,
               `admins`.`username` AS `team_username`,
               `admins`.`fname`   AS `team_fname`,
               `admins`.`lname` AS `team_lname`,
               `admins1`.`username` AS `addedby_username`,
               `admins1`.`fname` AS `addedby_fname`,
               `admins1`.`lname`   AS `addedby_lname`
FROM `tbcc_l_lists`
LEFT JOIN `tbcc_l_categorygroup` ON tbcc_l_lists.categorygroup = tbcc_l_categorygroup.id
LEFT JOIN `tbcc_l_listtype`   ON tbcc_l_lists.list_type = tbcc_l_listtype.listtype_id
LEFT JOIN `tbcc_l_contacts` ON tbcc_l_lists.id = tbcc_l_contacts.primary_list
LEFT JOIN `tbcc_l_contacts_rejects` ON tbcc_l_lists.id = tbcc_l_contacts_rejects.primary_list
LEFT JOIN `tbcc_l_admins`   ON tbcc_l_admins.id = tbcc_l_lists.researchby
LEFT JOIN `tbcc_l_admins` AS `admins` ON admins.id = tbcc_l_lists.team
LEFT JOIN `tbcc_l_admins` AS `admins1`   ON admins1.id = tbcc_l_lists.added_by
WHERE (1=1) LIMIT 200000;

I am working on this issue since last couple of days, Any help will be highly appreciable.

DISTINCT is not a function.  It applies to the whole row.  And it is probably unnecessary.  It takes an extra pass over the data to perform the DISTINCT functionality.
LIMIT 200000 seems quite unrealistic.  What is the goal?
The query, without a WHERE clause, will scan the entire tbcc_l_lists table.  That takes time if the table is big.
COUNT(*) is the usual pattern.  COUNT(x) is often misused; this means "count the row if x is NOT NULL.  Or did you really want that test?
Please provide EXPLAIN SELECT so we can see what parts are being inefficient.
That may lead to requesting SHOW CREATE TABLE for each table.
You say the configuration is the same -- are you referring to both the memory size and my.cnf?


why HDD is more sensitive to the change of innodb_fsync_threshold than SSD What am I doing:
I want to do some experiments to find some interesting facts in DBMS when changing configurations above different physical storage media.
Disk:
Intel P45 SSD vs. SATA HDD 7200 RPM.
Workload:
TPC-C
Performance measurement:
Throughput
Standarized Result when changing innodb_fsync_threshold.
Every data point is repeated 10 times.

Thanks for the help!

Might a hardware perspective explain the differences?
The steps of disk I/O are something like:

buffering (for writes)
send data to the device
"Seek" the desired cylinder on the device
Wait for the read/write head to be over the sector that needs read/written
perform the I/O
buffering (for reads)

Steps 3 and 4 are the dominant time consumers for HDD.  For SDD there is no "head" to move nor platter spinning, so both of these steps take no time.
More to the point, 3 and 4 take a variable amount of time, based on, well, the phase of the moon.  And a good controller, etc, can play games that optimize disk activity.
Most benchmarks have "real life" flaws.

They ignore interference from other threads -- that may be fighting for access to the same drive.
They pound on the resource to find the "max" that can be handled.  While this is an interesting metric, in real life if the server is at the max, it is about to keel over.  A butterfly flapping its wings can cause it to fall off the cliff.

Some optimizations:

RAID can cache things -- read and/or write -- to smooth out activity.
RAID can "lie" about when action is finished.  This is because, for example, a "battery backed write cache" can safely hold a write in ram on the controller while returning "successful write" to the user.  This may lead to a puzzling inflection point in the graph -- the device will suddenly slow down when the write cache is full.


How to do failover between AG setup for FCI-FCI servers Hope I can portrait my question well because i really get confused with AG's :)
We have the setup like below-
Windows cluster WKCLU01 with 4 nodes (Node1,2,3,4) and a file share witness in DC3, total 5 votes so ODD# fits in
Out of those 4 node
In DC1 --> SQL FCI Shared storage between Node1 and Node 2 as SQL1\inst1
In DC2 --> SQL FCI Shared storage between Node3 and Node 4 as SQL2\inst2
Now we have to setup AG between DC1 and DC2. below is my understanding:
AG can be setup between 2 replicas here SQL1\inst1 and SQL2\inst2 in ASYNC mode as per limitation and cant use SYNC mode. Assuming this is correct

Is it true automatic failover will happen between each of the FCIs only just like plain old Always on FCI's?

Now how can we do a planned failover between DC1 and DC2 as per our bi-monthly activity? Do we have TSQL or PS to help us automate this. On some msdn links i am confused doing so will cause data loss and somewhere it wont if we change sync mode?

Please suggest

When you said it needs to be set as "async" as per limitation, are you talking about SQL limitation or limitation on your side because of, for example, network latency ?
I'm pretty sure you can build you AG in Sync mode if you want so, I will presume it's limitation on your side.
To answer your questions:
If the primary node for SQL1\inst1 goes down, the cluster will failover to the other node. (just like regular FCI) - Same thing for you second instance in DC2
As you AG will be in Async, the best approch to plan a failover from your DC1 to your DC2 without any data loss would be to
1- Have a maintenance windows where there will not be too much activity
2- Switch your AG to Sync mode and let it sync
3- Once sync (you can check dmv to make sure they are sync), trigger a failover within SQL (Using the Gui in SSMS or TSQL)
4- Once the failover is completed, set the AG back to Async
Note that in Async, you cannot enable Automatic failover so if ever your FCI from DC1 fail (problem with the shared storage for example) while being primary, it won't failover to your DC2 and manually failover to it may cause data loss.


Removing WALS when replication slot is not busy One query in case of asynchronous replication with replication slots and no wal archiving if a slave node remains disconnected from master for more than wal_keep_segments, it will not be able to do the replication when it comes online.

Now my question is when the slave connects to the master again will the XLOG in master also keep on building  as the slaves replication is stuck or postgres will remove the replication slot if it is not busy / active?

Postgres Version - 10.4

One query in case of asynchronous replication with replication slots and no wal archiving if a slave node remains disconnected from master for more than wal_keep_segments, it will not be able to do the replication when it comes online.

This is not the case if the slot is being used correctly.  The whole point of a replication slot is to retain the WAL even when the replica is disconnected.  The setting of wal_keep_segments provides a floor, not a ceiling, to WAL retention (wal_keep_segments is also pretty much obsolete now that slots are a mature technology).

  Now my question is when the slave connects to the master again will the XLOG in master also keep on building as the slaves replication is stuck or postgres will remove the replication slot if it is not busy / active?

PostgreSQL will not automatically remove the slot, you need to do it manually using pg_drop_replication_slot.  However, unless you did something incorrectly (or ran into a bug), you should never be in this situation.  If the replica was correctly using the slot before it got disconnected, then the WAL should still be there when it reconnects.


Postgresql install adding openssl I install postgresql in my ubuntu Server 18.04 LTS, and I want it support OpenSSL.

I do the lengthy way with PostgreSQL source code installation, where I can select the openssl feature:

 ./configure –with-openssl
  make
  make install
  ldconfigure

Upon installing the Ubuntu server I can select with the help of tasksel PostgreSQL to be installed, or using the apt-get postgresql-10  postgresql-contrib

Does the 2nd way configure PostgreSQL to add the feature of openssl (be SSL ready)?

You can try pg_config with --configure flag.

  --configure

  Print the options that were given to the configure script
  when PostgreSQL was configured for building. This can be used to
  reproduce the identical configuration, or to find out with what
  options a binary package was built. (Note however that binary packages
  often contain vendor-specific custom patches.)

And confirm whether the installed version has SSL enabled or not!


MariaDB 5.5 -- how to customize password rules I am using RHEL 7, which includes MariaDB 5.5.

I want to set up minimum requirements for passwords: minimums length 15, 1 lowercase, 1 uppercase, 1 symbol, 1 number.

I figure that I am probably not the first person using Rhel 7 to face this problem.  Does anyone have some guidance on the best approach to add the above password rules to MariaDB 5.5 under Rhel 7?

I have so far pieced together these facts.  (Some I am not so sure are correct.)  I believe these facts indicate my best option is to just do whatever 5.5. allows.

MariaDB 5.5 doesn't do this. There is no plugin.
MariaDB 5.7
introduced new password features.
MariaDB 10.1 and later has password
plugin with this functionality.
Installing an updated MariaDB on Rhel
7 would requiring making my own RPM and possibly put the system
security at risk.
I could write my own password routines and put them
in the database.  But this is not recommended, because it is easy to
introduce a security flaw.
I can configure 5.5 to work with Linux's
PAM, or use the standard linux-user-name-matches-the-mysql-account-name authentication.

A couple of comments:

Re: "the standard linux-user-name-matches-the-mysql-account-name authentication" - I assume you mean using the unix_socket plugin for authentication. This only works on localhost, i.e. the user has to be logged in on the database server.
There is no MariaDB 5.7, but there is however a MySQL 5.7.
There is no need to build your own RPM for MariaDB 10.x on RHEL7. You can just add the relevant MariaDB 10.x .repo file to your /etc/yum.repos.d/ directory. You can use the repo config tool on mariadb.org.

It's up to you to decide whether it's worth the effort to upgrade to MariaDB 10.1+ and thereby be able to use the password checking plugins - simple_password_check and cracklib_password_check. (10.4 is now GA, though at the moment this is still relatively new, so you may want to consider 10.3 or older as a more stable release.)