Database table for exchange rates I want to create a table in my database to store the exchange rates for currencies so I don't have to make an api call every time.

My table has the following structure:

from_currency
to_currency
rate

If I have 170 currencies the table will have 170 * 170 (~29.000) entries. This seems a bit much for me. Is this the normal way to store exchange rates?

There isn't really anything wrong with that design, its almost exactly what our production fact table looks like so you aren't far off. 29,000 rows isn't that many these days.

Here is the structure that we use in our master warehouse:

CREATE TABLE Fact.ExchangeRates
(
    TradingDate    DATE         NOT NULL,
    SourceCurrency CHAR(3)      NOT NULL,
    TargetCurrency CHAR(3)      NOT NULL,
    ClosingRate    DECIMAL(9,2) NOT NULL,
    AverageRate    DECIMAL(9,2) NOT NULL
);

Then, we have a fact table in each of our client data warehouse's that is built from the master. The data warehouses are set to use a single base currency so we can eliminate the source currency:

CREATE TABLE Fact.ExchangeRates
(
    DateKey     DATE         NOT NULL REFERENCES Dimension."Date"(DateKey),
    CurrencyKey CHAR(3)      NOT NULL REFERENCES Dimension.Currency(CurrencyKey),
    ClosingRate DECIMAL(9,2) NOT NULL,
    AverageRate DECIMAL(9,2) NOT NULL
);


Gaps and islands: client solution vs T-SQL query Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?

To be specific, let us provide some test data:

CREATE TABLE dbo.Numbers
  (
    n INT NOT NULL
          PRIMARY KEY
  ) ;
GO

INSERT  INTO dbo.Numbers
        ( n )
VALUES  ( 1 ) ;
GO
DECLARE @i INT ;
SET @i = 0 ;
WHILE @i < 21
  BEGIN
    INSERT  INTO dbo.Numbers
            ( n
            )
            SELECT  n + POWER(2, @i)
            FROM    dbo.Numbers ;
    SET @i = @i + 1 ;
  END ;
GO

CREATE TABLE dbo.Tasks
  (
    StartedAt SMALLDATETIME NOT NULL ,
    FinishedAt SMALLDATETIME NOT NULL ,
    CONSTRAINT PK_Tasks PRIMARY KEY ( StartedAt, FinishedAt ) ,
    CONSTRAINT UNQ_Tasks UNIQUE ( FinishedAt, StartedAt )
  ) ;
GO

INSERT  INTO dbo.Tasks
        ( StartedAt ,
          FinishedAt
        )
        SELECT  DATEADD(MINUTE, n, '20100101') AS StartedAt ,
                DATEADD(MINUTE, n + 2, '20100101') AS FinishedAt
        FROM    dbo.Numbers
        WHERE   ( n < 500000
                  OR n > 500005
                )
GO

This first set of test data has exactly one gap:

SELECT  StartedAt ,
        FinishedAt
FROM    dbo.Tasks
WHERE   StartedAt BETWEEN DATEADD(MINUTE, 499999, '20100101')
                  AND     DATEADD(MINUTE, 500006, '20100101')

The second set of test data has 2M -1 gaps, a gap between each two adjacent intervals:

TRUNCATE TABLE dbo.Tasks;
GO

INSERT  INTO dbo.Tasks
        ( StartedAt ,
          FinishedAt
        )
        SELECT  DATEADD(MINUTE, 3*n, '20100101') AS StartedAt ,
                DATEADD(MINUTE, 3*n + 2, '20100101') AS FinishedAt
        FROM    dbo.Numbers
        WHERE   ( n < 500000
                  OR n > 500005
                )
GO

Currently I am running 2008 R2, but 2012 solutions are very welcome.
I have posted my C# solution as an answer.

The following C# code solves the problem:

    var connString =
        "Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=Benchmarks;";

    var stopWatch = new Stopwatch();
    stopWatch.Start();

    using (var conn = new SqlConnection(connString))
    {
        conn.Open();
        var command = conn.CreateCommand();
        command.CommandText = "dbo.GetAllTaskEvents";
        command.CommandType = CommandType.StoredProcedure;
        var gaps = new List<string>();
        using (var dr = command.ExecuteReader())
        {
            var currentEvents = 0;
            var gapStart = new DateTime();
            var gapStarted = false;
            while (dr.Read())
            {
                var change = dr.GetInt32(1);
                if (change == -1 && currentEvents == 1)
                {
                    gapStart = dr.GetDateTime(0);
                    gapStarted = true;
                }
                else if (change == 1 && currentEvents == 0 && gapStarted)
                {
                    gaps.Add(string.Format("({0},{1})", gapStart, dr.GetDateTime(0)));
                    gapStarted = false;
                }
                currentEvents += change;
            }
        }
        File.WriteAllLines(@"C:\Temp\Gaps.txt", gaps);
    }

    stopWatch.Stop();
    System.Console.WriteLine("Elapsed: " + stopWatch.Elapsed);

This code invokes this stored procedure:

CREATE PROCEDURE dbo.GetAllTaskEvents
AS
  BEGIN ;
    SELECT  EventTime ,
            Change
    FROM    ( SELECT  StartedAt AS EventTime ,
                      1 AS Change
              FROM    dbo.Tasks
              UNION ALL
              SELECT  FinishedAt AS EventTime ,
                      -1 AS Change
              FROM    dbo.Tasks
            ) AS TaskEvents
    ORDER BY EventTime, Change DESC ;
  END ;
GO

It finds and prints one gap in 2M intervals in the following times, warm cache:

1 gap: Elapsed: 00:00:01.4852029 00:00:01.4444307 00:00:01.4644152

It finds and prints 2M-1 gaps in 2M intervals in the following times, warm cache:

2M-1 gaps Elapsed: 00:00:08.8576637 00:00:08.9123053 00:00:09.0372344 00:00:08.8545477

This is a very simple solution - it took me 10 minutes to develop. A recent college graduate can come up with it. On the database side, execution plan is a trivial merge join which uses very little CPU and memory.

Edit: to be realistic, I am running client and server on separate boxes.


Dealing with Oracle ARCHIVELOG during huge updates It's required to update columns with metadata globally in all rows of all tables in DB. Let's say every table has a column MY_META and the aim is roughly

update ANOTHER_TABLE set MY_META = 'HELLO'

for each table.

Estimated total rows count is 2e9.

Suppose ARCHIVELOG mode is on, so during those updates a lot of extra space is going to be consumed.

The process of updates is planned to be running in production DB simultaneously with business transactions, which should not be lost.

The simplest thing to start with is temporarily installing plenty of hardware for ARCHIVELOG files.

Is there an elegant way to achieve the same goal programmatically or via tuning "secret options"?

Of course. Do not perform an UPDATE at all. Just add it as new column with the default value of HELLO.

It took 0.12 seconds and generated an insignificant amount of redo in my sandbox:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
1000000000

Elapsed: 00:00:14.34

SQL> select sn.name, ss.value from v$statname sn
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size        5964

Elapsed: 00:00:00.00

SQL> alter table t1 add my_meta varchar2(20 char) default 'HELLO' not null;

Table altered.

Elapsed: 00:00:00.12
SQL> select sn.name, ss.value from v$statname sn
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size       20408

SQL> select count(*) from t1 where my_meta = 'HELLO';

  COUNT(*)
----------
1000000000

Elapsed: 00:01:00.96


Database design help needed I am developing a hotel reservation system have come up with two approaches to model the database (MySQL).

OPTION 1

rooms

id | name | description | total_rooms

rates

id | room_id | start_date | end_date | price

reservations

id | user_id | room_id | total_rooms | checkin_date | checkout_date | total_amount

In this method, the room price can be entered through a date range (start and end), but checking for room availability is a little more complex.

However, the number of rows in the rates table is very less.

OPTION 2

rooms

id | name | description

availability

id | room_id | date | total_rooms | booked_rooms | available_rooms | price

reservations

id | user_id | room_id | checkin_date | checkout_date | total_rooms | total_price

This method involves every individual date of each room into the availability table and the table might become very big.
But I think checking for room availability and generating reports will be much easier if taking this approach.

Would like to know which method is used generally by hotel reservation systems, or if there's another different method.

OPTION 3:

I would prefer this option in order to design the model based on the question.

I have also split up ReservationRoom into another table, because there is a possibility that single user may be able to reserve multiple rooms, and every room may have different price on different dates.

Room
====
RoomId
Name
Description

RoomRate
========
RoomRateId
RoomId      /*FK to Room table*/
StartDate
EndDate
Price       /*price per room*/

Reservation
===========
ReservationId
UserId      /*FK to user table*/
etc...

ReservationRoom
===============
ReservationId   /*FK to Reservation table*/
RoomId      /*FK to Room table*/
FromDate    /*Reservation From Date*/
ToDate      /*Reservation To Date*/
Price

I don't see any issues writing queries for the above proposed schema. Please take care of indexes and unique keys, which is beyond the scope of this question, but I thought it's worth to mention!

In order to filter the available rooms, you need to be dependent on ReservationRoom table. (Edited)

I think, this design provides you flexibility. Please highlight, if you notice any flaws in this design?


Why Oracle 11g installs in folder C:\app\ Just curious, now Oracle 11g installs by default in folder C:\app\

Why? Before, Oracle was installed by default in folder C:\oracle... Why now is created "app" folder?

This is explained in the documentation, here. See "B.2 Changes to the Optimal Flexible Architecture for Oracle Database".


Finding reason for page splits So normally we have 20-85 Page splits/s but sometimes this spikes up the 700 - 2500 range.

So to figure out what was happening i created a Extended Event with the following:

CREATE EVENT SESSION MonitorPageSplits ON SERVER
ADD EVENT sqlserver.page_split
(
   ACTION (sqlserver.database_id, sqlserver.sql_text)
)
ADD TARGET package0.asynchronous_file_target
(
   SET
       filename = N'***\MonitorPageSplits.etx',
       metadatafile = N'***\MonitorPageSplits.mta'
);
GO

ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start;
GO
--Wait until we notice a page split and run code below!
ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = STOP;
GO

Then to get the data i used the following query

SELECT
   split.value('(/event/action[@name=''database_id'']/value)[1]','int') AS [database_id],
   split.value('(/event/data[@name=''file_id'']/value)[1]','int') AS [file_id],
   split.value('(/event/data[@name=''page_id'']/value)[1]','int') AS [page_id],
   split.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') AS [event_time],
   split.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text]
FROM
   (
       SELECT CAST(event_data AS XML) AS split
       FROM sys.fn_xe_file_target_read_file('***\MonitorPageSplits_0_131317886982910000.etx', '***\MonitorPageSplits_0_131317886982910000.mta', NULL, NULL)
   ) AS t
ORDER BY [event_time]
GO

Now plotting the data in excel diagram gave the following

So then i started to look at the data around 2017-02-17 10:14:39 as the first spike then.
Which resulted in the following data:

database_id file_id page_id event_time              sql_text
7           0       0       2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.607 Unable to retrieve SQL text
7           1       37769   2017-02-17 10:14:39.607 Unable to retrieve SQL text
.....

So then i used the following DBCC TRACEON (3604);dbcc page (7, 1, 26536, 1);DBCC TRACEOFF (3604); and that gave me Metadata: ObjectId and with that i could get the table.

But how do i figure out what caused the page split spike?

Also database_id 7 is the main application on the server and file_id 1 is the Data file of the database.

Some more analysis:

3 tables are involved

2 small tables that are involved in one page split each.

The rest of page splits are caused in the table that has the most data and most inserts/updates in the application.

Am i worrying for nothing is it normal to have page splits like this?

I'd suggest the first thing to determine is whether or not the server workload actually struggles during those spikes. Do you see for example IO being maxed out, or other queries being blocked (due to the splits increasing the time the write transactions take to complete) or slowed? If not, the page splits are not an immediate concern, but still may be worth looking into.

The question is, are these "good" or "bad" page splits. This link might help you determine what you're seeing. Logging your index fragmentation levels before and after a spike might also be a simpler way.

"Good" page splits are simply inserts to the end of an increasing index (clustered or otherwise)that require a new blank page, so it's really not a page split as generally thought of, even though SQL Server counts it as such--presumably because there is some overhead, but probably not more than the inserts cost in general.
"Bad" page splits are updates or inserts to the middle of an index that overflow the page, and are the ones that cause both internal and external fragmentation, with external not much of an an issue with SSDs and/or shared storage, and internal being of more potential impact due to the IO and cache memory they waste.

It could be that you've got a mix of good and bad, perhaps good into the clustered index and bad in multiple non-clustered indexes. That's pretty much unavoidable, and you'll just need to consider your index maintenance and possibly a specific fill factor on indexes that are frequently affected. But read https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ first.

However if you find your clustered index is being bad-splitted, then it may be worth considering whether a clustered index that better supports your inserts would be in order. Or if the splits are caused by updates adding more data during the life of a record, a specific fill factor might be in order, but really only if the updates are evenly distributed throughout all your data, since a fill factor to support only your recent data would waste a lot of space/IO/cache if most of your data is static over time.
The ideal clustering config really depends how your table is used overall though, not just on how it's written to.


How to write correct array string parameter for mysql query? I'm trying to put up a correct string to pass as a parameter to a simple query.
Correct query that returns records:
SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04');

...but if I pass this (''2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04'') as a parameter it doesn't work.
I also tried to pass this ('2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04')
... but it also doesn't work.
My idea is to put this string as a parameter through a procedure.
Perhaps like this:
IN parameter TEXT

BEGIN

SET @c = CONCAT('SELECT id_s FROM group_of_id_s
WHERE date IN (', '''' , parameter, '''', ')');

PREPARE stmt from @c;
EXECUTE stmt;

END

any reference or helpful advice would be appreciated.
EDIT: Based on very helpful example from @nbk and changing date format before comparing it to an array, I managed to put up a valid query that works like a charm. You can see it below in all of its glory. ;O) Response from @nbk is therefore considered as solution. Thanky you very much @nbk.
SELECT id_s FROM group_of_id_s
WHERE find_in_set(DATE_FORMAT(`date`, '%Y-%m-%d'), parameter) > 0;

Use find_in_Set
Schema (MySQL v5.7)
CREATE TABLE group_of_id_s (id_s int ,`date`date);

INSERT INTO group_of_id_s VALUES(1,'2020-08-01'),(2,'2020-07-01');

delimiter //
CREATE PROCEDURE myproc (IN parameter TEXT)
       BEGIN
         SELECT id_s FROM group_of_id_s
         WHERE find_in_set(`date`, parameter) > 0;
       END//
delimiter ;

Query #1
CALL myproc("2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04");

| id_s |
| ---- |
| 1    |

View on DB Fiddle


PostgreSQL Index Caching I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:

PostgreSQL indexes, like rows, live on disk but may be cached.
An index may be entirely in the cache or not at all.
Whether it is cached or not depends on how often it is used (as defined by the query planner).
For this reason most 'sensible' indexes are going to be in the cache all the time.
The indexes live in the same cache (the buffer cache?) as rows, and therefore cache space used by an index is not available to rows.

My motivation for understanding this follows on from another question I asked where it was suggested that partial indexes can be used on tables where a majority of the data will never be accessed.

Before undertaking this, I'd like to be clear that employing a partial index yields two advantages:

We reduce the size of the index in the cache, freeing up more space for rows themselves in the cache.
We reduce the size of the B-Tree, resulting in a faster query response.

Playing a bit with pg_buffercache, I could get answers to some of your questions.

This is quite obvious, but the results for (5) also show that answer is YES
I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.)
Since the planner is who decides whether to use an index or not, we can say YES, it decides caching (but this is more complicated)
The exact details of caching could be derived from the source code, I couldn't find too much on this topic, except this one (see the author's answer, too).  However, I'm pretty sure that this again is far more complicated than a simple yes or no.  (Again, from my edit you can get some idea - since the cache size is limited, those 'sensible' indexes compete for available space.  If they are too many, they will kick each other from cache - so the answer is rather NO.)
As a simple query with pg_buffercache shows, the answer is a definitive YES.  It is worth to note that temporary table data don't get cached here.

EDIT

I've found Jeremiah Peschka's terrific article about table and index storage.  With information from there, I could answer (2) as well.  I set up a small test, so you can check these yourself.

-- we will need two extensions
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pageinspect;

-- a very simple test table
CREATE TABLE index_cache_test (
      id serial
    , blah text
);

-- I am a bit megalomaniac here, but I will use this for other purposes as well
INSERT INTO index_cache_test
SELECT i, i::text || 'a'
FROM generate_series(1, 1000000) a(i);

-- let's create the index to be cached
CREATE INDEX idx_cache_test ON index_cache_test (id);

-- now we can have a look at what is cached
SELECT c.relname,count(*) AS buffers
FROM
    pg_class c
    INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
    INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 pg_statistic_relid_att_inh_index |       4
 pg_operator_oprname_l_r_n_index  |       4
... (others are all pg_something, which are not interesting now)

-- this shows that the whole table is cached and our index is not in use yet

-- now we can check which row is where in our index
-- in the ctid column, the first number shows the page, so
-- all rows starting with the same number are stored in the same page
SELECT * FROM bt_page_items('idx_cache_test', 1);

 itemoffset |  ctid   | itemlen | nulls | vars |          data
------------+---------+---------+-------+------+-------------------------
          1 | (1,164) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (0,1)   |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)   |      16 | f     | f    | 03 00 00 00 00 00 00 00
          5 | (0,4)   |      16 | f     | f    | 04 00 00 00 00 00 00 00
          6 | (0,5)   |      16 | f     | f    | 05 00 00 00 00 00 00 00
...
         64 | (0,63)  |      16 | f     | f    | 3f 00 00 00 00 00 00 00
         65 | (0,64)  |      16 | f     | f    | 40 00 00 00 00 00 00 00

-- with the information obtained, we can write a query which is supposed to
-- touch only a single page of the index
EXPLAIN (ANALYZE, BUFFERS)
    SELECT id
    FROM index_cache_test
    WHERE id BETWEEN 10 AND 20 ORDER BY id
;

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..8.54 rows=9 width=4) (actual time=0.031..0.042 rows=11 loops=1)
   Index Cond: ((id >= 10) AND (id <= 20))
   Buffers: shared hit=4
 Total runtime: 0.094 ms
(4 rows)

-- let's have a look at the cache again (the query remains the same as above)
             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 idx_test_cache                   |       4
...

-- and compare it to a bigger index scan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
    FROM index_cache_test
    WHERE id <= 20000 ORDER BY id
;

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..666.43 rows=19490 width=4) (actual time=0.072..19.921 rows=20000 loops=1)
   Index Cond: (id <= 20000)
   Buffers: shared hit=4 read=162
 Total runtime: 24.967 ms
(4 rows)

-- this already shows that something was in the cache and further pages were read from disk
-- but to be sure, a final glance at cache contents:

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2691
 idx_test_cache                   |      58

-- note that some of the table pages are disappeared
-- but, more importantly, a bigger part of our index is now cached

All in all, this shows that indexes and tables can be cached page by page, therefore the answer for (2) is NO.

And a final one to illustrate temporary tables being non-cached here:

CREATE TEMPORARY TABLE tmp_cache_test AS
SELECT * FROM index_cache_test ORDER BY id FETCH FIRST 20000 ROWS ONLY;

EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tmp_cache_test ORDER BY id;

-- checking the buffer cache now shows no sign of the temp table


Index Fragmentation - log issues I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a maintenance plans to some of my new servers and understand the issues with my old servers.

My database log size is 50GB(converting MB to GB) and when I use the following query:

SELECT name,
   size/128.0 FileSizeInMB,
   size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS EmptySpaceInMB
FROM sys.database_files;

it returns 25GB as the Empty space consistently. My database is in FULL recovery mode with hourly log back up. Doesn't this log file get trimmed at some point in time after hourly transactional backup and reduce the size of the log file?
We have a defragmentation plan set up at night. This plan runs for 15 mins and rebuilds all indexes above 40% fragmentation level. The way the DBA from one of our vendors explain is that during the rebuild the tables are locked and hence make the log file grow. I don't understand this clearly.
Doesn't the log files grow in both rebuilding and re-organizing? Cant a long running re-organize fill up logs quickly?
How frequently should we update statistics after a daily index re-organize - immediately or weekly/monthly?

Doesn't this log file get trimmed at some point in time after hourly
  transactional backup and reduce the size of the log file?

No, it does not reduce the size of the log file on disk. The physical log file is made up of virtual log files (VLFs) and each VLF represents a portion of the transaction log. When a VLF is included in a transaction log backup (because there are no active transactions using that VLF) that VLF is marked inactive and can be overwritten. These inactive VLFs are considered free space within the log file because they can be overwritten at any time by an active transaction if required.

It is possible to shrink the physical file and reclaim the disk space, however, it is generally not recommended as the log file most likely requires that space for large operations, such as index maintenance, and regrowing the log file is a performance bottleneck.

Overview of Transaction Log in SQL Server

  The way the DBA from one of our vendors explain is that during the
  rebuild the tables are locked and hence make the log file grow. I
  don't understand this clearly. Doesn't the log files grow in both
  rebuilding and re-organizing? Cant a long running re-organize fill up
  logs quickly?

Index maintenance operations (REBUILD, REORGANIZE etc) are logged in the transaction log, even ONLINE operations that don't lock the underlying table. So the vendor's DBA is correct that rebuilding indexes can cause log growth, but it is not due to table locking, it is because it is a logged operation.

Check out these links for additional information on space utilisation during index DDL:

Transaction Log Disk Space

Disk Space Requirements for Index DDL

  How frequently should we update statistics after a daily index
  re-organize - immediately or weekly/monthly?

It depends on a number of factors, how many rows in the underlying table/index, rate of data modifications, are you using trace flag 2371 and auto-update statistics on your databases etc?

Also, how frequently are you rebuilding your indexes, because the statistics related to your indexes will be getting updated at the same time, and if you're tables have clustered indexes then your column statistics should also be getting updated.

If you're using Auto-Update Stats, then you can read about the threshold for updating stats here. There's also some useful information in the answers to a similar question here.


Oracle explain for cascading delete is wrong My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.

If I hit the explain button on a statement like this DELETE FROM TOP_LEVEL_TABLE WHERE SOMETHING = 'whatever' it only shows my the involvment of the TOP_LEVEL_TABLE even if 30 other tables are involved.

How can I get a more realistic result?

I don't think this is possible.

If I'm not mistaken, the optimizer doesn't even know (or at least doesn't try to know) that the statement will "touch" other tables as well.

It's not explicitely documented, but the following quote from the manual only talks about the tables "referenced" in the SQL statement (emphasis is mine):

The row source tree is the core of the execution plan. It shows the following information:
   -  An ordering of the tables referenced by the statement
   -  An access method for each table mentioned in the statement
...

I think the important part is: "tables referenced by the statement". No mentioning of "dependent" tables.

Edit:
If you need to extract that information, you probably have to trace your session and look at the TKPROF output.


Sync MySQL 4.0 table to MySQL 5.6 server every few minutes I am tasked with getting a copy of a table on a MySQL 4.0 server synced to a completely different database on a MySQL 5.6 server every few minutes for the purposes of having that table available during a migration phase for a really old application.  Basically this:

10.10.10.10:oldb.table -> 20.20.20.20:newdb.table

The table is ~65,000 rows and ~18MB.

Obviously I cannot use replication with version 4.0.  I have looked at using mysqlhotcopy, but it appears that it only copies entire databases, and I need the table to live in a different database on the destination host.  I have also looked at using mysqldump, however I cannot have the table on the new server be dropped for the new data to be inserted resulting in queries that see an empty table, and if I use --insert-ignore --no-create-db --no-create-info it still does not account for deleted rows on the source table.  I also looked at selecting all rows from the existing table and doing a REPLACE INTO, or INSERT/UPDATE ON DUPLICATE KEY but this would also not account for rows that were deleted from the source table.  Any thoughts on how to accomplish this?

This looks interesting and might (just might) be a solution to your issue. It shows how a 4.1 server can simulate Stored Procedures (SPs - not available till version 5 of the MySQL server) using the Federated storage engine. Basically, it shows that a 5.x server can access data on a 4.x server and even update that data. It also shows that you can use 5.x to read the data also. This is probably a good fit for your requirements, however the federated engine does not support transactions. It's also covered here (Morgan Tocker is a MySQL community manager).


How do I Continue After a Failure in SSIS I want to implement something like the following:

In this package, I want to loop through a list of database names, dynamically modifying connection manager settings.

However, the first failure on "Test Connection" will halt and fail the package execution.

But I'd like to continue my Foreach loop. I get the sense that this should be possible (otherwise what's the use case for these red failure arrows?)

Things I've tried include messing with "MaximumErrorCount".

To answer the question as I asked it, it is possible to continue after failures by making sure that MaximumErrorCount is zero for the task and its parent containers... In this case that means the "Test Connection" task, the "foreach" container and the package itself.

I ended up doing something different though. And Jamie Thomson's Verify A Connection Before Using It was helpful here. Here's what I did:

I replaced the Test Connection task with a script task. The script task was almost exactly like Jamie's script except that I

only used one connection manager, not all of them,
the task result was always success
and I set a new variable "User::ConnectionSucceeded" to the result of the connection test.

I also changed the arrows coming out of the test connection task to evaluate expressions, they evaluate:

@[User::ConnectionSucceed] == True
@[User::ConnectionSucceed] == False

respectively.

I also had to remember to edit the multiple constraints going into the "Update LastMonitored" task. I changed them to use a logical "OR".

Here's what my SSIS package looks like now:


set-returning functions in postgres I've been trying to use the generate_series() function of Postgres to obtain a a table like this:

I didn't arrived to a solution yet, so any ideas would be appreciated.

I guess something like this would do:

select x.n, y.n
from generate_series(1,3) as x(n)
cross join generate_series(1,4) as y(n)
order by y.n, x.n

The idea is to take the Cartesian product between a set of 3 elements and a set with 4 elements. In this case Z_3 and Z_4

Since 3 and 4 is co-prime ( gcd(3,4) = 1 ), we can conclude that the Cartesian product between Z_3 and Z_4 is isomorphic with Z_12 (if only we had formatting options ...). See zm-cross-zn-isomorphic-to-zmn for a proof. I.e we can use Z_12 and use modulo arithmetic to achieve the desired set:

select mod(x.n,3)+1, mod(x.n,4)+1
from generate_series(1,12) as x(n)
order by mod(x.n,4)+1, mod(x.n,3)+1


Cannot TNSPING from VirtualBox Client to Oracle Database On Host I am new to Oracle, attempting to learn about connecting remote clients to an Oracle 12c Database server instance.

SUMMARY: I cannot get TNSPING to work from a client machine to a known good database on a host machine.

DETAILS:

I am hosting the Oracle 12c database on my Windows 7 x64 development machine. I've done numerous tests of connecting to the database from that machine - TNSPING, SQL*Plus, ASP.Net web page, and they all connect to the database no problem. Great!

So I figured a next logical step is to try to connect from another client inside my network. I created a Win7 client using VirtualBox running on the same host (RM08) as the Oracle Database. I completely disabled Windows Firewall on that client machine. I then installed the Oracle Client on that virtual machine. That gives me SQL*Plus and various other tools, and led me through a configuration wizard to create a tnsnames.ora.

When I tnsping from that virtual/client machine to the Oracle host I get "ORA-12541: TNS: no listener". But I am confident the listener is in fact running, because the database works fine when I access from the host machine itself.

Further information:

-- The host machine has IP address 192.168.1.6

-- The virtual/client machine has IP address 10.0.2.15

-- From the client machine I get success when I ping the host

-- From the client machine I can successfully open up various ASP.Net web pages at http://192.168.1.6

-- Windows firewall is completely disabled on BOTH the client and host machines.

On the client machine, the tnsnames.ora file has contents:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

On the client machine, when I issue:

tnsping orcl

I get:

C:\Users\rmvirt>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:11:37

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.6)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
TNS-12541: TNS:no listener

If I ping by IP address, I get a similar message, but it mentions EZCONNECT, I assume because I including that option when installing Oracle client:

C:\Users\rmvirt>tnsping 192.168.1.6

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:13:26

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=192.168.1.6)(PORT=1521)))
TNS-12541: TNS:no listener

Finally, if I issue

tnsping badname, I get:

C:\Users\rmvirt>tnsping badname

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:15:15

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

That at least gives me confidence that my tnsnames.ora file is syntactically correct and being referenced.

I have read this somewhat similar question, with no luck: Using Oracle client to connect to remote database

I have also read the "Testing Connections" section from the "Database Net Services Administrators Guide" at Using Oracle client to connect to remote database.

But I am still stumped.

Can anyone suggest other troubleshooting steps?

Thanks!

On the database server, issue the command 'lsnrctl status'.  You should get a list of database services known to the listener.  There should be one for your database.  It is the listed 'service name' that should be the subject of the 'SERVICE_NAME parameter in your tnsnames.ora.  There is a more complete explanation of your error at http://edstevensdba.com/oracle-tns/ora-12514-tnslistener-does-not-currently-know-of-service/


mysql logging activity from specific user or ip I have mysql server.

The server is accessed by my application, and by external auditor (person using mysql workbench).
The auditor has specific user and password and dedicated IP and it is granted only for select privileges.

I need to log the activities from the auditor. Is it possible to do it somehow ? If not can I log the packages that are coming from the network ?

You could use the general log. In fact, you should try using the MySQL table version of the general log.

If you run this:

SHOW CREATE TABLE mysql.general_log\G

You should see something like this:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.14 sec)

mysql>

The user_host column will record the MySQL user and the IP Address/DNS Name the command came from.

You are probably saying right now, "That table is a CSV file. I would have to parse it." That's true, you would have to. However, did you know that you can convert it to a MyISAM table? I have actually tried this out for one of my employer's DB Hosting Clients, and I wrote a post explaining it (See the Feb 24, 2011 post down below).

Here are the basic steps:

Make the mysql.general_log table MyISAM

Run the following:

CREATE TABLE mysql.general_log_original LIKE mysql.general_log;
ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Enable the general log

Add the following to /etc/my.cnf

[mysqld]
log-output=TABLE
general-log

If you also want the text version of the general log, add this:

[mysqld]
log-output=TABLE,FILE
general-log
general-log-file=/var/log/mysql_general.log

Restart mysql

Simply run service mysql restart

I have many posts about using the general log and the slow log as MyISAM tables:

Jan 22, 2013 : How often does the slow_log table get updated in MySQL?
Nov 10, 2012 :  Redirecting MySQL errors and slow logs into another database
Jan 08, 2012 : How to enable MySQL general log?
Dec 27, 2011 : How to do MySQL User Accounting
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?
Feb 24, 2011 : Audit logins on MySQL database

CAVEAT

MySQL 5.5.28 has recently introduced the audit log plugin and how to install it.

Here are the options

audit-log-buffer-size
audit-log
audit-log-file
audit-log-flush
audit-log-policy
audit-log-rotate-on-size
audit-log-strategy

These are new to me so I have nothing pros or cons at this time on the Audit Log Plugin.


Unexpectively slow data loading into Oracle Big amount (4.000.000+) of files (1-4Mb each) are loading into Oracle 10g for about several days.
Table is allocated in big tablespace, which was created on special volume (labeled BIG_VOL) over hardware RAID (Adaptec 3805 , 8 HDD x 2Tb, raid level 6).

RedoLogs was moved on two another SSD drives. No archivelog.
There are only DBF and control files on BIG_VOL.

Transaction closed (by COMMIT) after every loaded file.

Perfomance Monitor shows ~6-9Mb per second on writing for this BIG_VOL !
Simple copy any big file shows about 80+Mb per second!

Can I do something to improve speed?

There's some information missing that would be useful but my first approach would be to concatenate the files into a smaller number of large input files to cut down the file handling overhead. Look at the load method and check if the data profile supports the use of SQL Loader direct loads. If SQL loader is not appropriate maybe use external tables and add the APPEND hint to the INSERT.

You may be able to think about how the table is partitioned and if the partitioning is reflected in the files, i.e. each file being relevant to one partition. This might give you scope for making the load parallel based on the partition criteria.

The above assumes that you are inserting data but if you have updates in there as well then I would go for external tables with a merge statement.
This has worked for me with 10g in the past. I had a shell script loading each file based on its suitability for each loading method.
We loaded up to 80 million records in around 3 to 4 hours from, in our case, hundreds of files rather than millions. So taking a complete guess at 100 records per file you should be able to load them in less than a day.


can sql generate a good plan for this procedure? I have read that procs that do not always do the same thing per se, will not always have a good plan generated.  That is (correct me if I'm wrong), if I have a proc that if the day is even it reads from table X otherwise it reads from table Y and the first time it executes is even, then the plan generated will be optimized for reading from table X and not Y, and sql will use that plan even when reading from Y, that imho is simple to understand and try to avoid, but what about procedures that act on the same table.

Like the sample below (obvioulsy a very simple example), if the first time it runs, and the item does exist, and subsequent runs generally do not (or vice-versa), would I end up with a "not so great" plan which would affect performance?

CREATE PROCEDURE dbo.MyProc
(
@data    VarChar(25),
@name    VarChar(25)
)

AS

    IF NOT EXISTS (SELECT * FROM dbo.MyTable WHERE myname = @name)
    BEGIN
        INSERT dbo.MyTable (myname, mydata)
        VALUES (@name, @data)
    END
    ELSE
    BEGIN
        UPDATE dbo.MyTabel
        SET mydata = @data
        WHERE myname = @name
    END

SQL Server 2005+ has statement level recompilation. Therefore, IIRC you'll get an optimal plan depending on branch.

However, your code isn't concurrency-safe. Two overlapping calls can get true for the NOT EXISTS: you get an error. This is why you use MERGE on SQL Server 2008.

Or assorted JDFI patterns for older versions. Also see Need Help Troubleshooting Sql Server 2005 Deadlock Scenario which has several options: it depends on your load too


Rename Availability Group and AG script I have two questions for you guys. I really appreciate your help.

1.- Is there a way to rename an Availability Group?

2.- Is there a way to get the T-SQL statements in order to re create a Availability Group in case that I need to drop it and create it again?

If there's information that you want me to share, please let me know and I will provide it.

Once created an Availability Group (AG) cannot be renamed, although the databases within the AG can be renamed. You'd have to re-create it.
See here for the options for ALTERing an AG: https://msdn.microsoft.com/en-us/library/ff878601.aspx
When creating the AG using the GUI (wizard), once all the steps are completed you will see a 'Script' button which gives the option to generate a script of those steps to a new window.
You can then either save the script for later and quit the GUI, or just "finish" the wizard to actually create the AG. (The script will stay in the window)
It's a good idea to keep the script in case it needs to be re-created later (e.g. if you want to rename the AG...)

It doesn't seem to be currently possible to "Script As..." an AG in the way you would for a table or stored procedure for example.

You could also write the CREATE AVAILABILITY GROUP statement from scratch using the MSDN boilerplate code as a starting point (https://msdn.microsoft.com/en-us/library/ff878399.aspx) but that is likely to be very cumbersome and error-prone compared to using the GUI which will check you've got everything right before it lets you proceed!


Trim characters from a field but only if it doesn't result in a duplicate? I have a database of servers and some servers were entered with the fully qualified domain name.  I want to rename all of these.  However, in some instances the server has been entered both ways.  For instance, servername.domain.com and servername.  When I run my query below, these cause an error because of course that column can only contain unique records.

UPDATE ServerData
SET    ServerName = replace(ServerName, '.domain.com', '')
WHERE  ServerName LIKE '%.domain.com';

Error "Cannot insert duplicate key row in object 'dbo.ServerData' with unique index 'UK_ServerData'."

How can I modify the query to simply ignore records that would generate an error so I can deal with them individually later?

This will check to make sure the value you are writing doesn't already exist in that field:

UPDATE s
SET    ServerName = replace(ServerName, '.domain.com', '')
FROM serverdata S
WHERE  ServerName LIKE '%.domain.com'
AND NOT EXISTS (SELECT 1
                FROM serverdata s2
                WHERE s2.servername = <> replace(s1.ServerName, '.domain.com', ''))


Find time of execution of old query in Oracle I have a delete query which was run on a table in Oracle 11g Enterprise Edition about 2 months back. I need to find the exact time of when the query was executed.

I found the query in the DBA_HIST_SQLTEXT view. But I do not see any date/time field in the table to find the exact time of execution. Is there any way that this can be found out?

I also tried to extract the same from AWR report. But snapshots are available only till 1.5 months back.

It means you have the SQL_ID of the SQL statement and now you want to know when it was executed.

You can use the following query where you can supply the SQL_ID of the SQL query that you have in DBA_HIST_SQLTEXT.

SQL> SELECT timstamp FROM dba_hist_sql_plan WHERE sql_id='your sql_id';


how to view colmn names of joined tables Is there a way to make this query:

SELECT * FROM food
JOIN conditions ON food.id = conditions.id
JOIN flavors ON food.id = flavors.id
JOIN symptoms ON food.id = symptoms.id
JOIN tags ON food.id = tags.id
WHERE 1

show column names instead of 0-infinity

To be a little more clear, I need the column names from the tables (flavors,conditions,symptoms, and tags) to show up instead of a numerical value.

The above query gives me this but far longer:

 9 => string '15.012820512821' (length=15)
 10 => null
 11 => null
 12 => string '17.846153846154' (length=15)
 13 => null
 14 => null
 15 => string '5.3846153846154' (length=15)
 16 => null
 17 => null

and the column names those numbers represent:

 0 =>
    array (size=1)
      0 => string 'id' (length=2)
  1 =>
    array (size=1)
      0 => string 'name' (length=4)
  2 =>
    array (size=1)
      0 => string 'addadhd' (length=7)
  3 =>
    array (size=1)
      0 => string 'alzheimers' (length=10)

In phpmyadmin I can see that  9 => string '15.012820512821' corresponds to addadhd, but 9 != 2, so I can't just match those values. If I had the column name instead of 9, I could easily match the value. I'm very frustrated.

Just putting this point out first and foremost, you should not use select *, you should declare the columns you want, even if you want everything you should have everything listed one by one so any column additions later on don't potentially screw up other functions.

Now that's over

This will get you the column names, but I can't guarantee the order that you will be pulling the data out, they will be listed here generally in the order that the tables / columns were created, which will be potentially be different to what you are getting in your initial query,

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema =  'DatabaseName'
AND table_name
IN (
'food', 'conditions', 'flavors', 'symptoms', 'tags'
)

you could replace the select with

SELECT CONCAT( table_name,  '.', column_name,  ', ' )

and then copy the output to the select statement (remember to remove the final,) which then allows you to pick the exact order you want everything to come out in which is a win under you're not using select * and a win under you get to choose which columns are where


Regex rules engine based on SQL I have a table which I use to determine actions based on conditions provided. This is what it looks like right now.

IPAddress, MACAddress, Hostname, ScriptName, Argument1, Argument2, Argument3
10.2.1.* ,  .*       , .*      , diagnostics.sh, -runAll,        ,

In this table, the first three columns are the conditions, and the last four columns are the actions. Values of the condition cells are regex values that I do REGEX_LIKE on, so when I am looking up the correct script to run on a device, I look at it's IP (or MAC I'm given one or the other, not both) and hostname and if the regex matches, then I get the script name that needs to be run and arguments for that script.

Some scripts have one argument, some have two and others have three.

How do I modify this so I can an arbitrary list of conditions and actions. e.g. I am given a Key, Value map (attribute name, attribute value) for conditions, and once a rule has been satisfied, I retrieve a list of action Key, Value elements that are relevant for that device.

To keep things simple, there won't be any updates done to this database once it is initialized. It's initialized on start up by reading rules from a flat file, and the used at runtime to determine rules that have been satisfied for provided input.

This is the schema I think is better suited for this, but I don't know if this is correct and I don't know how to get the matching rules either.

Table Name: Column List
Attributes: attributeId, name, value
Conditions: conditionId, ruleId, attributeId
Actions   : actionId, ruleId, attributeId
Rules     : ruleId

PostgreSQL

I'm not sure why you would want to use regexes for this. PostgreSQL already has specific types.

CREATE TABLE whatev (
  IPAddress  inet,
  MACAddress macaddr,
  Hostname   text,
  ScriptName text,
  CHECK ( IPAddress IS NULL OR MACAddress IS NULL )
);

INSERT INTO whatev
VALUES ( '10.2.1/24', null, 'Evan Box', 'rmrf' );

Don't store ip's, netmasks, or macaddresses as text.

SELECT * FROM whatev
WHERE ipaddress >> '10.2.1.4';

I don't much understand the rest of what you want to do. I would also point you to ip4r which is awesomely great at this.

As far as storing conditions I'm not entirely sure what you mean, but you can certainly store conditions as inet there are a rich set of operators for it


Run SQL Server Snapshot Agent automatically I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works just fine. Only problem is, that it is never starts. If I look into the Snapshot-Agents status, I can see that it has never been run before. If I just click start in that dialogue, everything works as expected afterwards. But I don't want the user to have to Open the sql server studio and activate the snapshot agent manually.

What I already tried:

running the snapshot.exe with all params set. It seems to work fine, but afterwards I get an error in my replicationmonitor that something from a path could not be read, but there arent any settings for a path, so I don't know how to fix this.
exec msdb.dbo.sp_start_job .. here I tried some params but I have no clue what to put here. My best guess was @job_name='myreplication' but I just get an error that it cant be found.

What is the easiest way to automate this?

I believe you are looking for sp_startpublication_snapshot.  You'll want to execute this at the Publisher on the publication database.

Regarding the error: from a path could not be read, you'll want to ensure the Snapshot Agent process account has sufficient permissions to generate the snapshot.  The process account needs the following permissions per BOL:

At minimum, be a member of the db_owner fixed database role in the distribution database.
Have write permissions on the snapshot share.
The account that is used to connect to the Publisher must at minimum
be a member of the db_owner fixed database role in the publication
database.

This is covered in Replication Agent Security Model.


Query if server is participating in an availability group Is there a way to query whether a server is participating in an availability group?

SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

please see this link for more info..

https://www.pythian.com/blog/list-of-sql-server-databases-in-an-availability-group/


Postgres replication globally Please advise which Postgres version is better for setting up a replication between a master which resides in one continent and one of its standby server is located in a different continent, and network latency is high.

Thanks.

I'm sorry, but IT isn't magic...
If your network has high latency how can you expect data change to be spread without latency ?

But if you don't care about your writing rate on the master, you can slow it down with synchronous replication and you won't have any latency between your master and your standby. Of course you will now face writing lag on your master!

I'm afraid postgreSQL isn't suitable for your need in that case...
Here, you're facing the CAP theorem issue. Maybe, you could try another approach to favor consistency and partition tolerance as MongoDB, BigTable or Redis ?


mysql percona 5.7 not releasing memory we are using percona mysql 5.7 version.
MySQL not releasing the memory after operation complete.
we are very much grateful if anyone can guide us to resolve issue.
Thanks

MySQL doesn't "release memory" that is allocated statically, for example innodb_buffer_pool_size. These allocations will consume more memory until they reach the configured limit and then stay there. The only way to "release it" is to reduce the size of those variables. You can reduce innodb_buffer_pool_size at runtime without restarting mysqld on 5.7.


Ubuntu cannot open shared library mysqlx.so MySQL Server version:

Ubuntu: 5.7.23-0ubuntu0.18.04.1-log (Ubuntu)

MySQL installed using apt-get.

When installing a plugin with the following command:

mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

...I receive the following error message:

  ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/mysqlx.so' (errno: 2 /usr/lib/mysql/plugin/mysqlx.so: cannot open shared object file: No such file or directory)

What can't Ubuntu locate/find the mysqlx.so library?

Three comments:

One: Maybe you are using  'mysqlx.so; and you forgot the closing " ' "

I mean please use 'mysqlx.so' in stead of 'mysqlx.so

Two: We expect to find the file mysqlx.so in the that /usr/lib/mysql/plugin/.

Is the file mysqlx.so in than path /usr/lib/mysql/plugin/

Three: If so, maybe you have to check if the file mysqlx.so has full permissions for the user that controls mysql.


Can I tell Postgres that a new constraint is valid without actually running the check? I have a constraint that ensures one of three columns is populated, and I'm adding a new column, so I'm replacing the old constraint with a four-column version. Since I know from the old constraint that one out of three columns must be populated, I can be sure that one out four will be populated and the new constraint is valid.

I know I can add it as NOT VALID and later validate it concurrently, and that's the kosher thing to do, but it's a bit of a waste of time for Postgres to validate all 5 billion valid rows. Can I mark it as valid without checking all the rows again? Alternatively, is there any downside to leaving it invalid indefinitely?

You can safely leave the check constraint invalid, if that doesn't bother you. I cannot think of any place where a check constraint is used for query planning, and it will be enforced for data modifications.

It should be safe to set convalidated to TRUE in pg_constraint in this case, but since you get no actual benefit from it, why do it?


Full-text: lots of FT_MASTER_MERGE waits in SUSPENDED state after multiple full-text indexes created (server hangs) We did a test on SQL Server 2014 when we had 10 databases, 100 different schemas in each database, 10 of small (~50 rows) tables in each schema (so 10K tables in total) and we created full-text indexes on all these tables in all these databases simultaneously.

In several minutes we found that SQL Server stopped to accept any connections (except ADMIN:. connection). If we restart the server, we can connect, but in some time it hangs again. After some investigation we found that it is caused by consuming all working threads, and dm_os_tasks and dm_os_waiting_tasks showed us that there is a lot FT_MASTER_MERGE waits in SUSPENDED state. We googled that "Full-text is waiting on master merge operation", but found no more actual info about it.

We tried different full-text catalog configurations: one catalog per DB, one catalog per schema, one catalog per index. Anyway the server hangs with all these suspended tasks.

What's the root cause of the waits, how this can be fixed/mitigated?

And what is the recommended way to enable full-text on such a large amount of tables?

You will need to stagger the operations instead of doing all at once.  Connect item does not talk about accepting new connections.  But due to this wait threads are not being released (in your case) and new connectoins are not possible.

Ref:

https://connect.microsoft.com/SQLServer/feedback/details/1069538/fulltextindex-master-merge-operation-was-not-done
https://www.sqlskills.com/help/waits/ft_master_merge/

This is a known issue with SQL Server. From the connect item:

  This is caused by the way our current job scheduler is setup resulting
  in multiple master merge operations getting queued up but never
  signalled. To be clear, this only happens when multiple
  indexing/reorganize operations are invoked simultaneously - the
  indexing operation completes just fine and the results are queryable.
  It's only the master merge that times out and is re-scheduled for a
  later period of time.

  Due to the complexity of the fix we have decided to wait till the next
  major release before triaging it. At the moment, it is advised to
  stagger index populations in order to not cause such timeout issues.
  Please let me know if you have any further questions.


Cannot access SQL Server - SQL Error 18456 SQL Server Login is failing for one user where as others can access.

Login is Windows Authenticated (Part of AD group user and admin access)

I can see login in list of xp_logininfo members for that particular group in SQL Server Security

Mixed Mode is enabled (Both SQL & Windows Login)

But still getting

  Login failed for user 'Domain\Username'. Reason: Could not find a
  login matching the name provided. [CLIENT: ]

Cannot access SQL Server - SQL Error 18456

You need to figure out what user does have rights to SQL server. Usually this is the user that you logged in with when you installed SQL Server or the user that is the default administrator on the computer.

I am assuming you have windows server 2012 and above.

I am write down some tricks to short out the Microsoft SQL Server , Error : 18456.

1. Launch Server Manager – Start – Click Server Manager . In the
    upper right corner, click Tools, then Computer Management. Expand
    Local Users and Groups (Under Computer Management-System Tools) then
    click Users

Notice under Description… There is a Built-in account for
administering… This is very likely an account that has access to SQL
server. Try logging onto windows with that account that is Built-in
account for administering then we can grant rights to the user you
want to use to login to SQL Server.

Now that you are logged into Windows with an account that has access
to connect to SQL Server, Let’s go in and grant rights to the user
that you want to be able to use to access SQL Server.

2. Launch SQL Server Management Studio again and you should be able
    to Connect. Expand your ServerName, then Expand Security, then
    Logins. Click Logins

Right-Click Logins Select New Login…

Click the Search Button

Type in the Windows User Name you would like to add as an SQL
Administrator then click Check Names (or you can click advanced and
select from a list)

Click Server Roles in the left pane; then turn on the sysadmin
checkbox, then click OK.

You can then confirm you have sa rights by double-clicking the user
name in the left Object Explorer and clicking on Securables.

Now you can log out of Windows, login as the user you just granted
rights to and all should work fine.

For your further ref : Here


Shouldn't Windows Server Failover Clustering already take care of SQLServer Failover Clustering? After reading this question, Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

If Windows Server Failover Clustering is installed, why would it not allow failover of SQL server?

What would be the effects of having Windows Server Failover Clustering, without SQL AlwaysOn FailoverClustering? Meaning in case of failover, would the second Sql server node would not operate?

Does this mean AlwaysOn Failover Clustering is an additional Installation package which needs to be installed, because Windows Server Failover Clustering did not do its job correctly?

I always inquired about this, seems like Windows Server Failover Clustering should encompass the whole VM, and not leave SQL server out of the picture.

Let me start by saying there is no "AlwaysON", it was marketing term used by Microsoft to make sure Availability groups technology gets good amount of "talk". They have succeeded and this terminology/technology is hit. It is not called "alwayson availability groups" or "always on" but simply Availability Groups..

MS has confused the terms "Failover cluster" with "AlwaysON failover cluster". In books online they both mean the same but let me point out fine difference. When you deploy SQL Server failover cluster instance (FCI) on Windows Server failover cluster (WSFC), this is called failover clustering, while when you have this but database on failover cluster instance (FCI) is participating in Availability groups (AG) then it is called AlwaysOn failover clustering. I do not prefer using alwayson failover clustering for FCI, simply because AG can be configured without FCI.

  Windows Server Failover Clustering is installed, why would it not allow failover of SQL server?

As matter of fact it is WSFC which control failover not the FCI or AG.

  What would be the effects of having Windows Server Failover Clustering, without SQL AlwaysOn FailoverClustering? Meaning in case of failover, would the second Sql server node would not operate?

Nothing, Availability groups can be configured without FCI on a simple standalone nodes which are part of WSFC. The failover will be controlled by WSFC and after failover the other node becomes primary and the old one becomes new secondary.

  Does this mean AlwaysOn Failover Clustering is an additional Installation package which needs to be installed, because Windows Server Failover Clustering did not do its job correctly?

No. Everything works because of WSFC it is the base on which clustering technology is based. FCI and AG are different things: They both are installed "upon" WSFC. Although starting from SQl Server 2017 and Windows Server 2016 you can have Cluster Less Availability groups.

  Does this mean FCI is an additional Installation package which needs to be installed, because Windows Server Failover Clustering (WSFC) did not do its job correctly?

Yes you are correct. To create FCI you need to run SQL Server installation and install SQL Server in cluster aware mode. FCI is used to provide failovers to databases which in turn leverages WSFC feature for failover. It's not SQL Server which does failover, it's WSFC SQL Server which just brings instance online on other node. So saying that WSFC does not do its job is wrong. Like I said WSFC is the base on which FCI and AG run. SQL Server FCI without WSFC cannot do failover.


Querying a date range i query this SQL query:

SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR [DATE] < '31-03-2016');

But in SQL Studio the result to another date and time.

[DATE] = VARCHAR(10) (DD-MM-YYYY)
[TIME] = VARCHAR(10) (HH:MM:SS) 24h

How to fix ?

Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure.

Priority 2: Stop using BETWEEN. This can cause all kinds of issues, especially if you fix the table and combine the date and time into a single column. The end of a range is different depending on the data type, but using >= beginning of the range and < beginning of the NEXT range will always work. Consider that the table has changed, you can say as follows (and I'm not sure what you're trying to do with your second and third where clauses, but you should just be able to look at the date column):

WHERE [DATE] >= '20160101'
  AND [DATE] <  '20160401';
-- note that I don't have to look at the TIME column at all
-- also note that I use a *SAFE* format for the string literal

Priority 3: Stop comparing dates as strings. If you can't fix the table, then your clauses need to first convert the string columns into valid date/time values, so you aren't comparing strings. And you need to use string formats that are safe in SQL Server; dd-mm-yyyy is not one of those formats - it can break (or just return the wrong data) if you change language settings, for example.

WHERE CONVERT(datetime, [DATE], 105) >= '20160101'
  AND CONVERT(datetime, [DATE], 105) <  '20160401';
-- 103 makes sure the stored strings are interpreted as d/m/y

Additional reading:

Dating Responsibly
Bad habits to kick : mis-handling date / range queries
What do BETWEEN and the devil have in common?


Are newly inserted data automatically stored in InnoDB buffer pool I understand that innoDB will store select statement result in the buffer pool, does InnoDB store newly inserted data (insert into statement) into buffer pool even after data has committed and wrote to disk?

Yes and no. Yes, buffer pool will contain the recently inserted data. No, the buffer pool doesn't contain SELECT statement output, that is what the query cache does, but it is generally harmful, deprecated and removes in MySQL 8.0. The buffer pool contains InnoDB pages (both index and data).


What is the use of ibdata1 file in mysql? I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions.

Solution 1: innodb_file_per_table
Solution 2: innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Solution 3: innodb_data_file_path=ibdata1:10M:autoextend:max:50M

And I really dont have knowledge about the ibadata1 file, can anybody help me on this and I want to know whether is it safe to fix the ibdata size(solution 3) and what does the ibdata exactly for?

In My Opinion you should use your solution1.

I am not very clear with what you will accomplish with the solution2 and solution3. with innodb_file_per_table OFF because they will never shrink the ibdata size whenever there delete,drop or truncate on an InnoDB table so using these option your ibdata will grow endlessly.

For your information have a look at this way to reduce the size of ibdata in mysql.?

without using  innodb_file_per_table, the ibdata file never compresses or shrinks, Not when you delete a row, drop a table, or a database. In no time, 3GB of data can become a 20GB file if you have an active queuing system.

Have a look why to use innodb_file_per_table : Reasons to use innodb_file_per_table.


What's the optimal way to query data using another table column as a between filter sorry for the ambiguous question title. I don't know what's it called.

The thing is let's say I've a order table storing uid , order_id and ip looks like this

| uid   | order_id |    ip
| 10000 |    1     | 2098957412
| 10001 |    2     | 2869229574
| 10000 |    3     |  825358912

and another ip table storing ip_from , ip_to and country_code

| ip_from    |   ip_to     |  country_code
| 10000      |   200000    |      GM
| 825358910  |  925358912  |      VN
| 2098957412 | 2198957412  |      FR

I'd like to do something like this

select order.uid, order.order_id, order.ip, ip.country_code
from order
left join ip
on  order.ip >= ip.ip_from
and order.ip <= ip.ip_to

to have a result like this

| uid   | order_id |    ip       | country_code
| 10000 |    1     | 2098957412  |     FR
| 10001 |    2     | 2869229574  |    null
| 10000 |    3     |  825358912  |     VN

What's the optimal query to having such a result?

What's the optimal query to having such a result?

Simple.

Create proper indices (order(ip) and ip(ip_from,ip_to)).

Complex.

Create a field of LINESTRING type from ip_from to ip_to. Create spatial index (RTree) by it. Use MBRContains for filtering.

From the other side - the ranges (ip_from,ip_to) must be NON-overlapped.

If it is true, then you'll obtain the result with simple

select order.uid,
       order.order_id,
       order.ip,
       ( SELECT ip.country_code
         FROM ip
         WHERE ip.ip_from <= order.ip
         ORDER BY ip.ip_from DESC
         LIMIT 1 ) country_code
from order

Indices ip(ip_from) and order(ip) (or even covering index ip(ip_from, country_code)) is enough for this query.


ORA-00942: table or view does not exist I have application / service which connects to Oracle database (11g) which shows me "ORA-00942: table or view does not exist" in its log file without any indication of particular  table/view with which it has problem. And there is no way on app side to increase log details to include this data.  I have direct access to the Oracle database which is using by this app (not DBA level privileges, but quite high level of access) any advice on how to check with which table or view my app having issue? Probably I can use some query to check this? I'd like to find out object name so that I can check granted access and any locks on it.

If you enable tracing for this specific error, a session trace file will be generated whenever it occurs. This will allow you to view the SQL that causes the error. You will probably need the cooperation of your DBA to do this, as it's likely only they will be able to activate the trace and access the trace dump directory.

Here's a link showing how to do this:

http://oracle.erkansaka.org/2011/09/how-to-trace-oracle-error.html

So for you that would be:

alter system set events '942 trace name errorstack level 1';


Redundant argument in printf at /usr/bin/pt-table-checksum While pt-table-checksum seems to work well for most tables, a few of the big ones get this:

  Error checksumming table mydb.mytable: Redundant argument in printf
  at /usr/bin/pt-table-checksum line 8106.

Versions:

  pt-table-checksum 2.2.11

Servers (MariaDB acting as both slave to MySQL and master to another MariaDB, happens in both cases):

  mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine
  wrapper mysql  Ver 15.1 Distrib 10.1.24-MariaDB, for Linux (x86_64)
  using readline 6.3


Delete image/blob content to reduce *.mdf file size? I have an old SQL Server database and find it unnecessary to keep old image files the users upload.

Currently the .mdf filesize is around 10 GB and database size is mainly blown up by the image files stored in each record row (I estimate 8GB are image files). If all blob/image files older than 1 year old are deleted. Is it possible to drastically reduce the .mdf filesize to 2-3 GB?

If this works, I'll schedule a script to delete all old blob/images at the end of each year, and just keep images for the past 12 months.

Yes, deleting the unused images will free up space inside your DB files.  To actually reduce the size of those files you will have to SHRINK them after the images have been deleted.  Here's a link to the MS Technet site for this process.  Shrinking the files will most likely introduce internal fragmentation into your tables and indexes.  A thorough REBUILD would be appropriate afterwards.  On a 10GB database, once a year, this shouldn't be an undue burden.


Postgres upgrade and converting latin1 to utf8 I'm pretty new to PostgreSQL, but I've been tasked with doing an upgrade. The current system runs on CentOS 5.11 and is Postgres 8.1.23 with a default encoding of latin1.  The new server is OpenSuSE 42.1 with Postgres 9.4.9 with a default encoding of utf8.

I've been trying to use the pg_dumpall command in order to get the users and their roles moved along with the databases/tables/data. But, I'm unable to restore to the new server because of the difference in encoding.

I see that the pg_dump command has the -E option to specify encoding, but it doesn't seem to give me a way to get users and roles. Would the -E option even accomplish what I'm needing -- the conversion from latin1 to utf8?

What is the best way for me to migrate/upgrade users, roles, data, etc. and at the same time convert from latin1 to utf8?

Thanks!

Here are the steps you should perform:

Dump roles and tablespaces with pg_dumpall and the --globals-only option
Dump data and schemas with pg_dump and the --encoding option to have data in utf8 for each database
Create each new database on new server with correct encoding
Restore roles and tablespaces on new server
Restore schemas and data for each new database

Here you will find documentation for pg_dump and pg_dumpall in 8.1:
https://www.postgresql.org/docs/8.1/static/app-pgdump.html
https://www.postgresql.org/docs/8.1/static/app-pg-dumpall.html

And here you will find documentation for pg_restore and psql (your restore tool depends on your dump options) in 9.4:
https://www.postgresql.org/docs/9.4/static/app-pgrestore.html
https://www.postgresql.org/docs/9.4/static/app-psql.html

And here is generic documentation about databases encoding in PostgreSQL:
https://www.postgresql.org/docs/9.4/static/multibyte.html

Have a nice day!

Arkhena


ORDER BY items must appear in the select list [...] Using Microsoft SQL Server 2008, I get the following error.

  Msg 104, Level 16, State 1, Line 43
   ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The query is I am using is kind of complex, but the CASE statement in side of the ORDER BY clause can not see the aliased column name, here is a brief example.

SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
ORDER BY CASE WHEN foo = 2 THEN 1 END;

In my production query the left-query needs to be ordered by the column [360_set] found in the table, and the right-query needs to be ordered as if [360_set] was null.

How do I fix this error, and why does this syntax generate an error?

Here is the version info,

Microsoft SQL Server Management Studio     10.0.5512.0
Microsoft Analysis Services Client Tools   10.0.5500.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 6.0
Microsoft Internet Explorer                9.10.9200.16635
Microsoft .NET Framework                   2.0.50727.5472
Operating System                           6.1.7601

If an alias is used in an ORDER BY it must be used on its own, not inside an expression.

If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.

So for example

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B

Returns (ordered by alias)

+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+

But

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B + 0

Returns (Ordered by base table column B)

+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+

You can of course just wrap the whole thing in a derived table or CTE.

WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;


Why does MariaDB still use the "mysql" name everywhere within the file system? I understand that MariaDB forked a version of MySQl but is is very confusing to still deal my.cnf, /usr/share/mysql , /usr/lib64/mysql and so on? Are there any reasons, why they did not change the naming on most of those folders? This had been very confusing to me and that is the reason why i have not migrated to it yet. It's either you are MariaDB or MySQL but don't try to be both.

MariaDB is a forked version on MySQL that attempts to be a drop-in replacement.  A good drop-in replacement should work without requiring a lot of configuration and data file movement.  By using the same directories and filenames, it is far simpler to install as a replacement for MySQL.

I haven't tried MariaDB, but I would expect to be able to install it test it, and decide if I want to revert back to MySQL.  While I would backup my databases, I don't expect to have to restore my data whichever option I pick.


Privileges on SQL Server I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?

You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.

From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.

From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...

Two ways with which you can possibly handle this:

Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.

Deny the VIEW ANY DATABASE server permission for the logins in question. They will now only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.


How should I deploy these SSRS reports? I need to make a decision about deployment and security of some SSRS reports and would like some advice on what the most appropriate method is.

We have 40+ sites that have access to information for only their site.  Each site has access to a number of reports, say Report A, Report B, etc.  For each site, the report name is Report A - Site 1, Report B - Site 1, etc.  For each report (Report A), there is a Master report (Report A - Master).  The Master report is the report itself - dataset and formatting - and includes a hidden parameter for site name.  Each sites' report links to this Master report and passes in the site name.  Thus if the report changes, the change is made in one place.

Please note that limiting the reports to site-specific information on the database is not possible.  Our data source uses a service account and all users access to the information is handled in the Web Portal.

Now it is time to grant users access to these reports.  Currently it is set up that all of the reports and the master reports are in a single folder.  Access is controlled on each report individually and users must have access to the Master report as well as their site reports.

I would strongly prefer to set up a folder for each site and control access that way.  Obviously that would make it easier as site-specific reports are added, and it's superusers who will be controlling access.  My concern is this will make deployment of reports incredibly difficult.  Because of the linked mechanism, all 40+ versions (plus Master) are in the same BIDS project - and it seems like a nightmare to deploy to 40 different locations for every change.

I would like to know if anyone else has had a similar challenge and found a good way to solve it.  I have played around with linked reports, but then the site report can't "find" the Master report, even if a linked copy is placed in the same folder.

EDIT: To clarify, and use the exact terminology, the Master report is a sub-report of each site report.

Well... I figured it out!

Here's what I did:

Deploy all the reports from the BIDS project to a main folder, say Source Reports.
On the ReportServer, create site-specific folders in another folder, say Site-Specific Reports.  Also in that folder, create a Master Reports folder.
Copy the Master reports to the Master Reports folder.
For each site report, create a linked report in their site-specific folder under Site-Specific Reports.

Now here's the "tricky" part... if you edit the report in the Source Reports folder in Report Builder, you can set up the sub-report to point to a specific folder structure on the ReportServer.  This enables you to use sub-reports anywhere on your ReportServer, not just in the same local directory.  My assumption is you can also type in the full path of the deployed report you wish to use as a sub-report into BIDS, but with Report Builder you can browse and click.

So, in this case, I have made all of the site-specific reports point to the Master report in the Master Reports folder.

This is a great solution for us.  Now we can grant user access at the folder level without creating excessive burden on the report deployment process.


Is there a way to optimize the process of uninstalling and installing SSMS? in terms of patching sql server 2016 to the latest cumulative patches, I found out that stopping the services helps avoiding lots of inconveniences, specially if there is log shipping and replication involved:

However,
when upgrading to the newest SSMS, stopping the services does not seem to make the uninstall of the old version go quicker, it is indeed a very slow process.

same slow is the installation.

even having the relevant services stopped for this operation:

Plus a reboot at the end of the process.
Is there anything that can be done, in order to automate or optimise the process of installing the new SSMS (currently 18.5.1)?

All of the relatively recent versions of SSMS install side-by-side with each other, so I wouldn't bother removing them honestly. They'll upgrade the current version if it is on the same major release.
The newer versions are being released faster and sometimes can be a bit buggy, so having an older version to fall back on can also be helpful if you encounter a bug.
As far as making it faster, you could try using a package manager like winget (in preview, but by Microsoft) or chocolatey or any other generic automation software (Ansible, etc.)
There is also the command line option of SSMS itself, as Kin mentioned. And I will also echo the warning to not be doing this regularly (or at all) on the actual SQL Server machine.


MongoDB secondary node does not become primary when other nodes are stopped I want to cluster MongoDB by using a replica set. I have created a three-node replicaset (rs0,rs1,rs2), and run the DBs in parallel with no issues. But when I shut down any two instance (one primary and secondary), the remaining secondary member is not made the primary, instead it remains as a secondary.

Step1:

rs0: primary
rs1: secondary
rs2: secondary

Every thing is ok. When

Step2:

rs0: stopped
rs1: stopped
rs2: secondary  (This is not promoted to primary)

Why?

This is by design. If you want to run with only one node, restart that node without replica set switch.

  If a majority of the replica set is inaccessible or unavailable to the
  current primary, the primary will step down and become a secondary.
  The replica set cannot accept writes after this occurs, but remaining
  members can continue to serve read queries if such queries are
  configured to run on secondaries.

Read these resources for more details:

Replica Set Elections
mongo replica set to single server
Voting in MongoDB


Index maintenance for AG DBs I wanted to get some opinions on the index maintenance we currently do at the company where I work. One of our production SQL 2012 clusters that consists of 4 nodes, with two instances on each, has multiple AGs which service some very heavy workloads. Some of the databases in these AGs are 2TB+.
We have a standard daily index maintenance routine that does the usual rebuild vs reorg depending on the fragmentation level, but we also only do reorgs in indexes that are over a certain size because we have seen issues with SYNC latency if these larger indexes were to be rebuilt. Once index maintenance has been performed we then update statistics etc.
This job can sometimes run up to 12 hours+ so it impacts on our critical business hours where we see peak traffic, so we really need to do something to alleviate this.
I've seen quite a bit of commentary recently where it has been suggested that index maintenance isn't always required at all, and I suspect that may be the case for us where we are doing a reorg on large indexes that are only 5% fragmented.
I guess I'd like some ideas on how I could identify indexes where the level of maintenance we do daily isn't necessarily required, other than disabling it and observing the impact, if any.

You're not alone on seeing sync latency with AGs when doing index rebuilds.  From Sean Gallardy's blog post SQL Server Index Maintenance – You’re Doing It Wrong (emphasis mine):

In the worst of the cases, which does seem to happen quite often, it can bring the entire server to its knees, causing long blocking waits, filling up the log, causing remote AG replicas to fall behind, among a large list of other items. This does have real and painful consequences.

Note that an important caveat mentioned in the comments of that post is that fragmentation can affect the performance of SQL Server's readahead mechanism, which is used heavily during large table scans (ETL loads, big reporting queries).
I honestly like your suggestion of disabling maintenance and seeing what happens.  You could disable it on just the very large tables, and see if there is any negative impact vs the gains you get in terms of shorter maintenance windows.
To comment on this specific statement in your question:

Once index maintenance has been performed we then update statistics etc.

FYI, rebuilding an index will result in statistics being updated for that index with FULLSCAN.  So make sure you're not updating stats twice on these tables that are getting rebuilt.
You should also take a look at Erik Darling's blog post Because Your Index Maintenance Script Is Measuring The Wrong Thing.  You could check avg_page_space_used_in_percent to determine if there are indexes where you can reclaim a lot of space (on disk and in memory) by rebuilding.  This would probably be the biggest "win" from rebuilds.
On a related note, another of Erik's posts (What Metrics Does Rebuilding Indexes Improve?) points out some specific situations that might warrant an ad-hoc rebuild:

It’s my opinion, and you can take it or leave it, that index rebuilds
should be reserved for special circumstances.

You deleted a lot of data
You need to change something about the index
You have a Heap with a lot of forwarded fetches

Unfortunately, there isn't an easy button here.  Hopefully the links help inform your decision process though.


Is there a system primary key associated with each row? Does a system primary key or unique identifier for each row in a table exist? Like @@ROWID, @@IDENTIFER, or some other identifier that uniquely identifies each row regardless of how it was created? Using SQL Server 2000 through SQL Server 2012.

In practical terms from a code or user perspective, no.

There is one internally (rid) but it is not exposed in any documented system view or function


MySQL read explain - using where In Oracle there is an important note in the explain plain where it says filter a.col1=... this one letting you know col1 is first retrieved from the disk and only then filter is been done. This important piece of information let you know the exact column been used within a specific index and which are filter after and may be good candidate for index.
In MySQL explain result we see something like:
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type   | key                | key_len | ref   | rows   | filtered | Extra                              |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
|  1 | SIMPLE      | SD    | NULL       | range  | ix_store_composite | 64      | NULL  | 252978 |    60.00 | Using index condition; Using where |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+

What I would like to know which of the columns data are coming from the index and which are been filtered after (the using where).
I couldn't find any option to get additional detail on the explain

"Using index condition" does not mean the same as "Using index".
Run this:
EXPLAIN FORMAT=JSON SELECT ...

If you need further help, post that in your Question.  Also show us the SELECT; it will help us go into more detail.


query output shows ##,###,### instead of right output I am working on one project in which, query output gives ##,###,### instead of right values.
according to requirement we calculate the time period and display it in separate table.
this is query I am using as calculation
select (to_char((coalesce(11808000,0) - (coalesce(3562.66,0) - (coalesce(1800,0) + (coalesce(1800,0))))),'FM99,999,999'))::character varying as mytest

so if I run this one, it will give output properly, but the seperate table what we use to display output is giving wrong values. can any one help me with this ??

If the output looks like ##,###,###, the number is too big for the format.
Use a longer format string like
FM99,999,999,999


Order of Execution of Set clause in Update Query (Postgres) I recently came across this strange behaviour in Postgres. I had a table like the following one:

sasdb=# \d emp_manager_rel
           Table "db3004db.emp_manager_rel"
   Column   |  Type  | Collation | Nullable | Default
------------+--------+-----------+----------+---------
 emp_id     | bigint |           |          |
 manager_id | bigint |           |          |

select * from emp_manager_rel ;
 emp_id | manager_id
--------+------------
      1 |        123

I executed the following update statement :

UPDATE 1:

update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;

which update the table like follow:

 emp_id | manager_id
--------+------------
      4 |        124

UPDATE 2:
I executed the following query (on the original table, not on the updated)

update emp_manager_rel set  emp_id=emp_id*4 , manager_id = manager_id+emp_id ;

it updates the table like follows:

 emp_id | manager_id
--------+------------
      4 |        124

I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.

There is nothing "strange" about this behaviour - it's the only sane way to process such an update.

There is no such thing as "order of updates" - all SET clauses should be assumed to happen in parallel.

The SQL standard requires that the right hand side of the assignment is the value of the columns as it was before the UPDATE statement started. The order in which the column assignments are listed in the UPDATE statement is irrelevant for the outcome of the UPDATE statement.

Any other behaviour of the UPDATE statement would be a bug and a violation of the SQL standard.

For the same reason, the following statement will swap the two column values:

update some_table
   set x = y, y = x;

or

update some_table
   set y = x, x = y;


Migrate SQL Server to Oracle 11G Offline (OMWB) So, I'm having some difficult to migrate SQL Server to Oracle. This time there appears a new error message:

Migration actions have failed check the migration reports for details. :
Capture
Offline Capture
ORA-00942: table or view do not exist.

I executed the OMWB_OFFLINE_CAPTURE.bat, I passed the parameter to batch and generated the directory with database related.

After, I executed "Migrate To Oracle" I caught the sqlserver2008.ocp file, I finalized, and that error mentioned above appeared. And it does not show the table or view nonexistent.

How do I fix it?

The resolution is:

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SESSION TO YOUR_USER

Or grant DBA Privilege
Before you granted the privileges, delete the repository associate and create again with new privileges or create the repository before you grant the privilege.

This way resolve the problem.


Prevent UPDATE TRIGGER to fire when no changes made in SYBASE In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), the trigger still gets fired and logs in audit table, which I don't want (to prevent duplication when no changes).

test has

Id | NAME | DESC

test_a has

updated_by | date | Id | NAME | DESC

This is the update trigger I have:

create trigger test_utrig on test for update as
insert into audit..test_a select 'update',update_by(),getdate() inserted.* from inserted

I tried to use if (update(Id) or update(NAME) or update(DESC)), but still the update trigger get fired on no changes. Please help me how to stop trigger from firing when no changes are done.

I don't know SyBase but from the documentation, it appears as if you can check whether one or more columns are updated. It should be sufficient to check if one is updated like:

create trigger test_utrig on test
for update as
    if update (id)
        insert into audit..test_a (updated_by, date, Id, ,NAME, DESC)
        select update_by(), getdate(), inserted.Id, inserted.NAME, inserted.DESC
        from inserted

It appears to be a mismatch between the number of columns in the trigger and in the audit table, so I removed the constant 'update'.