ORA-27100: shared memory realm already exists oracle I'm trying to start our db after performing a shutdown abort on a oracle 10g server, but I'm getting the following message:

  ORA-27100: shared memory realm already exists
  Linux Error: 17: File

Oracle 10g running under Centos 6.x

Anyone know any leads about solving that issue? I found this link but since I don't have a extensive background on oracle db administration I rather confirm if I should  follow these instructions.

EDIT In 2017 : This is an old question when I was on a company that I no longer work for. Sadly I never had the change to solve this neither have the current scenario to reproduce this error.

Phil is right - you need to see if you have any orphan Oracle processes and ipcs shared memory.

If you have more than one Oracle instance on your server, include the instance in your grep commands; more than likely, you may have some orphan Oracle processes still running.  Assuming your SID is custdev, you could do a

ps -fu oracle | grep custdev

and then do a kill -9 PID (where PID is the process ID) on any of the PIDs that show up.


MySQL transfer ISO-8859-1 to UTF-8 How to transfer data from Database with latin1 character set, to database UTF-8. And on transferring convert data from latin1 to UTF-8 e.g., û->ū, ë->ė, þ->ž, á->į and etc.

Found a solution. As I looked more into my first DB it had UTF-8 encoding, but all fields in tables was latin1. And data in there was windows-1257. So the solution:

SELECT CONVERT(CAST(pavad as BINARY) USING cp1257) pg_zodynas


How to Create Insert Statement from Table Data using sp_export_data without Id? How to Create Insert Statement from Table Data using sp_export_data without the Id?
I have the following statements and want to omit the Identity. How would I accomplish this?

This is how I'm scripting the one row:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName WHERE tblTableNameId = 123"

This is how I am script the whole table:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName"

Any & all feedback is appreciated.

If you are OK with using a slightly different approach (still T-SQL) go to this link and scroll down to the answer by Neeraj Prasad Sharma where he creates the stored procedure named INS.

Within that procedure you can add a where clause of something like table where id <> 1.  Here is some example code.

First create your table and test records.

CREATE TABLE testNegative
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,Col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testNegative
        ( Col1 )
VALUES  ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test5' ) ;
GO

This will insert ID's 1-5 into the table.  Now say that I want to script out every row other than ID 1.   I would then run this:

EXEC INS ' dbo.testNegative where id <> 1';

This procedure has worked well for me in a number of scenarios.  I think it will do what you are looking for here.


Single database won't load MySQL When I turned off my MySQL server, and turned it back on, it wouldn't read my database anymore. Just a single database, everything else in mySQL was fine.

When I try looking at any of the tables it shows in phpmyadmin, it says:

orderofthebutterfly.tablename does not exist.. (all in lowercase.. even though i've set
lower_case_table_names = 2 in the my.ini config.)

When I try to drop the db, i get this:

1010 - Error dropping database (can't rmdir '.\OrderOfTheButterfly', errno: 41)

I have verified that all the data is still there. There is a file in the MySQL directory with all the tables in it.

I have also deleted the folder and redid the database, and the same thing happened when I turned it on to work with the next day.

litterally just reinstall. that's the only way. just redo that database.


How to improve insert in RDS of AWS? I have a MySQL db.t2.small in AWS, the server isn't in my country, but I get 70ms. Now I need to insert over 7000 records in a table, but it takes a lot, over 6min. I tried many configurations:

-query_cache_size=16777216
-query_cache_type=1
-net_write_timeout=300
-max_allowed_packet=536870912
-innodb_lock_wait_timeout=3600
-innodb_flush_log_at_trx_commit=2 (Also I tried setting it to 0).

I tried sending the requests 1 by 1 and also using bulk, but the result is the same, almost 20 insert per sec.

What am I missing?

My Java bulk code:

try {
        conn = bd.getConectionAWS();
        PreparedStatement stmt = null;
        if (conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
        try {
            String query = "INSERT INTO CONTRATO(Codigo,Nombre,IdEstado,DesEstado,FechaInicio,IndActivo,Activo,Descripcion,Cliente,Responsable) VALUES(?,?,?,?,?,?,?,?,?,?) \n"
                    + " ON DUPLICATE KEY \n"
                    + " UPDATE Nombre=?, IdEstado=?, idEstado=? ,DesEstado=? ,FechaInicio=?, IndActivo=?,Descripcion=?,Cliente=?,Responsable=?";
            stmt = conn.prepareStatement(query);
            for (Contrato contrato : listaDatos) {
                stmt.setString(1, contrato.getCodigo());
                stmt.setString(2, contrato.getNombre());
                stmt.setInt(3, contrato.getIdEstado());
                stmt.setString(4, contrato.getDesEstado());
                stmt.setTimestamp(5, contrato.getFechaInicio());
                stmt.setString(6, contrato.getIndActivo());
                stmt.setString(7, contrato.getActivo());
                stmt.setString(8, contrato.getDescripcion());
                stmt.setString(9, contrato.getCliente());
                stmt.setString(10, contrato.getResponsable());
                stmt.setString(11, contrato.getNombre());
                stmt.setInt(12, contrato.getIdEstado());
                stmt.setString(13, contrato.getDesEstado());
                stmt.setTimestamp(14, contrato.getFechaInicio());
                stmt.setString(15, contrato.getIndActivo());
                stmt.setString(16, contrato.getActivo());
                stmt.setString(17, contrato.getDescripcion());
                stmt.setString(18, contrato.getCliente());
                stmt.setString(19, contrato.getResponsable());
                //          stmt.executeUpdate();
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
        }

I took a few things from https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

My "1 by 1" is the same, just replaced addBatch for executeUpdate();.

A SELECT 1; query takes 0.059sec.

There are two ways to efficiently (read: quickly) load 7000 rows.

LOAD DATA INFILE -- After you have built a 7000-line CSV file.
"Batch" INSERT -- like INSERT INTO t (a,b) VALUES (1,2),(5,6),(9,2), ...; -- Be cautious about the number of rows.  100 to 1000 is a good range of what to do at a time.

max_allowed_packet=536870912 -- NO, not in a tiny 2GB VM; change to 16M.  Other likely settings to check:

key_buffer_size = 10M
innodb_buffer_pool_size = 200M

I assume your tables are InnoDB??


Is there any way to identify which SP return data I have inherited a DB (SQL Server 2016) with almost 6000 SP and no naming conventions… I have to identify all SP that return data vs which are update or inserts or delete… a lot of the SP are complex, and can do update / insert and then return data…. Is there any way to identify which sp return data and which do not (IE are pure insert / delete / update)…. Besides visually eye balling each is there a quick method…

Thank you… In advance.

This works locally for me to identify result returning procs using sp_describe_first_result_set, which is only available on SQL Server 2012+. It's on the quick/dirty side of things, but hopefully it'll get you started,

Use [Yourmom]

CREATE TABLE #commands
     (
         ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
         CurrentObject sysname,
         Command NVARCHAR(2000)
     );

CREATE TABLE #success
     (
         object_name sysname NULL,
         is_hidden BIT NULL,
         column_ordinal INT NULL,
         name sysname NULL,
         is_nullable BIT NULL,
         system_type_id INT NULL,
         system_type_name sysname NULL,
         max_length SMALLINT NULL,
         precision TINYINT NULL,
         scale TINYINT NULL,
         collation_name sysname NULL,
         user_type_id INT NULL,
         user_type_database sysname NULL,
         user_type_schema sysname NULL,
         user_type_name sysname NULL,
         assembly_qualified_type_name NVARCHAR(4000) NULL,
         xml_collection_id INT NULL,
         xml_collection_database sysname NULL,
         xml_collection_schema sysname NULL,
         xml_collection_name sysname NULL,
         is_xml_document BIT NULL,
         is_case_sensitive BIT NULL,
         is_fixed_length_clr_type BIT NULL,
         source_server sysname NULL,
         source_database sysname NULL,
         source_schema sysname NULL,
         source_table sysname NULL,
         source_column sysname NULL,
         is_identity_column BIT NULL,
         is_part_of_unique_key BIT NULL,
         is_updateable BIT NULL,
         is_computed_column BIT NULL,
         is_sparse_column_set BIT NULL,
         ordinal_in_order_by_list SMALLINT NULL,
         order_by_is_descending SMALLINT NULL,
         order_by_list_length SMALLINT NULL,
         tds_type_id INT NULL,
         tds_length INT NULL,
         tds_collation_id INT NULL,
         tds_collation_sort_id INT NULL
     );

CREATE TABLE #fail (
                    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    CurrentObject sysname,
                    Command NVARCHAR(2000),
                    Error_Line INT,
                    Error_Message NVARCHAR(4000),
                    Error_Number INT,
                    Error_Procedure sysname,
                    Error_Severity INT,
                    Error_State INT
                    );

DECLARE @CurrentObject sysname;
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( CurrentObject, Command )
SELECT OBJECT_NAME(sm.object_id),
       N'EXEC sys.sp_describe_first_result_set @tsql = N''EXEC ' + OBJECT_NAME(sm.object_id) + N''';'
FROM   sys.sql_modules AS sm
WHERE  OBJECT_NAME(sm.object_id) IS NOT NULL
AND    sm.definition LIKE '%CREATE%PROC%';

DECLARE result_cursor CURSOR FOR
SELECT CurrentObject, Command FROM #commands

OPEN result_cursor
FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
WHILE @@FETCH_STATUS = 0
BEGIN

 BEGIN TRY

    PRINT @CurrentCommand;
    INSERT #success (
                             is_hidden,
                             column_ordinal,
                             name,
                             is_nullable,
                             system_type_id,
                             system_type_name,
                             max_length,
                             precision,
                             scale,
                             collation_name,
                             user_type_id,
                             user_type_database,
                             user_type_schema,
                             user_type_name,
                             assembly_qualified_type_name,
                             xml_collection_id,
                             xml_collection_database,
                             xml_collection_schema,
                             xml_collection_name,
                             is_xml_document,
                             is_case_sensitive,
                             is_fixed_length_clr_type,
                             source_server,
                             source_database,
                             source_schema,
                             source_table,
                             source_column,
                             is_identity_column,
                             is_part_of_unique_key,
                             is_updateable,
                             is_computed_column,
                             is_sparse_column_set,
                             ordinal_in_order_by_list,
                             order_by_is_descending,
                             order_by_list_length,
                             tds_type_id,
                             tds_length,
                             tds_collation_id,
                             tds_collation_sort_id
                         )
    EXEC(@CurrentCommand);

END TRY

BEGIN CATCH

        INSERT #fail (
                      CurrentObject,
                      Command,
                      Error_Line,
                      Error_Message,
                      Error_Number,
                      Error_Procedure,
                      Error_Severity,
                      Error_State
                     )

        SELECT @CurrentObject,
               @CurrentCommand,
               ERROR_LINE(),
               ERROR_MESSAGE(),
               ERROR_NUMBER(),
               ERROR_PROCEDURE(),
               ERROR_SEVERITY(),
               ERROR_STATE();

END CATCH;

    UPDATE #success
    SET object_name = @CurrentObject
    WHERE object_name IS NULL

FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
END

CLOSE result_cursor
DEALLOCATE result_cursor

SELECT *
FROM #commands AS c

SELECT *
FROM #success AS s

SELECT *
FROM #fail AS f

DROP TABLE #commands, #fail, #success


Oracle data dictionary, tell built-in roles apart from custom roles In Oracle, if you describe the DBA_ROLES view, you only get three columns:

SQL> describe dba_roles;
 Name                      Null     Type
------------------------------------------------
 ROLE                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                  VARCHAR2(8)
 AUTHENTICATION_TYPE                VARCHAR2(11)

There is no column that could tell me whether or not a given role is one of the built-in roles the RDBMS comes with, like SET_CATALOG_ROLE or a user-created role like LEMONADE_STAND_MANAGER.

How can I get a list of all user-created roles (excluding all built-in roles)?

A list of predefined roles in Oracle 12c can be found in the Security Guide. Also in Oracle 12c the view dba_roles has a column ORACLE_MAINTAINED. A 'Y' in this column tells you that this is a role created by the Oracle installation scripts. For Oracle versions before 12c this column does not exist. So for Oracle version lower than 12c you have to check the manuals or search the internet for lists of Oracle predefined roles.

Note

There is a dictionary table SYS.USER$ that contains all roles and users. This view is not visible to non dba users.

select user#,type#,name from user$ order by user#;

lists all  users and roles ordered by their internal Iduser#. If typeis 0 the item is a role. Most of the time these IDs will be created in a sequential manner, so that roles and user created during installation have a low Id. But I found 12c databases with a lot of Oracle created roles with a high Id. Also if you upgrade a database then there will be roles created by Oracle scripts that have a higher Id than user created roles. So the user# is not a reliable criterion.


Multiple instance of Mysql realtime backup My current mysql backup strategy is replication. I setup one or more slaves following a master. If the master failed, I still have a clean and up to date copy of the data from one of the slaves. It works without any problem. Now I started to have more master nodes to manage, each master node holds different data.

Now if I wanted to use the same backup strategy, I needed to setup at least one slave instance for each master. It's going to double the number of mysql nodes. Ideally I'm hoping to have only one server for all backups. Possible solutions I can think of are:

To run multiple instances of mysql slaves on different ports on the same machine;
Crontab + Rsync;

For possible solution #1, it should work, but it costs memory to running slave instances. For possible solution #2, I'm afraid when the data is being rsync'ed, the master failed, the backup might not contain a complete copy of the data. So is there any other better strategy to safely backup all master node to a single place?

"My current mysql backup strategy is replication."
 Replication cannot be a backup strategy but you can setup backups on slave! I guess you're talking about HA "if master fails then use slave"...

Anyways... MySQL 5.7 comes with Multi-source replication. You can have single slave machine replicating from two different masters.

You may write your own script to loop around CHANGE-MASTER-TO switching masters and replicate.

Idea for you:

while(true) {

change master to master1 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

change master to master2 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

}

Finally you can setup backup on this slave. (This is little complex than wht's written below)

You may also have individual slaves for both masters and setup backups on them. You can later push those backup to remote location / network drive/ tape / upload to s3 to retain longer.

master1--> slave1

master2--> slave2

(Consider backing up binary-logs if you want point in time restores.)

For backups you can use traditional mysqldump or mydumper/loader.
If your data size is large, it'd be better to go /w physical-backups, follow settingup xtrabackup for mysql with Holland framework.


Transactional Replication: Maintaining schema changes Whats the best approach to not to break replication but still apply schema changes to Primary and Secondary ? (not just DDL, new articles etc)

We have a huge database, so we had to go with replication using a back-up. The process is time consuming (2-3 hours). Each release we have tons of schema changes, as we are in active dev mode, we don't want to waste 2-3 hours to redo the process all over again and again.

Any suggestions or ideas ?

With transactional replication you must ensure that your schema changes are compatible or risk errors on the subscriber.  In practice this means reviewing the schema change scripts and testing their compatibility with your replication topology.  Adding articles requires a new snapshot, but you can create a second publication to hold the added tables if you want to reduce the snapshot size.

If you don't want to do that, then a log-based replica is probably a better design.  See, eg Read Scale Availability Groups


How to make Indexed Views work for SQLCLR? In SQL Server, the indexed view is a hellscape of limitations.  But I need one.  I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search on substrings of that prettified version.  So I need a full-text index on a persisted calculated column or a materialized view.

However, the formatter depends on data stored in multiple tables.

So this works fine for a view, but not for a persisted computed column because those can't query from multiple tables.

My SQLCLR method is precise and deterministic, so it should be eligible for use in indexed views, but indexed views can't have SQLCLR in their index keys.

I could re-implement my formatter using the T-SQL FORMAT function... but FORMAT is also incompatible with indexed views.

Can indexed views do anything?  Ever?

I'm open to the "roll your own materialized view with triggers" approach if anybody can refer me to a good document on best practices for doing that.  Last time I tried, it spiraled out of control and there was tremendous duplication between the code for insert and update and the initialization.

Is there some way I'm missing to get performant text searches on calculated data on a few million rows, without using a materialized/indexed view or a persisted calculated column?

The formatter does not perform data access. However, the data I need to feed into the formatter for it to be useful will be coming from multiple tables (with a nice regular join), hence I can't solve this problem with persisted computed columns. I can't key the SQLCLR column, so I can't use it in a fulltext index.

I thought this would be simple. Persisted columns and indexed views are designed to perform calculations on write, and to properly implement the observer pattern so that changes to their dependencies are reflected on the calculated value.

Can indexed views do anything? Ever?

It actually is kind of miserable when you think of an indexed view as a solution, only to find your use case hits on one of the many limitations of indexed views (I'm looking at you, LEFT JOIN).

You're right that you can't include the SQLCLR in the index keys of the indexed view.  However, you can include it in the SELECT list of the view definition, which will persist the value to disk.  So you can at least avoid the cost of calculating the value on the fly when reading from the table.

In the AdventureWorks2014 sample database, I created this view on the delightfully named Person.Person table:

CREATE OR ALTER VIEW dbo.PersonWithHashForSomeReason
WITH SCHEMABINDING
AS
SELECT
    BusinessEntityID,
    PersonType,
    dbo.SpookyHash(CONVERT(binary(50), FirstName)) AS FirstNameHash
FROM Person.Person
GO

Note: I was too lazy to write my own CLR function, so this one is from this Q&A.

I can make that an indexed view by clustering it on BusinessEntityID:

CREATE UNIQUE CLUSTERED INDEX CX_BusinessEntityID
ON dbo.PersonWithHashForSomeReason (BusinessEntityID);
GO

Looking for a specific set of rows in the table produces an index scan (execution plan link).  Notice the lack of a Compute Scalar operator, which would normally be used to produce the hash value.  Since the hash is persisted to disk in the indexed view, that's not necessary:

SELECT BusinessEntityID, FirstNameHash
FROM dbo.PersonWithHashForSomeReason WITH (NOEXPAND)
WHERE FirstNameHash = 0x910C426C533F2C0AAF350158331E3B01;

I had to use a NOEXPAND hint to get it to use the view.

You'll notice the warnings in the plan are due to the fact that the whole table was scanned in order to find those values, since there is no index keyed on FirstNameHash.

Unfortunately, trying to create a nonclustered index on this persisted value fails:

CREATE NONCLUSTERED INDEX IX_FirstNameHash
ON dbo.PersonWithHashForSomeReason (FirstNameHash);

  Msg 1976, Level 16, State 1, Line 48
  Cannot create index or statistics 'IX_FirstNameHash' on view 'dbo.PersonWithHashForSomeReason' because cannot verify key column 'FirstNameHash' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.

This is because SQL Server doesn't trust us:

  Why don’t we trust the user? What can really go wrong here??

  Assume if the user incorrectly sets the custom attribute
  IsDeterminsitic to true for a non-deterministic function and assume he
  is able to create an index on a computed column that invoked this
  function without persisting it. [...] This might lead to index
  corruption as the function might return different values for the same
  input as the function is non-deterministic.

  So for the sake of user’s safety, Sql Server in this release REQUIRES
  the user to persist the computed columns (unlike the tsql case) to
  actually index the computed columns.

I imagine the same limitation exists for fulltext indexes, although I haven't tried it.

The takeaway from all this, as you mentioned in your own post, is that you're kind of stuck here.  Really your only option is to:

accept the table scan (not ideal, and probably not even practical based on your description), or
use triggers to "manually" keep this value up to date in a normal column


How can I write results to SAME results set? I have automatically created a script with lots (15K) of statements.

How can I get the results to all be in the same results set, temp table or file?

I want to be able to look at the results easily:

select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_1] order by [utcDT] desc;
...
select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_2] order by [utcDT] desc;

If you are going to keep all of these queries and run them, there are some options.

1

adding union all between all executions, you could use find and replace to add this to each select statement.

Example:

select name from sys.databases where name = 'master'
UNION ALL
select name from sys.databases where name = 'tempdb'

But when using order by you would have to change more parts of your query, an example:

 SET NOCOUNT ON;
 SELECT NAME FROM
 (SELECT TOP(1) name from sys.databases where name = 'master' order by name ) as [sysdatabases]
  UNION ALL
SELECT NAME FROM
 (SELECT TOP(1) name from sys.columns  order by name )  as [syscolumns];

You cannot do this

SET NOCOUNT ON;
SELECT TOP(1) name from sys.databases where name = 'master'
UNION ALL
SELECT TOP(1) name from sys.columns
ORDER BY NAME

Since it would sort on the entire result set.

Find and replace all in ssms

2

You could run result to file or text

Be sure to go to Tools - Options - Query Results - SQL Server - Results to Text
and disable include column headers int he result set.

This is done so you don't get the same column header on each of these queries. You will have to add the headers afterwards.

Open a new query window to implement the changes, and select result to file.

Run the query with NOCOUNT

 SET NOCOUNT ON;

 SELECT name from sys.databases where name = 'master'

 SELECT name from sys.databases where name = 'tempdb'

Change the file type to csv if you'd like:

The result:

3

Insert all the values in a temp table & read from the temp table

 SET NOCOUNT ON;
 CREATE TABLE #temp(val varchar(255));

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'master';

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'tempdb';

 SELECT * FROM #temp;
 DROP TABLE #temp;

These are not the only options you have, and does not account for all the time it will take to execute all of these one by one.

4

Using Powershell

1) Save your query as a .sql file to your desired location

2) Run the next two statements with powershell while changing the parameters

import-module "sqlps" -DisableNameChecking

invoke-sqlcmd -Serverinstance instancename -inputFile "C:\temp\bla.sql" | out-File -filepath "C:\temp\TestOutput.txt"

3) validate the output

Output For this query

 SET NOCOUNT ON;
 SELECT name from sys.databases where name = 'master'
 SELECT TOP(1) name from sys.columns


What can cause an Index "total writer" to be the same in every index? I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)).

I use this query to analyze unused Indexes:

--Possible bad Indexes (writes > reads)
SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;

But what I see, is that 14 Indexes ( yes i'm studing to disable them ) have the same "total writes". If I hold F5, i can se that they're used in "writes" at the same time.

Total reads are different.

What is the reason of this? why every index is updated ( write ) at the same time and same ammount, But with different reads?

Didn't found anything like this on web or stack.

Thanks

Unless an index is filtered, they should all be written to the same number of times under normal DML activity.

If a row is updated or added, any columns in any index must also be added/updated.

So you should not expect some indexes to be written to a different number of times, unless you are reorganizing or rebuilding individual indexes specifically.

Also, please read this post over on SQLPerformance.com:

Don't just blindly create those "missing" indexes!


Updating a PostgreSQL Table With Different Random Dates I have two tables in PostgreSQL, Employee and Leave like following:

CREATE TEMP TABLE employee_table AS SELECT
  id::int,
  name::text
FROM ( VALUES
  (1, 'John' ),
  (2, 'David')
) AS t(id, name);

CREATE TEMP TABLE leave_table AS SELECT
  id::int,
  leave_date::date,
  emp_id::int
FROM ( VALUES
  (1, '01/10/1993' ,1),
  (2, Null         ,1),
  (3, Null         ,1),
  (4, '02/12/1990' ,2),
  (5, Null         ,2),
  (6, Null         ,2)
) AS t(ID,Leave_Date,Emp_ID);

I want to update the Leave table and set the Leave_Date column to a random date maybe 01/01/2000 for each employee but if an employee has more than one Null entry in Leave table, I want to update his null entries with two different dates which means one employee should not have two same Leave_Date value in Leave table and after update my leave table should look like following:

ID        Leave_Date    Emp_ID
1         01/10/1993      1
2         01/01/2000      1
3         01/01/2001      1

As shown above, initially john had two null entries in Leave table and the result shows that those entries are update with two different dates. Is there any way to this?

First you never gave a test case for when there is only one date and it is null, so we create that.

INSERT INTO employee_table (id, name) VALUES (3, 'Evan Carroll');
INSERT INTO leave_table VALUES ( 10, null, 3 );

Then we run a command to check whether or not an emp_id has more than one entry in leave_table. The results are in that derived table. We update accordingly. Here we generate a date that represents the year start between 1900-2020. Just update this for what you mean by "random date" you didn't define it in your question.

UPDATE leave_table
SET leave_date = CASE
  WHEN t.count = 1 OR t.count IS NULL
  THEN '01/01/2000'::date
  ELSE '1/1/1900'::date + ('1 year'::interval*floor(random()*120))
END
FROM (
  SELECT emp_id, count(*) FROM leave_table
  WHERE leave_date IS NULL
  GROUP BY emp_id
) AS t
WHERE leave_date IS NULL
AND t.emp_id = leave_table.emp_id;

Then we have it

TABLE leave_table;
 id | leave_date | emp_id
----+------------+--------
  1 | 1993-01-10 |      1
  4 | 1990-02-12 |      2
  2 | 1964-01-01 |      1
  3 | 1929-01-01 |      1
  5 | 1933-01-01 |      2
  6 | 1902-01-01 |      2
 10 | 2000-01-01 |      3

Now, as @McNets pointed out yesterday, I am kind of cheating. Instead, try this (much more complex query) which suffices the question's update his [emp_id] null entries with two different dates

WITH t AS (
  SELECT
    id,
    emp_id,
    leave_date,
    count(*) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table
  WHERE leave_table.leave_date IS NULL
)
UPDATE leave_table
SET leave_date = CASE
  WHEN t.max_nulls = 1 OR t.max_nulls IS NULL
  THEN '01/01/2000'::date
  ELSE date_series_emp.ds
END
FROM t
INNER JOIN (
  SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)
) AS date_series_emp
  USING ( emp_id, row_number )
WHERE t.id = leave_table.id;

Breaking it apart, the CTE does this

  SELECT
    id,
    emp_id,
    leave_date,
    count(*) FILTER (WHERE leave_date IS NULL) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table

That generates how many nulls are in the set, and row numbers from within the set that we can join on for a 1:1 with the update query,

 id │ emp_id │ leave_date │ max_nulls │ row_number
────┼────────┼────────────┼───────────┼────────────
  2 │      1 │            │         2 │          1
  3 │      1 │            │         2 │          2
  5 │      2 │            │         2 │          1
  6 │      2 │            │         2 │          2
 10 │      3 │            │         1 │          1

The only other tricky part is the inner-join select,

  SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)

There we're taking the distinct emp_ids, and joining them on a sequence of dates that you're calling random. We count(*) over that sequence to give it a corresponding random number from within the cardinal sequences generated.

Then we join this to the table and perform the update..

This method does have a one drawback, if the input size ever exhausts your pool of "random dates" (only 1081 of them), the update on rows past that max won't be performed at all.


Elegant way of sending e-mails with Service Broker Is there an elegant way for sending e-mails using a Service Broker service/queue?

I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on the queue.

According to the book SQL Server 2005 DBA Street Smarts: A Real World Guide to SQL Server 2005 Certification Skills, Database Mail uses the Service Broker:

  Additionaly, Database Mail is scalable because it uses the Service Broker queue, which allows the requests to be handled asynchronously and even saves the request if the server goes down before it can be handled.

Maybe I'm too off base here, but, is there a way I can send messages directly to the Database Mail queue using Service Broker?

If I have to set up a stored procedure for sending an e-mail on behalf of Service Broker, I think it should be easier to just setup a job on the SQL Server Agent to check for the events I'm interested in inside the error log and send the operators an e-mail.

I'm open to suggestions.

is there a way I can send messages directly to the Database Mail queue using Service Broker

When you invoke sp_send_dbmail that is exactly what is happening.

  I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to
  send e-mails to operators for each message on the queue

Then do just that. Create an activated stored procedure on the queue of your service that receives the notifications and have it RECEIVE the message and invokes sp_send_dbmail.

I think you are trying to 'cut out the middleman' and have the EVENT NOTIFICATION send the notification directly to the DB Mail queue. That is not possible because the code that handles the messages arriving in the DB Mail queue expects the messages to have a certain format (ie. the one created by sp_send_dbmail). The messages sent by Event Notifications would have a completely different format and the DB Mail processor would choke as it does not understand them. In order to prevent exactly such situation Service Broker services are bound to specific contracts and DB Mail service does not accept the http://schemas.microsoft.com/SQL/Notifications/PostEventNotification contract used by Event Notifications.


What are the arguments in favor of using ELT process over ETL? I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process.
What are the differences in the two approaches and in which situations would one be "better" than the other? It would be great if you could provide some examples.

lots of Discussions about ETL vs ELT out there.

The main difference between ETL vs ELT is where the Processing happens
ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory)
ELT processing of data happens in the database engine

Data is same and end results of data can be achieved in both methods.

it very much depends on you and your environment
If you have a strong Database engine and good hardware and you can do heavy processing on it, ELT is good for you,
If you have a busy datawarehouse engine and you need to free it from processing go for ETL.

notice that having an ETL tool gives you both the options, like ETL(T), you can do the Transformation in the ETL tool and you can do transformation in the Database engine as well

but ELT you only have the option of transformation in the database engine, but you should know that Databases are better at set based operations than record-at-a-time ETL tools.

similar question asked on SO but supporting ETL and also
a nice Article comparing ETL vs ELT, but favoring ELT


PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values I am working on a financial software where we have huge table of ticks with more than 800M rows.

A single row: symbol_id, broker_id, date, time, bid, ask

The problem is the table has many ticks with identical first four columns and different bid and ask.

What is the best way to update the table and combine such rows into one by taking an average of bid and ask columns?

Example:

|1|2|2012-01-01|12:00:00|1.3456|1.3678|

|1|2|2012-01-01|12:00:00|1.3463|1.3612|

Result row:

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

SELECT col1, col2, col3, col4, AVG(col5) col5, AVG(col6) col6
FROM table_name
GROUP BY col1, col2, col3, col4;


When trying to configure data guard broker I'm getting Error: ORA - 12154 : TNS: could not resolve the connect identifier specified I'm trying to setup data guard broker in my production instance. I have duplicated the primary database to standby database successfully. I've added primary database to data broker configuration and when I'm trying to add standby database following error is getting

DGMGRL> ADD DATABASE 'orcl_standby' AS CONNECT IDENTIFIER IS orcl_standby maintained as physical;
ERROR: ORA - 12154 : TNS: could not resolve the connect identifier specified

Tnsping from primary db to standby db and vice-versa is successful. I'm able to connect primary db from standby db using sqlplus sys/password@orcl as sysdba but when trying to connect to standby db from primary db, error is getting like

  ORA - 12154 : TNS: could not resolve the connect identifier specified.

Tried out all possible solutions.

Below is my tnsnames.ora file

    ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.42)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (SID = orcl)
      (UR = A)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL_STANDBY=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.44)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_standby)
      (SID = orcl_standby)
      (UR = A)
    )
  )

Any help will be appreciated

The part after IDENTIFIER IS must be a connection to the database. You should replace the orcl_standby with something like '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(HOST=<server of the Standby>)(PORT=1521)))(CONNECT_DATA=(SID=<the SID of the Standby>)(SERVER=DEDICATED)))'.


Preferred way to store DateTime We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information?

Storing Date and Time in 2 separate columns or one column using DateTime?

Can you explain why that approach is better?

(Link to MySQL docs for reference, the question is general, not specific to MySQL)
Date and Time types: Date and Time

Storing the data in a single column is the preferred way, since they are inextricably linked.  A point in time is a single piece of information, not two.

A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time" is the fractional value.  So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861.  42631 is the number of days since 1900-01-01.  .39861 is the portion of time elapsed since midnight.  Don't use a decimal type directly to do this, use an explicit date/time type; my point here is just an illustration.

Storing the data in two separate columns means you'll need to combine both column values any time you want to see if a given point in time is earlier or later than the stored value.

If you store the values separately, you'll invariably run into "bugs" that are difficult to detect.  Take for instance the following:

IF OBJECT_ID('tempdb..#DT') IS NOT NULL
DROP TABLE #DT;
CREATE TABLE #DT
(
    dt_value DATETIME NOT NULL
    , d_value DATE NOT NULL
    , t_value TIME(0) NOT NULL
);

DECLARE @d DATETIME = '2016-09-20 09:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

SET @d = '2016-09-20 11:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.dt_value >= '2016-07-01 11:00:00';

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.d_value >= CONVERT(DATE, '2016-07-01')
    AND dt.t_value >= CONVERT(TIME(0), '11:00:00');

In the above code, we're creating a test table, populating it with two values, then performing a simple query against that data.  The first SELECT returns both rows, however the second SELECT only returns a single row, which may not be the desired outcome:

The correct way to filter a date/time range where the values are in discrete columns, as pointed out by @ypercube in comments, is:

WHERE dt.d_value > CONVERT(DATE, '2016-07-01') /* note there is no time component here */
    OR (
        dt.d_value = CONVERT(DATE, '2016-07-01')
        AND dt.t_value >= CONVERT(TIME(0), '11:00:00')
    )

If you need the time component separated for analysis purposes, you could consider adding a calculated, persisted, column for the time portion of the value:

ALTER TABLE #DT
ADD dt_value_time AS CONVERT(TIME(0), dt_value) PERSISTED;

SELECT *
FROM #dt;

The persisted column could then be indexed allowing for fast sorts, etc, by time-of-day.

If you are considering splitting the date and time into two fields for display purposes, you should realize that formatting should be done at the client, not the server.


Determining sizes for Separated Partitions of programs, data and logs in SQL Server As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.

Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?

Thanks

Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?

The programs itself won't take much space. You need to make sure the default locations are updated to reflect the data, log and backup locations.

Coming to the size of data & log, you need to do some estimates on the size of clustered index + non-clustered indexes and use the potential estimates for data growth over the next n time period. Estimates may NOT match with the actuals but that is the right way to do it for the data file size. The below links will help.

http://msdn.microsoft.com/en-us/library/ms178085.aspx

http://msdn.microsoft.com/en-us/library/ms190620.aspx

For the size of the transaction log, there are some defaults used by few consultants that log should be this many times of the data file but that is NOT a good way to do it. You will either make the log smaller or too big for your needs if you follow that route. The right way is estimate the largest transaction in your database and keep the log with some head room for that and watch for the transaction log size autogrow event. If the log grew after sometime then you need to make the log bigger than that size and watch it for some more time. It takes few iterations but that is the right way to do it. What is the largest transaction? That depends and most likely it may be the index rebuild of your largest table. Hope this is a good starting point for you, let me know if you have more questions/comments on this.


Moving MSDB database When I am trying to move msdb database from it's default location (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:). After moving the database I am getting error as shown below.(the error I am getting while expanding database node in SSMS and now I am not able to access any database.)

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Database 'msdb' cannot be opened due to inaccessible files or
  insufficient memory or disk space.  See the SQL Server errorlog for
  details. (Microsoft SQL Server, Error: 945)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

The procedure I followed to shift msdb database is as follows:

For each file to be moved, run the following statement.

ALTER DATABASE msdb
 MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSDBData.mdf' )

ALTER DATABASE msdb
 MODIFY FILE ( NAME = MSDBLog , FILENAME = 'E:\MSDBLog.ldf' )

Stop the instance of SQL Server to perform maintenance.
Move the file or files to the new location.
Restart the instance of SQL Server or the server.
Also i did confirm the path by running following query

SELECT name, physical_name AS CurrentLocation, state_desc
     FROM sys.master_files
     WHERE database_id = DB_ID(N'msdb');

And output was showing current location of both MSDBdata and MSDBLog as E:\

This error happened while I did it for testing server. I want to do it for a production server. So please help me on this error. How exactly to move msdb database?

In the comments you indicate that your SQL Service is running as the Network Service account.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms684272(v=vs.85).aspx

  The NetworkService account is a predefined local account used by the
  service control manager. This account is not recognized by the
  security subsystem, so you cannot specify its name in a call to the
  LookupAccountName function. It has minimum privileges on the local
  computer and acts as the computer on the network.

This is the default account for installations under Vista and Windows Server 2008. During installation this account was granted Full Control file system permissions to the SQL Server data directory as seen in the File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups section of the following article.

http://msdn.microsoft.com/en-us/library/ms143504.aspx#Reviewing_ACLs

Since you have relocated some of your database file the SQL Server service account needs permissions to the new locations. In the comments @Marian provided instructions for granting Network Service the required permissions.

  Right click the drive -> Properties -> Security -> Edit -> add the
  Network Service user -> give him full control. By default he hasn't.
  Or better, just create a specific folder and give him Full Control
  permissions there. And move the data files in that folder.

The advice to create a new folder and move the files there is excellent as well.

Any additional services that are running as the Network Service account will aslo have permissions to this folder. Ideally the SQL Services should each have their own separate account.

http://support.microsoft.com/kb/2160720

  When choosing service accounts, consider the principle of least
  privilege. The service account should have exactly the privileges that
  it needs to do its job and no more privileges. You also need to
  consider account isolation; the service accounts should not only be
  different from one another, they should not be used by any other
  service on the same server. Do not grant additional permissions to the
  SQL Server service account or the service groups. Permissions will be
  granted through group membership or granted directly to a service SID,
  where a service SID is supported. For more details please refer to
  Books Online Topic Setting Up Windows Service Accounts


How to see if index is being read "ordered" from DMVs I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can see if the read is "ordered". Is this information available other places or is the only solution to search the plan cache?

That data in the query plan only indicates whether the query plan requires on an ordered scan, or whether an allocation-order scan would work too.  Even if the query plan does not require an ordered scan, you normally get one anyway, as allocation-order scans are only allowed under specific circumstances, as Paul White explains all here.
So since this data is about the query plan, not the scan method, it's only available in the query plan.


PostgreSQL seems to ignore RAISE EXCEPTION in a CTE Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows:

CREATE FUNCTION public.assert (
    in_assertion boolean,
    in_errormessage text
)
RETURNS boolean
IMMUTABLE
LANGUAGE plpgsql
SECURITY INVOKER
AS $function$
    BEGIN
        IF NOT in_assertion THEN
            RAISE EXCEPTION 'assertion failed: %', in_errormessage;
        END IF;

        RETURN in_assertion;
    END;
$function$
;

Upon testing I found that the exception is not thrown as I would expect. For example, with CREATE TABLE emptytable (somecolumn text); and

CREATE FUNCTION public.testassert_buggy (
    out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
    WITH firstquery AS (
            SELECT * FROM emptytable
    ), nonemptycheck AS (
            SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
    ) SELECT * FROM firstquery;
$function$
;

I would expect a call like SELECT testassert_buggy(); to throw the exception, but instead the result is

 somevalue
-----------

(1 row)

(Note that firstquery actually returns 0 rows; the 1 row is due to this being a function with out parameters.)

With the following small change in the second-to-last line, the exception IS thrown.

CREATE FUNCTION public.testassert (
    out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
    WITH firstquery AS (
            SELECT * FROM emptytable
    ), nonemptycheck AS (
            SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
    ) SELECT firstquery.* FROM nonemptycheck, firstquery;
$function$
;

If I rewrite the last query switching the table list (i.e. with FROM firstquery, nonemptycheck) there is again no exception. I'm puzzled. Is the query optimized in some way that ignores side-effects like exceptions? I tried to remove IMMUTABLE from the definition of assert, but that didn't make a difference.

Unreferenced CTEs are not executed at all (except data-modifying CTEs!)
Related thread on pgsql-bugs with Tom Lane explaining the behavior.

In your first example you have:

SELECT * FROM firstquery;

No reference to the CTE nonemptycheck. So the CTE is never executed.

In the second example you have:

SELECT firstquery.* FROM nonemptycheck, firstquery;

nonemptycheck is referenced, so it is executed, resulting in the exception.

No row

Your added test case in the comment fails for a similar reason. Since the first CTE returns no row, the outer SELECT returns no row. There is no need to execute the second CTE, since the result will not be displayed. The optimizer's job is to avoid fruitless work ..

Appending nonemptycheck as cross-joined subquery (CROSS JOIN or appended after comma) instead of a second CTE does not help, either. A similar optimization avoids execution: Since firstquery returns no row, there is no point in evaluating the nonemptycheck, even in a subquery:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  FALSE
   )
SELECT f.*     -- even if you append ", n.*" to SELECT list
FROM   firstquery f
    , (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) n         -- not executed

Solution

You can force evaluation with a FULL OUTER JOIN:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  false
   )
SELECT f.*
FROM   firstquery f
FULL   JOIN (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) nonemptycheck ON TRUE;  -- always executed

Side effect: This would return a single row filled with NULL values, when firstquery returns no row. Not in this particular case, though, since your assert raises an exception in this case.


SQL Server tempdb on SSD showing IO We recently separated out our tempdb files to a new SSD and have started seeing:

  5348 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\tempdb\tempdb4.ndf] .

We have multiple occurrences of this error. We did not see the errors when tempdb was back on its original RAID 5 home. I followed a tutorial on SQLIO and I think the SSD should be much faster, when doing 8kb random read/writes, than the previous RAID 5 disks.  So why are we seeing these errors?

Also, by way of more proof that not all is well, the batch file we run overnight (which is when these errors occur) is taking 7 hours.  It took 6.25 hours on the old disks.

The disks sit in a directly attached array. The RAID5 for data, RAID 10 for logs and a spare slot that we used for the SSD. The RAID 5 and SSD are formatted for 64kb block size. The log is incorrectly set to 4KB block size (I know – will fix when I get a chance).

These are the results of SQLIO:

T drive (ssd)
Ios= 8KB random writing,IOs/sec=31847.48,MBs/sec=248.8
Ios= 8KB random reading,IOs/sec=76391.66,MBs/sec=596.8

S drive (RAID 5)
Ios= 8KB random writing,IOs/sec=2601.3,MBs/sec=20.32
Ios= 8KB random reading,IOs/sec=3138.45,MBs/sec=24.51

For 64K sequential read/writes they were around the same.

Tempdb is split into 4 1.5Gb files (this is the same before and after the move).

SQL server 2012 is patched to SP3.

Do you have any idea what could be causing all these I/O errors getting reported by SQL Server?

Is it possibly an Array or HBA driver issue? Does a single disk added into a spare slot on an directly attached array needs careful configuration in terms of cache?

I would highly recommend that you test your new T:\ drive using Crystal Disk Mark.  Check out the guide from Brent Ozar here:

How to Test Your Storage with CrystalDiskMark

Compare the results from the T:\ drive with

the old RAID 5 disk (where tempdb used to be)
your machine

If the SSD is slower than those other two devices, and nothing else has changed* in your setup, it's likely there is a problem with the disk itself, or with the driver being used, or the controller for the array this disk sits in, etc.

*things that might have changed since you moved tempdb:

the number of tempdb files for the database increased or decreased (someone said "hey, why not, since we have to restart the database to move tempdb anyway")
maintenance tasks were rescheduled to coincide with the now-slow nightly job (especially those that have potential to hit tempdb hard, like index rebuilds or checkdb)
the maintenance window to move tempdb was also used to deploy new code (for the nightly job, perhaps) that makes heavier use of temp tables, or has queries with bad spills, etc

Next Steps

Since it seems like the disk is reasonably fast (per the benchmarks you shared), I think it would be a good idea to log the contents of sys.dm_io_virtual_file_stats before and after the nightly batch job you mentioned.  This will tell you how much I/O is happening on tempdb during that process.  This is important, because maybe there really is more I/O than the disk can handle.  So here's what you do:

Run this query right before your nightly batch job is scheduled to run:

select *
from sys.dm_io_virtual_file_stats((select DB_ID('tempdb')), default);

Save the results somewhere (like Excel or something - probably not in tempdb :P)
Wait 7 hours (until the job finishes)
Run the same query and save the results
Edit your question to include the results

We can then take the difference of the two snapshots and determine how many bytes were read / written during the job.  You can also use those numbers to calculate overall latency during that period.

Note: a more granular approach would be to log the results of that query to a table every 5 minutes (or less if you want)


Are there any known benchmarks/statistics on UnQLite? I came across UnQLite the other day in Stack Overflow and it has me intrigued for some software I'm working on. However, very few tests have been published on how well it performs. There are plenty of questions and
public data available that help on SQLite and others but nothing on UnQLite. (I'm looking at serverless databases, hence my mention of SQLite. I understand that SQLite is relational whereas UnQLite is KVP and the two are not forked from the same parent projects)

I'd like to know:

What UnQLite seems to achieve in read/write speeds
What types of reads and writes (sequential, synchronous, large data, batch, random, etc.) it does well at compared to some other databases
Where bottlenecking seems to occur
How large the database tends to get (disk size or rows) before the time it takes to find and extract data begins to see a significant increase (i.e. slows down)
If at all possible, what disk format(s) (ext3/4, NTFS, exFAT, HFS+) cause problems for the database
Any recommended ways of optimizing performance with it

Thank you.

I have found some information and pointers here. Extracts from that thread:

  ...retrieved records from a given collection a parsed only once (FastJSON) and cached in-memory for fast retrieval. The cache is a O(1) hashtable so performance shouldn't be a problem.

  ...only the first retrieval is a little bit slower than the others since it involve a disk access (In case you are working with an on-disk DB) and a preprocessing phase (FastJSON decoding) while later fetches are in-memory operations only with no decoding overhead.

  My Observations about document storage in UnQlite :

  In memory: Store faster, search slower ;But both slower than SQLite!
  In File: Store slower, search faster

  e.g:- For same data, In UnQLite:

  In mem: store: 901.9 ms, search: 2586 ms
  In Local disk: store: 15223.3 ms , search: 114.2 ms

  SQLite:
  In mem: Store 9507 ms , Search : 105 ms

  Honestly, I haven't benchmarked SQLite indexes with UnQLite doc store. But, keep in mind that UnQLite is shipped with a disk KV storage engine that support nearly O(1) record retrieval.

The information given lacks specifics and context - nothing how the tests were conducted. Yet I believe it's enough to construct myself a vague opinion.

To me it seem that UnQLite has faster write but slower read performance, in relation to SQLite. It is probably a general trend when comparing SQL with NoSQL database systems.


How to force Windows to support not supported LC_COLLATE and LC_TYPE in PostgreSQL? I have fresh installation of PostgreSQL 9.1.x. Unfortunately, I discovered that OS doesn't support LC_COLLATE = 'pl_PL.utf8' and LC_CTYPE = 'pl_PL.utf8'. I read in PostgreSQL documentation that:

  On all platforms, the collations named default, C, and POSIX are
  available. Additional collations may be available depending on
  operating system support.

Is a way, however to force Windows to support that locale ? I mean a patch, plugin or something.

On Windows, it's likely that the needed locale is already installed, it's just named differently than on Unix.

As an example, the following database creation appears to works fine for me with PG9.1 on my  Windows XP, with its default locale French_France.1252 and no additional language pack installed that I'd recall.

CREATE DATABASE pldb
       ENCODING = 'UTF8'
       LC_COLLATE = 'Polish'
       LC_CTYPE = 'Polish'
       TEMPLATE=template0;

Also, a quick test of the sort order by submitting this query in UTF8 in this newly created database:

select * from (values ('z'), ('ź'), ('ż'), ('s'), ('ś'), ('x'), ('y')) as letters order by 1;

seems to output correct results:

  s
  ś
  x
  y
  z
  ź
  ż


How to update ID=null values in table to incremental counter values? On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows:

ID   NaturalID               Comment

1    franknfurther03071972   blahblah
2    chrisrock12081980       nonsense
null clairecampbell24121990  merry christmas
3    walterhermes22032001    young guy
4    tanjaolsen16051996      nice
null timharris20041999       came late

The rows with "null" IDs are new, the numbered IDs are those already existing in the main, target table. The NaturalID can uniquely identify an entry (in fact, it's multiple columns). I want to set the "null" IDs to incremental values, following the current max ID, here: 5 and 6, increasing when more null IDs are found.

Currently, I use a cursor to iterate over the rows with ID null and update each ID with a value, but sincce it's really a very big table, it would take days.

I tried to do an update with row_number(), but it gives me an error "Windowed functions can only appear in the SELECT or ORDER BY clauses.":

update StagingTable set ID=ROW_NUMBER() over (order by NaturalId)
from StagingTable where id is null  -- fails

How can I do it?

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) +
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.


Can I use SELECT statement for values in a list partition? Or do I need to explicitly state them? So I have a table I would like to partition, and in creating the partitions I am trying to run a statement like this:

partition by list(apple_type)

create table foo.apples_green
partition of foo.apples_master
for values in(select apple_type from foo.fruits where fruit_type = 'apples' and color ='green');

Unfortunately I keep getting an error: ERROR: syntax at or near "select".  I don't understand if it's just not something that can be done in postgres10, or if I am totally missing some type of syntax.  Any help would be great.

Thanks much!

When in doubt, read the fine manual, which says about partition bounds:

  and partition_bound_spec is:

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

Nowhere does it say "subselect"; only literal values and special keywords that substitute literal values are allowed.


.Net Application Using Inefficient Query Plan - caused by ARITHABORT setting? One of our applications is causing a problem in that there is a stored procedure that returns fine in SSMS, in under 1 sec, but in the application its taking anything up to 10 minutes, depending on the parameters used. Any combination of parameters work fine in SSMS and the execution plan looks good to me.

However, when profiled the Application is clearly using a different, less efficient plan. The SqlClient used to connect has the ARITHABORT setting, set to OFF, which when replicated in SSMS I run into the same performance issues.

I'm guessing ARITHABORT OFF will not allow the optimizer to use the cached plan? Or does it run a separate plan?

According to the Apps guys, there is no way to change the SQLClient connection to use ARITHABORT ON

I'm guessing the ARITHABORT setting is a bit of a misnomer here, and it's actually a parameter sniffing thing caused by the optimizer not using the good plan?

I need to force it to use a good plan regardless, so how best to address the issue? Do I create a plan guide or optimize the stored proc in some way?

This is SQL Server 2008R2 SP2.

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key.

Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this:

  We have seen that there is a large number of different cache keys, so
  potentially there can be very many plans for a single stored procedure. But we have also learnt that many of the SET options that
  are cache keys are legacy options that you should never change.

  In practice, the most important SET option is ARITHABORT, because the
  default for this option is different in an application and in SQL
  Server Management Studio. This explains why you can spot a slow query
  in your application, and then run it at good speed in SSMS. The
  application uses a plan which was compiled for a different set of
  sniffed parameter values than the actual values, whereas when you run
  the query in SSMS, it is likely that there is no plan for ARITHABORT
  ON in the cache, so SQL Server will build a plan that fits with your
  current parameter values.

If you can change the stored procedure I would try to fix it there rather than with plan guides. The possible solutions are also explained in the linked article but basically you have these options:

OPTION (RECOMPILE) / WITH RECOMPILE
Add the "optimal" index
Drop the index you don't want the plan to use
Use an index hint to force use of any of the other indexes.
The query hint OPTIMIZE FOR.
Copy the parameters to a local variable.
Change the application behaviour.


Update Table A row if Table B row is changed Is is anyhow possible to tell the database to execute a little script, if a row is changed in a table?

Scenario:
The database constantly monitoring Table B to see if rows are inserted, updated or deleted. If a row is inserted, updated or deleted: Update the row in Table A with the referring identifier.

Setup:

Table A
------------------------
UserID   Name   SalesSum
1        Carl   5
2        Peter  0
3        Oscar  3

Table B
--------------------------
UserID   Timestamp   Sales
1        01:00:00    3
1        02:01:00    1
1        03:54:00    1
3        01:20:00    2
3        02:45:00    1

The point of this being not to do a subquery every time the SalesSum is needed in the application, like:

Select A.Name, SalesSum = (select sum(B.Sales) from [Table B] as B where A.UserID = B.UserID) from [Table A] as A

But instead "just" doing it easy, nice and clean:

Select Name, SalesSum from [Table A]

Or is there anything I might have overseen or am wrong about?

EDIT:

Many of described the uses of inner joins for these sub-calculations.
But when "trying" this, I get a couple of errors: First I have to include each column in the GROUP BY clause unless it is defined by via an Aggregate function. But when trying to do this, then it errors when having a NTEXT datatype column in the clause, because it cannot be compared.

Here is an example of the original query:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = (select sum(C.Sales) from OrdersDetails as C),
 SalesPriceSum = (select sum(C.Sales * C.Price) from OrdersDetails as C)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Where
 ' Clauses on finding specific columns based on the Orders table and the Users table
Order By
 U.Name asc
 B.Period asc

The "new" query, with another inner join, could be something like:

Select
 U.UserID,                                                                 ' Int - Primary Key
 U.Name,                                                                   ' Nvarchar(MAX)
 B.OrderID,                                                                ' Int - Primary Key
 B.Comment,                                                                ' Ntext
 SalesAmount = sum(C.Sales),
 SalesPriceSum = sum(C.Sales * C.Price)
From
 Orders as B
Inner join
 Users as U on B.UserID = u.UserID
Inner join
 OrdersDetails as C on B.OrderID = C.OrderID
Where
 ' Clauses on finding specific columns in Order table
Group By
 U.UserID
 U.Name
 B.OrderID
 B.Comment     <------- This errors because of the Ntext datatype!
Order By
 U.Name asc
 B.Period asc

As written, this is only an example, and therefore there can be a large number of selected columns - specially from the Orders table. All these needs to be in the Group By clauses. But this cannot be done with the Ntext datatypes. So what do I do?

I don't think you need to use a trigger for this at all. With an index on B(UserID) INCLUDE(Sales) (or if there is already a clustered index leading with UserID), this query will get what you need, pretty efficiently, without having unnecessary maintenance happening all the time (even when no queries are running to calculate sums):

SELECT a.UserID, a.Name, SalesSum = SUM(b.Sales)
  FROM dbo.[Table A] AS a
  INNER JOIN dbo.[Table B] AS b
  ON a.UserID = b.UserID
  GROUP BY a.UserID, a.Name;

Look ma, no subquery!

This will generate a different plan, likely, but certainly not a more expensive one. I don't think you're going to get as much benefit maintaining this sum using triggers as you think you will. Make sure you test your entire workload, not just the query that gets the sum, if you implement triggers.

That said, here is what a trigger would look like.

CREATE TRIGGER dbo.MaintainRedundantSums
ON dbo.[Table B]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE a
    SET a.SalesSum = COALESCE(SUM(b.Sales), 0)
    FROM dbo.[Table A] AS a
    LEFT OUTER JOIN dbo.[Table B] AS b
    ON a.UserID = b.UserID
    WHERE EXISTS (SELECT 1 FROM inserted WHERE UserID = a.UserID)
       OR EXISTS (SELECT 1 FROM deleted  WHERE UserID = a.UserID);
END
GO

Please test this trigger in a test environment; this is fairly off the cuff, partly because it's dinner time and partly because I genuinely don't want you to use a trigger for this. If you experience an actual performance problem when calculating the sums at runtime, let's talk about that, instead of premature optimization.

EDIT

Regarding your "slow" join, how does this perform in comparison?

;WITH d AS
(
  SELECT OrderID, SalesAmount = SUM(Sales), SalesPriceSum = SUM(Sales*Price)
    FROM dbo.OrderDetails
    GROUP BY OrderID
)
SELECT
  u.UserID,
  u.Name,
  o.OrderID,
  o.Comment,
  d.SalesAmount,
  d.SalesPriceSum
FROM
  dbo.Users AS u
INNER JOIN
  dbo.Orders AS o -- meaningful aliases please, not A, B, C etc.
  ON u.UserID = o.UserID
INNER JOIN
  d ON d.OrderID = o.OrderID
-- WHERE ...

-- no GROUP BY needed here
ORDER BY
  u.Name,
  o.Period;

You should look at the query plan and see if the cost is mostly in the sorting. I expect that you don't have an index to support ordering by u.Name first, for example. Also I would verify that you have indexes to support your WHERE clauses and JOINs, and I certainly hope that OrderDetails.OrderID is indexed appropriately. If you want help improving the performance of a query (or set of queries), post the queries and their actual (not estimated) execution plans. Jumping to the conclusion that a trigger must be the way to fix it is kind of like buying a new car when you have a flat tire. Fix the problem, don't try to outfit a solution for the whole system.


SQLPlus: Cmd vs PowerShell From cmd.exe, when I run:

C:\oracle\client\BIN\sqlplus.exe username@server

then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get:

ERROR:
ORA-01017: invalid username/password; logon denied

After searching for a bit and making triply sure that I'm typing my password right I still can't figure out why SQLPlus isn't working for me from PowerShell. What am I missing?

Arg, found the problem. My username is ops$account and PowerShell treats $ as a start of variable token. Because $account was null the username I was passing was just "ops". I fixed the issue by escaping the $ with the backtick, i.e.

C:\oracle\client\BIN\sqlplus.exe ops`$account@server


Audit logins and logouts I need to trace and monitor client login connections (successful and failure) to our important SQL servers (20 MSSQL Server). What method do you recommend?

Note: I think enabling "both failed and successful logins" to windows event logs may create large log files so in my mind it is last option. Also i think auditing may affect database performance so this is last option too.

Anything that you do here will impact performance somewhere.  But if it's a requirement then it's a requirement.  The methods you've laid out in your question are the accepted, low impact methods of getting that.

You can mitigate the large log files by rolling over to a new file more frequently.  By default it does that on every server restart, but you can add an agent job to run sp_cycle_errorlog every day, week or whatever.  Then change the number of files to retain (limited to a max of 99).

--May need to adjust if running an instance.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 90

But that just tells you that the user in question logged on.  If you need to do more with that information such as track what they did, then you will need to turn on Auditing (recommended if it's a legal requirement such as HIPAA/SOX; although only available in Enterprise) or you can just use Extended Events.  Extended Events are as lightweight as you can get with tracking statements against the database.

There is one other option and that's a logon trigger, but I would strongly advise NOT to follow that route.  If the logon trigger messes up for whatever reason then you could prevent anyone from logging into the database.  But it only gives you slightly more information than you would get from turning on successful and failed login monitoring.


Skip events on sp_BlitzIndex Is there any way to skip some results on sp_BlitzIndex? Like has done on sp_Blitz, using the @SkipChecksTable.
For example, i have some index thats are not in use, but, those indexes are UNIQUE and i cannot drop then. I just want to ignore these indexes in the result

No, there is no functionality like that in sp_BlitzIndex.


How do I add a composite check constraint that references a composite key? Given the following tables

CREATE TABLE users (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  users_id         TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  description      TEXT NOT NULL
);

Is it possible to create constraints for 'user_id' column , that i can use values form 'users' table only with the same organization_id?

A check constraint that references another table like that is called a FOREIGN KEY. You can do what you want with MATCH FULL

As a special note the types must be the same, so in your example tasks.users_id is text. It must be int (the underlying type of users.id).

CREATE TABLE users (
  id               SERIAL,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  int,
  PRIMARY KEY (organization_id, id),
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  organization_id  int  NOT NULL,
  users_id         int  NOT NULL,
  description      TEXT NOT NULL,
  FOREIGN KEY (organization_id, users_id)
    REFERENCES users (organization_id, id) MATCH FULL
);

You can add the constriant after the fact like this..

ALTER TABLE tasks
  ADD CONSTRAINT asdf
  FOREIGN KEY (organization_id, users_id)
  REFERENCES users (organization_id, id)
  MATCH FULL;


How to give a user "create any database" permission in SQL Server? Is it different from the dbcreator permission?

Can anybody explain the process?

The Server-Level Roles documentation says:

  Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

This obviously exceeds the ability to just "create any database" mentioned in the title of your question.

To add a login to the dbcreator role:

EXECUTE sys.sp_addsrvrolemember
    @loginame = N'LoginName',
    @rolename = N'dbcreator';

The process to grant only the permission CREATE ANY DATABASE is as simple as:

-- Must be in master to grant server-scoped permissions
USE master;
GRANT CREATE ANY DATABASE TO LoginName;

It is generally seen as best practice to grant only the minimum permissions required.


What is a "database"? There was lot of discussion in this question: What database technologies do big search engines use?

So much discussion that it made me confused. So... what is a database, anyway? Are only relational databases "databases"? Are object-oriented databases "databases"? Is any system that allows me to store and retrieve information (like a map, list, etc) a database?

Or does a database have to store/retrieve information and also have some administration features like Users and Privileges? Was dBase III plus a database, since it wasn't really relational?

I will quote Dictionary.com, as I take this as the meaning of database:

  a comprehensive collection of related data  organized for convenient access, generally in a computer.

Under this definition, you can consider a database anything from a full-fledged RDBMS (SQL Server, Oracle, etc.) to a basic flat file.  If it stores data, it technically can be considered a database.

Now, like most things in our modern world, there's the accepted meaning of a name.  And in the case of database, that will vary from person to person.  A lot of people think of a database solely as an entity managed by a data system.

It is worth noting @FrustratedWithFormsDesigner's comment:

  card-catalogues would also count if you removed the "...generally in a computer".

I agree with that statement, and I don't necessarily think that a database needs to live in a "computer" or any electronic device.  A card-catalogue is a perfect example of a non-computerized database.


Restore database - maintenance plan task I need to schedule a task that restores a database once a week from a backup. I have setup maintenance plans for backing up databases (Management > Maintenance Plans) but cannot find out how to make a restore task - does this need to be an Execute T-SQL Statement Task, or is there a Toolbox item for it?

There is no 'Restore DB Task' in the maintenance plan. You will have to use the Execute T-SQL Statement Task. In that task, add a restore script in your T-SQL statement. Simple example:

RESTORE DATABASE YourDBName FROM DISK = 'D:\DBbackups\YourDB.BAK'
WITH REPLACE
GO

Then create a schedule for weekly execution. Hope this is what the business requires, as you are overwriting a DB.


SQL Server Index Drops - Considerations I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB.  I am interested in the opinions of this forum on what checks or confirmations other than reads should be evaluated before performing the index DROPs.  One that comes immediately to mind is whether the index is being used to enforce a necessary unique constraint.  What other factors are important to consider?

If there was a clever person who decided to use index hints in their application's queries, dropping said index will cause the query to fail outright if/when it runs.
Something like a quarter or year-end report might not be showing any index usage due to its infrequency of execution depending on how often the system is restarted.


Query Optimization - CTE Is Slow With OPENQUERY I am running the below query that utilizes a CTE - the full recordset is roughly 10K records.  When I do a Select Top 1000 the query takes roughly 45 seconds to return the results.  I am querying a postgresql database from sql server 2008

Is there a quicker way of writing this query?

Select [Employee Name] = employeename,[Customer Number] = Customernumber
,AVG(quantityordered) As AverageQty,AVG(Price) As AveragePrice
,[Sale Date] = CAST(Saledate As Date)
FROM OPENQUERY(192.155.10.22,'with MaxNumber as
                      (
                        select max(cc2) as "MCN"
                        ,Customernumber
                        from employeeinfo
                        group by Customernumber
                      )
                      Select
                      c1.employeename,c1.Customernumber
                      ,c1.Saledate,ep.saledc1cription
                      ,eq.quantityordered,eq.price
                      FROM MaxNumber c2
                      INNER JOIN Customer c1
                      ON c2.Customernumber=c1.Customernumber
                      AND COALESCE(c2."MCN",0)=COALESCE(c1.cc2,0)
                      INNER JOIN orderpart ep
                      ON ep.Customer = c1.Customerid
                      INNER JOIN orderquantity eq
                      ON eq.employeename = ep.employeename
                      INNER JOIN employee cm
                      ON c1.employee = cm.employee')
WHERE CAST(Saledate As Date) >= '01/01/2017'
GROUP BY customername, Customernumber, Saledate

EDIT
There are no indexes on the tables, and I am unable to modify them to add any.  And this is the execution plan

While I haven't used OPENQUERY, I'll add some - more or less common sense - notes:

You are not doing any joins between the remote (Postgres) server tables and the SQL Server ones. I suggest you move the whole query inside the Openquery call.
The COALESCE() suggests that the 2 columns are nullable? Are they?
If not, remove the COALESCE() calls which prohibit the use of indexes. If they are nullable, you can try to modify the predicate to something like (if they can be 0, it'd have to be more complex):

AND ( c2."MCN" = c1.cc2  OR  c2."MCN" IS NULL AND c1.cc2 IS NULL )

The CAST(Saledate As Date) >= '01/01/2017' looks like a killer, too. There is no reason to use CAST. The expression can be written as (the cast may not be needed at all in Postgres, depending on the column type):

Saledate >= CAST('2017-01-01' AS date)

After pushing the whole query to the linked server, there is no reason to have the WHERE and GROUP BY in the external query. You can collapse the query into one level.
After the rewrite, and if it is still slow, check the execution plan in the remote server. Besides indexes, examine the plan regarding the CTE. CTEs in Postgres are optimization fences, and their use restricts the optimizer into certain options - which may be good or bad for efficiency - depending on too many variables.

The query, in Postgres syntax:

SELECT *
FROM OPENQUERY(192.155.10.22, '
    WITH MaxNumber AS
      (
        SELECT
            MAX(cc2) AS "MCN"
            Customernumber
        FROM employeeinfo
        GROUP BY Customernumber
      )
    SELECT
        c1.employeename           AS "Employee Name",
        c1.Customernumber         AS "Customer Number",
        AVG(eq.quantityordered)   AS "AverageQty",
        AVG(eq.price)             AS "AveragePrice",
        CAST(c1.Saledate AS Date) AS "Sale Date"
    FROM
        MaxNumber c2
      INNER JOIN Customer c1
        ON  c2.Customernumber = c1.Customernumber
        AND COALESCE(c2."MCN", 0) = COALESCE(c1.cc2, 0)
      INNER JOIN orderpart ep
        ON  ep.Customer = c1.Customerid
      INNER JOIN orderquantity eq
        ON  eq.employeename = ep.employeename
      INNER JOIN employee cm
        ON  c1.employee = cm.employee
    WHERE
        Saledate >= CAST('2017-01-01' AS date)
    GROUP BY
        c1.customername,
        c1.Customernumber,
        CAST(c1.Saledate AS date)
')

(will still need fixing, there is a mismatch between the openquery which has select employeename and the external query which has group by customername)


how to use is_srvrolemember to list all server roles of a user For dealing with server permissions I am currently using
 a procedure called sp_SrvPermissions V6.0 by Kenneth Fisher thank you Kenneth, however,

while running a profile trace I often see the following line:

select is_srvrolemember('sysadmin') * 1 +
       is_srvrolemember('serveradmin') * 2 +
       is_srvrolemember('setupadmin') * 4 +
       is_srvrolemember('securityadmin') * 8 +
       is_srvrolemember('processadmin') * 16 +
       is_srvrolemember('dbcreator') * 32 +
       is_srvrolemember('diskadmin') * 64 +
       is_srvrolemember('bulkadmin') * 128

My question is:

How can I use the above script that uses is_srvrolemember to find out the server roles of mycompany\myuser?

The code you saw in profiler has nothing to do with the sp cited.

You can find explicit server roles of mycompany\myuser using following code:

select sp.name
from sys.server_role_members rm join sys.server_principals sp
        on rm.role_principal_id = sp.principal_id
where suser_name(member_principal_id) ='mycompany\myuser';

What I mean by explicit roles: these are roles to which the principal mycompany\myuser was added explicitely: exec sys.sp_addsrvrolemember 'mycompany\myuser', 'some_server_role'.

But your principal is Windows principal, this means he can inherit server role membership from different Windows groups of which he is member.

To find out all the roles he is member you should impersonate the principal and explore his login tokens:

execute as login = 'mycompany\myuser';

select *
from sys.login_token
where type = 'SERVER ROLE';

This will give you all the server roles mycompany\myuser is member of, those inherited from Windows groups included.


Database in SIMPLE recovery model full using SSIS bulk insert task I have a database in SIMPLE recovery model with 10GB free.

The file I am trying to load through SSISs bulk load is 1GB.

The table is empty (truncated prior to each loading), is a heap and has no non clustered indexes. It is a table of varchar(255) fields.

When running the bulk insert task, I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message:

[Bulk Insert Task] Error: An error occurred with the following error message: "Could not allocate space for object 'dbo.tablename' in database 'DBNAME' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

Firstly, I thought because I was in SIMPLE recovery model, bulk loading isn't logged. I know that it is when there is data in the table AND a clustered index, but neither is the case here. I assume the Bulk Insert Task is included in this as it should use BCP. Am I wrong?

Secondly, it seems strange that a 1GB text file would result in 10GB in SQL Server varchar fields. This seems mighty strange. The data is not being transformed, it is being Bulk loaded into a table. Why is it growing so large?

I can watch the database size grow from 2GB to 12GB (max space
  available) where it runs out of space and returns the following
  message:

The issue is either you're simply running out of disk space and the physical file cannot grow to complete the transaction, or you have a limit on the SQL file growth settings and the physical file cannot grow more to complete the transaction (whatever max space available means extactly e.g. per settings or disk capacity).

  I thought because I was in SIMPLE recovery model, bulk loading isn't
  logged.

Per the below site and quoted text it does state  minimal logging is more effecient than full logging, but it does NOT state that NO logging occurs at all in SIMPLE recovery mode.

  Prerequisites for Minimal Logging in Bulk
  Import

  under the simple recovery model or bulk-logged recovery model, minimal
  logging of bulk-import operations reduces the possibility that a
  bulk-import operation will fill the log space. Minimal logging is also
  more efficient than full logging.

So this means there IS still SQL transaction logging that occurs when the database is in SIMPLE recovery mode but it's minimal logging when compared to FULL recovery mode.

  Why is it growing so large?

It would seem the explanation would be that your your committed transaction sizes with the BULK INSERT operations are HUGE. Simply make the commit\batch size parameters of your logic in SSIS or your execute TSQL in the package of a smaller size. Try testing with 100, 1000, 10000, 100000, and so on to see what gives you the best result and prevents the issue from occurring.

If the transactions are smaller, once the committed transactions are committed in SIMPLE recovery mode, then the log space of the committed transactions can be reused by subsequent BULK INSERT (or other) transactions.

Additional Resources

(These articles seem to have some references to potential parameters in SSIS package areas to set for smaller transactions, etc. The titles or the steps may be for a different ultimate goal, but there is potentially applicable content in these for what you may need to change in your SSIS logic to rectify your issue so these may be worth a simple read.)

Top 10 SQL Server Integration Services Best Practices
Have your SSIS Fast Load (Bulk Load) and Row by Row Error Messages too


MySQL 'generator' not closing connections I have the following function which is leaving connections open but I can't figure out why.

The Cursor class creates a python-generator type function, allowing me to iterate over millions of rows.

connection=MySQLdb.connect("", "", "", "",
                           cursorclass = MySQLdb.cursors.SSCursor)
cursor=connection.cursor()
cursor.execute("select id, ...", (profile_id, ...))
try:
    for row in cursor.fetchall():
        yield row
except:
    pass
finally:
    connection.close()

Where am I going wrong?

See if you have enable "connection pooling".  If so, don't worry about the connections staying open.


SQL, list if has at least one value for each property I have the following query:

SELECT
    m.MilgaId,
    opt.PropertyId,
    sd.PropOptionId As StudentOptId

FROM
     Milga AS m
        INNER JOIN
        PropertyOptionInMilga AS poInMil
            ON m.MilgaId = poInMil.MilgaId
        INNER JOIN
        PropertyOption AS opt
            ON poInMil.PropertyOptionId = opt.PropertyOptionId
        LEFT OUTER JOIN
        StudentData AS sd
            ON opt.PropertyOptionId = sd.PropOptionId AND sd.UserId = 'A8F60C49-960F-4A6D-A759-925077607C5A'
WHERE
    (m.IsEnable = 1) AND
    (m.IsSpecial = 0)

    GROUP BY
        m.MilgaId,
        opt.PropertyId,
        sd.PropOptionId,
        m.IsEnable,
        m.IsSpecial
    HAVING
        (
            m.IsEnable = 1 AND
            m.IsSpecial = 0
         )
    ORDER BY
        m.MilgaId

Query results:

MilgaId PropertyId  PropOptionId
937       90            2291
937       132           null       <-----  132 with null
937       132           2434       <-----  132 has one without null so it's in result
938       25            38
938       91            NULL        <-----
938       132           2434
939       201           2600
939       202           null

I would like to get all the rows for MilgaId when all its PropertyId has at least one value

The is the results I need:

MilgaId PropertyId  PropOptionId
937       90            2291
937       132           2434

** MilgaId=937 is in the results because it has two propertyId: 90 & 132, while 132 is null but also 2434.

Milga=938 is out because for PropertyId=91 there is no value.

This is a start

select Milga.*
from Milga
join ( select MilgaId, count(dictinct(PropertyId)) as cnt
       from Milga
       group by(MilgaId) ) m1
  on Milga.MilgaId =  m1,MilgaId
join ( select MilgaId, count(dictinct(PropertyId)) as cnt
       from Milga PropOptionId is not null
       group by(MilgaId) ) m2
  on m2.MilgaId =  m1MilgaId
 and m2.cnt     =  m1cnt

if the count is the same then all PropertyId have a value


MySQL Migration from Slave to Master I recently had a problem with our server. It required us to install a new hard drive, cpanel, and setup the old hard drive as a slave. How can I migrate the mysql database from the slave drive to the master drive? The slave contains the tables with MYD, MYI, and frm files. The database is probably about 20G.

I'm assuming SSH is the best way to do this. I am not very familiar with SSH commands and cannot find out how to do this. I've already setup the new database (on the fresh install on the master drive) with the same name, users, and passwords.

System: Linux, Centos 6.4

I was left wondering because you mentioned SSH which implied connecting from your database server to a different machine (free tip: You use "SSH" to connect to a machine, but the things you type after that are "shell commands", not "SSH commands."  I googled that phrase just now and was horrified at how frequently they're improperly called "SSH commands") ... and in Sqlbot's world, a "slave" is a different machine -- a replication slave -- which also was initially confusing.

You mentioned MYD and MYI files but not IBD files, so either you are using MyISAM and not InnoDB, or you're using a mix but you never set innodb_file_per_table = 1.

Your path to recovery depends on whether you are using InnoDB.

If you're not using InnoDB, then you can -- believe it or not -- just copy the old database directories and files into place and fix the permissions if needed.  MySQL is extremely forgiving about this with MyISAM tables.

Connect to your MySQL server and locate the data directory.  From an old CentOS machine of mine (probably still the same for you):

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Find the comparable directory on your old hard drive, and copy all of the directories -- but not the files, and not the "mysql" directory -- into your datadir (probably /var/lib/mysql).

Then, if the files aren't owned by the "mysql" user,

root@host# chown -R mysql:mysql /var/lib/mysql

Then see if it worked.

mysql> SHOW DATABASES;

If you are using MyISAM only, your databases and tables should be on the new server, though you will need to do CHECK TABLES on all of your tables or use the mysqlcheck utility to verify that they are all intact and repair them as needed.

If you're using InnoDB, the steps will be different, so see if this works for you.


Which of these table designs is better for performance? I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.

Here's what I know

Company has over 2.5 million accounts
Of these, they currently work an average of 200,000 per month (that changes with staffing levels, which are currently low)
They have 13 different cost types they'd like to track, and they have warned that they might add more in the future
They want the costs to be tracked daily
Costs are not split across the entire inventory. They are either split across the # of accounts that are worked per month (200,000), or users can enter account identifiers to apply a cost to a group of accounts, or they could simply specify which accounts to apply the cost to.

My first thought was a normalized database:

AccountId
Date
CostTypeId
Amount

My issue with this is, do the math. This table is going to get huge quickly. Assuming all 13 cost types get applied to all worked accounts for the current month, that's 200k * 13 * N days in month, which is somewhere around a 75-80 million records per month, or close to a billion records per year.

My second thought was to denormalize it a bit

AccountId
Date
TotalCost
CostType1
CostType2
CostType3
CostType4
CostType5
CostType6
CostType7
CostType8
CostType9
CostType10
CostType11
CostType12
CostType13

This method is more denormalized and can create up to 6 million records per month (200k * N days in month), or about 72million per year. It's a lot less than the first method, however if the company decides on a new Cost Type in the future, another database column will need to be added.

Of the two methods, which do you prefer? Why? Is there another alternative that you can think of which would handle this better?

I am most interested in reporting performance, both summerized and detailed reports. The job that will spread the costs out over accounts will be run nightly when no one is around. A secondary concern is database size. The existing database is already almost 300GB, and I believe the space on disk is around 500GB.

The database is SQL Server 2005

A billion records a year isn't much.

With partitioning (per Costtype maybe) and archiving it is manageable.

The number of data items to store is still 200k * 13 * N. As columns, you'll get less rows per page and it will take more space than as rows. You may gain if "CostType1" is not a fixed length datatype, but it's marginal.

"KISS" as they say