ORDER BY performance impact I am using SQL Server 2012. We have a table that specifies the incoming requests to be processed. Some of the columns are (total columns are 10),

requestId
requestStatus (Enum - Processed, NotProcessed, Failed, etc)
requestedTime
requestCompletionTime

The query to pick the new requests is

select top 10000
from RequestProcessingTable
where requestStatus = 'NotProcessed'
order by requestId

The total number of rows in this table is 300 million.

We have 2 indexes, one clustered (requestId - primary key of this table) and one non-clustered (requestStatus).

Since we want to process the requests based on the requestId we need to do the order by. When we introduce the order by it is taking 3 times more time to select. Is there a best way to create indexes or do it in a different approach so that the performance of the SQL query can be improved.

Thanks a lot for your help in advance.

Create a filtered index on requestId with the predicate where requestStatus = 'NotProcessed'. The optimiser is likely to favour this as long as the query conditions exactly match the index definition.

It may help to use a small tinyint for the status instead of a longer character string. There will be more rows per data page and less IO.


Find SSIS package which calls stored procedure We are trying to catch errors into an Error Log Table, similar to below.
We have many Master SSIS packages, calling many stored procedures.

Following code will log into the Error Log Table. Is there a SQL reserved keyword to find the SSIS package which executed the stored procedure?

insert into dbo.ErrorLogData
(
    ErrorNumber
    ,ErrorDescription
    ,ErrorProcedure
    ,ErrorState
    ,ErrorSeverity
    ,ErrorLine
    ,ErrorTime
    ,SSISPackage --???
)
VALUES
(
    ERROR_NUMBER()
    ,ERROR_MESSAGE()
    ,ERROR_PROCEDURE()
    ,ERROR_STATE()
    ,ERROR_SEVERITY()
    ,ERROR_LINE()
    ,GETDATE()
    -- what is reserved keyword for SSIS package?
);

SQL Server: Error Logging and Reporting within a Stored Procedure

As far as I know, there is not a built-in function that provides the package name. I think the closest you are going to get is to use the APP_NAME function. This does depend on parameters being passed as part of the connection string to specify the name - which means that it could be changed at anytime, but on the positive side it also means it can be as flexible as you need it to be.

However, because it is set on the client-side the documentation does come with this warning:

  The client provides the application name, and APP_NAME does not verify the application name value in any way. Do not use APP_NAME as part of a security check.

If the client did set the value in the connection though, this is the result you would get (example using Sql Server Management Studio from the link above):

USE AdventureWorks2012;
GO
IF APP_NAME() = 'Microsoft SQL Server Management Studio - Query'
PRINT 'This process was started by ' + APP_NAME() +
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 101) + '.';
ELSE
PRINT 'This process was started by ' + APP_NAME() +
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 102) + '.';
GO


How to use sqlcmd to create file names for output based on the day of the week or the date? I have a SQL agent job that queries the database and exports the results into a csv file.
It needs to run daily and The output file (Output.csv) gets overwritten everytime the job runs. Is there a way to create new files instead of overwriting? Say monday.csv, tuesday.csv etc or maybe {date}.csv

This is the command:

SQLCMD -S SQL-CLUST1 -Q "SELECT * FROM TABLE" -o "\\\PATH\output.csv" -W -s","

Step type: Operating system (CmdExec)

Run as : SQL Proxy

Note: The output file and append output to existing file is unavailable in the advanced tab.

Below script will help you. Just save it as a .bat file.

@echo off
setlocal
set timehour=%time:~0,2%
sqlcmd -S SQL-CLUST1 -E -Q "SELECT * FROM TABLE" -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.txt

If you have xp_cmdshell enabled, then it is much easier :

DECLARE       @sqlCommand   VARCHAR(max)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)

SET    @filePath = 'C:\Temp\'

SET    @fileName = 'Output_' +
       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

SET    @sqlCommand =
       'SQLCMD -S server_name -E -d master -q "select @@servername" -o "' +
       @filePath + @fileName +
       '" -h-1'

PRINT       @sqlCommand

--EXEC   master..xp_cmdshell @sqlCommand
GO


Redshift Spectrum CTE Union I have a query with a CTE that uses a Spectrum table. When I try to union the CTE with itself:

WITH foo AS (
  SELECT col1
  FROM spectrum.bar
)
SELECT * FROM foo UNION ALL
SELECT * FROM foo;

I get the following error:

[XX000][500310] [Amazon](500310) Invalid operation: Relation "spectrum_foo_58abe8db83a3e" already exists Details: ----------------------------------------------- error: Relation "spectrum_foo_58abe8db83a3e" already exists code: 13 ..

Is this a known issue and/or is there any way to work around it (that doesn't involve pasting the original query for every union)?

If this can be helpful to someone, as a workaround I've created a view instead of using CTE.

CREATE VIEW foo_view AS
  SELECT col1
  FROM spectrum.bar
with no schema binding;

SELECT * FROM foo_view UNION ALL
SELECT * FROM foo_view;


Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error? Consider:

SQL> select sysdate from dual;

SYSDATE
-----------
22-Aug-13 0

SQL> select sysdate from dual; select sysdate from dual;

select sysdate from dual; select sysdate from dual

ORA-00911: invalid character

SQL>

Notice that when I execute two queries at once in the command window, it throws the ORA-00911: invalid character error which generally indicates syntactic error.

It is a syntax error.

SQL*Plus expects either:

A single SQL command, terminated by either a ";" character or a "/" on a line by itself.
A PL/SQL block
A SQL*Plus command

What you have entered is 2 queries on a single line, which SQL*Plus will send to the RDBMS - Oracle will then try and parse the string sent as a single query and fail because it is not valid SQL.

See the documentation for more details: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_four.htm#i1039424


Innodb updates records with delay I have two tables in my database like these:

CREATE TABLE `main` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_id` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL DEFAULT '0',
  `done` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have queries like these:

INSERT INTO `main` (`id`, `type`, `status`) VALUES (NULL, '1', '1');
INSERT INTO `sub` (`id`, `main_id`, `count`, `done`) VALUES (NULL, '1', '2', '0');
update sub set done = done + 2  where main_id =1

After these three , my app select the record using a query like this:

SELECT * FROM `sub`  where main_id =1

My problem is that sometimes (not always) the select query returns the data with delay. For example it returns the data after 10 minutes while thousands of other records are updated with no delay.

P.S: There is no caching layer on the app.
P.S: There is no caching in MySql.

There are two things you must remember is at play
TRANSACTION ISOLATION
The default for tx_isolation is REPEATABLE-READ.
It is plausible and probable that the update sub set done = done + 2  where main_id =1 is being held up.
In fact, your INSERTs must generate auto_increment values. This requires doing some sort of gap index locking. If other INSERTs are occurring on main and sub,
LOCKS ON DATA
You could potentially catch such delays in the act by running
SHOW ENGINE INNODB STATUS\G

The update sub set done = done + 2  where main_id =1 might be visible at some point due to any row-level locks and/or index-level locks.
Locking of the PRIMARY KEY for auto_increment generation must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

I actually discussed this in a later post : How to analyse innodb status on deadlock in insert Query?


Oracle RMAN Incremental update or merge questions 1- Does Oracle RMAN support Incremental Update/Merge option for device type SBT ? Or its only supported for Disk ?

2 - During an update/merge operation during the incremental backup, does RMAN write directly into the image copy by updating the changed blocks ?

Thanks in advance

1: Incremental image copy update is only supported on disk

2: Updating the image copy is done using a differential backupset.  So you need the diff before you can update the image copy.  A good description of the process is in the Oracle docs

For both answers, see the RMAN Backup and Recovery User's Guide: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV8186

Table 9-2 explains the process in detail.


Should I shrink this SQL Server data file? I have read many articles describing how shrinking data files causes terrible things to happen floods, famine, fragmentation, and general mayhem.

Here is the cliff notes of my situation:

SSAS company with a central logging database for all client errors.
Recent code roll created a new bug exponentially increasing the size of the database.
Bug is fixed and the useless records have been removed, approximately 70 million.
DB used space is now down to a normal level leaving 200+ gig of white space.

I am unable to find any posts or articles describing what I will gain from reclaiming this space.

Other than backup times increasing, are there ANY negative effects from a significant amount of unused space in a data file?

Please note the following in case it may help give a more informed answer:

The drive is not in danger of filling up
Space is not needed else where on the SAN
Backup times have double in duration (both full and differential)
Our DR SAN replication has slowed slightly.

Presuming you are sure the database is not going to need the 200+ GB of extra space it is currently consuming, I would recommend performing a shrink without reorganizing pages, using TRUNCATEONLY:

DBCC SHRINKFILE (N'<file name>' , 0, TRUNCATEONLY);

TRUNCATEONLY simply truncates the last part of the file without moving rows inside the data file.  This is important since it will prevent logical fragmentation of tables inside the data file.  If you perform the shrink without specifying TRUNCATEONLY SQL Server will move pages around inside the file to get the smallest possible file size, thereby creating anywhere from "some" to "a lot" of fragmentation, which may adversely affect performance. Read more about this at the Books Online page for DBCC SHRINKFILE.  Thanks to @Zane for the suggestion to include these details in my answer.

Once you have performed the shrink, you may want to ensure the file growth parameters are set correctly.  For instance, ensure the file growth is not percentage based; use a number of MBs that makes sense for your expected growth rate.


Using queries instead of loops to modify database principals I need to check for the existence of certain users on some database servers, each of which has over 1,000 databases, and create the users if they don’t exist and assign proper database roles to them.  I already do this currently by using loops (no cursor though!) and dynamic SQL, but I was wondering if there is any way to do it by using queries.  I’m relatively new to writing T-SQL and – to me – dynamic SQL seems ugly.  It seems to me that if there were any way to resolve a variable inside of a query, it would make things easier.  For example, I could use the following query as the inner query of a correlated query where the outer query got a list of databases from master.sys.databases:

select name
from @dbname.sys.database_principals
where type = ‘G’ and name like ‘%SomeUser%’

This doesn't work... but is there any way to do something like this without dynamic SQL?

You are going to have to use dynamic SQL.  Even if there were a way to access a system table that has all the information on on server, you would need dynamic SQL for the four-part naming on other servers.

By the way, you can resolve variables inside queries.  However, a variable does not work for a server, database, table, or column name.  In short, variables replace constants.

Here is a suggestion on how to do it in your case.


Missing data when trying to import large columns using powershell Powershell command (whitespace added for readability):

Invoke-Sqlcmd -ServerInstance "$db" -Database "$dbname" -Username "xxxx" -Password "xxx"
              -Query "select logmessages from log
                      where TIME_STAMP BETWEEN '2019-01-11 09:00:00' and '2019-01-11 09:02:00'
                      AND logmessages like '%string%'
                      order by TIME_STAMP"
              -QueryTimeout 0 -ConnectionTimeout 0
| Export-Csv "C:\Users\xxxxx\Desktop\PS\data.csv" -Append

Each row result is expected to be around 01 mb. The problem is I'm seeing partial data.

The table has 17 columns and I'm trying to fetch data from one of the column (type:varchar; length: -1) which typically stores xml data close to the size of 1mb per record. When I run a select statement against that column, only partial data is returned.

The column you are having trouble with us a varchar(Max) column. This is one of several "LOB data types", which can be up to 2GB.

The invoke-sqlcmd PowerShell cmdlet will, by default, truncate strings to 4000 characters. This can be very useful to prevent accidently pulling back many GB of data from a query without realizing it. This is similar to the default query timeout, which prevents accidental long-running queries.

A "surprise" LOB column containing 2GB per row could pull back 1TB of data in as few as 500 rows--which would have unintended performance issues for other users on the server and possibly even on the local network, assuming a low bandwidth network.

To override that default, simply provide the -MaxCharLength parameter to indicate you want more than 4000 characters from your varchar(Max) column. (Similar to the way you already supply the -QueryTimeout 0 parameter.


How to enable odbc connnections in new sql server setup? Fresh server 2012 R2 Essentials install, trying to get SQL Server Express to accept ODBC connections. My database is setup and I have all of my data populated, but now I am trying to get my reporting software to connect to the database and am not sure how to get the server to allow incoming connections.

By default when Express Edition is installed the TCP/IP protocol is disabled. You need to go into SQL Server Configuration Manager and enable that protocol.


Should I restore without the log file? I need to restore a SQL Server database with/without the log file. This is for a reporting system. When I restore the log file it is sometimes 8x larger than the data file. I am restoring this once a week for a reporting system that will not need to be restored (if it fails I will restore it from the OLTP system).

Is there any reason I should just restore it without the log and keep the recovery model simple so the log file does not grow too much?

On the source the log file is not being backed up. A different group is managing that system.

Community wiki answer:

You can't restore without creating the log file that was there when the database was backed up, so no.

It makes sense to have the reporting system in simple recovery (and perhaps even in read only) but in order for that log file to be smaller than the production system (assuming it doesn't fit there and you can't restore the big log file and shrink it), you'll have to restore it somewhere else first, shrink the log file there, and take another backup.


Oracle function based index with empty function name I'm trying to understand this syntax in the creation of an index in a database I'm using:

CREATE  INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3'));

After searching for a while, I could not find any example using this syntax, and how it is different from a normal multi column index:

CREATE  INDEX IDX_2 ON MYSQ.MYTABLE ('ID_COLUMN1', 'ID_COLUMN2', 'ID_COLUMN3');

I figured out it is a function based index, analyzing the following example I was trying to assemble:

CREATE TABLE t (a number, b number, c number);

insert into t values (1, 2, 3);
insert into t values (4, 5, 6);
insert into t values (7, 8, 9);
insert into t values (10,11, 12);
insert into t values (13, 14, 15);
insert into t values (16, 17, 18);

CREATE INDEX IDX2 ON t (a, b, c);
CREATE INDEX IDX ON t (('d'), ('b'), ('c'));

select * from USER_INDEXES;

INDEX_NAME  INDEX_TYPE  TABLE_OWNER     TABLE_NAME  TABLE_TYPE  UNIQUENESS  COMPRESSION     PREFIX_LENGTH   TABLESPACE_NAME     INI_TRANS   MAX_TRANS   INITIAL_EXTENT  NEXT_EXTENT     MIN_EXTENTS     MAX_EXTENTS     PCT_INCREASE    PCT_THRESHOLD   INCLUDE_COLUMN  FREELISTS   FREELIST_GROUPS     PCT_FREE    LOGGING     BLEVEL  LEAF_BLOCKS     DISTINCT_KEYS   AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR   STATUS  NUM_ROWS    SAMPLE_SIZE     LAST_ANALYZED   DEGREE  INSTANCES   PARTITIONED     TEMPORARY   GENERATED   SECONDARY   BUFFER_POOL     FLASH_CACHE     CELL_FLASH_CACHE    USER_STATS  DURATION    PCT_DIRECT_ACCESS   ITYP_OWNER  ITYP_NAME   PARAMETERS  GLOBAL_STATS    DOMIDX_STATUS   DOMIDX_OPSTATUS     FUNCIDX_STATUS  JOIN_INDEX  IOT_REDUNDANT_PKEY_ELIM     DROPPED     VISIBILITY  DOMIDX_MANAGEMENT   SEGMENT_CREATED
IDX2    NORMAL  USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (null)  (null)  (null)  10  YES     0   1   6   1   1   1   VALID   6   6   2019-08-19T22:06:23Z    1   1   NO  N   N   N   DEFAULT     DEFAULT     DEFAULT     NO  (null)  (null)  (null)  (null)  (null)  NO  (null)  (null)  (null)  NO  NO  NO  VISIBLE     (null)  YES
IDX     FUNCTION-BASED NORMAL   USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (nul

http://www.sqlfiddle.com/#!4/ae9ea5/5

What is the purpose of this index?

That is not an unnamed/empty function. That is a column expression made from a constant, between parentheses.

And it is listed as function-based index, because:

CREATE INDEX

  When you specify column_expression, you create a function-based index.

The below is a function-based index too by definition, while it is nothing more than the a constant for all rows.

CREATE INDEX IDX3 ON t('a');


How can this table possibly be converted to a MySql table? I got a TABLE in Excel format like:

This is a STEN TABLE containing FACTORS (A,B,C,D,E....etc)
Each Factor is like a separate test.
Each Factor i.e. Factor A has a [Raw Score] and a [Sten]
Supposing Factor A, had a question like:
-----------------------------------

Why do humans have Eyes?
The answer options could be like:

a) To Watch movies =  [Raw Score] -> 10,
b) To Read Novels  =  [Raw Score] -> 5,
c) To close them while sleeping = [Raw Score] ->0

So if they Chose a) then, the system will go to the STEN TABLE to get the STEN Equivalent under Factor A, in this case, the sten equivalent will be 4. (See Factor A->Row score 10-> Sten column)
What could be the most practical way to have this STEN TABLE with Factors and their Raw Scores and Stens created?
Smothing like
              **STEN TABLE**
                         |
                         |
                **FACTORS (A,B,C,D...)**
                      /     \
                     /       \
                    /         \
                   /           \
                  /             \
            **[Raw Score]      [Sten]**

EDIT 1:

To See a larger image, please click here: http://ctrlv.in/459785

Please note that, the stens are not equal for all Factors, though the Raw Scores are the same. i.e. in Factor A, [Raw Score]->3 = [sten]->2 but in Factor C  [Raw Score]->3  = [sten]->1 and in Factor F  [Raw Score]->1 [sten]->2 whereas in Factor E  [Raw Score]->1 = [sten]->1.
Thank you.
Any Suggestion is highly honored.

I'd pull this data into two tables: Factor's and STEN Lookup's.

Your Factors table would look like:

Factor_ID(PK) | Factor_Name
--------------|------------
  1           |      A
  2           |      B
  3           |      C
  4           |      D

Your `STEN_Lookup'

Factor_ID(FK) | Score_Value | STEN_Value
--------------|-------------|-------------
  1           |       0     |       1
  1           |       1     |       1
  1           |       2     |       1
  1           |       3     |       2
  1           |       4     |       2
  2           |       0     |       8
  2           |       1     |       9
  2           |       2     |       11
.....

I'd suggest you do some reading on normalization, as it helps massivly when designing your tables. A great Video Resource is MVA, (Video 2 on this hyperlink).


Can I drop and recreate a system extended stored procedure? I am having a problem with one of my servers where I cannot execute xp_cmdshell under certain conditions. The full problem can be found here problem running xp_cmdshell when Application Name set in connection string

I want to see if dropping and recreating it will fix it (on a test server first). I'm grabbing at straws here.

No, you can't drop and re-create xp_cmdshell. It's not even its own procedure, but part of a library in xpstar.dll (or at least it was at one point). And I am extremely skeptical that, even if you could re-create this procedure, that would fix the problem you're having.

Maybe instead of banging your head against this problem with xp_cmdshell, you should just bite the bullet and implement the functionality you need in CLR. Or perform the tasks you need outside of SQL Server altogether.


Variable values in Oracle where clause I am trying to pass an variable value in my Select statement where clause, I tried a very simple query like below

Declare v_WK varchar(20);
exec :v_WK :='Wk 6'
Select * from myTable Where Column = :v_Wk

In toad it pop up the filtering window(How do I avoid that)

DATE_DIM table is like below

UNIQUE_ID   WEEK_NAME   WEEK_BEGIN  WEEK_END
1   Week 6, 2018    20180205    20180211
2   Week 5, 2018    20180129    20180204
3   Week 4, 2018    20180122    20180128
4   Week 3, 2018    20180115    20180121

And Fact Table is like

ACCT_ID WEEK_NAME   SALES
10001   20180205    10
10001   20180206    20
10001   20180207    10
10001   20180208    15
1002    20180129    100
1002    20180130    100
1002    20180131    100
1002    20180132    100

Here I need an Output like

ACCT_ID WEEK_NAME   SALES
10001   Week 6, 2018    55
1002    Week 5, 2018    406

So Basically I wanna loop each ACCT_ID like

SELECT *
FROM Fact_Table
where Week_Name between (Select Week_Begin from DATE_DIM WHERE WEEk_NAME =: v_Wknm) /* Here I will have to loop Each Week_name for Each Acct_ID */

This is what I have tried

DECLARE v_Init_Cnt NUMBER(10):=1;
        v_Tot_Cnt NUMBER(10);

BEGIN

    SELECT COUNT(*) INTO v_Tot_Cnt FROM DIM_DATE;

    WHILE(v_Init_Cnt <= v_Tot_Cnt)
    LOOP

    dbms_output.put_line(v_Init_Cnt);

    VARIABLE v_Id NUMBER(10);
    exec :v_Id:= v_Init_Cnt;

    dbms_output.put_line('var' || v_Id);

    SELECT * FROM FACT_TABLE
    WHERE WEEK_NAME between (select WEEk_BEGIN FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) and (select WEEk_END FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt)

    v_Init_Cnt :=  v_Init_Cnt +1;
    END LOOP;

END;

Is there any efficient and correct way to do this in oracle? Thanks in Advance

Just forget all that PL/SQL mess full of incorrect syntax, this is a single query:

with date_dim as
(
  select 1 as unique_id, 'Week 6, 2018' as week_name,
    '20180205' as week_begin, '20180211' as week_end from dual union all
  select    1, 'Week 5, 2018', '20180129', '20180204'from dual union all
  select 1, 'Week 4, 2018', '20180122', '20180128'from dual union all
  select 1, 'Week 3, 2018', '20180115', '20180121'from dual
),
fact as
(
  select 10001 as acct_id, '20180205' as week_name, 10  as sales from dual union all
  select 10001 as acct_id, '20180206' as week_name, 20  as sales from dual union all
  select 10001 as acct_id, '20180207' as week_name, 10  as sales from dual union all
  select 10001 as acct_id, '20180208' as week_name, 15  as sales from dual union all
  select 1002  as acct_id, '20180129' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180130' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180131' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180132' as week_name, 100 as sales from dual
)
select dd.week_name, sum(f.sales) as sales from date_dim dd
join fact f on (f.week_name between dd.week_begin and dd.week_end)
group by dd.week_name
;

That gives your needed output:

WEEK_NAME         SALES
------------ ----------
Week 5, 2018        400
Week 6, 2018         55


Execute Permissions for a Store Procedure that creates databases I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to run this Stored Procedure without granting them Create Database Permission.

I Tried
WITH EXECUTE AS OWNER/USER_NAME

The Analysts still get the below error:
Msg 262, Level 14, State 1, Line 67
CREATE DATABASE permission denied in database 'master'.

Any advice?

It did not work because the EXECUTE AS clause of a CREATE {object} statement can only reference a User, which is a database-level principal. CREATE DATABASE is an instance-level permission, so it needs to be granted to a login (an instance-level principal), not to a user.

This is easy to accomplish using Module Signing:

SETUP

USE [tempdb];
CREATE LOGIN [DbCreator] WITH PASSWORD='create a DB';
CREATE USER [DbCreator] FOR LOGIN [DbCreator];

EXEC(N'CREATE PROCEDURE dbo.CreateDatabase
(
  @NewDatabaseName sysname
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N''CREATE DATABASE '' + QUOTENAME(@NewDatabaseName);

EXEC(@SQL);
');

GRANT EXECUTE ON dbo.[CreateDatabase] TO [DbCreator];

APPLY MODULE SIGNING

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$CreateDatabase]
    ENCRYPTION BY PASSWORD = 'UseBetterPassword!'
    WITH SUBJECT = 'CREATE DATABASE permission',
    EXPIRY_DATE = '2099-12-31';

-- 2) Sign the Module:
ADD SIGNATURE
    TO [dbo].[CreateDatabase]
    BY CERTIFICATE [Permission$CreateDatabase]
    WITH PASSWORD = 'UseBetterPassword!';

-- 3) Backup the Private Key to a VARBINARY string
--    to be copied and pasted somewhere safe (optional):
SELECT CERTPRIVATEKEY(CERT_ID(N'Permission$CreateDatabase'),
                      'NewPassword!', 'UseBetterPassword!');

-- 4) Remove the Private Key (optional):
ALTER CERTIFICATE [Permission$CreateDatabase]
    REMOVE PRIVATE KEY;

-- 5) Copy Certificate to [master] (Public Key only):
DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'Permission$CreateDatabase')), 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$CreateDatabase]
FROM BINARY = ' + @Cert);

-- 6) Create Login and Grant Permission:
EXEC (N'USE [master];
CREATE LOGIN [Permission$CreateDatabase]
    FROM CERTIFICATE [Permission$CreateDatabase];

GRANT CREATE ANY DATABASE TO [Permission$CreateDatabase];');

TEST

EXECUTE AS LOGIN = N'DbCreator';
SELECT SESSION_USER;
-- DbCreator

CREATE DATABASE [_TEST:FAIL];
/*
Msg 262, Level 14, State 1, Line XXXXX
CREATE DATABASE permission denied in database 'master'.
*/

EXEC dbo.[CreateDatabase] N'_TEST:PASS';
-- Success!!

SELECT * FROM sys.databases WHERE [name] = N'_TEST:PASS';

REVERT;
SELECT SESSION_USER;
-- dbo
DROP DATABASE [_TEST:PASS];

For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, and why you should use it instead of the easier yet more dangerous SET TRUSTWORTHY ON, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


The memory space and ram space allocated to my SQL Server How do I find out how much memory space and RAM has been allocated to my SQL Server. SQL Server version is SQL Server 2012 11.0.5343.0.

Please help. I have searched online and all i can find is how much memory is being used by my SQL Server.

I need to find out how much memory and RAM have been allocated to my SQL Server already. So, that I can figure out how much memory and RAM are not being used.

Thanks!

sys.configurations contains various configuration parameters for SQL Server, including two that control the memory used by SQL Server:

SELECT c.name, c.value_in_use
FROM sys.configurations c
WHERE c.name like '%server memory%';

Results from my machine:

+------------------------+--------------+
|          name          | value_in_use |
+------------------------+--------------+
| min server memory (MB) |          256 |
| max server memory (MB) |         1024 |
+------------------------+--------------+

It will tell you the maximum and minimum amount of memory that SQL Server will use.  If the number is 2147483647 that indicates SQL Server will use as much memory as it can get, assuming it needs it.  This is the default value for max server memory (MB).

This query shows various memory quantities, including how much is actually in use by SQL Server:

SELECT upvt.Item
    , upvt.Value
FROM (
    SELECT *
    FROM sys.dm_os_sys_info dosi
    ) src
UNPIVOT (
    [Value] FOR Item IN (
          committed_kb
        , committed_target_kb
    )
) upvt

committed_kb represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager.

committed_target_kb represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:

the current state of the system including its load
the memory requested by current processes
the amount of memory installed on the computer
configuration parameters

If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory.

sys.dm_os_sys_memory shows the state of system memory; that is memory visible to Windows itself:

SELECT upvt.Item
    , upvt.[Value]
FROM (
SELECT    [Total size of physical memory available to the O/S, in (KB)] = CONVERT(varchar(50), total_physical_memory_kb)
        , [Size of physical memory available, in KB] = CONVERT(varchar(50), available_physical_memory_kb)
        , [Size of the commit limit reported by the operating system in KB] = CONVERT(varchar(50), total_page_file_kb)
        , [Total amount of page file thatis not being used, in KB] = CONVERT(varchar(50), available_page_file_kb)
        , [Total amount of system cache memory, in KB] = CONVERT(varchar(50), system_cache_kb)
        , [Total amount of the paged kernel pool, in KB] = CONVERT(varchar(50), kernel_paged_pool_kb)
        , [Total amount of the nonpaged kernel pool, in KB] = CONVERT(varchar(50), kernel_nonpaged_pool_kb)
        , [Description of the memory state. See the table below] = CONVERT(varchar(50), system_memory_state_desc)

FROM sys.dm_os_sys_memory
) src
UNPIVOT (
    [Value] FOR Item IN (
        [Total size of physical memory available to the O/S, in (KB)]
        , [Size of physical memory available, in KB]
        , [Size of the commit limit reported by the operating system in KB]
        , [Total amount of page file thatis not being used, in KB]
        , [Total amount of system cache memory, in KB]
        , [Total amount of the paged kernel pool, in KB]
        , [Total amount of the nonpaged kernel pool, in KB]
        , [Description of the memory state. See the table below]
        )
    ) upvt;

Results on my system:

+-----------------------------------------------------------------+-----------------------------------+
|                              Item                               |               Value               |
+-----------------------------------------------------------------+-----------------------------------+
| Total size of physical memory available to the O/S, in (KB)     | 16662488                          |
| Size of physical memory available, in KB                        | 2496588                           |
| Size of the commit limit reported by the operating system in KB | 33323116                          |
| Total amount of page file thatis not being used, in KB          | 17849648                          |
| Total amount of system cache memory, in KB                      | 3447208                           |
| Total amount of the paged kernel pool, in KB                    | 1186844                           |
| Total amount of the nonpaged kernel pool, in KB                 | 257484                            |
| Description of the memory state. See the table below            | Available physical memory is high |
+-----------------------------------------------------------------+-----------------------------------+


Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system? We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?

Bulk Logged will not help you in this situation.  Your transaction log is growing because you are performing large transactions.  In simple mode, the log is truncated after a checkpoint.

Depending on how you are loading your data, you may need to break it up into smaller chunks.  Make sure you are not loading all the data in one large transaction.


MSSql bcp transaction rollback on error I am trying to bulk insert data from a file to MS SQL Server using the bcp utility but it is not functioning as I expect it to with regard to errors. I want to understand what I'm doing incorrectly or where my assumptions are incorrect.

With the below command:

bcp mydatabase.dbo.mytable IN "c:\infile.txt" -f "c:\infile.fmt" -T -m0 -S myserver -e "c:\errors.txt"

I expect bcp to include all records in the file in a single batch and to fail after encountering the first error and to report the errors in the error file. It however completes by importing as many records as possible through the whole file. It doesn't stop or roll-back any transaction.

If I change the -m option to -m 1, then it does fail after the first record, but does not roll back the batch at all (e.g. the records in the batch imported prior to the error are persisted in the import table after the bcp finishes).

The documentation of bcp appears to indicate that the batch would be rolled back on encountering the first error, and that the batch will include all records in the file if no batch size is specified.

The bcp utility version is 13.0.1601.5.

from:Microsoft BCP Utility Documentation

  -b batch_size

  Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that
  imports the whole batch before being committed. By default, all the
  rows in the data file are imported as one batch. To distribute the
  rows among multiple batches, specify a batch_size that is smaller than
  the number of rows in the data file. If the transaction for any batch
  fails, only insertions from the current batch are rolled back. Batches
  already imported by committed transactions are unaffected by a later
  failure.

and

  -m max_errors

  Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. A syntax error implies a
  data conversion error to the target data type. The max_errors total
  excludes any errors that can be detected only at the server, such as
  constraint violations.

  A row that cannot be copied by the bcp utility is ignored and is
  counted as one error. If this option is not included, the default is
  10.

A rollback of a BCP batch transaction occurs when SQL Server errors occur during the bulk insert (e.g. constraint violations) but not due to client side errors (e.g. data type conversion during BCP parsing). Erred records are not sent to the server and are not part of a transaction.

SQL Server errors will not only rollback the current batch but abort the entire BCP operation.


SQL Server 2000 dateformat permanent change I am using SQL Server 2000 for an old system web site. I've just hit an issue that is going to cause me endless hassles down the road.

When I installed the server on my local system I took all the defaults but now any datetime data type is getting stored in mm/dd/yyyy format.

Now this is not an issue for inserting data but when getting the data out and writing it to a page its in US format.

The site is going to be based in the UK on a UK server so I need my local development system to mirror that, and I also want to have to avoid using CONVERT on every field which is a datetime.

Tried changing the database collation a few times without success.

So my question was this:

Is it possible to change the format of the datetime so that inserting 23/01/1972 is stored as such and not 1/23/1972?

SERVER Properties:

Default language: English (United States)
Collation: SQL_Latin1_General_Cp1_CI_AS

Database Properties

Collation: SQL_Latin1_General_CP1_CI_AS

Specifically as as example

CREATE TABLE [dbo].[lbg_app_section1](
    [id_app_section1] [int] IDENTITY(1,1) NOT NULL,
    [refid_applications] [int] NOT NULL,
    [dateofbirth] [datetime] NULL,
    [gender] [nvarchar](50) NULL,
    [ninumber] [nvarchar](15) NULL,
    [address_hsno] [nvarchar](60) NULL,
    [address_twncty] [nvarchar](60) NULL,
    [address_cnty] [nvarchar](60) NULL,
    [address_postcode] [nvarchar](15) NULL,
    [address_country] [nvarchar](30) NULL,
    [dt_section1_modified] [datetime] NULL
) ON [PRIMARY]

As stated in the comments, dates are not stored as "dates".  They are actually stored as numbers.  So there is no need to worry about that side of things.  You can change the default output though by changing your language setting.

EXEC sp_configure 'default language', '23';
GO
RECONFIGURE
GO

This is the setting for British English.  You can see all of them displayed in the syslanguages system view.  syslanguages also contains the default date format.  In the case of British English it is dmy.  American English is mdy.


Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" missing I try to run an application where its database on postgresql. It won't work and I'm getting the following error whenever I try to interact with the Postgres service:

could not connect to server: Aucun fichier ou dossier de ce type
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432`

I checked if Postgresql service is running

sudo systemctl status postgresql

postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enab
   Active: active (exited) since Thu 2019-04-04 10:28:27 +01; 58min ago
 Main PID: 1212 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 3522)
   CGroup: /system.slice/postgresql.service

avril 04 10:28:27 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
avril 04 10:28:27 ubuntu systemd[1]: Started PostgreSQL RDBMS.

checked the content of the socket unix connection

ls /var/run/postgresql/

got this empty folder

brahim@ubuntu:/var/log/postgresql$ ls /var/run/postgresql/
10-main.pg_stat_tmp

checked the content of the data directory

ls /var/lib/postgresql/10/main/

it contains the following

 base         pg_logical    pg_serial     pg_subtrans  pg_wal
 global       pg_multixact  pg_snapshots  pg_tblspc    pg_xact
 pg_commit_ts  pg_notify     pg_stat      pg_twophase
 postgresql.auto.conf pg_dynshmem   pg_replslot   pg_stat_tmp
 PG_VERSION   postmaster.opts

the file postgresql.conf has the following configuration

  # - Connection Settings -
  #listen_addresses = 'localhost'
  port = 5432
  max_connections = 100
  unix_socket_directories = '/var/run/postgresql'

I tried to uncomment the listen_addresses instruction and it didn't work, also I've tried with '*' for all, same thing

the postgresql file log has

2019-04-04 12:17:57.765 +01 [942] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-04 12:17:57.767 +01 [942] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-04-04 12:17:58.190 +01 [971] LOG:  database system was shut down at 2019-04-03 20:19:54 +01
2019-04-04 12:17:58.192 +01 [971] PANIC:  replication checkpoint has wrong magic 1886413116 instead of 307747550
2019-04-04 12:17:59.900 +01 [942] LOG:  startup process (PID 971) was terminated by signal 6: Aborted
2019-04-04 12:17:59.900 +01 [942] LOG:  aborting startup due to startup process failure
2019-04-04 12:17:59.902 +01 [942] LOG:  database system is shut down

I am running postgresql 10.6
any thoughts

Try passing the -a option to ls to also show files starting with the period . character (i.e. "hidden" files):

$ ls -a /var/run/postgresql/
.  ..  .s.PGSQL.5432  .s.PGSQL.5432.lock


Mirroring SQL 2008 R2 database I have just started as a DBA and my first task is to move a 4TB database from a physical server to a virtual server with little down time.

I have found a way to copy the database across, but not how to keep it up to date without having a current full/log backup.

Does any one know of a way I can mirror the database without having to make a full backup and mirror transaction logs?

I have just started as a DBA

Solving problems using Backup and Restore is the bread-and-butter of a DBA.

  move a 4TB database from a physical server to a virtual server with little down time.

Define "little".

The safest, simplest thing is to leave the database in Simple recovery, and use Full/Differential backups to move the data.

You can also switch to Full recovery and use Log backups instead of Differential backups.

In both cases you would

1) Take a Full backup

2) Restore the Full backup on the new server WITH NORECOVERY

3) Begin Downtime

4) Take a final Log Backup or Differential Backup

5) Restore the all the Log Backups or the Differential Backup WITH RECOVERY

6) Set the database OFFLINE on the old server

7) End Downtime

You can perform steps 1, 2, 4, and 5 as many times as you want in testing, and to measure the required downtime.


How to write a query to identify common combinations of parameters used to call a stored proc? recently I deployed a change into our production environment that ALTER'd an existing stored proc and essentially all the addition did was INSERT into a table I created the parameters passed in by the user calling that stored proc.

Example: User from finance passes in 15/20 possible parameters in his front-end application.. those exact parameters are first INSERTed into my table where each column is a parameter.  If the user enters in nothing for a given parameter (because they are optional) the cell will be empty (not NULL).

So again, I need to aggregate and figure out what is the most common combination of parameters that are actually used (I do not care about the actual values, just that the parameter was used or not).

To clarify my table.. Each row is one stored proc call and each column is a parameter.

My table:

You need to find all of the distinct groups of parameter values for a given proc, and then sort by the count of those groups.  Something like this:

SELECT
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99
ORDER BY
    COUNT_BIG(*) DESC;

That would give the different parameters that sp_YourProc was called with, ordered from most common to least common.

If you don't care about the values, and you just want to know that certain combinations of parameters are being used, you could substitute static values like this:

SELECT
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
ORDER BY
    COUNT_BIG(*) DESC;


How to display '0' where no data found in PL/SQL Oracle Reports I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows.

Data1            25,000
Data2
Data3            23,000

Now I want to show a simple '0'in front of an empty record (i.e. Data2). I've tried it in adding a Formula Column (PL/SQL), or by initializing a '0' in the property palate of field by defining '0' against "Value if null". But, it doesn't work.

However, when I remove the format mask, value '0' is displayed by both above mentioned ways (by adding a formula column or by defining the value against 'value if null'). But, by doing this, the amount is displayed without format mask (i.e. 25000 and 23000).

How can I achieve the above discussed scenario. I want that the format mask should be there, and there should b '0' in front of empty records as well. In short, I need the following output in Oracle Reports 11g - 64 bit.

Data1            25,000
Data2                 0
Data3            23,000

The format mask needs to be changed to 999,999,999,990.  The 9 format suppresses the value if it is a leading zero, whereas 0 prints it.


How to prevent scheduler jobs from running at instance startup I'm currently testing Oracle 11g restore procedures from RMAN backups. I can restore instances to another host, but problem I'm having is that as soon as I open the restored instance, scheduler jobs start immediately.

I can disable scheduler by running:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

But I can't do this until after instance has started.

Is there a way to disable jobs before starting up the instance?

Set the job_queue_processes parameter to 0 before opening the database.


How do I query PostgreSQL enums like in MySQL? With the following table in MySQL:

CREATE TABLE bob(foo ENUM('a','b','c'));

INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

SELECT * FROM bob WHERE foo >= 2;
+------+
| foo  |
+------+
| b    |
| c    |
+------+

With the following table in PostGres:

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
INSERT INTO bob (foo) VALUES ('a'), ('b'), ('b'), ('c'), ('c');
SELECT * FROM bob WHERE foo > 2;
(HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.)

I've read that there are some fancy ways of getting around this problem, but is there a way to create a Type that mimics the MySQL capabilities?  Or is there a data type with the capabilities that I want?  (Ability to check string on insert, but also to make where cases against an aggregate).

I think you have a misconception about ENUM because of MySQL breaking the spec and being goofy. They're different in both MySQL and PostgreSQL, but they're more apparently different in PostgreSQL. You're trying to seamlessly query an internal representation detail. I'm actually glad PostgreSQL prevents it.

Instead use the ENUM support functions

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
SELECT * FROM enum_range('b'::stuff, NULL);

This returns everything from 'b' to the unbounded top of the enum (in this case 'c')

Now create a table and insert some values,

CREATE TABLE bob ( foo stuff );
INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

Now to query it, do this

SELECT *
FROM bob
WHERE foo = ANY(enum_range('b'::stuff, NULL));

Or more simply as Abelisto suggested in the comments. Note that the details are still abstracted. You don't know what the sort order of b is,

WHERE foo >= 'b'::stuff;
WHERE foo >= 'b';

Enums are ordered by default. In PostgreSQL they're stored as int4. In MySQL they're stored as int2 or int1. However, their implementations should not be revealed to the user.

Just think, in your query above, reloading from a database after you add a value could potentially re-order the enums. Then your query breaks because you've assumed a specific value for 2.

See also,

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?
How does MariaDB handle joins on ENUMs?


Create index if it does not exist I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?

This is a similar issue to the column creation one that is solved with this code:
https://stackoverflow.com/a/12603892/368511

Index names in PostgreSQL

Index names are unique across a single database schema.
Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)

If you do not care about the name of the index, have Postgres auto-name it:
CREATE INDEX ON tbl1 (col1);

is (almost) the same as:
CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Except that Postgres will avoid a naming collisions and automatically pick the next free name:
tbl1_col1_idx
tbl1_col1_idx2
tbl1_col1_idx3
...

Just try it. But, obviously, you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.
Test for existence
Postgres 9.5 or newer
Now available:
CREATE INDEX IF NOT EXISTS ...
Also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.
However, the manual warns:

Note that there is no guarantee that the existing index is anything
like the one that would have been created.

It's a plain check for the object name. (Applies to variants for older versions below, too.)
To find existing indexes on the same table for the same column(s):
SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

Only works for columns, not other index expressions.
Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).
Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...
Further reading:

Find tables with multiple indexes on same column
Normalize array subscripts for 1-dimensional array so they start with 1

Postgres 9.4
You can use the new function to_regclass() to check without throwing an exception:
DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

Returns NULL if an index (or another object) of that name does not exist. See:

How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.
Postgres 9.3 or older
Cast the schema-qualified name to regclass:
SELECT 'myschema.myname'::regclass;

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, use a DO statement:
DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN

        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.


How to get super parent from table with self relationship i need to create Function that will return the super parent of a given id from a table

the table look like table1 (id, parentId, someData)
the parentId is related to the id

for example i have 1 - 2  - 3  - 4 id of each row

id   parentId
1    null
2    1
3    2
4    3

now when i give the Function 4 as parameter it will return 1

i'm using SQL server 2008

how can this be done

Something like this

/*
create table #table1 (id int, parentId int)

INSERT INTO #table1
VALUES (1,null),
(2,1)
,(3,2)
,(4,3)
,(5,4)
,(10, null)
,(11,10)
,(8,11)
*/
declare @child int;
set @child=4;
with prt as
(select 1 rn,id, parentid from #table1 where id=@child
union all
select rn+1 rn, t.id,t.parentid from #table1 t join prt on prt.parentid=t.id
where t.parentid is not null
)
select top 1 parentid from prt order by rn desc

Recursive CTE


Different results from sys.dm_db_file_space_usage with database prefix To make it simple:

According to MSDN (link) among other sources, you can get the amount of current free space in tempdb by using the following bit of TSQL:

-- MSDN version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Now, we're using a scheduled script that dynamically does what it can without going overboard with space usage. Prior to today, the MSDN version worked fine in environments using clustered SQL Server 2008's, but today I found out that one of our new environments with SQL Server 2012 and AlwaysOn Availability Groups behaves differently.

There, the "MSDN version" returns almost no free space at all, while the "other" version correctly shows the available free space in tempdb.

Can someone explain this to me? Is there a shared tempdb among all the AlwaysOn replica groups so that by default, not specifically defining tempdb somehow defaults to some local version of tempdb, or what is really going on here? Can I trust that the queries run against the db actually utilize tempdb.sys.dm_db_file_usage_space as opposed to whatever it is that regular sys.dm_db_file_usage_space is pointing out to?

I'm trying to find out more about this myself, but we need an answer quite urgently, so I'm asking here just to be sure. Thanks!

It looks like that particular DMO was changed to work with all databases. This can be demonstrated by running the following scripts on a 2008r2 server.

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM model.sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

As you can see the output will be the same. However, if you run the same scripts on a 2012 server the output will be different.

Technet explains that in the 2008R2 the DMO only worked on tempdb http://technet.microsoft.com/en-us/library/ms174412(v=sql.105).aspx and in 2012 that restriction was removed http://technet.microsoft.com/en-us/library/ms174412(v=sql.110).aspx.

Hope That Helps.


Setting up Oracle publishers in SQL Server replication I am attempting to set up and oracle publisher on a sql server 2008R2 server, but I am getting the following error.

"Unable to run SQL*PLUS. Make certain that a current version of the Oracle client code is installed at the distributor.  For addition information, see SQL Server Error 21617 in Troubleshooting Oracle Publishers in SQL Server Books Online. (Microsoft SQL Server, Error:21617)"

The information I have found states that an oracle client must be installed and the oracle_home\bin must be in the path variable.  I have verified that it is.

So far I have taken the following steps
Installed oracle administrator client
Added TNS_ADMIN env variable
Added ORACLE_HOME env variable
connected to the remote oracle database from the distributor via sql*plus

I am hoping someone will have run into similar errors in the past

Well after a call to microsoft I got the problem resolved.  Turns out it was an easy fix, SQL Server had to be restarted.  It looks like sql server was not picking up the updated path variable, so it wasn't finding the oracle binaries.  Luckily, while a production server, I was able to schedule a quick restart to resolve the problem.  After that sql was able to connect to the oracle database, for the rest I was able to follow microsoft's documentation pretty closely and get the replication working.

I wanted to make sure I update this, because I didn't see anything in the documentation saying sql had to be restarted after installing the oracle client, and hopefully it will be helpful to someone else.


Modeling Graph Data in Cassandra DB I want to use Apache Cassandra to store a large amount of graph data according to a property graph model. The model contains the following entities:

Vertices: Contains a map of key/value pairs (properties). Some keys should be indexed for querying (see below).
Edges: Connects two vertices to each other in a given direction. Contains a label and possibly some edge data. The edge data is a map of key/value pairs, where some keys should also be indexed for querying.

Both vertices and edges have a unique primary key, which can be a string or integer value.

Example:

#A vertex
{node_type:'module',pk: 1,...}
#Another vertex
{node_type:'function',pk: 2,...}

#An edge
{incoming_vertex: 1,outgoing_vertex: 2,label: 'body',data : {}}

I want to perform the following types of queries on the graph:

Retrieve a list of vertices based on their primary key (e.g. "fetch the vertex with pk = a5f...") or the value of one or several indexed properties (e.g. "fetch all vertices with node_type = 'module' and ...").
Traverse the graph from a given vertex along its edges, using the edge label, direction and one or several indexed edge properties to determine the path taken (e.g. "fetch all vertices that are connected to vertex A through an outgoing edge with label body and property ... = ...).

In addition, I have the following requirements and boundary conditions:

Retrieving the list of edges for a given vertex should be as efficient as possible (O(1) ideally).
The number of edges will be much larger than the number of vertices in the graph.
The model should scale to several billion vertices and several hundred billion edges (appropriate hardware provided).
The graph data will usually be written only once and read many times, so the model can be optimized for query performance at the cost of write performance.

My initial idea for a data model is the following:

Use one column-family for vertices as well as edges respectively, where the row-key is the primary key of the vertex/edge and a single text column contains its JSON data. Indexes on vertex/edge properties are modeled as additional columns (whose data is denormalized and manually updated whenever the vertex/edge data changes)
Use one dynamic column family for managing the adjacency (edge) list for vertices, with a composite primary key that contains the primary key of the vertex, the primary key of the edge, the edge label and the edge direction (incoming or outgoing) for each vertex.

Is this a sensible data model? Any other suggestions on how to implement this?

For Graph database on Cassandra have a look at TitanDB:

What you need is already implemented in TitanDB. Implementing your own Graph Database is not trivial, and would be very time consuming. In most cases, a proven solution is best. (BTW, I am not involved in TitanDB development or business.) I have no idea about your use case, but I do not see a reason to implement something new, except as a hobby.

Update I found a whitepaper about Titan GraphDB's data model in database: https://github.com/thinkaurelius/titan/wiki/Titan-Data-Model. It gives some hints how to design a datastore for graphs.

Aurelius is now also part of Datastax and they work on a combined solution for storing big graphs in Cassandra.


How do query notifications detect changes in the query result? I am working with a legacy database that has implemented query notifications against a table called ColorList.

While running a server-side trace for an unrelated issue, I noticed the query

SELECT color FROM ColorList

was being executed every 10 milliseconds.

Is this how Query Notification works? Does SQL Server store the results of the original query and then run the query endlessly until it detects a change?

Read the Mysterious Notification for an explanation on how it works. If you see the query executed every 10ms it means your application is running it every 10ms. My 8 ball is telling me your code does not check the notification EventArgs and is blindly resubmitting the query despite the Info being of the value Invalid, indicating a query than cannot be notified. Which is to be expected, since your query does not conform to the Query Notification restrictions:

  The projected columns in the SELECT statement must be explicitly
  stated, and table names must be qualified with two-part names.


Trouble killing sessions then immediately dropping users in Oracle 11g XE I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call.  Here is what I have so far:

DECLARE
cursor c is select *
                 from v$session
                where username = 'USER_9FFB9';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter system kill session ''' ||
                 c_row.sid || ',' || c_row.serial# || '''';
    execute immediate 'drop user user_9FFB9 cascade';
    end loop;
end;
/

The problem with this is that I'm getting back this error when I add the drop user call right after the kill session call:

ORA-01940: cannot drop a user that is currently connected

However, if I execute the drop user statement after waiting only a couple of seconds in a separate statement, it works fine.  What do I need to do to be able to kill the sessions and drop the user in one go?  Is there a loop on v$session that I should do, checking to see if it's still open?

Update  I've updated my script to this:

DECLARE
cursor c is select *
                 from v$session
                where username = 'USER_abc';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter user USER_abc ACCOUNT LOCK';

        execute immediate 'alter system kill session ''' ||
                 c_row.sid || ',' || c_row.serial# || '''';

    end loop;
    DBMS_LOCK.SLEEP(5);
end;

/

drop user user_abc cascade

I'm still getting this error, however:

ORA-01940: cannot drop a user that is currently connected

Is there a commit or something like that which I need to add in there?

COMPLETED SCRIPT

With Justin Cave's help, here is the final script I have which works great:

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user user_#databaseName# account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'USER_#databaseName#')
  LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'USER_#databaseName#';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 2 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user user_#databaseName# cascade';
END;

Obviously #databaseName# is a placeholder for a simple value like ABC.  Thanks Justin!

When you issue an ALTER SYSTEM KILL SESSION, Oracle merely marks the session as killed and does the actual work of killing the session asynchronously.  That may take just a couple seconds, it may take many hours if the session has an uncommitted transaction that did a lot of work that now has to be rolled back or if the session needs to stay around in a killed state in order to notify the client that their session was terminated.  It may, therefore, require a substantial amount of waiting before a user could be dropped.

You could do something like this where your loop will wait indefinitely for all the sessions to disappear.

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user abc_user account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'ABC_USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'ABC_USER';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 10 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user abc_user cascade';
END;

Generally, though, I'd question the problem you're trying to solve.  I can't think of many times that it would make sense to drop a user that has active sessions at the time you decide to issue the drop.  In the vast majority of cases, the presence of active sessions strongly implies that the account should not be dropped.


I want to set lower_case_variables to 1 but I'm unable to locate my.cnf MySQL was started with service mysql start only, but it is not showing the default path of my.cnf:

[root@localhost home]# ps -ef|grep mysql
root     23505     1  0 19:23 pts/4    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql    23596 23505  0 19:23 pts/4    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid
root     23713 19420  0 19:49 pts/4    00:00:00 grep mysql

my.cnf is not under all our default locations.

When I start MySQL by creating a new my.cnf it does not start up.

I want to restart MySQL & add a variable in my.cnf.

When I remove my.cnf from /etc, MySQL is starting up with out any errors.

To locate all "my.cnf" in the system:

$ find / -name my.cnf


A very strange thing in the tempdb In all the following scenarios on my laptop, all the tables in the SSMS Tables folder will be deleted from tempdb:

Instance Restart;
Stop & Start SQL Server Service;
End Task SQL Server process in task manager;
OS (WINDOWS) Restart.

I have no problems with the above.

But …

The following scenario is very interesting:

First, I create a table in tempdb:

USE tempdb;
GO
CREATE TABLE Test_Table
(ID INT,
Title VARCHAR(100));
GO
INSERT INTO Test_Table
VALUES
(1,'Test01'), (2,'Test02'), (3,'Test03');
GO

Then I exit SSMS and shut down Windows.

A few days later …

Turn on my laptop.

When I get into SSMS, I'm surprised to see that there is a Test_Table with its records in the tempdb.

Why? I did not create such a table in the model database.

I thought so much about this.

Can the following explanations prove this hypothesis?

You should be aware that Windows shut down is much faster than restarting.

It is likely that in Windows restarting, the SQL Server service will also be restarted, and to end the service, it should release the resources it has, as well as empty the contents of tempdb.

But in the Windows shut down process, the Windows shut down service is likely to be responsible for releasing resources, and in fact kill all processes, and obviously there is no accident in the tables in the Tables folder of tempdb, Because the SQL Server service does not restart.

I tested this in versions SQL Server 2019, 2017 and 2008.

Do you think your guess is correct?

I'd be happy to express your opinion about this.

You probably created another test_table in the model database, so then TempDB inherits that on startup.


SQL Server is unable to connect to server , requires the actual server name I buy a cloud vps on vsp.ag , which is ubuntu os and I install sql server
using this .
I can remotely connect from my local PC using IP address of VPS . But I can't configure the distribution.

It said

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name,

'v3149'. (Replication.Utilities)

What I want to do is synchronization my local database and the database on vps using merge replication .
How can I fix it ?

According to Release notes for SQL Server vNext on Linux, under the section Unsupported features and services, Replication is not currently supported.


How do I know my Oracle database is correctly configured and running? I've installed a fresh Oracle 19.3 on Linux CentOS 8.3. I used the install steps (manual install) from this official Oracle guide.
I tried to start the DB using the standard script:
[oracle@localhost admin]$ /home/oracle/scripts/start_all.sh
The Oracle base remains unchanged with value /opt/u01/app/oracle
[oracle@localhost admin]$ ps -aux | grep oracle
root        3309  0.0  0.1 137504  7532 pts/1    S    Apr19   0:00 su oracle
oracle      3314  0.0  0.0  25680  5632 pts/1    S    Apr19   0:00 bash
oracle      8756  0.6  0.5 224100 28768 ?        Ssl  01:59   0:00 /opt/u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle      8892  0.0  0.0  61604  4024 pts/1    R+   01:59   0:00 ps -aux
oracle      8893  0.0  0.0  12112  1052 pts/1    S+   01:59   0:00 grep --color=auto oracle

I see only one oracle process, the listner. I don't see a process named cdb1*, which is the name of the DB I installed.
I have a Basic client and a SqlPlus client installed. When I try to connect via SqlPlus I get this:
[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 01:12:36 2021
Version 19.3.0.0.0

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

Enter user-name: oracle
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: 1804505411
Process ID: 0
Session ID: 0 Serial number: 0

I put tnsnames.ora at $ORACLE_HOME/network/admin:
[oracle@localhost admin]$ ls -l /opt/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/
total 8
drwxrwxr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rwxrwxr-x. 1 oracle oinstall 1536 Feb 13  2018 shrept.lst
-rwxr-xr-x. 1 root   root      391 Apr 20 01:29 tnsnames.ora

Here is the content of my tnsnames.ora (I'm really not sure about this, don't know what service name to use - I understood it is not the SID):
[oracle@localhost admin]$ cat /opt/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/network/admin/tnsnames.or
a
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.129)(PORT = 1521))

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

tnsping gives this:
[oracle@localhost admin]$ tnsping orcl 5

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-APR-2021 02:25:13

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)

So, I don't know if my DB is really running. I don't know if the tnsnames.ora is correct. I'm not a dba, I need this Oracle DB for a testing environment. Could someone guide me through this please?

I found the issue. The DB wasn't starting because I forgot to update /etc/oratab after the dbca install. Should have updated the db parameter with "restart:Y".
cdb1:/opt/u01/app/oracle/product/19.3.0/dbhome_1:Y


potgresql foreign key index unused for some columns on one specific server I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and sub_type, respectively.

My problem is that while indices are present for both columns, they are not used for sub_type_id, so from the below SQL queries:

EXPLAIN ANALYZE SELECT * FROM publication WHERE type_id=1068114;
EXPLAIN ANALYZE SELECT * FROM publication WHERE sub_type_id=1068114;

The first shows an index scan, the second shows a sequential scan. This is even true if I set enable_seqscan=off.

What is even more frustrating is that doing the same on my local server, both SELECTs use indices (both running 9.4 postgres on debian).

There is obviously a difference in either the indices or the columns on the server, but I do not know what. How can I find out why one index is used, and the other is not?

It seems that the table publication needed a VACUUM FULL. After freeing up enough space for that and running it, new indices seem to work now.


Stress Test to Saturate Memory for MySQL InnoDB I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of memory.

Perhaps a query I could use for mysqlslap?

Update

I thought other people would benefit from a simple way to load test mysql and this was an easy question where lots of people would have "tools". They would help my obscure situation and I could use to investigate our real problem.

We have our configuration tuned, and it was fine for months but recently we eventually get mysqld: Out of memory (Needed 220428 bytes) and MySQL will crash minutes later.

I'm not researching your usual "whoops I allocated too much to my buffer pool" problem. That memory is allocated at start up and improper setting causes immediate shutdown. I'm really after the other 20%. Just creating the maximum number of connections with a high buffer pool hasn't proven to reproduce the problem so I want to stress everything. I am not ruling out stressing the buffer pool in case we are actually suffering a MySQL bug relating to it.

Here is an information dump for people interested:

/3GB to overcome 2GB for mysql - we haven't set /LARGEADDRESSAWARE but we do have /3GB and /PAE with 8gb memory
We are at risk of low System Page Table Entries but last we saw over 18,000 (not a lot but enough, it stayed at this level at last crash)
Mysql keep fixing memory related errors in their change logs
Mysql 5.1 let you put the inndb buffer pool in Address Windowing Extensions (i.e. use PAE) with innodb_buffer_pool_awe_mem_mb but MySQL 5.5 has no such option

I'm no authority on any of the above I just want to be able to produce high memory activity so we can A) reproduce the problem and then B) solve it in the medium term before moving off 32bit, which is the real solution but it won't happen tomorrow.

Update 2

Turns out Mysql 5.5's mysqld.exe is already built to be /LARGEADDRESSAWARE but cannot set the innodb engine size equal to or greater than 1600M. Connections do use the extra memory from /3GB (proven with mysqlslap).

vcvars32.bat
dumpbin.exe /headers mysqld.exe
Microsoft (R) COFF/PE Dumper Version 10.00.40219.01
Copyright (C) Microsoft Corporation.  All rights reserved.

Dump of file mysqld.exe

PE signature found

File Type: EXECUTABLE IMAGE

FILE HEADER VALUES
             14C machine (x86)
               5 number of sections
        4E6A3CEF time date stamp Sat Sep 10 04:21:03 2011
               0 file pointer to symbol table
               0 number of symbols
              E0 size of optional header
             122 characteristics
                   Executable
                   Application can handle large (>2GB) addresses
                   32 bit word machine

My conclusion. If you don't use /3GB or otherwise set connection memory + innodb buffer memory greater than available memory you are bound to eventually get mysqld: Out of memory... although mysql seems to do it's best to reject connections to avoid this problem.

Take home message, test that you can actually achieve max_connections with mysqlslap and the rest comes down to maintaining OS memory (and other resources like free system PTE).

From Windows Platform Limitations in the MySQL 5.5 Reference Manual:

  On Windows 32-bit platforms, it is not possible by default to use more than 2GB of RAM within a single process, including MySQL.

What's to investigate?  We already know what happens when you max the memory: Really Bad Things™.

If there's a remote chance that your setup will periodically require this much memory, 32-bit Windows is the wrong platform.

Almost universally, the biggest memory consumer is of course the InnoDB Buffer Pool, but there's no need for any queries to test this... you don't need any activity at all, or any tables, because the entire amount of memory declared for innodb_buffer_pool_size is allocated immediately when the server starts.  The buffer pool never grows, never shrinks, never changes, ever.  The number of free pages changes, but those are not "free" from the operating system's perspective -- they're still just as allocated, merely marked as containing nothing of interest within InnoDB.

If the operating system refuses to allocate the amount of memory provisioned for the buffer pool, MySQL will simply refuse to start.

This is illustrated here, where the OP mistakenly believed that the server was crashing and restarting "because" memory couldn't be allocated for the buffer pool, but was in fact crashing for a Linux-specific reason but then refusing to restart because the system would not allocate the total amount of memory required for the pool, due to overuse of available memory by something else... but this allocate-all-at-startup behavior for the InnoDB buffer pool is not platform-specific.

So, you should be able to set this value near the max and then find that taking the server process over the edge should not require very much additional effort at all.  But I'm still not sure what the point is.

As you realize, MySQL uses memory for a variety of different purposes, several of which are dynamically-sized, definable on a per-connection basis, and allocated on demand, which makes it virtually impossible to provision a server based on limiting memory usage to some worst-case scenario absolute value, yet expecting that server to be able to handle its typical load efficiently.

The simplest illustration of this is the fact that you can obviously reduce the theoretical maximum memory utilization of a given instance by restricting the maximum number of simultaneous client connections... but any given application needs a certain number of available connections to perform efficiently, and if that number is below your target value, then you're not really solving anything -- it just feels like you have.

I say, either your server has enough memory for the workload, or it doesn't.  If it doesn't, then attempting to "tune" your way out of potential trouble is unlikely to offer much in the way of solutions.

Some ideas on how to easily generate demand for more memory...

SELECT * FROM large_table ORDER BY non_indexed_column;

SELECT * FROM large_table WHERE non_indexed_column = some_value;

SELECT * FROM large_table WHERE some_column LIKE '%a_freqent_match%';

Queries like these could trigger the allocation of a sort buffer, a read buffer and/or a random read buffer, which should make a new request to the OS for the memory that buffer requires.

Simple solution:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`eat_memory_until_server_crashes` $$
CREATE PROCEDURE `test`.`eat_memory_until_server_crashes`()
BEGIN

-- this procedure is intended to eat as much memory as it can
-- it creates a series of consecutively-numbered session variables as large
-- as your configuration will allow them to be.

-- do not run this unless you intend to crash your server

-- also, do not run from a gui tool -- use the mysql command line client:

-- mysql> CALL test.eat_memory_until_server_crashes;

-- if you kill the query or thread before the server crashes,
-- the memory consumed will be returned to the OS

  DECLARE counter INT DEFAULT 0;
  LOOP
    SET counter = counter + 1;
    SET @qry = CONCAT('SET @crash_me_',counter,' := REPEAT(\'a\', @@max_allowed_packet)');
    SELECT counter, @qry;
    PREPARE hack FROM @qry;
    EXECUTE hack;
    DEALLOCATE PREPARE hack;
    -- adjust timing or remove this entirely depending on how quickly you want this to happen
    DO SLEEP(0.1);
  END LOOP;

END $$

DELIMITER ;

Inspiration for this: Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12194). OReilly Media - A. Kindle Edition.


Why is SQL Server using a clustered index scan for a self referencing FK cascade delete I have made an example to allow me to show what my issue is:

Setup:

CREATE TABLE [dbo].[Test](
    [TestId] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentTestId] [bigint] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC)
)
GO

ALTER TABLE [dbo].[Test]  WITH CHECK ADD  CONSTRAINT [FK_Test_ParentTest]
      FOREIGN KEY([ParentTestId])
REFERENCES [dbo].[Test] ([TestId])
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_ParentTest]
GO

DECLARE @iter INT
SET @iter = 1
WHILE @iter < 1000
BEGIN
    INSERT INTO dbo.Test ( ParentTestId )
    VALUES  ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null )
    SET @iter = @iter + 1
END
go

This creates a self referencing table and adds over 40,000 rows to it.

Action:

DELETE FROM dbo.Test WHERE TestId = 200

I then want to delete a row from this table.  If you turn on the actual query plan and run the above statement you can see that 20% of the cost is taken in a Clustered Index Scan for the self referencing key.

This is not a big deal in this scenario, but my real scenario has over 25 million rows in a large table.

So, I have two questions:

Why is it doing an Index Scan?  It has a Primary Key/Clustered Index value.  Why would it not do a Index Seek?
How can I make it do an Index Seek?  (It is taking about 1 min to delete a row.)

Edit:
I thought that this could be due SQL Server looking to see if other rows were referencing the one I was deleting.  But I set "Enforce Foreign Key Constraint" to "No" and it still took the same cost do a clustered index scan.

It needs to validate that the row you are trying to delete is not a parent of an existing row.

You don't have an index on ParentTestId.

So it must do the scan.

CREATE NONCLUSTERED INDEX ix ON  [dbo].[Test](ParentTestId)

Then you see a seek.

BTW: The 20% estimated cost of the scan is likely to be an underestimate in this case.

The FK validation is under a left semi join and SQL Server costs it as though only a partial scan will be needed and it will find a matching row and the delete will fail.

Presumably the rows you are actually deleting will succeed more often than not and so a full scan will be required in order to validate that there are no conflicting rows.

Using trace flag 4138 to turn off row goals

DELETE FROM dbo.Test
WHERE  TestId = 200
OPTION (querytraceon 4138 )

The re-costed plan shows the CI scan at 100% rather than 20% (as it now assumes a full scan will be needed)

This difference in estimated cost is sufficient for the missing index suggestion to show up.

The costs shown in this plan are still not very representative however. You might notice that they add up to 219%.

Also the overall plan cost of the queries with and without the trace flag are both identical at 0.0168268. The full CI scan ought, in fact, to be costed at 0.152373 (0.0485075 + 0.103866)

but it seems to be capped at no more than the original plan cost (and the overall plan cost doesn't get adjusted upwards either hence incorrect percentages)


Excessive memory grants from indexed foreign key cascade delete Each day, stores can enter sales information into our OLTP application.  The app calls a stored procedure in SQL to save this information.  Based on the users's activity, the application sends a code indicating whether the proc should perform an insert, update, or delete.

This save stored procedure is receiving memory grants of 60 GB for deletes to one row.  To duplicate the issue, I ran an ad-hoc delete query between a begin tran and rollback and captured the actual plan below:

https://www.brentozar.com/pastetheplan/?id=r189liBI4

The schema is like follows:

Daily_Item_Sales_Headers -- ~100 million rows on this system
========================
DlyItmSlsHdr_Key decimal(15,0) primary key nonclustered,
DlyItmSlsHdr_PaperworkBatch_Key decimal(15,0), -- FK to parent batch of data that contains other types of data
UK_DlyItmSlsHdr_PaperworkBatchKey_Key clustered, unique, unique key located on PRIMARY (DlyItmSlsHdr_PaperworkBatch_Key, DlyItmSlsHdr_Key)

Daily_Item_Sales -- ~790 million rows on this system
================
DlyItmSls_Key decimal(15,0) primary key nonclustered,
DlyItmSls_DlyItmSlsHdr_Key decimal(15,0), -- FK to header table, cascade delete,
UK_DlyItmSls_DlyItmSlsHdrKey_Key clustered unique constraint on (DlyItmSls_DlyItmSlsHdr_Key, DlyItmSls_Key)
[columns about sales data]

The query I ran is simple:

delete Daily_Item_Sales_Headers where DlyItmSlsHdr_Key = 1

The plan shows the header deletion is correctly cascading to the child sales rows.  The plan also shows an index seek on the clustered index of the child table.  However, this clustered index seek to the child table has an estimated 790 million rows.  The actual number of rows is ~100.  The high estimated rows is causing a memory grant of ~60 GB.

Using dbcc show_statistics on the child table indexes, I was able to see that the statistics were updated last night with a 2% sample size.  The histogram shows between 1 and ~33,000 rows estimated per parent key.  So the statistics appear to show the estimate should be much lower.

Why is this delete query generating such a large memory grant?

I saw this question about excessive sort memory grants that appear to be caused by a bug, but it looks different to me, because there are no sorts in this plan. Maybe it is the same bug applying to the table spools when cascading to the child table?

Excessive sort memory grant

Because of the foreign key cascading, I do not think I can work around the memory grant by deleting the child rows first before deleting the parent rows.  This is an OLTP system with up to 10,000 stores working at once, so I cannot drop the foreign keys on demand for a single delete.

EDIT 2/28/2019 1:13 PM CST

The SQL instance has about 400 GB memory allocated to it.

The application has the following trace flags enabled:

1222: deadlock tracing
4199: query processor fixes
2312: use 2014 cardinality estimator
2453: @Table variable cardinality

Disabling trace flags yields different estimates for the child table cascade delete:

Trace Flag 2312    Trace Flag 4199   Row Estimate
===============    ===============   =============
      on                 on            790 million rows
      on                 off           608 rows (very accurate)
      off                on            1 row
      off                off           1 row

Adding a querytraceon for the flag 9130 mentioned in the linked question makes no difference.

A coworker found this interesting article about a memory bug in SQL 2014.  The linked resolution was to add option (MAX_GRANT_PERCENT = 1) to the query.

https://www.theregister.co.uk/2016/02/09/microsoft_sql_server_2014_bug/

EDIT

The exact SQL Server version is SQL Server 2014 SP2 CU 12.

The database is compatibility level 110 - SQL Server 2012.  We are unable to change the compatibility level for a while.

I believe you're encountering the same bug in the new Cardinality Estimator that I did. I wrote about the problem here, with a more-detailed look here.

In short, the new Cardinality Estimator has a bug when estimating rows to be deleted on a cascading delete. If the driving value on the parent table is far enough outside its histogram, the CE will assume that every single row of the child table gets deleted.

Lucky you though, my server took the high estimates and decided to start scanning instead :(

Also luckily for you, you're dealing with a proc instead of ORM madness, so there are viable solutions. One would be to add DBCC TRACEOFF( 4199) to your procedure (potentially causing permissions issues). That doesn't future-proof you though, since in 2016+ 4199 status doesn't matter for the CE bug. Another option might be to add OPTION(OPTIMIZE FOR (@1 = <some middle value>)).

Since you're not getting a scanning plan, you could also just strongarm the memory grant with MAX_GRANT_PERCENT.


Postgres dumping specific non-table objects I find myself needing to migrate a specific application from one host to another. That application touches a subset of tables within a certain PostgreSQL database; in order to migrate properly, I need to dump all the data from some of these, as well as the schema only from others, while ignoring some other tables in the same database.

The obvious solution is to use pg_dump with the right combination of -t and --exclude-table-data. However, when I do this, certain functions and triggers on the tables listed do not get dumped. These are necessary for the application to work, so I need them in the output as well.

I have not been able to find a way to dump triggers and functions by name. Passing their names to -t does not produce any output. Meanwhile, doing a whole-database dump does indeed dump the triggers and functions; however, this includes the extraneous tables in the same database (used by other applications), which I don't want to bring along.

I could run a whole-database dump with -T options set to exclude all the tables I don't want. However, this would require me to update the dump script every time anyone adds a new table, which would be somewhat inconvenient. I would prefer to only change the script when I update the schemas for my application. I could also run the whole dump and post-process the output to include only the tables I want, but this would require me to basically parse SQL on my own, which I'd rather not.

Is there a way using pg_dump to include specific triggers and functions in the dump, by name? Alternately, can I tell it to include objects that are dependencies of the tables I dump, or similar?

I could run a whole-database dump with -T options set to exclude all the tables I don't want.

So what you want is to automatically exclude all tables but the ones you specify? You could always do this with dynamic sql..

SELECT format(
  'pg_dump %s',
  string_agg(  format('-T ''%I.%I''', schemaname, tablename), ' '  )
)
FROM pg_tables
WHERE schemaname = CURRENT_SCHEMA -- whatever schema.
  AND tablename NOT IN ('myDesiredTableName');

Run that with psql -c and then run the output from it to dump the database.


SSRS: Error Getting the Fields And Parameters for a shared Dataset based on a Stored Procedure I am using SSRS and adding a shared dataset based on a stored procedure with parameters. When I go to refresh the fields so Visual Studio adds the correct parameters and fields, sometimes I get the following error message:

  Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.

The detail message is

  Procedure or function 'SP NAME' expects parameter '@parameter name', which was not supplied.

The problem happens quite randomly. Sometimes it works, other times it doesn't. Doesn't matter what is inside the stored procedure. Without the fields and parameter list, you can not add the data set to a report.

Thanks!

This error is due to the way Visual Studio Data Tools/BIDS/SSRS Designer  to caches datasets. The workaround I found to get the fields and parameters refreshed correctly is as follows.

With a Shared Dataset Properties Window Open

Hit Refresh Fields
Close out the error msg.
Click on Query Designer
Click on the Exclamation Mark Button
A define Query Parameters window should pop up. Put in values for the parameters. Good defaults to use are ones that return little to no data. You are just looking for SSRS to refresh the data set metadata.
Hit Ok.
Wait a bit until the query is done.
Hit ok.
Check to see if data is now in the fields and parameters list. It should be!

Please note: The error will pop up even after you do have fields and parameters listed. However, you just need to make sure it is up to date. The error message popping up is fine and will continue to pop up. When ever you need to refresh the shared data set, just follow the steps above.


Database Schema for Blank Database is large. 20MB Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?

If you create a database without specifying a file size, the size is mapped from model. You can see what this is by running:

EXEC model.sys.sp_helpfile;
-- on my system, size of primary data file -> 3264 KB

Now, if I create a database just using CREATE DATABASE, I get an exact replica of those files.

CREATE DATABASE [floob1];
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 3264 KB

I can try to shrink it after the fact, and it will shrink some, but not entirely.

DBCC SHRINKFILE(floob1, 1);
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 2368 KB

So you can do that, but perhaps you should first find out what's in your model database and why it's 20 MB. Perhaps you can fix this problem by removing objects and/or shrinking that file. If shrinking the file doesn't reduce the size, then check for objects:

SELECT s.name, t.name, rows = SUM(p.rows)
  FROM model.sys.partitions AS p
  INNER JOIN model.sys.objects AS t
    ON p.[object_id] = t.[object_id]
  INNER JOIN model.sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
  WHERE p.index_id IN (0,1)
  ORDER BY rows DESC;

SELECT s.name, o.name, codesize = DATALENGTH(m.definition)
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
    ON o.[object_id] = m.[object_id]
  ORDER BY codesize DESC;

It's possible that objects were created there by mistake (in which case drop them), or have more rows than they should (in which case prune them and rebuild).

Note that if you try to override model and make a smaller database, you can't:

CREATE DATABASE [floob2]
ON (NAME = floob2_data,
  FILENAME = 'C:\...wherever...\floob2.mdf',
  SIZE = 1MB, FILEGROWTH = 2MB);

This results in an error:

  Msg 1803, Level 16, State 1, Line 18
  The CREATE DATABASE statement failed. The primary file must be at least 4 MB to accommodate a copy of the model database.

I assume you'll get a similar error if you try to create a database with an explicit data file < 20 MB, because it sounds like your model database is about 20 MB. So going back to my previous point, you're going to need to dig in and see why model is 20 MB, and fix that. (And note that I have no idea how you're measuring 20 MB - it could be the log file, for all I know, so you may need to look at that file instead.)

Of course, if space is a real issue and you put thousands of databases there under the assumption that they'll never grow, and then they all start growing, this is going to be a problem.