Can a table belong to multiple tablespaces? Can a table belong to multiple tablespaces?

A segment can only belong to one tablespace. So an ordinary table (plain heap table with no LOBs, varrays, nested tables or partitions) can only live in one tablespace.

Partitioned tables (or indexes, materialized views, ...) can have different partitions in different tablespaces though (with at least one restriction: all the tablespaces must have the same block size).

For tables with LOBs, LOBs storage can be out-of-line, in which case you can specify a different tablespace for each persistent LOB column. (Varrays can be stored as LOBs depending on size, so same applies for them.)

Index organized tables can have an overflow segment. When that's the case, the overflow segment can also be put on a different tablespace than the "main" part of the IOT.

Nested tables are also a special case (whether they're in heap tables or IOTs), and the main and nested parts don't have to live in the same tablespace.

There might be other specially handled types, but the main idea is: one segment lives in one tablespace. If you have an object with multiple segments (the most usual being partitions and LOB storage or overflow segments), then those segments need not all be in the same tablespace (with some limitations).


Extremely slow mySql restores Please bear with me as I ask this question, which looks similar to questions already on DBA@SE.

In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database and Application Server are hosted on this server.
There are two MySql databases - Prod and Staging on the same server. Some of the tables are on MyIasm engine while some on InnoDB.

root     10119     1  0  1233  1012   3 Mar03 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe ...
mysql    10233 10119  9 238047 108896 0 Mar03 ?        12:11:11 /usr/local/mysql/libexec/mysqld ...

Every day a dump of the Prod db is taken and restored in the Staging db. The total size is close to 1 GB and a few tables have more than million rows.

Taking the dump via mySqlDump is fast and takes less than a minute (complete dump - not just deltas from binary logs). Optimizations like turning off auto_commit, foreign_key_checks etc. have been added to the dump.
From a couple of days, the restoration part (to the Staging DB) has become extremely terrible as it takes ~1 hr and paralyzes the application. (The same restore on my dev box takes less than 4 minutes - configurations are different and noone's accessing it)

key_buffer_size is 512Mb [~12.5 % of the total RAM]. table_cache=256 is suboptimal (but there aren't too many requests during that time). read_buffer_size=1M. sort_buffer_size=64M
In the restore script, before inserting data each table is locked, data is inserted and then the table is unlocked.
Slow Query logs are enabled (and most of the queries from the dump find a place there) - Could high I/O be affecting the performance?

I am trying to figure out the root cause so that it may be mitigated. As I have limited access to the server, I am trying to gather possible questions so that I take them to the hosting providers. Could you please provide me with pointers that I should be looking at?

CPU utilization and I/O peak during this time.

Thank you.

[Edit]
- After increasing innodb_buffer_pool_size, the restore time has reduced to an extent.

This is the sample dump, that gets generated:
SET FOREIGN_KEY_CHECKS=0;
SET unique_checks=0;
SET AUTOCOMMIT=0;

// The scripts go here:
DROP TABLE IF EXISTS `access_tokens`;
CREATE TABLE `access_tokens` ( ... );
LOCK TABLES `access_tokens` WRITE;
INSERT INTO `access_tokens` VALUES (...) // All of the data is in a single insert
UNLOCK TABLES;

...
// Script for other tables

SET FOREIGN_KEY_CHECKS=1;
SET unique_checks=1;
COMMIT;
SET AUTOCOMMIT=1;

You have not included your server config so I am writing all I could think of.

You have already innodb_buffer_pool_size so skipping that.

On Staging Server use following

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

On mysqldump use following options

--disable-keys
--extended-insert
--add-locks

You should use change log size this way.


Query: select all rooms which are not booked I have a simple room booking system consisting of following tables:

booking
| id | user | begin | end |

room
| id | name |

booking_map
| id | room_id |

I can list all rooms that are booked at the moment with the following query:

SELECT booking.id,booking.user,booking.begin,booking.end,room.name
FROM booking,booking_map,room WHERE ((booking.id = booking_map.id)
AND (room.id = booking_map.room_id) AND (begin < now()) AND (end > now()));

But how would I list all rooms that free?

Let's look at some example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE booking
    (`id` int, `user` varchar(2), `begin` datetime, `end` datetime)
;

INSERT INTO booking
    (`id`, `user`, `begin`, `end`)
VALUES
    (1, 'u1', '2013-02-03 00:00:00', '2013-02-04 00:00:00'),
    (2, 'u2', '2013-02-03 00:00:00', '2013-02-06 00:00:00'),
    (3, 'u3', '2013-02-05 00:00:00', '2013-02-07 00:00:00'),
    (4, 'u4', '2013-02-10 00:00:00', '2013-02-11 00:00:00')
;

CREATE TABLE room
    (`id` int, `name` varchar(2))
;

INSERT INTO room
    (`id`, `name`)
VALUES
    (1, 'r1'),
    (2, 'r2'),
    (3, 'r3'),
    (4, 'r4'),
    (5, 'r5'),
    (6, 'r6')
;

CREATE TABLE booking_map
    (`id` int, `room_id` int)
;

INSERT INTO booking_map
    (`id`, `room_id`)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 1)
;

First we need to find all bookings for the day:

Query 1:

SELECT *
  FROM booking
 WHERE begin <= '2013-02-04'
   AND end >=   '2013-02-04'

[Results][2]:

| ID | USER |                           BEGIN |                             END |
|----|------|---------------------------------|---------------------------------|
|  1 |   u1 | February, 03 2013 00:00:00+0000 | February, 04 2013 00:00:00+0000 |
|  2 |   u2 | February, 03 2013 00:00:00+0000 | February, 06 2013 00:00:00+0000 |

Or really, we need a list of all booked rooms for the day. For that we just join booking to booking_map:

Query 2:

SELECT m.room_id
  FROM booking AS b
  JOIN booking_map AS m
    ON b.id = m.id
 WHERE b.begin <= '2013-02-04'
   AND b.end >=   '2013-02-04'

[Results][3]:

| ROOM_ID |
|---------|
|       1 |
|       2 |

Now, with that information we can just return all rooms that are not in the list of booked rooms:

Query 3:

SELECT *
  FROM room AS r
 WHERE r.id NOT IN(
    SELECT m.room_id
      FROM booking AS b
      JOIN booking_map AS m
        ON b.id = m.id
     WHERE b.begin <= '2013-02-04'
       AND b.end >=   '2013-02-04'
   )

[Results][4]:

| ID | NAME |
|----|------|
|  3 |   r3 |
|  4 |   r4 |
|  5 |   r5 |
|  6 |   r6 |


Possible to do INSERT with some columns from SELECT query and some columns with specific values? Is it possible to do an one INSERT statement where some values are from a SELECT query and some from specific values?
Trying to do something like this where cust_id, rep_id are from a select query and sku_ordered is a specified value:
    INSERT into ORDERS (cust_id, rep_id, sku_ordered='ABC123')
     SELECT id,
            sales_rep
     from CUSTOMERS
     where cust_name = 'ABC Corp';

Literal must be a column in output list simply:
INSERT into ORDERS (cust_id,   rep_id , sku_ordered)
SELECT                id   , sales_rep,   'ABC123'
from CUSTOMERS
where cust_name = 'ABC Corp';


Insufficient Prilivege for Function I'm using a Function that I can't read, when I try to open it, I can't see the definition.
I get following error

  Error : Insufficient privilege.

Nevertheless, it is being executed by my app without any issue, but I would like to export my app to another server and I can't export this function.

How should I do to be able to read it ?

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature.

Definer and invoker security contexts differ as follows:

A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.

A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

https://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html


Oracle flashback query syntax - all tables to same timestamp I'm not finding a lot of examples online of how to use Oracle Flashback Query and I'm hoping somebody can clarify syntax. I'm trying to execute a query with a large number of tables in it, but the syntax seems to require me to specify a timestamp for every single table involved in the query.

For example, the following query gets me consistent data, but it requires me to specify a timestamp for each table:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 as of timestamp to_timestamp(2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t2
    on t1.somekey = t2.somekey

The following example (which is how most examples online look) gives past data for table1, but joined to current data from table2 - they don't line up:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 t2
    on t1.somekey = t2.somekey

Since I have a large number of tables, I'm hoping for a way I can execute the entire query against a flashback timestamp - something like this:

select (as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss'))
       t1.Field1, t2.Field2
  from table1 t1
  join table2 t2
    on t1.somekey = t2.somekey

Anybody know if this is possible, or if the current syntax doesn't allow it? I'm using 11.2 if it matters, but nothing I see online suggests that this has changed in 12.

Look at the DBMS_FLASHBACK package and the ENABLE_AT_TIME procedure (12.1 Documentation).  This allows you to do something like the following:

execute DBMS_FLASHBACK.Enable_At_Time(TIMESTAMP '2016-11-01 02:00:00');

The session will then run all queries as of the SCN most closely matching the provided timestamp.

SELECT t1.Field1, t2.Field2
FROM table1 t1
JOIN table2 t2 ON t1.somekey = t2.somekey

To stop doing flashback queries as of the set timestamp, simply disable it.

execute DBMS_FLASHBACK.Disable;


Connecting to MySQL VM remotely (Azure Resource Manager) I have a VM setup in Azure (running Windows Server 2012 R2)  with MySQL 5.7 installed. I am trying to access this using MySQL Workbench - which when running on the server works perfectly.

However, trying to use Workbench from my own PC is giving me there error "10060":

I have also tried doing this using a PHP file in an Azure web app to connect, again not working.

I have set the following:

Azure Security Group, inbound port 3306 is open

On the server the following inbound port rules have been applied:

User is set to stackexchange@% (i.e. not localhost)

Is there something I have missed? Do I need to allow the database to have remote connections?

Apologises if this is the wrong site. Wasn't sure if it was server related or related to the DB itself.

There is this tutorial by Microsoft but it uses the classic deployment model - not Resource Manager. And questions like this on here don't cover MySQL specifically.

After a bit of head banging I have found that changing the bind-address on mysql to the ip address of the node connecting to mysql makes mysql refuse to restart

So instead set the bind-address = 0.0.0.0

Then let the firewall and mysql handle allowing the hsot.

Create a firewall rule on the mysql node like below:

Then create a remote mysql user limiting the host to the internal/private ip address above.


How to get last messge between two friends in the messge table having id in descending order? Hi friends help me to solve this problem ," display all last messages of a user from all his/her friends"

My sample message table is

---------------------------------------------------
|  id | msg       | sender |  reciver | friend_id |
---------------------------------------------------
|  1  | Hi        | 1      | 2        | 1         |
|  2  | Hi..      | 2      | 1        | 1         |
|  3  | Hello     | 6      | 1        | 3         |
|  4  | What dong | 1      | 6        | 3         |
---------------------------------------------------

I need only the last message b/w the user and his friends (here consider user id is 1)

I need the result like below , please help me to achive this with mysql

---------------------------------------------------
|  id | msg       | sender |  reciver | friend_id |
---------------------------------------------------
|  4  | What dong | 1      | 6        | 3         |
|  2  | Hi..      | 2      | 1        | 1         |
---------------------------------------------------

The name of this table is message..

select a.id, a.msg, a.sender, a.receiver, a.friend_id
from msg a
left join msg b on (a.friend_id = b.friend_id and a.id < b.id)
where b.id is null
  and (a.sender = 1 or a.receiver = 1)
order by a.id desc;

Can be tested at http://sqlfiddle.com/#!9/35823/10

It uses "groupwise-max" solution with a left join as can be found for instance in mysql manual (last example).

Your schema seems a bit weird keeping sender, receiver and friend_id, but I suppose friend_id is a FK to some friends table where pairs of (1,2) and (1,6) are stored, so it is more of a "friendship_id" ?


SSIS export to CSV Flat File Destination task, error The code page on input column is ... and is required to be I get this error on a File Destination Task,   do you know what the cause is and how to fix it ?

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at export data to csv [Flat File Destination [187]]: The code page on input column "..." (958) is 1252 and is required to be 65001.

ta

The issue is caused by a clash in code pages between 1252 (i.e. Windows-1252) in SQL Server and 65001 (i.e. UTF-8) that your CSV file is expecting.

I believe one solution to the issue would be to CAST your source columns to NVARCHAR in the initial SELECT from the database, or use a Data Conversion task to convert them to Unicode strings.

An alternative would be set the code page in the Flat File Connection Manager to 1252 ANSI - Latin.


MongoDB gives certificate not trusted while openSSL says keys is OK I have used let's encrypt to generate SSL certification. All the private key, chain and certificate is generated by `let's encrypt.

Now to use it in MongoDB I first merged privkey.pem and cert.pem into a file called mongo.pem:

cat /etc/dehydrated/certs/mongo.example.com/privkey.pem /etc/dehydrated/certs/mongo.example.com/cert.pem > /etc/ssl/mongo.pem

Now using openssl I converted ca.crt into ca.pem:

sudo touch /etc/ssl/ca.pem
sudo chmod 777 /etc/ssl/ca.pem
sudo truncate -s 0 /etc/ssl/ca.pem
sudo openssl x509 -in /etc/ssl/ca.crt -out /etc/ssl/ca.pem -outform PEM

Now a file is created called ca.pem in /etc/ssl path. Finally I append chain file content to the end of ca.pem:

sudo cat /etc/dehydrated/certs/mongo.example.com/chain.pem >> /etc/ssl/ca.pem

Now to verify the key I used openssl is below:

openssl verify -verbose -CAfile /etc/ssl/ca.pem /etc/ssl/mongo.pem

The output is:

/etc/ssl/mongo.pem: OK

It's good to note that I have done the same exact procedure on another server and it went all ok and MongoDB works as expected.

Now my MongoDB config is as below:

ssl:
    mode: preferSSL
    PEMKeyFile: /etc/ssl/mongo.pem
    CAFile: /etc/ssl/ca.pem

NOTE: My mongoDB is a single primary node in which replication is enabled (Use to capture changes on DB)

Now when I restart MongoDB it gives error below:

A member of a replica set enters RECOVERING state when it is not ready to accept reads. The RECOVERING state can occur during normal operation, and doesn’t necessarily reflect an error condition. Members in the RECOVERING state are eligible to vote in elections, but are not eligible to enter the PRIMARY state.

2018-05-15T18:26:00.171+0000 E NETWORK  [conn1] SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.171+0000 W NETWORK  [replexec-0] couldn't check isSelf (mongo:27017) socket exception [CONNECT_ERROR] for SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.171+0000 I NETWORK  [conn1] Error receiving request from client: SSLHandshakeFailed: SSLHandshakeFailed. Ending connection from 127.0.0.1:47454 (connection id: 1)
2018-05-15T18:26:00.171+0000 I NETWORK  [conn1] end connection 127.0.0.1:47454 (0 connections now open)
2018-05-15T18:26:00.171+0000 E NETWORK  [replexec-0] SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.172+0000 I REPL     [replexec-0] This node is not a member of the config
2018-05-15T18:26:00.172+0000 I REPL     [replexec-0] transition to REMOVED from STARTUP

Modules cannot connect to MongoDB and reports:

OperationFailure: node is not in primary or recovering state

Now I do not have access to primary node and it cannot show my dbs. When I disable SSL, everything works and node gets back to primary state. Why is this happening, while I have done the exact same thing on a node in another datacenter?

I changed hostname from mongo to mongo.my_domain.com. Moreover SSL keys are generated for mongo.my_domain.com. Mongo is now up and running with SSL.


Optimization of multiple CASE Statements I have a rather long set of SQL which relies on CASE statements that gives me a percentage of the population of a particular zip code that fits a particular parameter. In the code below, it would give me the percentage of households with an elderly parent.

Now, the below code is actually created by a python script that generates it based on the list of selected factors and the possible values. In the case of one field there are 35 possible values which generates 35 different CASE statements. In the example below, homeowner status has three values which break out to three CASE statements but in fact it has 8 potential values...

The code runs. However, it takes a month of Sundays and I'm wondering if there is some way of optimizing it. My somewhat unhelpful SQL professor used to say-- "just let the query optimizer deal with it -- lots of smart people worked on that so you don't have to worry" ...

SELECT
  DD_1409.ZIP_CODE,
  count(*) RECORDS,
  (SUM((CASE WHEN GENDER = 'F'
    then 1
    else 0 end)) / count(*)) * 100 GENDER_F,
  (SUM((CASE WHEN PRESENCE_OF_ELDERLY_PARENT = 'Y'
    then 1
    else 0 end)) / count(*)) * 100 PRESENCE_ELDERLY_PARENT_Y,
  MEDIAN(LENGTH_OF_RESIDENCE),
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'P'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_P,
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'R'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_R,
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'U'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_U,
  (SUM((CASE WHEN MOPS.KBMG_INDEX_GEN2.GEN2 in ('T1','T2','T3','T4','T5','T6','T7')
    then 1
    else 0 end)) / count(*)) * 100 GEN2_T

FROM MOPS.DD_1409
 LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.IID
 WHERE STATE = 'CA'
 AND ONE_PER_ADDRESS ='Y'
GROUP BY DD_1409.ZIP_CODE;

I've tagged both Oracle and postgresql as it currently resides in Oracle but may be migrated to a postgresql instance in the next month.

In Postgresql, you can use the FILTER clause as follows (untested):

SELECT
  DD_1409.ZIP_CODE,
  count(*) RECORDS,
  count(*) filter (where GENDER = 'F') / count(*) * 100 GENDER_F,
  count(*) filter (where PRESENCE_OF_ELDERLY_PARENT = 'Y') / count(*) * 100 PRESENCE_ELDERLY_PARENT_Y,
  MEDIAN(LENGTH_OF_RESIDENCE),
  count(*) filter (where HOMEOWNER_STATUS = 'P') / count(*) * 100 HOMEOWNER_STATUS_P,
  count(*) filter (where HOMEOWNER_STATUS = 'R') / count(*) * 100 HOMEOWNER_STATUS_R,
  count(*) filter (where HOMEOWNER_STATUS = 'U') / count(*) * 100 HOMEOWNER_STATUS_U,
  count(*) filter (where MOPS.KBMG_INDEX_GEN2.GEN2 in ('T1','T2','T3','T4','T5','T6','T7')) / count(*) * 100 GEN2_T
FROM MOPS.DD_1409
 LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.IID
 WHERE STATE = 'CA'
 AND ONE_PER_ADDRESS ='Y'
GROUP BY DD_1409.ZIP_CODE;

But as others have mentioned, optimising expressions isn't going to help you; the Postgresql syntax is merely cleaner.

You'll need to show us your execution plan(s) and table definitions for us to be able to help you further.


Microsoft Column Store Database? Using Microsoft technologies/products is it possible to create a column store database with column family sets, where each row being able to have a different number and type of columns (a different column family).

It sounds like what you're looking for is a "non-relational database," often called a document or NoSQL database.  Amazon defines that here:

What is a document database

  A document database is a type of nonrelational database that is designed to store semistructured data as documents.

Microsoft has a cloud based product called Azure Cosmos DB, which you can read about here:

Introduction to Azure Cosmos DB

  Keeping database schema and indexes in-sync with an application’s schema is especially painful for globally distributed apps. However, with Cosmos DB, you do not need to deal with schemas or indexes. The database engine is fully schema-agnostic.

Microsoft doesn't offer an on-premises version of this product.

SQL Server also has json and XML data types. You could serialize objects with this loosely structured format and store it in a column of this type, but the preferred approach would be to use Cosmos DB or another document database like mongodb or ravendb.


Audit Plug-in in MYSQL 5.7.5 Following error occured while using installing MariaDB Audit Plugin Library (i.e. server_audit-1.1.8.tar.gz) in 5.7.5-m15 MySQL Community Server (GPL) in Centos release 6.5:

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/server_audit.so' (errno: 13 /usr/lib64/mysql/plugin/server_audit.so: undefined symbol: _my_thread_var)

Based on the MariaDB documenentation claims that plugin should workin MariaDB or MySQL Server with version 5.5 or newer (MySQL 5.5.14, MariaDB 5.5.20).

I also attempt to use same plugin in MYSQL Server version: 5.6.22 MySQL Community Server (GPL), it works perfectly.

Anyone experienced and resolved above issue?

errno: 13 should be enough of a clue here. 13 is generally permission denied. Check the .so file in your plugindir. It will likely have privs that will prevent the user running MySQL from opening it.


Deadlock when delete using OR operator I got a deadlock when having more than one sessions doing the following:

start transaction
insert data to table A
delete data from table A
commit

at the same time

They may delete the same records or different

I could replicate it with a simplified code like below, the table is empty at the start

Session 1:

BEGIN TRANSACTION
DECLARE @component_id  numeric = 1
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

Session 2

BEGIN TRANSACTION
DECLARE @component_id  numeric = 2
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

The primary key is (area_id, component_id, generic_id)

The foreign key is (operator_id)

There are 2 indexes on component_id and operator_id (nonclustered)

Deadlock graph

Actual execution plan of the delete query from session that was not chosen as the deadlock victim
https://www.brentozar.com/pastetheplan/?id=ryyPArbi7

The deadlock does not happen if I remove the OR operator and change it to 2 delete queries

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

Actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sy9ACrbsm

Can anyone please explain to me why the difference?

The issue is not about how many delete operations there are, it’s about how it finds the rows that are to be deleted.

If you look at the query plans you’ll almost certainly let see that your delete with OR is scanning the whole table. This will take out locks to make sure the rows don’t move around while it’s looking for ones to delete. Meanwhile, your inserts will take out locks, as the table and other indexes are maintained. Without the OR, you’re getting Seek operations, and only small parts of the table are locked.


Where are partitions defined in mysql I have a large table that I want to spread across multiple disks/LUNs.  It will contain blobs and I want to have it separated out from the other data.

How do I go from new disks (lets call them sdb1 and sdc1, the OS, etc. is on sdba1) attached to a (virtual) Linux database server to putting the parts of the database, really one table spread across a couple dedicated devices.

Have looked at the Partition information but its unclear how I change the location of the partitions so everything just doesn't end up in the same data directory.

There is a CREATE TABLE syntax where you can declare the data folder and index folder under the partition_definition of a partition_option:

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir'] <<<<<---------
        [INDEX DIRECTORY [=] 'index_dir'] <<<<<---------
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

CAVEATS

I know it works for MyISAM because I wrote about it in September 2011 : Dynamically changeable database. For an already existing MyISAM table, it can done from the OS by moving the .MYD and .MYI to another disk and setting up two symlinks. I wrote about that in May 2012 : Can I have one table be an "alias", or "symlink", to another?. When done correctly, SHOW CREATE TABLE will display DATA DIRECTORY and INDEX DIRECTORY and their respective folders.
I personally implemented this in production for a client when I did the following

Converted mysql.general_log from CSV to MyISAM
Moved /var/lib/mysql/mysql/general_log.MYD to a large 3TB disk
Moved /var/lib/mysql/mysql/general_log.MYI to a large 3TB disk
Setup /var/lib/mysql/mysql/general_log.MYD as a symlink to the real .MYD
Setup /var/lib/mysql/mysql/general_log.MYI as a symlink to the real .MYI
enabled the general log
Watched the client's general log growth to 1.1TB
I wrote up how to set this up back in June 2011 : Mail alert from MySQL when databases are edited (under Option 3 : You Could Monitor the general log)

Using hard links, it can be done to MyISAM tables in Windows, although the effort to do so may not be worth it. I wrote about that back in January 2012 : Can I specify a different disk for a specific MySQL table?
I do not believe [DATA DIRECTORY [=] 'data_dir'] and [INDEX DIRECTORY [=] 'index_dir'] were implemented for InnoDB. Even if the it were implemented for InnoDB, I would adamently advised against it and I wrote about why you should not back in August 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?

Your Original Question

MySQL 5.5 Only : With regard to your question in the heading, Where are partitions defined in mysql, you can run this query to locate all tables that have partitions:

select count(1) partition_count,table_schema,table_name
from information_schema.partitions
group by table_schema,table_name
having count(1) > 1;

Here is a sample:

mysql> select count(1) partition_count,table_schema,table_name
    -> from information_schema.partitions
    -> group by table_schema,table_name
    -> having count(1) > 1;
+-----------------+--------------+-------------------------+
| partition_count | table_schema | table_name              |
+-----------------+--------------+-------------------------+
|              31 | clickit      | t_keyword_conversion_ga |
|               2 | part         | myoldpart               |
|               3 | test         | messages                |
|             256 | test         | ngram_key               |
|               2 | test         | rc1                     |
+-----------------+--------------+-------------------------+
5 rows in set (1.60 sec)

mysql>

You can get the complete definition by running SHOW CREATE TABLE db.tbl\G from any table from the result of that query.


In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server? I am looking for some advice in setting up a new SQL Server 2019 Always On instance.

We have a 2 server cluster right now, moving to Always On with a similar two virtual server setup.

I want to know if that is recommended.

We have about 200 databases, maybe 40 used per day.

We have about 350 SSRS reports run on average per day, up to max of 1100 in the past couple months.  We have about 50 SSIS jobs (packages) that run, mostly every night.

Should SSRS and SSIS be on a separate server?  Or should the SSRS temp databases be on the SQL Always On servers but the SSRS install be on the separate server?

The documentation at Reporting Services with Always On Availability Groups (SQL Server) states:

Reporting Services and Power BI Report Server offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services will not automatically use a different replica for the report server databases when a failover occurs. The use of MultiSubnetFailover, with the report server databases, is not supported.
Manual actions or custom automation scripts need to be used to complete the failover and recovery. Until these actions are completed, some features of the report server may not work correctly after the Always On availability groups failover.

So in general, you won't get any benefit of putting the reporting service database in an availability group without figuring out what needs to be done after a failover occurs.
Using SSIS with an availability group is supported and you can review the pertinent information at SSIS Catalog.
In general, putting them on a standalone server will be easier.  If you have hardware redundancy at the VM host layer, you are getting almost no value out of putting them in an availability group, particularly if your VMs are on SSDs that can reboot in about 20 seconds.


MySQL Replication ignore few columns I have setup MySQL replication, is there a way to skip replicating one column or multiple columns from few tables?

Replicate it!  Else you won't be able to failover to a Slave if the Master dies.  Deal with access control in some of the following ways:

Use GRANT to restrict what each user can see.
Use VIEWs to avoid showing the sensitive columns.
Segregate the sensitive columns into other tables (vertical partitioning), so that the GRANT applies to tables instead of columns and VIEWs aren't needed.


Performance implications of using OPENQUERY in a view Please see this question on stackoverflow :

  I am using an EasySoft ODBC driver to link a SQL Server 2008 R2
  Express instance to Interbase and I am having some difficulty with
  getting metadata from the remote server. From looking about on the net
  the main sugestions all mention using OPENQUERY instead of the four
  part linked server syntax.

  E.G. My current (problematic) approach is...

CREATE VIEW [LIVE].[vwPRDETS]
AS

SELECT *
FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)

  But on some tables I get the error when calling the view...

    Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
    linked server "LBLIVE" supplied inconsistent metadata. An extra column
    was supplied during execution that was not found at compile time.

  Also, some views I cannot even create because I get the following...

    Msg 7315, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
    linked server "LBLIVE" contains multiple tables that match the name
    ""SYSDBA"."AUDIT_LBABKP"".

  Although there is only one of the tables mentioned.

  The alternative approach from searching the net seems to be more
  like...

SELECT *
FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')

  So, my question is, if I use OPENQUERY in my view definition will SQL
  Server be able to optimise the resulting SQL being sent to Interbase?
  Or is there really not much difference between the two approaches?

It's a cross over subject and would love a dba's POV.

Summary

Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

Long

The key factor is where the query runs.

In this case, it is a trivial SELECT so all rows from a table will be sent down the wire. It doesn't matter.

When you add JOINs and WHEREs then it can matter. You want SQL Server to allow the linked server to do as much filtering as possible to reduce the size of the data coming over the network.

For example, the 2nd case here should be more efficient.

SELECT *
FROM OPENQUERY(<linked server>,
            'SELECT <column list> FROM MyTable') T1
     JOIN
     SomeLocalTable T2 ON ...
WHERE T1.foo = 'bar'

SELECT *
FROM OPENQUERY(<linked server>,
           'SELECT <column list> FROM MyTable WHERE foo = ''bar''')
     JOIN
     SomeLocalTable T2 ON ...

A limitation of OPENQUERY is that you can't parametrise: so you need dynamic SQL to add WHERE clauses etc.

Linked servers performance can be affected by sp_serveroption. The setting collation compatible says it all

  If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

That is, try not to let SQL Server process the data locally.

Note: On my foo = 'bar' 2nd example above, the filter is sent to the linked server because it just a string constant to SQL Server. The real WHERE clause in the first example may or not be sent remotely.

Finally, I've also found that staging the data into a temp table and joining that onto local tables is often better then joining directly onto the OPENQUERY.


Query Previous Records (Oracle) Consider an Oracle database that has a person, a date and a value in every record. When I pull a record, I want to very quickly and easily get the previous record where person is the same in both the one I fetched and the previous and date is the maximum date that comes before the record I fetched. When I ask for this, there is usually a hell of a lot of confusion, so here is an example data set:

PERSON,DATE,VALUE
1,2013-12-25,823
2,2013-11-20,384
1,2013-11-10,4837
1,2013-10-01 377
1,2014-01-01,8347

Now, when I fetch the first record, I want to also get the record 1,2013-11-10,4837 because person matches (as 1, not 2), and the date of the previous record is the maximum date that comes before the record I fetched.

I have tried using the connect clause, but it doesn't give the ability to ask for "the record with the maximum date that is less than the date of the currect record." The multiple join method that I'm using isn't functional because there are too many records - it takes hours to complete a single query.

Analytics functions to the rescue

In particular, lead/lag will be your friend.

SELECT
   LAG(ACTION_DATE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_ACTION_DATE
,  LAG(VALUE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_VALUE
,  PERSON
,  ACTION_DATE
,  VALUE
FROM
    SomeTabe

SQLFiddle


Alternative query to avoid count(distinct) i have a performance issue with my query and i need to rewrite it to have my result in better time. If you can help me with an idea?

My query is the following:

select
    dc.Country,
    COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
FROM
    dbo.Fact_VisitorsCountryDetails fvcd
    INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry
WHERE
    fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate
    AND fvcd.IdPartnerid = @IdPartnerId
GROUP BY
    dc.Country
ORDER BY
    Visitors DESC;

My DDL is:

CREATE TABLE [dbo].[Dim_Country](
    [Id] [bigint] NOT NULL,
    [Country] [varchar](24) NULL,
 CONSTRAINT [PK_Dim_Country_Id] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Fact_VisitorsCountryDetails](
    [IdDate] [bigint] NULL,
    [IdPartnerId] [bigint] NULL,
    [IdCountry] [bigint] NULL,
    [IdAppinstance] [bigint] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id] FOREIGN KEY([IdCountry])
REFERENCES [dbo].[Dim_Country] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id] FOREIGN KEY([IdDate])
REFERENCES [dbo].[Dim_Date] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id] FOREIGN KEY([IdPartnerId])
REFERENCES [dbo].[Dim_PartnerId] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id]
GO

DISTINCT is always a huge overhead.
You might try to move the aggregation into a Derived Table (of course this might only help if the optimizer is not smart enough to do that automatically)

select dc.Country,
  fvcd.Visitors
from
 (
   select
       fvcd.IdCountry,
       COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
   FROM
       dbo.Fact_VisitorsCountryDetails fvcd
   WHERE
       fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate
       AND fvcd.IdPartnerid = @IdPartnerId
   GROUP BY
       fvcd.IdCountry
 ) fvcd
INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry
ORDER BY
    Visitors DESC;


Can High Water Mark be cleared? Lately I experience below errors when restoring db2 database

SQL2043N Unable to start a child process or thread
SQL1084C  Shared memory segments cannot be allocated

db2stop and then db2start always helps.

As a part of my job (testing) I restore databases often during the day. Current usage can be decreased via deactivating databases when is it really close to Memory Limit. But lately only deactivation didn't help and I was still getting these errors. Then I found out that HWM usage might be the trouble part as it is only increasing and after I do db2stop & db2start both Current usage & HWM usage are reset.

Is there any other way how to 'clear/reset' HWM on db2?

DB2 9.7.2 on SuSE

From what I have seen reaching a High Water Mark means approaching some established limit. There are multiple "high water marks" in DB2 depending on which area you are talking about. Some relate to memory used for sorting/joining. Others relate to maximum agents allowed or differing heap sizes, or even package caching.

I'd recommend taking a look at this article on developerworks. Though it was written for an older version of DB2 (8.1), it still holds valuable nuggets for what parameters to tune when to get what you are looking for. I recommend taking a peek at it. If you search for high water mark on the page you find all the different instances I was referring to and which parameters tune them.

Also, take a look to see if you are running any hash joins and how many. This article is also helpful for understanding which parameters can be tuned that affect hash joins and certain usages of memory.


Still wrong to start the name of a user stored procedure with sp_? One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I could provide my co-worker with an explanation.

In the article (by Brian Moran) it is explained that giving the stored procedure an sp_ prefix makes SQL Server look at the master database for a compiled plan. Because the sp_sproc doesn't reside there, SQL Server will recompile the procedure (and needs an exclusive compile lock for that, causing performance problems).

The following example is given in the article to show the difference between two procedures:

USE tempdb;
GO

CREATE PROCEDURE dbo.Select1 AS SELECT 1;
GO

CREATE PROCEDURE dbo.sp_Select1 AS SELECT 1;
GO

EXEC dbo.sp_Select1;
GO

EXEC dbo.Select1;
GO

You run this, then open the Profiler (add the Stored Procedures -> SP:CacheMiss event) and run the stored procedures again. You're supposed to see a difference between the two stored procedures: the sp_Select1 stored procedure will generate one more SP:CacheMiss event than the Select1 stored procedure (the article references SQL Server 7.0 and SQL Server 2000.)

When I run the example in my SQL Server 2008 R2 environment, I get the same amount of SP:CacheMiss events for both procedures (both in tempdb and in another test database).

So I am wondering:

Can I have done something wrong in my execution of the example?
Is the 'do not name a user sproc sp_something' adagium still valid in newer versions of SQL Server?
If so, is there a good example that shows its validity in SQL Server 2008 R2?

Thanks a lot for your thoughts on this!

EDIT

I found Creating Stored Procedures (Database Engine) on msdn for SQL Server 2008 R2, which answers my second question:

  We recommend that you do not create any stored procedures using sp_ as
  a prefix. SQL Server uses the sp_ prefix to designate system stored
  procedures. The name you choose may conflict with some future system
  procedure. [...]

Nothing is mentioned there about performance problems caused by using the sp_ prefix though. I'd love to know if that's still the case or if they fixed it after SQL Server 2000.

This is fairly easy to test yourself. Let's create two very simple procedures:

CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

Now let's build a wrapper that executes them a number of times, with and without the schema prefix:

CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

Results:

Conclusions:

using sp_ prefix is slower
leaving out schema prefix is slower

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix


Insert- and Read-only table in Postgres I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete).

I came to know there is FOR UPDATE lock, but did not get how to use it.

Let's say for example I've the below table, how can I make it immutable (or, if I understood correctly, how can I use FOR UPDATE lock permanently)?

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

Assuming that "user" does not include any roles with superuser privileges (who can do everything), run this as owner of the table or superuser:

REVOKE ALL ON TABLE account FROM public;
GRANT SELECT, INSERT ON TABLE account TO public;

Users also need the USAGE privilege for the SEQUENCE attached to the serial column:

GRANT USAGE ON SEQUENCE account_user_id_seq TO public;

About serial:

Safely rename tables using serial primary key columns

.. and the USAGE privilege on the schema and CONNECT of the database, both of which are the case for schema public by default.

If you only want a subset of users to be affected, grant privileges to a group role instead of public and grant membership in that role to those users. You still need to revoke privileges from the pseudo-role public, which can be viewed as the default group role granting membership to all (irrevocably). And grant whatever is still needed to others (by way of another group role, for instance).

CREATE ROLE my_group;
GRANT my_group TO my_user1;
GRANT my_group TO my_user2;  -- more?
REVOKE ... FROM public;
GRANT SELECT, INSERT ON TABLE account TO my_group;
GRANT USAGE ON SEQUENCE account_user_id_seq TO my_group;

CREATE ROLE others;
GRANT others TO other_user1;
GRANT others TO other_user2;
GRANT ... TO others;

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


Processor queue length but not very high CPU usage I've a virtual server running SQL Server 2016 (4 vcores) where most of the time there is a processor queue length of 4 (sometimes it goes up to 15) but the CPU is using 25% on average. There are about 3000 batches/sec.

Using a query found in an article of Glenn Berry found on SQLSkills.com I found out that there is an avg_task_count of 15 and a avg_runnable_task_count of 2 (but not constantly) :

SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

Is it correct to say that the processor queue length and the avg_task_count are an indication there are a lot of requests and the server has problems to process them? But what I don't understand is why isn't the cpu usage higher?

The physical host has a 25% CPU usage, there is no memory reservation but there is no memory over-commitment on the host.

While the recommendations below may not specifically reduce the processor queue length below 4 on a 4-CPU VM, they will help with overall performance, which is presumably why you're asking your question.

Best practices published by VMware and Microsoft recommend explicitly that you should set both a CPU reservation, and a memory reservation for any VM running SQL Server.

See Architecting Microsoft SQL Server on vSphere - Best Practices Guide by VMware for all the details about how to make SQL Server run reliably with the best performance possible.

I know you're not using Microsoft's Hyper-V product, but for future users who might see this post, see Running SQL Server with Hyper-V Dynamic Memory for their recommendations.

Specific recommendations the get the best performance out of SQL Server include:

Configure "max server memory" to a setting that makes sense for your environment.  This post may help you determine what that should be set to.
Ensure you set "min server memory" to a value that makes sense.  For production SQL Servers, it is "typical" to set this either to the same value as "max server memory", or to a value 1 or 2 GB lower than max server memory, in case the O/S experiences extreme memory pressure.
Configure the service account used by SQL Server with the "Lock Pages in Memory" right.  See this post for details.
Ensure you have at least 2 cores configured for the VM.  You probably want many more than 2, but setting up the VM with a single core is a recipe for disaster.  For production SQL Server installs, I'd recommend at least 8 cores.
In Hyper-V, configure the VM to be NUMA aware, and disable "Dynamic Memory".  Many server folks will balk at this suggestion since it reduces the number of VMs that can be ran on any single host server; however it is critical for good performance for any service that consistently needs a lot of memory, such as a database management system.  In VMWare, configure the VM with a memory reservation that matches the total amount of memory assigned to the VM.  This prevents the VMware balloon driver from consuming memory in the VM, which would in turn reduce memory available to SQL Server, and thereby reduce performance.
Ensure you use configure the VM with a high-queue SCSI interface and network interface - read the best practices docs linked above for specific recommendations.

As @SqlWorldWide mentioned in a comment on your question, you should look at this.  Taken from that newsgroup post:

  First of all anything below 10 is normal for the processor queue length
  counter in the perfmon (according to the perfmon's documentation)

  Secondly if you have multiple processors you have to divide the queue length
  by the number of the processors. Because there is only one scheduler in the
  SMP system too.

  And lastly the queue length is an instanteneous counter. It shows the number
  of ready threads right now, which is a sort of misnomer because IMHO it
  takes into consideration only the highest priority queue (note that there is
  queue per priority). Those threads will eventually block on something like
  I/O or synchronization mechanism.

  Now the CPU usage counter is different:  It is calculated by
  measuring the duration of the idle thread is active in the sample
  interval, and subtracting that time from interval duration  Idle
  thread will run only when there is no ready thread with higher priority. And
  this is essentially the basic assumption of any preemtive system. I can have
  single thread (in addition to the Idle one) but if it runs code like this:

  while ( true );

  The CPU usage counter will show 100%. Note that in this hypothetical case
  the queue length is 1 :o)


SQL Job PowerShell task fails same command succeeds as user Simple requirement: delete files on remote path older than 20 days.

PS C:\Users\Administrator> Get-ChildItem `
    -Path \\FileServer\SQLBackups\SQLServer\ `
    -File -Recurse -Force |
  Where-Object {
    ($_.LastWriteTime -lt (Get-Date).AddDays(-20))
} | Remove-Item

The above command succeeds (vanity backticks & linebreaks added for readability).

The relevant SQL 2016 Job (backticks and linebreaks not added, but the command text is the same)

@subsystem=N'PowerShell',
@command=N'Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item',
@proxy_name=N'XPProxy'

The error information returned by PowerShell is:

  'A parameter cannot be found that matches parameter name 'File'.

No permissions issues (refer proxy_name);
SQL Powershell version:

Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      -1     -1

SQL @@VERSION:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Below is the actual parameter list of the Get-ChildItem function. Why can't SQL use the -File parameter?

Get-ChildItem
   [[-Path] <String[]>]
   [[-Filter] <String>]
   [-Attributes {ReadOnly | Hidden | System | Directory | Archive | Device | Normal | Temporary | SparseFile | ReparsePoint | Compressed | Offline | NotContentIndexed | Encrypted | IntegrityStream | NoScrubData}]
   [-Depth <UInt32>]
   [-Directory]
   [-Exclude <String[]>]
   [-File]
   [-Force]
   [-Hidden]
   [-Include <String[]>]
   [-Name]
   [-ReadOnly]
   [-Recurse]
   [-System]
   [-UseTransaction]
   [<CommonParameters>]

cd C:

Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item

https://www.sqlservercentral.com/forums/topic/powershell-for-network-path
http://www.midnightdba.com/Jen/2013/05/quick-tip-navigating-to-a-unc-within-sqlps/

Adding the cd C: line to my Powershell task in SQL Job allowed UNC references to resolve remote server file directory.

To quote a wise man:
"SQL Server Agent integrates with powershell in very unintuitive ways sometimes"

Thanks for your help, Peter.


Automatic ETL between similar tables I have 43 tables on 2 different servers which I have to sync regularly. Because of our network policy and IG reasons, we can't have link server object between these two servers. Standard procedure so far is to create a SSIS package that will do the transfer however, I have come across situation where I need 43 of those. As much as I would love to get on with the task, there has to be a smarter way of doing this. I have seen some articles about generating packages dynamically using C# but C# ain't my strongest suit.

Is there a way from within SSIS to perform this task dynamically?

I was thinking like maintaining mapping tables, one with source and destination tables and another with source and destination column mappings and looping through it but obviously metadata needs to be refreshed to achieve the successful execution.

Any thoughts or ideas will be highly appreciated.

This kind of situation is exactly what BIML (Business Intelligence Markup Language) is made for.

From Introduction to Business Intelligence Markup Language (BIML) for SSIS, this solves exactly the problem you are facing:

  With Microsoft SQL Server Integration Services (SSIS), you can build
  powerful and flexible packages to manage your enterprise-wide ETL
  solutions. However, every ETL project has some repetitive tasks: you
  need to import 20 different flat file exports from the ERP system, you
  need to load 15 dimensions into the data warehouse and so on. With
  SSIS out-of-the-box, you are forced to create multiple individual
  packages which is very time consuming due to the lack of easy code
  reuse. In a typical BI project, the ETL implementation can easily take
  up 60% of the project time, so it is beneficial if the development
  time of similar SSIS packages can be cut down.

Some tools you should definitely check out are:

BIDS Helper which has an add-in for BI Development studio or SQL Server Data Tools
SSIS Package generator

If you want to create a metadata mapping table and use that to generate SSIS packages you can have a look at Generate multiple SSIS packages using BIML and metadata


Can anyone recommend a beginner DBA course in London My organisation is in need of a SQL Server DBA and as the companies only "SQL Guy" my boss has suggested me to take SQL Server DBA course.  My general T-SQL knowledge is fairly good, I can write fairly complicated queries, have done stuff with stored procedures etc.  But when it comes to proper DBA knowledge, is where I fall down.  All my previous role had a DBA team who guarded their knowledge jealously so I am missing lots of best practice knowledge on how to deal with SQL server.

Can anyone recommend a good beginner SQL Server 2012 DBA course in London (UK)?

Thanks

Ian

Ask your boss to get you a pluralsight subscription.

Additionally, read answers from people like Paul White, Aaron Bertrand, Remus, etc and try to answer questions on this site.

Read blogs from sqlskills.com, brentozar.com, mssqltips.com, sqlperformance.com.

Also folks from sqlskills and brentozar are giving inperson training in LD.

Sqlbits event in UK and the videos on it are extremely helpful and don't forget virtual academy from Microsoft.


Large delete query seems to have frozen We ran a delete query on a database with 1.8bn rows. This delete would delete 1.2bn rows.

In hindsight, we would have broken up this query into 100m at a time but we are in a position where it has been running for 24 hours and the log file is at 2Tb which appears to be the maximum size allowed for a log file.

The database is in SIMPLE recovery mode.

Is there any saving this query? Or do we need to just restart the SQL Server and see what happens? Will the database be unusable? Is there anything we can do to kill this off as cleanly as possible?

First of all, check the SQL errorlog to see if it actually hit a max size for the log. If it did, then the query has no hope of completing, it is probably already in a rollback state.

Even if it is, I always prefer to kill the spid manually (use sp_who2 or sp_WhoIsActive to find the spid, then do a kill 59 or whatever). You also can't check the rollback status unless you do an explicit KILL, see this related thread.

Since this is a delete, and not an update or insert, you may be very lucky and find that it rolls back immediately. If not, it may take as long (or longer) to roll back as it did to get to this point.

To see the rollback status, use

kill 59 with statusonly

Unfortunately, I have found this frequently does not show anything useful, just a "0% complete". In that case, you'll have to use sp_who2 and watch the IO and CPU to see if it is still doing something.

Regarding the reboot, this is a grave risk. If the spid is actively rolling back (CPU and IO are changing), then restarting SQL will only take the database offline entirely until the rollback is completely finished (hours and hours). But, if the CPU and IO are not moving, then it may in fact clear it right away. Either way, it is a risk.

One final option, if things are especially dire: If you have a backup from just before the delete started (and there haven't been other updates to the db), then the fastest way to recover may be to simply drop the DB, restart SQL, and restore from backup.

If you can't drop the DB (or if you already restarted the instance and the sql errorlog is predicting a 24-hour recovery time), then shut down SQL services, delete the MDF and LDF files from disk, start up SQL, drop the (ghost) database, and restore from backup.

Obviously you'd only attempt that if this were a back-end processing database that users didn't interact with.


Log File Size near 100% - How to find Active Transaction The transaction log file size is nearing 100% on one database.
I run the below and both return 0 so no active transaction running.

The last query tells me that the database is in SIMPLE mode, but 'ACTIVE_TRANSACTION' is returned in log_reuse_wait_desc column.

However, I cannot see any running transaction! I have tried querying sys.dm_tran_database_transactions but do not see any transaction for this specific database.

How do I find this transaction?

SELECT @@TRANCOUNT;

SELECT XACT_STATE();

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

You can use the following DMV to view any transactions that might be causing your log file to be bloated: sys.dm_tran_database_transactions.

Both your queries are limited to your current connection, meaning they give you no information about other connections made to the SQL server.

@@Trancount;
XACT_STATE


Cassandra change replication factor hive datastore I am trying to change replication factor of HiveMetaStore but I receive this message:

cassandra@cqlsh:system> alter KEYSPACE HiveMetaStore with replication =
    {'class': 'NetworkTopologyStrategy', 'DCassandra1': 1};

  InvalidRequest: code=2200 [Invalid query] message="Unknown keyspace hivemetastore"

HiveMetaStore keyspace exists. What could be the problem?

I changed replication factor from OpsCenter and it worked.

Why was I not able to change id from cqlsh?

By Default cql will convert the table/keyspace/column name in lowercase.
If you have table/keyspace/column in uppercase you have to enclose it in double quotes.

alter KEYSPACE "HiveMetaStore" with replication =
    {'class': 'NetworkTopologyStrategy', 'DCassandra1': 1};

Opscenter handled it for you:)

CQL UpperCase/lowercase


Storing all data in a single db versus multiple dbs in Mongodb We have an app that is basically an ERP. Some of the resources in the ERP are satellite tracked. So the information related to the resource itself is minimal, but the satellite tracker produces data every 3 minutes. Our instinct was to store the satellite information in it's own db or even it's own cluster and store the ERP information in it's own cluster.

What do you think?

Well, 2.2 will add database level concurrency (this is available for testing in the 2.1 development branch as I write this answer), so if you are concerned about lock contention between the two, then separate databases would be a good idea.

You don't really mention what your criteria are here to make the decision, so the new 2.2 feature is the only relevant one I can think of - feel free to comment or expand and I'd be happy to answer further.


What are exact benefits of using R services on Enterprise vs Standard? I am currently using SQL Server 2016 SP standard edition and want to install R services. In the documentation is said that a stand alone installation is allowed only on Enterprise (Machine Learning Server (Standalone)).

Also, I found a post, where the guy is talking that on standard I am not able to use parallel operations and there are memory limitations:

Could anyone tell what are the exact memory/processors limitations?

I need such details as going to use heavy R calculations and the tests for now show that the implementation in the standard edition is not working as I hoped.

Check out the "Feature availability across editions of SQL Server Machine Learning Services" page of Books Online:

  The difference between basic and advanced integration is scale.
  Advanced integration can use all available cores for parallel
  processing of data sets at any size your computer can accommodate.
  Basic integration is limited to 2 cores and to data sets fitting in
  memory.

Books Online goes into more details on that page about deployment restrictions as well like:

  Only a (Standalone) server offers the operationalization features that
  are included in a Microsoft (non-SQL-branded) R Server or Machine
  Learning Server installation. Operationalization includes web service
  deployment and hosting capabilities.

  For an (In-Database) installation, the equivalent approach to
  operationalizing solutions is leveraging the capabilities of the
  database engine, when you convert code to a function that can run in a
  stored procedure.


configure sql developer for oracle using IPC fails with network adapter error I have oracle 11 XE installed locally. I can connect on the same machine using sqlplus with sqlplus user/pass. I can also connect using sqlplus user/pass@XE (XE is defined in tnsnames.ora and this connection uses TCP (default). I added an entry in tnsnames that uses IPC (XE_IPC) and I can connect using sqlplus user/pass@XE_IPC. tnsnping XE_IPC succeeds.

SQL Developer connection, when I choose TNS and then XE_IPC from the dropdown, fails with a network adapter error.

My question: how do I configure SQL Developer such that the IPC connection works? Once that works, what is the JDBC connection string that will work?

Here is the rub: my company locks down ports like nobody else, so things like localhost and 127.0.0.1 do not work. This is why I'm using the IPC connection (or at least trying to).

TCP connections using sqlplus, JDBC, and SQL Developer work flawlessly when the server is on another machine.

I tried following Balazs Papp's suggestion. First issue was JDBC version was too low, so I copied ojdbc8.jar from SQL developer JDBC/lib directory to the oracle home JDBC/lib directory that had the other ojdbcX.jar. Passed that test.

Now my error is the native OCI library load failing:

Testing testing native OCI library load ... Failed:
  Error loading the native OCI library
  The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
  the expected native library directory C:\oraclexe\app\oracle\product\11.2.0\server\bin is present and precedes any other client installations.
  java.library.path = C:\usr\bin\sqldeveloper\sqldeveloper\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;c:\oraclexe\app\oracle\prod

There is no obvious way to set java.library.path. \windows\sun\java\bin doesn't exist and is added by SQL Developer. My path starts with C:\oraclexe\app\oracle\product\11.2.0\server\bin, so everything before that is added by SQL developer.

What file(s) is SQL Developer looking for to use the thick client?

When I ignore the error and try his next step, when I click TEST I get "Status: Failure - Test failed: no ocijdbc18 in java.library.path". Sadly, I don't see where ocijdbc18 can be downloaded, and it doesn't exist on my system (I do have ocijdbc11.dll in my server directory.)

This is not IPC, but achieves the same, and you do not even need a listener for this.

Make sure you configure SQL Developer to use an Oracle Client with the OCI/Thick Driver. You can use the installed database home as client, and you can set this in Tools / Preferences / Database / Advanced, e.g:

When creating the connection, choose Local/Bequeath as connection type, e.g:


Migrating MySQL 8.0.20 database to AWS Aurora I originally posted this question on stack overflow as I did not know this service existed and someone suggested I may get a better answer here.
I was trying to use the AWS RDS Proxy service and realized that the only compatible versions with the service are AWS Aurora databases/clusters.
The current database I have is a MySQL database using MySQL version 8.0.20. AWS Aurora is MySQL compatible but only with version 5.6 and 5.7. I am not overly familiar with these versions and how different it would be to version 8.0.20.
Has anyone gone through the process of converting a MySQL database to Aurora? I know there are docs on the subject out there but they deal with version 5.6 or 5.7, has anyone done it for later versions?
I have all the MySQL code in SQL files along with the stored procedures so the database creation can be automated to an extent, it would just be if anyone has ran into issues doing it/found a better way than running multiple SQL files to transfer over data.
If you have gone through the process are there major differences between MySQL and Aurora in terms of running stored procedures etc?
EDIT
Just to give a bit of background, this question is the original reason I am migrating to Aurora as it seems to have a better service for managing connection pools https://stackoverflow.com/questions/67600414/should-i-use-connection-pooling-in-aws-lambda

The biggest potential problems with going backward from 8.0:

CTEs
Windowing functions
new COLLATIONs
Various minor syntax changes that are best found by trying the transfer.

If you are not using those, a mysqldump might produce a file that can be read by 5.6 or 5.7.  Try it.  Come back with specifics if that fails.


mariadb (mysql) innodb not optimizing SELECT for partitions? I have a table partitioned by on column "level" -- I have logs where level 7 is the verbose info, which is like 20x as many entries as all the other levels.

When purging old logs in level 7, it was taking quite a while. I had assumed it was just the amount of logs. (And possibly also the transaction time).

But when I compared the query-select with a clone in myisam, it seems it's ignoring the simple partition optimization it could make!

Even worse, this select optimization doesn't carry over to the DELETE query, as per EXPLAIN! It takes the partition, but won't use the date index!

Is this a bug, or just my lack of knowledge?

SHOW GLOBAL VARIABLES LIKE '%version%'
innodb_version                      5.6.26-74.0
protocol_version                    10
version                             10.1.9-MariaDB-1~jessie
version_comment                     mariadb.org binary distribution
version_compile_machine             x86_64
version_compile_os                  debian-linux-gnu
version_malloc_library              system jemalloc
version_ssl_library                 OpenSSL 1.0.1t 3 May 2016
wsrep_patch_version                 wsrep_25.11

https://gist.github.com/avimar/16824955e21781c7e2c6594ef743bc6f

show create table logs_fs
CREATE TABLE `logs_fs` (
  `uuid` char(36) DEFAULT NULL,
  `host` varchar(4) DEFAULT NULL,
  `date` datetime(6) NOT NULL,
  `hrtime` bigint(20) unsigned NOT NULL,
  `level` tinyint(4) DEFAULT NULL,
  `value` text,
  KEY `uuid` (`uuid`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB) */

My naive query: sql should know which partition!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` WHERE `date` < date_sub(now(), interval 1 week) and level=7
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs ALL date    NULL    NULL    NULL    1,777,576   Using where

I had to specify the partition AND leave out the level=7 to get down to a lower number!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` PARTITION (p1) WHERE `date` < date_sub(now(), interval 1 week)
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs range   date    date    8   NULL    423,360 Using where; Using index

Indeed, it works in myisam! (Funny, it says it's querying 196k rows, but it returns the proper count of 210701 which matches innodb)

CREATE TABLE `logs_fs_myisam` (
  `uuid` char(36) DEFAULT NULL,
  `host` varchar(4) DEFAULT NULL,
  `date` datetime(6) NOT NULL,
  `hrtime` bigint(20) unsigned NOT NULL,
  `level` tinyint(4) DEFAULT NULL,
  `value` text,
  KEY `uuid` (`uuid`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = MyISAM) */

explain select SQL_NO_CACHE count(*) FROM `logs_fs_myisam` WHERE `date` < date_sub(now(), interval 1 week) and level=7`
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs_myisam  range   date    date    8   NULL    195,890 Using where

PARTITIONing with fewer than 10 partitions is rarely useful.

Perhaps PARTITION BY RANGE(TO_DAYS(date)) would be better?  Perhaps daily partitions is best for your application.

Is your goal to purge only "level 7 AND old"?  The gets rather messy because  you would be purging only part of a partition, not being able to DROP PARTITION.

On the other hand, maybe this is a valid application for subpartitioning??  That would be exciting (to me) since I have not yet seen a use for subpartitioning.  Beware of the limitations of what you can put in the BY clause.

I think you would have to go with

 PARTITION BY RANGE(date)
     SUBPARTITION BY KEY(level)

And use ALTER TABLE ... TRUNCATE PARTITION ...  This would leave an empty partition behind, but it would be quick.  Check the ramifications of innodb_file_per_table=1.


Direct values on predicate produce not-so-good plan I'm using StackOverflow dump to run some tests.

In particular, I'm querying this table:

I have created this index:

I'm running the following query (just forcing an index to test alternatives)

I get the following exec plan high a cost (66.63).

These are the IO statistics after running this query:

Then, I run the same query by providing variables instead of direct values

I get a better plan (Cost is 0.4385).

Statistics are also better:

At first...I thought SQL Server was not recognizing direct values as INT, but there are neither type mismatch nor implicit conversion warnings.

I also tried avoiding parallelism, but I still get a high-cost plan (and higher IO statistics) with MAXDOP 1 when passing direct values in the predicate.

When comparing both plans, there are different estimates:

What's wrong with direct values being passed as part of predicate?

What's wrong with direct values being passed as part of predicate?

The Difference Between Variables and Parameters

The optimizer uses the statistics density vector when calculating estimates for variables.

When "direct" or "static" values are embedded in the query directly, the statistics histogram is used instead.  This is why you get different estimates, and thus different plans.

Here's my estimated plan: https://www.brentozar.com/pastetheplan/?id=SJCduTuKN

On my 2010 copy of the SO database, the density of the OwnerUserId column is .000003807058.  Multiplying that by 3,744,192 rows = 14.2544 rows.  Which is exactly the number of rows estimated to come out of IX_Posts_OwnerUserId.

You can get this information about the statistics for that index by running this DBCC command:

DBCC SHOW_STATISTICS('dbo.Posts', 'IX_Posts_OwnerUserId');

Here's the (abbreviated) output:

Name                    Updated             Rows
IX_Posts_OwnerUserId    Apr  8 2019  8:33AM 3744192

All density     Average Length  Columns
3.807058E-06    4               OwnerUserId

Since PostTypeId is also part of the WHERE clause, statistics are automatically generated for that column as well. That density vector comes out to be .25 x 3,744,192 rows = 936,048 rows.

DBCC SHOW_STATISTICS('dbo.Posts', '_WA_Sys_00000010_0519C6AF');

And the output:

Name                        Updated             Rows
_WA_Sys_00000010_0519C6AF   Apr  8 2019  9:04AM 3744192

All density Average Length  Columns
0.25        4               PostTypeId

Since this is an "AND" predicate, the estimate uses the lower of the two.

When you use static values instead of variables, it uses the statistics histogram.   This is in the third resultset of that SHOW_STATISTICS command.  For the key you're using, here is the histogram entry:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
22656           13040       11371   305                 42.7541

This is where the estimate of 11,371 comes from in the "static values" plan.

The histogram can be a better estimate a lot of the time, as it deals with edge cases a bit better - since there will often be some outliers in a big table like this.

Costing Differences

In this specific case, the histogram produces an estimate that is exactly right.  The cost of the plan produced is (correctly) higher than the one that uses the density vector, because it has to process many more rows.

The "lower cost" plan thinks that 14 rows will be produced by that seek, when in fact 11,371 rows are produced.

Logical Reads

Logical reads are slightly higher in the parallel plan because of nested loops prefetching.  It doesn't seem to make a big difference on my machine - elapsed time for the queries was within 10 ms of each other.

The parallelism doesn't actually help anything, since all of the rows end up on one thread (on my machine anyway).  Adding OPTION (MAXDOP 1) helps with the execution time, but doesn't remove the extra logical reads.

One potential solution to the "extra reads" problem for this query would be to avoid the key lookup entirely by adding PostTypeId as an included column:

CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts (OwnerUserId)
INCLUDE (PostTypeId)
WITH (DROP_EXISTING = ON);


How to determine/set the Java classpath for an Oracle DB? l am trying to make use of some proprietary Oracle Java classes/methods using an Oracle provided virtual machine (E-Business Suite Vision, Oracle 12.2.28)

On my test server I define a function:

[oracle@apps java_test]$ sqlplus USER/PASS@SID

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 21 10:21:24 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create or replace function methodname(foo in varchar2, bar in varchar2)
return varchar2 as
language java
name 'path.to.classname.methodname(java.lang.String,java.lang.String) return java.lang.String';
/  2    3    4    5

Function created.

Which seems to compile. However when I attempt to call it, I get the following error.

SQL> select methodname('foo', 'bar') FROM DUAL;
select methodname('foo', 'bar') FROM DUAL
                                                                                *
ERROR at line 1:
ORA-29540: class path/to/classname does not exist

l know for a fact that this class exists at /u01/install/APPS/fs1/EBSapps/comn/java/classes/path/to/classname.class. The question is have is, how do I set the classpath for the database (either permanently or for the session) so that it can access this method? Alternatively, is there a query to determine what the classpath is so that I can copy the classes to the proper location in the operating system?

The JVM embedded in your Oracle database only allows you to access java classes loaded inside that database: accessing external classes (from OS files) is not allowed.

First you need to find out what java classes are already loaded in your database by reading dictionary view ALL_JAVA_CLASSES. You will find that it has already 10's of thousands of classes loaded.

SQL> select owner, count(*) from all_java_classes group by owner order by owner;

OWNER        COUNT(*)
---------- ----------
MDSYS            1134
ORDSYS           1944
SYS             34358

3 rows selected.

But it is unlikely that the EBS-specific classes you want to invoke are there. So you then need to load those classes in your database.

You can do that using the loadjava command line tool, which lets you load entire jar files into your database. Or you can also use the DBMS_JAVA package, specifically the LOADJAVA() method.

Of course those EBS classes may have dependencies on other EBS or third-party classes. You will then also need to load those.

Read about it in the Java Developer's Guide (https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev)


Transfer data from Excel to table How can I transfer information from an Excel file to a table in a SQL server database through SSMS?

Excel file Consists of five columns and a thousand rows.

I followed the steps as explained here,at second 00:52 I can not continue because of the following error message :

  The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local
  machine. (System.Data)

I tried to export the excel file to different versions of Microsoft(2003, 2007, 2010) but the problem was not solved.

Is there any other way to solve this problem?

You must install the Microsoft Access Database Engine 2016, as Justin Onstot already wrote (https://www.microsoft.com/en-us/download/details.aspx?id=54920), and ALSO you MUST run the correct version of Import/Export application (32 or 64 bit) to be successful. You have a 64-bit SQL Server, so you must install the 64-bit Access Database Engine and perform the following steps:

Go to Start Menu -> Microsoft SQL Server 2017 ->  SQL Server 2017 Import and Export Data (64-bit)
Open the application and try to import data using the "Excel 2016" option, it should work fine.

Hope this helps.


Performance of XML to Table Function in Stored Procedures In a number of the databases I'm helping to maintain, there is a pattern where the code passes in a list of IDs as an XML string to a stored procedure. A user-defined function turns them into a table which is then used to match IDs. The function that accomplishes this looks like this:

ALTER function [dbo].[XMLIdentifiers] (@xml xml)
returns table
as
return (
    --get the ids from the xml
    select Item.value('.', 'int') as id from @xml.nodes('//id') as T(Item)
)

This works fine when we test it in a wide variety of scenarios in SSMS. But if it's called within a stored procedure, it will run extremely slowly and the execution plan will show it spending the large majority of time parsing these IDs. This is true whether we write the results to a temp table, join to them or use them in a subquery.

Example from an execution plan using the above function:

Can anyone offer insight as to why we are seeing such poor performance from these queries? Is there a better way to parse the values from XML? Most of the databases using this pattern are on SQL Server 2014 or 2016.

To answer part of your question, yes there is a better way to parse the values from the XML.

Always( * ) extract the text() from the xml node at the earliest opportunity.

( * - "it depends" but always test to make sure)

In your case, this means changing the "nodes" method to use the text() node as part of the xpath query:

ALTER function [dbo].[XMLIdentifiers] (@xml xml)
returns table
as
return (
    --get the ids from the xml
    select Item.value('.', 'int') as id from @xml.nodes('//id/text()') as T(Item)
)

In this simple test, you will see not only does a simple statistics test show a dramatic improvement, but also the execution plan changes significantly.

declare @x xml
select @x = (select top(100000) row_number() over(order by @@spid) as [n] from sys.columns as [a],sys.columns as [b] for xml auto,elements,type);

declare @c int;

set statistics io,time on;

-- Usual suspect
select @c = nd.value('.','int')
from @x.nodes('//n') x(nd);

-- Using text() when we might also need other parts of the node
select @c = nd.value('(./text())[1]','int')
from @x.nodes('//n') x(nd);

-- Using text() when that is all we need from the node
select @c = nd.value('.','int')
from @x.nodes('//n/text()') x(nd);

set statistics io,time off;

RESULTS

Not using text():
   CPU time = 1281 ms,  elapsed time = 1459 ms.

Using text() late:
   CPU time = 719 ms,  elapsed time = 739 ms.

Using text() early:
   CPU time = 406 ms,  elapsed time = 473 ms.


PostgreSQL replication for archiving I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.

I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work.

Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either.

Thanks

Bucardo is log/trigger based as I understand it.  This means it doesn't require that other tables are not accessed.

The simple solution seems to me to create a trigger which inserts rows in these tables to rows in archive tables when they are inserted or, in the alternative, deleted.  This way everything works properly on Bucardo's side but you retain a copy of the full data elsewhere.

Actually a better option might be to move to an unreplicated partition on delete.  That way you still get a consistent view of the data as if the delete never took place.

Additionally if you have trouble making Bucardo work, Slony would be an option.


append 0 returned by DATENAME function Sql Server management studio I want to append 0 to my minute(mi) result returned by DATENAME function of sql server management studio.

select datename(mi,GETDATE())

If the current time where 12:06:33.12(hh:mi:ss) then '6' would be
returned by the above query and I want 0 to be prefixed with the
result i.e '06' only when my minute is less than 10.
This can be easily done by branching and checking the result obtained
by above query and seeing if it is less than 10 i.e using a CASE
statement.Is there a much cleaner way to do the job without
branching?

You could do something like this:

SELECT RIGHT('00' + CONVERT(VARCHAR(2), datename(mi,GETDATE())), 2)


Does MySQL Replication hamper the performance of my DB? I am not exactly a 'Qualified' DBA, but yes i am in charge of my own DB which we use in our social app.

I have recently implemented Master/Slave Replication on my Database for obvious reasons.

What I wish to know is whether MySQL Replication kills the performance of my DB since for every user writing on my Master DB creates an additional write on Slave DB (I might be wrong here).

There is no lag between my slave & master. So, Replication is almost instantaneous.

There are two major things a Master has to handle because of a Replication Setup

DISK I/O

The Master has to write every completed SQL transaction to the binary logs

NETWORK I/O

When a Slave is connected, the Master must play traffic cop.

Master writes SQL statement to the most recent Binary Log
Master polls for DB Connections emanating from Slaves
For Each Slave DB Connection, the Master does the Following:

Master receives request for the oldest SQL Statement from the Binary Log since the the Slave's last entry in its Relay Logs
Master sends the following to the Slave's IO Thread

Master Log Filename
Master Log Position
SQL Statement at the Master's Log Filename and Position

If a busy Master has Slaves with Seconds_Behind_Master : 0, simply marvelous.

If either of these metrics become noticeable, you may need to do certain things:

Scale up DB Server

Place Binary Logs on a Separate Disk
Faster Disk I/O
RAID10 (rather than RAID5)

Is raid 5 suitable for a mysql installation?
Master - Slave MySQL setup on a VMWare Cloud — Is it needed? (ServerFault)

More RAM

Scale out the Topology

Add More Slaves
Make Each a Slave Master to Other Slaves
Create Distribution Masters

Here are my other posts about setting up replication topologies

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Aug 20, 2012 : How to use MySQL Server 5.6 for GeoIP replication?
Jun 25, 2012 : Mysql database replication on different vlan/subnet/another site
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Aug 18, 2011 : With MySQL Replication, what level of resilience is possible?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Mar 29, 2011 : MySQL high availability, failover and replication with Latency


Restrict root user access to postgres user I know this is a little weird but bear with me.

If a hacker gains access to a server's root user, he can type "su postgres" and then "psql" and boom - instant access to everything.

I was wondering if there was any possible way to block the root user from switching to the postgres user. In other words, I want to force a postgres-specific password to be used before postgres access is granted.

Is there any way to do this? Or is the root user always God?

You cannot do what you are asking.

Here's what you should do instead:

Run your database on its own computer in its own network segment. Don't run anything else on that computer (save backup / monitoring agents)
Use a separate management network for server management / backup / monitoring
Send your db + os security logs to another machine and use a software tool to monitor them and send alerts
Don't connect to database from web servers as a superuser


Vacuum in postgresql and Purge in mysql Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?

Similar, but not exactly.

They are both related to the MVCC architecture, and related to garbage collection, broadly speaking.

Vacuum in PostgreSQL removes obsolete or deleted record versions, and marks the space as available for re-use. This is accomplished by running the VACUUM command periodically, or by enabling the optional autovacuum daemon.

Purge in MySQL removes obsolete InnoDB record versions from the rollback segment, so that space becomes available for re-use. Purging happens automatically every 10 seconds in the InnoDB server master thread, or in a dedicated purge thread. In fact, you can't disable purging unless you run MySQL in a forced-recovery mode.

The difference is subtle and related to the respective implementation of MVCC. Probably the answer you're looking for is simply, yes.


Optimised query to select document and possible annotations I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current user.

Originally I tried this:

SELECT content,select_start,select_end,select_colour
FROM documents
LEFT OUTER JOIN annotations
USING (document_id)
WHERE document_id=<some_document_id_here> AND (user_id=<some_user_id_here> or user_id IS NULL)

But it failed to return a document if another user had added annotations. So I'm now using this:

SELECT content,select_start,select_end,select_colour
FROM documents A
LEFT OUTER JOIN (
    SELECT document_id,select_start,select_end,select_colour
    FROM annotations
    WHERE user_id=<some_user_id_here>
) B
ON A.document_id = B.document_id
WHERE A.document_id=<some_document_id_here>

which works, but the inner select statement could create quite a sizeable temporary table. I'd like to know if there's a better way to achieve this.

The best I've been able to come up with is to limit the size of the temporary table as much as possible by changing the inner WHERE to:

WHERE user_id=<some_user_id_here> AND document_id=<some_document_id_here>

It means the document ID has to be repeated in the statement, but at least means that the inner table is small, and pretty much guaranteed to be in-memory.


How to check/change MySQL named pipe settings? I'm having a hard time finding anything on this because I don't know what to call it.  But when you install MySQL it asks you if you want to allow connections to the database from other machines or not, and gives you an option to use named pipe.   Well, I want to check how a particular server is setup and change it.  How do you do that? I mean without reinstalling.

If you are using Windows, you need not worry about named pipes at all. Why ?
Years ago, MySQL for Windows had distributed three(3) different executables:

mysqld.exe
mysqld-nt.exe
mysqld-max-nt.exe

The two executeables mysqld-nt.exe and mysqld-max-nt.exe used the named pipes protocol. This is briefly mentioned in Chapter 23 Section 23.2 Page 353 Bulletpoint 3 of the Book

I have a 3-year-old post about this (MySQL-NT is crashing frequently)
Those NT-based mysqld executables are no longer being distributed. Even for mysqld-nt, --enable-named-pipe was not enabled by default. The option is still available today for mysqld.exe.
Do yourself a favor and do not enable it since there is so little documentation on using this protocol. It was originally meant for Windows NT (Not There).
YOUR ACTUAL QUESTION
You can run
SHOW GLOBAL VARIABLES LIKE 'named_pipe';

It will either say ON or OFF.
You can configure it by adding this to my.ini under the [mysqld] group header
[mysqld]
named-pipe

and restarting mysqld.exe by running (as Administrator)
C:\> net stop mysql
C:\> net start mysql


Which is suitable db2 version on windows server 2012 server? I am planning to install db2 database server on my client machine.

So which version of db2 will be more suitable on  windows server 2012 server

  db2 10.1.0

or

  db2 10.5.0

or any other ?

Thanks in advance!

db2 v11.1 was recently released, it works great on a 2012 server.


split big table into two joined view i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute.

for example now i have

tb_products

iditem | name | attributes
-----------------------
1      | art1 | a
2      | art1 | b
3      | art1 | c
4      | art1 | d

what i need is

vs_product

iditem | name
-----------------------
1      | art1

vs_attrib

iditem |  attributes
-----------------------
1      | a
1      | b
1      | c
1      | d

what i have tried is something like this and it seem to work, but is too slow when extractiong attributes

vs_product

SELECT
Min(.tb_products."id") AS "Product ID",
FROM
tb_products
GROUP BY
tb_products.item

vs_attrib

SELECT
a1."id" AS "oldid",
(
SELECT MIN
    ( a2."id" )
FROM
    tb_products a2
WHERE
    a1.name = a2.name
GROUP BY
    a1.name
) AS "newid"
FROM
tb_products AS a1

any ideas?

only the second, can you be more specific?

For second view test

CREATE VIEW vs_attrib
AS
SELECT MIN(iditem) OVER (PARTITION BY name) iditem, attributes
FROM tb_products

Index tb_products (name, iditem) may increase view's performance.


60GB in version store in short amount of time, allow snapshot isolation is disabled We had a situation where the version store went from zero to 60 GB in just a few minutes.  It stayed high for hours and due to the performance impact of this on tempdb (disk queue was 1000), almost everything became super slow and we ended up failing over to a secondary to get back to a reasonable state of performance.

Snapshot isolation is not enabled on any of the databases, and I can see from the session captures that everything is read committed or read uncommitted.   NOLOCK is used extensively (please refrain from going down this rabbit hole unless it is 100% relevant to the issue at hand) to manage blocking.

I have found references stating that online index rebuilds will use version store, but there were no rebuilds running.  There was a REORGANIZE running on a 122 GB table, but according to my understanding this would not use any version store as a reorg just moves one page at a time.  Additionally, this had been running for hours before the version store usage escalated, so I'm quite sure this was not causing the issue.

There were no sleeping sessions with open transactions.

I have found some other references about MARS and triggers using version store, but not seeing that those were factors here as there are only two triggers for auditing on some small user tables.

The one thing I've noticed that started at the time the version store exploded was a column data type change was started on a load table that had ~30 million rows:

ALTER TABLE dbo.some_table ALTER COLUMN UAI_ID BIGINT NOT NULL

And it was still running 5 hours later. (Please refrain from asking why this would be done as I don't know.)  There have been no recent changes to the process and it hasn't caused a problem before, so I don't see that this would cause the version store to go nuts, either.

I've seen references to reviewing the "internal space" used by sessions, but these only add up to ~10 GB, so I'm not seeing a correlation there.

Prevailing wait types were page latch and CXPACKET shortly after the version store usage went high.  As time went on and things started to pile up, SLEEP_TASK and CXPACKET prevailed.

What am I missing here?  What other types of activity could use so much version store so fast?

What other types of activity could use so much version store so fast?

If the instance was acting as a readable secondary replica for an availability group, then snapshot will be used for read queries against that database - regardless of the settings or specific isolation levels requested.  If there are a lot of data changes on the primary for that AG database, or really long running transactions, then the version store can grow very quickly.

Note that this could be a different database and AG from the one where performance issues were occurring - since prior to SQL Server 2019, all user databases share the same version store in tempdb.

Regarding the waits you encountered, one thing I learned recently that's not very intuitive is that hash spills to tempdb incur the SLEEP_TASK wait type.  This shouldn't cause the version store to grow, but it lines up with the disk issues you mentioned with tempdb (disk queue being 1,000, etc).