Does PostgreSQL always initialize database of same size? Let's say I run CREATE DATABASE abcd, PostgreSQL will create an empty database and if I run SELECT pg_size_pretty(pg_database_size('abcd'));, I get size of the database, let's say 8000kB. What I want to know is will the size of the empty database always be the same no matter what or does it depend? I specifically need to know if empty database size would change over time (create database 1 year from now) or if it would change over quantity (10000th database). I can't test for those cases and I couldn't find any information on that. Thank you.

Absolutely not. The database is formed from the contents of template1 a "master database." Adding things to template1 will directly affect the contents of the new database.
From the docs, Template Databases

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

However, you can create a copy of template0 which we hope is a virgin database, also from the docs links above.

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a "virgin" user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a virgin database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on
Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.
To create a database by copying template0, use:
CREATE DATABASE dbname TEMPLATE template0;


Can I reference a specific column in a remote table (MySQL)? I have two databases (MySQL) on two different servers, and one table references a table in the other. However, I am unable to point the reference to a specific column in the remote table for some reason. Can someone help?

Thank you!

SELECT a.foo, b.bar
    FROM db1.tableA AS a
    JOIN db2.tableB AS b  ON a.something = b.whatever;

This assumes both "databases" are on the same instance of MySQL on the same server.  Otherwise, you need to reword the question.


Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX) I have a SQLCLR stored procedure with the following signature in Visual Studio 2013:

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString ExecSql2Json(SqlString sql)

Or alternatively I've tried:

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlChar ExecSql2Json(SqlChar sql)

Either way the parameter and return type gets defined as NVARCHAR(4000). I'd like them to be NVARCHAR(MAX).

I know I can manually write my own CREATE PROCEDURE statement to have fine grain control. Is there any option anywhere to change the deploy DDL generated by SQL Server or do I just have to manually add a script to drop and read the UDF with the proper signature?

You need to specify a SqlFacet for the return value. This is the same as specifying a SqlFacet for an input parameter, except you prefix it with return:

For (max) length, use MaxSize=-1 in the SqlFacet attribute, example follows:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        IsDeterministic=true,
        IsPrecise=true
        )
    ]
    [return: SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)]
    public static SqlString ScalarFunction1
        (
            [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)]
            SqlString input
        )
    {
        return input;
    }
}

The T-SQL script generated for this SQLCLR function is:

CREATE FUNCTION [dbo].[ScalarFunction1]
(@input NVARCHAR (MAX))
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [Library].[UserDefinedFunctions].[ScalarFunction1]


Database design for payment feature system I am building a system where visitors can buy modules from my website. For now I only have 3 available, but of course this number will keep growing.

In order to facilitate this in my MySQL database I am doubting between one of these two architectures, mainly concerning about performance and scalability.

I have a user table in my solution which consists of the user_id, email, password and other fields.

Approach one:
One table called "user_modules" where a column represents each module, and a boolean (1 or 0) corresponds to either they have the module or not.

+---------+----------+----------+----------+
| user_id | module_1 | Module_2 | Module_3 |
+---------+----------+----------+----------+
|       1 |        1 |        0 |        1 |
|       2 |        1 |        1 |        0 |
|       3 |        1 |        1 |        1 |
+---------+----------+----------+----------+

Approach two:
Two tables:
a modules table where all the different modules are in

+-----------+-------------+--------------------+-------+
| module_id | module_name | module_description | price |
+-----------+-------------+--------------------+-------+
|         1 | test        | test2              |     5 |
|         2 | demo        | demo               |    10 |
+-----------+-------------+--------------------+-------+

user_modules table where every row is a user_id coupled with a module_id.

+---------+-----------+
| user_id | module_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         1 |
|       2 |         5 |
|       3 |         1 |
+---------+-----------+

Now that I typed everything out, it seems clear to me that approach two is the better option. However, are there other suggestions or other possible improvements here?

The second approach conforms to Third Normal Form (3NF) which is always a better place to start than something which is un-normalized (0NF) like your first option.

Converting repeating columns into rows in another table is perfectly scalable and will help keep your source code more stable because you won't be adding columns to your (option 1) table when you create new modules.

Furthermore, the second option is more powerful because it lets you easily add other information about the purchase of a module by a particular user.  For example you could add purchase_date to your user/module table.  You could also add the actual_purchase_price to that same table.  This would allow you to keep track of things like changes to the price you charge for modules or whether you sold a particular module to a particular user at a promotional price.


Function without parameters, and calling a function inside I'm new with PostgreSQL and feeling quite overwhelmed..
What I'm trying to achieve is something like this:
Wanted outcome

year
descrip
percent

2019
Word word word
0.10

2018
Word word sad
0.80

2017
Word empty word
0.15

etc
Word word word
0.41

Where percent is function_A divided by function_B** (function_A / function_B).
I already have the functions

function_A has two parameters : year (extracted from status_date) and code. Returns the number of orders defined by the previous
parameters.
function_B has one parameter: year (extracted from status_date).
Returns the total number of orders by year.

Function
This is what, wrongly, I did so far - including code from nbk's original answer:
The function shouldn't have any parameters.
CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc text, percentage bigint) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip text DEFAULT 0;
    percent bigint DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM created_dt) as numeric),
                order_desc as text,
                ROUND(function_A(inyear,incode))::decimal / function_B(inyear) as percent
        FROM orders o
        WHERE EXTRACT(YEAR FROM o.creation) = inyear
        AND o.order_code = incode
        AND o.descrip
        GROUP BY EXTRACT(year FROM o.creation), o.code
        ORDER BY retyear ASC;
END;
$$
LANGUAGE plpgsql;

To call the function I expect to use:
SELECT * from summary_code()

My data looks something like this:
'orders' Table
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    descript CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Records
INSERT INTO orders
(id, code, service_id, descript, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I'm tired','2019-06-16 11:12','2019-06-18 14:08',1);

I'm sorry if this comes as a little cheeky, but I don't know where to start.

CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    descript CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

INSERT INTO orders
(id, code, service_id, descript, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I''m tired','2019-06-16 11:12','2019-06-18 14:08',1);

CREATE FUNCTION function_A(in numeric, in int,out noorder int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1 AND "code" = $2 $$
    LANGUAGE SQL;

CREATE FUNCTION function_B(in numeric,out noorderyear int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1  $$
    LANGUAGE SQL;

CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc TEXT, percentage numeric) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip TeXT DEFAULT 0;
    percent numeric DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM status_date) as numeric) teayear,
                MIN(descript) as descrip,
                ROUND(Max(function_A(CAST(EXTRACT(year FROM status_date) as numeric),code))::decimal / function_B(CAST(EXTRACT(year FROM status_date) as numeric)),2) as percent
        FROM orders o
        GROUP BY teayear
        ORDER BY teayear ASC;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM summary_orders()

retyear | order_desc           | percentage
------: | :------------------- | ---------:
   2018 | Fulfilled blue squit |       1.00
   2019 | Requested blue kit   |       0.60

db<>fiddle here


Performance about this simplified where-condition query I have a query in SQL Server 2008 like this:

SELECT
        a.type,
        a.name,
        a.startDate,
        a.endDate,
        b.key          AS key,
        b.is_locked    AS is_locked,
        NULL           AS idx,
        NULL           AS page_count
FROM
    jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

WHERE
    a.status IN (0,1,7)
    AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    AND a.type in (15,17,19)

UNION ALL

SELECT
        a.type,
        a.name,
        a.startDate,
        a.endDate,
        NULL           AS key,
        NULL           AS is_locked,
        c.idx          AS idx,
        c.page_count   AS page_count
FROM
    jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

WHERE
    a.status = 5
    AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    AND a.type in (15,17,19)

I simplified the query into like this:

SELECT * FROM
(
    SELECT
            a.type,
            a.name,
            a.startDate,
            a.endDate,
            b.key          AS key,
            b.is_locked    AS is_locked,
            NULL           AS idx,
            NULL           AS page_count
    FROM
        jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

    WHERE
        a.status IN (0,1,7)
        AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

    UNION ALL

    SELECT
            a.status,
            a.name,
            a.startDate,
            a.endDate,
            NULL           AS key,
            NULL           AS is_locked,
            c.idx          AS idx,
            c.page_count   AS page_count
    FROM
        jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

    WHERE
        a.status = 5
        AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
) t
WHERE t.type IN (15,17,19)

I would like to know if the simplified query has better performance or not than the original one?

Note: if there is syntax error, please let me know in the comment section because I typed this query by my hand.

A bit of additional OOT question:
What is the english term for the process of simplifying an expresion or logic like above which by reducing the the two same where condition (t.type IN (15,17,19)) into one ?

Another example is like this:

R = (x+2)/2 + (x+3)/2

you can simplify it by removing the duplicate "/2" become one like this:

R = ( (x+2) + (x+3) ) / 2

You can answer the OOT question in the comment section.

Thanks in advance.

You dont need subquery, make it one-time I/O, you will get better performance:

select * from jobs
where (
       (status IN (0,1,7) and startDate > getdate())
       or (statu=5 and startDate=getdate())
       )
and type in (15,17,19)


How to find how many number of transactions are happening in SQL Server/SQL Server database in a day? I have to migrate my SQL Server on a particular public cloud, which is charging/licensing on the basis of number of transactions happening on the SQL Server.

I am aware of the Performance Counters for SQL Server:Databases - Transactions/Sec AND Batches/Sec.

Do I simply convert these transactions per second data to hours, or is there some other method to find out total number of transactions going on in SQL Server.

The Perfmon counters for Transactions/Sec and Batch Requests/Sec are cumulative counters, meaning they keep going up after the SQL Server starts up.

To get a count per day since startup, divide the counter output by the number of days that the server has been up. An easy way to do that is to check the creation date of TempDB - here's an example query, but keep in mind that rounding will be a problem if the server's been up for less than a day or two:

SELECT DATEDIFF(dd, create_date, GETDATE())
FROM sys.databases
WHERE database_id = 2;

To get daily numbers going forward, consider saving the contents of that counter to a table. Then you can take a day's metric, subtract the day before, and you'll get the counter delta for that one day. (I use sp_BlitzFirst and the Power BI Dashboard for DBAs to do this with 15-minute granularity. (Disclaimer: those are open source tools that I wrote.))

Unfortunately, if you try to do daily sampling, it can be inaccurate due to:

The server restarting or clustered SQL Server service failing over
The Availability Group or database mirror transitioning roles (failing over) does not reset those counters, but you'll need to add up the transactions or batches from both replicas
If you're combining metrics from multiple replicas, keep in mind that you've also probably got monitoring software, and the monitoring software's queries are also going to be counted


Share the database between vmware guest and host I wanna Access the MySQL Database founded in Windows 10 from VMware Workstation MAC OS X guest and give the guest ( MAC in VMware ) only permissions to just one database from the host (Windows 10),

I mean I want the Mac guest can access to one database from the host (NOT For ALL Databases).

I am connected in the guest by bridge network adapter.
I know I must do a change in /Applications/XAMPP/xamppfiles/etc/my.cnf but don't know what. ( I am using XAMPP 7.1.6, it's doesn't have bind_address ).

I want the database always up in my machine ( Windows 10 ) and when I run the VMware mac guest ( MAC ) so the guest can access to this database.

( I mean I want the database to be accessed in the host ( although when VMware is off ) .

I have already made a database in PhpMyAdmin in the host ( Windows 10 ), How can I access to just this database from the MAC guest in the VMware.

I wanna mention I don't want to give permissions to this database to another computers in my Network, or another computers that have my IP Address.

I appreciate any help. Thanks.

You have to comment out the single line in the `my.cnf:

 bind_address = 127.0.0.1

By default mysql server listen only on the localhost for security reason. When that line is commented, mysql server become listening on the all available interfaces because default value is bind_address = *. Now you can connect to the mysql from the host OS by IP-address of the VM.

To restrict guest access to the single database/schema you have to perform the next query:

GRANT ALL PRIVILEGES ON database.*
   TO 'guestuser'@'1.2.3.4'
   IDENTIFIED BY 'somepassword';

where 1.2.3.4 is an IP-address of your host OS.

If you have dynamically assigned IPs on your LAN then you have to allow connections from the whole subnet your IPs are belongs to:

GRANT ALL PRIVILEGES ON database.*
   TO 'guestuser'@'192.168.%'
   IDENTIFIED BY 'somepassword';

where 192.168.% means 192.168.0.0-192.168.255.255


Recover data from bak Oracle 9i file Our customer gave us a .bak archive with name file_decrypted_379299.bak which we know was created with Oracle 9i in "hot" style.

We tried to recover the bak file with Oracle 9i RMAN but we don't know how to make reference to this file.

We have not found any specific reference, documentations explains we need specific parameters from how this bak file was created... but customers says "normal" parameters.

We just installed oracle 9i with default database configuration, we don't want database instead we only want SQL tables and data.

Do you know how to ask for these parameters?
How to specify in RMAN the location of this file?

Any tool to recover data without installind the database?

Thanks for your help

Are you sure this is an RMAN backup set? Maybe it is just a dump file made while the database is online. For 9i you only had exp and not the newer expdp available to make dumps so you could try to import it with imp and see what the output is:

imp system/password@abc FULL=Y FILE=file_decrypted_379299.bak


How to ensure pg_stat_activity shows all my queries? I am working with pg_stat_activity in postgres. It is working and I can see queries in this view after clicking the play button in datagrip. However, some queries I definitely executed (see screenshot, and look at the first query) are not shown at all. I need statistics of them (so really all my queries). How to ensure pg_stat_activity shows all my queries? Do I need to add additional settings?

pg_stat_activity is not an auditing facility.  It only shows one query per connected session.  Either the query which is currently executing, or if the connection is idle than the immediately prior query.

If you want to see historical queries, use a different tool. Maybe  pg_stat_statements, or log_statement=all.  Or any of a large number of third-party tools.


frequently dropping table in sql server Kindly help me understand my below concern.

In our current business requirement. We have to pull the complete data everytime. We have to truncate out table and load it with 20million of data.

I want to know instead of truncating. If I do a drop and load the table with select * into. Will this frequent dropping of table have any adverse affect on database performance in long term. Like high fragmentation or something related to pages.

Be noted that we cannot use merge as per our current data.

Just wanted to know the adverse affect of frequent drooping and creation of tables.

There is a problem with the way you are doing this now, the huge amount of data just seems like a waste. Tons of logging and its just a nightmare for i.os. I would personally look into merge statements instead.

This performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

But if you are going with this solution, I would believe the better option is just to disable the indexes on the table. Then truncate the table and then load. This should allow faster inserts. You will need to reenable the indexes though and then rebuild them.


MySQL Database and Table Loss I have installed mysql and XAMPP on my system. I wanted to remove the mysql service of XAMPP. After removing it, I tried to install the new service for mysql using "MySQLInstanceConfig.exe". but now I can't see any of my databases or tables. Have I lost all my data? The data folder in mysql directory is full.

This is a picture of my data folder. I have all the data here. but mysql can not read them.

Is there any way to make the mysql read these !?

If the mysql service is up right now, shut it down

C:\> net stop mysql

You need to create my.ini

Go to  C:\Program Files\MySQL\MySQL Server 5.5

You should see a bin folder and the data folder you have in your question

Create my.ini in C:\Program Files\MySQL\MySQL Server 5.5

I noticed you have

ibdata1
3 innodb log files
binary logs that start with mysql-bin

Make sure you have these options in my.ini

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=3
innodb_log_file_size=256M
innodb_buffer_pool_size=768M
log-bin=mysql-bin

You need to start the service manually

C:\> cd "C:\Program Files\MySQL\MySQL Server 5.5\bin"
C:\> net start mysqld

By default, when starting up mysqld like this, mysqld.exe expects the my.ini to initially be in its parent directory.

If mysql starts successfully, you should see mysqld.exe in the processlilst of the Task Manager and the file clarinet.err should have today's date. You can shut it down using:

C:\> cd "C:\Program Files\MySQL\MySQL Server 5.5\bin"
C:\> mysqladmin -uroot shutdown

Now configure mysql service so that it uses C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe


Generate due dates using function in a select statement I want to create a semi - monthly amortization schedule by using a function.

Here is my function:

CASE WHEN day(@dt) < 16
THEN
   CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH
   THEN
      eomonth(@dt)
   ELSE -- SET AS 30
      dateadd(day, 30-day(@dt), @dt) end
ELSE
   dateadd(month, 1, dateadd(day, 15-day(@dt), @dt)) -- SET AS 15
END

Let's say I already have a table of schedule like this

instno |  amount   |  duedate
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  NULL
3      |  1000.00  |  NULL
4      |  1000.00  |  NULL
5      |  1000.00  |  NULL

I want to fill the remaining 4 installments using the function I created or similar.

So far, I have tried doing,

select *,
    dbo.duedategenerator(LAG(duedate,1,'1-30-2020') OVER (ORDER BY b.ilno))
from table

Output should be like this:

instno |  amount   |  duedate
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  2-15-2020
3      |  1000.00  |  2-29-2020
4      |  1000.00  |  3-15-2020
5      |  1000.00  |  3-30-2020

One solution uses recursion:

Setup

CREATE TABLE dbo.Test
(
    instno integer PRIMARY KEY,
    amount money NOT NULL,
    duedate date NULL
);
GO
INSERT dbo.Test
    (instno, amount, duedate)
VALUES
    (1, 1000.00, '20200130'),
    (2, 1000.00, NULL),
    (3, 1000.00, NULL),
    (4, 1000.00, NULL),
    (5, 1000.00, NULL);

CREATE FUNCTION dbo.SemiMonthlyAmortizationScheduleDate
    (@dt date)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        ScheduleDate =
            CASE
                WHEN DAY(@dt) < 16
                THEN
                    CASE
                        WHEN MONTH(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH
                        THEN EOMONTH(@dt)
                        ELSE DATEADD(DAY, 30 - DAY(@dt), @dt) -- SET AS 30
                    END
            ELSE
               DATEADD(MONTH, 1, DATEADD(DAY, 15 - DAY(@dt), @dt)) -- SET AS 15
            END;

Solution

WITH
    R AS
    (
        SELECT
            T.instno,
            T.amount,
            T.duedate
        FROM dbo.Test AS T
        ORDER BY
            T.instno
            OFFSET 0 ROWS
            FETCH FIRST 1 ROW ONLY

        UNION ALL

        SELECT
            Top1.instno,
            Top1.amount,
            duedate = SD.ScheduleDate
        FROM
        (
            SELECT
                T.instno,
                T.amount,
                R.duedate,
                rn = ROW_NUMBER() OVER (
                    ORDER BY T.instno ASC)
            FROM R
            JOIN dbo.Test AS T
                ON T.instno > R.instno
        ) AS Top1
        CROSS APPLY dbo.SemiMonthlyAmortizationScheduleDate(Top1.duedate) AS SD
        WHERE
            Top1.rn = 1
    )
SELECT
    R.instno,
    R.amount,
    R.duedate
FROM R
ORDER BY
    R.instno;

Try it online at db<>fiddle demo

The ROW_NUMBER thing is just a way to get TOP in the recursive part of a CTE see Performance Tuning the Whole Query Plan for details on that if you need it.

If you can use SQL CLR, a faster solution is available using the technique I show in my answer to How can I eliminate this costly index seek operation from my query?


SQL server alwayson 3 Node Primary Down I am using three node alwayson Availability Group SQL 2012 Enterprise Edition.
2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR
I need to perform a DR switchover with all DC down And DR database up for applications.
Can this solution work or I need to use Log Shipping as third node instead of alwayson AG node.
Please help on this.
Thanks

Yes , it should work considering 3rd node is part of same windows failover cluster.
I believe with 2 nodes you are trying to achieve HA and if 3rd node is in different data center it's ideal for DR.
.
Also I am not exactly sure when you say taking DC down, if both nodes down, then test the case for quorum as I guess you might be on windows 2012 and things are tricky there for odd/even majority of votes


dbcc checkdb differences between physical_only, full and data_purity I'm getting lost about one thing. According to MS docs full checkdb with no option does column-value checks, so what's the gain of running data_purity? By 'gain' I mean how detailed checks are being done, I'm not considering performance now.
Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?
Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB:

DBCC CHECKDB('AdventureWorks2019') WITH PHYSICAL_ONLY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 39189.

DBCC CHECKDB('AdventureWorks2019') WITH ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.

DBCC CHECKDB('AdventureWorks2019') WITH DATA_PURITY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.

Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?

Yes, that's right. You only need to enable the DATA_PURITY option running CHECKDB on a pre-2005 created database once, if the result is clean then subsequent CHECKDBs without the option will automatically include the check. And it's part of the standard CHECKDB process in 2005 and above-created databases.

Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB

That's because it's the same check process - the option wouldn't have any effect in a SQL2005+ created database because CHECKDB does that check as standard.


Join tables but keep matches in a single row I have the following tables structure in a PostgreSQL DB:

These tables have this content:

So if I want to retrieve the SB associated to each server I'll need to join these 3 tables:

Server with Map_ServerSB on server.Id = Map_ServerSB.IdObj1
Map_ServerSB with SB on Map_ServerSB.IdObj1 = SB.Id

The thing is that, this way, I'll get 2 records.

However, I would need to get a single record; something like this:

Note: We won't have more than 2 records in SB for each server (there could be zero, one or two records for each server)

Is there any way to accomplish that?

Thanks!

You need either 2 more joins, or to do a GROUP BY first (to collapse the two rows into one) and then join Map_ServerSB and SB (twice). In a query:

SELECT
    s.Id     AS ServerId,
    s.Name   AS Server_Name,
    sb1.Name AS SB_1,
    sb2.Name AS SB_2
FROM
    (
        SELECT
           ms.IdObj1,
           MIN(ms.IdObj2)
               AS sb_id_1,
           CASE WHEN MIN(ms.IdObj2) <> MAX(ms.IdObj1)
               THEN MAX(ms.IdObj1) ELSE NULL
           END
               AS sb_id_2
        FROM
            Map_ServerSB AS ms
        GROUP BY
            ms.IdObj1
    ) AS ms
    JOIN Server AS s
      ON s.Id = ms.IdObj1
    JOIN SB AS sb1
      ON ms.sb_id_1 = sb1.Id
    LEFT JOIN SB AS sb2
      ON  ms.sb_id_2 = sb2.Id
  ;


Grant dbcreator only for databases matching prefix In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON;

            DECLARE @event_data XML;
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON;

            DECLARE @event_data XML;
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO


Cannot Create Backup Device - SQL Server 2008 R2, Error 1326 (permissions look ok) Trying to create a backup device for a new db. I'm using management studio and just using the "New Backup Device..." wizard and giving UNC path to the backup directory.

There is a pre-existing db on another server, it has backups working to the same Samba share.

I'm setting up backups for a new db on a new node, same vlan/subnet using the same share, same domain account for the SQL service user.

I can access and read/write just browsing the UNC with Windows Explorer
The share is presented as Samba from Gluster storage on RHEL.

Node1: Working node:

Service user: sqluser@domain.local, Path:\\shr01\main-storage\files\dbbackup\db1\full

Node2: Not working node:

Service user: sqluser@domain.local  Path: \\shr01\main-storage\files\dbbackup\db2\full

Both directories have the same permissions and ownership, I've tried pointing the path from Node2 to the either share but I get the same result with either paths from Node2:

This means that the path can be found, but the permissions aren't working.
The directory is owned by a sambaadmin@domain.local user from the file server and can be accessed by BOTH nodes in Windows with full read/write with the service user sqluser@domain.local, but it doesn't work via SQL Server backup device creation? Help. Please.

Figured it out -- Windows credential manager didn't have the credentials stored for the share. After adding the proper creds to the credential manager it worked ok.


To list the databases and logins Can anyone tell me the query to list the databases and SSRS reports and different logins in a particular server ?

List all databases

select * from sys.databases

List SSRS Reports

use [ReportServer]

SELECT * FROM [dbo].[Catalog]
WHERE [Type] = 2
ORDER BY [Path]

List all Logins

select * from sys.syslogins

List users having permission to reports
from stackoverflow link
https://stackoverflow.com/questions/6600480/ssrs-determine-report-permissions-via-reportserver-database-tables

    select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
   inner join dbo.Policies B on A.PolicyID = B.PolicyID
   inner join dbo.Users C on A.UserID = C.UserID
   inner join dbo.Roles D on A.RoleID = D.RoleID
   inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName


Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore? I had a very large corrupt innodb table. mysqld would crash whenever I did a check table on it. I did a innodb-force-recovery=1 and did a dump of the table. No errors showed up.

I dropped the table and attempted to restore, it completed fine.  I did another mysqlcheck on the table, part way through Mysqd crashed with this error:

Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.
InnoDB: You may have to recover from a backup.
120203 23:56:24  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex bc702d5400040f760004
....
...
120203 23:56:24  InnoDB: Page checksum 1745088809, prior-to-4.0.14-form checksum   2260539380
InnoDB: stored checksum 3161468244, prior-to-4.0.14-form stored checksum 2260539380
InnoDB: Page lsn 230 3433899790, low 4 bytes of lsn at page end 3433899790
InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.

I thought dumping a table successfully then restoring from it should clear the corruption? why is it still corrupted and I cannot restore it?

This is on an Ubuntu 10.04 release mysql 5.1.41.

This is on a VPS host so a memory check would not work.  Is there anything else that could be the problem?

Any help would be appreciated.

The error seems to indicate a problem with the primary key on the table mycompany.fact.

InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")

You can open the dump file in any text editor and remove the primary key constraint from the CREATE TABLE schema definition.  Then once you've restored your database, you can always add the primary key back in.  You would also need to deactivate Foreign Key Checks in your backup file.

-- at the beginning of the file
SET foreign_key_checks = 0;

-- at the end of the file
SET foreign_key_checks = 1;

This may get your database restored, but if there is a problem with the data in the column you're using as the primary key, then you may see other errors.

If that didn't work, I would also try

Backing up all the databases
Stopping MySQL
Delete the ibdata and iblog files
Starting MySQL again
Restore all my databases.

If there is a problem with your InnoDB files, this may be a way to solve it.

The problem could also be deeper and running a fsck on the filesystem that stores your InnoDB files may find corruption on the disk.  Rebooting your computer is one way to easily cause an fsck on all partitions.


Creating non-clustered index on large table fills transaction logs Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows.

create nonclustered index nix_TableName on Schema.TableName (
    Column2 asc, Column3, asc
) include (Column4, Column5) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The problem is the transaction logs fill up to quickly which sit on a drive partitioned for 50GB. The db is sitting on an availability group with 3 nodes, DW1, DW2, DW3.

What would be the most optimal/practical way of getting this index created without crashing? Can I throttle the index creation, like do it in batches? Are there any tricks I'm missing that I can include in my create nonclustered index statement?

I did find this documentation. I'm trying a few of these tips next.

There is already a clustered column-store index on the table.

Furthermore checking the activity monitor for the server I see that the process for the index creation is being suspended. With the wait_type being CXPACKET. I'm not specifying a MAX_DOP.

As far as I know the fact that the underlying table is a columnstore index isn't too relevant here. The linked documentation that you included in the question already gives you the standard fixes for your problem. Below is a list of possible fixes rated in

Increase your log file size. This will help you in the future if you ever need to drop and recreate the index for some reason.
Set the SORT_IN_TEMPDB option to ON. I didn't know this, but that option can help in cases when you have enough free space in tempdb.
Build the index with page compression. This can reduce the amount of data written to the transaction log, but that varchar(4000) column could cause issues.
Create a copy of the table's structure with the indexes that you need and insert data into it in batches. This can be difficult to pull off in production and will overall be inefficient but you can split up your index build into separate transactions this way. If something goes wrong just drop the temporary copy of the table that you made.
If the table is partitioned, you might be able to use partition switching to build the nonclustered index one partition at a time.
Temporarily change your recovery model to bulk-logged. This can cause all sorts of issues so make sure that you fully understand all of the ramifications of doing this.


Insert much slower when target table has clustered index I've just had to debug an issue which has me thoroughly confused.

An ETL Transform process on our Development Data warehouse has just failed after working successfully every day for months. The same SSIS job calling the same stored proc, with the same table schema, indexes and data works fine in production.

This step would normally take less than 2 min. Today, after 4 hours the job hasn't completed, but hasn't failed either. There are no reported errors. Nothing in the SQL log, and sp_who2 doesn't show anything blocking.

Here is a link to the estimated plan when the query performs well.
Here is a link to the estimated plan when the query does not finish.

The job truncates a staging table then inserts about 600,000 rows of data. The ETL process has exclusive access to the table. When I checked, all I could see was waits on CXPACKET.

I have traced the fault to a unique clustered index.

The table has a non clustered primary key on an identity column (see below)

CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
    [ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
    [TransactionId] [varchar](255) NOT NULL,
    [KeyType] [varchar](255) NOT NULL,
    [FinancialYear] [varchar](255) NOT NULL,
    [Job] [varchar](255) NOT NULL,
    [Subjob] [varchar](255) NOT NULL,
    [AnalysisCode] [varchar](255) NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL
     CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED
(
    [ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The problem clustered index is:

CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
(   [FinancialYear] ASC,
    [KeyType] ASC,
    [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

There is a 3rd non-clustered index which doesn't affect the insert at all.

Two of us have been working this for 4 hours. I have dropped and re-added the Index 20-30 times trying options in different combinations.

Summary: Clustered index blocks inserts. Non clustered works fine.

We have tried:

Server restart made no difference.
Tables truncated and sp run manually from SSMS no diff. (With SA privs)
Rebuilding the index didn't help.
If I drop the clustered index the insert works.
After insert above, I can add the clustered index without error.
If drop and re-add the index as non-clustered it works.
I have checked the data and it is unique grouped by those 3 fields.
Changing index so that its not unique didn't make any difference.
Adding/removing with tablock hint didn't help.
I tried sorting the data before inserting and it made no difference.

Running: Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3

Any ideas or suggestions would be greatly appreciated.

Let's take a step back and forget all of the troubleshooting around the clustered index. You have an INSERT query which used to finish in a reasonable amount of time but now won't finish after hours. Why might that query now be slow? Let's take a look at the estimated plan:

Reading from right to left, the plan is to first scan the single row from Extract_DW_Control_Finance, do a loop join with a scan of Extract_JCS_Trans on the inner side, sort the data according to the clustered key of the target table, and do another loop join with a scan of Extract_GL_Jnl_Trans on the inner side. The first join probably isn't the issue. The plan can't actually benefit from parallelism, but with a single row in the outer result set the scan on Extract_JCS_Trans should only happen once. However, the optimizer estimates that a single row will come out of that join. If that row estimate is wrong then you could end up doing hundreds of thousands of clustered index scans on Extract_GL_Jnl_Trans.

The query plan for the query which performs well uses a different strategy. The row estimates are significantly different and it performs a hash join:

I suspect that the optimizer will choose a different plan for the poorly performing query if you fix the row estimates. If the Extract_DW_Control_Finance table will always have one row you could consider moving that into a local variable and possibly using a RECOMPILE hint. That could result in a much better estimate.

In terms of why removing the clustered index causes the issue, I suspect that the optimizer does a hash join to Extract_GL_Jnl_Trans without the clustered index. A hash join does not preserve the order of the outer input but a loop join does preserve order. The optimizer may have costed doing the sort on a single row and performing a loop join lower than doing a hash join and performing the sort later on 356566 rows. However, if the sort is not needed then doing the hash join may have had a lower cost than the loop join. It probably all comes down to fixing your cardinality estimates.

If you need to do more troubleshooting while the slow query is running, you could consider trace flag 7412 if you're on SQL Server 2016 SP1. That should given you clues as to where SQL Server is "stuck" in the query plan. If you're able to ask for an actual plan or run the query directly in SSMS you could use sys.dm_exec_query_profiles or the live query statistics feature.


SQL Server infrastructure planning Is there any general official Microsoft guide for planning SQL Server hardware? For instance, if you plan to have 500 concurrent сonnections you should have minimum 4 cores, 256GB of RAM, 15K HDD.

All I have managed to find on MS website is specific for products like SharePoint and so on...

Will appreciate valuable links or critic.

Is there any general official MS guide for planning SQL Server hardware for instance:

No.  The hardware requirements for 500 concurrent connections is too dependent rate and cost of the queries that the connections execute.  That's why you see this kind of prescriptive configuration guidance from applications (like SharePoint), after they have tested the application at different scales.

If you can break down the workload by type of request, with peak sustained request rates for each, and cost for each in terms of CPU time, Logical IO, and Log IO required you can start to get a first-order approximation of the required hardware.  EG for

SmallRead  - 100 rps,   2ms CPU,    100 LIO,   0 Log IO
BigRead    - 10  rps, 400ms CPU, 100000 LIO,   0 Log IO
SmallWrite - 80  rps,  40ms CPU,     10 LIO,   2 Log IO
BigWrite   - 2   rps, 400ms CPU,  10000 LIO, 200 Log IO

200+4000+3200+800 ~ 8000 ms/s of CPU time, or 8 about cores.

The Logical IO depends on the size of database, size of memory, and cache hit ratio, so it's hard to esitmate.  But having RAM >= 20% of database size typically provides good caching.

And the the IOPS at the log are also hard to estimate, because concurrent transactions piggyback on log IOs.

For Data Warehouse workloads Microsoft does produce prescriptive guidance for hardware configuration in the Data Warehouse Fast Track program that enables hardware vendors to produce reference configurations rated by data warehouse size.   But that's only possible assuming that Data Warehouse workloads are all very similar to each other.

But if your workload uses a very large database and needs to support analytics, the Fast Track configuration guidance might be relevant.  If you're thinking about using it for a hybrid or OLTP workload, though you'll need to use one of the variants that use all-flash storage.


Why does SELECT 1/2 return 0? As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.

I am using SQL Server 2008.

They're integers.

Try:

select 1.0/2.0;

Or:

select CONVERT(NUMERIC,1)/CONVERT(NUMERIC,2);


How can I change an existing type from "bigint" to "bigserial"? I have a PostgreSQL table with the following structure:

I simply need to change the TYPE of prove_identity_id from bigint to bigserial.  I read the docs but wasn't able to understand how to legally achieve the change without the following error: PostgreSQL said: type "bigserial" does not exist

PostgreSQL 10.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

bigserial is not a type. It's a pseudo-type, a notational convenience that is resolved to type bigint internally, plus a sequence, a column default, a dependency and an ownership.

Basic commands to convert an existing bigint column with existing rows to a bigserial:

CREATE SEQUENCE tbl_tbl_id_seq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE tbl_tbl_id_seq OWNED BY tbl.tbl_id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(tbl_id), 0)) FROM tbl;

setval() to get the SEQUENCE in sync with the currently highest id.  Replace "tbl" with your table name (prove_identity).

However, zooming into your added image (which should be plain text), it looks like it may be a proper bigserial column already. This might all be a misunderstanding, then.

More details:

Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Related:

How to convert primary key from integer to serial?
Safely and cleanly rename tables that use serial primary key columns in Postgres?

In Postgres 10 or later, consider an IDENTITY column as alternative:

Auto increment table column


Select next or first row For a given ID, I'm trying to get the next row or if not present (end of list), return the first one.
ID (primary key)    value
 1                   John
 3                   Bob
 9                   Mike
 10                  Tom

If we look for 5, it should return the row with ID 9.
If we look for 10, it should return the row with ID 1.

Currently, I simply run two queries, first getting the next:
SELECT * FROM table WHERE id > 5 order by id limit 1;

and then if this returns no result, return the first:
SELECT * FROM table WHERE id = 1;

But I was wondering if there was a way to do it in one go?

You can combine the two queries:
with t as (
  select *
  from the_table
  where id > 5
  order by id limit 1
)
select *
from t
union all
select *
from the_table
where id = 1
and not exists (select *
                from t);

Another option is to use an OR condition:
select *
from the_table
where (id > 5  or id = 1)
order by id = 1, id
limit 1

The order by id = 1 will sort the row with id = 1 at the end and all others (where that condition is false) will be sorted by the actual id value. The disadvantage is that you need to repeat the search condition
Typically OR conditions aren't optimized well, so the first alternative might be faster.


improving performance of concurrent inserts on innodb with randomized primary I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator.  It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up generating surrogate keys in monotonic increasing order.

Therefore I am worried with inserts from lots of different users to the same table for that primary surrogate key being used, since the inserts will all be using numbers near each other at a given point in time, might there be enough gap lock contention to slow down the insert?

I have two workarounds in mind.  One is to use an md5 hash of the BIGINT number as the actual primary key.  This way, all concurrent inserts are likely to lock different gaps.  The other solution is that even though for programming simplicity, it would be best to use the the new BIGINT given by my sequence generator as the surrogate id, instead use a primary key tuple (user_id, surrogate_int_id).

Also, if there secondary indices on the table do inserts take gap locks on all of these as well?

One INSERT per transaction, containing 50 rows, with "consecutive" (or "nearby") PRIMARY KEY values will be optimal, regardless of one or many connections.

Using MD5, UUID, etc, is a disaster for huge tables.  When the table is too big to be fully cached in RAM, the inserts become I/O bound.  In contrast, 50 'adjacent' records will cause almost no I/O.

A secondary index that is a MD5 or UUID is just as bad as the PRIMARY KEY being such.  This is because a secondary key is a BTree, just like the PRIMARY KEY (and the data) is a BTree.  (OK, the secondary key is not quite as bad, because it is probably smaller.)

If you have multiple connections doing inserts, plan on having occasional deadlocks.  Deal with them by re-executing the batch INSERT.

As for "gap locks", first let's get the big picture figured out.  That is, deal with the advice I have given you, and provide a SHOW CREATE TABLE.


How to properly downgrade mysql 8 to 5.7 in centos 8 I would like to downgrade MySQL 8 to MySQL 5.7 on centos 8, can I get the proper steps/commands. Thanks in advance.

Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you back up your data before starting the upgrade process.
https://dev.mysql.com/doc/refman/8.0/en/downgrading.html


Parallel execution sequence Which statement is correct about the parallel executions plan?

A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel.
B. First, the CUSTOMERS table is scanned in parallel, then the SALES table is scanned in parallel, and then they are joined serially.
C. First, the SALES table is scanned in parallel, then the CUSTOMERS table us scanned in parallel, and then they are joined in parallel.
D. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and thenjoined serially.
E. First, the CUSTOMERS table is scanned in parallel, then the SALES table us scanned in parallel, and then they are joined in parallel.

I think E is correct but the correct answer is A. Why?

B, D incorrect:

The join is performed in parallel, there is only 1 serial operation on the picture, only one parallel->serial (P->S) transition, and that is when the query coordinator collects the results.

A (, D) incorrect:

Parallel SQL uses the producer-consumer model. One set reads the table, the other set builds/probes the hash table, they do not read seperate tables simultaneously.

C incorrect:

There is only 1 DFO (:TQ1), the order of execution can be easily recognized by the order of virtual tables or the table queues (:TQ1000x). This is not necessarily true for multiple DFOs, but it is inside 1 DFO. :TQ10000 comes before :TQ10001, but :TQ10000 does not necessarily come before :TQ20000. Read more about this:

https://jonathanlewis.wordpress.com/2015/12/21/parallel-plans-2/

So the first table to be read is CUSTOMER, not SALES.

E correct:

Well, this is how it works, but that is not a real explanation. Easier to prove the other options wrong.


SQLITE Insert with Select Syntax I'm trying to create a categorical column, with 3 states:
subA, subB or Neither.

I'm working with SqliteStudio 3.1.0, RHEL7.0.

I loaded the my csv into SQLITE via Python/Pandas. Everything works as expected.

I added constraints in the GUI:

"fullpath" TEXT Unique NOT NULL.

I added the Sub columns in SqiteStudio and double checked in Data table.

alter table data add column Sub text(100)

That works, no errors given. Then I try:

insert into  data(Sub)
select  fullpath, case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

I get this error which I don't understand.

[18:35:45] Error while executing SQL query on database 'test': 2 values for 1 columns

You are doing an insert into but you only specify one field (sub), then you select 2 fields, just as the error message says.

insert into  data(Sub) -- only one field
select  fullpath, case -- 2 fields
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

You would either need to add a fullpath field to the data table and do:

insert into  data(fullpath, Sub)
select  fullpath, case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

Or you need to select one column

insert into  data(Sub)
select  case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data


Joining Oracle tables with 2 strings to be oa a single row (in a distinct SQL filter) I have an Oracle Database and if I fire that SQL:

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    A3T2.COD_REC, A3T2.COD_VAL
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me:

ID_PL   AN    DENUMIRE  COD_DESEU   TIP_AMB  COD_REC   COD_VAL
353940  2013  DIA       15 01 03    LEMN     R 3
353940  2013  DIA       15 01 03    LEMN               R 1

but if I make an

SELECT distinct RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    (select coalesce(A32.COD_REC,'') || coalesce(A32.COD_VAL,'')
     FROM a3_t2_rows a32
     WHERE A32.TIP_AMB=A3T2.TIP_AMB AND
        A32.FK_ID_ANEXA = CHT.ID AND
        A32.COD_DESEU = A3T2.COD_DESEU AND
        A3T2.TIP_AMB = 'LEMN'
     ) as cod_op_lemn
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'

it gives me an

[Error] ORA-01427: single-row subquery returns more than one row

The question is - how can I join R3 from COD_REC and R1 from COD_VAL so it can be R3 R1 in both rows being a single row with distinct. Thanks!

Simply do a GROUP BY instead of DISTINCT:

SELECT RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    coalesce(MIN(A32.COD_REC),'') || coalesce(MIN(A32.COD_VAL),'')
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'
GROUP BY RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB


Does InnoDB ever release unused disk space? A large MySQL association table with 500M rows recently ballooned into 1B rows due to a bug (now fixed). Disk usage went from ~130GB to 240GB.

Unfortunately there is not room on this server for any of the standard remedies (OPTIMIZE or ALTER TABLE table ENGINE=InnoDB, dump & rebuild, build into temporary table & rename & drop old table, etc.). I only have 50GB remaining.

Here's the question:

While I've successfully removed over 100M rows so far (1B down to 850M), disk usage has actually grown by 10GB (to 250GB). Yes, new records are also being added every second, but I'm deleting far faster than they are being added.

Will InnoDB ever release this space?
Why does the table disk usage
grow, while I'm shrinking the row count?

Any other advice to reclaim unused disk space when the regular solutions don't fit?

InnoDB tablespace size for individual tables (.ibd) can be reduced with OPTIMIZE and ALTER TABLE ... ENGINE=InnoDB;, but the system tablespace file ibdata1 can be never be shrunk.
The source of ibdata1's growth in in the undo logs.
I have written posts about this since July 2013. I first learned about this phenomenon from Percona's post Reasons for run-away main Innodb Tablespace.
Suggestion #1
If you mysqldump the data into another server running mysql in a newly installed instance, you could setup replication to it, then failover and trash the old master.
Suggestion #2
If you do not have another server, you'll have stop the application, mysqldump the data, drop all DBs, shutdown mysql, delete ibdata1, start mysql (which recreates ibdata1, reload the mysqldump). See my very old post Howto: Clean a mysql InnoDB storage engine?


Fullbackup + Differential/Copy-only We have a PROD full backups copied over to support envs daily  and we restore to support environments to triage some issues, as the DB is growing big we would want to run the full backup copy only on the weekends. Daily backups are done as schedule but copying over to support env happens only on weekend. Copy-only or differential can we apply with one full backup ? or what is the best practice and effective way .

Sunday :Full Backup taken
Mon-Friday : Differential/Copy-only backups taken
Saturday : Fullbackup + (Mon-Friday)Differential/Copy-only backups applied .

Will this work out? and one thing to remember we have daily full backups.

We do hourly log backups, and want to know the best practice for backup strategy and restore in support env

Since you're in full recovery and just need to keep the "support" DB up-to-date from production as-needed or once-a-week, then is log shipping not an acceptable option? You could have it restore once-a-day during off hours if it being in standby for the triage of issues would work. The transaction logs could copy over every hour, or whatever you feel is appropriate.

Otherwise, you may be able to setup a custom SQL Agent job (with TSQL scripting or maybe xp_cmdshell) to copy over the transaction logs from prod but have a disabled SQL Agent job (on support instance) to kick off ad hoc to as-needed to apply the copied over transaction logs to the support DB. This would allow you to have ad hoc log shipping essentially without standby assuming you setup the restores correctly to not be in standby.

The once-a-week backup file from prod could be copied over to the "support" server and overwrite or delete the existing file depending on naming conventions. You could then schedule that with SQL Agent job to do the once-a-week full backup and still let the transaction logs copy over without restore until you need to kick it off ad hoc as-needed.

You would setup a job to purge TRN files that are older than a certain time frame once the full backups complete so the old ones are deleted from the "support" server. This may be step 2 of your "support" full backup file restore and just plugin the age of files to purge as-needed. See below example and change arguments and variables as-needed per your requirements and test of course.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1


SQL Server RAM usage is increasing and query executions is slowing down with time I'm using SQL server on a server with 48 GB of RAM.

When I start the SQL Server service, it's using 12% of the RAM at first, but after 24 hours it's reaching 90% and query executions begin slowing down. We have to restart the service so we can serve users.

What could be the problem and how we can fix it?

Example images:

and
(select value_in_use from sys.configurations where name = 'max degree of parallelism')

and my server cores are 47.

Based on the waits statistics in your system you have incorrect settings for parallelism.

Maxdop in your system should not be more than 16, you can find the correct value based on this MS article: Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Here are some T-SQL scripts to do it (I don't know your exact NUMA configuration but the scripts alilyze it):
MAXDOP setting algorithm for SQL Server

Here is the explanation given by Max Vernon on which I agree. He explains why incorrect setting of maxdop slows down the execution.

  If you set MAXDOP higher than the number of cores/numa node, you end
  up with calls into far memory which are many many times slower than
  calling near memory. This is because each numa node has its own
  memory; having a query use more threads than are present in a single
  numa mode will spread the CPU load over multiple cores, and therefore
  multiple memory nodes.


AlwaysOn Primary Showing as Question Mark If I look in the dashboard for my AlwaysOn implementation I can see my groups are synchronised and no data loss.  They also failover successfully but when looking in the Group folder list under replicas on the secondary, the primary node is showing as having a question mark next to it.

Has anyone seen that before?

That is normal. It is just indicates you that you are on the secondary server and the role is taking care of by the primary node (the one without the question mark). If it was a 3 node or more cluster you might see the question mark on all node expect the primary. When it failover you will see the reciprocal of this on the other server.


Safe and secure implementation of logon trigger in SQL Server 2014 Express edition? I've to implement the following requirement:
Access to SQL Server instance shall be allowed only from a C# application.
Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS.
Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin.
Connection from one specific host machine shall be denied.
Connections from a specific C# application shall be allowed.
Is it possible, secure and safe to implement this via logon trigger?
If yes, how can I implement it in a reliable manner.
In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated.
In case something goes wrong how can I disable it?

You can try to do this...
CREATE TRIGGER CSharpOnly ON ALL SERVER FOR LOGON
AS
BEGIN
     IF APP_NAME() NOT IN (N'Your C# app_name() according to SQL Server')
     BEGIN
          RAISERROR('Please use sea sharp.', 16, 1);
          ROLLBACK;
     END
END

...but, since application name and other connection properties are easily spoofed, it is very easy for your users to bypass them - they can just tell SSMS, for example, that it should present itself as the same APP_NAME(). So, it all depends on your definition of "in a reliable manner."
When things go south
In order to fix a bad trigger, you can bypass logon triggers entirely by using the Dedicated Administrator Connection (DAC) to connect and modify / disable the trigger. You can also keep your initial connection established when you create / enable the trigger - existing connections won't go through the trigger again unless they get disconnected.


How can I Use Access to combine tables of information I have two tables in access with a common ID number in both. One table has mail to info and the other table has account information. The account information can have multiple listings per ID. The mail to info has one listing per ID. I need to combine the two tables to create a new table with 1 row per ID but containing multiple columns with the added account information. How do I do that?

Use the query wizard to create a new "temporary" table - a 'view' (a query). This you can use then as source for your letter.


Generate database diagram from SQL Server with no proper integrity So... I've ended up in a project, which by far is the worst quality POS I've seen in a long while. It is basically a Visual Basic .NET windows client application used for various administrative tasks and print outs.
It is using inline SQL (concatenated sql-strings of course) and stored procedures all over the place. All reports have their own embedded SQL.
On top of this it is joining in tables from four different databases, two of which is clearly some reverse engineered attempt to integrate two standard systems not meant to be integrated at all.
It's even updating data in these other systems.

OK.
With some sort of hope of a complete rewrite in the near future I'm trying to make sense of this system. My plan is break it down into use cases, and document these from a database/integration standpoint.

Is there, some tool, that's using a SQL-profiler data dump or similar to "reverse engineer" a database diagram. I'd like to see some sort of graphical representation or anything at all to straighten out this spaghetti...

(Or any other ideas or help on how to approach this would be helpful!)

Try Dataedo - it's a database documentation tool that I think can help in your case. You will be able to import multiple database schemas into one repository, then further split them into smaller pieces called modules. You can describe with rich text and create ER diagram for each. You can define missing FKs and use them for the diagrams. You are able to describe every data and program element (table, column, function, parameter etc.) and export such a documentation to PDF document or HTML. Maybe this will straighten out your spaghetti ;)

I'm the product manager of Dataedo.


php/mysql merge duplicate records in table and create a new table please with respect to username and storetype, how can I merge rows to achieve Final table? storetype should be unique per username. There are thousands of usernames and records so php/mysql would help. thanks

Here is the pure MySQL solution

create table finaltable select * from table1 where 1=2;
alter table finaltable add primary key (id);
insert into finaltable (username,storetype,id)
select * from
(select username,storetype,min(id) min_id
from table1 group by username,storetype) A;
update finaltable A inner join
(
    select username,storetype,
    MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    from table1 group by username,storetype
) B using (username,storetype) set
A.filename = B.filename,
A.`date`   = B.`date`,
A.`desc`   = B.`desc`,
A.password = B.password,
A.email    = B.email,
A.ftp      = B.ftp
;

First here is your sample data from the question

mysql> drop database if exists karto;
Query OK, 2 rows affected (0.01 sec)

mysql> create database karto;
Query OK, 1 row affected (0.00 sec)

mysql> use karto
Database changed
mysql> create table table1
    -> (
    ->     id int not null auto_increment,
    ->     username varchar(20),
    ->     filename varchar(20),
    ->     `date` date,
    ->     `desc` varchar(20),
    ->     storetype varchar(20),
    ->     password varchar(20),
    ->     email varchar(20),
    ->     ftp varchar(20),
    ->     primary key (id),
    ->     key username_storetype (username,storetype)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1
    -> (username,filename,`date`,`desc`,
    -> storetype,password,email,ftp) values
    -> ('ken',NULL         ,NULL        ,'good'  ,'google','1235'    ,'ken@me.com','Ken.me'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken','Product.txt','2012-1-1'  ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,'good'  ,'amazon','4654'    ,'Ken1@me.com','Ken1.net'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('ken','Amazon.txt' ,'2012-1-4'  ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'works' ,'google','477'     ,'Sam1@gm.com','sam'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam','Sales.txt'  ,'2012-10-10',NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'broken','ebay'  ,'3534534' ,'samlee@hm.com','Sam44'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'ebay'  ,NULL      ,NULL,NULL),
    -> ('sam','june.txt'   ,'2012-2-12' ,NULL    ,'ebay'  ,NULL      ,NULL,NULL);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | NULL        | NULL       | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  2 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  3 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  4 | ken      | Product.txt | 2012-01-01 | NULL   | google    | NULL     | NULL          | NULL     |
|  5 | ken      | NULL        | NULL       | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  6 | ken      | NULL        | NULL       | NULL   | amazon    | NULL     | NULL          | NULL     |
|  7 | ken      | Amazon.txt  | 2012-01-04 | NULL   | amazon    | NULL     | NULL          | NULL     |
|  8 | sam      | NULL        | NULL       | works  | google    | 477      | Sam1@gm.com   | sam      |
|  9 | sam      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
| 10 | sam      | Sales.txt   | 2012-10-10 | NULL   | google    | NULL     | NULL          | NULL     |
| 11 | sam      | NULL        | NULL       | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
| 12 | sam      | NULL        | NULL       | NULL   | ebay      | NULL     | NULL          | NULL     |
| 13 | sam      | june.txt    | 2012-02-12 | NULL   | ebay      | NULL     | NULL          | NULL     |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
13 rows in set (0.00 sec)

mysql>

Now, let's run the SQL I proposed

mysql> create table finaltable select * from table1 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table finaltable add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into finaltable (username,storetype,id)
    -> select * from
    -> (select username,storetype,min(id) min_id
    -> from table1 group by username,storetype) A;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update finaltable A inner join
    -> (
    ->     select username,storetype,
    ->     MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    ->     MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    ->     MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    ->     MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    ->     MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    ->     MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    ->     from table1 group by username,storetype
    -> ) B using (username,storetype) set
    -> A.filename = B.filename,
    -> A.`date`   = B.`date`,
    -> A.`desc`   = B.`desc`,
    -> A.password = B.password,
    -> A.email    = B.email,
    -> A.ftp      = B.ftp
    -> ;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from finaltable;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | Product.txt | 2012-01-01 | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  5 | ken      | Amazon.txt  | 2012-01-04 | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  8 | sam      | Sales.txt   | 2012-10-10 | works  | google    | 477      | Sam1@gm.com   | sam      |
| 11 | sam      | june.txt    | 2012-02-12 | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
4 rows in set (0.00 sec)

mysql>

All you have to do is run these SQL commands with each mysqli function in PHP and you are all set.

Give it a Try !!!


oracle 10G forms connection with Oracle database 10G Enterprise Edition in windows 7 I am trying to connect Oracle Forms 10g version (9.0.4) to the Oracle 10G Enterprise Edition.

At first I tried to connect to the db without setting the "Local Net Service Name Configuration" in Oracle Net Configuration Assistant.I got the error

After setting the "Local Net Service Name Configuration" in Oracle Net Configuration Assistant, I am still getting an error

Can anyone explain me how to connect to the database with forms as I am stubbed as of long struggle for connecting the database?

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

The following are the listener.ora and tnsnames.ora contents

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = RahulSingh-PC)(PORT = 1521))
    )
  )

The message is an indication of incompatibility of Forms version that you're using with your OS, i.e. Windows 7.

You'll need to install Oracle Forms version 10.1.2.0.2 with patchset 3 to have this corrected.

Else you can also install Oracle Forms 11g R2 which is certified for Windows 7


enabling fsync in postgresql 9.1 currently fsync option is disabled in postgresql.conf.

If I am enable that,

From:

#fsync = on

To:

fsync = on

(I am enabling fsync to avoid data corruption if any failure since my data (tablespace created in mounted remote partition) is available in remote location)

should I need to consider consider below properties also?

#synchronous_commit = on
#wal_sync_method = fsync

Should I need to enabled above configuration also?

If you've not changed those parameters they should be enabled by default.

You should only need to uncomment them if you're going to change them.

You can check the running parameters for the server with SHOW ALL; or by specific parameter, e.g. SHOW fsync; on the psql command line.

The wal_sync_method should default to a best guessed method for your platform, most likely fdatasync on Linux or fsync on most other operating systems.

You can read more about these parameters in the WAL runtime config documentation

However, it is awesome that you care enough to double-check all this. =)


How to control the excessive use of ram by SQL Server? The database server that I am using is running 6 different SQL Server instances. It has 48 GB RAM. And one of them is consuming more than 10 GB of RAM, total consumption is 20 GB for now. The RAM consumption is growing continuously. Couple of days before it was using more than 40 GB of RAM and the server was responding very slow. The application shows the crashing problems when saving data.

So I restarted the SQL Server services.

As soon as the services were restarted, the usage came down to 4 GB, but now it is growing.
And I am worried that it will grow up to 40 GB in 4 or 5 days and make the server slow.

Service restarting is not the good option I guess.

I also found from different sources that we can set the maximum memory usage size for SQL Server. And am not quite sure if this will help or not. I can't test this because the server is using the production database and it would be a risk if the service stops while modifying the setting in SQL Server.

Can any one help for this problem?

That is by design.  SQL Server is supposed to use all the available memory, as it is storing more and more data in memory so that it doesn't need to go back to the disk to get the same memory over and over.

If you need to limit the amount of memory that a single SQL Server instance is using you can do this in SQL Server Management Studio by right clicking on the instance name within the Object Explorer and selecting properties.  Then select the memory tab and set the maximum amount of memory that the SQL Server will be allowed to use.  Now this isn't going to limit all aspects of SQL Server to that amount of memory.  This only controls the buffer pool and the execution plan cache.  Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting.  However these other things typically don't need all that much memory, it's the buffer pool and the execution plan cache which need the bulk of the memory.

If you set this setting on one instance you'll want to set it on all the instances so that they don't step on each other.


Why is using a table variable more than twice as fast as a #temp table in this specific case? I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for 2005.

When executing the stored procedures (definitions below) with only 10 rows the table variable version out performs the temporary table version by more than two times.

I cleared the procedure cache and ran both stored procedures 10,000 times then repeated the process for another 4 runs. Results below (time in ms per batch)

T2_Time     V2_Time
----------- -----------
8578        2718
6641        2781
6469        2813
6766        2797
6156        2719

My question is: What is the reason for the better performance of the table variable version?

I've done some investigation. e.g. Looking at the performance counters with

SELECT cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';

confirms that in both cases the temporary objects are being cached after the first run as expected rather than created from scratch again for every invocation.

Similarly tracing the Auto Stats, SP:Recompile, SQL:StmtRecompileevents in Profiler (screenshot below) shows that these events only occur once (on the first invocation of the #temp table stored procedure) and the other 9,999 executions do not raise any of these events. (The table variable version does not get any of these events)

The slightly greater overhead of the first run of the stored procedure can in no way account for the big overall difference however as it still only takes a few ms to clear the procedure cache and run both procedures once so I don't believe either statistics or recompiles can be the cause.

Create Required Database Objects

CREATE DATABASE TESTDB_18Feb2012;

GO

USE TESTDB_18Feb2012;

CREATE TABLE NUM
  (
     n INT PRIMARY KEY,
     s VARCHAR(128)
  );

WITH NUMS(N)
     AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0)
         FROM   master..spt_values v1,
                master..spt_values v2)
INSERT INTO NUM
SELECT N,
       'Value: ' + CONVERT(VARCHAR, N)
FROM   NUMS

GO

CREATE PROCEDURE [dbo].[T2] @total INT
AS
  CREATE TABLE #T
    (
       n INT PRIMARY KEY,
       s VARCHAR(128)
    )

  INSERT INTO #T
  SELECT n,
         s
  FROM   NUM
  WHERE  n%100 > 0
         AND n <= @total

  DECLARE @res VARCHAR(128)

  SELECT @res = MAX(s)
  FROM   NUM
  WHERE  n <= @total
         AND NOT EXISTS(SELECT *
                        FROM   #T
                        WHERE  #T.n = NUM.n)
GO

CREATE PROCEDURE [dbo].[V2] @total INT
AS
  DECLARE @V TABLE (
    n INT PRIMARY KEY,
    s VARCHAR(128))

  INSERT INTO @V
  SELECT n,
         s
  FROM   NUM
  WHERE  n%100 > 0
         AND n <= @total

  DECLARE @res VARCHAR(128)

  SELECT @res = MAX(s)
  FROM   NUM
  WHERE  n <= @total
         AND NOT EXISTS(SELECT *
                        FROM   @V V
                        WHERE  V.n = NUM.n)

GO

Test Script

SET NOCOUNT ON;

DECLARE @T1 DATETIME2,
        @T2 DATETIME2,
        @T3 DATETIME2,
        @Counter INT = 0

SET @T1 = SYSDATETIME()

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.T2 10
SET @Counter += 1
END

SET @T2 = SYSDATETIME()
SET @Counter = 0

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.V2 10
SET @Counter += 1
END

SET @T3 = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,
       DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time

The output of SET STATISTICS IO ON for both looks similar

SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10

Gives

V2
Table '#58B62A60'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#58B62A60'. Scan count 10, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

And as Aaron points out in the comments the plan for the table variable version is actually less efficient as whilst both have a nested loops plan driven by an index seek on dbo.NUM the #temp table version performs a seek into the index on [#T].n = [dbo].[NUM].[n] with residual predicate [#T].[n]<=[@total] whereas the table variable version performs an index seek on @V.n <= [@total] with residual predicate @V.[n]=[dbo].[NUM].[n] and so processes more rows (which is why this plan performs so poorly for larger number of rows)

Using Extended Events to look at the wait types for the specific spid gives these results for 10,000 executions of EXEC dbo.T2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16         | 19             | 19             | 0              |
| PAGELATCH_SH        | 39998      | 14             | 0              | 14             |
| PAGELATCH_EX        | 1          | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

and these results for 10,000 executions of EXEC dbo.V2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX        | 2          | 0              | 0              | 0              |
| PAGELATCH_SH        | 1          | 0              | 0              | 0              |
| SOS_SCHEDULER_YIELD | 676        | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

So it is clear that the number of PAGELATCH_SH waits is much higher in the #temp table case. I'm not aware of any way of adding the wait resource to the extended events trace so to investigate this further I ran

WHILE 1=1
EXEC dbo.T2 10

Whilst in another connection polling sys.dm_os_waiting_tasks

CREATE TABLE #T(resource_description NVARCHAR(2048))

WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'

After leaving that running for about 15 seconds it had gathered the following results

+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
|  1098 | 2:1:150              |
|  1689 | 2:1:146              |
+-------+----------------------+

Both of these pages being latched belong to (different) non clustered indexes on the tempdb.sys.sysschobjs base table named 'nc1' and 'nc2'.

Querying tempdb.sys.fn_dblog during the runs indicates that the number of log records added by the first execution of each stored procedure was somewhat variable but for subsequent executions the number added by each iteration was very consistent and predictable. Once the procedure plans are cached the number of log entries are about half those needed for the #temp version.

+-----------------+----------------+------------+
|                 | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run       |            126 | 72 or 136  |
| Subsequent Runs |             17 | 32         |
+-----------------+----------------+------------+

Looking at the transaction log entries in more detail for the #temp table version of the SP each subsequent invocation of the stored procedure creates three transactions and the table variable one only two.

+---------------------------------+----+---------------------------------+----+
|           #Temp Table                |         @Table Variable              |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE                    |  9 |                                 |    |
| INSERT                          | 12 | TVQuery                         | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable |  5 |
+---------------------------------+----+---------------------------------+----+

The INSERT/TVQUERY transactions are identical except for the name. This contains the log records for each of the 10 rows inserted to the temporary table or table variable plus the LOP_BEGIN_XACT/ LOP_COMMIT_XACT entries.

The CREATE TABLE transaction only appears in the #Temp version and looks as follows.

+-----------------+-------------------+---------------------+
|    Operation    |      Context      |    AllocUnitName    |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                     |
| LOP_SHRINK_NOOP | LCX_NULL          |                     |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1  |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2  |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL          |                     |
+-----------------+-------------------+---------------------+

The FCheckAndCleanupCachedTempTable transaction appears in both but has 6 additional entries in the #temp version. These are the 6 rows referring to sys.sysschobjs and they have exactly the same pattern as above.

+-----------------+-------------------+----------------------------------------------+
|    Operation    |      Context      |                AllocUnitName                 |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                                              |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT  | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1                           |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2                           |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_COMMIT_XACT | LCX_NULL          |                                              |
+-----------------+-------------------+----------------------------------------------+

Looking at these 6 rows in both transactions they correspond to the same operations. The first LOP_MODIFY_ROW, LCX_CLUSTERED is an update to the modify_date column in sys.objects. The remaining five rows are all concerned with object renaming. Because name is a key column of both affected NCIs (nc1 and nc2) this is carried out as a delete/insert for those then it goes back to the clustered index and updates that too.

It appears that for the #temp table version when the stored procedure ends part of the clean up carried out by the FCheckAndCleanupCachedTempTable transaction is to rename the temp table from something like #T__________________________________________________________________________________________________________________00000000E316 to a different internal name such as #2F4A0079 and when it is entered the CREATE TABLE transaction renames it back. This flip flopping name can be seen by in one connection executing dbo.T2 in a loop whilst in another

WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects
WHERE name LIKE '#%'

Example Results

So one potential explanation for the observed performance differential as alluded to by Alex is that it is this additional work maintaining the system tables in tempdb that is responsible.

Running both procedures in a loop the Visual Studio Code profiler reveals the following

+-------------------------------+--------------------+-------+-----------+
|           Function            |    Explanation     | Temp  | Table Var |
+-------------------------------+--------------------+-------+-----------+
| CXStmtDML::XretExecute        | Insert ... Select  | 16.93 | 37.31     |
| CXStmtQuery::ErsqExecuteQuery | Select Max         | 8.77  | 23.19     |
+-------------------------------+--------------------+-------+-----------+
| Total                         |                    | 25.7  | 60.5      |
+-------------------------------+--------------------+-------+-----------+

The table variable version spends about 60% of the time performing the insert statement and the subsequent select whereas the temporary table is less than half that. This is inline with the timings shown in the OP and with the conclusion above that the difference in performance is down to time spent performing ancillary work not due to time spent in the query execution itself.

The most important functions contributing towards the "missing" 75% in the temporary table version are

+------------------------------------+-------------------+
|              Function              | Inclusive Samples |
+------------------------------------+-------------------+
| CXStmtCreateTableDDL::XretExecute  | 26.26%            |
| CXStmtDDL::FinishNormalImp         | 4.17%             |
| TmpObject::Release                 | 27.77%            |
+------------------------------------+-------------------+
| Total                              | 58.20%            |
+------------------------------------+-------------------+

Under both the create and release functions the function CMEDProxyObject::SetName is shown with an inclusive sample value of 19.6%. From which I infer that 39.2% of the time in the temporary table case is taken up with the renaming described earlier.

And the largest ones in the table variable version contributing to the other 40% are

+-----------------------------------+-------------------+
|             Function              | Inclusive Samples |
+-----------------------------------+-------------------+
| CTableCreate::LCreate             | 7.41%             |
| TmpObject::Release                | 12.87%            |
+-----------------------------------+-------------------+
| Total                             | 20.28%            |
+-----------------------------------+-------------------+

Temporary Table Profile

Table Variable Profile