MySQL, temporary on disk table on primary key retrieval? MySQL 5.5.24, Windows 7 64-bit.

I've InnoDB table with primary key defined on three columns:

CREATE TABLE `common_descriptions` (
  `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `object_type` varchar(32) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `lang_code` char(2) NOT NULL DEFAULT '',
  `object` varchar(12) NOT NULL DEFAULT '',
  `object_holder` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`object_id`,`lang_code`,`object`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I'm executing fairly simple query which (in my opinion) should use index to return rows

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

yet it creates 10 temporary tables of which 3 are on disk temporary tables (confirmed multiple times by monitoring show status like '%tmp%';). object column seems to trigger creation of temporary tables. When I replace object (varchar(12)) with description (mediumtext) temporary table is not created. explain extended of above query:

mysql> explain extended select SQL_NO_CACHE object_id, lang_code, object from co
mmon_descriptions limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: common_descriptions
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 47
          ref: NULL
         rows: 49765
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Both tmp_table_size and max_heap_table_size are set to 16M.

After reading documentation on temporary tables (http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html) I don't see any good explanation on why temporary tables would be created in this case. Why is it happening?

OBSERVATION #1

You need to look at what the Query Optimizer sees and what it is interpreting

First, look carefully at the query

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

There is no WHERE clause. It is generating a temp table for an unbounded result set.

LIMIT has low precedence in the eyes of the Query Optimizer. Why ?

According to the MySQL Documentation on SELECT

  The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

Another section of that same page says the following:

  With one argument, the value specifies the number of rows to return
  from the beginning of the result set:

  SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

  In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Once you use the clause LIMIT, the SELECT must generate a result first. Your result has no boundary (no WHERE clause). The LIMIT is applied afterwards.

That explains the temp table creation.

OBSERVATION #2

You mentioned the following in your question

  When I replace object (varchar(12)) with description (mediumtext) temporary table is not created

According to the MySQL Documentation on the Temp Tables (your link)

  Some conditions prevent the use of an in-memory temporary table, in
  which case the server uses an on-disk table instead:

  Presence of a BLOB or TEXT column in the table
  Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes
  Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the
  SELECT list, if UNION or UNION ALL is used
  The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an
  on-disk table.

The problem has a lot to do with the result set and the first bulletpoint (Presence of a BLOB or TEXT column in the table) Since TEXT field cannot occupy an in-memory temp table, it goes straight to disk. That's just the tip of the iceberg.

When you ran

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

you were essentially read the entire row. How come ?

The common_descriptions is defined with the InnoDB Storage Engine

The primary key of an InnoDB table sits in a logical structure call the Clustered Index (internally it is called gen_clust_index, which can only be seen when running SHOW ENGINE INNOD STATUS\G)

That document says

  Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records

Since you accessed every column of the PRIMARY KEY (object_id, lang_code, object), internally you were essentially fetching every row (including description) which explains why a PRIMARY KEY retrieval still yielded a temp table on disk. When you changed the SELECT list (object_id, lang_code, description) things evident changed. a little. An in-memory temp table would not have been made. Why?

MySQL Documentation says this:

  When the server creates an internal temporary table (either in memory or on disk), it increments the Created_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

Thus, Created_tmp_tables would not have incremented because of the description being mediumtext. It went straight to disk. Created_tmp_disk_tables would have incremented instead.

SUMMARY

Many factors were involved

LIMIT used again a result set with no WHERE clause
InnoDB Clustered Index
description being MEDIUMTEXT

SUGGESTION

Try adding an ORDER BY

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
order by object_id, lang_code, object
limit 1;

I hae seen it when done against a single column PRIMARY KEY.

Give it a Try !!!


How to check growth of database in mysql? I want to know is there any method to check the growth of database on file
EXAMPLES

Database A contains all tables in INNODB storage engine
Database B contains all tables in MyISAM storage engine
Database C contains a mixture of InnoDB and MyISAM tables

This is very funny. I just answered a question where I posted queries you can run to tell you how much disk space is consumed by each storage engine by database.

See that post from Jan 13, 2013 : Translating backup size to database size

In essence, you need to consult the information_schema for data and index sizes as viewed by mysqld. Here are some examples of how to get the needed information:

MyISAM

To get the amount of diskspace used by all your MyISAM tables, run this:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
) A;

To get this by database

SELECT db,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,engine,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema,engine
) A;

InnoDB

To get the amount of diskspace used by all your InnoDB tables, run this:

SELECT
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
) A;

To get this by database

SELECT db,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,engine,
    SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema,engine
) A;

CAVEAT #1

I have discussed this many times before

Sep 13, 2011 : Determining max database and table size supported and present size
Dec 01, 2011 : MySQL Workbench Database Sizes
Jul 07, 2012 : Find the largest databases on my server

CAVEAT #2

Keep in mind that these queries will report how much actual space is occupied for data and indexes. The actual file sizes may actually be more than the information_schema says. That being the case, you must run OPTIMIZE TABLE tblname; for each table that has fragmentation.

For example, suppose you have a MyISAM table mydb.mytable and you run this query:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

Goto to the OS and run on of the following:

ls -l mytable.MY[DI] (Linux)
dir mytable.MY* (Windows)

The sum of the bytes listed may be more than what the information_schema says. If that is the case, go run OPTIMIZE TABLE tblname; and run the same query, you may notice that the table will shrink.

That being said make sure run periodically run OPTIMIZE TABLE against all your tables that experiences heavy updates, delete, and inserts.


How can I parse mysql bin log file for statements (Row/Mixed Format) I have a mysql database running with binary log enabled. Unfortunately, a statement was executed by the client, which turned out erroneous. Since a number of records have been "affected", I want to begin by parsing the bin-log for data-changing statements from that time period. Then we might be able to begin picking up the pieces.

I have used:

mysqlbinlog -v --base64-output=decode-rows --start-datetime="2015-06-26 08:30:00" --stop-datetime="2015-06-26 09:30:00" data/mysql-bin.000005 > C:\temp\binlog.txt

The output is quite intimidating. What I would appreciate is a strategy for parsing out any INSERT, UPDATE or DELETE statements. Keep in mind I'm on MIXED MODE.

Thank you.

When you have binlog_format=MIXED some statements will appear as actual SQL. Other statements may get obfuscated like this:

### INSERT INTO `sanma`.`response`
### SET
###   @1=743096223
###   @2=0
###   @3='IR'
###   @4='UnderProcess'
###   @5=2015-05-29 20:51:34
###   @6=743053329
###   @7=NULL
###   @8=NULL
###   @9='758484829'
###   @10=1432912893
###   @11=758484829
###   @12='UnderProcess'
###   @13=0
###   @14=NULL
###   @15=NULL
###   @16=NULL
###   @17=NULL
# at 20535714
#150529 20:51:33 server id 71  end_log_pos 20535741     Xid = 84151212
COMMIT/*!*/;

That statement was possible to see because the INSERT ... SET syntax was used.

Other statements may get obfuscated beyond recognition. This is the risk of using MIXED or ROW.

I have dealt with this recently

Jun 02, 2015 : Why binlog capture only postions
May 15, 2015 : Show sql statements from mysql binlog (GTID)

The only thing you can do is

# Look for INSERT, UPDATE, DELETE statements
mysqlbinlog ... > statements.sql
grep -i "^[IUD][NPE][SDL][EA][RT][TE]"     statements.sql  > dml.sql
grep -i "^### [IUD][NPE][SDL][EA][RT][TE]" statements.sql >> dml.sql

There are no guarantees you will catch every change in statement form when mysqld switches to binlog_format ROW intermittently.


Table Data Space Used Confusion I have a heap table with around 700k rows.  The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME).  Why does Data Space Used say this table is taking up nearly 4000000KB (4GB) in Object Explorer Details.  It is compressed with PAGE compression.

I know I must be missing something on this calculation because I have another heap table with 12.7 million rows that is only taking up 1.5 GB and it has a row size of 356 bytes.  It is also compressed with PAGE compression.

EDIT:
I just did an SELECT * INTO to see what the size of this data would be in another table and it's only 28MB...

The solution was to create a clustered index on the table which immediately shrank the data size down to 13MB.  I found this out thanks to the following post on StackOverflow: https://stackoverflow.com/questions/3336934/reduce-sql-server-table-fragmentation-without-adding-dropping-a-clustered-index

  The point is, you have substantial fragmentation on the Heap. You keep calling it a "table", but there is no such thing at the physical data storage or structure level; a table is a logical concept, rendered physically as:

  either the Heap plus all Nonclustered Indices plus Text/Image chains
  or the Clustered Index plus all Nonclustered Indices plus Text/Image chains.

  Heaps get badly fragmented; the more interspersed (random) Insert/Deletes/Updates there are, the more fragmentation.

  There is no way to clean up the Heap, as is. MS does not provide a facility (other vendors do).


Is it good practice to create new tables for new elements in a database? I am trying to create a good model for a database that I need for an application. The way that I have tought about realizing it involves creating a new table for each new element that is added, but I am unsure if that really is such a good idea. I found a related question that used a garden as an analogy, and I quite liked that, so I will try to exemplify using that below. Note that each gardener only works with one garden. The gardeners doesn't need to be able to be at several gardens.

I have a set of Gardereners as such:

PrimaryKey | Name | Garden
1          | Bob  | Garden1
2          | James| Garden2
3          | Ian  | Garden3

Each Garden will point to a new table containing the flowers. The Garden contains all the flowers in that garden.

FlowerNumber | Type
1            | Holly
2            | Lilach
3            | Larch

The question is then, would it be "right" to create a new table Garden for each new Gardener that is created? I tought it kind of wasteful to create new tables all the time, but I am not sure of any other ways to do it. I think object-oriented thinking might have perverted my way of thinking about databases.

No. This will cause maintenance problems: when you want to update the structure of Garden, then changes (adding a new column, removing an old column, changes to constraints, changes to triggers, whatever) will have to be propagated to every Garden_* table. It is also bad data modelling. You should aim to have one table per entity/relation in your system. Having multiple Garden tables would imply that they are fundamentally different.

I'd suggest a structure more like this:

Gardener
--------
  ID (PK)
  Garden_ID (FK to Garden.ID)
  (other fields such as name, contact info, ...)

Garden
------
  ID (PK)
  (other fields such as name, garden address, ...)

Flower
------
  ID (PK)
  Type_ID (FK to FlowerTypes.ID)
  Garden_ID (FK to Garden.ID)
  (other fields)

FlowerType
----------
  ID (PK)
  name
  (other fields)

The data for this schema could look like this:

Gardner
-------
ID | Name  | Garden_ID
-----------|----------
1  | Bob   | 100
2  | Sally | 101

Garden
------
ID | Name
---------
100| Big garden
101| Little garden

Flower
------
ID  |  Type_ID  | Garden_ID
----|-----------|----------
1   |  200      |  100
2   |  201      |  100
3   |  202      |  101

Flower_type
-----------
ID   | Name
-----------
200  | Lilly
201  | Hosta
202  | Rose

This will let a gardener be assigned to one garden. If you need more flexibility such that gardeners can be assigned to multiple gardens, and gardens can have multiple gardeners assigned to them, you'd need a separate garden assignment table, and remove the garden_id field from gardener:

garden_assignments
------------------
  ID (PK)
  garden_ID (FK to garden.ID)
  gardener_ID (FK to gardener.ID)


Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way
(e.g. a SQL command that would generate the Amazon-format)
Please see:
https://docs.aws.amazon.com/machine-learning/latest/dg/creating-a-data-schema-for-amazon-ml.html
NOTE:  I obtained an answer from AWS support; see below.
Although the reference above doesn't explicitly name the format as JSON, it looks very much like a JSON format to me.

FROM AWS Support-Team:
There are 3 ways of obtaining database schemas through AWS services that I have heard so far.  The first two are using Lake Formation, and the last is with glue:

In the Lake Formation console window select Tables, click on the desired table, and hit the properties button, this will show a json schema as part of the information ("StorageDescriptor")
If the Lake Formation was setup using database blueprints, the blueprint will contain the schema, it is available from the lake formation console under blueprints
AWS glue data catalog – once a database is crawled by glue, it’s table schemas are available under the “View Properties” after clicking on the table, similar to 1)


How to select the largest value of the same column in multiple tables? I have multiple tables which have some columns with the same meaning, eg., event_timestamp.

The schemas are like following:

Table A:
+-----------------+-----------+------+-----+---------+----------------+
| Field           | Type      | Null | Key | Default | Extra          |
+-----------------+-----------+------+-----+---------+----------------+
| aid             | int(11)   |      | PRI | NULL    | auto_increment |
| user_id         | ........  |      |     |         |                |
| event_timestamp | timestamp |      |     |         |                |

...
Table D:
+-----------------+-----------+------+-----+---------+----------------+
| Field           | Type      | Null | Key | Default | Extra          |
+-----------------+-----------+------+-----+---------+----------------+
| did             | int(11)   |      | PRI | NULL    | auto_increment |
| user_id         | ........  |      |     |         |                |
| event_timestamp | timestamp |      |     |         |                |

How can I get the latest (largest) value of event_timestamp among these tables A~D for a specific user_id?

If you are using a DB that doesn't support CTEs (such as mySQL currently) or the query planner doesn't can't optimise what richardtallent's solution is trying to do so ends up scanning instead of seeking on user_id or event_timestamp (I suspect it MS SQL Server's planner may end up doing this), you could try unrolling the CTE a little and using it as a derived table:

SELECT MAX(et)
FROM   (
           SELECT MAX(event_timestamp) AS et FROM tableA WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableB WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableC WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableD WHERE user_id=@u
       ) AS dt

This should make it obvious to the planner that it can use indexes on the user timestamp columns (or better still, an index covering both) for each source table before unioning the four single row results and picking the max from there.

Of course if the query planner is smart enough to see how to perform richardtallent's version this more efficient way then which you chose comes down to which you find easier to maintain. The above may be hassle due to needing user_id=@u in several places making it impossible to use as a view in that form but it should be fine in a stored procedure or ad-hoc prepared query.


Searching for keywords in fulltext indexes using the "contains" function Lets assume "and" is not a stopword, in my language it should not be.

How do I search for it using the contains function?

It is a keyword, but how do I escape the keywordness?

No luck with:

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, 'AND')

(got no search hits) or

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, '[AND]')

(this was giving me an exception)...

If you believe that "AND" should not be considered a stop word by SQL DB Engine while performing full-text index search, you need to configure it accordingly.
From CONTAINS description.

The search for characters in the word or phrase is not case-sensitive. Noise words (or stopwords) (such as a, and, or the) in full-text indexed columns are not stored in the full-text index.
If a noise word is used in a single word search, SQL Server returns an error message indicating that the query contains only noise words.
SQL Server includes a standard list of noise words in the directory \Mssql\Binn\FTERef of each instance of SQL Server.

You need to edit \Mssql\Binn\FTERef location manually to remove AND from the list of stopwords.
Here is MSDN resource:
CONTAINS
Configure and Manage Stopwords and Stoplists for Full-Text Search


SQL Server system views VERY slow at one computer I have a weird problem and do not know how to debug or solve this issue.

We have a Microsoft SQL Server 2012 stored at a HyperV VM in the network. Clients in the same network access this SQL Server. The VM and the clients are part of a local domain. The database access is done via the ADO libaries (Delphi programs). The NT Authentification is used. Everything works fine.

One computer is in the network, but not in the domain. The whole TCP/IP stuff seems to be correctly setup. The server can be connected, and database access works fine and fast. All ADO based (Delphi) programs work fine and fast. But queries to the system tables are extremely slow and need up to 5 seconds.

The query is as simple as

SELECT * FROM sys.dm_exec_connections

or

SELECT * FROM sys.dm_exec_sessions

I have no idea what to do about this.

Update: I have done following further checks:

exec sp_WhoIsActive shows nothing during the query.
exec sp_who2 mentions "Awaiting command"
Logging in with "sa" is not changing anything.
In Delphi/ADO (outside domain) it is slow, in MS SQL Studio (outside domain) it is fast. Both tested with and without NT authentication.

I found the problem. Every time I connect to the server using the machine name (e.g. SERVER1\ABC), the system-view queries are slow, but everything else is fast. If I connect via IP-Address 10.0.0.x\ABC, then the system-view queries are fast too. That's very weird. At least I have a workaround. (Note: The SQL server is inside the domain)

This sounds more like a name resolution / reverse lookup issue. Have you tried adding an entry in the hosts file on the "Computer outside of domain"?

Windows keeps it in C:\Windows\System32\drivers\etc

Edit - https://stackoverflow.com/questions/10872557/how-slow-are-tcp-sockets-compared-to-named-pipes-on-windows-for-localhost-ipc


Select random 10 unique rows from MySQL database? I have one table (questions) with 54 questions.

The students have to answer all 54 questions.

I have to show 10 questions at once, then next 10 and so on....

I have to select 10 questions randomly and next 10 random questions except the previous 10 questions that already answered, and so on....

I store all the answers in another table named user_answer.

what will be the MySQL Query?

You can do this by using following query.

SELECT * FROM questions WHERE question_id NOT IN (SELECT question_id FROM student_answers WHERE student_id = '5') ORDER BY RAND() LIMIT 10;

I have assumed that there is 3 tables.

question - Hold all questions
student_answers - hold student specific answers of each question.
student - hold student informations.

I expect that student_id is the primary key of student table and foreign key of student_answer table.

The student_is is brought from session or somewhere.


How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings Our production server is running SQL Server 2008 SP3.

I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
And I have a few questions.

We have a relatively high amount of CXPACKET wait events on our production server (in average of 85%).
The server has default values for “cost threshold for parallelism” and “Max Degree of Parallelism”

I started to explore the possibility of increasing the value of “cost threshold for parallelism”.
I suspect the indeed the value is too low, but I wanted to get as much information as possible before I change anything and not just change it to 25 or 50 because that’s what the some of the internet posts suggested.

I’ve read this nice post about how to determine the value:
http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/

The median cost in my server was 63.

How can I monitor my server’s status “before” and “after” I change to settings, in order to verify the effect of the value on the system’s throughput?

Thanks in advance,
Roni.

If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do).

MaxDOP: https://support.microsoft.com/en-us/kb/2806535

...and I read somewhere that cost threshold needn't be changed but am still trying to track that link down :(

In any case, focus on MaxDOP.


Why does DBCC CHECKTABLE WITH TABLERESULTS only report Error 8990 on corrupt tables? I have a production VLDB that I'm using Paul Randal's guidance on to spread DBCC CHECKDB workload over a week. I'm logging the results of DBCC CHECKTABLE WITH TABLERESULTS and DBCC CHECKALLOC WITH TABLERESULTS to a table an noticed DBCC CHECKTABLE WITH TABLERESULTS was only returning a single row per table on non-corrupt tables. Here's the first few columns.

Error   Level   State   MessageText
2593    10      1       There are 57 rows in 1 pages for object "SmallTable".

But when I run DBCC CHECKTABLE WITH TABLERESULTS on a corrupt table in a test database, which I corrupted with this method, I get the following:

Error   Level   State   MessageText
8939    16      98      Table error: Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data), page (1:312). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
8928    16      1       Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data): Page (1:312) could not be processed.  See other errors for details.
8980    16      1       Table error: Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:312) and previous child (0:0), but they were not encountered.
2593    10      1       There are 0 rows in 0 pages for object "tblWhoops".
8990    10      1       CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'tblWhoops' (object ID 565577053).

I'm curious as to why Error 8990 ins't returned in with the non-corrupt table? DBCC CHECALLOC WITH TABLERESULTS and DBCC CHECKDB WITH TABLERESULTS return this row when there aren't any allocation or consistency errors to report.

Bonus question, why doesn't DBCC CHECKCATALOG have a WITH TABLERESULT arguement?

I'm curious as to why Error 8990 ins't returned in with the non-corrupt table?

I wasn't in the developers mind when it was being thought of, but my guess is to reduce some noise. It'd be redundant to give a summary of the table that had no errors. Since the Check* output is already pretty verbose, my guess is to cut down on some of that since there are no errors.

In fact, looking at the messages they are even labeled in a way that makes me believe they only wanted to give the output when an error occurred.

There are 57 rows in 1 pages for object "SmallTable".
CHECKTABLE found 0 allocation errors and 0 consistency errors in table 'SmallTable' (object ID 645293745).

Since the Check* commands are specifically to investigate and report back their findings (in addition to potentially fixing) you could argue for this either way. To really know, we'd have to ask the original developers if they even remember or had a thought about it.

  Bonus question, why doesn't DBCC CHECKCATALOG have a WITH TABLERESULT arguement?

Again, no clue. Best guess would be no one specifically asked for the output in that format or after the initial creation, no time was allotted to go back and add it.


Log plans for queries that never finish We have an UPDATE in our 2am nightly load that has on two occasions pegged CPU at 100% and never completed after many hours. I strongly suspect that this is happening because of inaccurate statistics causing nested loops in a join. I am not able to reproduce it in lower environments with the same hardware specs and postgres configuration.

I need to prove that the nested loops are occurring before I can put in an RFC to address the problem (e.g. adjusting analyze and autovacuum settings). I have setup auto_explain but this only seems to capture plans for queries that actually finish. How can I log plans for queries that do not finish?

In SQL Server I would be able to do this by running sp_whoisactive with @get_plans=1. I am looking for something similar in Postgres. The only idea I've had so far would be to run EXPLAINs using cron, but this seems very hacked together.

The auto_explain feature seems to fit this use case, if you can take a brief service outage to enable it. It logs a plan for any statement exceeding a time threshold whether or not it has completed yet. It can also log plans before execution starts, so you can even get the plan of a statement that blows out memory!


ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version 11.2.0.0.0 I'm trying to mount my database but sqlplus returned the following error :

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             679477352 bytes
Database Buffers          385875968 bytes
Redo Buffers                5455872 bytes
ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version
11.2.0.0.0
ORA-00202: control file: '/oracle/product/12.1.0/dbhome_1/control01.ctl'

I know that the official documentation said :

Action: Either restart with a compatible software release or use CREATE CONTROLFILE to create a new control file that is compatible with this release.

So I would like to create my new control file with the following code :

create controlfile reuse database "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 '/oracle/oradata/local/redo01.log' size 20M,
GROUP 2 '/oracle/oradata/local/redo02.log' size 20M
DATAFILE
'/oracle/oradata/local/DEVELOPMENT.dbf',
'/oracle/oradata/local/DEVIL_INDEX.dbf',
'/oracle/oradata/local/example01.dbf',
'/oracle/oradata/local/syseaux01.dbf',
'/oracle/oradata/local/system01.dbf',
'/oracle/oradata/local/temp01.dbf',
'/oracle/oradata/local/undotbs01.dbf',
'/oracle/oradata/local/users01.dbf'
CHARACTER SET AL32UTF8;

and when I execute that on sqlplus it returned me the following error :

SQL> @/oracle/scripts/create_controlfiles.sql
create controlfile reuse database "ORCL" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01210: data file header is media corrupt
ORA-01110: data file : '/oracle/oradata/local/DEVELOPMENT.dbf'
ORA-01130: database file version 12.1.0.2.0 incompatible with ORACLE version
11.2.0.0.0
ORA-01110: data file 2: '/oracle/oradata/local/DEVELOPMENT.dbf'

So

my database files seems to be configured for oracle 12.1.0 (which is my current oracle version 12c) but not compatible with older version.
my control file seems to be configured for oracle 11.2.0 so not compatible with my current version.

Any suggestion ?

(I accept that this is not quite technically accurate, but is close enough)

The error message is telling you that the database was created in a higher version than the one you are trying to open it in.  All the database files are marked with this version.

This database was created in v12, you're trying to open it in v11.  This will not work.  There is nothing you can do except use a v12 database home to mount/open the database.

It is possible to create a database in v12 which is compatible with v11, by using the COMPATIBLE parameter.  This tells the database engine what version the database should remain compatible with, and what database features to use.


MySQL - Search Alphanumeric string using wildcards - underscore (_) and asterisk (*) I have a table, products, with a product_id column as follows:
product_id
110177
110177T
177

I am trying to filter down the product results that should fetch result as below but it is also fetching the id 177
110177
110177T

Query - select * from products where produuct_id like %'177'%
What updates in query to discard the string 177 in search result and only fetch rest of two?

If you want to eliminate the 177 value, you have to do the following (see the fiddle here):
CREATE TABLE p (p_id VARCHAR (25));

and populate it:
INSERT INTO p VALUES
('110177'),
('110177T'),
('177');

and then run the following SQL:
SELECT * FROM p WHERE p_id LIKE '___177%';

Result:
p_id
110177
110177T

Note that the ___177% (3 underscores) predicate here will pick up all values that have 3 characters (any single character) followed by 177 followed by any other characters or none.
This is due to the difference between the % (percent) wildcard and _ (underscore) - the _ means that the placeholder represents one, and precisely one character whereas the % wildcard represents 0 or more characters.
So, the 177 isn't picked up because it has no characters before the 177 - it's  explained well here.
The != solution proposed by @Akina will also work but it implies knowing the values to be excluded in advance - my reading of your question is that you want to eliminate any really short product_ids and not just particular ones!
If you have more sophisticated requirements, you should take a look at regular expressions - an example from PostgreSQL can be found here - MySQL documentation here.
p.s. welcome to the forum!


How to link other tables (entities) to parent and child categories? I am not a developer by any means, but I have a fairly good understanding of how relational databases work and the various types. I am trying to map a database design diagram (using Visio) and need to understand how I would represent the correct relationships in the correct way for a table which needs to reference Parent/Child categories.

As an example: A member/profile table linking to a product parent category table (reference table containing 3 different product types) which also has 2 layers of sub categories for each parent as a tree like structure that drills down into more specific products. I understand the Parent/Child relationships but would I need to link the member/profile table to the Parent Category table (which inherently links to the child categories), or would I need to link the member/profile table to all three tables individually (parent and two child tables).

What I am trying to represent within the diagram, to ensure this is correct is that this database will be used to drive a front end website and when a user selects a Parent Category type from a drop down list, depending on the category chosen, it would then present the appropriate drop down lists for the associated child categories, allowing the specific Product Types and Child categories products to be associated with the member.

I hope this makes sense and I have explained this in a way that someone will understand. I just need to know how to represent this correctly within Visio.

I am going to suggest an alternate solution.

Rather than trying to mock-up a database design in Visio, why don't you create the database design in the relational database? Once that is complete you can use software to create the database diagram from your actual database. Most relational databases have database diagrams built in, and if the built in diagrams don't fit your needs there are a variety of third-party alternatives, many of them free, or with trial periods.

The advantages to this approach is that doing the actual design will help you think through your choices, and even if it doesn't end up being the final design, you can use the work as a starting point.


Mysql Multiple data files rollover size we want to setup the mysql ibdata files in such a way that after every 100M , new data is added to another file specified in innodb_data_file_path

i.e. innodb_data_file_path=ibdata1:100M;ibdata2:100M:ibdata3:100M etc

and mysql uses it first uses ibdata1, when its filled uses ibdata2 etc.

Is it possible? if so how to add configuration?

EDIT:
We have a huge database of many gigs and we ran out of space recently. We  could not upgrade the disk, so we are thinking of having the new data automatically go to new files and have flexibility of moving those files to newly added physical disks.

we do not have innodb_file_per_table ON, but we have many dynamically created tables per second. We think that all that diskIO and open files might create some corruption.

((100G is not the same as 100M.  typo was fixed))

The line you gave is what you do -- in my.cnf.  (Double check the syntax.)

That configuration was very important 15 years ago -- when some operating systems could not create a file bigger than 2GB (or 4GB).  Now is is just an artifact.  Most people simply have autoextend on the last entry and forget about it.

The config says, as you say, "fill up one before moving on to the next".  The number (eg, "100M") is the pre-allocated size.

Why do you want this config?  I am curious as to whether you have a use case for current machines.

It is usually better to have innodb_file_per_table=ON.  Each table that you subsequently CREATE or ALTER is put into it's own "tablespace", which is identified by a file: <tablename>.ibd.  At that point, you don't need a huge, growing, set of ibdata* files.  (Leave autoextend on the last one, just in case.)

Limitations... There is no way to have that config automatically add another ibdata* file name, only autoextend the last one.  Once created, you can't get rid of any of the files without dumping and reloading.

EDIT. "but we have many dynamically created tables per second" -- Ouch!  How many tables do you have?  How tiny are they?  In general, thousands of tables is bad.  Might it make sense to combine tables in some way?  Would you care to elaborate on the design that 'needs' lots of tables?

Lots of tiny tables are better off in ibdata* (file_per_table = OFF).  The OS overhead for more files is non-zero.  (For big tables, the maintenance benefit of .ibd wins.)  Regardless of the setting, there will be a .frm file per table.

Yes, lots of ibdata* files would let you migrate some to new drives.  Use "symbolic links" (if unix) or the equivalent if Windows (Junctions?).


How to write a query which finds all circular references when a table references itself? (II) I have the following schema (names changed), which I cannot change:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

That is, each record is a child of another record.

The root node is the record with Id equal to 0.

I want to run query which will find all circular references. For example, with the data

INSERT INTO MyTable (Id, ParentId) VALUES
    (0, 0),
    (1, 0),
    (2, 4),
    (3, 2),
    (4, 3),
    (5, 6),
    (6, 5),
    (7, 5),
    (8, 8);

the query should return

Id | Cycle
2  | 2 > 4 > 3 > 2
3  | 3 > 2 > 4 > 3
4  | 4 > 3 > 2 > 4
5  | 5 > 6 > 5
6  | 6 > 5 > 6
7  | 7 > 5 > 6 > 5
8  | 8

This is similar to my question How to write a query which finds all circular references when a table references itself? from last year, except for the following differences:

The root node is hard-coded to have an Id of 0.
Any self-cycle (8 in the example above) except for the root should be considered a cycle
The accepted answer runs into infinite recursion on the case 7 > 5 > 6 > 5 in the example above

I am having trouble coming up with a query to account for these. Suggestions?

I think you're looking for a recursive common table expression.

WITH rcte (Id, ParentId, [Path])
AS ( --- Anchor:
     SELECT Id, ParentId,
            CAST(CAST(Id AS varchar(10))+' > '+
         CAST(ParentID AS varchar(10)) AS varchar(max))
     FROM MyTable

     UNION ALL

     --- Recursion:
     SELECT t.Id, rcte.ParentId,
        CAST(CAST(t.Id AS varchar(10))+' > '+
         rcte.[Path] AS varchar(max))
     FROM rcte
     INNER JOIN MyTable AS t ON rcte.Id=t.ParentId
     WHERE rcte.ParentID!=rcte.Id)

--- Returns rows where parent is child, i.e. self-recursion:
SELECT ISNULL(b.Id, a.Id) AS Id, ISNULL(b.[Path]+SUBSTRING(a.[Path], LEN(CAST(a.Id AS varchar(10)))+1, LEN(a.[Path])), a.[Path])
FROM rcte AS a
LEFT JOIN rcte AS b ON b.ParentId=a.Id AND b.Id NOT IN (SELECT ParentId FROM MyTable)
WHERE a.Id=a.ParentId
ORDER BY a.Id
OPTION (MAXRECURSION 0);

First off, every relation is a potential anchor in the tree. Then union those anchors to their respective relations (working your way down the tree for each recursion). The recursion stops when Id is ParentId, i.e. when you've come "full circle", or when there is nothing left to recurse.

Finally, the returned resultset is filtered for only those records where Id=ParentID. You might want to add a filter to exclude the 0-member as well, not sure how you wanted it.

Edit: I've LEFT JOIN'ed rcte again (as "b") to catch any branches leading up to a self-recursion. The query still doesn't precisely return the data in the question, but perhaps it's close enough. :)

A quick intro to recursive CTEs from a blog post of mine.


Return results from SQL JOIN even if criteria not met in all tables New to SQL. Building my first contact manager project. Current schema can be seen below.
Issue:

I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact.
I put together the following attempt, but it only returns results if the contactGUID is in every contact_type table.

--Problem: Only returns records if contactGUID is present in every contact_type table

SELECT
c.contGUID, c.contType,
i.indTitle, i.indFirstName, i.indMidName, i.indLastName,
e.emailAddress, e.emailDomain,
p.phnCountryCode, p.phnAreaCode, p.phnNumber,
s.socURL, s.socHandle,
a.addrCountry, a.addrCountryCode, a.addrCountrySubdivision, a.addrCountrySubName, a.addrSecondarySubdiv, a.addrMunicipality, a.addrMuniSubdivision, a.addrStreetNumber, a.addrStreetName, a.addrPostalCode, a.addrLatitude, a.addrLongitude

FROM
CONTACT c

JOIN INDIVIDUALS i
ON c.contGUID = i.indGUID

JOIN CONTACT_EMAIL ce
ON c.contGUID = ce.contactGUID

JOIN EMAILS e
ON ce.emailGUID = e.emailGUID

JOIN CONTACT_PHONE cp
ON c.contGUID = cp.contactGUID

JOIN PHONES p
ON cp.phnGUID = p.phnGUID

JOIN CONTACT_SOCIAL cs
ON c.contGUID = cs.contactGUID

JOIN SOCIALS s
ON cs.socGUID = s.socGUID

JOIN CONTACT_PHYS_ADDRESS cpa
ON c.contGUID = cpa.contactGUID

JOIN PHYS_ADDRESSES a
ON cpa.addrGUID = a.addrGUID

Question:

How do I return all other contact types (email, social, address) if say, CONTACT_PHONE record is not present for a given contactGUID?

As Akina mentioned, you need to use an OUTER JOIN, specifically LEFT OUTER JOIN or more simply written as LEFT JOIN to take all the records from the left-hand side of the join clause, join where they match the right-hand side of the clause, and just NULL out the fields where they don't match in the right-hand side. This tutorial from W3Schools gives a brief intro with visuals to the different join types. Here's another article with further information on different join types. (Note these articles are database-agnostic and there may be other available join types for your specific system as well, but these are generally all one ever needs.)
What you were previously doing by just specifying JOIN is using the shorthand syntax for the INNER JOIN clause which only returns rows that match on both sides of the join clause.


Update all values for given key nested in JSON array of objects I have a jsonb column data in a Postgres table my_table. It contains the following data:

[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"}
]

I want to update the updatedAt key of all objects in that array using one query. I tried:

update my_table set data = data || '{"updatedAt": "1571150000"}';

The above query added a new object within the array like the following:

[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"},
   {"updatedAt":"1571150000"}
]

I want the output like:

[
   {"id":"1","status":"test status1","updatedAt":"1571150000"},
   {"id":"2","status":"test status2","updatedAt":"1571150000"}
]

I also tried jsonb_set(), but that needs the second parameter to be the array index. I can't be sure of the count of JSON objects in the array.

If this can be solved with custom functions, also fine.

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again.

with ct as
(
    select id, jsonb_array_elements(data) dt
    from   t
)
, ct2 as
(
  select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2
  from   ct
)
update t
set    data = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id);

select * from t;

id | data
-: | :-----------------------------------------------------------------------------------------------------------------------------------
 1 | [{"id": "1", "status": "test status1", "updatedAt": "1571150000"}, {"id": "2", "status": "test status2", "updatedAt": "1571150000"}]

db<>fiddle here


Unable to connect to database engine using built-in administrator account I am trying to connect to our SQL server 2012 database engine using SSMS but it fails with error 18456. I am logged in as the built-in administrator account on the windows server where the DB is installed and the database engine is configured to use Windows authentication. Details of the error in SQL server logs are as follows:

2016-08-30 11:04:01.290 Logon        Error: 18456, Severity: 14, State: 5.

2016-08-30 11:04:01.290 Logon        Login failed for user 'SERVERNAME\Administrator'. Reason: Could not find a login matching the name provided. [CLIENT: ]

I will appreciate any assistance resolving this problem.

Even though you are logged in as an administrator to the server were sql is running and installed, that login must still be a valid login in Sql Server with appropriate permission, is this the case?


How To Set Variables in MySQL Update Trigger I have an estimates table, and an estimates_line_items table.  I am trying to update the estimates table whenever one it's child line items changes.  I keep running into a syntax error.  The error isn't very descriptive("You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version").  It appears I am screwing up at setting the variable values.

CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
   ON estimate_line_items FOR EACH ROW

BEGIN

   -- variable declarations
   DECLARE vPrev_amnt INT;
   DECLARE vNew_amnt INT;
   DECLARE nDiff INT;

   SET vPrev_amnt = OLD.price * OLD.quantity;
   SET vNew_amnt = NEW.price * NEW.quantity;
   SET nDiff = new_amnt - prev_amnt;

   -- trigger code
   UPDATE estimates SET
    subtotal = total + nDiff,
    total = subtotal + (tax_rate/100 * subtotal)
   WHERE estimate_id = NEW.estimate_id;

END;

EDIT: I've also tried setting the variables like this, with the same results:  SET vPrev_amnt := (SELECT OLD.price * OLD.quantity);

How about ...

drop trigger if exists Update_estimate_from_line_items;
delimiter //

CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
   ON estimate_line_items FOR EACH ROW

BEGIN

   -- variable declarations
   DECLARE vPrev_amnt INT;
   DECLARE vNew_amnt INT;
   DECLARE nDiff INT;

   SET vPrev_amnt = OLD.price * OLD.quantity;
   SET vNew_amnt = NEW.price * NEW.quantity;
   SET nDiff = vNew_amnt - vPrev_amnt;  -- names amended

   -- trigger code
   UPDATE estimates SET
     subtotal = total + nDiff
   , total = subtotal + (tax_rate/100 * subtotal)
   WHERE estimate_id = NEW.estimate_id;

END//

delimiter ;


Clustered Index with nightly ETL process We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting.  The clustered index on the table consists of a composite key of four columns.

Due to where the data is coming from, there really isn't any way to load the data in the same order as the clustered index.  Keeping the index active during load or dropping/rebuilding it after the load both take more time than the load itself without an index.  On the other hand no clustered with proper non-clustered indexes can still result in some reporting queries taking hours to process.

The only things I can think of to speed this up would be partitioning and throwing more hardware at the problem.  Am I missing anything else that would help?

There are few ways to work around this problem:

As you mention, partitioning will help. If you partition on the leading column in the index and make sure all partitions are equally sized, you can gain super scale. However, the total CPU required to build the index wont be much lower if you partition. But it will save you memory (if you have a slow I/O system, this can be useful)

If this is a traditional data warehouse fact table, you might be better off with a columnstore index

Instead of doing a mega update - which is slow. Consider this pattern:

Load the changes you want to make into a staging table with the same schema and cluster index as the main table
Join the staging table with the old table and use INSERT INTO a new table with a cluster index. Enabled Trace Flag 610 while doing this.

This way, you can keep the benefit of the old table being sorted and avoid resorting all the rows (only the ones that have changed).


Summary of locks for a query Does SQL Server have any simple way to fetch metrics on how many (and possibly what type) of locks a given query has taken out during the course of it's execution?

Rationale...
I'm in the process of rewriting of a rather nasty stored procedure which makes heavy use of cursors. A set-based solution is possible, and as a result I've got three versions on it: the original, a set-based version with table vars and a set-based version that's purely CTE based. I'm getting good metrics out for the CTE based solution, but because of the size and complexity of the query created by the CTE version I'm wary of releasing it until I've done as much due diligence as I can on it.

Now I realise that the locks SQL take out are those that it thinks it needs to take out at a given point in time, and on a loaded production server it may behave differently, but I'm hoping it would give me a feeling for how it's going to behave.

Currently I'm using profiler and holding transactions open so I can run sp_lock, but was wondering whether I'm missing a trick in SQL Server 2008.

Alternatively, I'm trying to persuade myself that profiler returning a high number of reads when inserting into a table variable is okay (for a given value of okay), but I've not convinced myself. The CTE solution has half the reads of the table-var version, and the high read-count of the table-var version seems purely to be down to the inserts. In terms of duration, both the CTE and table var one are about the same, giving execution times roughly 150-200% faster than the cursor based approach.

Extended Events with Histogram target, event counter target or a bucketizer target. You can start from the How to: Find the Objects That Have the Most Locks Taken on Them example on MSDN, which offers almost exactly what you want. You can modify the example to get the lock types (the example 'as is' filters out S and U locks) and you can refine it to filter per session etc etc.

That being said, I must point out that approaching a performance investigation from counting the locks acquired is highly unusual. The focus is usually be on wait stats, as described in the Waits and Queues methodology. See for exampleCapturing wait stats for a single operation for how to use, again, Extended Events to capture the relevant info for a specific query.

On the other hand if you are concerned about locks from the point of view of concurrency  (will the new CTEs create more contention due to locking?) then I would recommend investigating deploying snapshot isolation.


PostgreSQL: how do I query over a column with a custom range? I'm trying to write a query over a string column called grades_served that can have values such as

k-5
1-5
5-9

The query could be for a specific grade or it could be a range with a start and end value.

I wanted to check what the best approach would be on this. My gut instinct tells me to reformat the column into a range datatype, but the issue is this is a complex range, with string values, such as k and Pre-K.

I thought about creating a custom type but that seems like an overkill and I've never done that before.

http://www.postgresql.org/docs/9.3/static/sql-createtype.html

Would really appreciate any thoughts or recommendations.

The range type would allow best performance and simple queries.

If you have some string values like k and Pre-K mixed with numeric values, just create a grade table with a rank value (an integer value representing actual sort order):

CREATE TABLE grade(
   grade text PRIMARY KEY
  ,grade_int UNIQUE int NOT NULL
);

Your numeric grades can conveniently be assigned the same grade_value, other grades get integer values according to their rank.

You might write a simple function to convert ranges with your original string values to their integer representations on the fly ...
And vice-versa to display your grades_served column where needed.

Convert grades_served to an int4range type.
Using the overlap operator &&, your query for a range of grades can then simply be:

SELECT * FROM tbl WHERE grades_served && '[1,4]'::int4range;

Or, for a single grade, the "contains element" operator @>

SELECT * FROM tbl WHERE grades_served @> -1;

Index

If your table is not very small I suggest you create a GiST index to support that (and so does the manual).

CREATE INDEX tbl_grades_served_idx ON tbl USING gist (grades_served);


dual table postgres doesn't return timestamp I am trying to select now() from my local postgres database dual table:

select now() from dual;

It returns no rows. Just prints column name now(yyyy-MM-dd HH:mm:ss.ffffff)

What is it that I am missing that it cannot get the system time?

Don't create dual tables

Why are you using a dual table in PostgreSQL to begin with? PostgreSQL has an implicit dual table if there is no from-clause.

SELECT now(); -- works fine.

You can also

SELECT * FROM now(); -- works fine.

For more information, see my answer to Why does SELECT count(*) return 1?

Creating a dual table should still work

You'll never need a dual table with PostgreSQL, and you shouldn't create one: it just convolutes the syntax. It's also foreign for most PostgreSQL users. That said, it's easy to demonstrate that it works..

test=# CREATE TABLE dual AS ( VALUES (true) );
SELECT 1
test=# SELECT now() FROM dual ;
              now
-------------------------------
 2017-10-05 15:34:34.359092-05
(1 row)


TDE Performance and Cost Threshold for Parallelism Is there any documentation, test data, or other evidence to suggest that the Cost Threshold for Parallelism (CTP) setting might impact performance when TDE is enabled on a database?  Without going into all of the details, we have seen some significant performance degradation with benchmark tests after enabling TDE on a database, and we are looking for any settings that might have an impact.  CTP is currently set to the default of 5, which I know is not ideal, so please resist the urge chastise me for it.

We will also add CPUs and test further, but if we can get better performance by changing CTP or another setting, that would of course be better than adding CPUs (due to the cost of the hardware, as well as SQL Server licensing per CPU).

I am pushing to change the CTP and test again, but I was hoping to see if anyone has already tested it so that we can prioritize the changes we want to test accordingly.

Thanks!

Whenever you've got an overall general performance question like this, start by asking, "What's the server's primary wait type?"

My favorite way to get that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: I'm one of the authors of that open source script.) The first section of that will return your top waits since startup. If you post that output here, you'll get a lot better diagnostic answers.


How bad is query plan pollution (too many equivalent plans in cache)? Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well.

I noticed some old modules of a system have non-optimal SQL queries, with multiple concatenated value strings instead of parameter placeholders. They do a polling on a table, like every 10 seconds, to get items added during the last few minutes, which generates a new query plan on every execution.

Their performance is not too bad, no SQL injection risk (no web/user forms), they're old and it would be a lot of work to change their queries to correct parameterization. I suggest to do this change, but there's debate that it would be a waste of time, with other things being more important.

Edit: The database is supposed to run with mostly parameterized queries (which all newer modules use), so I would like to avoid the "optimize for ad hoc" option. Partially parameterized queries create a plan anyway. Is there a downside when running in ad-hoc optimized mode, with mostly parameterized queries?

To me, it seems like these old modules take a huge portion of the database resources, although they are few. Even a single module of this kind would create thousands of query plans over time, while all newer modules together have less.

Is it importante to change these, or can I leave them in their state, with optimization/parameterized queries only in current/future modules?

SQL is like:

select ItemId, ItemName from Items
where ItemType=3 and ItemCreator=1234
and ItemDate >= '2013-11-23 12:30:00'

where the values vary and the date is a few minutes before now. In a few cases, the date has been changed to a parameter like "@startDate", to avoid format problems, but ItemType and ItemCreator values are still concatenated strings.

When monitoring query plans with DMV or Activity Monitor (Recent Expensive Queries - Plan Count column), I notice some of these queries have 8000+ equivalent query plans in cache:

select count(*), query_plan_hash
from sys.dm_exec_query_stats
group by query_plan_hash
order by count(*) desc

then selecting the plan XML with a CROSS APPLY on sys.dm_exec_query_plan, with plan handles selected by query plan hash.

Edit/Temporary conclusion:
It seems like it is best to leave the very old applications as they are, even when creating tons of ad hoc queries. My greatest fear was that the flood of single use ad hoc queries would cause the good, multi-use parameterized and prepared query plans to be evicted from cache. This does not happen, because, when cleanup is done, ad hoc plans are evicted first, and others are rated by factors like complexicity, number of usages etc. So parameterized queries with a high usage rate will likely be kept, no matter how many ad hoc or partially parameterized plans flood in. Ad hoc optimization reduces plan size (actually, no real  plans are stored at first use), but even more plans may be kept, with a similar memory usage (is this correct?). Even partially parameterized SQL (DateTime parameters to avoid local format troubles) will quickly get evicted if not used again, even when sent with sp_executesql, which forces parameterization and plan caching. Having a huge number (5000 to 8000+) equivalent ad hoc query plans is no good, but probably less harmful than having to dig through years old C#, C++, maybe even Visual Studio 6.0 code, to fix queries (nobody pays for that, and the stuff is still running without recognizable problems).

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type],
       SUM(pages_kb)/1024 AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.


How can I integrate a new system to a design scheme I previously created? This is a follow-up to a question I wrote a while ago, for reference, you can look it up here.

I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown)

Now that a new educational system is introduced(while the old one is still not "deprecated") I have been looking for a way to integrate both curriculum systems together without creating a whole new different system; As the two systems are fundamentally different, this have been quite a challenge for me.

Here's a picture describing the new system . . .

(English level 1, 2, and 3 are classified as courses)

See, the new system removes the whole grades system. A jr. high school student can enroll in the exact same course(English level 3, for example) as a sr. high school student.

Is there a way to make those two systems work with my current design scheme? Or should I create a whole new scheme for the new system?

Would it not solve your problem to redefine the GRADE_LEVEL table such that it contains either the grade of a course or the level of the course (which ever applies)?

The rows in GRADE_LEVEL could contain values such as:

GRADE 7
GRADE 8
GRADE 9
GRADE 10
GRADE 11
GRADE 12
LEVEL 1
LEVEL 2
LEVEL 3

You might consider adding a partitioning attribute to the GRADE_LEVEL table that would indicate which educational system is being applied.  This may be helpful for some of your application logic that might need to apply different processing depending on which system is in use for a given COURSE.


Why is this relation not in Boyce-Codd Normal Form (BCNF)? I have the relation (A,B,C,D,E).

The relevant functional dependencies are:

{ E,B,C } → { D }
{ A,B } → { C }
{ E,B,D } → { C }
{ E,A,B } → { D }

The candidate key is (E,A,B).

I do not understand why this relation is not in BCNF, because the candidate key and its attributes are only elements on the left. Thanks a lot for any help!

C is a non-prime attribute (not part of the candidate key), and C depends on (A,B), which is a proper subset of the candidate key.

So the table is not in second normal form by definition.

Since BCNF implies 3NF and 3NF implies 2NF, this implies that the table is not in BCNF.


Lock time not involved in MySQL slow query selection? I wish to analyze some read-write contentions problems on a very huge table where writes are very fast, less than 1ms in most case. The point is that the contention makes those write queries sometimes wait for the lock for a long time, i.e. hundreds of milliseconds. But the query itself remains very fast so it's not selected as a "slow query", which threshold is actually 10ms.

The only way to gather the information I need is to put à 0ms threshold so I can capture all queries, but it's not sustainable for more than 10mn on our production environment, where it would produce about 200MB of logs per minute.

In fact I can't understand why MySQL don't include the lock time, added to the execution time, for the slow query log selection process. At least, I wish there were an option for that.

Am I missing something ?

Confirming what you already know, for future reference:

"The time to acquire the initial locks is not counted as execution time."
— http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

It stands to reason, at least to me, that a "slow query" is best defined as a query that is poorly-optimized and takes longer than it should even when run in isolation.  It follows from that, that queries which only coincidentally take a long time to run because of circumstances unrelated to the query itself -- such as waiting for table locks -- should not be included in the slow query log.
I base this on two things: intuition, since the slow query log can also "log_queries_not_using_indexes" -- which is presumably also intended to catch bad optimization before the data set grows to the point that the queries actually turn into "slow" queries... and the fact that it doesn't seem to have been designed that way just because it was easier to implement or for any architectural limitation.
In sql/sql_class.h:
void update_server_status()
  {
    ulonglong end_utime_of_query= current_utime();
    if (end_utime_of_query > utime_after_lock + variables.long_query_time)
      server_status|= SERVER_QUERY_WAS_SLOW;
  }

If utime_after_lock were replaced with start_utime ... that should drive the behavior you are expecting.  YMMV, but that's what it looks like from here, from a brief look into the source code.
Alternately, you could write a script in your favorite language to hold a connection to the server and execute SHOW PROCESSLIST every "n" seconds.  The script would "remember" what each thread's Time value was from iteration to iteration, and could report execution times to you with the granularity of the polling interval when it found interesting patterns.  When it sees an interesting query, it could switch to SHOW FULL PROCESSLIST to get the entire body of the query.
I would poll my servers for their processlist (not "full") at 1 second intervals -- yes, in production -- and display the number of threads and the current longest-running query on my real-time status dashboard and have never seen evidence of an ill-effect from this.


MySQL: Securing Access using Stored Procedures I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account).

What permissions do I need to grant so the web service account does not have table access but the stored procedure the service executes does?

You need to grant the EXECUTE privilege, but there is something else you need to know.

When you create a Stored Procedure (SP), make sure the SQL SECURITY of the SP is DEFINER and not INVOKER. Why?

When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

In plain English, if SP has INVOKER for SQL SECURITY, you have to give the table privileges away to the user permanently before the call is made. When SP has DEFINER for SQL SECURITY, the table privileges are given away temporarily (as long as the SP is running).

I discussed this before

Sep 11, 2012 : Is it possible to encapsulate a table in MySQL?
Apr 08, 2013 : Why does this procedure raise a privilege error?


Setting Blank lines to ON before executing a SQLFILE in shell script echo exit | sqlplus username/password@host_ip:port/Service_name @SQLFILENAME

I want  to execute set sqlbl on before executing The SQLFILENAME and also want that after executing SQLFILENAME it should exit sqlplus

$SQLPLUS -S username/password@ip:port/servicename<< EOF
SQL-STATEMENTS
set sqlbl on
commit
EOF


how to roll back SQL Server updates? A botched update that failed halfway is causing issues, I was wondering how I could roll back an update for SQL Server? Secondly, I was wondering if there is any other place (aside from)  **Setup Bootstrap\Log**  where  I could see which updates were installing and which one failed. I use SQL Server 2012

That path is the full source of SQL installation info and is a goldmine.

The best way to deal with a failed patch is to find out what happened; either in those files or the SQL ERRORLOG. It's most commonly SSISDB in an availability group or a filestream error (fixable with add/remove programs on the instance to run a repair).

I'm not sure if you can rollback an unsuccessful patch but it's easy to reapply the patch and try again, they resume very very well. After its successfully installed then they're easy to uninstall with add/remove programs again.

There aren't many good reasons to uninstall an SP or CU for SQL though. They're pretty important.


Initialize replication from multiple split backup files We are creating a new Node within our existing P2P replication setup. To be able to create the backup and copy the files I am Backing up to 9 files - each will be about 72Gb:

e.g.

BACKUP DATABASE [Database_Name] TO
DISK = 'C:\MSSQL_Backup\Database_Name_file1.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file2.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file3.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file4.bak'

etc. (up to 9)

When I restore at the New Server I will then add the new Node to the P2P Topology.

All good so far?

The question is: When I Initialize the subscription at the new Node - how can I specify the Backup that was used - given that it is made up of 9 files?

I have searched the Net and though I have found the question asked a couple of times - it seems that no one can answer.

I would test this, but I am really pushed for time.

Any help appreciated.

As no one got back to me (fair enough - maybe you've got jobs too!) I have tested this.

We didn't use this method in the end for the production task because the powers that be decided it was ok/best to use 7-Zip to split the single .bak file.

However, the answer is - you only need to specify the a file of the set and it works fine (I did this on my test setup):

Restore as normal > when specifying the Backup to use as the initialization point, give it the first file name.

I assume this is because the LSN is listed in this file (whether it contains the right bit of the Log data or not is irrelevant - as it's only looking for a point to reference through the Log Reader agent).

In fact to double check this, I have just gone back to my test setup and commenced replication (P2P in this case as in SQL 2008R2 it allows  you to specify the Backup file through the SSMS GUI Wizard - call me lazy!) by using the 3rd file in my 4 file backup set, which worked fine. I believe this proves that the LSN is in the header of each file in the Backup set and also why using a Log backup or Differential backup also works.

Just to make sure that anyone in the future that needs the same answer to the multi-file backup initialization question can find this - I need to include the initial question as I would have asked it so they find this when they search:

Q: Can I use a split Backup set to Initialize replication?

A: Yes you can, simply specify any of the bak files used in the Restore to initialize.


SSIS script task - Execute Dataflow task based on variable value I have been trying to create a package which executes a couple of dataflow tasks based on the available value in one of the variables. How can I achieve this?

The sample scenario: Variable param may have a string value "current" or "previous". If the user selects current then the data flow task A will get executed, else data flow task B will get executed.

I have two variables : 1) Param and 2) ParamValue. Using the script task If param=="current" then paramvalue would be 1, else if param=="previous" then paramvalue would be "Previous"

And in the precedence constraint added conditions to execute the data flow tasks. But its not working as expected.

Precedence constraints should be able to handle this requirement. Obviously without seeing the package and how you're setting up the constraints, we can't tell you why it isn't working. However, I've found that using an Expression for the Disable property of the data flow task can also be a good way to control flow. Maybe that will work better for you.

To do this, right-click on the data flow task and select Properties. Go to Expressions and click the ellipsis (...) to open the Property Expressions Editor. Under Property, select Disable, and under Expression, click the ellipsis again to open the Expression Builder. Then write the expression that determines if the data flow task should be disabled or not.


Design of a database about Zoos I am trying to design a database storing data about several Zoos. I just want to ask if my design is good. I know pretty well SQL and queries, but I'm not an expert at design. Requirement:
You will manage zoos, animals, food, visitors and visits. Each zoo has an id, an administrator, a name and several animals. An animal has an id, a name and date of birth; it can eat various foods, the latter consisting of an id and a name. The system stores the daily quota(integer number) for each animal and food, e.g., an animal A1 <food F1, 10; food F2, 5>; animal A2 <food F2, 1; food F5, 2>. A visitor is characterized by a personal number(an id), name and age. A visitor can visit several zoos. Such a visit is defined by a unique identifier, a day, the paid price, the visitor's personal number and the zoo id.
This is how I've done the design:
CREATE TABLE Zoo(
    id INT PRIMARY KEY IDENTITY(1,1),
    administrator VARCHAR(30),
    name VARCHAR(40),
);

CREATE TABLE Animal(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30),
    dob DATE,
    zoo_id INT REFERENCES Zoo(id)
);

CREATE TABLE Food(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(25)
);

CREATE TABLE DailyQuota(
    id INT PRIMARY KEY IDENTITY(1,1),
    animal_id INT REFERENCES Animal(id),
    food_id INT REFERENCES Food(id),
    quantity INT
);

CREATE TABLE Visitor(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(40),
    age TINYINT
);

CREATE TABLE Visit(
    id INT PRIMARY KEY IDENTITY(1,1),
    zoo_id INT REFERENCES Zoo(id),
    visitor_id INT REFERENCES Visitor(id),
    day DATE,
    paid_price SMALLINT
);

Want to hear any opinions

I also had to create a view that shows the ids of the zoos woth the smallest number of visitors. This is how I have done it:

GO
CREATE OR ALTER VIEW view_smallestCountVisitors
AS
    SELECT t1.zoo_id
    FROM (SELECT v.zoo_id, COUNT(v.zoo_id) MYCOUNT
            FROM Visit v
            GROUP BY v.zoo_id) t1
    WHERE t1.MYCOUNT IN (
        SELECT MIN(t2.MYCOUNT)
        FROM (SELECT v.zoo_id, COUNT(v.zoo_id) MYCOUNT
                FROM Visit v
                GROUP BY v.zoo_id) t2
        );
GO

It works fine but I am just wondering if this can be done in a shorter way.

While you've done a pretty thorough job of following the textbook requirements, there are some practical considerations that you might or might not want to include, depending on the expectations of the assignment.  Some of these have to do with bad requirements.  I don't know if you would get extra marks or reduced marks for pointing these out.

Food quantity as an integer is probably not precise enough
Food quantities should have a unit of measure. Some foods will be dry, some will be liquid. Iguanas probably need a few grams of "Iguana Chow" whereas elephants will need multiple kilos of whatever they're eating.
Amounts of money should be stored as MONEY or another non-integer data type.
If your zoos are all over the place you might need to consider storing a currency with the monetary amount
Storing the age of a visitor is a mistake.  Visitors age between visits.  You're better off storing the date of birth of the visitor and calculating their age at each visit, since you know the date of the visits
In a real system, some of this information is going to be optional. Not everyone is going to tell you their date of birth.  Similarly, you may not know when your giant tortoise was born.
People's names, if they matter, are usually stored in more than one field (e.g. given name and family name)
It's highly unlikely that every animal will have a personal record of their dietary requirements. Some animals might, but for a lot of animals it might make more sense to store the requirements for all animals of a given species.
The fact that animal species is not accounted for in the model is kind of questionable, since you would think that most zoos would care about something like that


Deploying database changes in high availability environment We are currently looking at our deploy process, which consists of an application tier and data tier for a web solution.

We'd like to develop a deployment strategy that does not require downtime for our sites.

From an application point of view this is simple enough - we have a load balanced environment, and can turn servers on and off as we deploy the code. However we are not really sure how to approach the database side of things, which at the moment is a bit of a single point of failure.

The database is used for both reads and writes, and it is not really feasible (as far as we can see) to turn either off without bringing sites offline. So we need to keep the database in sync with whichever version of the application is currently servicing requests. We also need to allow enough time for the database deploys to be made, without causing any problems.

Is this a solved problem in DBA circles? Does anyone have any tips, stories or resources that might help us approach this problem? At the moment we are not really sure what our first step in this process should be.

We are using SQL Server 2008

If you're talking SQL Server, I have some specific advice about making database changes backward compatible. The ideas and concepts are quite similar to other platforms, but the code samples won't help much.

The basic idea is this: make your changes in such a way that they don't require downtime and that they don't break existing code. For example, if you add a new parameter to a stored procedure, don't make it required. You can start out by making it optional, this way you can change the application to support the new parameter later, and it doesn't break in the meantime (and the code in the procedure can use logic to decide what to do, depending on whether the parameter was supplied). This can allow you to update multiple applications in a staggered way instead of all at once, and also allows you to change the application to the old behavior without also having to roll back the database changes. You can then make the parameter required after all the applications have been updated to support it.

Big depends here, because I have no idea what kind of scope your database changes encompass, but here are some ideas about how I accomplished this for specific changes:

Make your SQL Server database changes backward compatible when adding a new column

Make your SQL Server database changes backward compatible when dropping a column

Make your SQL Server database changes backward compatible when renaming an entity

Make your SQL Server database changes backward compatible when changing a relationship

That all said, your deploy scripts for databases should be using the same type of methodology as your code, including repeatable and verifiable scripts stored in source control as opposed to hand-written instructions about where to point and click in some UI to make a change. Why should your mentality about how to deploy database changes really be any different from how to deploy code cha


Restarting Mysql makes application fast I have a Drupal 7 application running a large Mysql database. It's built on AWS instance (30GB RAM, 8 CPU). Below is the my.cnf copy.

Issue is that my application gets noticeably fast when I restart the mysql which makes me think that current configuration isn't optimal and once the caches get filled, they slow down the server speed.

Any experts who could point out the issue here?

[mysqld]
# moving datadir to /media/db ebs storage
datadir=/media/db/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/dev/shm/
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

local-infile=0
log-warnings=2

skip-external-locking
bind-address        = 127.0.0.1
expire_logs_days    = 1
max_binlog_size         = 100M
log-bin=bin.log
log-bin-index=bin-log.index
binlog_format=row
key_buffer_size                = 512M
max_allowed_packet  = 32M
thread_stack        = 292K
myisam_sort_buffer_size = 64M
thread_cache_size = 50
myisam-recover         = BACKUP
max_connections        = 100
max_user_connections = 150
table_cache            = 16384
table_open_cache       = 10240
table_definition_cache = 4096
thread_concurrency     = 8
join_buffer_size = 4M
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 200M
innodb_buffer_pool_size= 8G
tmp_table_size=64M
max_heap_table_size=64M
wait_timeout=50
interactive_timeout=50
connect_timeout=10
open-files-limit               = 65535
innodb_flush_log_at_trx_commit = 0
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
table_open_cache = 5000
innodb_log_buffer_size= 32M
innodb_log_file_size = 512m
read_rnd_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 25
innodb_write_io_threads = 16
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 24
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_autoinc_lock_mode = 2

[mysqldump]
max_allowed_packet  = 32M

[isamchk]
key_buffer  = 32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
set-variable=long_query_time=1
long_query_time=10
slow_query_log_file =/var/log/mysql/mysql-slow.log

This may be the problem:

query_cache_size = 200M

Don't set it more than 50M.  When a write to a table occurs, that cache must be scanned to purge any entries relating to that table.  (Most production systems actually benefit from turning the QC off.)

Are you using MyISAM?  Or InnoDB?  The following values assume a mix of the two, and is sub-optimal for the typical case (all InnoDB):

key_buffer_size                = 512M
innodb_buffer_pool_size= 8G

5.5.58?  That's 3 major revisions behind.

You can only rarely "tune your way out of a performance problem".  Hopefully, the slowlog will point out that it is simply a matter of adding a composite index or reformulating a query.

Analysis of STATUS and VARIABLES

Observations:

Version: 5.5.58-log
30 GB of RAM
Uptime = 18:00:31; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Your dataset seems to be rather small, so the small setting for innodb_buffer_pool_size is actually OK, at least until the dataset grows significantly.

Shrink query_cache_size to 50M to improve performance.

The frequency of full table scans and range scans indicates the need to look into the slowlog.  Set long_query_time=1

Are the SAVEPOINTs working well for you?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 8192M / 30720M = 26.7% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (512M / 0.20 + 8192M / 0.70) / 30720M = 46.4% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 5,000 -- Number of table descriptors to cache
-- Several hundred is usually good.

( innodb_buffer_pool_size ) = 8192M -- InnoDB Data + Index cache

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 396,447 * 16384 / 8192M = 75.6% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 396,447 / 524280 = 75.6% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 64,831 / 60 * 512M / 258605568 = 2,243 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 110,734 / 105511 = 1.05 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON -- Re-analyze table when touching stats.
-- ON is likely to slow down certain SHOWs and information_schema accesses.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( net_buffer_length / max_allowed_packet ) = 16,384 / 32M = 0.05%

( query_cache_size ) = 200M -- Size of QC
-- Too large = too much overhead. Recommend either 0 or no more than 50M.

( Qcache_lowmem_prunes/Qcache_inserts ) = 942,628/2529864 = 37.3% -- Removal Ratio (pct of time need to prune)

Qcache_lowmem_prunes = 15/second

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (200M - 149143456) / 29625 / 8192 = 0.25 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

(Some of the other STATUS values indicate that the QC is effective.)

( Select_scan ) = 163,611 / 64831 = 2.5 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 163,611 / 2592449 = 6.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)

( log_slow_queries ) = log_slow_queries = OFF -- Whether to log slow queries. (Before 5.1.29, 5.6.1)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 1

( Connections ) = 97,624 / 64831 = 1.5 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 50 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
0 is inefficient for non-Windows; 10 is probably fine.
More than 100 may lead to OOM.

( thread_cache_size / Max_used_connections ) = 50 / 25 = 200.0%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,435
interactive_timeout = 50
query_cache_limit = 262,144
query_cache_min_res_unit = 2,048
wait_timeout = 50

Abnormally large:

Com_release_savepoint = 1.6 /HR
Com_savepoint = 1.6 /HR
Handler_savepoint = 3.2 /HR
Key_write_requests = 407 /sec
Qcache_free_blocks = 18,324
Select_range / Com_select = 34.2%
innodb_spin_wait_delay = 24
innodb_sync_spin_loops = 200
max_user_connections = 150
table_definition_cache = 4,096

Abnormal strings:

have_symlink = DISABLED
innodb_fast_shutdown = 1
tx_isolation = READ-COMMITTED

Tentative conclusions

Nothing jumps out to explain why slows down.
There could be some good clues in the slowlog -- such as table scans that don't show up until some time after startup.


SQL Server 2012 Express high Commit (KB) (low virtual memory condition) I have a customer running SQL Server 2012 Express Edition:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15
  Copyright (c) Microsoft Corporation Express Edition (64-bit) on
  Windows NT 6.1  (Build 7601: Service Pack 1)

The server is 64-bit, running Windows 7, and they have 8GB of RAM. The page file is configured to be managed by the OS.

The other day, they began noticing "low virtual memory condition" entries in the Windows Event Viewer such as the following:

  Windows successfully diagnosed a low virtual memory condition. The
  following programs consumed the most virtual memory: sqlservr.exe
  (2792) consumed 28103749632 bytes, svchost.exe (592) consumed
  617349120 bytes, and w3wp.exe (7700) consumed 609824768 bytes.

I logged in and took a look at the Windows Resource Monitor and noticed that the Commit (KB) counter for the sqlservr.exe process was "high" while the working set was relatively "low":

Process Explorer confirmed:

Based on the documentation for Commit (KB):

  This is the total amount of physical and virtual memory (page file)
  that is committed to this specific process.

I also monitored Perfmon's Available MBytes counter and it consistently reads an average around 4.624 GB.

select * from sys.dm_os_process_memory returned this following:

Why would sqlservr.exe be using so much "virtual" memory when there's still plenty of "physical" memory available? Also, I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

On a side note, a number of web links mentioned that McAfee Virus Scan Enterprise/McAfee Host Intrusion Prevention could cause this type of behavior. Both were installed by on this server by their IT group. I attempted to add the appropriate exclusions but never saw the Commit (KB) drop. How could I prove it's a McAfee issue?

UPDATE: I found what seems to be a similar issue with McAfee Host Intrusion Prevention and SQL Server on the same box.

Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (2792) consumed 28103749632 bytes

This is general message saying their is low virtual memory condition.

  Why would sqlservr.exe be using so much virtual memory?

Their can be quite a few reasons for such messages.

Windows is facing low memory condition
Page file is not configured properly
Some application installed is taking too much memory or leaking memory

As per the error message the SQL Server  was the one which required huge amount of memory and was paged out to disk but that is not the only component. You need to find out why SQl Server required huge amount of memory. Knowing the limitation of express edition you should be aware to not run memory intensive queries.

In the error message I can see w3wp.exe which means application is also running on same machine in such case its quite possible windows would face low memory condition. What is total RAM present on the system ?. Since you have multiple appplications running make sure you have enough RAM on system

  I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

Aaron already pointed out and I am going to emphasize on that, starting from SQL Server 2012 the SQL Server express database engine can consume more than 1G of memory. The BOL information is not updated one it still says memory is limited to 1 G which is incorrect. As per this support article

  Starting with SQL Server 2012, these memory limits are enforced only
  for the database cache (buffer pool). The rest of the caches in the
  SQL Server memory manager can use much more memory than is specified
  by these edition limits. For example, a SQL Server 2012 Express
  edition can use only a maximum size of 1.4 GB for its database cache.
  Other caches (such as the procedure cache, the metadata cache, and so
  on) can consume memory up to the size specified by the "max server
  memory" configuration.

EDIT:
From the output of DMV sys.dm_os_process_memory.

Physical_memmory_in_use_KB=445300 KB which is 445 MB

This is physical memory used by SQL Server instance. This is memory backed by physical RAM

Virtual_address_space_committed_kb= 29116348 KB which is 27 G
This is total SQL Server memory utilized (RAM+Pagefile) by SQL Server. So you can see now amount of SQL Server memory curently paged out to disk is 27G -0.4 G.
You need to find out why and what has caused SQl Server to request so much memory. I strongly suggest you to appply SP3 ASAP.

I am not ruling out possibility of McAfee not causing the issue but with statistics posted it seems SQl Server is one which is requesting more RAM


SQL Cluster IP should this be the same as the WFC cluster IP? Been building my first SQL Server cluster.

One question I have: Should the SQL Cluster IP address be the same as the WFC cluster IP address?

Thanks!

No you will need separate IP for Windows Server Failover Cluster (WSFC) and Failover Cluster Instance (FCI, what you mentioned as SQL Cluster).


How do I view a Microsoft SQL Server Compact Database I have installed SQL Server 2012 Enterprise Edition.

I can use SQL Server Data Tools to add databases and tables and query tables etc.

Next, I used Visual Studio to try out a tutorial on creating a website with a database. The database type created was an SQL Server Compact Local Database.

This database doesn't turn up in the list of databases in SQL Server Data Tools. How do I view compact local databases? (I want to be able to view the data in the DB, add rows using a GUI, and run SQL queries within the tool.)

Or are they different products, in which case does Microsoft have an equivalent tool to SQL Server Data Tools for SQL Server Compact databases?

After OP's Edit to the question :
Try to use Visual Studio 2010 SP1 + VS Tools for SQL CE 4
-- or --
SQL Server Management Studio 2008 R2 supports for SQL Server Compact 3.5 database. (ATTENTION from SSMS version 2012 and newer, Sql Server Compact databases are no longer supported by SSMS; You must use SSMS 2008 R2 or OLDER)
Note : SQL Server Management Studio cannot open SQL Server Compact 4 files, only SQL Server Compact 3.5 files.
Click View >>Toolbars >>SQL Server Compact Edition Editor as shown.

Additionally, refer to SQL Compact 3rd party tools and
SQL Server Compact Toolbox on Codeplex - It is a Visual Studio add-in and standalone app , for managing all aspects of your SQL Server Compact database files.