How to optimize indexes on MySQL query with various sorts I have an INNODB table levels:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_id            | int(10)      | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(5,4) | NO   |     | 0.0000  |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | NO   |     | NULL    |       |
| playable_character | int(2)       | NO   |     | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

There are ~4 million rows. Because of the front-end functionality, I need to query this table with a variety of filters and sorts. They are on playable_character, rating, plays, and date_published. The date_published can be filtered to show by the last day, week, month, or anytime(last 3 years). There's also paging. So, depending on the user choices, the queries can look, for example, like one of these:

SELECT * FROM levels
WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()
ORDER BY date_published DESC
LIMIT 0, 1000;

SELECT * FROM levels
WHERE playable_character = 4 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 WEEK) AND now()
ORDER BY rating DESC
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
ORDER BY plays DESC
LIMIT 1000, 1000;

I should add that rating and plays are always queried as DESC. Only date_published may be either DESC or ASC.

I started out with an index idx_date_char(date_published, playable_character) that worked great on the first example query here. Based on some other answers, I changed to two other indexes (date_published, playable_character, plays) and (date_published, playable_character, rating).

The first query still runs very fast, however there's some unusual things happening in EXPLAIN, when player_character = x exceeds a certain number of rows (~700,000): the USING WHERE pops on in EXPLAIN.

So, first question is are there any improvements in the query or indexes possible, and, second, what MySQL settings should get altered to allow for the large result sets.

Any suggestions greatly appreciated. TIA.

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.


After Update Trigger When Multiple Rows To Update Is it possible to use a trigger to update multiple rows?  For example, I have an app that will use a Table Value Param & a SQL Stored Proc to dump anywhere between 15 and 20 rows into a table.  Once that data is dumped, I then need a way (I was thinking a trigger) to update the remaining "missing" fields.  I thought this approach may work (it is the valid syntax for my update)

Create TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE a
Set
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum

How would I need to code this trigger to update multiple rows?  I assume I would somehow need to join on the virtual table but I am not certain.

You are right, there is a virtual table called "inserted" which is what you would need to join on. This table contains all the rows which have been inserted in the original table insert statement. So something like:

CREATE TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE a
Set
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum
INNER JOIN inserted i
ON b.linkfield = i.linkfield


SQL Server 2014 AG Listener I have built a two node SQL Server 2014 Always On cluster on the same subnet.

I need to create two availability groups/listeners in the same instance for two sets of databases. Do I need two separate IP addresses for each listener or one IP address can be shared for both listeners ?

Creating a listener creates (permissions must allow, otherwise the entry will be made by the network team) a DNS entry, and has a dependency to an IP address. It logically follows that each listener needs its own IP address, because how else will traffic know where to be directed?

See Failover Cluster Manager>[Cluster Name]>Services and applications> [Availability Group name]. Under Server Name section, the listener name is displayed and shows the IP Address underneath, which is reflected as a Dependencies entry if you open the properties of the listener name.

If you find a way around it, I'd sure like to know, because it doesn't make sense to me that it would work. Good luck!


Query hangs forever at first but works after one day I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it

First I rename the original tables in schema B
Then, using SQL Developer I copy the table to copy from schema A to B. (I right click the table to copy and select Copy and choose the destination schema)
Then I create all the indices that exist in the original tables.(Copying tables does not copy indexes, so you have to create them manually)

After the process is over, I run queries against all the tables. Everything goes fine with 9 of them, but with one of the tables the executed query hangs. The next day, when we run the same query against the same table, the execution takes only a moment. I have no idea what the problem is.  I tried not adding indexes but it didn't help. I know he way we copy table content might seem weird.

Not sure if the query against that one table really "hangs", it might just be taking much longer time to execute. If you say things look fine next day, the lack of statistics on the newly created tables might be the reason for poor performance. Next time, you follow your process of "copying" tables, you may want to explicitly collect statistics on newly created tables and their indexes before you run the queries.


Pgpool II : unable to read message length between two network interfaces Start log file:

2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for 0.0.0.0:9999
2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for :::9999
2015-03-06 01:57:56: pid 2760: LOG:  pgpool-II successfully started. version 3.4.
0 (tataraboshi)
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: checking backend no 0
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:58:02: pid 2792: ERROR:  unable to read message length
2015-03-06 01:58:02: pid 2792: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

Debug log file:

2015-03-06 01:55:07: pid 2680: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:55:42: pid 2712: DEBUG:  I am 2712 accept fd 6
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 1234 Minor: 5679 database
:  user:
2015-03-06 01:55:42: pid 2712: DEBUG:  selecting backend connection
2015-03-06 01:55:42: pid 2712: DETAIL:  SSLRequest from client
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  application_name: psql
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 3 Minor: 0 database: post
gres user: enterprisedb
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 0 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 1 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 0 length: 12
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 1 length: 8
2015-03-06 01:55:42: pid 2712: ERROR:  unable to read message length
2015-03-06 01:55:42: pid 2712: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

psql command:

[enterprisedb@testing_ppas93]$ psql -h localhost -p 9999 -d postgres
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request

I use pgpool II to control failover event between two servers (master on google cloud, slave on my local, centos x64, database: Postgres Plus Advanced Server 9.3). When I try to connect my database via port 9999 (example: psql -h localhost -p 9999 -d postgres) , pgpool raises the error

  ERROR: Unable to read message length

About errors above, because of the connection between pgpool and slave server, if I change slave's pg_hba.conf for pgpool host from md5 to trust, it work fine.

Two ways to fix:

ssh config among: pgpool, slave, master .
set up trust method in pg_hba.conf for pgpool .


What to do when facts change frequently in a star schema for data warehouse? I have a Quotation Management system that I built as a CRUD web app.

Over time, I realized I need to improve it so that management can have their reports.

Hence I studied the Kimball method of data warehousing.

I am now familiar with the concept of Dimensional and Fact tables.

On paper and pen, I have identified Quotation Line Item as the Fact table.

My issue is sometimes, the Sales Rep and the Customer have frequent back and forth regarding the Line Items so the line items will change quantities frequently or even removed.

I know that fact tables not supposed to have zero rows according to the Kimball book.

My question is how then do I handle such changes in the fact table?

According to a source, I am supposed to continuously update the fact table.

Please advise.

If I don't need to track history then just add/update/delete the fact records as the Quotation Lines change.

If I do need to track history then add effective start and end dates to the fact table.

See Timespan Accumulating Snapshot Fact Tables

Credit goes to Nick White in this thread.


Postgres is returning the “column reference '' is ambiguous” message inside a function even with aliases I have a function like this:

CREATE OR REPLACE FUNCTION test() RETURNS
TABLE(my_date date, gates smallint, area_type character(1),
      area_sizes float[], area_canvases float[]) AS $$
BEGIN
   return query
   select coalesce(s.my_date, r.my_date) as my_date,  coalesce(s.gates, r.gates) as gates, coalesce(s.area_type, r.area_type) as area_type, s.area_sizes, r.area_canvases
   from (select my_date, gates, area_type, area_sizes from table1 where area_type = 'X') s
   full join (select my_date, gates, area_type, area_canvases from table2 where area_type = 'Y') r
   on r.my_date = s.my_date;
END $$ language plpgsql;

When I run it with select * from test(); what I get is:

  ERROR:  column reference "my_date" is ambiguous
  LINE 4:        my_date,

The query if not wrapped inside the function works properly, but I cannot make it work inside it. Any idea?

The planner cannot understand which my_date you are mentioning there. Either the one in parameters or the one in the table1 (or table2). The correct syntax should be like the one below;

CREATE OR REPLACE FUNCTION test ()
RETURNS TABLE (
  my_date date, gates smallint, area_type character (1),
  area_sizes float[], area_canvases float[]
)
AS $$
BEGIN
    RETURN query
    SELECT
        coalesce(s.my_date, r.my_date) AS my_date,
        coalesce(s.gates, r.gates) AS gates,
        coalesce(s.area_type, r.area_type) AS area_type,
        s.area_sizes,
        r.area_canvases
    FROM (
        SELECT
            t1.my_date,
            t1.gates,
            t1.area_type,
            t1.area_sizes
        FROM
            table1 t1
        WHERE
            t1.area_type = 'X') s
    FULL JOIN (
        SELECT
            t2.my_date,
            t2.gates,
            t2.area_type,
            t2.area_canvases
        FROM
            table2 t2
        WHERE
            t2.area_type = 'Y') r ON r.my_date = s.my_date;
END
$$
LANGUAGE plpgsql;


Cannot remove unused filegroups I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use.

Some background:

We had some partioned tables that we converted back to non-partitioned ones
All partition-functions and -schemes were deleted
I queried the dm views for unused filegroups like this:

SELECT
*
FROM sys.filegroups fg
LEFT OUTER JOIN
sysfilegroups sfg
ON fg.name = sfg.groupname
LEFT OUTER JOIN
sysfiles f
ON sfg.groupid = f.groupid
LEFT OUTER JOIN
sys.indexes i
ON fg.data_space_id = i.data_space_id
WHERE i.object_id IS NULL

This got me a list of filegroups/files i tried to remove from the database.
But some of them could not be removed.
Example error message:

The filegroup 'FG_XXXX' cannot be removed because it is not empty.

I was not able to get the connection to tables/indexes for these filegroups via sys.data_spaces and sys.indexes:

SELECT * FROM  sys.data_spaces ds
INNER JOIN sys.indexes i
ON ds.data_space_id = i.data_space_id
WHERE ds.name = 'FG_XXXX'

I am aware that a filegroup can not be removed if it is referenced in some partition scheme.
But this cannot be the reason here, as i deleted all partition schemes/functions in the db.

Any hints what i could do to get rid of the filegroups?

You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

If that's indeed what's happening, the simplest way to move the LOB data is by recreating the table. You can move the data either with bcp out/in, or by inserting directly into a new table and renaming afterward (or by any other preferred method of moving the data you have). Make sure you specify the correct filegroups for row and text data when creating the new table.


How do you read MySQL InnoDB Monitor output? I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs.

The log says that "transaction 1 is rolled back". Why is this the case? The wager table has a foreign key relationship to the account table but is it "normal" for it (the trans running the insert) to not be able to obtain an S lock and therefore die as a result of a deadlock? I mean that seems pretty bad considering it is just a read to verify referential integrity.

And the second transaction is also writing to a wager table but there are no updates. So is the "mechanism" for creating auto-inc keys, or indexing mechanism or both that are causing a deadlock? Why would they not happen in serial fashion? Or may be they are but the wait period of some sort is too short.

Any help, pointers, further reading on how to use the log below to solve a deadlock problem appreciated.

111031 17:39:26
*** (1) TRANSACTION:
TRANSACTION 0 984899905, ACTIVE 180 sec, process no 10882, OS thread id 1104619856 inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3024, undo log entries 86
MySQL thread id 122, query id 28932942 localhost 127.0.0.1 bt update

INSERT INTO wager (amount_won, confirmation, created_by_partner, creation_date, description, fantasy_league,
     first_game_start, image_thumb, image_wide, kind, last_game_start, last_update_version, locked, name,
     num_future, num_past, num_present, partner, partner_wager_id, percent, progressive_num, result, status,
     sub_kind, teaser_type, user_account, wager_amount, win_amount)
 VALUES (null, 2, 1, '2011-10-31 17:39:26', 'Straight Bet', null, null, 'http://iphonebet_s.png',
     'http://iphonebet_l.png', 9, null, 0, 1, 'NFL: KC +4, -140', 0, 0, 0, 26, '39812820-1',
     null, null, 1, 1, null, null, 2176, '7000', '5000')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984899905 lock mode S locks rec but not gap waiting
Record lock, heap no 126

*** (2) TRANSACTION:
TRANSACTION 0 984900638, ACTIVE 61 sec, process no 10882, OS thread id 1076607312 setting auto-inc lock
mysql tables in use 1, locked 1
38 lock struct(s), heap size 6752, undo log entries 147
MySQL thread id 121, query id 28932947 localhost 127.0.0.1 bt update
INSERT INTO wager (amount_won, confirmation, created_by_partner, creation_date, description, fantasy_league,
     first_game_start, image_thumb, image_wide, kind, last_game_start, last_update_version, locked, name,
     num_future, num_past, num_present, partner, partner_wager_id, percent, progressive_num, result, status,
     sub_kind, teaser_type, user_account, wager_amount, win_amount)
 VALUES (null, 2, 1, '2011-10-31 17:39:26', 'Straight Bet', null, null, 'http://iphonebet_s.png',
     'http://iphonebet_l.png', 9, null, 0, 1, 'NFL: SD/KC o +7.5', 0, 0, 0, 26, '39812916-1',
     null, null, 1, 1, null, null, 2045, '260', '200')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984900638 lock_mode X locks rec but not gap
Record lock, heap no 72
Record lock, heap no 107
Record lock, heap no 126
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `bt/wager` trx id 0 984900638 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

Please look at this phrase from your question

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984899905 lock mode S locks rec but not gap waiting
Record lock, heap no 126

It says index PRIMARY has a shared lock (lock mode S) in Transaction 1

Now, check out this phrase from your question

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984900638 lock_mode X locks rec but not gap
Record lock, heap no 72
Record lock, heap no 107
Record lock, heap no 126

It says index PRIMARY has an exclusive lock (lock mode X) in Transaction 2. Three locks of this type are being held.

I have answered questions like this in the past, even as recent as 4 days ago

Trouble deciphering a deadlock in an innodb status log (Jun 6, 2011)
Will these two queries result in a deadlock if executed in sequence? (Jun 8, 2011)
Reasons for occasionally slow queries? (Jun 14, 2011)
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE? (Aug 8, 2011)
Concurrently update with limit mysql (Oct 28, 2011)

Here is the bottom line: The PRIMARY KEY, contained in the clustered index (internally called gen_clust_index), can experience intermittent locks when bulk inserting or performing multiple single INSERTs in a transaction. The application should be designed to perform a manual COMMIT after each INSERT. If this is not permissible due to the nature of the application, redesign the app to bulk insert into temp tables before populating the wager table.

As a sidenote, the reason these things do not run in serial fashion is due to the default transaction isolation as well as your app's design when it comes to INSERTs.

Here are the four(4) possible modes:

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ (default)
SERIALIZABLE

There are probably deeper explanations that the things I have already stated. If there is anyone out there with any further explanation or information I've missed, please chime in.


Wildcard for meta-command in postgresql I am searching if there is a wildcard assigned to the \ meta-commandsSee description - meta-commands in Postgresql.

for example..

postgresql=> \copy scheme.table FROM 'D:\path\to\%.csv' DELIMITER ';' CSV ENCODING 'LATIN1';

Make notice of the % in the example provided - A known declaration of wildcards could be _, %, . or *. But I do not seem to find documentation of wildcards in /meta-commands for Postresql.

First \COPY isn't postgresql. That's the client psql. From the docs,

  Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

No, psql does not provide filename completion. However there are other wrapper around COPY FROM STDIN that are not psql and they do. For instance pgloader provides FROM [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING

  The same clause can also be applied to several files with using the spelling FROM ALL FILENAMES MATCHING and a regular expression. The whole matching clause must follow the following rule:

And on top of that you can always use bash or whatever shell you're using to launch psql. Example with bash filename expansion.

for i in *.csv;
  do psql -c "\copy scheme.table FROM $$D:\path\to\$i.csv$$ DELIMITER ';' CSV ENCODING 'LATIN1';"
done;

Power-shell certainly also provides filename expansion. Google for it if you're on Windows 8+.


CREATE TABLE LIKE another table but with additional columns Is there a way to create a table that has the same structure as another table, but with additional columns?
From this question, I can copy a table's structure with:

CREATE TABLE new_table_name ( like old_table_name including all)

And then I can add columns one at a time, with ALTER TABLE new_table_name ADD COLUMN... but I am looking for a way to do this more succinctly, like:

CREATE TABLE new_table_name ( like old_table_name including all), new_col1 new_col1_type, new_col2 new_col2_type,...

You can use the syntax below;

CREATE TABLE old_table_name (
  id serial,
  my_data text,
  primary key (id)
);

CREATE TABLE new_table_name (
  like old_table_name including all,
  new_col1 integer,
  new_col2 text

);

The fiddle is here


How MySQL Queries and Questions status variables are calculated? I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions.

 show global status like 'Queries%';
| Queries                 | 28058738 |

mysql> show global status like 'Questions%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Questions     | 9458886 |
+---------------+---------+

The server is executing so many preparing statements and as per MySQL manual I got an assumption that Questions will contain Com_stmt_execute count. So I found my counts as,

mysql> show global status like 'Com_stmt_execute';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Com_stmt_execute | 9356066 |
+------------------+---------+

So what are the other counters which dominates to the total Questions status value?

Please see my Select and Query cache hits and I have very less write on this server.

mysql> show global status like '%Com_select%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 2720855 |
+---------------+---------+

mysql> show global status like 'Qcache_hits';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Qcache_hits   | 2316541 |
+---------------+---------+

Also the mysqladmin status show a reverse status if we consider the variable names.

$ mysqladmin status
 Questions: 28079556

I answered an old question like this Feb 25, 2014 : Questions since MySQL Startup

In that post, I described how everything MySQL executes to retrieve info is a question. I also mentioned how a billion questions is no big deal (See my ServerFault post 1 billion mysql queries in 24 days? Can something be wrong?)

Here is the difference

Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

You should go through the MySQL Documentation and read up on all Server Status Variables.


What is different bettween Cassandra NoSQL data model and SQL data model can any one tell. cassandra data model and sql data model
how we can define row and column with cassandra data model?

Cassandra is a Columnar Store NoSQL Database, Which goes beyond the Key Value pairs.

A Column family in cassandra is a container for rows, like table in any RDBMS.

And These are Stored and represented in Json Formats.

Below are some links where you can get more info:

http://wiki.apache.org/cassandra/DataModel

http://www.datastax.com/docs/0.8/ddl/index

http://www.javageneration.com/?p=70


VM'd a SQL server, having trouble with external HDDs First I am not a SQL DB. I inherited an old system and am trying to keep the wheels from falling off.
We have an MS SQL 2000 server (running server Win Server 2000) that we performed a P2V migration on. I did not realize that there are temp files running on an external USB HDD (that was connected to the old physical server) necessary for SQL operations.
Its looking like we can not connect the USB HDDs to the VM (due to age of the OS, and the HDD being a 3.0, doesnt look like VMWare supports 3.0 passthrough to Win Server2000).
So, is it possible to set those external HDDs up as a shared drive on another machine and run those temp SQL operations from the VM? As it is now the DB looks for a physical drive volume. We are considering swapping out the HDDs to USB 2.0, but this seems more time efficient. Thanks for any input

In general, you can put TempDB anywhere you want. It could be an existing drive on the VM (not great, but OK as a stopgap measure), or a new virtual drive, or a non-local drive (again, not a great idea).
There are a lot of references on the Internet for how to relocate TempDB. And for how to start SQL if the current TempDB is not accessible. Here are some good ones:

How to Move TempDB to Another Drive & Folder
Start SQL Server without tempdb

Also, run (don't walk) to your boss and let them know that both SQL 2000 and Windows 2000 are out of support, and have been for a long time. You are open to security vulnerabilities, performance risks, and reliability concerns. Do whatever you need to to make your boss realize that you can't provide good business value on unsupported software.


Finding earliest connected value over two columns I have a, perhaps slightly esoteric, use case.

Given a table like the following:

 id1 | id2 |      timestamp
-----+-----+---------------------
   3 |   4 | 2016-03-22 09:52:15
   1 |   2 | 2016-03-22 12:05:28
   2 |   3 | 2016-03-22 11:55:56
   5 |   6 | 2016-03-22 11:58:23
   6 |   7 | 2016-03-22 09:51:54

I would like to "trace" each id in id1 to its oldest id2 where connection can span over id1 and id2. For instance in the above dataset I would like the resultset to contain:

1 |   4 | 2016-03-22 09:52:15
5 |   7 | 2016-03-22 09:51:54

The results should be ordered by the timestamp column.
And ideally no other occurrences of "1" and "5".

I am using Amazon Redshift, which imposes some limitations.

So far I have dabbled with using window functions combined with a self join:

SELECT t1.id1,
FIRST_VALUE(t2.id2) OVER (
   PARTITION BY t1.id1, (t1.id1=t2.id1 or t1.id2=t2.id2)
   ORDER BY t2.timestamp ASC
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
FROM    test_decision t1
LEFT JOIN test_decision t2 ON t1.id1 = t2.id2

The general idea is to group based on logical expression (e.g. contains either id1 or id2), rather than partitioning only on rows with both. I've tried SELECT ... GROUP BY in a similar manner, but without getting the results I want.

I hope this use case makes sense to some of you.

In an effort to eliminate ambiguity: The rows are connected by having a value of id1 or id2  in either id1 or id2 columns of other rows. For instance, 1 leads to 4 through rows having (id1,id2) = (1,2),(2,3) and (3,4) in my example.

Regarding recursiveness: I think I can solve it by joining multiple times and coalescing the "outermost" value, my problem here is that the problem can be arbitrarily deep, e.g. I can solve this case by joining twice, but is there a way in SQL to get arbitrary recursion depth? And is that a good idea?

It's a recursive problem by nature. SQL introduced recursive CTEs for that purpose quite some time ago. Since Redshift does not seem to support this, your only remaining option to solve this within the database is a user defined function (UDF). It seems like the only server-side language supported by Redshift is plpythonu.

Here is an old answer with a PL/pgSQL function and an rCTE implementing the same solution side-by-side. But it's using the default procedural language of Postgres: PL/pgSQL:

Loop in function does not work as expected

No idea why Redshift chose not to support PL/pgSQL.


Delete unused index on report server I have a live server and a report server. The live server, where the application is running, has many indexes. When we create replication all the indexes are also created on the report server.

After 2 months, disk space on the report server is running out, and I have to make some space. I checked all the unused indexes on the report server and found many indexes that have not been used any time in the past two months. But they are required for the live server.

Can we delete those indexes that take unnecessary disk space on the report server as no application is pointing to report server? Will it be ok? I want to be sure before I make the changes.

Yes, you can drop the indexes on the subscriber if no one is using them.  Better yet, remove them from the publication article, or create a post snapshot script to drop whatever indexes you want.  Either way on next initialization, the indexes will not be created, or they will be dropped.


How to store Alternate Names for Places or City in Table What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching?
Examples:
In India user may search for "Chennai" or "Madras" and both are same City.
And "Sanjeeva Reddy Nagar" or "SR Nagar" are same Locality in Hyderabad City that user may search for.
Properties
-----------
PropertyId INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
PropertyName VARCHAR(64),
City VARCHAR(32),
Locality VARCHAR(32)

So in above structure how shall we accomodate alternate names for City and Locality?

Option-1:
May be having 2 more columns (AlternateCity, AlternateLocality) and have search function search in both the columns (Ex: if user search for Locality then it will search in Locality and AlternateLocality column)

Option-2:
May be having the same column store multiple data values in one column. Ex: City can store "Chennai (Madras)" and search can use Like operator?

What is the best way to store these data knowing one place may have 2 - 3 names.
Thanks.

The normalised way would be create a master table for town and then create another table for alternate names with a 1 to many relationship. This allows you to have 0..N alternate names. To fully normalise it you would pull all the names out from the town table and put them into the town names table and have a flag which denotes the default or primary name and locality for that town or you could go even further and have a name type column allowing you to further define the name type in a meaningful way.

This happens a lot with names and addresses for people. People have a primary (or legal name) and they also have one or more aliases or other known-as names and the technique of separating the name from the actual person and categorising their name via a type is commonly used - the same for their addresses


Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2 Background
I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of our databases over to a second virtual server, hoping to ease some of the load from the main SQL Server which hosts a Sage MAS 500 system.
Unfortunately, despite the virtual server having more resources than the old 2005 server, we have been experiencing performance problems, and I have been tasked with fixing these. I haven't made much progress, but today I noticed something strange:
Question
I've been checking dependencies for various tables and views, and noticed the time it takes to get results (especially when viewing objects which depend on the view or table in question) from 2008 R2 is much longer than it takes on the old 2005 server. Also, in some cases, different results are returned even though there has been no change in the tables/views since migrating. At the time I checked on the 2008 R2 server, there was virtually no activity so resources shouldn't have been a problem.
It seems to me this is symptomatic of our problems. Does anything come to mind why there would be such a drastic difference in View Dependency performance, or can anybody point me in a direction to investigate?

The 2008r2 "View Dependencies" functionality was completely rewritten from the 2005 version. This is because the 2005 version didn't work.

So it's not surprising that the results may be different (i.e., they should be correct now).  It is a little unexpected that the 2008r2 version might be slower at it, but not entirely surprising, as it is after all, using a completely different approach.

So, my guess would be that this phenomenon has little or nothing to do with your other problems.


Creating a database from a backup file I have a peculiar issue on my hands.  I have a few databases in my SSMS already (2014), and when creating a new database and performing a restore from a file that has never been used, it sets my main database to single-user mode.  During the restore I initially didn't realize it was moving the main DB to the new location.  I don't want this, but regardless of what I do it doesn't allow me to just restore this to the selected database.  I have been through the google search gauntlet, finding no results matching what I am experiencing.  I have also attempted to just create a new database by setting restore as my option, but the main database is selected.  Does anyone have a sure-fire way to get this issue resolved so I can add this other desired database to my system?

The RESTORE DATABASE command will create the database for you.

Don't create it first because then it will have to be deleted by the RESTORE command and a type if safety check in there might not allow the restore unless you specify the REPLACE option.

Also, is the database in that backup was read only when the backup was produced, then it will be read only when restore. Just turn off that database option after you done the restore.


Postgres revoke database access from user When I tried

REVOKE ALL PRIVILEGES ON DATABASE postgres from admin;

but the user admin is still able to connect to postgres remotely via pgadmin after that.

How do I completely revoke an user's access to a database?

Log: https://pastebin.com/raw/Q2mfkf3P

The right to connect is by default granted to PUBLIC.  You would have to revoke it from PUBLIC (and then grant it individually to other users who need it) to accomplish what you want.

Or use pg_hba.conf to deny the connection.


What are definer and invoker rights in database, Oracle and MySQL? What are definer and invoker rights in database, Oracle and MySQL? I just don't get it.

Let's take a simple example:
You have this procedure using "definer rights" - which is the default in Oracle.
CREATE PROCEDURE DEL_EMP AS
BEGIN
   DELETE FROM EMP;
END;

Another user who calls this procedure only needs EXECUTE privilege for this procedure, it is not required that such user has DELETE privilege on table EMP.
Procedure runs under permission of the procedure owner (or user who defined it, thus it is called "definer" rights).
"Inovker Rights" is the opposite. A user who likes to runs this procedure successfully must have EXECUTE privilege for this procedure and DELETE privilege for table EMP.
There are some more points regarding definer and invoker rights but for a general understanding this should be enough.


Should DB_RECOVERY_FILE_DEST_SIZE be smaller than the respective partition size? Is it OK to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to a drive partition that is dedicated for this purpose?

That is, if I use a partition of 10GB, is it ok to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to 10GB?

Or is there a chance that errors will occur due to insufficient space in the drive, and I should use a smaller value?

Edit (more details):

My setup has the following parameters:

db_recovery_file_dest            string  /oracle/fra
db_recovery_file_dest_size       big integer 10G

10 GB as 10240 MiB?

10 GB as 10000 MB?

Is it 10 GB because of the rounding, but the actual size is 10227 MiB? Or 10213 MiB?

Setting db_recovery_file_dest_size to 10G means the size of FRA will be 10*1024*1024*1024 = 10737418240 bytes. As long as that is less than or equal to the available space in the filesystem (not partition!), it should be fine.

I usually set db_recovery_file_dest_size a bit smaller than the amount of free space in the filesystem or ASM diskgroup if I get a dedicated filesystem or diskgroup for it.


Computed Column Operand type clash on Date Column When Checking For Null This error is generated when trying to create a table with a computed column from a Date field.

  Operand type clash: int is incompatible with date

If I comment out the computed column it works.

[DateInActive][Date] NULL,             -- Date origination
[IsValid] AS (IsNull(DateInActive, 1)) -- Failure here

The thought on IsValid is to simply return a bit of on|off for this field to signify that no in-active date has been reported and if so, it is valid.

The column will ultimately be passed on to JSON in a For JSON output as a is-a field for usage outside SQL.

Full SQL

CREATE TABLE [history].[PhoneBook](
    [PhoneBookId] [int] IDENTITY(1,1) NOT NULL,
    [PersonId] [int] NOT NULL,
    [PhoneId] [int] NOT NULL,
    [DateActive][Date] NOT NULL,
    [DateInActive][Date] NULL,
    [IsValid] AS (IsNull(DateInActive, 1))
 CONSTRAINT [PK_PhoneBook_1] PRIMARY KEY CLUSTERED
(
    [PersonId] ASC,
    [PhoneId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

According to the ISNULL documentation the returned data type is the same type as the check expression, which is data type date in this case. The ISNULL expression fails because the integer literal 1 cannot be implicitly converted to date.

To return a bit indicating whether or not the date column is NULL, you could instead use a CASE expression:

CREATE TABLE [history].[PhoneBook](
    [PhoneBookId] [int] IDENTITY(1,1) NOT NULL,
    [PersonId] [int] NOT NULL,
    [PhoneId] [int] NOT NULL,
    [DateActive][Date] NOT NULL,
    [DateInActive][Date] NULL,
    [IsValid] AS CASE WHEN DateInActive IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS bit) END
 CONSTRAINT [PK_PhoneBook_1] PRIMARY KEY CLUSTERED
(
    [PersonId] ASC,
    [PhoneId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Restoring a backup to an older version of SQL Server When trying to restore a backup to a SQL Server Express 2008 database I got the following error:

Restore failed for Server '...\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The database was backed up on a server running version
10.50.1600.
That version is incompatible with this server, which is running version 10.00.2531.
Either restore the database on a server that supports the backup, or use a backup
that is compatible with this server. (Microsoft.SqlServer.Smo)

Is there a way to get a backup which is compatible with the older (in my case 10.00.2531) version from the newer (in my case 10.50.1600) version of SQL Server Express?

You'd have use the Import/Export wizards in SSMS to migrate everything

There is no "downgrade" possible using backup/restore or detach/attach


How to reduce the size of ldf file? We take full backups of the DB every day (in full recovery mode) and keep them for 10 days. Because of our requirements, we don't want to take incremental or differential backups of the DBs.

Now we are observing that the ldf file size is growing too large. The question, is there any way to handle this scenario?

We don't want the ldf file to increase in size but at the same time we don't want to lose full backups.

Please then modify your database to be in 'SIMPLE' recovery mode instead of 'FULL' or 'BULK LOGGED'.  The full recovery mode never truncates the transaction log, unless you do a transaction log backup.  Only after it's backed up will SQL Server reuse the empty space in the transaction log.

Now setting the DB to simple mode, taking a full backup, and running a checkpoint is easy.  Managing the transaction log itself might take some more work.  You will want to ensure that you don't have too many VLFs (Virtual Log Files), which if you have the default setting of 1 mb growth you most likely do.

Here are the things to do:

 ALTER DATABASE DBNAME SET RECOVERY SIMPLE WITH NO_WAIT GO

Then take a full backup.  Do a checkpoint with a

USE DBNAME
CHECKPOINT;

Now let's see how many VLFs your transaction log has:

USE DBNAME
dbcc loginfo();

The number of rows returned are the number of VLFs.  Assuming the status is 0 on the last VLFs you can shrink and reduce the number to a reasonable one.  How big should your transaction log be you ask?  If you don't know the work load make a educated guess and wait a week, reindex, and see how big it is.  It should probably be about that size.

To shrink the log file you can use the GUI and script it out.  Here is a youtube video on how to use SQL Server Management Studio (SSMS) to use the GUI to shrink:
https://www.youtube.com/watch?v=xcT4DIMG-tI

Once you shrink it run DBCC LOGINFO(); again to see how many VLFs you have.  It should be signifigantly lower.  If it isn't, your final VLF is probably status: active (not 0).  If that's the case you will want to wait for the transactions to finish where it is no longer active, perhaps checkpoint just in case, and run your shrink again.

Now you should be down to a low number of files and a small size.  You don't want more than like 100 vLFs, 50 is the approx unofficial number a lot of people use to keep it around.  Expand your transaction log a few chunks at a time so you have more than 10 but less than 60 VLFs.  In a week, review your transaction log VLFs and size to see what it should actually be set to moving fwd and monitor it.

Notes:
-Please don't use shrink DB or shrink logs unless it's a very specific situation like this, under most conditions this is really bad practice.

-Read up on VLFs and log file maintenance from the best.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/


Stop seeding process in the AG In my scenario I have 2 databases I would like to add into my AG. I have initiated it via automatic seeding, but then deleted one DB from the AG pool. As a result I expected it will stop the seeding of that DB into the replica and just start seeding the second one. Unfortunately (for me) seeding process of the first one is still running. Therefore my question is - how can I stop it?
My test instance is SQL Server 2017 Enterprise CU22.

You can stop automatic seeding by setting seeding mode for that secondary replica to "manual."  This will stop seeding for all databases.
See this docs article for the details: Use automatic seeding to initialize an Always On availability group - Stop automatic seeding

To stop automatic seeding for an availability group, run the following script on the primary replica:
ALTER AVAILABILITY GROUP [<availability_group_name>]
MODIFY REPLICA ON '<secondary_node>'
WITH (SEEDING_MODE = MANUAL)

Once seeding has been stopped, double check that the "problem" database has been removed from the AG (and is removed from the secondary), and then restart seeding:

You can set automatic seeding on an existing database. The following command changes an availability group to use automatic seeding. Run the following command on the primary replica.
ALTER AVAILABILITY GROUP [<availability_group_name>]
MODIFY REPLICA ON '<secondary_node>'
WITH (SEEDING_MODE = AUTOMATIC)

From your question, I'm not sure if you deleted the problematic database from the primary server, or if you just removed it from the AG.


How to calculate avg of some column in join? i have three tables

teacher

id name
1  ali
2  reza

course

id title
1  math
2  litrature

gradetbl

id courseid teacherid grade
1  1        1         20
2  2        1         10
3  1        2         17

the result:

teacherid avg(grade)
1         15
2         17

i try something but it did not work

All you really need to do is to SELECT from the gradetbl, apply the AVG aggregate function over the grade and then GROUP BY the teacherid like so:

SELECT teacherid, AVG(grade)
  FROM gradetbl
 GROUP BY teacherid;

For the results you have indicated there should be no need to use a JOIN at all. You will only need to use a JOIN when you want to fetch data associated with the teacherid:

SELECT t.name, AVG(g.grade)
  FROM gradetbl AS g
  JOIN teacher as t
    ON t.id = g.teacherid
 GROUP BY t.name;

You should view the documentation for SELECT, GROUP BY and AVG for thorough examples and explanations of common usage.


Database design for big table I'm handling a big table which is at 200GB+ and has around 1 billion rows.

I'm looking into splitting the table into smaller tables and then joining the results whenever I query.

We do mainly reading on that table with occasional writing.

It it a good idea? Or, is it something I need not worry?

My database server has 4CPU and 32GB RAM.

I have the primary key as index to save space. Currently, there are no issues. However, I just considering whether to split it earlier or just keep it this way. What are the pros and cons of splitting into smaller tables with , let's say, 250 million rows each instead of plain 1 billion rows table? Do you think my 32GB RAM will be able to handle it?

There are a couple of patterns for splitting a single logical entity across several physical tables.

Vertical partitioning puts some of the columns in one table and some in another. There can be several such additional tables, if required. All these tables share the same primary key. Columns that are used together are stored together so one page fetch read all the required values. The advantage is there are more rows per page so scans and aggregates require fewer IOs. Sometimes it can be tricky deciding which columns should go together. Every INSERT becomes proportionately more expensive as the number of partitions increases.

Horizontal partitioning splits the data by key range. For example, all users with a surname beginning A through M go into table User_1 and N through Z go into User_2. The application figures out which sub-table to use at run-time, usually algorithmically or through a look-up, though most DBMS these days will offer this as a build-in feature, implemented through DDL, transparent to the application. If you have hot spots in data writes this can spread the pain out. The optimiser can eliminate whole partitions from range scans, improving response time. Loading and removing whole partitions can be very fast metadata operations.

Sharding is where key ranges are moved, not just to a different table, but to a whole different instance of the DBMS. The application decides which instance to connect to, depending on key range. This is a scale-out technique. Some DBMS support this as a feature, such as Galera for MariaDB. The obvious cost is in additional hardware to run the other instances, duplication of reference data across all nodes to maintain RI and application complexity.

The techniques may be combined, of course, so a table could be both horizontally and vertically partitioned.

General advantages of splitting:

Indexes may becomes shallower, saving costs on index look-ups.
Each partition can write to separate disk, spreading IO load.

Disadvantages include:

Application complexity
It will be more difficult to ensure data integrity
Upgrades become trickier.
Purging data is incrementally more difficult to get right.

Basically, if you don't have a problem, and don't see one coming, don't bother. Horizontal partitioning using DBMS functionality is the only one worth considering as a pre-emptive measure. Even then it will require a re-write of 200GB. Do you have the disk and service window to accomplish this?


Different execution plans during INSERT at @table variable and #temporary table I faced with strange situation: inserting of ~800 000 rows into table variable is much more faster than inserting (with the same select) into temporary table. INSERT is under stored procedure. SELECT have parameters from stored procedure.

Sample code below:

create procedure p1
    @Param1 int
AS
    declare @t table (id int)

    insert into @t
        select id
        from dbo.SomeTable
        where SomeField = @Param1

And if I change @t to #t - than I have more than 2.5 difference in procedure's execution time.

All the time as DBA I thought that so big row count should be processed in temporary tables... But now, I don't think so :)

Anyway, I'm just trying to understand what is going on and why the same SELECT have different execution plans during INSERT.

The same case (as I thought) was here: INSERT performance difference between temporary tables and table variable. But I have the opposite situation - INSERT into # is SLOWER than @

So, do you have any ideas?

what is the better in this case: use @ or #?
why do I get different execution plans ?

Execution plan here: https://drive.google.com/file/d/0B1iqgEJ5a7vfcjAwMmtJaVFrNWM/view?usp=sharing

Thank you in advance!

P.S. Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

P.P.S. I've found really good article about temporary tables and table variables here: What's the difference between a temp table and table variable in SQL Server?. But, unfortunately, it didn't help me.

The first thing that I noticed is that the query plan compilation time was over 3 seconds for each query. Wow, this is a really complex query!

Because the solution space of potential execution plans is so large (it grows exponentially with the number of the number of objects involved in the query), SQL Server is only going to be able to explore a tiny fraction of the potential query plans when coming up with a plan for these queries.  Remember that SQL Server's job isn't to create the best query plan possible, but instead to create a query plan that is good enough and to do so as quickly as possible.

I have often found that small changes in the way a query is formulated, even if they don't impact the logic of the query, can have a significant impact on the query plan.  Anecdotally, this grows more and more likely as the query grows more and more complex.  One possible reason that this could happen is that a tweak to the query might cause SQL Server to begin cost-based optimization with a different initial plan.  As cost-based optimization proceeds, this different starting point could yield a different exploration of the space of potential query plans--kind of like a different random seed impacts random number generation.  Note that the query plans you provided are significantly different (compare images of the plan shape below!) and SQL Server actually does estimate that the @table variable plan is slightly cheaper.

In terms of why the table variable vs. temp table would have such an impact on cost-based optimization, I'll hazard an only-partially-educated guess: inserting into a table variable forces a serial plan (see the NonParallelPlanReason of CouldNotGenerateValidParallelPlan that appears in the table variable plan, but not the temp table plan), and this may impact the code path that the query optimizer takes either generating an initial plan or in some phase of plan optimization.

If possible, the next step that I would try is to simplify the query so that fewer tables are used and/or the query is split into multiple queries (with intermediate #temp tables) so that each query is simpler and has better statistics available.  If that's not possible, you could also try more hacky options such as using query hints (e.g., force MAXDOP 1 on the temp table query, and see if the plan comes out more like the table variable query).

Query plan with #temp table:

Query plan with @table variable:

And finally, if you are interested in going a little bit deeper into how the query optimizer works, I have found Paul White's blog to be a great resource!


Performace of SELECT query based on primary key vs. indexed column I have a scenario where a row in a table can be obtained by using either primary key or another column which is indexed:
SELECT * FROM table_name WHERE primary_key = ?

vs.
SELECT * FROM table_name WHERE another_column = ?

another_column has unique values and has index created on it.
I am using Postgres, and on doing explain analyze on both SELECTs, they resulted in almost same execution time.
So, my question is, generally, is the performance for both the queries same or is one query better than the other? And why?

In PostgreSQL there is no difference between a primary key index and other indexes, so that does not surprise me.
The only slight difference could be caused by the size of the indexed data (wider values cause deeper indexes, so more blocks have to be traversed) or the speed of the comparison function. In most real world cases, the difference will be hard to measure.


SQL Server 2012 Table Partitioning - Have I done it correctly? I have a table that stores accounting transactions for what we call "jobs".

The table looks like this:

(
REGION char(3)
JobNumber char(17)
SeqNum decimal(5,0)
.....
.....
.....
AcctYear decimal(4,0)
)

Unpartitioned, the PK and clustered index is Region, JobNumber, SeqNum.

What we want to do is partition the table by year (AcctYear column), and have each partition located on a separate filegroup. The current and previous year will reside on an SSD and previous years beyond that will reside on cheaper disk storage. Each year has approx. 40 million rows.

I have already partitioned the table, but I am questioning whether I have done it correctly. Let me explain why...

What I have done is added the AcctYear to the end of the PK/Clustered Index (i.e made the PK/CI Region, JobNumber, SeqNum, AcctYear), created the function and schema etc. I have setup 30 partitions, one for every year from 2001 till 2030.

The reason I chose this method of adding the AcctYear to the end of the PK/CI was that I don't always expect the AcctYear to be specified in queries/joins, and if I put the AcctYear at the beginning of the Clustered Index, I would have to either always specify the AcctYear in every query and join, or create another secondary index only on the existing primary key columns.

What makes me doubt what I have done is that when I query for a specific job number AND AcctYear, the Actual Execution Plan shows that it is still looking through the 31 partitions, instead of knowing which partition that data is in. The SHOW STATISTICS IO, also says 31 seeks are being done.

So, have I done this partitioning correctly based on what I want to achieve? or Is there a better way?

Thanks for any help in advance!

Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble.

First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't specify it.

Second, there are several issue with the optimiser and partitioning that prevents elimination even when it is theoretically possible to achieve it (like in your  example). In SQL Server, you can think of a partition as a hidden index column. In your case, if you were to index in such a way that job number was leading the index, your query with both AcctYear and job number might do only a single seek.

If you add the most common queries you expect to your question, it would be possible to advise you on the best partitioning strategy that achieves your goal.


SQL Subquery , One to Multiple rows Following query I have written returns the result as expected, example 121350 rows.

SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id

However,WHEN i TRY TO ADD a subquery following
SELECT statement the resulted ROWS increases TO 155550 row due TO subquery returning one TO many ROWS.

(
               SELECT CASE
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG

Ordering by OrderId.

How can I solve this so that my consolidation of first query does not break due to subquery?

Current result I am getting with subquery...

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    0.639           21   0
125                L                121212                    0.639           21   0

expected result with consolidation.

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    1. 278         42   0

You will have to include DG in your grouping...

Select Consignment_id
       ,Orders_boxsize
       ,Orders_boxweight
       ,Orders_boxlength
       ,Orders_boxwidth
       ,Orders_boxdepth
       ,Order_dispatchment_id
       ,SUM(Component_weight) as Component_weight
       ,SUM(_ic) as _ic
       ,DG
from
(
SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
,(
               SELECT CASE
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id
) a
Group by
Consignment_id
,Orders_boxsize
,Orders_boxweight
,Orders_boxlength
,Orders_boxwidth
,Orders_boxdepth
,Order_dispatchment_id
,DG


How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option --single-transaction. For production, I am looking for way where I can upgrade mysql prod DB to 5.6 with minimal downtime.

We are using a different server, but the problem is how I can manage ongoing transaction on production DB, because while taking mysqldump and restoring to new server their will be many transactions performed on my existing production server. How can I sync this prod-DB updated transactions with new 5.6 mysql-server ?

I am thinking if we can run mysqldump with --single-transaction --flush-logs and after  restored this dump, restore remaining  transaction by  applying  new binary  logs that  created after mysqldump.

I am also planning to enable GTID replication with our new 5.6 mysql production.

We used replication (no GTID) to replicate from our production 5.5 servers to our new 5.6 servers so to minimize downtime. Basically, set up your new 5.6 server to replicate from the 5.5 (cannot use GTID at this time). Dump 5.5 and restore to 5.6, start the replication.

When you move production to the 5.6 server, stop the replication from the 5.5 server . Reconfigure 5.6 server to use GTID (need to issue RESET MASTER and RESET SLAVE on the 5.6). Then configure your downstream 5.6 slaves.


Why does minimal logging appear to result in a smaller table size? I'm testing using the publicly available AdventureworksDW2016CTP3 database.

If I insert the first 100k rows from FactResellerSalesXL_PageCompressed into a page compressed heap without minimal logging I get a table size of 12552 KB. Doing a rebuild of the table brings down the size to 6024 KB. If I do the same insert with minimal logging I get a table size of 5832 KB. Why is the initial table size over 50% smaller when minimal logging is used?

Below is code to reproduce the issue. I'm using SQL Server 2016 SP1:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3  (Build 14393: )

No trace flags are enabled.

The actual execution plans are effectively the same. I've verified that minimal logging occurs for the second insert but not the first. Changing the order of inserts does not make a difference. Clearing the buffer cache and plan cache also does not make a difference.

Table creation script:

-- create page compressed heap copy of FactResellerSalesXL_PageCompressed
SELECT * INTO FactResellerSalesXL_PageCompressed_Heap
FROM FactResellerSalesXL_PageCompressed
WHERE 1 = 0;
ALTER TABLE FactResellerSalesXL_PageCompressed_Heap REBUILD WITH (DATA_COMPRESSION = PAGE);

Code for the first insert:

-- insert 100k rows without minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;

Result:

╔═════════════════════════════════════════╦════════╦══════════╦══════════╦════════════╦════════╗
║                  name                   ║  rows  ║ reserved ║   data   ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬══════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 12808 KB ║ 12496 KB ║ 8 KB       ║ 304 KB ║
╚═════════════════════════════════════════╩════════╩══════════╩══════════╩════════════╩════════╝

Code for the second insert:

TRUNCATE TABLE FactResellerSalesXL_PageCompressed_Heap;

-- insert 100k rows with minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap WITH (TABLOCK)
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;

Result:

╔═════════════════════════════════════════╦════════╦══════════╦═════════╦════════════╦════════╗
║                  name                   ║  rows  ║ reserved ║  data   ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬═════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 5832 KB  ║ 5776 KB ║ 8 KB       ║ 48 KB  ║
╚═════════════════════════════════════════╩════════╩══════════╩═════════╩════════════╩════════╝

When I initially looked over your question, I thought that perhaps the second insert was benefitting from the first insert. Now that I look more closely, my initial though was that, due to the exclusive table lock, you were getting much denser pages than without the lock. This is why the rebuild on the heap created without the lock gets you down to almost identical table size.

To test my theory, I did things slightly differently. I generated two separate tables in advance, and then ran each of your queries so that I created two tables that both existed at the same time (this eliminates quasi-related things, like page reuse from the truncate you performed). This way I could compare them directly.

SELECT * INTO dbo.Heap_Tablock
FROM FactResellerSalesXL_PageCompressed WHERE 1 = 0;
ALTER TABLE dbo.Heap_Tablock REBUILD WITH (DATA_COMPRESSION = PAGE);

SELECT * INTO dbo.Heap_NoTablock
FROM dbo.FactResellerSalesXL_PageCompressed WHERE 1 = 0;
ALTER TABLE dbo.Heap_NoTablock REBUILD WITH (DATA_COMPRESSION = PAGE);

My first observation was that the population without tablock, even if run second (so benefitting from data already in cache), execution time was about 4X (only about 2X with clean BP each time). This is partly due to minimal logging, of course. But all of it? Not sure yet.

-- 3 seconds; 9 seconds after DROPCLEANBUFFERS
INSERT INTO dbo.Heap_Tablock  WITH (TABLOCK)
SELECT TOP 100000 * FROM dbo.FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- 13 seconds; 17 seconds after DROPCLEANBUFFERS
INSERT INTO dbo.Heap_NoTablock
SELECT TOP 100000 * FROM dbo.FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

Rather than look at sp_spaceused, which is rather high level and doesn't answer any questions, I looked at sys.dm_db_database_page_allocations instead. First, I just looked at the counts:

SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_Tablock'), 0, NULL, 'LIMITED');

-- 737

SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_NoTablock'), 0, NULL, 'LIMITED');

-- 1,569

So, without tablock, a lot more pages are associated with the table. But are they all allocated? Are the pages in the smaller table denser, contain ghost records, more likely to be compressed? Inquiring minds want to know!

SELECT is_allocated, is_page_compressed, has_ghost_records, COUNT(*),
  AVG(1.0*page_free_space_percent)
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_Tablock'), 0, NULL, 'DETAILED')
WHERE page_type = 1
GROUP BY page_type, is_allocated, is_page_compressed, has_ghost_records;

-- is_allocated  is_page_compressed  has_ghost_records  #     %
-- ------------  ------------------  -----------------  ----  ----------
--            0                   2                  0     2   50.000000
--            1                   1                  0   721  100.000000

SELECT is_allocated, is_page_compressed, has_ghost_records, COUNT(*),
  AVG(1.0*page_free_space_percent)
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID(N'dbo.Heap_NoTablock'), 0, NULL, 'DETAILED')
WHERE page_type = 1
GROUP BY page_type, is_allocated, is_page_compressed, has_ghost_records;

-- is_allocated  is_page_compressed  has_ghost_records  #     %
-- ------------  ------------------  -----------------  ----  ----------
--            1                   0                  0  1562   99.967989
---- *****************************^  DANGER!

I think this is an additional, unadvertised benefit of the exclusive lock (and it also explains the faster execution time: less I/O).

Again, just a theory based on zero source code access and a reluctance to fire up a debugger, but it seems like populating a heap, without the exclusive lock, does not page compress the data initially - it waits for the rebuild.

This is somewhat confirmed in Data Compression: Strategy, Capacity Planning and Best Practices, under "Manipulating Compressed Data", in the heap section of the table:

  The newly inserted row is page-compressed:
   - if new row goes to an existing page with page compression
   - if the new row is inserted through BULK INSERT with TABLOCK
   - if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT ... FROM
  Otherwise, the row is row-compressed.*

  * The resulting row-compressed pages can be page-compressed by running a heap rebuild with page compression.

That is not a verbatim confirmation of what we're seeing, but it seems to jive, since you are inserting rows into new pages that can't already have been page compressed, and without the TABLOCK hint, you don't meet any of the criteria for page compression at insert time. (And without deeply investigating the data, there may not be much benefit to row compression alone, which is common for certain data patterns.) So, you don't see the page compression savings until you rebuild.

Just another argument for clustered indexes, IMHO.


Spiky COMMIT results in performance slowdowns with Postgres 8.4 We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our web app. The time seems to taken up largely in "Postgres commit":

We're self-hosting Postgres 8.4.3 on Ubuntu 10.04, with essentially stock settings, on an Amazon EC2 c1.xlarge instance w/ EBS. Yes, I know that Postgres on EBS probably isn't the best setup. We plan to move to a newer Postgres DB on RDS later this year.

In the meantime, is there anything obvious that I should look at that might tame this spiky pattern?

It's hard to tell what exactly your graphs say.  But commit is designed to be slow.  By default, Postgres waits for changes to be written to disk before it lets commit return.

You can let commit complete before changes are written to disk with this option:

synchronous_commit = off

That should certainly help to spread the disk I/O required for commit over more time.  The downside is that a server outage can cause completed transactions to be lost.


Diagnosing high write throughput on RDS Postgres I'm using Postgres 9.6.1 on RDS, and seeing orders-of-magnitude higher write throughput than I expect to see.  I'm seeing a WriteThroughput around 40,000,000 Bytes/Second.  I've looked at the following to try to track down where it is coming from:

I installed pg_stat_statements and have been periodically running:

SELECT sum(shared_blks_dirtied) from pg_stat_statements

And it looks like my queries are only dirtying at most about 30 blocks per second.  A block is 8kB, right?  So that's only 240 kB per second.  (I also checked temp blocks and local blocks and I'm barely dirtying them or writing them at all.  I also confirmed that I'm not hitting the limit of unique statements that pg_stat_statements will track).

I'm also looking at my logs, and I see checkpoints running every 5 minutes about this size:

LOG:  checkpoint complete: wrote 8538 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=269.825 s, sync=0.021 s, total=269.921 s; sync files=2349, longest=0.010 s, average=0.000 s; distance=39599 kB, estimate=39599 kB

So that's approximately 40 MB / 5 minutes = 133 kB per second, which is the same order of magnitude I'm seeing in pg_stat_statements.

So I'm a little confused... am I doing the math wrong, or is there somewhere else I should be looking to see what's generating all that write throughput?

One other thing to mention: I have a lot of tables and schemas: approximately 25,000 schemas and 2 million total relations. (And I create / drop new schemas a couple times a minute generally). I've found that autovacuum can't keep up with this, even with very aggressive configuration, so I am manually vacuuming and analyzing tables based on my write patterns, and I've adjusted the autovacuum thresholds upwards so that in practice my manual vacuuming almost always gets there before autovacuum runs on it.  I've confirmed that my manual vacuums do show up in pg_stat-statements, so I'd assume that any writes they generate are already accounted for in my above analysis, but maybe not?

It turns out that the vast majority of my write throughput was coming from the stats collector.  Because of the very high number of relations in my database, the stats data is unusually large.  I was able to diagnose the problem by temporarily clearing the stats:

SELECT pg_stat_reset()

Which led to an immediate, dramatic drop in my write throughput.  To solve the issue going forward, I've redirected my stats directory to a ram disk instead of to the hard drive, using this setting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.RamDisk

The end result is that CloudWatch is now reporting around ~400 KB/s of throughput instead of 40 MB/s.


DROP PROCEDURE IF EXISTS not included in mysqldump I'm dumping my stored procedures only using the following command:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql

but the resulting dump file doesn't include a DROP PROCEDURE IF EXISTS before each procedure declaration.

How to add the drop query to my dump?

Thank you.

I dumped my stored procedures with the following

C:\>mysqldump -u... -p... -n -d --routines --triggers --all-databases > Z:\stuff.sql

Here is one the procedures with the DROP PROCEDURE included:

--
-- Dumping routines for database 'lovesh'
--
/*!50003 DROP PROCEDURE IF EXISTS `LoadMyData` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = cp850 */ ;
/*!50003 SET character_set_results = cp850 */ ;
/*!50003 SET collation_connection  = cp850_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`lwdba`@`127.0.0.1`*/ /*!50003 PROCEDURE `LoadMyData`()
    DETERMINISTIC
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

When you used --skip-opt, that is what caused it because --opt includes --create-options and DROP PROCEDURE is considered MySQL-specific.

  -a, --create-options
                      Include all MySQL specific create options.
                      (Defaults to on; use --skip-create-options to disable.)
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.

--skip-opt would undo --create-options and thus remove DROP PROCEDURE.

Mystery solved !!!

Answer to Bonus Question

Those are not comments; those are MySQL directives. Whenever MySQL runs a Command, it looks for these directives encased in Comments

The number 50003 indicates that this command will execute if and only if the version of MySQL is 5.0.3 or greater.

Here is another example from a mysqldump:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `datas` (
  `ID` int(2) DEFAULT NULL,
  `CATEGORY` int(2) DEFAULT NULL,
  `ORD` int(1) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;

The number 40101 indicates that this command will execute if and only if the version of MySQL is 4.1.1 or greater.

These SQL directives are there for your protection if you load these mysqldumps into earlier versions. These directives allow full acceptance of certain commands. Please do not remove them.

However, if you only work with MySQL 5.0+ and plan to dump your stored procedures separately, you can strip them using Perl or awk. Personally I would leave them be.


Why isn't my count aggregate respecting my pivot columns? In the pivot query below, why is every cell in a given row identical?

I was able to fix the query by replacing count(voo) with sum(voo), but I don't understand why count(voo) does not respect the AppVersion column.  I tried to follow Microsoft's Complex PIVOT Example, which also used Count.

select pv.EventDate, [VersionAA],[VersionBB],[VersionCC],[VersionDD]
from
(
    select ah.EventDate,ah.AppVersion,1 voo
    from AppHistory ah
    where EventDate > '2018-02-04' and reason = 1
) source
PIVOT(
    count(voo) --Fix: Replace "count(voo)" with "sum(voo)"
    For AppVersion in ([VersionAA],[VersionBB],[VersionCC],[VersionDD])
) as pv
order by EventDate

Example output with count(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |      1315 |      1315 |      1315 |      1315 |
| 2018-02-06 |      1200 |      1200 |      1200 |      1200 |
+------------+-----------+-----------+-----------+-----------+

Corrected output with sum(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |        12 |        18 |       300 |         3 |
| 2018-02-06 |        22 |        30 |       310 |         5 |
+------------+-----------+-----------+-----------+-----------+

My intent, which was met by sum(voo), was to to create a query which returned:
1) One row per day
2) One column per AppVersion (happily, the list of AppVersions is static)
3) Each cell contains a count of how many rows within AppHistory match the date and AppVersion of the column.

For reference AppHistory looks something like this:

CREATE TABLE [dbo].[AppHistory](
    [UserID] [bigint] NOT NULL,
    [AppVersion] [varchar](100) NOT NULL,
    [Reason] [int] NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [TotalActions] [int] NOT NULL,
    [created] [datetime] NOT NULL
)

EDIT

I tried to reproduce the problem using a small amount of data and failed.  In fact, the behavior goes away if I run select * into #AppHistory from AppHistory where EventDate > '2018-02-04' and reason = 1 and then replace AppHistory with #AppHistory in my queries.  So now I'm even more confused.  CheckDB says the database is fine.

I honestly didn't think of this until now, but AppHistory is a bit crazy:
A) Has almost 1 billion rows
B) Has a partition scheme
C) Has a clustered columnstore index.

That said, creating a new table with the same partition scheme and columnstore index also failed to reproduce this behavior.  So, now I'm even more mystified.

Query Plans:
(Bad Result)Plan using Count
(Good Result)Plan using Sum

Looks to me like a bug when producing the query plan when you have batch mode execution.

Here is a repro that shows the issue both in SQL Server 2016 (13.0.4001.0) and SQL Server 2017 (14.0.3015.40).

create table dbo.CS(CS bit not null, index IX_CS clustered columnstore);
create table dbo.X(C int not null);

go

insert into dbo.X(C)
select top(10000) row_number() over(order by (select null)) / 10
from sys.columns as c1, sys.columns as c2;

go

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0;

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0
option (querytraceon 9453);

Result:

1           2
----------- -----------
10000       10000

1           2
----------- -----------
10          10

Left join on dbo.CS here makes the query use batch mode and the trace flag 9453 disables batch mode.


Not all columns show up on select * on DB2 for IBM i My client has a table on their IBMi with a foreign key to another table. It's not showing up with a SELECT * FROM lib.table. However, if I specifically mention it in the column list. It does show up.

SELECT
        col1, col2.col3, fxcolid
    FROM  lib.table

I'm using the jt400 driver to query from SQLWorkbench/J.

There is an IMPLICITLY HIDDEN column attribute that can be applied. Please check for this...

This means the column will only appear when it is explicitly specified on the SELECT statement.

Check the CREATE TABLE statement for this property, or materialize the TABLE's DDL using some API or tool, such as RTVSQLSRC.


Reversion strategy when upgrading MySQL We are planning to upgrade from MySQL 5.6 to 8.0 and are considering disaster recovery plans. We have three separate databases within our MySQL 5.6 instance which are 200Gb, 170GB and roughly 9GB. Here are a few that come to mind. If anyone has any suggestions or comments, please let me know:

Image the VM which the MySQL instance runs on to backup the MySQL configuration files and .ibd files
Backup MySQL configuration files and .ibd/.frm files. If issues are to appear during the upgrade process we can revert the installation files via yum, install 5.6 again and copy back the configuration and .ibd/.frm files. Is this bad practice?

DO NOT DO #1 AS STATED! -- There are several vital files you have left out.  Capturing the entire mysql tree, plus my.cnf, would suffice.

And #2 is no better!

Without ibdata1, you have lost everything.


SQL Licensing on Azure, Standard D4 Windows 2012 Server 8 core, is it 1 phsysical core? I am trying to calculate my costs for Microsoft Azure. Imagine I take a Standart D4 Type machine running Windows 2012 and would like to install my own SQL Server 2012.
Are these 8 cores 1 quad core with hyperthreading enabled, or are these 2 Quad cores without hyperthreading ? I mean do I have to pay only 1 or 2 core license for SQL 2012 Standart  Edition?

I could't fint the answer online. Neither I have the chance to create the machine now and check the socket number.
Can anyone help me out?

At the risk of stating the obvious, nothing is physical in Azure as far as you are concerned.  The best thing you can do is go to the Azure Pricing Calculator, find the 'SQL Server' section, select the 'Standard' section, find D-Series, select 'D4' and slide the slider to 1.  That's what you will be paying, eg

SQL Server will treat that box as having 8 cores, so should you.  You can also compare and contrast costs, eg try Enterprise edition.  As far as running coreinfo on Azure goes, don't bother unless you've got some time to kill.  By signing up to IaaS you have resolved to care much less about that kind of thing, freeing you up to concentrate on the software.

If you are concerned about performance you should cast your eye over this superb article:

Performance Guidance for SQL Server in Azure Virtual Machines
https://msdn.microsoft.com/en-us/library/azure/dn248436.aspx


MySQL switch between data directories for individual databases I'm in the middle of designing a solution to an issue we're currently experiencing while writing to certain large MyISAM tables. The database in question basically stores information regarding two 'things', users and articles. The majority of the user tables are small but are read from and written to regularly. The majority of the article tables are very large but are pretty much only read from. Lots of the tables are linked through foreign keys (between the user tables, between the article tables and between user and article tables), and there are several views. The server is fairly hefty and has no problem serving the data under normal conditions.

The issue arises when we need to update the article data (which is now required every few days) and on doing so causes a backlog of writes to a few of the larger tables.
I had thought about separating the user data and article data into separate databases and then running the article update on a cloud server then migrating the updated data files over to the database server and switching MySQL over to the updated data. I know it's possible to specify the data directory in the my.cnf files all data but haven't been able to find out whether it's possible to specify data directories for specific databases. If not then this idea becomes pretty useless.
Any information on either whether this is possible (or not) or any other solutions would be great.

Thanks

p.s. We're running on MySQL 5.0.77

Rather than retype a lot information, here are some posts I made in the DBA StackExchange before on how to move MyISAM tables to different folder and disks

Dynamically changeable database
Can I specify a different disk for a specific MySQL table?
Mail alert from MySQL when databases are edited (I suggested creating a MyISAM-based general log that would stored on another disk. I did this for a client long ago and collected over 125GB of log info)


MySQL replication - some values are not being replicated MySQL Replication issues

I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion.

My structure looks like:

1) Master
   2) slave (without SSL)
   3) Slave (with SSL)

On some occasions, what goes wrong is that data are not replicated to slave.

  First my question, was if i someone is updating slaves. And i took off all
      privilege from all user other than read.
      which itself contradicts because both slave are not replicated and user's do not have permision on both slave.

Ignored DB:

binlog-ignore-db                                        = mysql
binlog-ignore-db                                        = test
replicate-ignore-db                                     = mysql
replicate-ignore-db                                     = test

Manual Test:
            I created new database, created table, new filed all was replicated to both slave. Updated works, altered works and delete works.

On some occasions when it misses out, randomly I am unable to troubleshoot or narrow down problem area.

Any suggestions?

FYI, I do not have skip-error in place.

Are you aware of http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/ ?

Perhaps some of the missing updates are being run with the test database as the current database?

It is less error-prone if you use the replicate-wild-* options. In your case

    replicate-wild-ignore-table = mysql.*
    replicate-wild-ignore-table = test.*


number of seconds to years-months-weeks-days-HH:MM-SS I am running this all the time to get HH:MM:SS from a number of seconds:

DECLARE @s INT
SELECT
    @s = 23251
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

when I want to add days to the equation I run the following query:

DECLARE @seconds AS int = 232511;
SELECT
    CONVERT(varchar, (@seconds / 86400))                --Days
    + ':' +
    CONVERT(varchar, DATEADD(ss, @seconds, 0), 108);    --Hours, Minutes, Seconds

what if I wanted as well the number of weeks, months, and years?

How would I go around that?

I think the exact calculation would need to have a reference to when the number of seconds happened.

Let's say for instance I am interested in how long a sql server job has been running for. I have jobs within the replication category that have been running for years, and the starting point is known.

When not specified we could assume the number of seconds are up to now.

Assuming you know @Seconds and @StartDateTime, something like the following could work:

 DECLARE @Seconds INT = 50000000;
 DECLARE @StartDateTime DATETIME2(7) = GETDATE();

 -- Get the EndDateTime
 DECLARE @EndDateTime DATETIME2(7) = DATEADD([SECOND], @Seconds, @StartDateTime);

 -- Make a one-row CTE of useful info for logic and math later
 WITH Calendar_CTE
      AS (SELECT @StartDateTime AS [StartDateTime]
               , CONVERT(DATE, @StartDateTime) AS [StartDate]
               , DATEPART(YY, @StartDateTime) AS [StartYear]
               , DATEPART(MM, @StartDateTime) AS [StartMonth]
               , DATEPART(DD, @StartDateTime) AS [StartDay]
               , @EndDateTime AS [EndDateTime]
               , CONVERT(DATE, @EndDateTime) AS [EndDate]
               , DATEPART(YY, @EndDateTime) AS [EndYear]
               , DATEPART(MM, @EndDateTime) AS [EndMonth]
               , DATEPART(DD, @EndDateTime) AS [EndDay]
               , DATEADD(DD, -1, DATEFROMPARTS(DATEPART(YY, @EndDateTime), DATEPART(MM, @EndDateTime), 1)) AS [EndLastOfPreviousMonth]),
      -- Another one-row CTE, this time with each DATEPART's differences
      Differences
      AS (SELECT [EndYear] - [StartYear] - CASE
                                             WHEN [EndMonth] < [StartMonth] THEN 1
                                             ELSE 0
                                           END AS [YearDiff] -- If EndMonth is less than StartMonth, subtract 1 Year from the naive differnce
               , CASE
                   WHEN [EndMonth] >= [StartMonth] THEN [EndMonth] - [StartMonth] - CASE
                                                                                WHEN [EndDay] < [StartDay] THEN 1
                                                                                ELSE 0
                                                                                END -- If EndMonth >= StartMonth -> If EndDay is greater than StartDay, subtract 1 Month from the naive differnce
                   ELSE 12 - ( [StartMonth] - [EndMonth] ) -- If EndMonth < StartMonth, subtract the difference from 12
                 END AS [MonthDiff]                    -- e.g. this May to next March: 12 - (5 - 3) = 12 - 2 = 10 months
               , CASE
                   WHEN [EndDay] >= [StartDay] THEN [EndDay] - [StartDay] -- If EndDay >= StartMonth, then take a simple differnce
                   ELSE DATEDIFF(DD, DATEFROMPARTS([EndYear], [EndMonth] - 1, [StartDay]), [EndLastOfPreviousMonth]) + [EndDay] -- If EndDay < StartDay, find the remaining difference to the previous end-of-month, then add the rest of the days to the EndDate (this accounts for differnt length months)
                 END AS [DayDiff]                                                                                -- e.g. Jan 30th to Feb 2nd: (Jan 31st - Jan 30th) + 2 = 1 + 2 = 3 days
               , @Seconds / 60 / 60 % 24 AS [HourDiff]                                                              -- e.g. Jun 29th to Jul 2nd: (Jun 29th - Jun 30th) + 2 = 1 + 2 = 3 days
               , @Seconds / 60 % 60 AS [MinuteDiff]
               , @Seconds % 60 AS [SecondDiff]
          FROM [Calendar_CTE])

     -- Put it all together
      SELECT CONCAT([YearDiff], ' Year(s), ', [MonthDiff], ' Month(s), ', [DayDiff] / 7, ' Week(s), ', [DayDiff] % 7, ' Day(s), ', [HourDiff], ' Hour(s), ', [MinuteDiff], ' Minute(s), ', [SecondDiff], ' Second(s)') AS [TimeElapsed]
      FROM [Differences];


Does the command SET STATISTICS...ON turn statistics information on for all queries running throughout the server or just the current connection? In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following:

  When STATISTICS IO is ON, statistical information is displayed, and
  when OFF, the information isn't displayed.

  After this option is set ON, all Transact-SQL statements return the
  statistical information until the option is set to OFF.

Does that mean all Transact-SQL statements that execute in the current connection, or across the whole server?
When I tested it myself, it seemed to be only within the scope of my connection.

The answer to the question can be found higher up the document tree:

SET statements:

  The Transact-SQL programming language provides several SET statements
  that change the current session handling of specific information. The
  SET statements are grouped into the categories shown in the following
  table.

Which explains that all these set statements are applied to the current session.

With the category for SET STATISTICS IO,TIME ON; being statistics statements


Why is INSERT INTO ... SELECT not copying all rows? I have two servers, SERVER-01 and SERVER-00 (not their real names). SERVER-00 is a SQL Server 2005 Standard instance, SERVER-01 is a SQL Server 2014 Standard instance. I have this query that runs in a SQL Agent Job every night on SERVER-00:

truncate table DataWarehouse.dbo.documents
set identity_insert DataWarehouse.dbo.documents ON

INSERT INTO [DataWarehouse].[dbo].[documents]
    (
     [documentID]
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]
    )
SELECT
     [documentID]
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]
FROM [server-01].[DataWarehouse].[dbo].[documents]

set identity_insert DataWarehouse.dbo.documents OFF

(The actual query includes more columns, I've trimmed it for readability.)

On SERVER-01, documents is a view. On SERVER-00, documents is a table. In this query [server-01] is a linked server connection on SERVER-00 (it uses the credentials of a sysadmin to connect to SERVER-01).

Only about half the rows are being INSERTed into documents on SERVER-00. There are no error messages or warnings being logged by the job - it always succeeds. How on earth can this be happening?

It turns out the culprit was our old friend, ARITHABORT!

In the view documents, poNumber is actually the output of a scalar-function call. One of the inputs was a BIGINT that I was trying to pass to a INT parameter, so an overflow error was being raised for some rows. Because the linked server connection from SERVER-00 to SERVER-01 was leaving ARITHABORT unset (and thus OFF), the error was being swallowed so I wasn't seeing it when running the query from that side.

Once I corrected the problem with the function (expanded the parameter to be a BIGINT), all the rows started copying over correctly!

For reference, I confirmed the linked server connection's options by running this query on SERVER-00:

SELECT *
FROM OPENQUERY([SERVER-01], '
    SELECT
        CASE WHEN SESSIONPROPERTY(''ARITHABORT'') = 1
        THEN ''ON''
        ELSE ''OFF''
        END AS [ARITHABORT]
')