Where does Postgres store all statistics? I recently ran into a weird problem while migrating from PG 9.4 => 11

I replicated the production server from PG 9.4 to PG 11 using pglogical. Everything worked out well, except we discovered that a few complex queries were taking tens of seconds on the PG 11 server, while there were taking under one second on the 9.4 server.

It turns out that running ANALYZE on the new server fixed the slow queries. So, I checked $PGDATA/pg_stat and $PGDATA/pg_stat_tmp and found them to be empty on both the servers!

Where does PG store statistics? (Follow-up question -- doesn't pglogical collect statistics while it is replicating?)

Edit / follow-up:

Output of query on pg_stat_user_tables on the new PG 11 server -

select analyze_count,autoanalyze_count, count(*) from pg_stat_user_tables group by 1,2;
 analyze_count | autoanalyze_count | count
---------------+-------------------+-------
             1 |                11 |     1
             4 |                 7 |     1
             1 |                 0 |    14
             4 |                 0 |   168
(4 rows)

Same query on old PG 9.4 server (current production server):

 analyze_count | autoanalyze_count | count
---------------+-------------------+-------
             0 |                13 |     3
             0 |               226 |     1
             0 |                 1 |    31
             0 |                 5 |     6
             0 |                21 |     2
             0 |                16 |     2
             0 |               601 |     1
             0 |                 8 |     1
             0 |                20 |     1
             0 |                 4 |     2
             0 |                 9 |     2
             0 |                10 |     2
             0 |                12 |     1
             0 |                 7 |     2
             0 |                41 |     1
             0 |                 2 |    11
             0 |                 6 |     1
             0 |                 3 |     8
             0 |               731 |     1
             0 |                39 |     1
             0 |                11 |     1
             0 |                 0 |    99
             0 |                50 |     1
             0 |                66 |     1
             0 |               241 |     1
             0 |                14 |     2

There are two different kinds of statistics, and you are confusing them.  One is about the distributions of values in the table columns and things like that.  These are held in the table pg_catalog.pg_statistic, and the more-user-friendly view pg_stats. This is a more-or-less normal database table. These are gathered by ANALYZE and/or VACUUM, or their "auto" equivalents.

The other kind of statistics records how many updates, inserts, etc. have occured on each table. On a live server, you can see this data using the views pg_stat_user_tables and friends, but there are no real tables underlying these views. These statistics affect the first kind of statistics, because they are used to decide how often ANALYZE and VACUUM are auto-run. These are stored outside the normal database tables, in "pg_stats_tmp" (or whatever "stats_temp_directory" is set to if it is not the default) when the server is running, and moved to pg_stat for cold storage while the server is shut down.  Upon a crash, all this data is discarded. If both of these directories are empty, then perhaps you have turned "track_counts" off, or perhaps you have set "stats_temp_directory" to a non-default value and so are looking in the wrong place. Or maybe there was a crash recently and they have not been rebuilt yet.

  Follow-up question -- doesn't pglogical collect statistics while it is replicating?

If you have "track_counts" or "autovacuum" turned off, that would explain the lack of recent stats.  Or, if you populated the new database extremely quickly, then maybe the automatic analyze just didn't have a chance to do its things yet.  If none of those are the case, then you would have to consider whether there are no stats (of the pg_stats variety) at all, or if they existed but were just not fresh enough.  Running this query might help you figure that out:

select analyze_count,autoanalyze_count from pg_stat_user_tables

But there is a pretty good chance you will never be able to reach a definitive conclusion, unless you are willing to recreate the situation from scratch and poke around in it.


Can MySQL server send a "server started/running" signal to a remote server? I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries.
Right now I'm polling the VM to get it somewhat working, but there's gotta be a better way.
I read the MySQL docs on handling Unix signals, but there isn't a "startup" type signal.
I also read about MySQL events, but those seem to be limited to operations within the database like SQL queries.
Does MySQL have a way to push events to a remote server on startup, shutdown, error, etc.?
It's probably possible to achieve in a script on the OS, and then send a curl request to the remote server with appropriate info, but MySQL's a big software and I'm curious if this functionality is hidden somewhere in it.

Such a feature cannot be in mysqld in the cloud.
In order not to pay for a cloud VM, it must be totally down.  That means that communication with it must be preceded by restarting your image.  Only then is mysqld awake.  At that point, it is ready to receive connections.  (And you must "connect" before doing "insert" or whatever you need to do.)
So, your "it's probably possible ..." paragraph is the approach to take.
To add to the complexity, there is probably no easy way to "automatically shut down" the VM after an idle period.  Consider having your "restart" script wait a few minutes, then shutdown.  (Where "a few minutes" is tuned to accommodate your typical activity time.)
For security reasons, MySQL deliberately has no built-in way to communicate with the rest of the world.  (A common request is to send an email based on some data change.)
Another thing to look into -- a proxy between your clients and the VM-MySQL server.  The proxy would live on the client machine(s).  It might be able to orchestrate the startup.  (There are many Proxy products out there; they are aimed at other things -- HA, sharding, routing, load balancing, etc.)


mysqld keeps automatically restarting I am running mysqld on Mac OSX Al Capitan. After boot up, I ran mysql.server start to start mysqld server.

But when I ran mysql.server stop, or sudo mysql.server stop, it just stopped for a while, later it will automatically restart itself again.

I also tried mysqladmin -uroot -p -hlocalhost shutdown for no avail.

It just keeps restarting again and again.

I guess it's maybe related to mysql_safe process, but I don't know how to properly stop mysqld, and force it not to restart again, until I myself run mysql.server start manually. Thanks!

If you used homebrew to install mysql, mysqld will keep on restarting unless you use following brew command:

brew services list

Get the name of service to stop and then run the following command:

brew services stop mysql@5.5

From: https://stackoverflow.com/a/57169447/2670370


How to lock a sql login after N unsuccessful login attempts Consider I have login called sql_login. Can I lock sql_login login after 5 unsuccessful login attempts.

When we create a login we can see there is option called password policy check the image. But there is nothing mentioned about locking password

Is there a option in Sql Server to lock a login after N unsuccessful login attempts

Yes, you can but you have to set everything up.

SQL Server (SQL) Login

When you enable the Enforce password policy option in the SQL Server Login window, you are basically telling the SQL Server to adhere either to the local security policy or to the policy defined in the domain.

  The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

  When CHECK_POLICY is changed to ON, the following behaviors occur:

  CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
  The password history is initialized with the value of the current password hash.
  Account lockout duration, account lockout threshold, and reset account lockout counter after are also enabled.

and also:

  The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).

Reference: Password Policy (MSDN / SQL Server 2012)

Setting Policy (locally)

So if you require an account to be locked out after 3 wrong tries, then you either have to define a local policy with secpol.msc or define a domain policy for account lockout.

Example in secpol.msc

SQL Server will then use this policy if the Enforce password policy option is checked.

A technical overview of the account lockout policy can be found here:

Reference: Account Lockout Policy Technical Overview (MSDN)
Reference: Account lockout threshold (MSDN)

Locked out SQL Login

Here is what happens after a SQL Login has been locked out after the set amount of incorrect logins (15 in my case as domain policy). You can see the Login is locked out is set. This can be unset to unblock the account.


MySQL warm up time after restart because of lost connection MySQL 5.6.10 on Windows 7.

I'm cleaning up and paring down a large INNODB table. Its .ibd file is originally 22 gigs. While I was in the middle of an ALTER, dropping some columns, the mysqld.exe process died.

Upon restarting and starting up mysqld, I noticed a lot of disk activity (~10mb/s) from mysqld, that has been going on for around 15 minutes so far. Also, in my data folder, there are files with names like "#sql-7b8_3.frm".

Could someone shed light on what's going on?

After a bit or research, it looks like the '#sql-xxxx.frm' files are just temp files created during ALTER statements, and possibly other MySQL behavior such as OPTIMIZE. If the mysqld service crashes, those files tend to get stranded. There's a few other stack exchange threads asking how to get rid of them, and to be honest, none of them worked for me. There's one rather clunky one suggested by the error logs, but instead I just deleted the temp files and dropped the original table I was working on. Then I reimported the table from an earlier dump.

During the course of an ALTER, '#sql-xxxx.frm' files are created. If the alter runs smoothly and completes, the '#sql-xxxx.frm' file will be deleted on its own.


How to find tempdb reside in drive location with drive size and tempdb usage details in single query in sql server? How to find tempdb reside in drive location with drive size and tempdb used details  in single query in sql server?

This information can be found using the management DMVs sys.master_files and sys.dm_db_file_space_usage, and the system function sys.dm_os_volume_stats:

SELECT vs.volume_mount_point AS [Drive],
  mf.[name] AS [File],
  mf.physical_name AS [Filename],
  mf.size*8/1024 AS [Size (MB)],
  unallocated_extent_page_count*8/1024 AS [Free Space (MB)]
FROM sys.master_files mf
INNER JOIN sys.dm_db_file_space_usage fsu ON fsu.database_id = mf.database_id AND fsu.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE DB_NAME(mf.database_id) = 'tempdb'


Why would you distribute replica shards across nodes and not mirror replicas? I'm trying to understand how replicas work in Elasticsearch.

This is a very basic question, but can someone explain why one node is not just all replicas? Surely in this example, if one node fails 50% of the shards will be lost?

In your example, there are two nodes.
The data is represented at 4 data set shards: A, B, C, D.
Both nodes hold all 4 data sets, with each node as primary writable shard for 2 data sets, and secondary (read only) replica shards for 2 data sets.
This allows you to spread workload across both nodes, with each node serving half of the workload. In the event if a node failure, the secondary replica data sets can be made primary on the other node, with a single node serving 100% of the workload. There is no data loss because A, B, C, & D exist on both nodes.


Non-Clustered Indexes - keys and nonkeys I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.

Here's my theory from the query I've just optimised, through a process of trial and error and reading the MSDN documentation.

The Query

DECLARE @pic_id int
SET pic_id = 1

SELECT ROW_NUMBER() OVER (ORDER BY pic_date desc) AS row_num, *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND (hidden = 0 OR pic_id = @pic_id)

The Index

CREATE NONCLUSTERED INDEX [IX_tbl_pics] ON [dbo].[tbl_pics]
(
    [map_id] ASC,
    [deleted] ASC,
    [pic_date] DESC
)
INCLUDE ( [hidden], [pic_id] )

There is also a PK index on pic_id

The Theory

The key columns are so, because they are used in either a WHERE clause (but not used in a OR situation) or and ORDER BY.

The nonkey (INCLUDE) columns as so, because they get used in the WHERE, but because they are used in an OR scenario they can't (can't = won't improve performance) be a key column.

Are these presumptions correct? If not, what am I missing?

Thanks!

You are asking the query optimizer to produce a plan that can answer the query:

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;

The rest is fluff (including the OR pic_id = @pic_id). This will be a table scan, guaranteed, because of the low selectivity of the predicates involved (I'm sure deleted and hidden are 0/1, and map_ip I doubt it has any significant impact). The only predicate that could save the query is pic_id = @pic_id but by placing it in an OR condition you killed it's chance. No secondary index can help it, realistically. The addition of ROW_NUMBER will ad a sort, most likely, but the real damage is the scan.

This is a lost cause. Come up with realistic requirements.


Merge replication with PDAs AND P2P replication between servers? We're currently building a setup to scan tickets at an event. We plan on having 2 servers at each entrance but can't have a local link between the two.

We have scanners (sqlCE) and half of them are subscribed equally to one of the two server with merge replication, and we thought about replicating the two servers together using peer-to-peer replication. Is this possible or we should use merge replication all the way?

Heres an ugly diagram:

Since the intention is to have the same data at multiple locations, Peer-to-Peer replication would be the most appropriate. Peer-to-Peer is the only out-of-the-box technology that SQL Server provides to handle writes at multiple nodes, so it makes sense to use it. (For completeness of this answer, Peer-to-Peer does require Enterprise edition, while Merge replication does not.)

Once you get the security of the topology ironed out (I found this MSDN page very handy), it's actually not that difficult to set up. If you're considering Merge replication, chances are your schema is already is decent shape, so conforming to the requirements and restrictions for Peer-to-Peer shouldn't be a problem.

While I don't know your exact data requirements, it seems like writes would be nicely partitioned (i.e., data entered on one side probably never needs to be edited in multiple locations after that), meaning update conflicts would be extremely unlikely.


How many log files does a database usually have? I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file.

Is this true?

Having an additional log file doesn't actually hurt performance in and of itself.  That doesn't however mean that you won't have performance issues when you have multiple files.  If one of your files is on slower storage for example you could end up with intermittent performance issues which can be difficult to troubleshoot and cost you quite a bit of time.  Also frequently when people use a second log file they end up with a larger log than they actually need.  As pointed out by Paul Randal in the link in the comments this can cause you significant issues during recovery.

The important thing to remember is that the system writes to one specific point in the log, and writes sequentially.  So if you have multiple log files you are only writing to one file at a time.  This means that having more than one log file is generally pretty useless.

The only reason I've found that you actually want to have a second log file is to temporarily extend your log onto a second drive.  You've run out of space for your existing log file and don't have time to add space to that drive.  You then create a second log file on a different drive with more space.  This should be a temporary situation however.  You'll want to expand your existing space (or create a new one) and move your log back into a single file.

I wrote about it in more detail here if you are interested.


Show help of commands in SQLite shell? Any command to show the usage of a command or a SQL statement?

Such as (though none works here):

.help schema
.help .schema
.help INSERT
.help SELECT

Besides the overview shown by .help, the is no more information in the shell.

You have to use the on-line documentation of the shell or the SQL language.


How to pass output of one stored procedure to the input of another I have a stored procedure which outputs @billingInvoiceIDOut. I want to use it as input in my next stored procedure. Can I just do in my second stored procedure

INSERT INTO table ( billingID ) VALUE (@billingidout)

My first stored procedure:

BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        SET NOCOUNT ON;
        IF NOT EXISTS(SELECT billingInvoiceID
                      FROM tblBillingInvoice
                      WHERE billingInvoiceID = @billinginvoiceID)
        --  IF @billinginvoiceID = NULL
        BEGIN
            INSERT INTO [dbo].[tblBillingInvoice
                (invoicenumber,versionnumber,patientID,date,
                officeID,userID,[tblBillingInvoice].status)
            VALUE ((SELECT IDENT_CURRENT('tblbillingInvoice')),1,@patientID,GETDATE(),
                    @officeID,@userID,1)
            SET @billingInvoiceIDOut =  (SELECT IDENT_CURRENT('tblbillingInvoice'))
        END
    END TRY

or do I have to pass this parameter in exec statement as input, as I am running both stored procedures in one master stored procedure?

You must pass the value from the first SP to the second via a variable local to the master SP.  The bare bones of the code will look something like this:

create master_sp
..
declare @LocalVariable int;

exec first_sp
    @sp_parameter = @LocalVariable OUTPUT;

exec second_sp
    @sp_input = @LocalVariable;


Mysql join two tables and limit by first input? i am only starting to play with MySQL and i seem to have been stumped by one problem.

I have one table containing a number of fields including a primary id field. I have a second table containing multiple rows with the value of user_id corresponding to the first table.

Or, this

Table 1:
Id name
1 foo
2 bar
3 baz
4 qux

Table 2:
id uid
1 1
2 2
3 2
4 2
5 3

I need to make a query with a limit i.e say LIMIT 3
Then the first 3 rows of table 1 should be selected, and all rows from table 2 having uid between 1 and 3 (The ids of the first 3 rows) should be returned.

You can use something like this:

select * from
(select * from table1 limit 3) t1
join table2 t2 on t2.uid = t1.id

You can check it: http://sqlfiddle.com/#!2/1a534/7


What happens behind the scenes when adding a nullable column in SQL Server? This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column order?

No.

This is a metadata only change in SQL Server 2008. See Size-of-data operations when adding and removing columns

SQL Server 2012 can also add non nullable columns with a default without having to update all rows however there are also some circumstances in 2012 in which adding a nullable column is no longer just a metadata change.


Can I have an InnoDB master and MyISAM slaves with Full-Text for searching? I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?

Absolutely !!!
Just run ALTER TABLE tblname ENGINE=MyISAM; against all tables on the Slave that you want to have the FULLTEXT index. Afterwards, you can run ALTER TABLE tblname ADD FULLTEXT (column[,column]);.
Please be very careful not to run DDL against those tables in the Master that are unique to InnoDB that will replicate to the Slave.
I have suggested using a different Storage Engine on a Slave in my past posts:

Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Nov 16, 2011 : Log MySQL DB changing queries and users
Mar 13, 2012 : https://dba.stackexchange.com/questions/14943/setting-up-mysql-across-a-series-of-production-servers/14945#14945


MySQL replication risks for the master I have a production MySQL server, fairly heavily loaded with an INSERT-only load on one of its databases.  I'd like to attach a non-production (sitting on my desk, more or less) replication slave to it so I can run slow, expensive analysis queries on recently inserted data.

The slave would be outside the regular domain of our DBAs so there is a risk of it going down, running slowly, having an incompatible schema, erroring on replicated statements, etc.  What risks does this pose to the availability and performance of the master?

Given my limited ability to configure the master and complete control over the slave, is there anything I can do to mitigate these risks or shift them towards the (unimportant) slave?

If binary logging is already turned on and you are simply creating a new slave, unless you are nearing the limit of network bandwidth on the master (in which case, you have bigger problems), there are really no risks.  From the master's perspective, the slave is just a single client which happens to be grabbing binary logs instead of executing SQL.

If binary logging is not already on the master, this will add to the I/O and storage overhead on the master because each transaction will have to be written to the binary log file after it completes.  This is sequential writes, though, so unless you are already nearing the limits of your I/O subsystem, it shouldn't be a huge deal.


Database design : hotel room and its detail Right now i'm creating DB schema for a hotel. I stumbled upon on my hotel rooms table design. The requirement of hotel rooms would have data like : columns like room_type, room_size, room_number.
room_type can have values like economic, standard, deluxe
room_size can have values like single bed, double bed
My question is, what the best way of creating this tables? Right now i'm thinking of two ways of doing it, which is :
Type 1
Basically i create these tables :
rooms_master (room_no, room_type_code, room_size_code)
rooms_type (room_type_code, room_type_description)
rooms_size (room_size_code, room_size_description)

with rooms_type and rooms_size act as master reference table for rooms_master.
rooms_master will have FK (room_type_code, room_size_code) to PK in table rooms_type/size

or this one :
Type 2
Basically i combine two reference table (rooms_type and rooms_size) into one general table
rooms_master (room_no, room_type_code, room_size_code)
general_table (key, code, description) with column "key" and "code" act as primary keys

sample data would be like this :
|Room_No|Room_Type_Code|Room_Size_Code |
----------------------------------------
|100    |1             |1              |
|101    |2             |2              |

|key      |code|description|
----------------------------
|ROOM_TYPE|1   |Standard   |
|ROOM_TYPE|2   |Deluxe     |
|ROOM_SIZE|1   |Single Bed |
|ROOM_SIZE|2   |Double Bed |

I'm weighing the pro and cons for those two ways.
Type 1 :
Pro :
1. rooms_master will have a strong relationship with rooms_type and rooms_size
2. easier to query data
Cons :
1. too many reference table like this (or is it okay to have much tables like this ?
   because sometimes i felt overwhelmed by a lot of reference tables like these)

Type 2
Pro :
1. A lot less table, because i can just put all reference data to a general table
   (not only rooms_type and rooms_size but others tables which only act as reference data)
Cons :
1. Harder to query, but it can be done (by query room size to general table,
   the room type and then join these two queries).
2. Doesn't have a strong relationship between rooms_master and general table.
3. Prone to error, if the values in column "key" in general tables is maintained wrong.

Right now i'm thinking toward to try using type 2, because much data in there, such as room type and room size aren't going to change (there will be no addition in near future).
So, please give me some insight of what ways should i chooes or are there any better ways of doing this ?

You basically answered the question already regarding the Type 2 approach:
Looking at the cons of Type 2, you already stated that this solution would violate referential integrity and therewith a normalized database structure. So I don't recommend using it.

I'd start with analyzing the requirement:
There are different rooms and different room descriptions. Different rooms may refer to the same description. So we have a 1..n relationship between descriptions and rooms.
A single room does not only have a description. It might also have a status (e.g. occupied, booked, not available, ...) and a base price (depending on the requirement the base price may be part of the room description).
A description of a room may have attributes like room size and room type, but also attributes like smoker/non-smoker, available for wheel-chairs y/n, dogs allowed y/n, ...

I'd probably go with the following schema:
rooms (room_no, room_desc_code, room_status_code, ...)
room_descriptions (room_desc_code, Room_Type, Room_Size,...)

Depending on the number of allowed values and the "expected change rate" I'd decide either to use a constraint or a lookup table. Especially for "Yes/No" columns (e.g. dogs allowed) I'd go for the constraint. The room_status_code would most probalby refer to a lookup table, since there are several allowed values and a "risk" of new values being required in the near future.


read-only permissions except specific stored procedures Right now I have two databases behind my asp.net web application.  I'm using a single login to access both databases.  the login has full ownership of one database and read-only rights to the second database.  I'd like to set it up so that specific stored procedures from my main database can do update/delete operations on the second database.  This way I can control the exact update/delete operations that are happening.  I've been having a hard time figuring this one out, any advice would be appreciated.

You can create a second user with elevated priviliges and use WITH EXECUTE AS 'username' clause in the stored procedure body, having the procedure executed as the user with readonly permissions would be elevated to the second user who can perform modifications to the second database.

In order for this to work you will also have to grant IMPERSONATE permission to the readonly user and to set a trust relationship between databases - this can be done in at least one of two ways:
ALTER DATABASE <dbname> SET TRUSTWORTHY ON
(which might impose some security issues)
or the second option to use certificates to authorize user. This link might be helpful (it's written in the context of Service Broker, but the idea is the same considering security): LINK


Database table design for many to many I have three tables: User, Role and Project
User Table
User_ID|Email             |Name     |
-------|------------------|---------|
      1|bobtest@gmail.com |Bob      |
      2|marytest@gmail.com|Mary     |
      3|samtest@gmail.com |Sam      |

Role Table
User_Role_ID|Role_Name          |
------------|-------------------|
           1|project_admin      |
           2|project_manager    |
           3|project_leader     |
           4|project_participant|

Project Table
Project_Id|Project_Name|
----------|------------|
         1|    Project1|
         2|    Project2|
         3|    Project3|

I have created a User_Role many to many table:
User_Role Table
User_Id|User_Role_ID|
-------|------------|
      1|           1|
      1|           3|
      2|           3|
      2|           1|
      1|           2|
      3|           3|

How can I design the database to show the relationship between user, role and project with the below scenario? One participant can be assigned to multiple projects.
Scenario1: If each project must have all the same roles, one project_admin, one project_manager, one project_leader and multiple project_participant, how should I design database?
What I though is to create a Project table as:
Project_Id|Project_Name|Project_admin|Project_manager|Project_leader|
----------|------------|-------------|---------------|--------------|
         1|    Project1|            1|              2|             1|
         2|    Project2|            2|              3|             3|
         3|    Project3|            3|              4|             1|

All Project_admin, Project_manager and Project_leader will be the foreign key of User table and one participant table for the project and those users with participant role, participant_id will be the PK and Project_ID and User_ID are the FK of project table and user table.
Participant_Id|Project_Id|User_Id|
--------------|----------|-------|
             1|  Project1|      1|
             2|  Project1|      2|
             3|  Project1|      3|
             4|  Project2|      2|
             5|  Project3|      3|
             6|  Project3|      4|

I'm not sure if this is fine as if I have a new role, I need to add a new column on the project table.
Scenario2: If each project can have different roles, for example, project1 can have one project_admin and many project_participant, project2 has one project_admin, one project_leader and many project_participant and project3 has one project_admin, one project_manager and many project_participant ,how should I design database?

Drop Project_admin|Project_manager|Project_leader from the project table, and add a role_id column to the project_participants table.


Objects in v$bh but not dba_objects The Oracle documentation has a section in the 11.2 Performance Tuning Guide with the title Determining Which Segments Have Many Buffers in the Pool.  The section lists the following query:

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

If we change this to a right join we get rows from v$bh regardless of whether there is a matching object_id in dba_objects.

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID(+) = bh.OBJD AND o.DATA_OBJECT_ID IS NULL
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

My question is what do these row represent?  They have file#, block#, and objd columns, but the objd values do not exist in dba_objects.  I dumped one of the blocks and it appeared to contain an index, but I couldn't tell anything else about it.  There are plenty of rows in v$bh that do match indexes in dba_objects, so the unmatched v$bh rows can't be unmatched just because they are blocks of indexes.  Something else must be going on.

There are more unmatched objects on one of my systems, but they all have unmatched objects including the 11.2.0.2.6 system.

Update:

For the system with the most unmatched objects, the majority can be matched when comparing the bh.objd with the dataobj# field from obj$.  I'm not sure why these objects aren't reflected in dba_objects, but perhaps an examination of the view (or a separate question) will answer that.

For the remaining 391 unidentified entries, here is some other information of interest.

SELECT class#, count(class#) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY class#
  4  HAVING count(class#) > 1;

    CLASS# COUNT(CLASS#)
---------- -------------
        12            59
        13            36
        18            15
        20            20
        22            25
        30            12
        32             6
        36            28
        38             3
        42            16
        44           125

    CLASS# COUNT(CLASS#)
---------- -------------
        52             4

12 rows selected.

I eliminated the class values with only one entry.  There were 33 of those.

SELECT status, count(status) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY status;

STATUS  COUNT(STATUS)
------- -------------
xcur              361

1 row selected.

The majority of the unmatched objects will be Undo.

You can join v$bh with DBA_ROLLBACK_SEGS to identify the Undo segments involved.

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;


Do I need to reindex my table in mysql after delete? I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as engine. Do I need to change it? I think its better for concurrent insert and select. pls give your suggestions. Do I need reindexing?

Thanks in advance..

When there is a lot on insert and delete operations on a table, the table will be "fragmented", and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen)
hth


Recommendation for PGA_AGGREGATE_TARGET in ASMM What can be the value of PGA_AGGREGATE_TARGET if we configure the ASMM in oracle 12c ?.Is there any recommendation for this one.Because the sga is only tuned in ASMM.

Setting the Initial Value for PGA_AGGREGATE_TARGET

  To set the initial value for PGA_AGGREGATE_TARGET:

  Determine how much of the total physical memory to reserve for the
  operating system and other non-Oracle applications running on the same
  system.

  For example, you might decide to reserve 20% of the total physical
  memory for the operating system and other non-Oracle applications,
  dedicating 80% of the memory on the system to the Oracle database
  instance.

  Divide the remaining available memory between the SGA and the PGA:

  For OLTP systems, the PGA memory typically makes up a small fraction
  of the available memory, leaving most of the remaining memory for the
  SGA.

  Oracle recommends initially dedicating 20% of the available memory to
  the PGA, and 80% to the SGA. Therefore, the initial value of the
  PGA_AGGREGATE_TARGET parameter for an OLTP system can be calculated
  as:

  PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2 where total_mem is the
  total amount of physical memory available on the system.

  For DSS systems running large, memory-intensive queries, PGA memory
  can typically use up to 70% of the available memory.

  Oracle recommends initially dedicating 50% of the available memory to
  the PGA, and 50% to the SGA. Therefore, the initial value of the
  PGA_AGGREGATE_TARGET parameter for a DSS system can be calculated as:

  PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5 where total_mem is the
  total amount of physical memory available on the system.

  For example, if an Oracle database instance is configured to run on a
  system with 4 GB of physical memory, and if 80% (or 3.2 GB) of the
  memory is dedicated to the Oracle database instance, then initially
  set PGA_AGGREGATE_TARGET to 640 MB for an OLTP system, or 1,600 MB for
  a DSS system.


What happens when the lock limit is reached? My question is about the default isolation level: "read committed".
As I understand, during a read committed transaction, when we update a record, the system puts a lock on that record, so other transactions would wait.
Is this correct?

Other: and there's a limit in the config file for the maximum number of locks in a transaction: max_locks_per_transaction.

What happens when that limit is reached? I made a test and nothing happened. Does the system exchange the row-level locks for a full-table lock?

max_locks_per_transaction does not apply to rows, but to objects.
The fact that rows do not count is made explicit in the documentation:

From https://www.postgresql.org/docs/current/static/runtime-config-locks.html

  This parameter controls the average number of object locks allocated
  for each transaction; individual transactions can lock more objects as
  long as the locks of all transactions fit in the lock table. This is
  not the number of rows that can be locked; that value is unlimited.

The information necessary to row locking is written in the row itself.


Is it a good idea to include a value column (e.g. from measurements) in an index? Is it a good idea to include a value column into an index for a table which has many million rows?

For example a measurement table which looks kind of like this:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY CLUSTERED (id ASC)
);

ALTER TABLE measurements ADD FOREIGN KEY (parameter_id)
REFERENCES parameters(id)

When I run a simple avg query the execution plan suggest to create an index which also includes the value column.

SELECT AVG(value)
FROM measurements
WHERE measuretime BETWEEN '2015-01-01' AND '2015-02-01'
GROUP BY parameter_id

CREATE NONCLUSTERED INDEX [idx_measurements_mt_pi_v]
ON [dbo].[measurements] ([measuretime])
INCLUDE ([parameter_id],[value])

I get that it's a good idea to have an index on measuretime and parameter_id, but isn't storing every value in an index a bit over the top?

Won't this result in a massive index? As values are probably always unique because of their precision. Or is just my concept of what include does wrong?

Besides is this really necessary, as the query is running only for about 2-3 seconds with 38.000.000 row (but I guess this could have a much higher impact on a long running production database)?

I'm also thankful for any other performance tips regarding this kind of data query!

EDIT: As suggested I've added the index and ran the query again with SET STATISTICS IO ON and SET STATISTICS TIME ON again

Output:

SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 17 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'measurements'. Scan count 5, logical reads 24212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1391 ms,  elapsed time = 481 ms.

And here is the new execution plan:

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY NONCLUSTERED (id ASC)
);

CREATE CLUSTERED INDEX cdx_measurements ON measurements(measuretime);

Yes, lookup by id will take two reads instead of one. Who cares, id is looked up in singletons. Is a trade off.


Counting the number of calls of a stored procedure I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data).

How can I let a counter go up everytime the CLR sp is called? Do I necessarily have to change the sp itself?

Suggestions are appreciated.

There is no such thing as a stored procedure trigger.

If you want an absolutely accurate count, then yes, change your CLR procedure (or whatever wrapper(s) it is called from) to write an entry to a log somewhere, every time.

If "close enough" is "good enough" then you can periodically poll the DMV sys.dm_exec_procedure_stats which will tell you the execution_count at any point in time. This count is typically accumulated since the last service restart (I'm not sure if certain other activities, such as specific RECONFIGURE actions, reset this DMV as it does for some others). So, you would have to poll it periodically, and factor in any service restarts that have happened in the meantime.

Here is a simply query demonstrating how to poll execution counts at any given point in time:

USE your_database;
GO

SELECT
  s.name, p.name, p.type_desc,
  ps.execution_count,
  ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
INNER JOIN sys.procedures AS p
ON ps.[object_id] = p.[object_id]
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE ps.database_id = DB_ID();

Here is another bit of code that can clear out this DMV without a restart:

USE [master];
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

There's a complete (at least AFAIK) list here:

http://mattsql.wordpress.com/2012/06/25/dont-believe-everything-you-read-reconfigure-flushes-the-plan-cache/

How you consolidate what happens when there has been a service restart (or any of these other DMV-clearing activities) is probably a completely different question.


Group together similar queries in sys.dm_exec_sql_text I'm analyzing a 2008 Instance that supports a 3rd party app.

The app will generate the SQL Code then send it to the Database as an ad-hoc query.

I'm using this query (based on a Glenn Berry script):

SELECT
    qs.creation_time
    ,qs.last_execution_time
    ,qs.execution_count
    ,qs.total_worker_time
    ,qs.total_physical_reads
    ,qs.total_logical_writes
    ,qs.total_logical_reads
    ,qs.plan_handle
    ,qt.text
    ,qt.dbid
FROM
        sys.dm_exec_query_stats AS qs WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE
    qt.dbid >= 7
OPTION (RECOMPILE)

My problem is that I'm getting thousands of plans for very similar queries i.e.

SELECT * FROM customers WHERE name = 'bob'
SELECT * FROM customers WHERE name = 'bill'

(in reality the queries are very large and up to 3000 characters)

It's making it almost impossible to get the data into a format that's ideal for high level analysis.

Is it possible to quickly compare 2 SQL queries and see if they are pretty much the same query? I'd then pick one of the queries at random and group all the activity against that one query. (I've tried DIFFERENCE but it's very slow)

Does SQL already store a value similar to the MD5 Hash sql_handle that allows it to see if two queries are similar and consequently reuse the same plan? (If such a value exists then I'd group on that)

I don't have this problem with Stored Procedures because the same plan is being reused. It's just all the similar ad-hocs that I want to group together.

What you are looking for is the column query_hash, which was introduced in SQL Server 2008. You can find this in sys.dm_exec_query_stats. Here's a sample query to look at top 20 most common patterns:

WITH agg AS (
SELECT TOP 20 COUNT(*) AS similar_query_count, query_hash
FROM sys.dm_exec_query_stats qs
GROUP BY qs.query_hash
ORDER BY similar_query_count DESC
)

SELECT similar_query_count,
SUBSTRING(st.text, (ca.statement_start_offset/2) + 1,
    ((CASE ca.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE ca.statement_end_offset
    END - ca.statement_start_offset)/2) + 1
) AS statement_text
,st.text AS full_text
FROM agg
CROSS APPLY (SELECT TOP 1 sql_handle, statement_start_offset, statement_end_offset FROM sys.dm_exec_query_stats WHERE query_hash = agg.query_hash) ca
CROSS APPLY sys.dm_exec_sql_text(ca.sql_handle) st

Another related column is query_plan_hash, which is useful for seeing similar execution plans.


Visual studio 2010 missing BI 2010 (SSDT 2010) Where can I find a link to install SSIS 2010 ( SSDT 2010 or Business Intelligence 2010)

I have VS 2010 on my machine.

Visual Studio 2010 is no longer supported for SQL Server Business Intelligence tools. You will not find a download link unless Google or some other site might have cached it somewhere.

You will need to download Visual Studio 2012 SSDT-BI or 2013, these support BI projects for SQL Server 2012 and 2014 respectively.


Is it possible to get a "pages touched" statistic from PostgreSQL? With PostgreSQL one can measure disk reads and cache hits. Is it possible to have a clearer statistic of how many index pages were "touched" to run a query? If so, how?

You can analyze your query with EXPLAIN (ANALYZE, BUFFERS) (BUFFERS being the essential option to see details about memory utilization). A "buffer" in RAM is the equivalent of a "block" on disk, 8 kb on most installations.

Example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tbl WHERE tbl_id = 123;

With a big table tbl that's not cached, yet (not recently queried), and a btree index on tbl_id, you'll see something like:

Index Scan using tbl_pkey on tbl  (cost=0.43..5.45 rows=1 width=86) (actual time=61.360..61.363 rows=1 loops=1)
  Index Cond: (tbl_id = 12345)
  Buffers: shared read=4

Meaning that 4 data pages have been read from disk. That includes both index and table pages. For an index scan reading a single row from a big table, that's typically 3 blocks from the index and one from the table.
(For a small table you'd see shared read=2 - only 1 index page for a shallow btree index.)

If you repeat the same, you typically see the same, executed much faster and one difference:

  Buffers: shared hit=4

Meaning that 4 data pages have been read from cache, cached due to the first invocation.

The manual explains:

  BUFFERS

  Include information on buffer usage. Specifically, include the number
  of shared blocks hit, read, dirtied, and written, the number of local
  blocks hit, read, dirtied, and written, and the number of temp blocks
  read and written. A hit means that a read was avoided because the
  block was found already in cache when needed. Shared blocks contain
  data from regular tables and indexes; local blocks contain data from
  temporary tables and indexes; while temp blocks contain short-term
  working data used in sorts, hashes, Materialize plan nodes, and
  similar cases. The number of blocks dirtied indicates the number of
  previously unmodified blocks that were changed by this query; while
  the number of blocks written indicates the number of
  previously-dirtied blocks evicted from cache by this backend during
  query processing. The number of blocks shown for an upper-level node
  includes those used by all its child nodes. In text format, only
  non-zero values are printed. This parameter may only be used when
  ANALYZE is also enabled. It defaults to FALSE.

Related:

"Recheck Cond:" line in query plans with a bitmap index scan


Is it possible to disable `execute` in lieu of `exec`? We've got an issue at the moment where we've got multiple query plans because some JDBC code uses call resulting in an exec statement, and other code uses execute. Is it possible to disable execute to nudge developers into only using call/exec (particularly since call is faster)?

Community wiki answer:

No, this is not possible. I'd add this as a code quality item to their code reviews.

That said, the root cause of the multiple plans is not the different call syntax. That could also happen with different SET options, etc. Instead look at query plans and the parameterization behavior, and the Query Store (if on 2016+) to see why you are getting different plans.

See also: Slow in the Application, Fast in SSMS? by Erland Sommarskog.


How can I store user generated content in postgresql or rdbms? **In this Image I show My actual problem occurs in usr_post means how I can simplify the database table for user-generated content or how can I design the table for user_generated content, Note I want to store every individual user-generated content **
Note that I am using Django framework and its backend support by PostgreSQL database means I am using PostgreSQL database
One to Many Relation will not work in my case here is a screenshot of unexpected output

here is my model configuration
class User(models.Model):
first_name = models.CharField(max_length=40)
last_name = models.CharField(max_length=40)
usr_name = models.CharField(max_length=40)
status = models.IntegerField(default=00)
verify_type = models.CharField(max_length=10, default='E')
login_value = models.CharField(max_length=50, default='NA')
login_alternate = models.CharField(max_length=15)
country_code = models.CharField(max_length=15)
area_code = models.CharField(max_length=15)
password = models.CharField(max_length=200)
created_at = models.DateTimeField(auto_now=True)
usr_post = models.ForeignKey(UserPost, blank=True, null=True, on_delete=models.SET_NULL)

I can't comment so I'll post here.
If I understood you correctly, you wish to know which posts each user created?
If so.. create another table "user_posts" which will have a foreign key with a user_id and then all your post data, Then if you want to find which posts are associated with each user you can use a JOIN.


How to split list of values into multiple rows after semi-colon? I'm thinking this is easy, but I'm having a hard time manifesting. I have a list of values, e.g.
@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly
I want the list to be:
@bob
@sarah
@kelly
@john
@denver_colorado
@james12
@kellybelly
essentially, splitting a line after ";" and add @ as a prefix to all values

Use the SPLIT_STRING function to separate the string on each ';'
Use the REPLACE function to change all '@' to a ';' so this also signals a split.
Prefix the value with '@'
declare @s nvarchar(max)

set @s = '@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly'

SELECT '@' + value
FROM SPLIT_STRING(REPLACE(@string, '@', ';'), ';')


Create table permissions issues with SQL Server I have a database _Repository and a Login COMPANY\ANALYST_USERS which contains a handful of employees, and I am using Microsoft SQL Server 2017.

The database has some core tables in it that I want people within the Login to be able to insert rows to, update and delete rows from, but definitely not be able to drop the tables themselves. But I also want users within the Login to be able to create their own tables/views within that database and be able to drop these items they've created. Is there a permissions setup that would allow this?

In Login Properties for COMPANY\ANALYST_USERS, under User Mapping, I added them as members to the db_datareader and db_datawriter roles for the _Repository database but ensured db_ddladmin was unselected. This however stopped them from being able to create tables/views so I then granted that Login CREATE TABLE and CREATE VIEW permissions for that database. Despite this, they still don't have permissions to create tables or views.

Create a new schema that they own.  Eg

CREATE SCHEMA ANALYST
ALTER AUTHORIZATION ON SCHEMA::ANALYST TO [COMPANY\ANALYST USERS]

There’s no way to grant them the ability to create tables in a schema and reliably limit their permissions on other objects in that schema.


How to make a PostgreSQL trigger thread-safe I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't mess things up), is it possible to do it with advisory lock like this?

BEGIN
    SELECT pg_advisory_xact_lock(1);
    -- do something thread-safely here
    ...
END;

According to this post, all triggers execute as part of the same transaction as the original query, so you would not need a lock statement:
https://stackoverflow.com/questions/10727335/after-insert-trigger-in-separate-transaction


How to query Job Activity Monitor to get a list of all jobs I am trying to obtain a list of ALL jobs (SQL Agent jobs). Specifically, I am trying to obtain the ID, name, status, LastRunOutcome, Last Run, Next Run, and most importantly description, owner AND schedule (eg. Every day at 5am) of each job.

I know that you can obtain all of this information through Job Activity Monitor window and then by clicking on each job. But I need to query this information.

My research here , here and here included a few articles that give partial info, but not complete one.

Any help in getting a full list of active and inactive jobs and their info is much appreciated. Thank you.

I have this code so far, which I hope is accurate:

SELECT
     job.job_id,
     notify_level_email,
     name,
     enabled,
     description,
     step_name,
     command,
     server,
     database_name
FROM
    msdb.dbo.sysjobs job
INNER JOIN
    msdb.dbo.sysjobsteps steps
ON
    job.job_id = steps.job_id

This gets you just about everything you mention and you can modify further to suit your needs:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
--AND msdb.dbo.sysjobservers.last_run_outcome = 0


Where is `pg_temp` documented in the PG manual? I have tried various ways to search for pg_temp in the manual, but could not find it. Is it documented in the manual?

It is documented at https://www.postgresql.org/docs/current/static/runtime-config-client.html

  Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first (even before pg_catalog). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for function or operator names.


Will reloading a table cause recompiles downstream? We have a vendor that is requesting we truncate a data table, and reload it with the same data daily. The table is 90k rows and approx 10mb. The reason they want this done is so the ETL program picks up that the table has been updated. This table is used in a number of queries.

The question I have is will the changes made to this table cause a ripple effect of recompiles in query plans where this table is used. Or will SQL be smart enough to see that the table contents haven't changed, and avoid the recompiled?

Yes, we know how stupid this is.

Yes, truncating a table with 90k rows will cause a recompile for any queries that touch it.

You could stop that by turning off auto update stats, and in any update-stats jobs or rebuild-index jobs, you exclude this table. However, I'd suggest that you leave it on - after all, the table really could change over time, and you'd want your query plans to update.


Foreign key with additional constraints? There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order.

How to impose an additional constraint in DB that says Item should be "Available" (at that point of time) if it needs be to added as item_id in the Order table? An item is manually determined as "Available" and can't be derived from other fields in the database in this scenario.

One schema design (I prefer) is to add a Type column which would have "Available" and "Unavailable" fields. But how can I check the Foreign Key constraint item_id should not only be a Primary Key in Item table, its Type should be "Available" as well?

This Stack Overflow answer using check constraints seems close, but is that the only way? I feel this is a trivial thing for RDBMS, or is this is not a normalized data?

The other schema design (I don't prefer) is to have a Table called "Menu" which could have only the Items Available. The problem is that this table is going to very dynamic in nature and it keeps changing depending on the availability of items. And, I am just creating a subset table out of Items depending on its state which doesn't seem to be a nice idea.

It's easy to do this programmatically; however, how do I achieve this in RDBMS? I like the idea of Database being intelligent enough to handle this.

Original answer is wrong! See Edit 1 for the corrected version.

Original answer

An amazing solution would require a foreign key to a column in a view or a inherited table, but unfortunately PostgreSQL (I suppose that's your RDBMS because of the tag) does not have that (yet).

I think a simple change in the way you organize the data would suffice: create a table like ItemsAvailableQuantity, connecting an Item with its availability which will be references in the orders. When an item is not available anymore, DELETE it from it.

CREATE TABLE Item (
    id   serial  NOT NULL
  , name text    NOT NULL
  , cost numeric

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
    );

CREATE TABLE ItemAvailableQuantity (
    id       serial  NOT NULL
  , item_id  integer NOT NULL
  , quantity integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES Item (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
  , CONSTRAINT postive_quantity -- This constraint is the same as
        CHECK (quantity > 0)    -- checking something like `available = TRUE`.
    );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
    id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
  , bill_id integer NOT NULL
  , item_id integer NOT NULL
  , units   integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES ItemAvailableQuantity (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
  , CONSTRAINT positive_units
        CHECK (units > 0)
    );

Notice! The constraint positive_units may cause problems when your software reduces the units and reaches 0. Make it something like CHECK >= 0 if needed, or add a trigger that automatically DELETE-s rows when units reaches 0 (or less) on each INSERT or UPDATE. This would preserve the table ItemAvailableQuantity to have only actually available items, which is what we want for being referenced from the table ItemOrder.

This should solve your problem. It's not an exact answer to your question. That would involve a trigger or a CHECK calling a function as in the link you provided.

To easily see the quantity of the items then, just create a view that joins ItemAvailableQuantity and Item. If you really want then, make it INSERT-able with a trigger (see yellow-box warning).

Edit 1

Actually Order (a.k.a. ItemOrder) should reference the Item instead of ItemAvailableQuantity to avoid any problem when the Item is not currently available, as stated in the comment.

This suggest we should remove the whole table ItemAvailableQuantity and only add a column available_quantity on Item.

CREATE TABLE Item (
    id                 serial NOT NULL
  , name               text   NOT NULL
  , cost               numeric
  , available_quantity integer NOT NULL

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
  , CONSTRAINT non_negative_quantity
        CHECK (quantity >= 0)
    );

Then, to be certain of inserting only available items into orders we could just run

INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
    (SELECT id FROM Bill WHERE condition = something -- customize at will
    , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
        AND other_condition = something
    , wanted_quantity)
    ;

where wanted_quantity is a parameter passed by your software to the query.

Still, solves the problem, but is no direct answer to the question.


What does "slot 80 for LOB data type node does not exist" error in sql server mean? When I modify a table in SQL Server 2008 I get the following error:

  The Database ID 29, Page (1:235225),
  slot 80 for LOB data type node does
  not exist. This is usually caused by
  transactions that can read uncommitted
  data on a data page. Run DBCC
  CHECKTABLE

Can any one help me?

While normal columns are stored in the actual row record (the structure on disk), LOB columns are stored on a different page, leaving a pointer to that page on the original page.

When you scan a table, all the data pages will be scanned, including the pointers to the LOB pages. If the timing is right, and you're running under READ UNCOMMITTED, you might scan the data page, but when the page containing the LOB data is scanned (by following the pointer in the referring record), it might have been changed/deleted in the meantime.

It could also be caused by corruption, in which case you should be able to reproduce the error again and again. In this case, run DBCC CHECKDB to check for corruption issues. If you can't reproduce it but it happens too often, see if you can't change the isolation level.


Where can I find SQL Developer Database examples (scripts) to add data to practice? Where can I find SQL Developer Database examples (scripts) to add data to practice?
I require SQL scripts that will add tables and data on those tables in order to practice with Power Bi.

The example schemas are now on Github.
https://github.com/oracle/db-sample-schemas


Decomposing a relation into 3NF/BCNF I am having trouble decomposing a relation to 3NF/BCNF.

I have determined that for the relation:

R (A,B,C,D,E,F,G,H,I)

where:

{A -> B,C}
{E -> F}
{D -> I}
{A,D -> G}
{G -> H}

...the primary (only) key is A,D,E.

I have determined that the relation is not in 2NF, and therefore also not in 3NF and BCNF, so I must start by decomposing it into 2NF.

I have worked through the steps and come up with the following decomposition:

+----------+-----+------------+
| Relation | Key | Attributes |
+----------+-----+------------+
| R1A      | A   | G,B,C      |
| R1B      | G   | H          |
| R2       | D   | I          |
| R3       | E   | F          |
+----------+-----+------------+

Is this the correct decomposition for 3NF/BCNF?

No, your decomposition is noteven in 2nd normal form.

{A -> B,C}
{E -> F}
{D -> I}
{A,D -> G}
{G -> H}

A, D and E cannot be found on the right side of a dependency, so they are members of every key. {A, D, E}  generate all attributes, so it is a superkey and therefore the only minimal key
But your decomposition is wrong AA is not a key of R1A, because G does not depend on A. So the key of R1A is {A,G}. So R1A is not in 2nd Normal Form because B depends on {A} which is a proper subset of the candidate key.
So R should be decomposed to {A,B,C}, {E,F}, {D,I}, {A,D,G}, {G,H}. These relations are in 2nd, 3rd and even in Boyce-Codd normal form.


Slave start to generate relay log files but not master server I am settings up two new servers. One is the master and the other is the slave. Below is the config file for both.
Master DB

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=1
binlog_do_db=data1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave DB

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=2
master-host = 202.*.*.*
master-user = replicationUser1
master-password = ********
master-port = 3306
replicate-do-db=data1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

So first in the master I run this reset master command it gave me this error. reset master;
ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER. Then on the slave I first ran stop master then reset slave then start slave. THen I notice this files are generated in the slave master.info,mysqld-relay-bin.000001, mysqld-relay-bin.index and relay-log.info. In addition I have also ran this command on slave and it gave me no errors.

change master to MASTER_HOST='202.*.*.*', MASTER_USER='replicationUser1', MASTER_PASSWORD='******',

What should I do now I am quite stuck do not know how to move it next? Thank you.

You need a log-bin entry in your master my.cnf file

Per http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory.

After setting the log-bin and restarting the master, you then can run RESET MASTER; and then SHOW MASTER STATUS; to get the correct values for your CHANGE MASTER command for your slave.


Query cache size in SQL Server 2014 Is there a way to define/set/increase the query cache size in SQL Server 2014 similar to what MySQL has

http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

SQL Server doesn't have any direct analog of MySQL's query cache, i.e. an in-memory cache of actual query results. Data and compiled query plans will be cached in memory, however.

If you've got a particularly expensive query with a lot of joins, you may want to look into creating an indexed view.


Copy my data from a existing db to a new db I have a database which has my data's that I have been working for months. Now, I want to delete that db. What concern's me is that the data's that I have might come in handy in the future development. So I want to get a backup for this db, where I can get all the data's and table structures in this db as a backup and then delete it.

I am confused about how to do this.

Have a look at the mysql_dump dump utility (MySQL docu).

Alternatively you can use the Export function of PhpMyAdmin to generate a SQL file containing all you structure and data.


Oracle: Am I on a RAC? What query can I perform to determine if I am running on a RAC system?  If I'm on a RAC, I need to query from GV$* rather than V$*.

You can check the value of the CLUSTER_DATABASE parameter:

SQL> select name, value from v$parameter where name = 'cluster_database';

NAME                  VALUE
--------------------- ---------------------
cluster_database      TRUE

According to the Oracle Database Reference:

  CLUSTER_DATABASE is an Oracle RAC parameter that specifies whether or not Oracle RAC is enabled.


Install SQL Server on Debian I want to install SQL Server on Linux. From MS site I read that Red Hat, SUSE, and Ubuntu are supported but I want to use it under Debian. Since Ubuntu is based on Debian, is there a chance to succeed with installation?

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup

This worked for me on Debian 8 (Jessie) when I installed the pre-2017 vNext for dbfiddle:

apt-get install curl apt-transport-https sudo
add deb http://ftp.debian.org/debian jessie-backports main to /etc/apt/sources.list
apt-get update && apt-get install -t jessie-backports openssl ca-certificates
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
apt-get update && apt-get install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

However I tried and failed to get the RTM Linux version of 2017 installed on Debian Stretch, ultimately using Ubuntu.


SQL Server Threads and Degree Of Parallelism My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always tries to use multiple threads (if available and needed ) to run a query, regardless of whether it is a parallel or nonparallel query.

But the following statement (From book Microsoft SQL Server 2012 Internals (MS Press)) seems to state something different:

A parallel query execution plan can use more than one thread; a serial execution plan, used by a nonparallel query, uses only a single thread.

Edit: My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

For a parallel execution, multiple schedulers/processors would process the same execution request (which is broken down to several tasks) at the same time, and each of the scheduler will use (again, when there is a need) multiple workers/threads from their respective NUMA node.

In other words, parallel or not, there is a chance multiple threads are used.

Obviously there must be a flaw in my original understanding, but I don't know where the misconception is. Does my original understanding only apply when it is a PARALLEL query being executed?

Thanks

My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

A serial plan will not use multiple threads it will only use single thread to perform the task. When the serial query would start it would be bound to a scheduler and that scheduler will not perform any other task unless it completes this query. The scheduler may wait for a process to complete thus forcing query to wait.

It is also worthy noting that MAXDOP setting controls the amount of thread used for parallel processing and MAXDOP is always specified per operator in the execution plan and not per execution plan

I strongly suggest you read below article by Paul White

Understanding and Using Parallelism in SQL Server


How to query plan cache for all query's using a table variable? Is there a way to query the sql plan cache for all query's using a table variable?

As far as I can tell, a real table can still be called [@sometable] the same as a table variable, but this is still a useful filter. The definitive XQuery filter seems to be that there is no @Database attribute on a node named Object.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select qp.query_plan
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_query_plan(p.plan_handle) qp
where qp.query_plan.exist('//Object[empty(@Database) and substring(@Table[1],1,2)="[@"]')


Moving TDE database to a new database but having problems with the cert This is my first time working with TDE so hopefully I haven't done anything horribly wrong.

I'm trying to move my TDE database from one server to another.  I have created a master key on the master database of the new server.  I then restored my certificate from backup using the following command:

CREATE CERTIFICATE MyServerCert
FROM FILE = 'CertBackupFile'

When I created it originally I did not have a private encryption key so I just encrypted it with a password.

Now when I try to restore the backup of my TDE I'm getting the following error

Msg 15507, Level 16, State 1, Line 2
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

I've tried looking at the create statement but don't see where I can put in my original password.  Also when I look at sys.certificates on my original server the pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY but on the new one it says NO_PRIVATE_KEY.  So I assume I'm doing something wrong with the create.

Can anyone point me in the right direction to fix this?

You will need to create a master key on the new server - if it does not exit already.

USE MASTER;
GO
if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';

Then you have to restore your certificate:

CREATE CERTIFICATE serverCert  FROM FILE = 'C:\cert_Backups\servercert.cer'
WITH PRIVATE KEY (FILE = 'C:\cert_Backups\servercert.key'
    ,DECRYPTION BY PASSWORD = 'strong_Passw0rD')
GO

But for this to work you will have to make sure that you made a backup of the original certificate with the private key.

Backup certificate ServerCert
 to file = 'C:\cert_Backups\servercert.cer'
 with private key (file = 'C:\cert_Backups\servercert.key',
 encryption By Password = 'strong_Passw0rD')
GO

If you only have a cer file and not a key you will need to backup your certificate again


MySQL Server 5.6 only runs when making swapfile I am new to MySQL server. I have set up a dedicated MySQL server. I am aiming to install MySQL server version 5.6 and since version 5.6 is not available on the Ubuntu repository, I have to upgrade it myself. Unfortunately, upgrading the MySQL server is a pain.

Everything went smoothly up until starting the MySQL by using the command:

sudo service mysql.server start

It will issue an error:

  The server quit without updating PID file (/var/run/mysqld/mysqld.pid)

I've searched far and wide on what's causing this error. Unfortunately, I have found none. Not until a colleague of mine suggested making a swapfile. And thankfully enough it worked! Right now the MySQL server is running smoothly.

I was just wondering what caused the error and what are the drawbacks in this kind of setup in the long run?

MySQL 5.6 wants more memory

It's likely that MySQL 5.6 just needs more memory than your previous version.

MySQL 5.6 tries to allocate more memory per connection than previous versions - I believe a lot of this is attributable to the performance schema, which is now enabled by default.

Small servers (esp. ~512MB memory), or servers with a high max_connections setting, can run out of memory even if the other settings are fairly conservative.

If this is the case, you might see some messages from the OOM Killer in your kernel log - via the dmesg function or in /var/log/kern.log, indicating that it's killed the MySQL process because it wanted too much memory.

If you don't want to use a swap file, you may be able to prevent this behaviour by reducing max_connections or disabling the performance schema with performance_schema=off in your my.cnf file.

Swap space is generally a useful thing

Percona's Peter Zaitsev has discussed the merits of swap files on MySQL servers before on his MySQL performance blog.

It's a good read, but to summarise, his own preference was that "I tend to keep swap enabled on MySQL server but of course ensure there is not swapping happening (si/so columns in VMSTAT are zero or close to zero)."

The upside of having swap space is that the server won't kill off processes if it runs out of memory. The downside is that the server might spend time paging instead of running entirely from memory. It's really up to you, but I'm inclined to agree with the author.


Find the date and time that a data page was modified Is there a method (documented or undocumented) to determine the last time a data page was changed in a SQL Server 2012 database data file.

If you know the file and page number already:

DECLARE @file INT, @page INT; -- input parameters

SET @file = 1;    -- default, most systems
SET @page = 1681; -- exemplar on my system; you'll need to change

DECLARE @p NVARCHAR(15) = RIGHT(N'000' + CONVERT(NVARCHAR(4), @file), 4)
  + N':' + RIGHT(CONVERT(NVARCHAR(10),CONVERT(VARBINARY(32),@page),1),8);

;WITH x AS
(
  SELECT [Page ID],[Transaction ID]
    FROM ::fn_dblog(NULL,NULL)
    WHERE [Page ID] = @p
)
SELECT MAX([End Time]) FROM ::fn_dblog(NULL,NULL) AS y
INNER JOIN x ON x.[Transaction ID] = y.[Transaction ID]
WHERE Operation = N'LOP_COMMIT_XACT';

If you don't know the page already, you may need to do some up-front investigation using DBCC IND, DBCC PAGE, and various catalog views, especially if you have partitioning enabled, multiple files in the target filegroup, etc.


MYSQL Row Count Limits? What is the maximum row count for a table in MYSQL?

I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 bytes. Upon reaching how many rows should there be an alarm? They are intermittently used so archiving them will involve some additional work.

Which of the is advisable?
1. Archiving
2. Moving to big data
3. Leaving them as it is.

I have compile a summary of MySQL limits.  Some, related to your question:

An InnoDB partition is limited to 64TB (2**46 bytes).
A MyISAM table is limited to 2** 56 bytes, but you need to change a setting to get beyond 2**48.
A table can have 1024 partitions until some recent release, then 8192.
(BIGINT is not relevant in these limits.)

Is there alarm?

Do you have an AUTO_INCREMENT?  If so, you need to watch out for it overflowing.  600M is 28% of the way toward INT SIGNED and 14% of the way to INT UNSIGNED.
58GB is big, but certainly not the biggest ever.
You did mention performance issues, so we probably don't need to discuss things that can go wrong with tables that big
Archiving -- Based on how fast the data is accumulating, when will you run out of disk space?  It would be better to be concerned when the disk is 50% full -- this would leave room for an ALTER.

Is this a Data Warehouse application?


Mysql datadir and binlogs: same partition or not? On a MySQL cluster, does it make sense to store MySQL database files and binlogs in different partitions?

(This is about partitions on the same physical disk. If they were on different physical disks, the answer would be yes -- so to allow parallel reads and writes.)

One could think that by separating datadir and binlog dir on different partitions, if one gets filled up the other continues to have free space. But actually this doesn't matter as any of these two events would stop the replication or the MySQL server altogether. On the other hand, a single partition for datadir and binlogs would allow them to share the available free space and therefore give them more room for expansion.

For this reason it doesn't seem there's any particular drawback in sharing a partition, while there may be some advantages.  Is there something else to take into consideration?

For splitting a single physical drive into two partitions, I see only the big downside (as you pointed out):  You could run out of space on one partition, while having space left on the other.  (Many times I have seen this.)

For multiple drives, I recommend striping as being better than manually segregating this file/log/table/etc from that file/etc.  In many cases the actions are sequential (first scan the index, then reach into the data), making the manual split of no benefit.  And striping avoids the "out of space, but not really" problem.

Let's approach your question from a different angle...  Why give yourself more work (partition maintenance) when the benefit is questionable?

If you have one HDD and one SSD, then put the logs (sequential access) on the HDD and the data+indexes (random access) on SSD.


Elegant Database Design I'm trying to make "online Judge Environment"(like SPOJ). In which users have facility to see their past submissions for each question, their best code performance(like running time, if they succeed to answer correctly). User have their USENAME(which will be unique) and PASSWORD. Around 10,000 users are expected.

My Effort : there will be one 'login' table in which each column contains USERNAME and PASSWORD. For each question, there will be separate table, in which each column contains USERNAME, their respective code file, status(correct answer, compile error, runtime error,etc.), running time(if any). every question table may contain more than one entry for same USERNAME.

Is there any better design? Should I store code files(text files) in database itself, or somewhere in file system and just give location path of file in table?

Your usernames are to be unique. Good, they should be. But now you seem to think that Username would make a good primary key. Not necessarily. One important characteristic of a PK is stability. Unless you wish to implement a "one you've chosen a username you're stuck with it forever" policy (and that is a decidedly user-unfriendly policy), someone may want to change their username. Doing so when that username is scattered all over the database is going to be difficult.

I am not a fan of design rules that demand a surrogate key for every table. But typically 50% or more of tables should have surrogate keys. This is one of them.

And I certainly hope that when you say the login table will contain the password, you do mean the hash of the password, don't you?

@ypercube speaks for all of us about the "for each question, there will be separate table" idea. Not good.

Now as for the user's code. For that you have choices. You don't mention the target DBMS (if you even have one at this point) but some handle text or clob fields better than others. The question of handling actual disk files is one you will have to research yourself. Will those files reside in a file server, within a version control system, just a dedicated folder on some disk somewhere? In any case, what are the OS-based and/or network access privileges that will be needed? You get the idea. There are a lot of questions unrelated to databases that must be answered.

Not to say that one is superior to the other, but unless you or someone on your team has done this before, you may want to start with storing the code in the database. That will probably get you started faster and you can move to system files (if you determine it to be the better option) more or less at your leisure.


AlwaysOn Secondary Replica DB-size I just noticed something peculiar. We're using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, it shows a modification time of several months ago. Similarly, using sp_helpdb 'DBNAME' in the secondary replica, it claims the space is significantly less than in the primary node.

Still, the DB shows as being Synchronized in SSMS object explorer, and upon reading the data in the DB, it's clear that the latest data is being replicated properly.

This is not a problem, I would simply like to understand what's going on, as I can't seem to find this information by googling for it. Why doesn't the space usage seem to be updated properly? Is it a question of permissions, or a symptom of the read-only mode itself, or what?

Thank you!

.mdf and .ldf files do not show the last modification date in the way you think it does.

The file-system date reflects the last time the instance started the database.  Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database.

Can you add the output of sp_helpdb 'dbname' for both instances to your question?  Also add output from:

SELECT d.name
    , mf.name
    , mf.type_desc
    , mf.size * 8192E0 / 1048576 AS FileSizeMB
    , mf.state_desc, mf.max_size * 8192E0 / 1048576 AS MaxFileSizeMB
    , mf.growth  * 8192E0 / 1048576 AS FileGrowthMB
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = 'dbname';

The following query, when run from the database in question, will show the number of megabytes allocated and used:

SELECT sum(total_pages) * 8192E0 / 1048576 as TotalMB
    , sum(used_pages) * 8192E0 / 1048576 as UsedMB
FROM sys.allocation_units;


MySQL select by primary key against 100M of records within 30ms I have no expirience with MySQL and database performance tuning in general.

Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within 30ms per request in average.

Some details:

PK is 16 bytes (it's UUID stored as binary(16)).
Each record is approximately 100 bytes and can be shrinked to 24 bytes if normalized (though cardinality is approximately 10 to 1 and we need joins in this case).
Table should be writeable. There may be 1 update per 1 select, though updates can be deferred and their performance is less critical.
I could allocate up to 8GB for mysql needs.
Disk is a regular HDD (this concerns me the most, because seek time is about 9ms)

Please, suggest, what storage engine is preferable, what mysql parameters should be tuned. Also suggest, please, some technics (perhaps selecting groups of records with where in() against record by record, etc.) to improve performance.

Thanks in advance.

The problem here is "large number of queries" which you likely have other ideas of than me (who says sure - you need a lot of hardware for 30.000 queries per second).

Generally otherwise - yes. This is possible.

Lots of memory needed ;) keep it all in memory.

O rget a proper disc subsystem. Seriously, your "regular hard disc" is dead slow. Like terribly slow. SSD area good and low cost alternative (roughly 550 times as fast as your hard disc in random access). Unless you RAM up to keep it all in memory your disc IO is the typical bottleneck... and SSD are the cheapest solution. Unless you REALLY up your RAM you simply have no other alternative than gettig some decent IO capabilities, the (end user level) hard disc (i.e. a non SAS 15k high performance disc) just wont get you far.


Identify the type of a SQL Server backup Long story short, we had a hosting plan on www.hostforlife.eu (btw, terrible service even on more expensive packages, plus they lost ALL files on their servers and we had to restore our backups as they lost those too) and for db backups we were opening tickets so they would take them manually for us.

When we moved to another host (the plan with hostforlife had expired) and tried to restore the SQL Server 2014 backup we couldn't do it (our host actually).

The backup had no extension and we are not sure what type of backup it is (a couple of methods were tried but didn't work).

We contacted their support for this but because we had no active plan they refused to even tell us how the backup was taken.

We could pay for a 3 month hosting but I do not want to give any more money to this crappy company.

So, my question is, how can we identify the backup type to restore it?

Restore Headeronly From Disk = '...'

There's only one SQL Server backup format. If this doesn't return anything useful then it's not a SQL Server backup file and may be something else (I can only imagine what; perhaps you'd have to open it in a text editor and see what the first few characters are).

It will return a BackupType column:

1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial

That would help you determine the order in which you need to restore (usually database with norecovery, then differential database with norecovery, then transaction log, then with recovery).

There are other methods to identify the file using PowerShell and SMO but this is the quickest.


View created using Join Queries taking too much to load I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables.
Now the problem is every time i am selecting the view with even 2-3 columns without adding any further condition or clause, it is taking more that 45-50 seconds to load.

No of records in the view : around 250

No of Columns in view : 45-50

My.cnf seems okay, i have checked it using mysqltuner perl script.
I have tweaked the relevant parameters in my.cnf , but no significant effect noticed in query response time.

Engine Type : InnoDB, Foreign key constraints applied.

Mysql version : Percona mysql 5.6

Any Ideas?

Thanks in Advance.

Your view will join all the tables involved in the view definition in order to create the required view.

First, of course, you should check that your tables are properly indexed so that the joins are as efficient as possible when creating the view.

However, joining 25 tables with 30 inner joins is a relatively expensive undertaking, resulting in your case with the 45-50 seconds of delay before getting results.

A good general strategy is to break a big slow step into smaller faster steps. I would consider whether it is possible to create 4 or 5 temporary tables into which you can insert smaller data sets.  Then once the temporary tables exist you could join them together to get a final result.

This does require some design effort in creating the temporary tables so that the individual tables create the proper subsets of data for each query.

You can create a Materialized View which may work for you depending on your requirements.  Rather than explain the details please look at:

http://www.fromdual.com/mysql-materialized-views

You must write code to populate the materialized view and to refresh the data as needed.
If you must have the most current data, then you will need to write triggers to provide the needed updates.


Error Code 1117 Too many columns; MySQL column-limit on table I have a table with 1699 columns and when I'm trying to insert more columns I get,

  Error Code: 1117. Too many columns

In this table I have only 1000 rows. For me the most important thing is the number of columns. Are there any limitations on the table? I want to create 2000 columns. Is that possible?

Why would you need to create a table with even 20 columns, let alone 2000 ???

Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval.

If a 2000 column table undergoes SELECT * FROM ... WHERE, you would generate large temp tables during the processing, fetching columns that are unnecessary, and creating many scenarios where communication packets (max_allowed_packet) would be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table. How much more so a 2000 column table.

In terms of mysql, you would have to compensate for such bad design by setting options comparable to DB2 Stage2 Processing. In this case, those options would be

max_allowed_packet
tmp_table_size
max_tmp_tables
max_heap_table_size
max_length_for_sort_data
max_sort_length
sort_buffer_size
myisam_max_sort_file_size
myisam_sort_buffer_size

Tweeking these settings to make up for the presence of dozens, let alone hundreds, of columns works well if you have TBs of RAM.

This problem multiplies geometrically if you use InnoDB as you will have to deal with MVCC (Multiversion Concurrency Control) trying to protect tons of columns with each SELECT, UPDATE and DELETE through transaction isolation.

CONCLUSION

There is no substitute or band-aid that can make up for bad design. Please, for your sake of your sanity in the future, normalize that table today !!!


Design feedback on my first database Couldn't find a newbie forum; hope this is in the right place.

In my quest to learn more about databases, analytics, and creating service APIs to work with them, I decided to scrape an online archive of Jeopardy data and plan to reconstruct the data into a functional db.  Pretty sure this has been done before, but learning is the primary objective here :)

I'd like to get the data structured so I can run some advanced statistics on it. To do so, I'll need to query things like a player's score at any given point in the game, see who picked which clues and in what order, identify (and grade) players' wagering on daily doubles & Final Jeopardy, etc. If all goes to plan, it would be neat to expose this via public API so others can play with it too.

There's a decent amount of data here, though it's not very big compared to enterprise dbs:

6000 distinct games
~60 clues per game, so ~360k total unique clues.
12000 players, which includes duplicates (ex: Ken1, Ken2, Ken3)

Below is the latest draft of my data model (original SVG, updated SVG) based on the data available from j-archive, the general structure of Jeopardy games, and my limited understanding of database design. Appreciate any feedback -- especially on the clue_response table, which is the only table with transactional data.

Data is currently stored in an sqlite3 db.  Will probably migrate it to MySQL once I settle on a structure.

Having numbered columns is an anti pattern. Your Game table shows it.

The relationship between Game and Player is many-to-many, even though the cardinality is fixed from a game's perspective. Resolve the problem by adding a new table ("Participant"?), with foreign keys to both Game and Player.

To see how it helps, try writing a query to count the number of games an individual was in using each design.

The relationships to Player Round Result should be to this new table, not to Game and Player separately. This ensures a Player must necessarily have been in a Game to have a result recorded. The foreign key columns in Player Round Result will have the same names and types, but the foreign key constraint declaration will reference a different target.

Further, I'd suggest the table currently called Player would be better as Person, since the values it holds are for the human per se. The new table I suggest above would be well-named as Player as it is all about a person participating in a game.